MySQL索引题目分析

1.之前看视频呢的时候，里面提到一道索引题目：假设某个表有一个联合索引(c1,c2,c3,c4)-只能使用该联合索引的c1,c2,c3部分
a.where c1=x and c2=x and c4>x and c3=x（c1,c2,c3,c4都用到了索引，c4是范围索引查找）
b.where c1=x and c2=x and c4=x order by c3（c1,c2用于查找，c3用于排序）
c.where c1=x and c4=x group by c3,c2（只用到c1）
d.where c1=? and c5=? order by c2,c3（c1用于查找，c2, c3用于排序）
e.where c1=? and c2=? and c5=? order by c3,c2（c1,c2用于查找，c3用于排序）
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表示用到了索引，实际用到了4列索引。实际就是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.索引不能用于计算，尽量扩展索引