实战讲解MySQL执行计划，面试官当场要了我

JavaEdge在掘金
1,567 阅读32分钟

本文已收录在Github，关注我，紧跟本系列专栏文章，咱们下篇再续！

  • 🚀 魔都架构师 | 全网30W技术追随者
  • 🔧 大厂分布式系统/数据中台实战专家
  • 🏆 主导交易系统百万级流量调优 & 车联网平台架构
  • 🧠 AIGC应用开发先行者 | 区块链落地实践者
  • 🌍 以技术驱动创新，我们的征途是改变世界！
  • 👉 实战干货：编程严选网

0 执行计划和SQL性能优化

SQL优化不就是让SQL都用上索引？对，但并不完全对。应该说，根据查询语句设计良好的索引，让所有查询都尽可能用上索引，这本就是一种SQL优化技巧，但仅是其一。SQL优化技巧包含但不限于索引的良好设计。有时DB有很多表，每个表数据量也不少，写出的SQL也复杂，充斥各种关联和嵌套子查询，表面上看起来这个SQL部分用上了索引，最后性能还是极差，why？

所以最简单的SQL优化就是设计好索引，让一般不太复杂的普通查询都用上索引，但针对复杂表结构和大数据量的上百行复杂SQL优化，就建立在你先懂这个复杂SQL怎么执行的。

那么多数据表，每个表都有个聚簇索引，聚簇索引的叶子就是那个表的真实数据，同时每个表还设计了一些二级索引，那么上百行的复杂SQL跑起来的时候到底是如何使用各个索引，如何读取数据的？

这SQL语句（不管easy or hard），在MySQL底层，针对磁盘上的大量数据表、聚簇索引和二级索引，如何检索查询、筛选过滤、使用函数、排序、分组，到底怎么查出来的这个过程就是一个很重要的东西：执行计划！

即每次你提交一个SQL给MySQL，他内核里的查询优化器，都会针对该SQL语句的语义去生成一个执行计划，这个执行计划就代表了，他会怎么查各个表，用哪些索引，如何做排序和分组，看懂这个执行计划，你就学会了真正的SQL优化的一半。

看懂执行计划后，还能根据它去想各种办法改写SQL，改良你的索引，进而优化SQL的执行计划，最终让SQL性能提升，这就是SQL调优。

explain或者desc获取MySQL如何执行select语句的信息。

explain 
  SELECT *
  FROM emp,
  		 dept
  where emp.deptno = dept.deptno
  and emp.deptno = 10;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEdeptconstPRIMARYPRIMARY4const1100
2SIMPLEemprefFK_DEPTNOFK_DEPTNO5const3100

结果包含很多列

1 各列字段说明

1.1 id

SELECT标识符。查询中SELECT的序列号，表示查询中执行select子句或操作表的顺序。

如果该行引用其他行的并集结果，则该值可为NULL。

id分为三种情况:

1 id相同

执行顺序从上到下

explain se1ect * from emp e join dept d on e.deptno = d.deptno 
	join salgrade sg on e.sa1 between sg.1osal and sg.hisal;

2 id不同

如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

explain select * from emp e where e.deptno in 
	(select d.deptno from dept d where d.dname = 'SALES');

3 id相同和不同，都存在

相同的可认为是一组，从上往下顺序执行。

所有组中，id值越大， 越先执行

explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sa1
between sg.1osal and sg.hisal where e. deptno in (select d.deptno from dept d where
d.dname = 'SALES');
select_ type

1.2 select_type

区分SELECT类型，普通查询 or 联合查询 or 子查询：

  • simple（简单表，不用表连接或子查询）
  • primary（主查询，外部查询）
  • union（union中的第二个或后面的查询语句）
  • subquery（子查询中的第一个select）

1.3 table

输出结果集。对应行正在访问哪个表，表名或别名，可能是临时表或union合并结果集。

  1. 具体表名，则表明从实际物理表中获取数据，也可是别名
  2. 表名是derivedN，表示使用了id为N的查询产生的衍生表
  3. 当有union result时，表名是union n1,n2等的形式，n1,n2表示参与union的id

1.4 type

