执行计划（ Execution Plan ）是数据库管理系统（ DBMS ）在执行一个查询时生成的一种计划或路线图。这个计划描述了 DBMS 如何获取所需数据并执行查询，包括了操作的顺序、访问表的方式、索引的使用，以及连接操作等信息。执行计划是数据库查询优化器生成的，它的目标是使查询尽可能高效地执行，减少资源消耗和响应时间。
执行计划通常以树形结构的形式表示，被称为执行计划树。这颗树包含了一系列执行操作，每个操作都代表了一个步骤，从中获取或处理数据，然后将数据传递给下一个步骤。这些操作可以包括表扫描、索引扫描、连接操作、排序、过滤等等。
数据库查询优化器负责生成最优的执行计划，它会分析查询语句、表结构、索引的可用性等因素，以确定如何最有效地执行查询。生成的执行计划会被 DBMS 执行引擎用于实际执行查询操作。
在 MySQL 中，您可以使用 EXPLAIN 语句来生成和解释查询的执行计划。
在 MySQL 命令行或查询工具中，输入您要分析的查询语句，然后在查询语句之前加上 EXPLAIN 关键字。例如：
EXPLAIN SELECT column1, column2 FROM your_table WHERE condition;
# 例：
EXPLAIN SELECT * FROM user_info WHERE id > 1;
执行上述 EXPLAIN 语句。MySQL 将返回一个描述查询执行计划的表格，其中包括有关查询的各种信息，如操作顺序、表、索引、连接类型等。
以下是执行计划中常见字段的含义：
这些字段提供了有关查询执行计划的详细信息，可以帮忙我们了解查询是如何执行的，是否使用了索引，连接类型是什么，以及是否存在潜在的性能瓶颈。通过分析这些字段，我们可以更好地优化查询和数据库性能。
我们在后面的内容中，会频繁使用 EXPLAIN 语句，用于验证索引是否被正确使用。
在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
我们在上篇文章 解密 MySQL：索引—优化数据库性能的关键（一） 中，我们已经了解了索引的底层数据结构是一颗 B+树。
由于构建一棵 B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。
举例：创建一个（ a,b ）的联合索引，那么它的索引树就是下图的样子。
可以看到 a 的值是有顺序的，1 ，1 ，2 ，2 ，3 ，3 ，而 b 的值是没有顺序的 1 ，2 ，1 ，4 ，1 ，2 。
但是我们又可发现 a 在等值的情况下，b 值又是按顺序排列的，但是这种顺序是相对的。
这是因为 MySQL 创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。
所以 b=2 这种查询条件没有办法利用索引。
场景：user_info 表有一个（ a ，b ）的索引，下面的 sql 语句是否会走到这个索引：
# sql1
SELECT * FROM user_info WHERE a = '张三' AND b = '三年级';
# sql2
SELECT * FROM user_info WHERE b = '三年级' AND a = '张三';
# sql3
SELECT * FROM user_info WHERE b = '三年级' OR a = '张三';
# sql4
SELECT * FROM user_info WHERE a = '张三' AND b LIKE '三%';
# sql5
SELECT * FROM user_info WHERE a = '张三';
# sql6
SELECT * FROM user_info WHERE b = '三年级';
# sql7 ，这里假设 b 字段，为出生日期
SELECT * FROM user_info WHERE a = '张三' AND YEAR(b) = 2000;
数据库优化器能够重新排序条件，使其与索引相匹配。因此，也会走索引。创建联合索引的时候应该注意的问题：
创建联合索引还是多个单列索引：
前缀索引是一种特殊的索引，它只针对索引列的前若干个字符创建索引，而不是整个列。通常用于处理文本类型的数据列。
MySQL 数据库支持在字符串类型的列上创建前缀索引，语法如下:
# 这里 length 表示创建索引的前缀长度。
CREATE INDEX index_name ON table_name (column_name(length));
# 例子
CREATE INDEX idx_title_prefix ON article (title(10));
前缀索引的特点如下:
根据上面的特点，我们可以发现前缀索引更适用于查找变化较小的文本列，如名称、标题等静态信息。需要注意避免前缀值重复太多导致过滤效果不明显。总之，前缀索引是处理文本字段的重要手段，可以有效缩减索引体积，提升文本查找速度。
在 MySQL 中，回表（ Index Lookups ）是指在使用非聚簇索引进行查询时，MySQL 需要根据该索引的键值去聚簇索引中查找对应的数据行的过程。
举例来说：
假设有一个包含学号（主键索引）、姓名和年龄的表，并在姓名字段上创建了非聚簇索引。当使用姓名索引进行查询时，MySQL 会首先在该索引中定位到满足条件的记录的主键值，然后再根据这些主键值去聚簇索引（通常是主键索引）中查找对应的数据行。这个额外的查找聚簇索引的过程就是回表。
在 解密 MySQL：索引—优化数据库性能的关键（一） 文章中，我们了解到在 InnoDB 存储引擎中，非聚簇索引的 data 域存储相应记录主键的值而不是地址。所以导致在利用非聚簇索引进行查询时，需要进行回表。
回表操作可能导致额外的 IO 开销，影响查询性能，特别是当查询的列不包含在非聚簇索引中时，具体造成的影响如下所示：
为了优化查询性能，一般可以使用覆盖索引和索引条件下推来避免回表操作，提高查询效率。
覆盖索引是指一个索引包含了查询所需的所有列，从而数据库可以直接使用索引返回查询结果，而不需要再去访问实际的数据行。
举例：如果有一个包含列 A 和列 B 的表，并且创建了 (A, B) 的联合索引，如果某个查询仅仅需要使用列 A ，并且 A 已经包含在了索引中，那么查询就会利用到覆盖索引，直接从索引中获取所需的数据，而无需再访问实际数据行。
覆盖索引的优势：
覆盖索引在以下情况下会被使用到：
假设有一个 user_info 表，其中包括一个联合索引 (a, b) 和一个主键索引 (a)。下面的 sql 语句是否会使用覆盖索引：
# sql1—仅需主键列的查询
SELECT a FROM user_info WHERE a = 100;
# sql2—范围查询涉及联合索引的第一个列
SELECT a FROM user_info WHERE a > 100 AND a < 200;
# sql3—基于联合索引的查询，只选择包含在索引中的列
SELECT a, b FROM user_info WHERE a = 100;
# sql4—按联合索引的第一个列进行排序
SELECT * FROM user_info ORDER BY a;
# sql5—使用聚合函数，仅涉及索引列
SELECT COUNT(a) FROM user_info WHERE a > 50;
# sql6—覆盖索引列满足查询条件
SELECT a, b FROM table_name WHERE a = 100 AND b = 'other_value';
# sql7—查询需要不在索引中的列
SELECT a, c FROM table_name WHERE a = 100;
# sql8—or
SELECT a, b FROM table_name WHERE a = 100 OR b = 'other_value';
使用覆盖索引的要点总结:
索引条件下推（ Index Condition Pushdown ，ICP ）是一种数据库查询优化技术，其主要作用是在查询执行过程中利用索引预先过滤数据，减少读取的数据量。
通过 解密 MySQL：一条 SQL 语句的执行过程 文章，我们可以很容易的想到，索引条件下推是在 sql 执行的优化阶段进行的。但是想弄明白索引条件下推是什么，我们还需要了解一下，MySQL 的组成，具体如下图所示：
从上图我们可以看出 MySQL 从上至下分为以下几层：
MySQL 服务层负责 SQL 语法解析、触发器、视图、内置函数、binlog 、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。“索引下推”的“下”其实就是指将部分上层（服务层）负责的事情，交给了下层（存储引擎）去处理。
其大致执行流程如下:
这样可以避免全表扫描，锁定需要读取的数据范围，有效利用索引提升查询效率。
优点总结如下:
有一张 products 表，如下所示：
CREATE TABLE products (
id int(10) primary key,
name varchar(100),
price int(10),
cate varchar(50),
sold int(10)
);
CREATE INDEX idx_name ON products(name);
CREATE INDEX idx_cate_sold ON products(cate, sold);
假设我们需要执行下面这条 sql：
select * from products
where name like 'iphone%'
and cate = 'phone'
and sold > 10000;
不使用 ICP 的情况，执行计划如下所示：
通过执行计划，我们可以发现 MySQL 进行了全表扫描 all rows 和 Where 条件过滤。
使用 ICP 的情况，执行计划如下所示：
通过执行计划，我们可以发现 MySQL 利用索引 idx_name 和 idx_cate_sold 分别过滤，记录数降低，效率提高了。
所以正确使用索引条件下推可以显著优化查询计划，减少无效记录访问。
MySQL 中支持索引条件下推(ICP)的版本和存储引擎如下:
版本方面：
所以建议使用 MySQL 5.6 及以上版本,可以利用 ICP 带来的查询性能提升。
存储引擎方面：
InnoDB 是 MySQL 的默认存储引擎，因此大多数情况下，只要使用 MySQL 5.6+ 版本与 InnoDB 引擎组合，就可以直接使用 ICP 。MyISAM 不支持 ICP 也是其逐步被 InnoDB 取代的一个重要原因之一。
在 MySQL （ 5.6 及更高版本，且使用 InnoDB 存储引擎） 中的 MRR 指的是 Multi-Range Read ，即多范围读取。MRR 是一种优化查询的技术，它可以在读取多个索引范围时减少磁盘 I/O 和 CPU 消耗。
通常情况下，在执行查询时，MySQL 会遍历整个索引树，以找到所有匹配的行。但是，对于大型数据集，这种方式可能会导致性能下降，因为它需要大量的磁盘 I/O 和 CPU 资源。
MRR 通过将索引分成多个范围并在内存中缓存结果来避免这种情况。在使用 MRR 时，MySQL 会尝试将查询范围分成多个不重叠的部分，并使用范围扫描技术来查找每个部分中的匹配行。这种方式可以有效地减少磁盘 I/O 和 CPU 消耗，从而提高查询性能。
简单说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。需要注意的是，MRR 适用于某些类型的查询，例如使用等于、大于、小于等操作符的查询。对于使用 LIKE 操作符的模糊查询，则不适合使用 MRR 。
通过参数 optimizer_switch 的标记来控制是否使用 MRR 。
当设置 mrr=on 时，表示启用 MRR 优化。mrr_cost_based 表示是否通过 cost base 基于成本的方式来启用 MRR 。
如果选择 mrr=on,mrr_cost_based=off 则表示总是开启 MRR 优化，参数 read_rnd_buffer_size 用来控制键值缓冲区的大小。
默认情况下：mrr=on,mrr_cost_based=on
在不使用 MRR 时，优化器需要根据二级索引返回的记录来进行回表，这个过程一般会有较多的随机 I/O 。
使用 MRR 时，SQL 语句的执行过程是这样的：
通过上述过程，优化器将二级索引随机的 I/O 进行排序，转化为主键的有序排列，从而实现了随机 I/O 到顺序 I/O 的转化，提升性能。
MySQL 5.5 版本前，MySQL 本身只支持一种表间关联方式，就是嵌套循环（ Nested-Loop ）。如果关联表的数据量很大，则 join 关联的执行时间会非常长。
在 5.5 版本中，MySQL 通过引入 BNL （ Block Nested-Loop Join ）算法来优化嵌套执行。BNL 将外层循环的行/结果集存入到 join buffer ，然后每次遍历被驱动表都与 join buffer 中的数据进行比较，以此来减少全表扫描的次数。
例如，下面这个语句：
select *
from t1 straight_join t2
on t1.a = t2.b;
假设字段 b 上是没有建立索引的。这时候，被驱动表上没有可用的索引，流程如下所示：
虽然 BNL 算法是全表扫描，但是是在内存中进行的判断操作，速度上会快很多。
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k 。如果放不下表 t1 的所有数据话，则会进行分批处理，流程如下所示：
虽然分成多次放入 join_buffer ，但是判断等值条件的次数还是不变的。
MySQL 在 5.6 版本开始增加了提高表 join 性能的算法：Batched Key Access (BKA)，即批量索引访问。
BKA 其实就等价于 MRR + INLJ （ Index Nested-Loops Join ，基于索引的嵌套循环联接）。
使用 BKA 的表的 JOIN 过程如下：
可以看出，BKA 将有序主建投递到存储引擎是通过 MRR 的接口的调用来实现的，所以 BKA 依赖 MRR 。
BNL 和 BKA 都是批量的提交一部分行给被 join 的表，从而减少访问的次数，那么它们有什么区别呢？
原文链接：解密 MySQL：索引—优化数据库性能的关键（二）
微信公众号：啊杰在拱趴
求关注！！！！
这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。
V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。
V2EX is a community of developers, designers and creative people.