SQL 语句优化是一个既熟悉又陌生的话题。面对千奇百怪的 SQL 语句,虽然数据库本身对 SQL 语句的优化一直在持续改进、提升,但是我们不能完全依赖数据库,应该在给到数据库之前就替它做好各种准备工作,这样才能让数据库来有精力做它自己擅长的事情。
就拿 MySQL 来讲,一条 SQL 语句从客户端发出到数据库端返回结果一般会经历几个阶段:词法解析、语法解析、语义解析、逻辑优化、物理优化、最终执行并返回结果。那么这几个阶段,我们 DBA 能参与的也就是两个阶段:逻辑优化以及少许物理优化。所以在我们 DBA 这侧,对 SQL 语句的优化简单来讲就是让我们自己写的 SQL 语句能更好的适应数据库内置的优化规则,进一步让 SQL 语句在每个处理阶段能扫描更少的记录数量、字段数量来改善查询效果。
逻辑优化可以理解为基于N多数据库内置规则的预处理,规则定义越全面,对 SQL 语句优化的就越极致。比如使用表关联代替子查询、分组聚合条件上推、在特定条件下用内连接来替换外连接、视图上推到基表等等一系列优化措施。
物理优化可以理解为数据库按照当前 SQL 语句涉及到的表统计信息、列统计信息、索引个数、索引优劣、当前运行负载、当前硬件资源等可变因素来决定如何生成最优执行路径的方法。
一般来讲,我们拿到一条“不是很优化”、“烂的”、“慢的” 等 SQL 语句(至于怎么拿到这条语句,不在本篇讨论范围),应该按照以下几个步骤来逐步分析:
一、定位该SQL 语句涉及到的表结构,确认是磁盘表还是视图,如果是磁盘表,那么该考虑以下几点:
- 这些表是否统一为InnoDB引擎(MySQL 5.7 以及以前一些老旧版本可能会有非InnoDB引擎表),如果不是,转换表引擎为InnoDB。因为MySQL从5.5 版本开始,所有针对存储层的优化都是针对InnoDB引擎的。
- 极个别表为临时表。查看临时表相关参数是否设置合理;或者说能否把临时表替换为磁盘表。
- 查询每张表的字段类型,看有无不合理的部分。
- 查询每张表的记录数,检查是否过大需要后续拆分。
- 查询每张表的统计信息,检查是否及时做了更新。
- 针对这些表结构做进一步分析,查看索引设计是否合理?大致会有以下几种结果:
(1). 都没有索引,有的连主键都没有。
(2). 都有主键或者唯一索引,但是没有二级索引。
(3). 有主键或者唯一索引,也有一些二级索引,并且二级索引可选择性也比较优化。
(4). 有主键或者唯一索引,也有一些二级索引,但是这些二级索引可选择性很差。
二、如果有些表是视图,需要考虑以下几点:
- 该视图内部的算法有两种,一种是临时表(TEMPTABLE)、另外一种是合并(MERGE )。可以针对这两种算法来分别测试视图整体性能哪个较优化。
- 该视图内部如果有很复杂的处理逻辑,想办法把这部分内容简化或者从数据库剥离转交给应用处理,避免数据库将其劣势放大。
- 该视图如果非必须,可拆解为基表与上层SQL 语句做合并处理,这样效率较之前更优化(比如视图内部多表关联与上层基表再次关联,拆分后,优化器就会有更多更优的表关联顺序)。
三、到了这一步,如果是多张表关联,此处检查表关联键:
- 表关联键为主键和外键,也即两表用来关联的字段在一张表唯一并且在另一张表被引用,这时需要补充额外的过滤条件来减少扫描记录数。
- 表关联键为非主键,也即两表用来关联的字段都不唯一, 需要优化为唯一键值关联。
- 表关联键字段编码不一致,需要人为转换字段编码并改为一致。
四、基于以上几点,表结构分析这块已经大致完毕。接下来从SQL 语句层面来分析,比如这条SQL语句能否修改为更加优化的方式。可以考虑以下两点:
- SQL语句本身很简单,没有必要做修改。 比如这条语句本身是20张表的内联查询,那它不够优化并不是因为写的不好,而是表关联个数实在太多。
- SQL语句本身很复杂,仔细分析后,可以简化这条语句的写法。 复杂SQL语句又可以分为很多类别,比如多张子表关联、多张表嵌套子查询、多个子查询合并输出、多个聚合类操作等等。每种都有不同的优化方法,后续我会一一介绍。
五、那么前面几点做完后,进一步分析优化后SQL 语句的执行计划(如果有条件模拟生产环境压力模型),一般考虑如下几点:
- 改写后的语句执行计划很优化,走最合适的索引、语句本身也改的很简洁,那么这条语句改写完成。
- 改写后的语句执行计划没有走合适的索引,可以考虑在表上建立合适的索引。如果建新索引后,这条语句执行效果很好,那么改写完成。
- 改写后的语句走了合适的索引,执行效果依然不理想,这时可能有以下几种原因:
(1). 这条语句走的索引在不同过滤条件下,运行效果忽好忽坏。
比如日期字段,过滤条件为昨天的查询记录数为100条,过滤条件为前天的查询记录数则变为1W条。
(2). 这条语句走的索引较优,但是表记录数实在太大,走索引过滤的记录数也很多,需要从表结构这层做些优化。比如前面几期讲过的分区表、拆分表等方法;或者是从业务层面限制这条语句来扫描更少的记录数等等。
经过以上几个步骤,一般的语句基本上都能达到比较优化的结果。后续我将逐步介绍各种优化方法以及在MySQL里如何付诸于实践。
关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!