MySQL优化思路总结

MySQL优化思路总结

1. 背景
   MySQL作为目前应用最广泛的开源关系型数据库，是程序员必须要掌握的知识技能，而对MySQL性能的优化又是程序员们经常需要面对的问题，不管是日常工作中还是面试中，都避免不了面对MySQL的性能问题，所以，博主特意整理了这篇文章，希望对各位有帮助。

2. 优化思路
   谈起MySQL的性能优化，除了最常见的语法优化和索引优化外还有多种手段能提升MySQL数据库的性能，但是在此之前，我们必须对MySQL的执行流程有所了解，关于MySQL的执行流程我在前面的文章中有提到，需要的朋友可以去看看
   csdn：https://blog.csdn.net/qq_41992429/article/details/128718542
   简书：https://www.jianshu.com/p/0865d692175f
   我整理的优化思路大致分为6个方向，下面将一一介绍
   

3. 语法优化

   • select指定字段名，避免二次解析

   • in条件不宜过多值，最多999个

   • 尽量把字段设置为not null，这样数据库不用去比较null值

   • 使用连接代替子查询，避免临时表的产生

   • 尽量用小表驱动大表，可以减少嵌套循环的次数、IO总量以及CPU运算次数

   • 避免在where子句中对字段进行null值判断，会导致放弃索引

   • union默认排序，union all默认不排序

   • having可以使用聚合函数，但是是在结果集上进行过滤，建议优先考虑where

   • limit优化，对偏移量很大的分页采用延迟关联来提升效率
     何谓"延迟关联" ：经过使用覆盖索引查询返回须要的主键,再根据主键关联原表得到须要的数据

     select xxx from t order by xxx  limit 50000,5
     改为
     select id,xxx from t inner join (select id from t order by title limit 50000,5) lim using(film_id) on t.id = lim.id
     

     这种做法会先通过主键关联查数据，而不是直接去扫描前50000行数据

   • group by优化，如果使用EXPLAIN分析group by的语句在EXTRA字段中显示filesort表示用到了文件排序，因为默认情况下MySQL会对group by的所有字段进行排序，因此我们可以手动选择索引字段进行排序，或者不需要排序可以禁用掉

     SELECT age,count(*)   FROM student GROUP BY age ORDER BY NULL;
     

   • order by优化，业务条件允许的情况下，WHERE 和 ORDER BY 使用相同的索引、ORDER BY 字段的顺序和索引顺序一致、ORDER BY 的字段都是升序或者都是降序可以提高排序的性能

   • join性能优化，优先选择索引嵌套查询连接算法进行连接，关联条件尽量选择索引字段，注意关联的两个表要确保编码一致和字段类型一致

4. 索引优化

   • 创建索引时不要选择字段重复过大的数据，也不要创建太多索引，会影响插入、更新和删除的性能
   • 联合索引需要遵从最左原则，不用在意顺序，MySQL优化器会自动优化字段顺序，遇到范围查询(>、<、between、like)就会停止匹配，在MySQL8.0版本中增加了索引跳跃扫描功能，当第一列索引的唯一值比较少时，即使where没有使用第一列索引，查询的时候也可以用到联合索引
   • 索引字段使用函数、发生计算会导致索引失效
   • 操作符前后类型不一致时会发生隐式转换，导致索引失效
   • %like匹配导致索引失效
   • or条件必须两边都用到索引才会走索引
   • in或者not in的条件过大会导致索引失效，进行全表扫描
   • 尽可能减少索引字段的长度，可以考虑前缀索引
   • 覆盖索引，减少回表。覆盖索引是一种索引的使用方式，需要配合联合索引一起实现，在MySQL5.6版本之后就开始支持了，能够很大程度上减少回表带来的IO消耗