描述如何连接表，表示MySQL在表中找到目标行的方式或访问类型。 常见类型：all、index、range、ref、eq_ref、const、system、null，性能由差到好。

一般保证查询至少达到range，最好达到ref。

1.4.1 ALL

最简单暴力的全表扫描，MySQL遍历全表找到匹配行，效率最差。对来自先前表的行的每个组合进行全表扫描。

若该表是未标记为const的第一个表，则通常不好，并且在所有其他情况下通常性能也非常糟糕。

一般通过增加索引避免ALL，这些索引允许基于早期表中的常量值或列值从表中检索行。

explain select * from film where rating > 9;

1.4.2 index

连接类型与ALL相同，除了扫描索引树外，这发生于：

  1. 若索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树 这时，Extra列显示Using index。仅索引扫描通常比ALL更快，因索引大小通常小于表数据

  2. 使用对索引的读取执行全表扫描，以按索引顺序查找数据行

    Extra列不显示 Using index。

当查询仅使用属于单个索引一部分的列时，MySQL可以使用此连接类型。

explain select title from film;

1.4.3 range

使用索引查询行，仅查询给定范围内的行：

  • key列指示使用的哪个索引
  • key_len包含使用的最长的key部分

此类型的ref=NULL：

EXPLAIN
SELECT
    *
FROM
    person
WHERE
    name like 'name123%' limit 100;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEpersonRangeperson_name_score_indexperson_name_score_index1022NULL111100.00Using index condition

当用如下操作符：

=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()

将key列与常量进行比较时，可使用range。索引范围扫描，常见<,<=,>,>=,between

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

range，SQL里有范围查询，如

select * from table 
	where age>=x 
		and age <=x

假设age是普通索引，就利用索引来进行范围筛选，那就是range。

  • 若你在执行计划看到const、ref和range，代表啥？

    都是基于索引在查询，所以问题不大，除非你通过索引查出来的数据量太多了，比如上面范围筛选一下子查出10万条。

这不就是通过索引来获取数据么，从索引根节点开始一通二分查找，不停的往下层索引跳转，就可以了，速度超快，感觉上跟ref或者range是一回事。那你就大错特错了！

某表里完整的字段联合索引【x1，x2，x3】，写个SQL：

select x1,x2,x3 from table where x2=xxx

x2不是联合索引的最左侧那个字段，所以该SQL无法直接从联合索引树的根节点二分查找，那他会怎么执行？注意该SQL里要查字段正好都是联合索引里的字段。

这种SQL实际查询时，会直接遍历【x1，x2，x3】联合索引树的叶节点。

  • 聚簇索引的叶节点存放完整的数据页，每个数据页里包含完整的一行行数据
  • 联合索引的叶节点存放也是数据页，但页里每行就只有x1、x2、x3和主键的值

所以该SQL会直接遍历KEY(x1,x2,x3)索引树的叶节点的那些页，一个个遍历，找到

x2=xxx

的那个数据，就把里面的x1，x2，x3三个字段值直接提取出。

这遍历二级索引的过程比遍历聚簇索引快，因为二级索引叶节点就包含几个字段的值，比聚簇索引叶子节点小多了。即此时只要遍历一个(x1,x2,x3)索引，无需回表到聚簇索引。

这种只要遍历二级索引就能拿到你想要的数据，而不需要回表的访问方式，就是index。

const、ref和range都是基于索引树的二分查找和多层跳转来查询，所以性能一般很高。index速度就比这三种差一些，因为他遍历二级索引树的叶节点，那肯定比基于索引树的二分查找要慢，但也比全表扫描好一些。

1.4.4 index_subquery

此连接类型类似于unique_subquery。它代替了IN子查询，但适用于以下形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

1.4.5 unique_subquery

此类型将eq_ref替换为以下形式的某些IN子查询：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery只是一个索引查找函数，可以完全替换子查询以提高效率。

1.4.6 index_merge

此联接类型指示使用索引合并优化。在这种情况下，输出行中的键列包含使用的索引列表，而key_len包含使用的索引的最长键部分的列表。

1.4.7 ref_or_null

