MySQL范围查询优化的场景实例详解
作者:lfwh 发布时间:2024-01-17 01:11:12
思考题
假设有一张订单表 order,主要包含了主键订单编码 order_no、订单状态 status、提交时间 create_time 等列,并且创建了 status 列索引和 create_time 列索引。此时通过创建时间降序获取状态为 1 的订单编码,以下是具体实现代码:
select order_no from order where status =1 order by create_time desc;
你知道其中的问题所在吗?我们又该如何优化?
解析
status和create_time单独建索引,在查询时只会遍历status索引对数据进行过滤,不会用到create_time列索引,将符合条件的数据返回到server层,在server对数据通过快排算法进行排序,Extra列会出现file sort;
应该利用索引的有序性,在status和create_time列建立联合索引,这样根据status过滤后的数据就是按照create_time排好序的,避免在server层排序
对的,为了避免文件排序的发生。因为查询时我们只能用到status索引,如果要对create_time进行排序,则需要使用文件排序filesort。
filesort是通过相应的排序算法将取得的数据在内存中进行排序,如果内存不够则会使用磁盘文件作为辅助。虽然在一些场景中,filesort并不是特别消耗性能,但是我们可以避免filesort就尽量避免。
阿里巴巴MySQL规范
【推荐】 如果有 order by 的场景,请注意利用索引的有序性。 order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。
正例: where a=? and b=? order by c; 索引: a_b_c
反例: 索引如果存在范围查询, 那么索引有序性无法利用,如: WHERE a>10 ORDER BY b; 索引 a_b 无 法排序
范围查询-基础
讲联合索引,一定要扯最左匹配!
最左匹配 所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配,值得注意的是,当遇到范围查询(>、<、between、like)就会停止匹配。 假设,我们对(a,b)字段建立一个索引,也就是说,你where后条件为
a = 1
a = 1 and b = 2
是可以匹配索引的。但是要注意的是~你执行
b= 2 and a =1
也是能匹配到索引的,因为Mysql有优化器会自动调整a,b的顺序与索引顺序一致。 相反的,你执行
b = 2
就匹配不到索引了。 而你对(a,b,c,d)建立索引,where后条件为
a = 1 and b = 2 and c > 3 and d = 4
那么,a,b,c三个字段能用到索引,而d就匹配不到。因为遇到了范围查询!
场景一: a = 1 and b = 2 and c = 3
如果sql为
SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;
如何建立索引?
如果此题回答为对(a,b,c)建立索引,那都可以回去等通知了。
此题正确答法是,(a,b,c)或者(c,b,a)或者(b,a,c)都可以,重点要的是将区分度高的字段放在前面,区分度低的字段放后面。像性别、状态这种字段区分度就很低,我们一般放后面。
例如假设区分度由大到小为b,a,c。那么我们就对(b,a,c)建立索引。在执行sql的时候,优化器会 帮我们调整where后a,b,c的顺序,让我们用上索引。
阿里巴巴Java 开发手册
【强制】 在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据 实际文本区分度决定索引长度。
说明: 索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%以上,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
场景二: a > 1 and b = 2
如果sql为
SELECT * FROM table WHERE a > 1 and b = 2;
如何建立索引?
如果此题回答为对(a,b)建立索引,那都可以回去等通知了。
此题正确答法是,对(b,a)建立索引。如果你建立的是(a,b)索引,那么只有a字段能用得上索引,毕竟最左匹配原则遇到范围查询就停止匹配。
如果对(b,a)建立索引那么两个字段都能用上,优化器会帮我们调整where后a,b的顺序,让我们用上索引。
场景三:a > 1 and b = 2 and c > 3
如果sql为
SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3;
如何建立索引? 此题回答也是不一定,(b,a)或者(b,c)都可以,要结合具体情况具体分析。
拓展一下
SELECT * FROM `table` WHERE a = 1 and b = 2 and c > 3;
怎么建索引?嗯,大家一定都懂了!
场景四: a > 1 ORDER BY b
SELECT * FROM `table` WHERE a = 1 ORDER BY b;
如何建立索引? 这还需要想?一看就是对(a,b)建索引,当a = 1的时候,b相对有序,可以避免再次排序! 那么
SELECT * FROM `table` WHERE a > 1 ORDER BY b;
如何建立索引?
对(a)建立索引,因为a的值是一个范围,这个范围内b值是无序的,没有必要对(a,b)建立索引。
拓展一下
SELECT * FROM `table` WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;
怎么建索引?
场景五: a IN (1,2,3) and b > 1
SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1;
如何建立索引?
还是对(a,b)建立索引,因为IN在这里可以视为等值引用,不会中止索引匹配,所以还是(a,b)!
拓展一下
SELECT * FROM `table` WHERE a = 1 AND b IN (1,2,3) AND c > 3 ORDER BY c;
如何建立索引?此时c排序是用不到索引的。
来源:https://blog.csdn.net/weixin_42326851/article/details/124993822
猜你喜欢
- 本文实例为大家分享了微信小程序实现星星评价效果的具体代码,供大家参考,具体内容如下代码实现wxml文件<!--pages/evalua
- 前言:Tkinter模块的Treeview组件类似于Dev中的treelist控件,但前者还可以当做树控件和表格控件使用,虽然功能可能没有d
- Pytorch把Tensor转化成图像可视化在调试程序的时候经常想把tensor可视化成来看看,可以这样操作:from torchvisio
- 爬虫思路初步尝试我先查看了network,并没有发现有可用的API;然后又用bs4去分析英雄列表页,但是请求到html里面,并没有英雄列表,
- 函数签名对象,表示调用函数的方式,即定义了函数的输入和输出。在Python中,可以使用标准库inspect的一些方法或类,来操作或创建函数签
- 前面说到了urllib2的简单入门,下面整理了一部分urllib2的使用细节。1.Proxy 的设置urllib2 默认会使用环境变量 ht
- 为什么会讲 MRO?在讲多继承的时候,有讲到, 当继承的多个父类拥有同名属性、方法,子类对象调用该属性、方法时会调用哪个父类的属性、方法呢?
- Numpy模块被广泛用于科学和数值计算,自然有它的强大之处,之前对于特征处理中需要进行数据列表或者矩阵拼接的时候都是自己写的函数来完成的,今
- Memento备忘录模式 备忘录模式一个最好想象的例子:undo! 它对对象的一个状态进行了'快照', 在你需要的时候恢复原
- 本文记录的要实现的功能类似于 MySQL 中的 ORDER BY,上个项目中有遇到这样的一个需求。 要求:从两个不同的表中获取各自的4条数据
- 通常来说Python中任何值都是一个对象,因此任何类型(int、str、list…)都是一个类。而类就必然有它的方法或属性,我们要记下这么多
- 本文为大家分享了python实现俄罗斯方块游戏,继上一篇的改进版,供大家参考,具体内容如下1.加了方块预览部分2.加了开始按钮在公司实习抽空
- 本文介绍了python OpenCV学习笔记之直方图均衡化,分享给大家,具体如下:官方文档 – https://docs.opencv.or
- pycharm设置Console控制台输出自动换行解决方法File --> Settings… --> E
- 计算两个信号的交叉谱密度结果展示:完整代码:import numpy as npimport matplotlib.pyplot
- urllib3是一款Python 3的HTTP客户端。Python标准库提供了urllib。在Python 2中,另外提供了urllib2;
- centos下安装配置phpmyadmin,我花了二个晚上,郁闷的我不行,配置phpmyadmin简单吧,很简单,我刚工作的时候,就配置过,
- 引言:2020年12月20python宣布适配苹果m1芯片,这意味着python3.9.0可以不经过rosetta转化,以原生的方式运行在最
- 最近在跑程序,然后Pycharm就跳出out of memory 的错误提示,可能是由于读取的数据太多导致的,Pycharm有一个默认内存的
- 一、朋友圈九宫格效果图二、图片基本操作打开要处理的图片判断打开的图片是否为正方形如果是正方形,就进行九等分,如果不是正方形,先用白色填充为正