优化MySQL哪些地方需要注意

本文主要给大家简单讲讲优化MySQL哪些地方需要注意，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望优化MySQL哪些地方需要注意这篇文章可以给大家带来一些实际帮助。

1、控制适当的数据文件大小：

  数据量越大，数据文件的大小也会越大，索引文件也就越大，从而影响性能，会使性能急剧下降，所以，要限制数据库单表的数据量和数据文件大小；

2、碎片空洞注意：

 往往我们在删除表数据的时候，明明已经删除可是在底层只是被标记为删除，实际上并没有释放空间，所以要定期查看表的碎片，

show  table  status  ---发现表的碎片情况（查看实际数据data_length和空洞数据data_free的比例如果，data_free非常大，则说明该表需要优化了）

优化的方法是：optimize  table  表名；   或者  alter  table  表名 engine=innodb来重建表空间；

3、行存储格式注意：

  在MySQL5.7.9之后，innodb表的行格式由之前默认的compact变成了dynamic，二者的区别可以查看官方文档，但是，compact格式会节约20%的空间，同时在存储UTF8或UTF8MB4数据的时候，compact格式在存储时会尽量的节约空间，不对其中的空格进行存储；

4、正确使用索引：

  索引虽然能提升查询性能，但是会降低MySQL写数据的速度，同时，也会增大数据文件的大小，加的索引越多，写入数据越慢，数据文件也就越大。所以在设计索引的时候，会要求主键的字段类型一定要用数字类型，并且要尽可能的小，能用INT的，绝对不要用bigint。在设计二级索引的时候，只添加需要的索引，避免添加重复索引和冗余索引，针对长字符串字段，尽量添加前缀索引；

---如何正确使用索引：

①：MySQL在使用索引时，采用的是最左匹配原则，如果是多列索引：idx_a_b_c(a,b,c)，则可以发挥索引功能组合有：a, (a,b)  (a,b,c)

②：MySQL在计算列里无法使用索引

③：MySQL在否定条件中不能使用索引

④：MySQL在join中连接字段类型如果不一致，则不能使用索引

*********************************************************************************

在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的同步性能；

优化MySQL哪些地方需要注意就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。