这种连接类型类似于ref，但是MySQL会额外搜索包含NULL值的行。此联接类型优化最常用于解析子查询。在以下示例中，MySQL可以使用ref_or_null连接来处理ref_table：

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

1.4.8 fulltext

使用FULLTEXT索引执行连接。

1.4.9 ref

通过普通二级索引列与常量进行等值匹配时来查询某表，则对该表的访问方法可能就是ref。

对于先前表中的每个行组合，将从该表中读取具有匹配索引值的所有行。 若查询请求：

  • 仅使用K的最左前缀

  • 或该K不是主键或唯一索引（即若连接无法根据key值选择单一的行记录）

则会使用ref，若使用的K仅匹配几行，则这是一种很好的联接类型。

ref可使用

= or <=>

运算符比较索引列，如下MySQL可以使用ref联接来处理ref_table：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

1.4.10 eq_ref

对于先前表中的每行组合，从此表中读取一行。除了system和const类型，这是最好的连接类型。 当连接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时，将使用它。

类似ref，区别在于所用索引是唯一索引，对于每个索引键值，表中有一条记录匹配； 简单来说就是多表连接使用primary key或者unique index作为关联条件。

eq_ref可用于使用=运算符进行比较的索引列。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式。在以下示例中，MySQL可以使用eq_ref连接来处理ref_table：

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

1.4.11 const

表最多有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可将该行中的字段值视为常量。 const很快，因为它们仅读取一次。

当将主键或唯一索引的所有部分与常量值进行比较时，将使用const。

比如在以下查询中，tbl_name可用作const表：

SELECT * FROM tbl_name 
	WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 
  	AND primary_key_part2=2;

select * from table where id=x
或
select * from table where name=x

通过：

  • 聚簇索引

  • 或二级索引+聚簇索引回表

就能查到你要的数据，这种根据索引直接查到数据的过程，在执行计划里称为const，即性能超高的常量级。

注意，你的二级索引必须是唯一索引，才属于const。

1.4.12 system

该表只有一行（系统表）。这是const 连接类型的特例。

type null，MySQL不用访问数据库直接得到结果。

1.5 possible_keys

此次查询中可能选用的索引。对一个表进行查询时，潜在可以使用的索引。

比如你有俩索引：

  • (x1, x2, x3)
  • (x1, x2, x4)

要是在where里根据x1和x2两个字段进行查询，显然两个索引都能使用，那到底用哪个？ 这就得通过成本优化估算使用两个索引进行查询的成本，使用哪个索引成本更低，就选择那个索引，实际选择的索引就是执行计划结果里key列的值。

1.6 key

此次查询中确切使用到的索引

1.7 key_len

当你在key里选择使用某索引后，那个索引里的最大值的长度，这就是给你个参考，大概知道那个索引里的值最大有多长。

key_len表示使用的索引长度，key_len可以衡量索引的好坏，key_len越小 索引效果越好，那么key_len的长度是如何计算的？

常见的列类型长度计算：

列类型是否为空长度key_len备注
tinyint允许NULL1key_len = 1+1允许NULL, key_len长度加1
tinyint不允许1key_len = 1不允许NULL
int允许NULL4key_len = 4+1允许NULL, key_len长度加1
int not null不允许4key_len = 4不允许NULL
bigint允许NULL8key_len = 8+1允许NULL, key_len长度加1
bigint not null不允许8key_len = 8不允许NULL
char(1)允许NULLutf8mb4=4,utf8=3,gbk=2key_len = 1*3 + 1允许NULL, 字符集utf8, key_len长度加1
char(1) not null不允许NULLutf8mb4=4,utf8=3,gbk=2key_len = 1*3不允许NULL, 字符集utf8
varchar(10)允许NULLutf8mb4=4,utf8=3,gbk=2key_len = 10*3 + 2 + 1动态列类型, key_len长度加2, 允许NULL, key_len长度加1
varchar(10) not null不允许NULLutf8mb4=4,utf8=3,gbk=2key_len = 10*3 + 2动态列类型, key_len长度加2
CREATE TABLE `user` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`),
  KEY `idx_sex` (`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

