首页 / MYSQL / MySQL索引题目分析
MySQL索引题目分析
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL索引题目分析,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5948字,纯文字阅读大概需要9分钟。
内容图文
![MySQL索引题目分析](/upload/InfoBanner/zyjiaocheng/458/b476da8d2d264c11aa48fad02c5bc64d.jpg)
a.where c1=x and c2=x and c4>x and c3=x
b.where c1=x and c2=x and c4=x order by c3
c.where c1=x and c4=x group by c3,c2
d.where c1=? and c5=? order by c2,c3
e.where c1=? and c2=? and c5=? order by c3,c2
2.建表语句如下,插入了50万条数据,如果表数据太少,mysql会优化成直接做全表扫描,而不需要使用索引:
CREATE TABLE `MNG_ROLE` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) NOT NULL,
`CREATE_DATE` char(8) NOT NULL,
`CREATE_TIME` char(6) DEFAULT NULL,
`UUID` char(32) NOT NULL,
`REMARK` varchar(3000) NOT NULL DEFAULT ‘‘,
`RESERVER` varchar(3000) NOT NULL DEFAULT ‘RESERVER‘,
PRIMARY KEY (`ID`),
UNIQUE KEY `UK_UUID` (`UUID`) USING BTREE,
KEY `INDEX_NAME_CREATE_DATE_TIME_REMARK` (`NAME`,`CREATE_DATE`,`CREATE_TIME`,`REMARK`(255)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=510335 DEFAULT CHARSET=utf8
3.语句执行情况:
a.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ AND REMARK > ‘A‘ AND CREATE_TIME=‘200000‘\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: range
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 962
ref: NULL
rows: 1
Extra: Using index condition; Using where
分析:type=range表示用到了索引,实际就是REMARK的范围查询,MySQL并且对索引进行了过滤,就有了Using index condition。实际上这条语句在不影响语义的情况下,把过滤条件改为:WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ AND CREATE_TIME=‘200000‘ AND REMARK > ‘A‘,此处用到的索引列包括:NAME,CREATE_DATE,CREATE_TIME,REMARK,key_len为962=50*3+14*3+255*3+5,5个长度是由于NAME是一个可变长度
===========================性感的分割线====================================
b.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ AND REMARK = ‘A‘ ORDER BY CREATE_TIME\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: ref
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 176
ref: const,const
rows: 1
Extra: Using index condition; Using where
分析:这里用于查询的索引只用到两列:NAME,CREATE_DATE;而CREATE_TIME的索引是用于排序,而REMARK是用于索引结果集的过滤。为什么CREATE_TIME的索引能用于排序?
1.首先我们要知道InnoDB的索引的数据格式是类似于有序的平衡树,当我们用了NAME和CREATE_DATE作为过滤条件,刚好CREATE_TIME的索引已经完成排序,那存储引擎就没必要在内存中再进行排序,可以运行下面两个语句,观察两者区别:
MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ ORDER BY CREATE_TIME\G
MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ ORDER BY REMARK\G
===========================性感的分割线====================================
c.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND RESERVER = ‘A‘ GROUP BY CREATE_TIME, CREATE_DATE\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: ref
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 152
ref: const
rows: 1
Extra: Using index condition; Using where; Using temporary; Using filesort
分析:这里只用到一个索引(NAME)用于查找,但是这个语句在内存中建立了临时表(Using temporary),并且进行了排序(Using filesort),这些都是非常耗资源的。因为group by的操作,实际是先把过滤的数据,放到一个内存的临时表中,再对数据排序,最后对数据进行聚合操作。
我现在修改一下语句,这样会更加高效,原因就是利用索引的排序:EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND RESERVER = ‘A‘ GROUP BY CREATE_DATE, CREATE_TIME\G
===========================性感的分割线====================================
d.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role2‘ AND RESERVER=‘A‘ ORDER BY CREATE_DATE,CREATE_TIME\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: ref
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 152
ref: const
rows: 1
Extra: Using index condition; Using where
分析:索引用于查找的,只用到NAME,用于排序的有CREATE_DATE,CREATE_TIME
===========================性感的分割线====================================
e.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role2‘ AND CREATE_DATE=‘20151010‘ AND RESERVER=‘A‘ ORDER BY CREATE_TIME,CREATE_DATE\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: ref
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 176
ref: const,const
rows: 1
Extra: Using index condition; Using where
分析:这里的NAME和CREATE_DATE都是用于查找的索引,CREATE_TIME用于排序的索引,但是CREATE_DATE的排序是无效的,因为前面的where过滤条件,已经赋值:CREATE_DATE=‘20151010‘,直接无视一个常量的排序。下面这个语句就需要内存排序:EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role2‘ AND CREATE_DATE=‘20151010‘ AND RESERVER=‘A‘ ORDER BY REMARK\G
4.总结:
1.索引可用于查找(type: ref),排序,过滤(Using index condition):
2.根据最左前缀原则,一旦有范围查询的字段,后面的索引将无效,而且索引需要串联,如果where c1=? and c3=?,这样将无法使用索引
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),清晰度越高越好
4.索引不能用于计算,尽量扩展索引
MySQL索引题目分析
标签:
本文系统来源:http://www.cnblogs.com/hippo0918/p/4873229.html
内容总结
以上是互联网集市为您收集整理的MySQL索引题目分析全部内容,希望文章能够帮你解决MySQL索引题目分析所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。