数据库索引不被使用的情况

    众所周知，对数据库建立索引会提高查询效率，而有很多情况会导致引擎放弃使用索引。比如使用字符串截取函数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

究竟是什么使得数据库放弃索引了呢