id字段类型为bigint，长度为8，id为主键，不允许Null ，key_len = 8 。

EXPLAIN select * FROM user WHERE id = 1;

name的字段类型是varchar(10)，允许Null，字符编码是utf8，一个字符占用3个字节，varchar为动态类型，key长度加2，key_len = 10 * 3 + 2 + 1 = 33 。

EXPLAIN select * FROM user WHERE name = '张三';

age的字段类型是int，长度为4，允许Null ，key_len = 4 + 1 = 5 。

EXPLAIN select * FROM user WHERE age = 19;

sex的字段类型是char(1)，允许Null ，字符编码是utf8，一个字符占用3个字节，key_len = 1 * 3 + 1 = 4 。

EXPLAIN select * FROM user WHERE sex = '1';

备注：

VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其key_len还需要再加 2 bytes

联合索引key_len计算

我们删除user表其他辅助索引，建立一个联合索引

ALTER TABLE user DROP INDEX idx_name, DROP INDEX idx_age, DROP INDEX idx_sex;

ALTER TABLE user ADD INDEX idx_name_age(name, age); 1、部分索引生效的情况

我们使用name进行查询

EXPLAIN select * FROM user WHERE name = '张三';

由于联合索引，根据最左匹配原则，使用到索引只有name这一列，name的字段类型是varchar(10)，允许Null，字符编码是utf8，一个字符占用3个字节，varchar为动态类型，key长度加2，key_len = 10 * 3 + 2 + 1 = 33 。

2、联合索引完全使用索引的情况

EXPLAIN select * FROM user WHERE name = '张三' AND age = 19;

由于联合索引，使用到（name，age）联合索引，name的字段类型是varchar(10)，允许Null，字符编码是utf8，一个字符占用3个字节，varchar为动态类型，key长度加2，key_len = 10 * 3 + 2 + 1 = 33 ，age的字段类型是int，长度为4，允许Null ，key_len = 4 + 1 = 5 。联合索引的key_len 为 key_len = 33 + 5 = 38。

可根据key_len判断联合索引是否生效。

1.8 ref

当你的查询方式是索引等值匹配时，如const、ref、eq_ref、ref_or_null，ref列描述的就是你和索引列等值匹配的是：

  • 等值匹配一个常量值
  • 或是等值匹配另外一个字段的值

比如：

EXPLAIN SELECT * FROM t1 WHERE x1 = 'xxx'

执行计划，针对t1表的查询：

  • type=ref，基于普通的二级索引的等值匹配
  • possible_keys只有index_x1，针对x1字段建立的一个索引
  • 而实际使用的索引也是index_x1，毕竟就他一个索引能用
  • key_len=589，index_x1这个索引里的x1字段最长589字节，可见x1字段是存储字符串的，因为是个不规律长度
  • ref字段，既然你是针对某二级索引进行等值匹配，那和index_x1索引进行等值匹配的是什么？一个常量或其它字段？这里的ref的值是const，即是使用一个常量值跟index_x1索引里的值进行等值匹配

假设你要是：

EXPLAIN SELECT * 
FROM t1
	INNER JOIN t2
		ON t1.id = t2.id;

此时执行计划里的ref肯定不是const，因为你跟t1.id字段等值匹配的是另外一个表的id字段，此时ref值就是那个字段名称。

看执行计划，对t1驱动表执行全表扫描，接着针对t1的每条数据都会去t2表根据其主键执行等值匹配，所以第二个执行计划的type=eq_ref，被驱动表基于主键进行等值匹配，且使用的索引是PRIMARY，就是使用了t2表的主键。

ref就是说到底是谁和t2聚簇索引里主键值进行等值匹配呢？是常量值吗？

不是，是test_db库的t1表的id字段，这里跟t2表的主键进行等值匹配的是t1.id字段。

哪个字段或常数与 key 一起被使用。

若为普通二级索引

# name是普通二级索引，不是唯一索引
select * from table where name=x

则此时这种查询速度也很快的，在执行计划是ref。

若是包含多个列的普通索引，那必须从索引最左侧开始连续多个列都是等值比较才属于ref，类似

select * from table where name=x and age=x and xx=xx

