首页 > 数据库 >SQL 查询优化之 WHERE 和 LIMIT 使用索引详解

SQL 查询优化之 WHERE 和 LIMIT 使用索引详解

时间:2024-08-23 08:55:13浏览次数:11  
标签:idx sql 索引 LIMIT SQL acct 排序 WHERE id

奇怪的慢sql

我们先来看2条sql

第一条:

第二条:

表的索引及数据总情况: 索引:acct_id,create_time 分别是单列索引,数据库总数据为500w。

通过 acct_id 过滤出来的结果集在 1w 条左右。 查询结果:第一条要5.018s,第二条0.016s

为什么会是这样的结果呢?第一,acct_id和create_time都有索引,不应该出现5s查询时间这么慢啊 那么先来看执行计划

第一条sql执行计划:


 第二条执行计划:


仔细观察会发现,索引只使用了idx_create_time,没有用到idx_acct_id。

这能解释第一条sql很慢,因为where查询未用到索引,那么第二条为什么这么快?看起来匪夷所思,其实搞清楚mysql查询的原理之后,其实很简单。 我们来看这2条sql查询,都用到了where order by limit。

当有limit存在时,查询的顺序就有可能发生变化,这时并不是从数据库中先通过where过滤再排序再limit。

因为如果这样的话,从500万数据中通过where过滤就不会是5s了。 此时的执行顺序是,先根据idx_create_time索引树,从最右侧叶子节点,反序取出n条,然后逐条去跟where条件匹配。

若匹配上,则得出一条数据,直至取满10条为止,为什么第二条sql要快,因为运气好,刚好时间倒序的前几条就全部满足了。 搞清楚原理之后,我们了解了为什么第一条慢,第二条快的原因,但是问题又来了为什么mysql不用idx_acct_id索引,这是一个问题,因为这样的话,我们的建立的索引基本失效了,在此类sql下查询效率将会是相当低。 因为通过acct_id过滤出来的结果集比较大,有上万条,mysql认为按时间排序如果不用索引,将会是filesort,这样会很慢,而又不能2个索引都用上,所以选择了idx_create_time。 

为什么mysql只用一个索引

这里为什么不能2个索引都用上,可能很多人也不知道为什么,其实道理很简单,每个索引在数据库中都是一个索引树,其数据节点存储了指向实际数据的指针,如果用一个索引来查询,其原理就是从索引树上去检索,并获得这些指针,然后去取出数据。

试想,如果你通过一个索引,得到过滤后的指针,这时,你的另一个条件索引如果再过滤一遍,将得到2组指针的集合,如果这时候取交集,未必就很快,因为如果每个集合都很大的话,取交集的时候,等于扫描2个集合,效率会很低,所以没法用2个索引。

当然有时候mysql会考虑临时建立一个联合索引,将2个索引联合起来用,但是并不是每种情况都能奏效,同样的道理,用一个索引检索出结果集之后,排序时,也无法用上另一个索引了。 实际上用索引idx_acct_id大多数情况还是要比用索引idx_create_time要快,我们举个例子:

耗时:0.057s

可以看出改情况用idx_acct_id索引是比较快的,那么是不是这样就可以了呢,排序未用上索引,始终是有隐患的。 

联合索引让where和排序字段同时用上索引

我们来看下一条sql:

耗时: 1.999s

执行计划:


该sql通过acct_id过滤出来的结果集有100万条,因此排序将会耗时较高,所幸这里只是取出前10条最大的然后排序
查询概况,我们发现时间基本消耗在排序上,其实这是内存排序,对内存消耗是很高的。 


那么我们有没有其它解决方案呢,这种sql是我们最常见的,如果处理不好,在大数据量的情况下,耗时以及对数据库资源的消耗都很高,这是我们所不能接受的,我们的唯一解决方案就是让where条件和排序字段都用上索引 解决办法就是建立联合索引:

然后执行sql:

 

 

耗时: 0.016s

联合索引让where条件字段和排序字段都用上了索引,问题解决了!

联合索引使用的原理

但是为什么能解决这个问题呢,这时大家可能就会记住一个死理,就是联合索引可以解决where过滤和排序的问题,也不去了解其原理,这样是不对的,因为当情况发生变化,就懵逼了,

下面我们再看一个sql:

耗时:1.391s

索引还是用idx_acct_id_create_time,时间居然慢下来了。

执行计划是: 


看执行计划,排序用到了filesort,也就是说,排序未用到索引。 那么我们还是来看看,索引排序的原理,我们先来看一个sql:

耗时:0.029s

执行计划为:


这里执行的步骤是,先从索引树中,按时间升序取出前100条,因为索引是排好序的,直接左序遍历即可了,因此,这里mysql并没有做排序动作,如果想降序,则右序遍历索引树,取出100条即可,查询固然快,那么联合索引的时候,是怎样的呢?

使用组合索引:idx_acct_id_create_time。这个时候,因为acct_id是联合索引的前缀,因此可以很快实行检索,如果sql是