5. 内存优化
   MySQL进行数据操作的时候会先将数据从磁盘读取到内存中，在内存中操作数据，然后通过刷盘机制将修改的数据重新刷回磁盘，因此我们可以通过合理分配内存来提升MySQL的性能，这可以说是见效最快的一种方式。
   对于MySQL的内存优化，本文主要提供两个方向，一个是buffer pool，一个是change pool

   • buffer pool：InnoDB存储引擎使用Buffer Pool在内存中缓存表数据和索引，处理数据时可以直接操作缓冲池的数据，提升InnoDB的处理速度。buffer pool中维护着三个双向链表和三种数据页，分别是freeList、LRUList、flushList，空白页、缓存页、脏页，并且LRUList还通过last recent used算法实现了冷热数据的隔离，会自动淘汰不常用的数据，关于buffer pool的具体内容我会在后面的文章中详细介绍，本文先提供buffer pool的优化参数
     • innodb_buffer_pool_instances 与 innodb_buffer_pool_size 配置缓冲池的实例和缓冲池大小：通过配置多个缓冲池可以减少不同线程的竞争，提升并发度。通常在专用服务器上，80%的物理内存会分配给Buffer Pool。
     • innodb_buffer_pool_chunk_size 配置缓冲池的块大小：当增加或减少innodb_buffer_pool_size时，操作以块形式执行，块大小由此参数决定，默认为128M。
     • innodb_max_dirty_pages_pct 配置脏页比例：根据设置的缓冲池中脏页比例，来触发将脏页刷盘的时机。另外，InnoDB也根据redo log的生成速度和刷新频率，来触发刷盘时机。
     • innodb_read_ahead_threshold 与 innodb_random_read_ahead 预读参数配置：预读是指一次I/O请求磁盘中某页中的数据时，会同时同步取出相邻页面的数据，缓存到缓冲池。因为，InnoDB认为这些页面的数据大概率也将会被读取，从而来提升I/O性能。包括线性预读和随机预读。
   • change buffer：用来缓存不在缓冲池中的辅助索引页(非唯一索引)的变更。这些缓存的的变更，可能由INSERT、UPDATE或DELETE操作产生，当读操作将这些变更的页从磁盘载入缓冲池时，InnoDB引擎会将change buffer中缓存的变更跟载入的辅助索引页合并。change buffer可能缓存了一个页内的多条记录的变更，这样可以将多次I/O操作减少至一次，以减少大量的随机IO。在内存中，change buffer占据缓冲池的一部分。在磁盘上，change buffer是系统表空间的一部分，以便数据库重启后缓存的索引变更可以继续被缓存
     • **innodb_change_buffering **：如果工作集很少用到二级索引，那么我们可以通过innodb_change_buffering 参数禁用change buffer来获得更多的内存和CPU，参数值有all: InnoDB默认值，允许所有操作；none: 不要缓存任何操作；insert: 缓冲插入操作；deletes: 缓冲区删除标记操作；changes: 缓冲插入和删除标记操作；purges: 缓冲在后台发生的物理删除操作
     • Innodb_change_buffer_max_size：如果辅助索引的变更比较多或者比较均衡，那么可以通过这个参数来调整change buffer占整个缓冲池的百分比，默认情况下是25，最大值为50
   • 关于MySQL内存方面除了上述的两个优化方向外还有其他的思路，比如说log buffer的设置、redo log的刷盘策略（通过Innodb_flush_log_at_trx_commit参数，设置为1：每次提交事务都将进行同步，刷盘操作（默认）；设置为0：每次事务提交不进行刷盘操作（系统默认master thread每隔1s进行一次重做日志的同步）；设置为2：表示每次事务提交时都只把redo log buffer内容写入page cache，不进行同步，由文件系统(os)自己决定什么时候同步到磁盘）、自适应哈希索引等，这里就不一一赘述了，有兴趣的小伙伴可以评论区留言或者自行探索

6. 缓存优化
   在MySQL早期版本有query cache，但是后来慢慢禁用，直至现在8.0版本直接废弃掉了，原因在于query cache缓存的是查询结果集，这样的缓存命中效率太低，只要SQL语句的条件不一样就会导致无法命中，所以query cache的实用性是非常低的。
   除了query cache之外，在MySQL的执行流程中我们还可以将执行计划缓存起来，也就是plan cache，这个缓存的是MySQL经过解析器、预处理器和优化器解析SQL并且优化之后得到的SQL最终执行计划，将这个缓存之后如果后续遇到相似的SQL语句那么就可以跳过前面解析和优化的过程，这在SQL复杂的情况下会大大提高性能，因为对于复杂的SQL而言解析和优化的成本是很高的，可惜的是MySQL并没有直接提供相关参数让我们开启plan cache，如果我们想做到这一点的话可以使用阿里云的RDS数据库，这是阿里基于MySQL开发的一种稳定可靠、可弹性伸缩的在线数据库服务。

7. 查询优化器优化
   MySQL采用的是CBO（基于成本的优化规则），这种方式会根据模型计算出各个可能的执行计划的代价，然后选择代价最少的那个。它会利用数据库里面的统计信息来做判断，如果执行计划非常多，那么在这一步会耗费很多性能，所以我们可以通过指定优化器的行为来避免复杂的计算和选择，具体方法可以看这篇文章：https://www.jianshu.com/p/1b3fd4f12e77
   这种方式和plan cache的不同在于这里是手动指定优化器行为，而plan cache是将优化器优化完成的执行计划进行缓存

8. 业务优化
   如果我们在工作中遇到了系统性能的问题，除了从数据库层面解决之外还可以根据我们对业务的理解进行系统的优化，比如使用异步、缓存、任务分治、懒加载、限流、负载均衡等方式减轻数据库方面的压力，获得性能上的提升