索引可能是【name，age，xx】。

还有个例外，若你用如下这种语法

name is NULL

即使name是主键或唯一索引，还是只能走ref。但若针对一个二级索引同时比较了一个值还限定了is NULL，类似

select * from table where name=x or name IS NULL

此时在执行计划叫：ref_or_null，就是在二级索引查你要的值及NULL值，然后再回表聚簇索引里查，因为同时有：

  • 索引等值比较
  • NULL值查询

所以就叫ref_or_null。

1.9 rows

所使用的查询方式，会查出多少条数据

1.10 filtered

在查询方式查出来的这波数据里，再用上其它不在索引范围里的查询条件，又会过滤出来百分之几的数据。

如SQL：

EXPLAIN SELECT * FROM t1 WHERE x1 > 'xxx' AND x2 = 'xxx'

只有x1字段建了索引，x2无索引，此时执行计划：

针对t1表的查询方式是range，即基于索引进行范围查询，用的索引是index_x1，即x1索引，然后基于如下条件：

x1>'xxx'

通过index_x1索引查出数据约1987条，接着对这1987条数据再基于where里的其他条件，即x2='xxx'进行过滤。

  • filtered=13.00，即估算基于x2='xxx'条件过滤后的数据大概13%，即最终查出来的数据1987 * 13% = 258条

小结

假设你以后在分析执行计划的时候看到const，肯定是通过主键或唯一索引的访问，速度超高。

看到ref，就是：

  • 用了普通索引
  • 或用主键/唯一索引搞了一个IS NULL/IS NOT NULL

只要看到const或者ref，至少说明这部分执行速度很快。

1.8 rows

该查询一共扫描了多少行。 这是一个估计值，不准确。

1.9 filtered

此查询条件所过滤的数据的百分比

1.10 extra

除了extra字段的内容，最多告诉你每个表咋查询的，用啥索引，查出多少数据，但很多时候，针对一个表可不简单。

除了基于索引查询数据，可能还得基于where里的其他条件去筛选数据，此时还会筛选出来一些数据。如：

EXPLAIN SELECT x1
					FROM t1
					WHERE x1 = 'xxx'

访问了t1表

  • 使用ref访问方法：基于二级索引index_x1查找，索引最大数据456字节，查找目标是个const代表的常量值，通过索引可查出25条数据，经过其他条件筛选过后，最终剩下数据是100%

  • extra=Using index：该查询仅涉及一个二级索引，无需回表，因为他仅是查x1字段，直接从index_x1索引查就

若无回表，仅二级索引里执行，extra就是Using index。

using filesort

性能消耗大，需额外的一次查询（排序）。可检查MySQL是否可用索引来解析ORDER BY子句：

  • Extra列不包含Using filesort，则使用索引，并且不执行文件排序
  • Extra列包含Using filesort，则不使用索引，而是执行全文件排序
explain select * from emp order by sal;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEempALL14100Using filesort

EXPLAIN不区分优化器是否在内存中执行文件排序。在优化程序trace输出中可以看到内存文件排序的使用。查找filesort_priority_queue_optimization即可。

对于单索引，若

  • 排序和查找是同一字段，则不会出现该方式
  • 不是，则会出现

对于联合索引，不能跨列（最左匹配原则）。

有时在SQL里排序时，若排序字段有索引，直接可从索引里按排序顺序去查找数据：

EXPLAIN
SELECT *
FROM user
ORDER BY name
LIMIT 1;

排序后再分页的语句。

idselect_typetabletypekeykey_lenrefrowsfilteredExtra
1SIMPLEuserindexname1941100

用了index方式访问，即直接扫描了二级索引，实际使用的索引也是name，它就是在name索引里按序找你LIMIT 1要求的1条数据。

所以大家看到返回数据是1条，也没别的过滤条件，所以filtered=100%，即1条数据都返回了。但若排序时没法用到索引，此时就会基于内存或磁盘文件来排序，大部分时候都得基于磁盘文件来排序，比如：

EXPLAIN
SELECT *
FROM user
ORDER BY password
LIMIT 1;

x2字段是没有索引

