MySQL 版本: SELECT VERSION(); // 8.0.23
组合索引相关的文档：https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html
EXPLAIN输出相关文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
SQL的执行顺序：https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/
DDL
CREATE TABLE `dev` (
`id` int NOT NULL AUTO_INCREMENT,
`x` int NOT NULL DEFAULT '0',
`y` int NOT NULL DEFAULT '0',
`z` int NOT NULL DEFAULT '0',
`m` int NOT NULL DEFAULT '0',
`n` int NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_x_y_z` (`x`,`y`,`z`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
WHERE 篇
-
EXPLAIN SELECT * FROM dev WHERE x = 1;// type=ref，因此是一种索引访问。
1.png
-
EXPLAIN SELECT * FROM dev WHERE y = 1;// type=ALL，因此是全表扫描。
2.png
-
EXPLAIN SELECT x, y, z FROM dev WHERE x = 1 AND y = 2;// type=ref；Extra=Using index。其中Using index表明MySQL使用覆盖索引。
3.png
-
EXPLAIN SELECT * FROM dev WHERE x > 1 AND y = 2 AND z = 3;// type=range，即范围扫描，也就是有限制的索引扫描。key_len=4表明仅仅用到了x这个索引。（int占4个字节）
4.png
简单讲就是最左前缀匹配原则，并且要利用组合索引后面的索引，其前面的索引必须是等值查询（其她各种情况可以试试.......
ORDER BY 篇
结合 where 篇的经验，先看看组合索引基于最左前缀匹配原则的等值查询，即x=constant
-
EXPLAIN SELECT * FROM dev WHERE x = 1 ORDER BY y;// Extra=NULL 预期的结果，索引本来就有序，所有order by不用单独排序。
5.png
-
EXPLAIN SELECT * FROM dev WHERE x = 1 ORDER BY y ASC, z DESC;// Extra=Using filesort 其实也很好理解，组合索引(x,y,z)本来就是有序的，但是在ORDER BY里，y和z的顺序是相反的，那必然得进行额外的排序。
6.png
非等值查询
-
EXPLAIN SELECT * FROM dev WHERE x = 1 and y > 2 ORDER BY z;// Extra包含Using filesort，因为组合索引(x,y,z)里，虽然x,y都用到了索引，但是y是范围扫描，因此ORDER BY z需要额外的排序。如果ORDEY BY z改为ORDER BY y则会利用索引，不会有额外的排序。
7.png
