那些年我们一起优化的SQL

一、背景

伴随着业务的持续发展与更新升级，在系统中出现了数量显著增加的SQL语句查询问题。尽管这些问题不会造成严重后果（即不致命），但会显著影响用户体验。在进行这些 SQL 语句查询效率优化的过程中，默认积累了一些宝贵的经验教训。本文旨在通过实际案例分析的方式，在深入探讨常见原因的基础上进一步解释如何有效进行 SQL 查询性能调优工作。

本文讲解基于MySQL 5.7。

二、慢查优化

本节主要针对常见的慢查进行分类，讲解怎么去优化。

2.1 建立索引的 正确姿势

面对大量数据时
如果没有相应的索引支持
数据库必须逐行扫描整个表的数据以进行判断
因此在进行SQL优化时
首要任务就是确认是否存在合适现有的索引
对于较为复杂的业务场景
一个表可能涉及多种不同的查询模式
这就要求我们建立多样化的索引来提升查询效率
然而过度依赖多个高密度的索引不仅会导致增删改操作效率低下
还会占用更多的存储空间用于保存这些辅助结构信息
因此关键在于掌握正确的策略来合理配置和管理好这些数据库对象

2.1.1 索引不要包含选择性过低字段

由于过滤效果欠佳，即通过该字段只能筛选出少量数据，在这种情况下无需建立索引。具体而言，若该数据仅占较小比例，则即使不依赖索引直接从数据库中查找也不会需要进行大量遍历即可定位目标数据。因此无需通过索引来进行查询

SQL：

    select * from my_table where col_a=1 and col_b=1
    
    AI写代码

索引：
index (col_a,col_b)

我们定义col_b为逻辑删除字段，并将其状态分为0表示未被逻辑删除和1表示已被逻辑删除。实际上，在数据库中仅有少量记录处于被逻辑标记为已删除的状态。然而，在实际应用中，默认情况下我们只会关注那些未被标记为已删除的记录。因此，在这种情况下col_b字段完全无需存在于任何组合索引中

2.1.2 选择性高的字段前置或者单独建立索引

SQL：

    select * from my_table where col_a=1 and col_b=1 and col_c=1
    
    AI写代码

索引：
index(col_a,col_b,col_c)

基于优先级col_c > col_b > col_a的情况下，在不考虑业务相关因素的前提下，在构建组合索引时最好将组合索引的构造顺序设置为index(col\_c, col\_b, col\_a)

由于组合索引底层存储首先按照第一项排序，在第一项相同的情况下，则依据第二项进行排序。若将选择性较低的项目置于前序时会使得IO操作次数有所降低。例如，在应用第一个筛选标准后得到的数据量为1万条，在应用第二个筛选标准后则仅有数百条数据。若先处理第一项筛选标准，则需在原有基础上进一步过滤；而按第二项优先处理则可减少后续操作负担。

如果某字段col_c具有极强的筛选能力，在执行过滤操作后仅剩余极少甚至零条数据，则无需为该字段专门创建索引即可。

需要注意的是，在这一规定中特别强调了在不考虑业务本身需求的前提下，在某些情况下即使某个字段（如col_a）的筛选能力略胜于另一个字段（如col_b），但因为后者（col_b）在业务场景中的适用性更强，则优先采用后者会更加合适，并且能够减少创建索引的数量。

2.1.3 尽量使用覆盖索引

SQL：

    SELECT sum(col_c) FROM my_table where col_a=1 and col_b=1
    
    AI写代码

索引：
index(col_a,col_b)

当对col_a和col_b进行筛选后仍存在海量数据时，则建议为该组合字段（col_a、col_b、col_c）创建一个索引。否则MySQL会通过批量读取的方式从col_c中获取数据来进行求和运算。

2.1.4 小结

1、选择性低的字段不用建立索引。

2、具有唯一性或者高选择性的字段无需与其他字段建立组合索引。