idselect_typetabletypekeykey_lenrefrowsfilteredExtra
1SIMPLEuserALL3100Using filesort

基于password字段来排序，无法直接根据有序的索引查数据，只能将所有数据写入一个临时的磁盘文件，基于排序算法在磁盘文件里按password字段值完成排序，再按LIMIT 1要求取出来头条数据。

这种把表全数据放磁盘文件排序的做法性能极差。

若用group by、union、distinct之类的语法，万一你没法直接利用索引进行分组聚合，那他会直接基于临时表来完成，也会有大量磁盘操作，性能也极低，比如：

EXPLAIN
SELECT password, COUNT(*) AS amount
FROM user
GROUP BY password
idselect_typetabletypekeykey_lenrefrowsfilteredExtra
1SIMPLEuserALL3100Using temporary; Using filesort

没有索引，该SQL只能对全表数据放到临时表里做大量的磁盘文件操作，然后才能完成对password字段的不同的值去分组，分组完后，对不同password值的分组去做聚合操作，这过程相当耗时，性能极低。

SQL调优核心就是分析执行计划里哪些地方出现全表扫描或扫描数据过大，尽可能通过合理优化索引保证执行计划每个步骤都能基于索引执行，避免扫描过多数据。

using temporary

  • 建立临时表保存中间结果，查询完成之后把临时表删除。

using index（性能提升）

表示当前的查询是覆盖索引，直接从索引中读取数据，而无需访问原数据表（回表）。即需要使用到的列，全部都在索引中。

  • 若同时出现using where，则索引被用来执行索引键值的查找
  • 若没有，则索引被用来读取数据，而不是真的查找

using index condition

MySQL 5.6新特性（Index Condition Pushdown）。

先条件过滤索引，之后找到所有符合索引条件的数据行，随后用 WHERE 子句的其它条件过滤这些数据行。

EXPLAIN
SELECT
    *
FROM
    person
WHERE score > 45678
    and name like 'NAME45%'
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEpersonrangeperson_name_score_indexperson_name_score_index1026NULL111033.33Using index condition

若有SQL：

SELECT * FROM t1 WHERE x1 > 'xxx' AND x1 LIKE '%xxx'

此时他会先在二级索引x1找，查询结果会额外的和

x1 LIKE '%xxx'

条件比对，若满足条件才会被筛选，这时extra=Using index condition。

using where（需回表）

使用where进行条件过滤。

若age为索引：

select age,name from ... where age = 18

当你：

  • 直接对一个表扫描，又没用到索引，然后where子句很多条件
  • 或你用了索引去查找，但除了索引外，还需要用其它字段进行筛选

如：

EXPLAIN
SELECT *
FROM user
WHERE password = '666';

password无索引：

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEuserALL250Using where

对t1进行查询，全表扫描，无任何索引，扫出2条数据。

extra=Using where，对每条数据都用了WHERE password = '666'筛选。

最终filtered过滤出50%数据，即从该表里筛选出来1条数据。

若where有个条件针对索引列查询，有个列是普通列筛选：

EXPLAIN
SELECT *
FROM user
WHERE name = 'JavaEdge'
  AND password = '666'

针对user表去查询，先通过ref方式直接在name索引里查，是跟const代表的常量值去查找，然后查出250条数据；

接着再Using where，使用AND password = '666'条件进行筛选，筛选后的数据比例是18%，最终所以查出来的数据大概应该是45条。

多表关联时，有时你的关联条件并不是索引，就会用 join buffer 内存技术来提升关联的性能：

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.x2 = t2.x2

连接条件x2无索引：

这执行计划很有意思，因为要执行join，肯定得先查询t1，对t1表直接 全表查询，查出来4578条数据，接着似乎很明确了，就是对每条数据的x2字段的值，跑到t2表里去查对 应的数据，进行关联。

但是此时因为 t2 表也没法根据索引来查，也是属于全表扫描，所以每次都得对t2表全表扫描一下，根据extra提示的Using where，就是根据t1表每条数据的x2字段的值去t2表查找对应的数据了，然后此时会用join buffer技术，在内存里做一些特殊优化，减少t2表的全表扫描次数。

