Explain详解与索引最佳实践②
案例解析
# 假设index (a,b,c)
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int NOT NULL DEFAULT 0,
`b` varchar(20),
`c` int,
`other_text` varchar(200),
`other_int` bigint,
`created_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_a_b_c` (`a`,`b`,`c`)
) COMMENT='验证案例1';
#脚本注入一些数据 略
#前置知识:key_len的计算规则
#最左前缀原则:最左字段可以使用到索引
explain select * from t1 where a = 30;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_a_b_c | idx_a_b_c | 4 | const | 112 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
#最左前缀原则:条件顺序不影响执行计划结果
explain select * from t1 where a = 30 and b = 'lisi';
explain select * from t1 where b = 'lisi' and a = 6;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_a_b_c | idx_a_b_c | 87 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
# show warnings; Cannot use range access on index 'idx_a_b_c' due to type or collation conversion on field 'b'
# 类型转换导致不能充分利用索引
explain select * from t1 where a = 30 and b = 6;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_a_b_c | idx_a_b_c | 4 | const | 112 | 10.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
# 最左前缀原则:完全利用索引的情况
explain select * from t1 where a = 30 and b = 'lisi' and c = 66;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_a_b_c | idx_a_b_c | 92 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
# 最左前缀原则:完全利用索引的情况,最后一个字段可以使用范围查找
explain select * from t1 where a = 30 and b = 'lisi' and c > 66;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_a_b_c | idx_a_b_c | 92 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
# 最左前缀原则:跳过中间的索引字段,只能使用到第一个字段的排序
explain select * from t1 where a = 30 and c = 66;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_a_b_c | idx_a_b_c | 4 | const | 112 | 10.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
# 范围条件依然可以使用到a和b的排序
explain select * from t1 where a = 30 and b > 'lili';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_a_b_c | idx_a_b_c | 87 | NULL | 86 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
# 范围条件依然可以使用到a,b的排序,但是c是无序的
explain select * from t1 where a = 30 and b > 'lili' and c = 66;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_a_b_c | idx_a_b_c | 87 | NULL | 86 | 10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
# like *% 可以使用a,b的排序
explain select * from t1 where a = 30 and b like 'lisi%';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_a_b_c | idx_a_b_c | 87 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
# like *% + 后面的条件也能使用上c的排序
explain select * from t1 where a = 30 and b like 'lisi%' and c = 66;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_a_b_c | idx_a_b_c | 92 | NULL | 1 | 10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
# like *% + 后面的范围条件也能使用上c的排序, filtered值不一致
explain select * from t1 where a = 30 and b like 'lisi%' and c > 66;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_a_b_c | idx_a_b_c | 92 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
# like %*,只能使用到a
explain select * from t1 where a = 30 and b like '%lisi';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_a_b_c | idx_a_b_c | 4 | const | 112 | 11.11 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
# like *% 的不同写法与 'lisi%' 效果一致
explain select * from t1 where a = 30 and b like 'lisi%lisi%';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_a_b_c | idx_a_b_c | 87 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
#like KK%相当于=常量，%KK和%KK% 相当于范围, 这个知识点有些违反直觉
