【MySQL】为什么索引没有被使用？

最新推荐文章于 2022-07-08 10:29:04 发布

coco3600

最新推荐文章于 2022-07-08 10:29:04 发布

阅读量924

点赞数 1

文章标签：数据库面试

 
  【MySQL】为什么索引没有被使用？

“为什么索引没有被使用”是一个涉及面较广的问题，有多种原因会导致索引不能被使用，下面列出几种常见的场景。

（1）若索引列出现了隐式类型转换（Implicit Type Conversion），则MySQL不会使用索引。常见的情况是，如果在SQL的WHERE条件中，字段类型为字符串，而其值为数值，那么MySQL不会使用索引，这个规则和Oracle是一致的，所以，字符类型的字段值应该加上引号。例如，表t_base_user的telephone列是一个字符类型的索引列，则：

 select * from t_base_user where telephone = 12345678901; 

这个语句在执行的时候不会选择索引，应该修改为：

 select * from t_base_user where telephone =  '12345678901'; 

（2）在使用cast函数时，需要保证字符集一样，否则MySQL不会使用索引。例如，表t_base_user的telephone列的字符集为latin1，则在使用cast函数时需要指定字符集：

 mysql> show full columns from lhrdb.t_base_user; 
 +------------+-------------+-------------------+------+-----+-------------------+----------------+---------------------------------+-------------------+ 
 | Field      | Type        | Collation         | Null | Key | Default           | Extra          | Privileges                      | Comment           | 
 +------------+-------------+-------------------+------+-----+-------------------+----------------+---------------------------------+-------------------+ 
 | oid        | bigint(20)  | NULL              | NO   | PRI | NULL              | auto_increment | select,insert,update,references |                   | 
 | name       | varchar(30) | latin1_swedish_ci | YES  | MUL | NULL              |                | select,insert,update,references | name              | 
 | email      | varchar(30) | latin1_swedish_ci | YES  | MUL | NULL              |                | select,insert,update,references | email             | 
 | age        | int(11)     | NULL              | YES  |     | NULL              |                | select,insert,update,references | age               | 
 | telephone  | varchar(30) | latin1_swedish_ci | YES  | MUL | NULL              |                | select,insert,update,references | telephone         | 
 | status     | tinyint(4)  | NULL              | YES  |     | NULL              |                | select,insert,update,references | 0 无效 1 有效     | 
 | created_at | datetime    | NULL              | YES  |     | CURRENT_TIMESTAMP |                | select,insert,update,references | 创建时间          | 
 | updated_at | datetime    | NULL              | YES  |     | CURRENT_TIMESTAMP |                | select,insert,update,references | 修改时间          | 
 +------------+-------------+-------------------+------+-----+-------------------+----------------+---------------------------------+-------------------+ 
 8 rows in set (0.00 sec) 
 9   
 mysql> explain select * from t_base_user where telephone=cast(12345678901 as char); 
 +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
 | id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       | 
 +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
 |  1 | SIMPLE      | t_base_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 521550 |   100.00 | Using where | 
 +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
 1 row in set, 1 warning (0.00 sec) 
   
 mysql> explain select * from t_base_user where telephone=cast(12345678901 as char  charset latin1); 
 +----+-------------+-------------+------------+------+-----------------------------------+---------------+---------+-------+--------+----------+-------+ 
 | id | select_type | table       | partitions | type | possible_keys                     | key           | key_len | ref   | rows   | filtered | Extra | 
 +----+-------------+-------------+------------+------+-----------------------------------+---------------+---------+-------+--------+----------+-------+ 
 |  1 | SIMPLE      | t_base_user | NULL       | ref  | idx_telephone,idx_telephone_email | idx_telephone | 33      | const | 260775 |   100.00 | NULL  | 
 +----+-------------+-------------+------------+------+-----------------------------------+---------------+---------+-------+--------+----------+-------+ 
 1 row in set, 1 warning (0.00 sec) 

