MySql范围查找时索引不生效问题的原因分析
作者:qq_25188255 发布时间:2024-01-12 14:42:33
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 是否根据首次索引条件查询出的主键进行二次查找,也是要看查询出来的数据量级,如果数据量接近全表数据量的话,就会进行全表扫描,否则根据第一次查询出来的主键进行二次查询。
来源:https://blog.csdn.net/qq_25188255/article/details/81316498
猜你喜欢
- 列表A是一个通过扩张对象浏览器(object explorer)中可编程性节点而建立的实例,选择存储过程,然后右击并选择新的存储过程。 许多
- 1、把这段拷到DW里,存成HTML文件。<HTML><HEAD><TITLE>move backgrou
- 启发式评估法(Heuristic Evaluation)是一种用来发现用户界面设计中的可用性问题从而使这些问题作为再设计过程中的一部分被重视
- 用ASP代码实现对access数据库的在线压缩处理,注意压缩前请备份数据库。我们知道每个一段时间压缩一下access数据库,可以减少数据库的
- 代码如下:CREATE DATABASE 临时的 ON ( NAME='临时的_Data', FILENAME='h
- 从低版本迁移到MySQL 8后,可能由于字符集问题出现 Illegal mix of collations (utf8mb4_general
- bootstrap自带的响应式导航栏是向下滑动的,有时满足不了个性化的需求,需要做一个类似于android drawerLayout 侧滑的
- 如今,体育运动的热潮日益流行。同样,以不正确的方式进行运动的风险也在增加。有时可能会导致严重的伤害。考虑到这些原因,提出一种以分析运动员的关
- 能够操控日期和时间对于大多数编程语言来说是基本的能力,Perl也不例外。但是在碰到与时间相关的更加复杂的处理时,Perl自带的功能常常就显得
- 1、问题描述在使用v-model指令实现输入框数据双向绑定,输入值时对应的这个变量的值也随着变化;但是这里不允许使用v-model,需要写一
- 前言在Django中有大量的通用类视图,例如ListView,DetailView,CreateView,UpdateView等等,将所有重
- 单例模式(Singleton Pattern) 是一种常用的软件设计模式,该模式的主要目的是确保某一个类只有一个实例存在。当你希望在整个系统
- 本文以一个简单的实例讲述了python实现斐波那契数列数列递归函数的方法,代码精简易懂。分享给大家供大家参考之用。主要函数代码如下:def
- 这篇文章主要介绍了python实现XML解析的方法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋
- 方法对比:使用df[(df[“a”] > 3) & (df[“b&
- 使用Python的pillow模块 random 模块随机生成验证码图片,并应用到Django项目中安装pillow$ pip3 insta
- 等间距取值a = np.linspace(float(-pi), float(pi), 100)从-pi到pi取100个值对区间进行等间距取
- asp函数实现把数字格式化为每3个数字时以逗号间隔的数字见下:<%Function Comma(str)If No
- 前言今天我的 PyCharm 突然间就死掉了,双击图标,等半天没有反应,也没有抛出什么错误。打开任务管理器,发现双击时启动了一个PyChar
- 前言项目中会有点到直线距离计算、两条直线交点坐标计算、两条直线夹角计算的需求。一、点到直线距离计算由于项目中得到点的坐标最容易,因此采用向量