MySQL优化的一些需要注意的地方

在MySQL中一些比较常用的系统参数：

1、general_log：

  建议在数据库正常服务时，将该参数关闭，因为他会记录提交到MySQL的一切东西，即浪费磁盘又影响效率。同时它也是分析问题的利器，可以在数据库有异常的时候，把它打开，然后截取一段时间的日志，以帮助定位问题；

2、query_cache_size：

  是用来缓存sql语句文本和对应查询结果的缓存空间。如果表没有变化，再次查询会直接返回结果，这是很高效的；如果表变化非常频繁，则要不断更新cache中的内容，并且这个时候所粒度非常大，反而会成为瓶颈。所以很多情况下会关闭这个选项，将参数设置0

3、tmp_table_size：

  在group by 或 distinct 的时候，如果sql语句用不到索引，就会使用系统内部临时表记录中间状态。如果tmp_table_size不够大，则MySQL会自动使用物理磁盘，这会对查询性能造成很大影响，增加此参数可以降低这种情况发生的概率；（注意：这是占用物理内存的，要考虑实际的内存空闲情况）

4、innodb_buffer_pool_size：

  innodb最只要的缓存，用来缓存innodb索引页面，undo页面及其他一些辅助数据，建议配置物理内存的50%--75%

5、innodb_buffer_pool_instances：

  这个参数，把原来一整块buffer pool分割为多块内存空间，每个空间独立管理自己的空闲链表、刷新链表、LRU及其他数据结构。这大大增加了并发性，能更有效利用缓存；

6、innodb_log_file_size和innodb_log_files_in_group

   这两个参数结合，决定了redo空间的大小，redo空间越大，可以存储的增量更新日志越大，有效降低buffer pool脏页面被淘汰的速度，同时减少了checkpoint的次数，降低磁盘IO置换率，从而提升数据库的写入效率。不过也有可能导致数据库异常退出时，恢复时间被拉长；

7、innodb_old_blocks_pct和innodb_old_blocks_time

  这两个参数控制buffer pool中缓存数据的过期和移动行为，二者结合设置，可以优化一些全表扫描带来的大规模更新buffer等问题；

8、innodb_numa_interleave：

  该参数避免出现内存被交换到SWAP分区，影响性能

9、innodb_autoinc_lock_mode：

  在innodb有自增列的情况下，在插入数据的时候，会自动产生自增值，这个参数是控制自增值生成的方式。目前有3个选项：0 1  2 ；使用2即in特人leave的，这样在insert数据的时候不会用到标记的auto-inc锁，避免了auto-inc的死锁问题，在insert...select场景下会极大提升性能，在做普通insert的时候，也会提升并发执行的效率；

 ---注意：这个时候产生的自增值不是连续的，同时binlog格式需要设置为ROW，才能保证数据的安全性和一致性；

10、innodb_flush_method

  innodb刷新数据和日志到磁盘文件的方式，默认为NULL，但其实如果是Unix系统上，默认是fsync；在使用SSD或PCIE类型的存储时，可以设置为O_DIRECT, 该参数会提升性能；

11、innodb_doublewrite

  如果底层存储时支持原子写的，则可以关闭两次写，以提升效率；

12、innodb_io_capacity

   如果使用SSD设备的时候，可以适当提高这个参数值，以加速flush的频率；

13、innodb_thread_concurrency

  在并发量大的实列上，增加这个值，可以降低innodb在并发线程之间切换的花销，以增加系统的并发吞吐量；

14、innodb_flush_log_at_trx_commit

  该参数的值0 1  2；0性能最好，但不安全； 1 最安全，但性能最差，如果使用的磁盘足够好，还是建议使用1  ，否则根据实际情况选择性的设置2

15、sync_binlog

  MySQL同步binlog到磁盘的方式， 1 最安全，但性能最差； 0性能最好，但最不安全。建议设置1

16、binlog_format

  MySQL的日志格式，在MySQL5.7.7以后，官方就默认为ROW了，这也是最建议的设置

17、binlog_order_commits

  事务在提交的时候写入binlog的顺序，这是双刃剑，如果打开，可以保证事务都以相同的顺序写入二进制文件，如果关闭则可以提升性能。根据实际情况决定吧

18、tx_isolation

  设置MySQL的隔离级别，推荐设置read committed，这在保证性能的前提下，同时设置binlog_format=ROW，确保通过binlog同步数据主从库的一致性，兼顾安全，满足绝大多数业务的需求；

19、slave_parallel_workers

  在进行多线程复制的时候，如果设置此参数为非零值，则可以打开多线程并发执行回放日志的操作，以提升slave的同步性能；

本文出自 “笨小孩的dba之路” 博客，请务必保留此出处http://fengfeng688.blog.51cto.com/4896812/1956365

MySQL优化的一些需要注意的地方

标签：mysql优化   数据库优化   mysql5.7优化