楔子
一个 SQL 语句写的好不好，评价的唯一标准就是它的时间，执行时间越短 SQL 写的就越好。所以 SQL 优化的目的，就是缩短 SQL 语句的执行时间。而基础以及日常的 SQL 优化方式就是设计好索引，让不太复杂的普通查询都用上索引。
但当表结构和我们写出来的 SQL 比较复杂时，比如包含各种关联和嵌套子查询，那么性能就容易变得很差。而且从表面上看，SQL 语句也用上了索引，但性能就是上不去。其原因就是，针对复杂表结构和大数据量的上百行复杂 SQL 的优化，并没有表面上那么简单，它需要我们要清楚这个复杂 SQL 是怎么执行的。
比如那么多的数据表，每个表都有一个聚簇索引，聚簇索引的叶子节点就是该表的真实数据。同时每个表还设计了一些二级索引，那么上百行的复杂 SQL 跑起来的时候到底是如何使用各个索引，如何读取数据的?
一个 SQL 语句（不管是简单还是复杂），在实际的 MySQL 底层，针对磁盘上的大量数据表、聚簇索引和二级索引，如何检索查询，如何筛选过滤，如何使用函数，如何进行排序，如何进行分组，到底怎么能把你想要的东西查出来，这个过程就是一个很重要的东西：执行计划。
也就是说，每次提交一个 SQL 给 MySQL 服务，它内核里的查询优化器，都会针对这个 SQL 语句的语义去生成一个执行计划。这个执行计划就代表了，它会怎么查各个表，用哪些索引，如何做排序和分组。而看懂执行计划，我们就学会一半的 SQL 优化了。
执行计划都包含了哪些内容
以下面的 SQL 语句为例：
-- id 为主键 SELECT * FROM t where id = 1; -- name 为二级索引 SELECT * FROM t where name = 'satori';
此时可以通过聚簇索引或者二级索引+回表的方式，拿到想要数据。而这种根据索引可以直接获取数据的过程，在执行计划里称之为 const，意思是性能超高，耗时是常量级别的。
因此以后你在执行计划里看到 const 的时候，就知道它是直接通过索引定位到数据，速度极快，这就是 const 的意思。
但是这里有一个要点，你的二级索引必须是唯一索引，才属于 const 级别。也就是说你必须建立 unique key 唯一索引，保证一个二级索引的每一个值都是唯一的，才会是 const。
那如果是一个普通的二级索引，不是唯一索引呢？其实这种查询速度也是很快的，它在执行计划里叫做 ref。还有复合索引，如果是从索引最左侧开始连续多个列都是等值比较，那么也是 ref。
但有一个例外，如果你用 name IS NULL 这种语法的话，即使 name 是主键或者唯一索引，还是只能走 ref 方式。但如果你是对一个二级索引做等值比较的同时，还出现了 IS NULL，类似于 where name=x or name IS NULL，那么此时在执行计划里就叫做ref_or_null。
总结：假设你以后在分析执行计划的时候看到了 const，就知道是通过主键或者唯一索引访问，速度超高。如果你看到了 ref，就是用了普通的索引，或者用主键/唯一索引时候还带了一个 IS NULL/IS NOT NULL。不管怎么说，只要你看到 const 或者 ref，那么恭喜你，说明起码这部分执行速度是很快的。
然后是范围查询，举个例子：
SELECT * FROM t WHERE age>=10 and age <=30
假设 age 是一个普通索引，此时就必然利用索引来进行范围筛选，一旦利用索引做了范围筛选，那么这种方式就是 range。
接下来再介绍一个比较特殊的访问方式：index，假设有一个复合索引 (x1, x2, x3)，现在有一个 SQL 语句：
SELECT x1, x2, x3 FROM t WHERE x2 = 'xx';
由于缺少了 x2，所以此时无法用到索引，无法从索引树的根节点开始二分查找。那么它会怎么做呢？我们看到这里选择的字段就是复合索引里面的几个字段，于是会直接遍历索引树的叶子节点，找到 x2 = 'xx' 的数据。
因为二级复合索引里面存放的是 (x1, x2, x3) 和主键的值，根据二级索引查找的时候，我们能直接将指定字段的值拿到。也就是说，如果 SELECT 查询的字段和建立索引的字段相同，那么可以避免回表。这种只要遍历二级索引就可以拿到想要的数据，而不需要回表的方式，就叫做 index 访问方式。
所以 index 的速度比 const、ref、range 就要差一些了，因为它是通过遍历二级索引树的叶子节点的方式执行的，肯定比基于索引树的二分查找要慢。但还是比全表扫描好一些的，因为全表扫描是在聚簇索引上进行的，而二级索引叶子节点就包含几个字段的值，比聚簇索引叶子节点小很多，所以速度更快。
而最后一种就是 all 了，也就是全表扫描，它是最次的一种。会扫描聚簇索引的所有叶子节点，也就是在表里一行一行地去扫描数据。如果一个表就几百条数据那还好，如果是有几万条，或者几十万，几百万数据，全表扫描基本就得跪了。
不同的 SQL 语句会用什么执行计划
对于一些简单的SQL语句，我们已经知道会用什么样的执行计划和访问方式了，也知道不同的访问方式是如何使用索引的。下面来继续介绍更多的 SQL 语句在写出来之后，会用什么样的执行计划。
假设下面一个 SQL 语句：
SELECT * FROM t WHERE x1=xx and x2>=xx;
这个 SQL 语句要查一个表，用了 x1 和 x2 两个字段， 此时有人可能会说了，要是对 x1 和 x2 建了一个复合索引，那是不是就直接可以通过索引去扫描了？答案是没错的，但如果是下面这种情况呢？
表里面已经存在了两个复合索引 (x1,x3) 和 (x2,x4)，显然这个 SQL 此时只能选择其中一个索引去用，那么会选择哪个呢？结论是 MySQL 负责生成执行计划的查询优化器，一般会选择扫描行数较少的那个索引。
比如说 x1=xx 在索引里只需做等值比较，扫描数据比较少，那么可能就会挑选 x1 去做一个索引树的查找，这在执行计划里就是一个 ref。而找到满足条件数据之后，接着做一个回表，回到聚簇索引里去查出每一条完整数据。接着加载到内存里，再根据每条数据的 x2 字段的值，对 x2>=xx 条件做一个筛选。
这就是面对两个字段都能用索引的时候如何选择，以及如何进行处理的方式。接着我们再来考虑另外一种情况：
SELECT * FROM t WHERE x1=xx and c1=xx and c2>=xx and c3 IS NOT NULL
其实我们平时经常会写出来类似这样的 SQL 语句，就是在筛选条件里，只有一个字段有索引（x1），其它字段都没有索引。这种情况其实是非常常见的，我们不可能对每个字段都建索引，这是不现实的。我们只能在所有的 SQL 语句里，抽取部分经常在 where 里用到的字段来设计两三个联合索引。
所以在这种情况下，必然有很多 SQL 语句，其 where 后的条件有好几个，但只有一个字段可以用到索引。对于当前而言，查询优化器生成的执行计划，仅仅会针对 x1 字段走一个 ref 访问，基于索引树快速查找到指定的一波数据。
当然此时拿到的仅仅是主键，然后还要回表，把每条数据完整的字段都查出来，然后加载到内存里去。接着针对这波数据的 c1、c2、c3 字段按照条件再进行筛选过滤，最后拿到的就是符合条件的数据了。
所以 x1 字段的索引设计，必然是要让 x1=xx 这个条件在索引树里查找出来的数据量尽可能少，才能保证后续的性能尽可能高。因为后续筛选就是普通的顺序遍历了，如果想让这个过程更快，那么只能在前面基于索引筛选的时候，过滤掉尽可能多的数据。
因此两个复合索引 (x1, x3) 和 (x2, x4)，基于 x1 筛选之后只有 300 条数据，基于 x2 筛选之后有 3w 条数据，那么 MySQL 肯定会选择 (x1, x3) 这个索引。
同时使用多个索引
上面的例子都是一个 SQL 语句只用一个二级索引，但在一些特殊的情况下，可能会对一个 SQL 语句用到多个二级索引。比如有这么一个 SQL：
SELECT * FROM t WHERE x1=xx and x2=xx
x1 和 x2 两个字段分别都有一个索引，所以也有可能会生成这样的执行计划：先对 x1 字段的索引树进行查找，查出一波数据，接着对 x2 的索引树查出一波数据，然后按照主键值对两波数据做一个交集。这个交集就是符合两个条件的数据了，接着回表到聚簇索引里面查完整数据就可以了。
但前面我们面对这种情况一直说的是，选择查 x1 或 x2 其中一个字段的索引，找出一波数据。接着回表到聚簇索引查完整数据，最后根据另外一个字段的值进行过滤就可以了。
那么到底什么情况下，会直接对两个字段的两个索引一起查，然后取交集再回表到聚簇索引呢？也就是什么情况下执行 SQL 的时候，一下子会查多个索引树呢？
其实很简单，就以上面的 SQL 语句为例，如果先查 x1 字段的索引，一下子弄出来上万条数据，这上万条数据都回表到聚簇索引里面去查完整数据，再根据 x2 来过滤，那么效果是不是不太好呢？
而如果同时从 x2 的索引树里也查一波数据出来，做一个交集，一下子就可以让数据量变成几十条，那么再回表查询速度就很快了。
一般来说，查询索引树的速度都比较快，但是到聚簇索引回表查询会慢一些。所以为了提升整体速度，最好在查询索引树的时候过滤掉尽可能多的数据。
所以如果同时查两个索引树取一个交集后，数据量很小，然后再回表到聚簇索引去查，就会提升性能。但是这需要很多的硬性要求，比如说如果有复合索引，你必须把复合索引里每个字段都放 SQL 里，而且必须都是等值匹配；或者通过主键查询+其它二级索引等值匹配，也有可能会做一个多索引查询和交集。
当然啦，如果是 x1 = xx or x2 = xx，也可能走多个索引，只不过此时查多个索引树之后，会取一个并集，而不是交集。
小总结
到现在为止我们已经知道写出来的 SQL 有哪些执行方式了，const、ref、 range 都是性能最好的方式，说明在底层直接基于某个索引树快速查找了数据。但有的时候可能你在用了索引之后，还会再回表到聚簇索引里查完整数据，接着根据其它条件来过滤。
然后 index 方式其实是扫描二级索引的意思，就是不通过索引树的根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，这种速度还是比较慢的，尽量还是别出现这种情况。
当然 index 方式怎么也比 all 好一些，all 就是直接全表扫描了，也就是直接扫描聚簇索引的叶子节点，那是相当的慢。index 虽然也是扫描叶子节点，但起码二级索引的叶子节点数据量比较小，所以相对 all 还是要快一些。
最后是一个 SQL 执行时可能用到多个索引，也就是对多个索引树进行查找，接着用 intersection交集、union并集的方式进行合并。如果你在执行计划里面看到这些字样，要知道是怎么回事。就是告诉你查找了多个索引，然后取了交集或并集，而且这种方式也不一定会发生。
多表关联的 SQL 语句是怎么执行的
如果仅仅是执行一下单表查询，那都是比较简单的，而且通常你把索引给建好了，让 SQL 语句执行时尽可能走索引，性能都不是什么大问题。
但我们基于 MySQL 做一些系统开发的时候，也会写一些多表关联语句，因为有时候想要查找需要的数据，必须借助多表关联的语法去编写 SQL 语句，才能实现你想要的逻辑和语义。但是使用多表关联的时候，你的 SQL性能往往会遇到一些问题。
那么下面就来看看，多表关联 SQL 语句到底是如何执行的，还是先举个例子：
SELECT * from t1, t2 WHERE t1.x1=xxx and t1.x2=t2.x2 and t2.x3=xxx
语句很简单，如果在 FROM 子句后面出现了两个表名，意思就是要针对两个表进行查询了，而且会把两个表的数据给关联起来。假设你要是没有限定多表连接条件，那么就会搞出一个笛卡尔积的东西。
比如 t1 表有 10 条数据，t2 表有 5 条数据，那么该语句就会查出来 50 条数据。因为 t1 表里的每条数据都会跟 t2 表里的每条数据连接起来，那么查出来的数据不正是 10 * 5 = 50 条吗？这就是笛卡尔积。
不过通常也不会写类似这样的 SQL 语句，因为查出来这种数据实在是没什么意义，所以通常都会在多表关联语句中的 WHERE 子句里引入一些关联条件。比如这里的：t1.x1=xxx and t1.x2=t2.x2 and t2.x3=xxx。
首先 t1.x1=xxx 可以明确，绝对不是多表关联的连接条件，而是针对 t1 表的数据筛选条件。本质就是先从 t1 表里筛选一波数据出来，然后再跟 t2 表做关联的意思。同理 t2.x3=xxx 也不是关联条件，而是针对 t2 表的筛选条件。
真正的关联条件是 t1.x2=t2.x2，这个条件意思是将 t1 表里 x2 字段的值和 t2 表里 x2 字段的值相等的数据关联在一起。比如 t1 表里有 1 条数据的 x2 字段的值是 123，然后 t2 表里有 2 条数据的 x2 字段的值也是 123，那么此时就会把 t1 表里的那条数据和 t2 表里的那两条数据分别关联起来，然后返回给你两条关联后的数据。
基本概念算是理解清楚了，具体到上面的 SQL，首先根据 t1.x1=xxx 这个筛选条件去 t1 表里查出来一批数据。此时可能是 const、ref，也可能是 index 或者 all，都有可能，具体看你的索引如何建的，它会挑一种执行计划访问方式。
然后假设从 t1 表里按照 t1.x1=xxx 条件筛选出一部分数据，接着对这部分数据，根据每条数据的 x2 字段的值，以及 t2.x3=xxx 这个条 件，去 t2 表里找 x2 字段值和 x3 字段值都匹配的数据。
比如说从 t1 表筛选出来的第一条数据的 x2 字段的值是 123，此时就根据 t2.x2=123 和 t2.x3=xxx 这两个条件，找出来一波数据，将两者关联起来。然后再以相同的方式处理第二条数据，直到 t1 表处理完毕，最终将结果集返回。
所以像这种，先从一张表里面查询一波数据，这张表叫驱动表，然后根据这波数据再去另一张表里进行关联，那么另一张表叫被驱动表。
如何根据成本选择执行计划
上面介绍了 MySQL 在执行单表查询时的一些执行计划，比如 const、ref、range、index、all 之类的；以及多表关联的时候是如何执行的，本质其实就是先查一个驱动表，接着根据连接条件去被驱动表里循环查询。
那么接下来我们再更深入一步，相信你或多或少已经感觉到了，无论是执行单表查询还是多表关联，似乎都有多种执行计划可以选择。比如有的表可以全表扫描，也可以用索引 A，也可以用索引 B，那么到底该用哪种执行计划呢?
毫无疑问，肯定会选择一个执行成本最低的执行计划，所以下面的问题就是 MySQL 是如何对一个查询语句的多个执行计划评估执行成本的？以及如何根据成本评估选择一个成本最低的执行计划，保证最佳的查询速度？
先了解一下 MySQL 里的成本是什么意思，简单来说，执行一个 SQL 语句，一般成本有两部分。首先数据如果在磁盘里，肯定要从磁盘里把数据读出来，这个从磁盘读数据到内存的过程就是 IO 成本。由于 MySQL 都是一页一页读的，因此将读一个数据页的成本的约定为 1.0。
在拿到数据之后，是不是要对数据做一些运算呢？比如验证是否符合搜索条件，或者做一些排序分组之类的事，这些都是耗费 CPU 资源的，属于 CPU 成本，一般约定读取和检测一条数据是否符合条件的成本是 0.2。
注意：这个所谓 1.0 和 0.2 就是 MySQL 自定义的一个成本值，代表的意思就是一个数据页的 IO 成本为 1.0，一条数据检测的 CPU 成本为 0.2，没什么神秘的。
然后假设我们要执行这样一条 SQL 语句：
SELECT * FROM t WHERE x1=xx and x2=xx
会先查看这个 SQL 可以用到哪几个索引，由于 x1 和 x2 都建立了索引，那么它们都能用到，因此它们俩的索引就是 possible keys。
接着会针对这个 SQL 计算一下全表扫描的成本，由于全表扫描需要把聚簇索引里的叶子节点上的数据页都读到内存里，有多少数据页就得耗费多少 IO 成本。接着对内存里的每一条数据都判断是否符合搜索条件，有多少条数据就要耗费多少 CPU 成本。
而具体耗费的成本，可以通过以下命令查看：
show table status like "表名"
该命令可以拿到指定表的统计信息，在对表进行增删改的时候，MySQL 也会自动维护。在返回的信息中，我们需要关注两个指标，分别是 rows 和 data_length。
rows 就是表里的记录数（对于 innodb 来说，rows 是估计值），data_length 就是表的聚簇索引的字节数大小，除以 (1024 * 16) 即可得到数据页的数量。根据记录总数和数据页的页数，就可以计算全表扫描的成本了。
- IO 成本：数据页数量 * 1.0 + 微调值；
- CPU 成本：记录数 * 0.2 + 微调值；
两者再相加即可计算出总的成本值，比如数据页 100 个，记录数有 2 万条，此时总成本值大致就是 100 + 4000 = 4100 左右。
索引的成本计算方法
了解完全表扫描的成本计算方法之后，再来看看索引的成本计算方法。因为除了全表扫描之外，还可能有多个索引可以使用，当然一般情况下，同时只能用一个索引，所以不同索引的使用成本都得计算一下。
使用索引访问数据的方式，大家应该都还记得，如果根据主键查，那直接走一个聚簇索引就 ok 了。如果是普通索引，那就两步走，先通过二级索引查询到主键，再根据主键去聚簇索引回表查询。
而这个过程的成本计算方法稍微有点特别，首先在二级索引里根据条件查数据的 IO 成本，一般是看你的查询条件涉及到几个范围。比如说 name 值在 25~100，250~350 两个区间，那么就是两个范围；而 name=xx 就仅仅是一个范围区间。
一般一个范围区间就粗暴地认为等同于一个数据页，所以根据二级索引查询的时候，这个 IO 成本都会预估的很小，可能就是 1 * 1.0 = 1，或者是 n * 1.0 = n，基本就是个位数这个级别。
但这仅仅是通过 IO 读取了二级索引的数据页而已，也就是二级索引读取的 IO 成本，将二级索引数据页到内存里以后，还得根据搜索条件去取数据，这个过程就是根据搜索条件在二级索引里搜索的过程。MySQL 会采用一种不怎么准确的算法，去估算根据查询条件可能会在二级索引里查出多少条数据来。比如估算的结果是会查到 100 条数据，那么从二级索引里查询数据的 CPU 成本就是 100 * 0.2 + 微调值，大概 20 左右。
在拿到 100 条数据之后，就得回表到聚簇索引里去查询完整数据，此时先估算回表到聚簇索引的 IO 成本。做法是直接默认 1 条数据就得回表到聚簇索引查询一个数据页，所以 100 条数据就是 100 个数据页的 IO 成本，也就是 100 * 1.0 + 微调值，大致是 100 左右。
因为在二级索引里搜索到的数据是 100 条，回表到聚簇索引之后，就可以拿到这 100 条数据的完整值了，然后判断这 100 条数据是否符合其他查询条件。这里耗费的 CPU 成本就是 100 * 0.2 + 微调值，大概 20 左右。
把上面的所有成本都加起来，就是 1 + 20 + 100 + 20 = 141，这就是使用一个索引进行查询的成本计算方法，其它二级索引也是同理。当然根据主键查询也是类似的，此时根据主键查询仅仅需要查询一个聚簇索引即可。
全表扫描的成本是 4100 左右，根据索引查找可能就 141，因此很多时候使用索引和全表扫描，两者的成本差距是非常之大的。所以会针对全表扫描和各个索引的成本，都进行估算，然后比较一下，选择一个成本最低的执行计划。
可能有人觉得这种成本计算方式不是太靠谱，但没办法，因为要在一个查询执行之前，就可以针对不同的访问方法精准计算出成本，是根本不现实的。最后只能是根据一些相对简单粗暴的办法，大致估算一下。
基于规则优化执行计划
下面来聊一聊下 MySQL 在执行一些相对复杂的 SQL 语句时，是如何对查询进行重写来优化具体的执行计划的。因为它有时候会觉得我们写的 SQL 一点都不好，生成的执行计划的效率不够高，需要自动帮我们改改。
比如 MySQL 要是觉得你的 SQL 里有很多括号，那么会把无关紧要的括号删除；其次像 i = 5 and j > i 这样的 SQL，会被改写为 i = 5 and j > 5，做一个常量替换。
还有比如 x = y and y = k and k = 3这样的SQL，都会给你优化成 x = 3 and y = 3 and k = 3，本质也是做个常量替换。或者是类似于什么 b = b and a = a 这种没意义的SQL，会直接给删掉。所以你会发现这些 SQL 的改写，本质上都是在优化 SQL 语句的清晰语义，方便后续在索引和数据页里进行查找。
还有一些是比较有意思的改写，比如下面的SQL语句：
select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1
这个 SQL 针对 t1 表的 id主键进行了查询，同时还要跟 t2 表进行关联。因为主键唯一，所以这个 SQL 语句可能在执行前就先查询 t1表 id=1 的数据，拿到 x1 字段值，假设值 'xx'，然后执行：
select t1表里id=1数据的常量值, t2.* from t1 join t2 on t2.x1 = 'xx'
除了 where 之外，还会把 t1 相关的字段都替换成，提前查出来的 id=1 的那行数据的字段常量值。
子查询的优化
下面看一下子查询是如何执行的，以及它的执行计划是如何优化的。先来看一个简单的子查询：
select * from t1 where x1 = ( select x1 from t2 where id=xxx)
这个查询会被拆分为两个步骤：第一个步骤先执行子查询，根据主键定位出 id=xxx 的 x1 字段的值。接着再以 x1=子查询的结果值作为条件，执行外层的 select，这个比较简单。
select * from t1 where x1 in (select x2 from t2 where x3=xxx)
这种 SQL 非常常见，子查询先查一波数据，然后判断 t1 表的哪些数据的 x1 值在结果集里。按照上面的结论，可能大家会认为 MySQL 会先执行子查询，然后再对 t1 进行全表扫描，判断每条数据是否在这个子查询的结果集里。这么想没有任何问题， 但 MySQL 却未必这么做，因为这种方式的效率是非常低下的。
对于上述的子查询，执行计划会被优化为：先执行子查询，把查出来的数据写入一个临时表里，也可以叫做物化表，意思就是把这个中间结果集进行物化。这个物化表可能会基于 memory 存储引擎来通过内存存放，如果结果集太大，则可能采用普通的 B+树聚簇索引的方式放在磁盘里。但无论如何，这个物化表都会建立索引。
接下来是不是全表扫描 t1 表，对每条数据的 x1 值都去物化表里根据索引快速查找一下，判断是否存在呢？其实可以反过来思考，假设 t1 表的数据量是 10 万条，而物化表的数据量只有 500 条，那么此时完全可以改成全表扫描物化表，然后判断 x1值在 t1 表中是否存在。
所以如果子查询得到的结果集比较少的话，MySQL 就会优化成上面这种方式。
小结
以上就是一些 SQL 优化原则，但在工作中还是建议大家尽量写简单 SQL，复杂逻辑用 Python、Java、Go 等开发语言实现即可。SQL 能单表查询就不要多表关联，能多表关联就尽量别写子查询，能写几十行 SQL 就别写几百行的 SQL，多考虑用代码在内存里实现一些数据的复杂计算逻辑，而不是都放 SQL 里做。
其实一般的系统，只要你 SQL 语句尽量简单，然后建好必要的索引，让每条 SQL 都可以走索引，数据库性能往往不是什么大问题。
本文参考自：
- 儒猿技术窝《MySQL 实战高手》