3、除了业务需求上的考虑，尽量将选择性高地索引字段前置。

在筛选后数据规模仍然较大的情况下可以选择针对索引字段进行补充优化。

2.2 使用索引的正确姿势

除了SQL本身不具备相应的索引支持之外，在实际应用中还可能发生这样的情况：即虽然存在相关联的索引配置然而这些配置未能得到正确执行。下面将介绍一些常见的导致这种情况出现的情形供在实际开发过程中予以警惕。

需要注意的一点是，在涉及二分法进行数据过滤的情况下（即未采用二分法进行索引过滤），系统可能会出现两种不同的情况：一种是触发了失效时执行特定操作；另一种则是仍然显示使用了相应的索引结构。

2.2.1 最左匹配截断

SQL：

 select * from my_table where col_b=1  
    
  
    
 select * from my_table order by col_b
    
    
    
    
    AI写代码

索引:
index(col_a,col_b)

组合索引的匹配遵循从左到右的顺序；无论在过滤还是排序时都需要遵循此规则。若选择以col_b字段进行索引，则查询必须包含col_a字段。

当采用组合索引时，在排序字段中使用col_b只需确保其中位于前的所有字段均可纳入过滤条件或排序条件即可，并非必须将col_b设定为组合索引中的末尾位置

比如：

    select * from my_table order by col_a,col_b
    
    AI写代码

col_a和col_b都可以走索引。

2、如果col_b是作为过滤条件，则col_b前面的字段都应该在过滤条件中。
比如：

    select * from my_table where col_b=1 order by col_a
    
    AI写代码

col_a和col_b都无法执行索引操作。具体原因在于：首先，在组合索引的左侧位置有其他列的存在；其次，并未将该列包含在当前查询字段列表中。

2.2.2 隐式转换

字段类型：
col_a(VARCHAR)
col_b(DATETIME)

索引：
index1(col_a)
index2(col_b)

SQL：

 select * from my_table where col_a=1  
    
  
    
 select * from my_table where col_b=1603296000000
    
    
    
    
    AI写代码

由于字段类型与查询数据的值类型不符合作用而导致数据库索引功能无法正常运行

col_a是字符类型，使用了数字类型进行查询。

该字段属于datetime数据类型的字段

2.2.3 in + order by 导致排序失 效

索引：
index(col_a,col_b)

SQL：

    select * from my_table where col_a in (1,2) order by col_b
    
    AI写代码

解决方式：

若col_a的过滤性能较弱，在构建组合索引表时可以通过先安排col_b字段置于前面，并将其移至该组合索引表的后端位置以达到规避或减小回表操作的目的

如果col_a的过滤性较强，则过滤后剩余的数据量有限，在这种情况下无需调整当前索引位置即可完成操作。剩余的数据需通过filesort进行排序处理。

如果海量数据经过筛选后依然充足，则推荐采用其他存储方案如Elasticsearch

对于SQL来说，可将其修改为仅检索id（或其他已存在于索引中的字段），并依据该id来查找对应的数据。这不仅有助于提升SQL执行效率，同时也可能引导MySQL底层在执行filesort时采用更为高效的数据排序方法。

2.2.4 范围查询阻断组合索引

索引：
index(col_a,col_b)

SQL：

    select * from table where col_a >'2021-12-01' and col_b=10
    
    AI写代码

解决方式：
可以调整下索引顺序，col_a放在最后面。index(col_b,col_a)

2.2.5 后缀匹配不能走索引

索引:
index(col_a,col_b)

SQL：

    select * from table where col_a=1 and col_b like '%name%'
    
    AI写代码

