建表
create table test1(
id int not null PRIMARY KEY auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('a1','a2','a3','a4','a5');
INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('e1','e2','e3','e4','e5');
INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('b1','b2','b3','b4','b5');
INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('c1','c2','c3','c4','c5');
INSERT INTO test1(c1,c2,c3,c4,c5) VALUES('d1','d2','d3','d4','d5');
create index idx_c1234 on test1(c1,c2,c3,c4);
场景分析
索引有查找和排序两大功能
- 用到了索引c1,c2使用查找,c3使用排序,c3使用到是因为mysql下面有查询优化器进行优化将sql语句调整到最优情况
最好按照索引创建的顺序来写SQL避免mysql底部优化器优化:explain SELECT * from test1 where c1='a1' and c2='a2' and c4='a4' order by c3;
- 用到了c1c2c3c4:
explain SELECT * from test1 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
- c3用作排序：
explain SELECT * from test1 where c1='a1' and c2='a2' order by c3;
- c4未用到且是filesort：
explain SELECT * from test1 where c1='a1' and c2='a2' order by c4;
- 只有c1用到索引c2,c3用于排序：
explain SELECT * from test1 where c1='a1' and c5='a5' order by c2,c3;
- 出现了filesort，索引顺序是c2、c3，检索顺序是c3、c2颠倒了没走索引所以是filesort：
explain SELECT * from test1 where c1='a1' and c5='a5' order by c3,c2;
- c2=a2相当于有常量，order by一个(c3,常量)的时候就相当于是order by c3，此时c1c2走索引，c3走排序不会filesort：
explain SELECT * from test1 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
- c1c2用于索引，c2c3用于排序：
explain SELECT * from test1 where c1='a1' and c2='a2' order by c2,c3;
- 只使用到了c1索引，c2c3查的索引没有扫全表：
EXPLAIN select c2,c3 from test1 where c1='a1' and c4='a4' group by c2,c3;
EXPLAIN select c2,c3 from test1 where c1='a1' and c4='a4' group by c3,c2;
- 索引分析总结
- 定制查找是常量
- 范围后面是失效
- 最终看排序，一般order by都会给个范围
- group by基本上都需要进行排序，会有临时表产生
- 建议
- 单值索引尽量选择针对当前查询过滤性更好的索引
- 组合索引选择时，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好；尽量选择可能包含当前查询中的where字句中包含更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
- 优化总结口诀
- 全值匹配我最爱，最左前缀要遵守
- 带头大哥不能死，中间兄弟不能断
- 索引列上少计算，范围之后全失效
- like百分写右边，覆盖索引不写星
- 不等空值还有or,索引失效要少用
- str引号不可丢，SQL高级也不难
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
