环境准备
-
MySQL版本8.0.28
-
建立测试表并插入一万条测试数据，测试数据文件另外 下载 。
CREATE TABLE `index_test` ( `id` varchar(32) NOT NULL COMMENT '主键', `int_hash_key` int DEFAULT NULL COMMENT '数值类型的hash索引', `int_tree_key` int DEFAULT NULL COMMENT '数值类型的tree索引', `str_hash_key` varchar(32) DEFAULT NULL COMMENT '字符串hash索引', `str_tree_key` varchar(32) DEFAULT NULL COMMENT '字符串tree索引', `time_hash_key` datetime DEFAULT NULL COMMENT '日期hash索引', `time_tree_key` datetime DEFAULT NULL COMMENT '日期tree索引', `unite_hash_1` varchar(32) DEFAULT NULL COMMENT '联合索引', `unite_hash_2` varchar(32) DEFAULT NULL COMMENT '联合索引', `not_index_str` varchar(255) DEFAULT NULL COMMENT '没有索引', PRIMARY KEY (`id`), UNIQUE KEY `index_int_hash_key` (`int_hash_key`), UNIQUE KEY `index_int_tree_key` (`int_tree_key`) USING BTREE, UNIQUE KEY `inde_str_hash_key` (`str_hash_key`), UNIQUE KEY `inde_str_tree_key` (`str_tree_key`) USING BTREE, UNIQUE KEY `inde_unite` (`unite_hash_1`,`unite_hash_2`), KEY `inde_time_hash_key` (`time_hash_key`), KEY `inde_time_tree_key` (`time_tree_key`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Explain
explain可以查看SQL的执行计划，是否应用索引，是否做全表扫描，预计操作的数据行数等信息，我们可以基于这些信息了解SQL的执行情况，以便于我们进行优化。
执行explain命令之后, 显示的信息一共有12列, 分别是:
- id：选择标识符。
- select_type：查询类型。
- table：输出结果集的表。
- partitions：匹配的分区。
- type：表的连接类型。
- possible_keys： 查询时可能使用的索引。
- key：实际使用的索引。
- key_len：索引字段的长度。
- ref：列与索引的比较。
- rows：扫描出的行数。
- filtered：按表条件过滤的行百分比。
- extra：执行情况描述和说明。
select_type
-
SIMPLE：最简单的SELECT查询，查询中不包含子查询或者UNION。
SELECT * FROM index_test WHERE int_hash_key < 10; -
PRIMARY：有嵌套逻辑的SQL中，嵌套查询最外层的部分被标记为PRIMARY。
-- 示例1 EXPLAIN SELECT * FROM index_test WHERE int_hash_key < 10 UNION ALL SELECT * FROM index_test WHERE int_hash_key > 10 AND int_hash_key < 20; -- 示例2 EXPLAIN SELECT (SELECT str_tree_key FROM index_test AS t2 WHERE t2.id = t1.id) FROM index_test AS t1 WHERE int_hash_key < 10; -
UNION：
UNION前后如果有两个SELECT，那么把出现在UNION之后的第二个SELECT标记为UNION；如果UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。-- 示例1 EXPLAIN SELECT * FROM index_test WHERE int_hash_key < 10 UNION ALL SELECT * FROM index_test WHERE int_hash_key > 10 AND int_hash_key < 20; -- 示例2 EXPLAIN SELECT * FROM (SELECT * FROM index_test WHERE int_hash_key < 10 UNION ALL SELECT * FROM index_test WHERE int_hash_key > 10 AND int_hash_key < 20) AS t1; -
SUBQUERY：出现在select或者where后面中的子查询被标记为SUBQUERY。
EXPLAIN SELECT (SELECT id FROM blog where id = 1) FROM index_test; -
DERIVED：这个其实我理解是SUBQUERY的一种特例，只不过出现的位置比较特殊，是在from后面的子查询，MySQL会将子查询结果存放在一个临时表中，称为
派生表，因为这是我们派生出来的，而非原始表。-- 示例1 EXPLAIN SELECT * FROM (SELECT * FROM index_test WHERE int_hash_key < 10 UNION ALL SELECT * FROM index_test WHERE int_hash_key > 10 AND int_hash_key < 20) AS t1; -
DEPENDENT SUBQUERY：子查询中的第一个
SELECT, 取决于外面的SELECT。EXPLAIN SELECT (SELECT int_hash_key FROM index_test AS t2 WHERE t2.id = t1.id) FROM (SELECT * FROM index_test AS t1 WHERE t1.int_hash_key > 10) AS t1;
type
访问类型，表示MySQL是如何访问数据的，是全表扫描还是通过索引等？结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 。一般来说，优化的SQL至少达到 range ，最好能达到ref 。
-
system：表里只有一行记录，这个属于const类型的特例，一行数据平时很少出现，可以忽略不计。
-
const：表示通过索引一次就找到了，当
WHERE条件中使用primary key或者unique索引做等值过滤时MYSQL就能将该查询转换为一个const。 -
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
EXPLAIN SELECT t1.* FROM index_test AS t1,index_test AS t2 WHERE t1.str_hash_key = t2.str_hash_key; -
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
-
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了
bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。 -
index：index与ALL的区别是index只遍历索引树，这通常比ALL快，因为索引文件通常比较小（index与ALL虽然都是读全表，但是index是从索引中读取，ALL是从硬盘读取）。
EXPLAIN SELECT id FROM index_test; -
all：全表扫描，意味MySQL需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。
possible_keys
这一列显示查询可能使用哪些索引来查找。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
key
显示MySQL实际使用那个索引来优化对表的访问。为NULL表示没有使用索引，可以使用force index 或 ignore index 强制MySQL使用或忽视possible_keys列中的索引。
key_len
表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的
ref
显示关联的字段. 如果使用常数等值查询, 则显示const, 如果是连接查询, 则会显示关联的字段。
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，该值越小越好。
Extra
展示额外的信息。有以下几种重要的值，Using filesort，Using temporary，Using index，Using where Using index。
-
Using filesort：出现
using filesort时一般就需要考虑进行优化，尤其是在数据量大的时候。-- Extra列是NULL EXPLAIN SELECT * FROM index_test ORDER BY id; -- Extra列是NULL EXPLAIN SELECT * FROM index_test ORDER BY int_hash_key LIMIT 0,10; -- Extra列是Using filesort EXPLAIN SELECT * FROM index_test ORDER BY int_hash_key; -
Using temporary：MySQL需要创建临时表来处理查询，常见于有
order by和group by子句的SQL。 -
Using index：表面查询操作使用了覆盖索引，不需要访问表的额外数据行，效率比较高。如果同时出现了
Using where表明索引被用来执行索引键值的查找，如果没有与Using where同时出现表明索引用来读取数据而非执行查找动作。-- Using index EXPLAIN SELECT id FROM index_test; -
Using where：不用读取表中所有信息, 仅通过索引就可以获取所需数据, 这发生在对表的全部的请求列都是同一个索引的部分的时候, 表示mysql服务器将在存储引擎检索行后再进行过滤.
EXPLAIN SELECT id FROM index_test WHERE not_index_str IS NULL;
验证
like
结论：当 % 在后索引生效，在前索引失效。
-- 生效
EXPLAIN SELECT * FROM index_test WHERE str_hash_key LIKE '1%';
-- 失效
EXPLAIN SELECT * FROM index_test WHERE str_hash_key LIKE '%1';
函数
-- 生效
EXPLAIN SELECT * FROM index_test WHERE str_hash_key = UPPER('1');
-- 失效
EXPLAIN SELECT * FROM index_test WHERE UPPER(str_hash_key) = '1';
范围查询
结论：当命中的数据范围较小或者需要操作的数据较小时应用索引。
-- 生效
EXPLAIN SELECT * FROM index_test WHERE int_hash_key < 10;
-- 失效
EXPLAIN SELECT * FROM index_test WHERE int_hash_key > 10;
-- 失效
EXPLAIN SELECT * FROM index_test WHERE int_hash_key > 10000;
-- 生效
EXPLAIN SELECT * FROM index_test WHERE int_hash_key > 10 LIMIT 0,10;
负向查询
结论：除主键列外，其它索引列的负向查询都不走索引。
注意：!=、<>查询时是不会把null值的结果查询出来的。
-- 生效
EXPLAIN SELECT * FROM index_test WHERE id NOT IN ("1")
-- 失效
EXPLAIN SELECT * FROM index_test WHERE int_hash_key NOT IN ("1");
-- 生效
EXPLAIN SELECT * FROM index_test WHERE id <> 'a';
-- 失效
EXPLAIN SELECT * FROM index_test WHERE int_hash_key <> 'a';
-- 失效
EXPLAIN SELECT * FROM index_test WHERE time_tree_key NOT BETWEEN '2023-12-27 00:00:00' AND '2023-12-28 00:00:00';
ORDER BY
结论：ORDER BY对主键索引排序会用到索引，其他的索引失效（覆盖索引除外）。
-- 生效
EXPLAIN SELECT * FROM index_test ORDER BY id;
-- 失效
EXPLAIN SELECT * FROM index_test ORDER BY int_hash_key;
-- 生效
EXPLAIN SELECT int_hash_key FROM index_test ORDER BY int_hash_key;
-- 失效
EXPLAIN SELECT time_tree_key FROM index_test ORDER BY int_hash_key;
类型不一致
结论：隐式类型转换会引发索引失效，在进行比较是要确保类型正确，严重的情况会直接爆错。
-- 生效
EXPLAIN SELECT * FROM index_test WHERE str_hash_key = "1";
-- 失效
EXPLAIN SELECT * FROM index_test WHERE str_hash_key = 1;
联合索引
结论：联合索引违背最左匹配原则，会导致索引失效。
-- 走索引
EXPLAIN SELECT * FROM index_test WHERE unite_hash_1 = '1';
-- 不走索引
EXPLAIN SELECT * FROM index_test WHERE unite_hash_2 = '1';
OR
结论：索引列与非索引列进行OR会造成索引失效。
-- 生效
EXPLAIN SELECT * FROM index_test WHERE id = '000f6198b700444cbc1b8b2ba0543952' OR id = '000f6198b700444cbc1b8b2ba0543953';
-- 生效
EXPLAIN SELECT * FROM index_test WHERE id = '000f6198b700444cbc1b8b2ba0543952' OR str_hash_key = 'a';
-- 生效
EXPLAIN SELECT * FROM index_test WHERE str_hash_key = 'a' OR str_hash_key = 'b';
-- 失效
EXPLAIN SELECT * FROM index_test WHERE id = '000f6198b700444cbc1b8b2ba0543952' OR not_index_str = 'a';
Hash索引
结论：Hash类型的索引不支持范围查询，如果使用范围查询则导致索引失效（注意：InnerDB和MyISAM存储引擎不支持Hahs索引）。
348
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
