乐趣区

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

jiezi

创立索引：

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';

索引的优化倡议：

  • 单列索引尽量抉择过滤性更好的字段。
  • 组合索引尽量把过滤性最好的字段放在右边。
  • 组合索引尽量抉择可能蕴含查问语句中更罕用的字段。
  • 尽可能通过剖析统计信息和调整查问语句写法来达到抉择适合索引的目标。
退出移动版