using join buffer

使用连接缓存

impossible where

where语句的结果总是false

no matching row in const table

对于具有联接的查询，存在一个空表或没有满足唯一索引条件的行的表。

explain select * from emp where ename = 7469;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLENo matching row in const table

小结

Extra 值含义说明
Using index使用了覆盖索引（Covering Index）。表示查询所需的所有数据都可以直接从索引中获取，无需回表查询数据行，这是非常高效的。
Using where表示 MySQL 服务器在存储引擎返回记录后，需要进行后过滤（Post-filter）。如果未使用索引，此值仅提醒使用了 WHERE 条件。
Using filesort表示无法利用索引完成排序操作（如 ORDER BY），MySQL 必须执行额外的文件排序。这通常需要优化，例如为排序字段建立合适的索引。
Using temporary表示查询需要使用临时表来处理结果，常见于 GROUP BY、DISTINCT 或复杂的 JOIN 操作。这会增加内存和 I/O 开销，应尽量避免。
Using index condition表示使用了索引条件下推（Index Condition Pushdown, ICP）优化。MySQL 存储引擎层利用索引中的附加条件提前过滤数据，减少了需要回表的次数。
Impossible WHERE表示 WHERE 子句中的条件永远为假（例如 WHERE 1=2），MySQL 优化器判断该查询不可能返回任何结果。
Select tables optimized away表示查询被优化器完全优化掉了。通常出现在使用 MIN() 或 MAX() 等聚合函数，并且有对应索引的情况下，MySQL 可以直接从索引中获取结果，无需扫描任何行。
No tables used当查询不涉及任何表时出现，例如 EXPLAIN SELECT 1;。

Extra 字段的这些值是 SQL 性能调优的重要依据：

  • 优先优化：应重点关注 Using filesort 和 Using temporary，它们通常意味着性能瓶颈。
  • 理想状态：Using index 和 Select tables optimized away 是高效查询的标志。
  • 综合判断：分析 Extra 时，必须结合 type（访问类型）和 key（实际使用的索引）等其他列一起看，才能全面评估查询性能。

explain extended

MySQL 4.1引入explain extended命令，通过explain extended 加上show warnings可以查看MySQL 真正被执行之前优化器所做的操作

explain select * from users;
show warnings;

可从warning字段看到，会去除一些恒成立的条件，可以利用explain extended的结果来迅速的获取一个更清晰易读的sql语句。

两个字段都能用索引，如何选择？

select * from table where x1=xx or x2>=xx

该SQL语句要查一个表，对x1、x2建个联合索引，不就直接走索引扫描了？

万一你建的索引是两个呢？比如(x1,x3)，(x2,x4)，你建了两个联合索引，但此时你这个SQL肯定只能选择其中一个索引，它会选择哪个？

MySQL负责生成执行计划的查询优化器，一般会选择在索引里扫描行数较少的那个条件。比如x1=xx，在索引里只做等值比较，扫描数据较少，那可能就选x1索引，做个索引树查找，执行计划里就是ref方式，找到几条数据之后，接着做一个回表，回到聚簇索引里去查出每条数返回据完整数据，接着加载到内存，根据每条数据的x2字段值，根据x2>=xx条件做筛选。

场景

select * from table where x1=xx and c1=xx and c2>=xx and c3 IS NOT NULL

一个SQL所有筛选条件里，就一个x1字段有索引，这也很常见，一般写好一个系统后，针对所有SQL分析时，当然不可能针对所有SQL里的每个where字段都加索引，最终只能在所有SQL里，抽取部分经常在where里用到的字段来设计两三个联合索引。

于是很多SQL可能where条件好多个，结果就一个字段可以用到索引，此时查询优化器生成的执行计划，就仅针对x1走个ref访问，直接通过x1索引树快速查到一波数据。 接着对这波数据回表每条数据完整的字段，然后都加载到内存。接着就能针对这波数据的c1、c2、c3字段按条件进行筛选和过滤，最后拿到符合条件的数据。

所以x1索引设计，必然尽可能要让x1=xx这条件在索引树里查找出来的数据量较少，才能保证后续的性能较高。