（3）如果WHERE条件中含有OR，除非OR条件中的所有列都是索引列，否则MySQL不会选择索引。

（4）对于多列索引，若没有使用前导列，则MySQL不会使用索引。

（5）在WHERE子句中，如果索引列所对应的值的第一个字符由通配符（WILDCARD）开始，索引将不被采用，然而当通配符出现在字符串其它位置时，优化器就能利用索引。

（6）如果MySQL估计使用全表扫描要比使用索引快，那么MySQL不使用索引。

（7）如果对索引字段进行函数、算术运算或其他表达式等操作，那么MySQL不使用索引。

索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

201464144446285.png?201454144457

　　注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

　　2.对于多列索引，不是使用的第一部分，则不会使用索引

　　3.like查询是以%开头

201464144511212.png?201454144524

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

201464144553091.png?20145414466

　　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

此外，查看索引的使用情况
show status like ‘Handler_read%';
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效

在MySQL中，并不是你建立了索引，并且你在SQL中使用到了该列，MySQL就肯定会使用到那些索引的，有一些情况很可能在你不知不觉中，你就“成功的避开了”MySQL的所有索引。

现假设有t_stu表，age,sname上建立了索引

索引列参与计算

如果where条件中age列中使用了计算，则不会使用该索引

SELECT `sname` FROM `t_stu` WHERE `age`=20;-- 会使用索引
SELECT `sname` FROM `t_stu` WHERE `age`+10=30;-- 不会使用索引！！因为所有索引列参与了计算

SELECT `sname` FROM `t_stu` WHERE `age`=30-10;-- 会使用索引

故，如果需要计算，千万不要计算到索引列，想方设法让其计算到表达式的另一边去。

索引列使用了函数

同样的道理，索引列使用了函数，一样会导致相同的后果

SELECT `sname` FROM `stu` WHERE concat(`sname`,'abc') ='Jaskeyabc'; -- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT `sname` FROM `stu` WHERE `sname` =concat('Jaskey','abc'); -- 会使用索引
索引列使用了Like %XXX

SELECT * FROM `houdunwang` WHERE `uname` LIKE '前缀就走索引%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE '后缀不走索引%' -- 不走索引
所以当需要搜索email列中.com结尾的字符串而email上希望走索引时候,可以考虑数据库存储一个反向的内容reverse_email

SELECT * FROM `table` WHERE `reverse_email` LIKE REVERSE('%.com'); -- 走索引
注：以上如果你使用REVERSE(email) = REVERSE('%.com')，一样得不到你想要的结果，因为你在索引列email列上使用了函数，MySQL不会使用该列索引

同样的，索引列上使用正则表达式也不会走索引。

字符串列与数字直接比较

这是一个坑，假设有一张表,里面的a列是一个字符char类型,且a上建立了索引,你用它与数字类型做比较判断的话：

CREATE TABLE `t1` (`a` char(10));

SELECT * FROM `t1` WHERE `a`='1' -- 走索引
SELECT * FROM `t2` WHERE `a`=1 -- 字符串和数字比较，不走索引！
但是如果那个表那个列是一个数字类型，拿来和字符类型的做比较，则不会影响到使用索引

CREATE TABLE `t2` (`b` int);

SELECT * FROM `t2` WHERE `b`='1' -- 虽然b是数字类型，和'1'比较依然走索引
但是，无论如何，这种额外的隐式类型转换都是开销，而且由于有字符和数字比就不走索引的情况，故建议避免一切隐式类型转换

尽量避免 OR 操作

select * from dept where dname='jaskey' or loc='bj' or deptno=45 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引
所以除非每个列都建立了索引，否则不建议使用OR，在多列OR中，可以考虑用UNION 替换

select * from dept where dname='jaskey' union
select * from dept where loc='bj' union
select * from dept where deptno=45

About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2 《DBA笔试面宝典》读者群小麦苗的微店

.............................................................................................................................................