该组SQL将导致索引失效。需要注意的是，在数据库设计中，默认情况下字段名不支持通配符查询功能。具体而言，在数据库中执行如"SELECT * FROM TBL WHERE name%"这样的前缀匹配操作时，默认是可以通过现有索引来实现的；然而对于类似"%name"这样的后缀模式查询，则无法直接利用现有的倒排索引树结构来进行高效的二分查找操作。因此，在实际应用中如果需要支持针对字段名末尾带有特定模式（如%n）的情况下的高效查询需求，则建议在数据库规模较大时考虑采用Elasticsearch等更为灵活的数据存储方案来解决这个问题

2.2.6 or查询导致失效

索引：
index(col_a,col_b)

SQL：

    select * from table where col_a=1 or col_b=''
    
    AI写代码

or查询可能导致Mysql索引失效，在这种情况下建议为col_a和col_b分别创建独立的Mysql索引，并通过Mysql的index merge功能实现数据优化。其本质是让系统同时根据两个字段各自的独立索引查找相关数据，并整合这些数据。

2.2.7 使用函数查询或运算

索引：
index(col_a,col_b)

SQL：

 select * from table where col_a=1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(col_b);  
    
  
    
 select * from table where col_a=1 and col_b+1=10
    
    
    
    
    AI写代码

2.2.8 不等于、不包含(只用到ICP)

索引：
index(col_a,col_b,col_c)

SQL：

 select * from table where col_a=1 and col_b not in (1,2)  
    
  
    
 select * from table where col_a=1 and col_b != 1
    
    
    
    
    AI写代码

2.2.9 选择性过低，直接走全表

选择性较低会引起索引失效。因为执行二级索引查询后仍需经历回表查询的开销，在这种情况下若仅能筛选出少量的数据，则综合比较而言，直接从数据表中获取信息比建立该索引更为高效。因此底层依据存储结构特点及存储引擎特性等因素来综合评估和决定是否建立该索引。

索引：

index(col_a)

SQL：

    select * from table where col_a>'2017-10-22'
    
    AI写代码

2.2.10 asc和desc混用

索引：
index(col_a,col_b,col_c)

SQL：

    select * from my_table where col_a=1 order by col_b desc,col_c asc
    
    AI写代码

desc 和asc混用时会导致索引失效，不建议混用。

2.2.11 小结

根据以上例子，总结几个索引失效的场景：

组合索引左匹配原则

发生隐式转换

组合索引，in + order by in会阻断排序用索引

范围查询会导致组合索引中断,涉及范围查询的那些字段应放置在组合索引的末尾

前模糊匹配导致索引失效

or查询，查询条件部分有索引，部分无索引，导致索引失效。

查询条件使用了函数运算、四则运算等。

使用了!=、not in

选择性过低

asc和desc混用

2.3 编写SQL的正确姿势

了解如何构建索引的同时，也学会了如何避免索引失效，在某些情况下，即使索引配置正确也会导致查询变慢，在这种情形下我们需要考虑优化我们的SQL查询语句

2.3.1 深分页

索引：

index(col_c)

SQL：

    select * from my_table where col_c=1 limit 1000,10
    
    AI写代码

为什么深分页会效率变差，首先我们要了解一下分页的原理。

MySQL limit未被引擎层传输，在服务层面执行原始数据筛选操作。当执行数据查询时, 首先由引擎层利用索引来筛选出一批符合条件的数据(索引筛选),随后再由服务层面完成进一步的非索引级别的筛选处理。

在引擎层进行过滤后会将获取到的数据暂时存储起来，在随后的操作中逐个提取这些数据，并在引擎层面完成必要的回表操作以传递完整的信息至服务层面。在此过程中，在服务层面会对这些提取到的数据进行查询条件的验证（此处采用非索引的方式）。若符合条件，则进一步检查所提取的数据范围是否符合limit限制；当所有符合条件且位于指定范围内后的数据全部处理完毕后才会返回结果。

因为进行深分页操作时会产生过多的无用回传数据（在处理前N条数据时，在实际应用中我们只需发送包含从第N-2到第N+2的数据），因此优化的方法应当是避免因深分页操作而导致的多余数据回传

