【MySQL】为什么索引没有被使用？
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【MySQL】为什么索引没有被使用？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7572字，纯文字阅读大概需要11分钟。
内容图文
【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
内容总结
以上是互联网集市为您收集整理的【MySQL】为什么索引没有被使用？全部内容，希望文章能够帮你解决【MySQL】为什么索引没有被使用？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。