调优 - 😄MySQL 执行计划 - 《MySQL》

前置知识：
执行计划有哪些方式？
- const
- ref
- range
- index
- all
根据成本选择执行计划

前置知识：

[1] MySQL索引原理

在 MySQL 底层，针对磁盘上的大量的数据表、聚簇索引、二级索引和联合索引，如何检索索引，怎么样筛选过滤，怎么使用函数，怎么进行排序，怎么分组，怎么样将需要的数据查出来。这个过程，就是执行计划。

执行计划有哪些方式？

不同执行效率的执行计划，被划分出一些方式。有以下方式：

const

常量级别，执行效率最高的执行计划方式。可以直接使用聚簇索引或者使用二级索引+聚簇索引回表轻松获取到数据，但是这个二级索引必须是唯一索引，就是说这个二级索引的字段，必须是每行数据唯一的。

例如”SELECT * FROM user WHERE id = 100”，这种就是直接使用聚簇索引就可以快速获取到目标数据。

“SELECT * FROM user WHERE stu_no = ‘u_1’ “，这个例子里面，必须是使用 stu_no 字段建立唯一索引，然后根据’u_1’这个唯一的值去二级索引里面快速获取到这行数据对应的id，然后使用id进行回表操作获取到数据。

ref

可以完全使用二级索引或者聚合索引来查找数据，但是这个二级索引不是唯一索引，这个方式称为 ref 。如果是联合索引，必须是按照索引匹配原则，完全走索引查询，获取数据才是 ref 方式。如果对一个字段使用了 IS NULL 的语法的话，即使这个字段是主键或唯一索引，但是一样是 ref 方式。如果针对一个二级索引同时比较了一个值，还有使用了 IS NULL 语法的话，那么这个在执行计划里面就成为 ref_of_null 。

例如”SELECT FROM user WHERE name=xxx “ ，name 字段作为二级索引，但是不是唯一索引，这种就是 ref 方式。 “SELECT FROM user WHERE name=xx AND age=xx “，同时有索引 KEY(name,age)的话，这时候也是 ref 方式。

“SELECT * FORM user WHERRE name=xx OR name IS NULL “。同时使用了索引和 IS NULL 语法，这时候，执行计划就是 ref_or_null 方式了。

range

使用索引并且是范围查找的时候，这时候的执行计划的方式就是 range。

例如”SELECT * FROM user WHERE age >10 AND age <20”，并且使用了 age 字段建立了一个普通的二级索引。这时候执行计划的方式就是 range。

以上3种方式，都是使用索引进行查询。速度都是算比较快的。因为都是使用了索引，用了二分查找。

index

这一种是比较慢的执行计划。遍历二级索引的所有叶子结点，就可以拿到需要的数据，不需要进行回表操作。这样的方式成为 index 。就算是不需要回表，但是也由于是使用了遍历叶子结点的方式，而不是使用索引进行二分查找的方式，所以效率还是比较差的。

例如 “SELECT id, name, age FROM user WHERE no =xx”。索引为 key（name，age）。这个语句没有使用上索引查询，因为 where 子句没用上索引。但是只需要读取到 id，name，age 这3个字段，这3个字段在二级索引的子节点里面全部包含了，所以只需要遍历二级索引的所有叶子结点即可。虽然慢，但是也比遍历所有聚簇索引的叶子结点要快一些，毕竟读取的字段少了一些。

all

这就是效率最差的全表扫描方式。需要遍历聚簇索引的所有叶子结点来获取数据。

根据成本选择执行计划

执行一个 SQL 语句的时候，是可以有多种执行计划的，选择不同的索引，进行不同的获取数据方式等等。这时候，SQL 优化器会核算这些执行计划的成本，然后选择最低成本的执行计划去执行。

一般成本分成两大部分，IO 成本和 CPU 成本。如果数据不在内存里面，需要从磁盘加载数据页进入到内存，这个过程就是 IO 成本，每加载 1 页数据页到内存的 IO 成本定位为 1.0。加载数据到内存里面后，需要对内存进行筛选和对比，这时候是使用 CPU 进行的操作，这个就是 CPU 成本，每筛选和对比1条数据的成本定位为 0.2。

例如全表扫描的成本计算：表里面一共有 10 页数据页，这些页数据页一共有 1000 条数据，那么加载这个执行计划的成本是：IO成本 10 1.0 + CPU成本 1000 0.2 = 10 + 200 = 210。

实际上 IO 成本和 CPU 成本还会有一些微调值，计算出来的数值加上微调值，就等于最后的估算成本了，即：IO成本 + IO微调值 + CPU成本 + CPU微调值 = 执行计划成本。

如果使用的是索引进行查找的话，那么会增加索引查找数据的成本，但是对于数据页的成本也会大大降低。整个过程是：

在索引里面进行估算 IO 索引页的成本，一般认为查询条件的一个查询范围=1个数据页的成本，例如”age > 10 and age < 20 “ 这样的话就是2个数据页的成本，如果是” age = 10” 那么就是1个数据页的成本，现在假设是2个数据页的 IO 成本，所以这里的索引页 IO 成本是 2 * 1.0 = 2 。
经过复杂的估算，可能估算出来在索引里面获取到 20 条数据，那么这里的 CPU 成本就是，20 * 0.2 = 4。
目前只是在索引页里面提取出数据，还需要进行回表查询。进行回表获取数据页的时候，首先估算 IO成本，一般认为，需要查询多少条数据就有多少个数据页，也就是说，从索引获取到 100 条数据后，每条数据代表一个数据页 IO 的话，IO 成本就是 20 * 1.0 = 20。
从聚簇索引加载了数据页，还需要根据条件筛选出数据(这些条件可能在索引里面没有包含，例如 age = 10 and name= ‘XX’ , 这里 age 建立了索引，但是 name没有索引) 。这时候筛选数据的CPU成本是 20 * 0.2 = 4。
最后，计算各个部分的成本: 索引 IO 成本 2 + 索引数据 CPU 成本 4 + 数据页IO成本 20 + 数据的 CPU成本 4 = 30。经过索引计算的成本才 30，如果是全表扫描，数据的成本是 210，索引大大提高了效率。数据越多，差距就越明显。

无论单表还是多表关联查询都会使用这样的一套估算成本的方法，估算全表扫描的成本，估算使用各个索引查询的成本，然后选择成本最低的计划来执行。