【MySQL】为什么索引没有被使用?
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【MySQL】为什么索引没有被使用?,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7572字,纯文字阅读大概需要11分钟。
内容图文
![【MySQL】为什么索引没有被使用?](/upload/InfoBanner/zyjiaocheng/860/3c51458a82064ea389240b6e405410e3.jpg)
【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 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。