mysql索引失效的几种情况(MySql范围查找时索引不生效问题的原因分析)
mysql索引失效的几种情况
MySql范围查找时索引不生效问题的原因分析1 问题描述
本文对建立好的复合索引进行排序,并取记录中非索引字段,发现索引不生效,例如,有如下表,ddl语句为:
|
create table `employees` ( `emp_no` int (11) not null , `birth_date` date not null , `first_name` varchar (14) not null , `last_name` varchar (16) not null , `gender` enum( 'm' , 'f' ) not null , `hire_date` date not null , `age` int (11) not null , primary key (`emp_no`), key `unique_birth_name` (`first_name`,`last_name`) using btree ) engine=innodb default charset=utf8; |
复合索引为unique_birth_name (first_name,last_name)
。使用以下语句:
|
explain select gender from employees order by first_name, last_name |
根据上图:type:all 及 extra:using filesort 可得,索引没有生效。
继续进行试验,对查询语句进一步改写,加上一个范围查找:
|
explain select gender from employees where first_name > 'leah' order by first_name, last_name |
执行计划显示如下图:
这里发现结果和第一次sql分析无异。继续试验。
改写sql语句:
|
explain select gender from employees where first_name > 'tzvetan' order by first_name, last_name |
此时,令人惊讶的是,索引生效了。
2 问题分析
此时,我们做一个大胆的猜测:
第一次进行sql分析时,因为第一次order by 后,得到的还是全表数据,如果根据复合索引中携带的主键查找每一个gender进行拼接,自然很费资源和时间,mysql不会做如此蠢的事。不如直接进行全表扫描,把扫描到的每条数据和order by得到的临时数据进行拼接,从而得到需要的数据。
为了验证上述想法的正确性,我们对三次sql进行分析。
第一次sql根据复合索引得到的数据量为:300024,为全表数据
|
select count (first_name) from employees order by first_name, last_name |
第二次改写的sql根据复合索引得到的数据量为:159149 , 为全表数据量的1/2。
|
select count (first_name) from employees where first_name > 'leah' order by first_name, last_name |
第三次改写的sql根据复合索引得到的数据量为:36731, 为全表数据量的1/10。
|
select count (first_name) from employees where first_name > 'tzvetan' order by first_name, last_name |
通过对比发现,第二次改写的sql根据复合索引得到的数据量是全表数据量的1/2。此时还没有达到mysql使用索引进行二次查找的量级。第三次改写的sql根据复合索引得到的数据量是全表数据量的1/10,达到了mysql使用索引进行二次查找的量级,于是从执行计划上可以看到,第三次改写sql是走了索引的。
3 总结
mysql 是否根据首次索引条件查询出的主键进行二次查找,也是要看查询出来的数据量级,如果数据量接近全表数据量的话,就会进行全表扫描,否则根据第一次查询出来的主键进行二次查询。
到此这篇关于mysql范围查找时索引不生效问题原因分析的文章就介绍到这了,更多相关mysql范围查找索引不生效内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://blog.csdn.net/qq_25188255/article/details/81316498
- nginx宝塔配置修改(宝塔面板默认的404页面不生效怎么办Nginx?)
- dockerswarm网络架构(docker swarm外部验证负载均衡时不生效的解决方案)
- elementui的el-popover修改样式不生效的解决(elementui的el-popover修改样式不生效的解决)
- 修改mysql默认超时(MySQL sql_mode修改不生效的原因及解决)
- mysql的日志怎么清除(MySQL删除了记录不生效的原因排查)
- mysql索引失效的几种情况(MySql范围查找时索引不生效问题的原因分析)
- 怎么用docker中的mysql连接数据库(解决Docker之mysql容器数据库更改不生效的问题)
- 宝塔面板端口一般设置多少合适(宝塔面板设置了防盗链不生效如何解决)
- docker配置源不生效(解决docker CMD/ENTRYPOINT执行sh脚本报: not found/run.sh:)
- 菲律宾安全吗(菲律宾安全吗)
- 泰国旅游攻略(泰国旅游攻略必去景点)
- 数字藏品市场有多乱 周杰伦丢了 一只猴 ,损失超300万(数字藏品市场有多乱)
- 这里输入关键词(怎么输入关键词搜索)
- 得这个 难治病 的人太多了,300个人赶到杭州商量怎么办(得这个难治病的人太多了)
- 经度,世界时间腕表的灵魂(世界时间腕表的灵魂)
热门推荐
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9