MySQL索引面试题分析(索引分析,典型题目案例)
【建表语句】
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
select * from test03;
【建索引】
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
show index from test03;
问题:我们创建了复合索引idx_test03_c1234 ,根据以下SQL分析下索引使用情况?
1 explain select * from test03 where c1='a1'; 2 explain select * from test03 where c1='a1' and c2='a2'; 3 explain select * from test03 where c1='a1' and c2='a2' and c3='a3'; 4 explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
1)
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
2)
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
explain select * from test03 where c4='a4' and c3='a3' and c2='a2' and c1='a1';
3)
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
4)
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
说明:4个索引全部使用,虽然c3在最后,但是mysql可以自动调优。
5)
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
c3作用在排序而不是查找
【索引的两大功能:查找和排序】
6)
explain select * from test03 where c1='a1' and c2='a2' order by c3;
7)
explain select * from test03 where c1='a1' and c2='a2' order by c4;
出现了filesort
8)
8.1
explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;
只用c1一个字段索引,但是c2、c3用于排序,无filesort
8.2
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
出现了filesort,我们建的索引是1234,它没有按照顺序来,3 2 颠倒了
9)
explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
10)
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
用c1、c2两个字段索引,但是c2、c3用于排序,无filesort
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
本例有常量c2的情况,和8.2对比(c2='c2'已经有具体值,为常量时,无需排序)
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
filesort出现
11)
explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
12)
explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
Using where; Using temporary; Using filesort
【group by表面理解为分组,但是要注意的是,分组之前必排序】
【结论】
【一般性建议】
1、对于单键索引,尽量选择针对当前query过滤性更好的索引
2、在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前(左)越好。(避免索引过滤性好的索引失效)
3、在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
相关文章
- mysql 数据库连接_java连接oracle数据库
- 【MySQL高级】MySql中常用工具及Mysql 日志
- MySQL启动过程指南:一步步掌握启动语句(mysql启动语句)
- Mysql命令行终端入门教程(mysql进入终端)
- 使用 MySQL 查询指定字段的 Tips(mysql取指定字段)
- 分析MySQL管理系统案例研究:解决数据库管理问题(mysql管理系统案例)
- MYSQL应用:优质数据安全运维服务(mysql应用案例)
- MySQL数据库面试题解析(mysql数据库面试题)
- MySQL回滚操作的失败案例(mysql回滚失败)
- 题深入解析MySQL数据库优化解答面试题(mysql数据库优化面试)
- 里MySQL在哪里安装?(mysql装在哪)
- MySQL面试:终极挑战题目及答案(mysql面试题及答案)
- MySQL:掌握配置命令助力开发(mysql 配置命令)
- MySQL 运维面试题精选,助你成功应对技术面试!(mysql运维面试题)
- MySQL中游标循环的使用方法(mysql游标循环)
- MySQL中的PM如何优化数据库性能(mysql中pm)
- 深入浅出MySQL中ER图的作用及实现方法(mysql中er图的作用)
- 打开大门,突破91道MySQL面试难关(91道mysql面试题)
- 面试题168道MySQL面试题拿下Dream Job的机会在此(168道mysql)
- 100道MySQL面试题帮助你梦想成真(100题mysql面试题)
- MySQL如何判断空值(mysql中判断是空值)
- MySQL分片在实际案例中的运用(mysql中分片案例)
- 防范MySQL万能注入的关键措施,重要性不容忽视(mysql万能注入)
- GET MYSQL 免费下载并破解MySQL数据库软件(mysql下载和破解)