多个二级索引

一般一个SQL只能用到一个二级索引，但有些特殊场景，可能一个SQL用到多个二级索引。

select * from table where x1=xx and x2=xx

然后x1、x2分别都有一个索引，也有可能会让查询优化器生成一个执行计划，执行计划里，就先对x1索引树查找，查出一波数据，再对x2索引树查一波数据，然后对两波数据，按主键值做交集，这交集就是符合两个条件的数据了，接着回表查完整数据即可。但之前对这种情况一直说的是，选择x1或x2其中一个字段的索引，就查一个字段的索引，找出一波数据，接着直接回表查完整数据，然后根据另外一个字段的值进行过滤。

那何时会直接对两个字段的两个索引一起查，然后取交集再回表？

何时一个SQL会查多个索引树？

比如x1、x2两个字段，若先查x1索引，一下子弄出上万条，都回表查完整数据，再根据x2过滤，效果是不是不太好？

若同时从x2索引树里也查一波数据，取交集，一下子让交集数据量变成几十条，再回表速度就快了。一般查索引树速度都快，但回表会慢点。

所以若同时查两个索引树，取交集后，数据量很小，再回表查，就能提升性能。

但若在一个SQL用多个索引，有很多要求，比如：

  • 如果有联合索引，你必须把联合索引里每个字段都放SQL里，且必须都是等值匹配
  • 或通过主键查询+其他二级索引等值匹配，也有可能会做一个多索引查询和取交集

只要记住执行SQL时，有可能会同时查多个索引树取交集，再回表。

所以SQL可能对多个索引树进行查找，接着用intersection交集、union并集的方式来进行合并，此时可能给你在执行计划里也会看到这些字样，就是告诉你查找了多个索引，做了一些结果集的交集或并集，而且这种方式也不一定发生。

新场景

有时可能一个SQL同时用多个索引，若你在SQL里写了类似

x1=xx or x2=xx

也可能会用多个索引，只不过查多个大索引树后，会取并集，而非交集。

现在大家知道SQL有哪些执行方式了：

  • const、ref、 range都是性能最好的方式，说明在底层直接基于某个索引树快速查找了数据了，但有的时候可能你在用了索引之后，还会 在回表到聚簇索引里查完整数据，接着根据其他条件来过滤
  • index就是扫描二级索引，即不通过索引树的根节点开始快速查找，而是直接对二级索引的叶节点遍历和扫描，这种速度还是较慢的，尽量避免。index怎么也比all方式好点。

2 show profile

SHOW PROFILE和SHOW PROFILES语句显示概要信息，该信息指示在当前会话过程中执行的语句的资源使用情况。

SHOW PROFILE和SHOW PROFILES语句已被弃用，并将在以后的MySQL版本中删除，而改用性能模式。此处我们就简单介绍一下，大家知道有这个东西就行了。

查看是否开启profile

select @@profiling

默认profiling关闭。可通过set语句在session级别启动profiling：

set profiling=1;

可查看执行过程中每个线程的状态和耗时。 其中的 sendingdata 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端，由于在sending data状态下，MySQL线程往往需要做大量的磁盘读取操作；所以经常是整个查询中最耗时的状态。

支持选择all,cpu,block io,context,switch,page faults等明细，来查看MySQL在使用什么资源上耗费了过高的时间，例如，选择查看cpu的耗费时间

show profile cpu for query 6;

对比MyISAM的操作，同样执行count(*)操作，检查profile，Innodb表经历了Sending data状态，而MyISAM的表完全不需要访问数据

如果对MySQL 源码感兴趣，可以通过show profile source for query查看sql解析执行过程的每个步骤对应的源码文件

show profile source for query 6

3 trace分析优化器

MySQL 5.6提供。通过trace文件能进一步了解优化器选择，理解优化器行为。

使用

开启trace，设置格式为json，设置trace最大能够使用的内存，避免解析过程中因为默认内存小而不能完整显示

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行trace的sql

select * from ....where....

检查information_schema.optimizer_trace可知MySQL咋执行sql：

select * from information_schema.optimizer_trace

参考：

avatar
文章
阅读
粉丝