关于mysql:MySQL-索引综合示例

8次阅读

共计 1936 个字符，预计需要花费 5 分钟才能阅读完成。

创立索引：

create index idx_test_c1234 on test(c1,c2,c3,c4);

测试索引：

-- 全值匹配能够应用索引
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' AND `c3`='a3' AND `c4`='a4';
-- 查问优化器优化后能够应用索引
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' AND `c4`='a4' AND `c3`='a3';
-- 查问优化器优化后能够应用索引
EXPLAIN SELECT * FROM test WHERE `c4`='a4' AND `c3`='a3' AND `c2`='a2' AND `c1`='a1';

-- 只能用到前三个索引，第四个生效
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' AND `c3`>'a3' AND `c4`='a4';

-- 能够用到四个索引，第四个用于排序，查问优化器会优化为：AND `c3`='a3' AND `c4`>'a4';
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' AND `c4`>'a4' AND `c3`='a3';

-- 查找用到了两个索引，排序用到了一个索引，无 filesort
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' AND `c4`='a4' ORDER BY c3;

-- 查找用到了两个索引，排序用到了一个索引，无 filesort
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' ORDER BY c3;

-- 查找用到了两个索引，排序没有用到索引，而是应用了文件排序
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' ORDER BY c4;

-- 查找用到了一个索引，排序用到了两个索引，无 filesort
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c5`='a5' ORDER BY c2, c3;

-- 查找用到了一个索引，排序无奈应用索引
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c5`='a5' ORDER BY c3, c2;

-- 查找用到了两个索引，排序用到了两个索引，无 filesort
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' ORDER BY c2, c3;
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' AND `c5`='a5' ORDER BY c2, c3;

-- 查找用到了两个索引，排序应用了一个索引（查问条件中 c2 是常量，只有惟一值，不须要再进行排序），无 filesort
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2`='a2' AND `c5`='a5' ORDER BY c3, c2;

-- 查找用到了一个索引，分组能够应用索引
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c4`='a4' GROUP BY c2, c3;

-- 查找用到了一个索引，分组没有应用索引（应用了长期表和文件排序）-- 分组的前提是排序，分组的索引准则基本上与排序统一
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c4`='a4' GROUP BY c3, c2;

-- 查找用到了三个索引，含糊查问后的字段能够持续应用索引（与大于小于不同）EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2` like 'kk%' AND c3='a3';

-- 查找用到了一个索引，含糊查问不能应用索引
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2` like '%kk' AND c3='a3';
EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2` like '%kk%' AND c3='a3';

-- 查找用到了三个索引，含糊查问后的字段能够持续应用索引（与大于小于不同）EXPLAIN SELECT * FROM test WHERE `c1`='a1' AND `c2` like 'k%kk%' AND c3='a3';

索引的优化倡议：