MySQL性能优化(七)：其它优化

红太狼 2023-07-21 05:45 15阅读 0赞

**目录**

一：数据库配置优化

二：读写分离

三：应用程序优化

四：mysql服务器硬件升级

五：定时清除不需要的数据，定时进行碎片整理(MYISAM)

六：对MySQL所在的linux服务器进行参数优化

--------------------

# 一：数据库配置优化 #

mysql是一个高度定制化的数据库系统，提供了很多配置参数，一般都需要根据应用程序的特性和硬件情况对mysql做配置优化，windows配置文件为my.ini，linux为my.cnf

常用的优化配置变量如下：

# 端口
    port=3306
    
    # 关闭查询缓存
    query_cache_type=0
    query_cache_size=0
    
    
    # 内存是影响数据库性能的重要资源，也是mysql性能优化的一个重要方面，
    innodb_additional_mem_pool_size=64M
    innodb_buffer_pool_size=1G
    innodb_log_buffer_size=1MB
    
    # 并发连接数 
    max_connections=1000
    
    
    
    
    binlog_cache_size=4M
    key_buffer_size=16MB
    bulk_insert_buffer_size=8MB

*  query\_cache\_type : 如果全部使用innodb存储引擎，建议为0，如果使用MyISAM 存储引擎，建议为2，同时在SQL语句中显式控制是否是哟你gquery cache
 *  query\_cache\_size: 根据 命中率（Qcache\_hits/(Qcache\_hits+Qcache\_inserts)\*100)）进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大
 *  binlog\_cache\_size: 一般环境2MB～4MB是一个合适的选择，事务较大且写入频繁的数据库环境可以适当调大，但不建议超过32MB
 *  key\_buffer\_size: key\_buffer\_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。此外，当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意，由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。所以，我们的 SQL 一定要尽可能让过滤条件都在索引中，以便让缓存帮助我们提高查询效率。如果不使用MyISAM存储引擎，16MB足以，用来缓存一些系统表信息等。如果使用 MyISAM存储引擎，在内存允许的情况下，尽可能将所有索引放入内存，简单来说就是“越大越好”
 *  bulk\_insert\_buffer\_size: 如果经常性的需要使用批量插入的特殊语句（上面有说明）来插入数据，可以适当调大该参数至16MB～32MB，不建议继续增大，某人8MB
 *  innodb\_buffer\_pool\_size: 用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key\_buffer\_size 参数。如果不使用InnoDB存储引擎，可以不用调整这个参数，如果需要使用，在内存允许的情况下，尽可能将所有的InnoDB数据文件存放如内存中，同样将但来说也是“越大越好”
 *  innodb\_additional\_mem\_pool\_size: 一般的数据库建议调整到8MB～16MB，如果表特别多，可以调整到32MB，可以根据error log中的信息判断是否需要增大
 *  innodb\_log\_buffer\_size: InnoDB 存储引擎的事务日志所使用的缓冲区。默认是1MB，系的如频繁的系统可适当增大至4MB～8MB。当然如上面介绍所说，这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB
 *  innodb\_max\_dirty\_pages\_pct: 根据以往的经验，重启恢复的数据如果要超过1GB的话，启动速度会比较慢，几乎难以接受，所以建议不大于 1GB/innodb\_buffer\_pool\_size(GB)\*100 这个值。当然，如果你能够忍受启动时间比较长，而且希望尽量减少内存至磁盘的flush，可以将这个值调整到90，但不建议超过90

开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表，其查询结果也不会被缓存。缓存建立之后，Mysql的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启缓存查询要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十MB比较合适。此外，还可以通过sql\_cache和sql\_no\_cache来控制某个查询语句是否需要缓存：SELECT SQL\_NO\_CACHE \* FROM tbl\_user WHERE email = ‘admin@163.com’;

# 二：读写分离 #

如果数据库压力很大，一台服务器支撑不了，那么可以使用mysql的主从复制实现多台服务器同步，将数据库的压力分散到多台数据库服务器上。将增删改交给master执行，查询交给slaver查询。

一个主服务器承担更新操作，多台服务器承担查询操作，主从之间通过复制实现数据同步。多台服务器一方面用来保证可用性，另一方面可以创建不同的索引来满足不同的查询

# 三：应用程序优化 #

由于数据库服务器本身的性能局限，就必须对前台应用进行一些优化，减少数据库的压力。

*  使用数据库连接池
 *  减少对mysql的访问

**：**能够一次连接就能提取所有结果的，就不用两次连接，例如批量插入、批量更新  
          **  ：**多表连接的性能很低，特别是连接的表比较多的时候，可以通过分为多个sql查询，然后使用应用程序组装成想要的数                  据格式，这是解决多表连接查询性能低的一个很重要的手段。  
            **：**数据库缓存，例如mybatis默认开启缓存的  
            **：**增加缓存层，如redis，memcache, elasticsearch

# 四：mysql服务器硬件升级 #

# 五：定时清除不需要的数据，定时进行碎片整理(MYISAM) #

# 六：对MySQL所在的linux服务器进行参数优化 #

/etc/sysctl.conf  
/etc/security/limit.conf  
/etc/fstab