MySql范围查找时索引不生效问题的原因分析
16lz
2021-12-14
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;
EXPLAIN SELECT genderFROM employeesORDER BY first_name, last_name
继续进行试验,对查询语句进一步改写,加上一个范围查找:
EXPLAIN SELECT genderFROM employeesWHERE first_name > 'Leah'ORDER BY first_name, last_name
这里发现结果和第一次sql分析无异。继续试验。
改写sql语句:
EXPLAIN SELECT genderFROM employeesWHERE 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 employeesORDER BY first_name, last_name
SELECT COUNT(first_name)FROM employeesWHERE first_name > 'Leah'ORDER BY first_name, last_name
SELECT COUNT(first_name)FROM employeesWHERE first_name > 'Tzvetan'ORDER BY first_name, last_name
3 总结
mysql 是否根据首次索引条件查询出的主键进行二次查找,也是要看查询出来的数据量级,如果数据量接近全表数据量的话,就会进行全表扫描,否则根据第一次查询出来的主键进行二次查询。
更多相关文章
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- [android源码下载索引贴】微信+二维码那都不是事......
- android触控,先了解MotionEvent
- 系出名门 Android(安卓)系列文章索引
- 系出名门 Android(安卓)系列文章索引
- 系出名门 Android(安卓)系列文章索引
- 滑条自动跳转,显示控件的新条目
- 系出名门 Android(安卓)系列文章索引
- Android(安卓)自定义Gallery