【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的原因)

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

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

　　3.like查询是以%开头

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

　　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