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

最新推荐文章于 2021-02-02 06:51:33 发布

RichardJing

最新推荐文章于 2021-02-02 06:51:33 发布

阅读量437

点赞数

文章标签： QQ 正则表达式 MySQL

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

结果：

可以看出，即使从首字符截取，查询仍没有使用索引。

那么从中间截取字符串就更没理由使用索引了。

(2)explain select * from test where substr(hostname,4,3)='com';

结果：

2.验证正则表达式

(1)explain select * from test where hostname like 'qq%';

结果：

(2)explain select * from test where hostname like '%qq%';

结果：

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 ;

结果：

5.验证SUM()函数

(1)explain select sum(num) as num from test group by hostname;

结果：

没有使用索引

(2)explain select sum(num) as num from test where hostname='qq.com';

奇怪的是这里就使用索引了。

四、实验结论

1.从首字符截取或者从首字符匹配，还是不能让数据库使用索引。

2.使用IN不破坏索引使用

3.COUNT()函数不破坏索引使用

4.最奇怪的是使用SUM()函数时，如果where = 子句就使用索引，而group by 就不可以。难道是group by 破坏了索引？我又做了一个实验：

explain select hostname from test group by hostname;

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

RichardJing

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
数据库索引不被使用的情况

众所周知，对数据库建立索引会提高查询效率，而有很多情况会导致引擎放弃使用索引。比如使用字符串截取函数substr()，使用格式转换函数LOWER()，使用IN等。网上甚至有人说只要使用了数据库函数就会导致该情况，真的是这样吗？首先，让我们看看什么是索引。我觉得可以将聚集索引比喻为知道一个英文单词拼写，然后在字典里查询该词。比如"orange"这个单词，从首字母开始查找，在"n"...
复制链接

扫一扫