出来的数据是按如下逻辑排序的3095+time13095+time23095+time3默认是升序的,也就是说,次sql相当于

他们是等效的。

如果我们把条件换成order by create_time desc limit 0,10呢?

这时候,应该从idx_acct_id_create_time树右边叶子节点倒序遍历,取出前10条即可

因为数据的前缀都是3095,后缀是时间升序。那么我们倒序遍历出的数据,刚好满足 order by create_time desc。因此也无需排序。 那么语句:

为什么排序无法用索引呢?

我们先分析下索引的排序规则,已知:id1<id2<id3…  time1<time2<time3….

查询结果集排序如下:

索引出来的默认排序是这样的,id是有序的,时间是无序的,因为有2个id,优先按id排序,时间就是乱的了,这样排序将会用filesort,这就是慢的原因,也是排序没有用到索引的原因。

查询计划使用以及使用说明

table:显示这一行数据是关于哪张表的
type:显示使用了何种类型,从最好到最差的连接类型为const,eq_ref,ref,range,index,all
possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引
key:实际使用的索引,如果为null,则没有使用索引。
key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
rows:mysql认为必须检查的用来返回请求数据的行数

标签:idx,sql,索引,LIMIT,SQL,acct,排序,WHERE,id
From: https://www.cnblogs.com/gdjgs/p/18221754

相关文章

  • MySQL存储引擎
    一、简介数据库存储引擎是数据库底层软件组件,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。现在许多不多的数据库管理系统都支持多种不同的数据引擎......
  • 【面试】一文搞懂MySQL的分库分表!
            MySQL分库分表是一种数据库优化手段,通常用于应对数据量巨大、并发量高的场景。随着系统数据的增长,单个数据库可能难以承受高负载,进而影响性能和可扩展性。分库分表的目标是将数据分散到多个库或表中,从而减少单一库或表的压力,提高系统性能。目录一我们为什......
  • 2.SQL
    SQL一.区分SQL二.基础运行逻辑1.建立客户端与服务端的链接2.查询过程三.库和表的增删改操作1.对于库的代码操作2.对于表的代码操作四.表的查询1.普通查询操作2.子查询和链接查询3.窗口函数【sum()over(partitionby字段orderby字段desc)】一.区分SQL mysql:......
  • MySQL面试问题(二)
    MySQL面试问题(二)文章目录MySQL面试问题(二)为什么要使用索引索引是不是越多越好MySQL索引机制什么是聚簇索引没有主键innodb如何处理联合索引批量向MySQL中导入1000w数据如何优化分页时偏移量很大效率很差如何优化大数据量高并发访问数据库优化方法为什么要使用索引......
  • python 05-标准库:csv、json、sqlite3、datetime模块
    csv模块importcsv#data.csv不存在时,会现在当前目录下创建一个data.csv文件withopen("data.csv","w",encoding='utf-8')asfile:writer=csv.writer(file)writer.writerow(["trasanction_id","product_id","pric......
  • sqlilabs less21-25关手工注入
    第21关一.登录页面二.BurpSuite抓包,进入重放器三.查询数据库先进行编码')andupdatexml(1,concat(1,database()),1)#四.查表,先进行编码')andupdatexml(1,concat(1,(selectgroup_concat(table_name)frominformation_schema.tableswheretable_schema='secur......
  • 织梦dedecms使用sql语句获取文章链接地址
    在织梦DeDeCMS中,可以通过SQL语句结合织梦特有的标签来获取文章链接地址。以下是如何使用SQL语句获取文章链接地址的具体步骤和示例。1.SQL语句织梦CMS允许在模板中使用自定义SQL语句来获取数据。这可以通过 {dede:sql} 标签来实现。2.获取文章链接地址SQL语句:编写SQL语句......
  • SQLite 和 DB Browser SQLite介绍安装
    SQLite介绍SQLite是轻量级的嵌入式数据库,‌无需独立服务器进程(不用独占服务器的资源),‌适合单用户或低并发场景;‌SQLite功能相对简单,‌不支持存储过程、‌触发器等高级特性,‌数据安全性较低;‌SQLite适用于嵌入式设备、‌移动应用和轻量级桌面应用DBBrowserSQ......
  • SQLite数据库
    一、概念:对于一般小批量的数据存放且掉电数据不会丢失,软件的配置及想要持续保存的数据会放入文件中,而对于大批量数据存储和管理时则使用数据库 二、类型:1、关系型数据库 (1)大型数据库:Oracle、DB2(2)中型数据库:MySQL(开源)、SQLServer(3)小型数据库:SQLite 虽然是小型数......
  • sqli-labs靶场通关攻略 21-25
    主页有sqli-labs靶场通关攻略1-20第二一关less-21步骤一:输入Username:adminPassword:admin利用Burpsutie进行抓包步骤二:在Cookie后输入'报错,判断闭合方式为')#对所写代码进行如下操作:选中右击->Convertselection->Base64->Base64-encode步骤三:判断列数')order......