解决方式：

 # 避免深分页

    
  
    
 select * from my_table where id>上次查询的数据id值 limit 100
    
  
    
  
    
  
    
  
    
 # 延迟关联 避免大量回表
    
  
    
 SELECT * FROM my_table t1,(select id from my_table where col_c=1 limit 1000,100) t2 where t1.id=t2.id
    
  
    
  
    
  
    
    
    
    
    AI写代码

防止深分页带来的性能问题：我们可以通过id过滤的方式，在每次操作时限制每批最多返回100条数据。这样做的好处是每次操作只需处理少量数据，并减少批量处理时的资源消耗。

建立索引策略： 当业务所需的数据量较小时，在设计数据库时可以通过确保所有涉及的SQL查询字段及其关联条件都位于相应的索引上，并无需进行表连接操作。

延迟式关联：通过采用延迟式关联操作，在构建数据库索引结构的基础上编写基于索引结构的数据查询SQL语句，并根据id字段反向获取详细信息。

2.3.2 order by id

索引：
index(col_a)

SQL：

    select * from table where col_a=1 and col_b=2 order by id
    
    AI写代码

MySQL INNODB二级索引其最终叶子节点均指向主键ID字段基于这一特点我们可以巧妙地将ID字段进行排序处理

在当前情境下，在col_b字段上执行了截断操作后，在执行SQL指令时发现该字段被截断并无法利用其字段id进行排序。由于主键索引通常具有较高的权重，在这种情况下可能导致MySQL未正确识别并选择该主键索引来完成排序任务。这将使得数据库系统在处理查询时不得不逐项扫描整个数据集以满足条件需求

鉴于无法确保id排序能够避免使用索引的情况下

三、慢查分析

在掌握了SQL优化的理论知识之后，在实际操作中如何验证编写的SQL是否正确地采用了适当的索引？这就需要学会分析其执行情况。

执行计划：我们可以利用explain关键字获取SQL的执行计划，并利用该执行计划来研究SQL的运行情况。

执行计划字段描述：

extra字段常见值：

补充说明：使用索引条件（Index Condition）表示该方法基于索引下推机制（Index Condition Pushdown）。其中部分字段因特定原因无法通过索引进行查询，则会在服务器层进行数据过滤（using where）。需要注意的是，ICP则是在存储引擎层执行数据过滤操作，而非在服务层。

using where 和 using index condition的区别：

为什么需要ICP机制：

索引:

index(col_a,col_b)

SQL：

    select * from my_table where col_a="value" and col_b like "%value%"
    
    AI写代码

当不使用索引条件时，在执行查询操作时系统将先执行索引查询以获取相关数据随后与目标列（col_b）进行比对。假设经过筛选后得到的数据量为1万条则经进一步筛选只剩下1千条有效数据这意味着会有近9千余下的9千条无需再次比对的情况。

在索引结构中包含了col_b字段这一关键点上，在实际应用中存在一定的限制。具体而言，在这种情况下虽然无法直接运用索引树的二分查找特性（这是因为采用了前模糊匹配策略），但可以通过遍历索引中的现有数据来实现相关的查询处理。通过引入ICP机制后，在这种情况下就可以基于现有的索引来进行过滤操作，并且这种过滤过程只会留下大约1000条有效的数据记录，从而有效降低了不必要的回表操作数量。

小结：在执行计划的过程中我们能够推断出SQL最终采用了哪些索引并了解这些索引的具体应用状态；进一步判断是否有必要进行优化

四、总结

我们应重视产品质量，在遇到问题时防患于未然而不要等到出现问题才采取补救措施。SQL优化应在数据库设计和开发初期就充分规划以规避可能出现的性能瓶颈问题。

做好SQL优化不仅需要掌握基本方法，并且十分实用。为了实现这一目标，正确地建立索引是关键步骤之一；必须确保其可使用性以避免潜在问题；最终要保证数据检索既高效又精确。