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是⾛了索引的。
mysql下载不了什么原因3 总结
mysql 是否根据⾸次索引条件查询出的主键进⾏⼆次查，也是要看查询出来的数据量级，如果数据量接近全表数据量的话，就会进⾏全表扫描，否则根据第⼀次查询出来的主键进⾏⼆次查询。
到此这篇关于MySql范围查时索引不⽣效问题原因分析的⽂章就介绍到这了，更多相关MySql范围查索引不⽣效内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。