众所周知，对数据库建立索引会提高查询效率，而有很多情况会导致引擎放弃使用索引。比如使用字符串截取函数substr()，使用格式转换函数LOWER()，使用IN等。网上甚至有人说只要使用了数据库函数就会导致该情况，真的是这样吗？
首先，让我们看看什么是索引。我觉得可以将聚集索引比喻为知道一个英文单词拼写，然后在字典里查询该词。比如"orange"这个单词，从首字母开始查找，在"n"和"p“之间找到"o"。然后再找第二个字母，在"q"和"s"之间找到"r"。当所有字母查找完毕，也就找到该单词了（因为这样的索引顺序是唯一的，所以一个数据库只能有一个聚集索引）。我觉得不管是聚集索引还是非聚集索引，都会有一个“字典”顺序。什么情况会破坏这种“字典”顺序呢？如果截取了字符串的后半段，失去了前面的字符，当然不能再按照“字典”顺序来查询了。如果索引是区分大小写的，使用LOWER()函数自然使得字符串不能再按索引顺序检索。反之，如果不破坏这种“字典”顺序，是不是就可以使用索引了呢？
以字符串截取函数substr()为例，如果我从首字符开始截取字符串，那么子串依旧有“字典“顺序，是否还能使用索引？为此，我设计了以下实验：
一、实验环境：
数据库：MySQL
数据：一张表格，含有两列：hostname,num。 hostname是网址，num是该网址被访问的次数。允许不同行有相同的hostname。
在hostname上建立索引：CREATE INDEX hn on test(hostname);（默认为非聚集索引）
二、实验内容
1.使用substr()函数从首字符截取hostname，用EXPLAIN查看数据库是否使用检索。使用substr()函数从中间截取hostname。
2.使用正则表达式，从字符串首开始匹配，比如：LIKE 'abc%'。匹配字符串中间段，比如：LIKE '%abc%'。
3.使用IN
4.统计表格中的网站数，COUNT(DISTINCT hostname)
5.统计每个网站的访问数，SUM(num) GROUP BY hostname
三、实验结果
1.验证substr()
(1)explain select * from test where substr(hostname,1,2)='qq';
结果：
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10989649 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+可以看出，即使从首字符截取，查询仍没有使用索引。
那么从中间截取字符串就更没理由使用索引了。
(2)explain select * from test where substr(hostname,4,3)='com';
结果：
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10989649 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
和预期的一样，没有使用索引。
2.验证正则表达式
(1)explain select * from test where hostname like 'qq%';
结果：
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | test | ALL | hn | NULL | NULL | NULL | 10989649 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
可以看出，虽然 possible_keys里显示了索引名称，但key的值是NULL，所以实际上没有使用索引。
(2)explain select * from test where hostname like '%qq%';
结果：
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10989649 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
这下连| possible_keys都是空了。
3.验证IN
explain select * from test where hostname in ('qq.com');
结果：
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
| 1 | SIMPLE | test | ref | hn | hn | 38 | const | 4245724 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
使用了索引，而且type是ref。
4.验证COUNT()函数
explain select count(distinct hostname) from test ;
结果：
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | test | index | NULL | hn | 38 | NULL | 10989649 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
使用了索引，而且type是index。
5.验证SUM()函数
(1)explain select sum(num) as num from test group by hostname;
结果：
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10989649 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+没有使用索引
(2)explain select sum(num) as num from test where hostname='qq.com';
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
| 1 | SIMPLE | test | ref | hn | hn | 38 | const | 4245724 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+奇怪的是这里就使用索引了。
四、实验结论
1.从首字符截取或者从首字符匹配，还是不能让数据库使用索引。
2.使用IN不破坏索引使用
3.COUNT()函数不破坏索引使用
4.最奇怪的是使用SUM()函数时，如果where = 子句就使用索引，而group by 就不可以。难道是group by 破坏了索引？我又做了一个实验：
explain select hostname from test group by hostname;
实验结果：
+----+-------------+-------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+-------+--------------------------+
| 1 | SIMPLE | test | range | NULL | hn | 38 | NULL | 22428 | Using index for group-by |
+----+-------------+-------+-------+---------------+------+---------+------+-------+--------------------------+
结果显示使用了索引，而且Extra一栏明确显示：Using index for group-by
究竟是什么使得数据库放弃索引了呢
