SQL优化

进行SQL优化之前，我们首先要确定是具体的某条SQL差。一般来说实际开发之中，无非就是两种情况需要对SQL做优化。

1. 开发的时候根据业务需求要做SQL优化

2. 除了实际开发的时候，往往还会有一些SQL会在项目正常运行一定时间之后，才会出现性能差的问题。
   针对这种情况。我们可以通过以下几种方式来帮助我们查看过去性能较差的SQL有哪些。

   • Mysql的性能模式 performance_schema中的EVENTS_STATEMENTS_SUMMARY_BY_DIGEST / statements_with_runtimes_in_95th_percentile
   • SQLServer内置的一些存储过程
   • Oracle的AWR报告

   关于上文的内容，在之前的一篇笔记之中我有过大概的介绍 数据库资源卡顿查询SQL - LeticiaFENG Note

EXPLAIN

确定了具体性能差的SQL之后，我们可以开始尝试优化了。毫无疑问的，第一步我们可以通过EXPLAIN查看具体SQL的执行计划，特别关注join、where、groupby、havingby等动作的具体执行计划内容。EXPLAIN其实无非主要观察四个filed的结果。

1. key：表示当前执行步骤使用到的索引，他可以是空的

2. type：指代当前执行步骤的类型。一般来说包含有以下几种可能：

   • system：系统表格查询，且只有一条数据才可能出现，属于const的特殊情况

   • const：表示通过聚簇索引或者是唯一索引，直接匹配到唯一的一条数据，并且返回。常见于 where id = N

   • eq_ref：唯一索引关联，表示通过聚簇索引或者唯一索引充当表关联时。例如 A.Id = B.aId 的时候。

   • ref：非索引关联，表示非唯一索引字段充当表关联时。跟eq_ref非常相似

   • fullttext：全文索引查询，指代通过全文索引快速锁定到行。

     (全文索引性能差其实是差在需要进行排序的时候，以及他创建索引时的资源损耗相比前缀索引要大得多，当然维护起来也是，但实际上他的查询效率还是比较快的，其实本质上Mysql的全文索引也是倒排索引，他跟ElasticSearch的根本差距其实只在于ES是专门做的数据分析引擎，通过分布式切片的方式来拆分查询、大量使用内存提高查询效率，并做针对性的优化来实现的高性能，而不是单纯因为ES用倒排索引这么简单，而Mysql的全文索引支持比较简陋底层通过辅助表 + 数组的方式来实现的，这自然不可能比得过ES。
     更具体的信息，可以参考官方介绍 MySQL :: MySQL 8.4 Reference Manual :: 17.6.2.4 InnoDB Full-Text Indexes）
     但在当前的市场环境中，大部分人也不会在意这一点，甚至大量的面试题其实只会让人记住一句“针对字符型数据构建索引的时候，不要使用全文索引，他的性能差”

   • ref_or_null：索引值 + null匹配，使用索引做查询的时候，其中包含 null 的判断，又包含普通的查询时 (必须是同一个字段)。例如：

     -- nickname 存在二级索引
     EXPLAIN SELECT * FROM users 
     WHERE nickname = 'John' 
        OR nickname IS NULL;
     

   • index_merge：索引融合，指代某个查询步骤可以通过两条或者两条以上的索引同时进行优化查询的情况。此时Mysql查询执行器会尝试将其组合起来一起来进行查询优化。

   • unique_subquery：唯一索引子查询，我们熟知当 in 集合是一个较大的范围的时候，如果将查询从 in 替换为exist 将会大大优化查询性能。而mysql-Innodb查询优化器也会实现类似的自动优化处理。一旦出现将唯一索引字段的IN集合查询自动优化为exist时，就会出现unique_subquery。

   • index_subquery：索引子查询，跟unique_subquery类似，唯一不同在于index_subquery是针对非唯一索引做in替换为exist的优化

   • range：范围查询，是使用索引进行优化查询的时候，如果查询条件涉及到一定的范围查询的，并且范围不会过大*(当查询的范围过大 / 排序字段超过索引覆盖范围，会退化为index)*那么就会出现range

   • index：全索引查询，针对索引进行查询，但是不需要做回表查询的情况。常见于range范围查询过大，导致优化退化，变成index级别全索引查询。

   • all：全表查询，常见于没有使用索引或者是索引失效又或者是匹配的数据字段过大最终导致index优化最终降级为all。

3. rows：表示当前执行动作涉及到的行数 (不是最终结果行数，而是需要扫描、检测的行数)

4. extra：当前查询动作的额外信息，一般来说有以下可能

   • using index：使用覆盖索引优化
   • using where：where查询过滤，并且查询的字段未被优化索引覆盖到
   • using temporary：mysql为了做处理的时候，无法直接处理完毕，而是通过构建临时表装载临时结果，再进行其他的处理
   • using filesort：mysql无法直接通过索引排序直接将结果排序出来，而是通过外部的文件排序的方式来实现排序效果
   • backward index scan：Mysql8.0之后的优化查询方式，反向扫描索引结构。
     在8.0之前，针对DESC的排序，Mysql要先通过索引进行排序，然后再filesort颠倒结果，提供了新的反向扫描的功能之后，
   • select tables optimized away：使用聚合函数的时候，就会出现该额外提示

一般来说，使用EXPLAIN之后，最为常见的情况是没有构建索引或者索引失效，以下针对这两点做一些简单的介绍。

构建索引

众所周知，实际上我们的构建的索引本质上是一种较为特殊的数据结构，他通过这种较为特殊的结构来提高我们查询的效率。而在Mysql-Innodb之中索引的结构基本都是B+树结构存在的，这使得每次的新数据的插入其实都需要索引结构做一定程度的同步新增。这也说明了，构建索引是需要付出一定代价的，因此所有构建的索引应当都保证收益大于这个代价。

构建索引时应当注意以下事项

1. 不应该允许索引字段存在NULL值

2. 具有高辨识度

3. 索引字段长度应当尽量简短

4. 对于字符类型应当尽量考虑前缀索引 (通过SQL手段确认多少长度为高辨识度)

5. 尽量跟主键组成组合索引

6. 构建索引的字段改动频率应当不高

7. 控制单个表的索引不要超过十五个

   ······

构造索引的模板

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

更加详细的模板介绍可以参考官方文档：MySQL :: MySQL 8.4 Reference Manual :: 15.1.15 CREATE INDEX Statement

一个表可以拥有多少个索引？

A table can contain a maximum of 64 secondary indexes. 官方文档指出最多能有64个二级索引

[MySQL :: MySQL 8.4 Reference Manual :: 17.21 InnoDB Limits](https://dev.mysql.com/doc/refman/8.4/en/innodb-limits.html#:~:text=A table can contain a maximum of 64 secondary indexes,REDUNDANT or COMPACT row format.)

索引失效

常见索引失效的情况

这个部分比较散乱，而且暂时我也想不到更为合适的分类总结的方式，先将就看看吧

• 使用 != / <> / NOT LIKE等不等值匹配，精准匹配必然会变为大范围匹配，大概率索引会直接失效，最好也是index

• 使用IS NULL、IS NOT NULL对字段进行控制判断，容易会导致索引失效

• 字符类型，使用LIKE左模糊，前缀索引直接失效

• 在组合索引之中，不符合最左匹配原则，不按照组合索引顺序进行查询
  最左匹配原则还导致了一些问题，只要顺序不一致就无法匹配，为了保证匹配往往还需要构建不同顺序相同字段的索引，多少是有点离谱
  (8.0 中Mysql通过支持Skip Scan Range Access Method，使得我们可以不再完全遵守最左匹配原则，但是需要注意的是，该技术其实是将我们的单次查询，改为多个的范围查询再将，然后再做查询，显然性能肯定不如我们直接最左匹配，不按照顺序越多，范围查询的次数也就越多)

• 在组合索引之中，使用 OR 或者在中间字段使用范围查询

• 针对索引字段使用函数转换

• 针对索引字段做隐式类型转换

• IN集合条件大量元素会导致索引时效

• ORDER BY字段不包含在条件查询字段 / JOIN字段之中，这种情况下Mysql无法通过索引来优化排序，extra大概率会有using filesort

• 在大数据量表格之中为拼接条件，使用 1 = 1 前缀，有可能会导致索引失效

• 构建索引的字段应当尽量避免存在NULL值

• 构建索引的字段应当保持高辨识度 (随着项目时间推移可能会出现该问题)

  ······

优化器导致索引失效的情况

为了方便后续理解，这里需要先大概介绍一下两个内容，当然这里只做一些简单的介绍，不会展开说明，个人认为是比较基础的东西，如有需要以后再补充说明。

前置知识

Mysql SQL执行顺序

from -> join -> where -> group by -> having -> select -> distinct -> order by -> limit
这个比较常规这里就不细说了，大概补充一下，方面后续扩展介绍下面的内容。

Innodb引擎SQL执行流程

一次标准的SQL交给Innodb的执行和处理包含以下的流程：

1. 连接器[Connection Manager] (维护此次SqlSession)
2. 解析器[Parser] (对SQL做语法分析、解析)
3. 预处理器[Preprocessor] (检查SQL表、列，做权限检查)
4. 优化器[Optimizer] (生成执行计划、选择索引、对SQL做优化)
5. 执行器[Executor] (执行SQL、调用存储过程)
6. 存储引擎[Storage Engine] (做数据的存储和读取)

多匹配索引选取不合理

当针对某个表做条件过滤 where + 排序 order by 的时候，如果存在两个索引，索引1(whereField1, whereField2, orderbyField)，索引2(orderField, whereField1, whereField2)。在我们人的视角来看，显然是先做where条件过滤进一步减少排序数据量，然后再做排序几乎是理所当然的想法。但是这针对Mysql的索引优化执行器来说，却是有难度的事情。

实际背景

实际上Mysql的优化器有相当大的概率会用索引2，曾经我就遇到过类似的情况，400W+的表格根据索引1几乎只需要1s左右的时间，就能将结果查出来。
但Mysql使用索引2了，结果花了10s+，key是索引2、type是index、extra是using where。从这个结果来看也非常合理，因为使用索引2先做了排序，根据Mysql sql执行顺序，order by是在where之后的，所以实际上并没有使用索引来优化查询，这导致了extra必然不可能是using index只能是using where，相当于变为了较大的范围查询，查询的字段又没有超过索引字段，所以type自然就是index。

解决

显然的其实就是由于Mysql-Innodb的优化器不够智能导致的结果，我们可以考虑通过使用hint语句来显式的让Mysql优先使用索引1来进行查询。也就是在 where 前面添加 force_index 索引1。

可能会有人觉得我这里介绍不够详细，但我不太想为此特地建一个表来复刻场景提供详细信息，特地写这个案例场景问题

关联表主驱动不做索引优化

我们知道常见的表 inner join 的关联方式有两种，第一种是更容易理解简明的在两个关系表间使用 join (inner、left、right)；第二种则是直接将所有相关表格写在 from 之中，然后再在where之中写所有关联表格的关联条件。(虽然这么说可能有点不太友好，但第二种关联方式其实算是隐式join，算是较为老派的SQL编写方式，在复杂的SQL编写之中很容易造成结构复杂难以维护、Mysql等不够完美的引擎对条件处理、执行策略做优化时造成影响等等问题，相当不推荐这种编写方式，虽然这与本文无关)

实际背景

有两个表格，他们的关系非常接近于一对一，但是本质上是一对多 (多的情况很少，大部分时候是一对一。 类似于toC端的订单和包裹间的关系) 。两个表的模型上，可以简单的将其抽象为A表 (一对N中的一)、B表 (一对N中的N)。
原先的SQL是from了这两个表格，在where之中关联并做条件查询，最后再做order by。原本因为数据量并不大没有出现任何问题，但随着时间推进，两个表格的数据不断增加，两个表格的数据量都接近了100w，相差在1w以内。慢慢的就发现原先的SQL性能变得很差，在实际优化的时候，首先查看了两个表的索引发现都有对应用于与对方表格关联的索引。
但在EXPLAIN之中，却发现两者的关联工作根本没有用到索引做优化，key根本没有、type为ALL、extra更是“集百家之所短”，using where + using temporary + using filesort全都有，其实也很好理解，因为根本没有用到索引做优化，所以Mysql先将 A、B 两个表关联起来存储到临时表之中 (using temporay的来源)，然后再做条件过滤 (using where的来源)和排序(using filesort的来源)。

解决

毫无疑问，其实这个问题也是Mysql-Innodb查询优化器不够智能导致的问题。为了解释这个问题，我们需要稍微深入一点介绍一下MySQL的表关联机制，在实际的关联处理中，Mysql会根据NLJ、BNL算法做笛卡尔积的关联结果计算，而笛卡尔积的计算我们都知道，外层次数少实际上性能更高。而在实际实现之中，这个外层次数其实就是关联表之中的，类似于left join和right join，实际上等同于直接指定左边表和右边表作为驱动表。
但在inner join之中，由于没有明确指定驱动表，这使得这里先说结论，目前根据经验总结来看，Mysql针对inner join实际上会优先根据两个表的大小和数据量来优先决定驱动表。

但，在我上面遇到的优化问题之中，两个表的大小是相近的数据量也是接近的，这导致了Mysql-Innodb查询优化器最终放弃使用索引做优化。本质上其实就是无法明确驱动表是谁导致的。解决方案其实也是通过hint语句解决的，也有两种解决方案：

1. force_index：指定我们的目标驱动表的索引，让查询优化器优先使用该索引做关联操作。
2. straight_join：该hint语句的实际意思几乎等同于inner join，但他会额外指定左表为驱动表。

资料补充

• Mysql官方对关联机制算法实现的介绍 (不懂关联机制的可以大概看看)

  MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.7 Nested-Loop Join Algorithms

• inner join、left join、right join的关联机制 & 处理
  其中对Inner join的介绍中，明确的指出了对于INNER JOIN来说，Innodb处理的时候对于表的处理顺序是灵活的、优化器可以选择任意顺序、不受位置限制。

The preceding examples demonstrate these points:

• For join expressions involving only inner joins (and not outer joins), parentheses can be removed and joins evaluated left to right. In fact, tables can be evaluated in any order.
• The same is not true, in general, for outer joins or for outer joins mixed with inner joins. Removal of parentheses may change the result.

MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.8 Nested Join Optimization

• 只靠官方文档很难说知道他具体处理措施和逻辑，在Oracle的社区，我们查找驱动表的信息可以找到一篇这样的Questions ，该文直接指出，实际上针对 inner join 的场景会根据表的大小、列值的值等统计因素来让优化器决定使用什么索引。

  The 'driving' table is the table we will join FROM -- that is JOIN TO other tables. For example, lets say you have the query:

  select * from emp, dept where emp.deptno = dept.deptno;

  In this case the driving table might be DEPT, we would fetch rows from DEPT in a full scan and then find the rows in EMP that match. DEPT is the driving table.

  The choice of a driving table made using many factors. For example, in the above query if there was an index on DEPT(DEPTNO) but not EMP(DEPTNO), we would probably use EMP as the driving table -- we would fetch rows from EMP and then using the DEPT(DEPTNO) index -- find their matches in the DEPT table. Table sizes, cardinality of column values, and other things can affect the choice of a driving table (eg: HINTS).

  When using the Rule Based Optimizer -- the placement of tables in the FROM clause is relevant. We process the from clause from the RIGHT to the LEFT -- we would tend to pick a driving table from the end of the FROM list. There is a hint in the Cost Based Optimizer to have this happen as well.

  When using CBO -- the order of tables is not relevant (unless you hint it to be). We use the statistics and data dictionary to determine which table is best to be used as the driving table.

  What is a driving table - Ask TOM

• 到这里为止，所有的理论直接证据结束了，剩下的是经验总结的结果。 在国内一个开源社区的文章之中介绍过 对 inner join 索引选用的结论。我尝试对他的经验做来源分析，但是没有找到任何直接证据，更像是由社区推文和经验做出的总结。如有出入后续再做修改

outer 表、驱动表的选择

对于 left join、right join 来说，其语义已经固定了 outer 表的选择，没啥讨论空间（除非 where 子句中打破了其语义）。对于 inner join，outer 表的选择是由优化器说了算的，举例：
select * from t1 join t2 on t1.a=t2.a;

a. 如果 t1.a、t2.a 都有索引，且基数高，则效率最高的算法是 Nested-Loop，由于有索引，通常我们会改称其为 Index Nested-Loop，则会选择小表作为 outer 表，这样循环的次数会更少；

b. 如果t1.a、t2.a 都没有索引，基于成本的考虑，则优化器会选择 BNL 算法或者 hash join，由于 outer 表要放入 join buffer 中，而这块内存的大小是根据 join_buffer_size 参数指定的，容量有限，所以还是会选择小表作为 outer 表；

c. 当然也不一定都是选择小表作为 outer 表，如果 t1 表有 10 万行数据，t1.a 有索引；而 t2 表有 20 万行数据，t2.a 没有索引。则优化器很可能选择 t2 表作为 outer 表，因为 Index Nested-Loop 算法肯定比 BNL 算法成本更低，也可能比 hash join 算法成本低。

例子比较简单，实际情况会更复杂，比如 SQL 中多半还会有 where 子句，这时候小表的定义就不是t1、t2的整表大小了，而是 t1、t2 应用完 where 子句后的数据大小，本篇不做过多讨论。

技术分享 | 咬文嚼字之驱动表 & outer表

SQL优化的极限

从对SQL调整来说，实际上我们的优化始终都是有限的，当数据量变大超过一定数量级的时候，由于Mysql直接基于磁盘IO的方式做查询，性能必然会变差，比较常规的认识是超过2kw的时候会导致B+树层级增加，导致IO次数增加降低性能 (当然这只是一种假定条件下的计算，如要精确需要具体表格具体计算。简单验证原文：Mysql-Innodb单表超过2kw逻辑验证)。除此以外，在面对大数据量范围搜索时索引辨识度过低导致索引失效也会出现SQL优化无法提升性能的情况。

谈及这些内容，就不得不提到一般来说针对数据库的两个标准的数据仓库的要求，OLAP和OLTP。

OLAP (Online Analytical Processing)

对业务数据执行多维分析,并提供复杂计算、趋势分析和复杂数据建模的能力。 最简单的理解就是针对大规模数据有一存储和数据的分析能力。

OLTP (Online Transaction Processing)

在线的事务处理能力，简单的来说就是ACID的标准事务机制。当然展开到数据仓库该内容会复杂很多。但我对数据仓库理解并不深刻。暂时也无意向数据仓库学习，所以先大概记住OLTP就是事务支持能力即可。

如果我们只从Mysql来出发，那么显然的Mysql只能算支持OLTP，而针对大数据的存储方面，我们都知道Mysql因为分区表形同虚设的支持能力导致它在面对大规模数据时，只能通过逻辑&物理上的双重意义的分表来实现拆分，而查询和分析能力则完全依赖于磁盘IO的查询能力，也就是说Mysql没有OLAP的任何特征。也因此，在针对大规模的数据的OLAP级别的要求时。常见的解决方案是直接引入ElasticSearch。并在实际业务之中通过双写的方式来实现Mysql - ElasticSearch的一致性。针对ElasticSearch的基本使用和底层原理的基本介绍已经在当前笔记网站记录过相关笔记，有需要可以前往ElasticSearch标签下查看。

(双写：通过TCC分布式事务 / 本地事务做的同步双写、通过线程 / MQ做的异步双写(引入MQ重试机制)、通过canal / FlinkCDC监听binlog做数据同步，如有需要以后补充，该内容不属于当前SQL优化主题的内容)