Mysql 竟然还有这么多不为人知的查询优化技巧,还不看看?

前言


Mysql 我随手造200W条数据,给你们讲讲分页优化 

MySql 索引失效、回表解析

今天再聊聊一些我想分享的查询优化相关点。


正文

准备模拟数据。

首先是一张 test_orde 表:

CREATE TABLE `test_order` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`p_sn` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`t_sn` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`type` TINYINT(4) NULL DEFAULT NULL,
	`create_time` DATETIME NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)

然后是一个存储过程 :

BEGIN                                
    DECLARE num INT DEFAULT 2000000;         
    DECLARE i INT DEFAULT 0; 
    
    WHILE i < num DO             
    INSERT INTO test_order(`p_sn`,`t_sn`,`type`,`create_time`) 
         VALUES(CONCAT('SN',i),UUID(),1,now());
        SET i =  i + 1;
    END WHILE;        
END

 执行存储过程,看下模拟数据:


开始。 

① 使用 count 、 group by  注意点

比如, 我们想统计一下 当前 表里面, 根据type维度 分别有多少 数据 :
 

SELECT COUNT(*) ,type
FROM test_order GROUP BY TYPE ;

目前可以看到我们现在数据库表 里面,其实type 就 1个 , 就是 1 。

真实场景,我们 肯定不止一个type。 

改造出模拟数据(尽量使数据更随机,真实业务场景也许会更加更加散乱):
 

将数据里面 id 是 7的 倍数的数据  的type 改成  5;

将数据里面 id 是 5 的 倍数的数据  的type 改成  2;

将数据里面 id 是 3 的 倍数的数据  的type 改成  4;

将数据里面 id 是 2 的 倍数的数据  的type 改成  3;

sql: 

UPDATE test_order a
INNER JOIN test_order b ON b.id % 7=0 AND a.id=b.id SET a.TYPE =5

UPDATE test_order a
INNER JOIN test_order b ON b.id % 5=0 AND a.id=b.id SET a.TYPE =2

UPDATE test_order a
INNER JOIN test_order b ON b.id % 3=0 AND a.id=b.id SET a.TYPE =4

UPDATE test_order a
INNER JOIN test_order b ON b.id % 2=0 AND a.id=b.id SET a.TYPE =3

看看效果 :

统计出 表里面 不同 type 类型 的 数据分别有多少条 ,且看看时间用了多久:

 

 看看 EXPLAIN :

 Using filesort : 通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所以并不是通过索引直接返回排序结果的排序都叫 FileSort 排序

可以看到,分析里面 出现了一个 using filesort , 这个玩意就是慢的原因。

可以看到 用到了 group by type , 返回来的数据 TYPE 是 1,2,3,4,5 默认 升序排好的。

是的,相当于 mysql 默认帮我们执行了排序, 无疑 这是需要花时间的。

所以说,当我们仅仅要的是 不同 type 数据的 统计数量结果, 那么我们是可以优化掉这个排序的耗时的。 

优化技巧 :


order by null 

我们在 group by 后面 加上 ORDER BY  NULL , 强制禁止排序  ,

看看效果 :

那有没有更加快的优化? 

有的, 加索引。 group by 是能命中索引的。

 加完索引效果:


②使用 left join  / right join 的注意点

关联查询, 比如 有 A 、 B  两个表 。

A表即是 我们的 test_order 表 200W条数据:

而B 表 是 test_order_detail 表  5W 条数据:

这两个表通过id、order_id 关联(简单举个例子)。

注意点:

1.当使用left join时,左表是驱动表,右表是被驱动表
2.当使用right join时,右表是驱动表,左表是被驱动表
3.当使用inner join时,mysql会默认自动选择数据量比较小的表作为驱动表,大表作为被驱动表

我们尽量要保证 小表 驱动 大表, 大小指的是数据量。

那么我们看 left join 来看看效果, A表 test_order 目前是大表  B表  test_order_detail是小表 效果:

我们使用  left join  , 故意把 大数据表放在 左, 小数据表放在右, 这时候 左大驱右小 ,

发现用了13秒,返回的是 200万条数据 
 

看看EXPLAIN分析情况: 

ps :
当查询引擎完成对行的计数时,结果集的其余部分出现。所以Heidi所谓的“网络时间”是计算行数的时间。这对于MyISAM来说实际上是瞬间的,而InnoDB需要一段时间。(heidiSQL编辑器)

那么如果我们反过来, 左小驱右大 :
 

 发现用了0.29秒,返回的是 5万条数据 

 看看EXPLAIN分析情况: 

可以看到 小表驱动大表的情况,时间效果的差距所在。

所以根据业务情况,必须要清晰地使用上 这个优化技巧 ,尽可能保证小表驱动大表。

为什么 ?

其实这个道理很简单, 驱动表 和 被 驱动表 , 就相当于 2层 for 循环遍历。

比如 大表200万数据 驱动 小表 5万数据 ,就是 :

for(int  驱动表行数=0 ;  驱动表行数 <20000000; 驱动表行数++){
    
    for (int 被驱动表行数=0 ;  被驱动表行数<50000;  被驱动表行数++){
        
         找出 驱动表行记录 条件  等于  被驱动表行记录 条件值
    }
    
}

那可能很多初学者还是不明白, 放外面是 200W 循环,里面再嵌套 5W  是 200 乘以 5 ?

那跟反过来5 乘以 200 有什么区别?  

简析:

可以看到上述的  EXPLAIN 大表驱动小表 或是 小表驱动大表, 可以看到 驱动表的索引都是不生效的, 生效的是 被驱动表的索引 。 

索引是b+树,在索引上等值查询的时间复杂度为logN。


因为驱动表不走索引,需要全表扫描,而被驱动表可以建立索引加速查找。


若小表驱动大表,则时间复杂度为 5W*log200W
若大表驱动小表,则时间复杂度为 200W*log5W
 

所以 为什么 时间耗时久 ,也就显然得知了。

是因为被驱动表又能命中索引,而且时间查找又快啊。


 ③ 对字段进行表达式操作 的注意点

比如 我们 想查出来 type  是  2 的 2倍 的数据 (这里简单用type举例, 可能业务上更多是 传入一个参数,然后触发某某计算倍数的概念):

当我们 把 字段 type 融入到 表达式 里面时,可以看到 耗时 是 2.45+秒 (因为索引失效了):

  看看EXPLAIN分析情况: 

而我们把 type 字段 抽出来,不参与 表达式操作,我们发现效果一样,但是耗时只有 1.3 秒(因为能命中索引) :

  看看EXPLAIN分析情况: 

 ④ 对明确知道的条件值 使用 or 查询  还是  UNION ALL ,有说法

比如我们想查出表里面 type 是1  或者 type 是 5的 数据 , 如果我们使用 or 去实现 ,大家知道的,使用or 是命中不了索引的,会全表扫描 。
 


很多这种时候,大家可能就会想, 遇到or 慢查询, 就换成  UNION ALL 呗 。
 

其实并不然 。

你可以理解为,当你使用or 查询 发现慢的时候, 你可以尝试使用UNION ALL 去替代调试 , 注意,是调试, 如果性能确实优化了,你就可以替代。

直接眼见为实 :

首先可以看到 union all  比 or 还要慢 。

甚至 还可以看看 in 的效果 ,也是跟 or 基本一致 也是 3秒 左右 。

我们看看 使用 in的 EXPLAIN :

 再看看 使用 or  的 EXPLAIN  :

or 和 in  几乎是一样的 在不中索引的时候。

那看看   union all 的  EXPLAIN :
 

可以看到命中了 索引的。

但是为什么这时候   union all 反而慢呢? 

原因 :

1. 其实我们可以关注到 rows 和 filtered  

2. 数据量情况 以及散乱程度 

当全表扫描 98% 的数据 都是需要的, 一次扫描拿出结果。

而 union all 进行了 2次 扫描,虽然扫的是索引,但是扫了96万 + 99 万 数据, 我们一共才200W数据。

 2次加起来 跟我们 全部扫描看到的row 199万 基本没区别。 

这时候就是看 数据的分布情况了。

继续看看 查询 三个 type :

使用 OR  :

 使用  union all  :

 再再再顺便再贴一个 示例 (查询不同字段条件值的场景),让大家知道 or 和  union all  就是需要看实际情况调试使用的 :

所以 什么时候用 or  什么时候 用  union all  , 非绝对, 要调试为准(特别是当你的union all 条件的字段也没索引的时候 ,你想想扫描多次表的效率)! 


⑤ order by  的效能 提升 

先改造一下表  :


平时我们写代码,很多时候,我们一些复杂的业务sql拆分,我们很愿意去拆,提高效率。

但是遇到排序, 我个人就很懒,基本 就是 丢到sql上面 order by 了。

那么 这就有说法了。

模拟点数据 :

UPDATE test_order a
INNER JOIN test_order b ON b.id % 7=0 AND a.id=b.id SET a.i_amount =99;
UPDATE test_order a
INNER JOIN test_order b ON b.id % 5=0 AND a.id=b.id SET a.i_amount =66;
UPDATE test_order a
INNER JOIN test_order b ON b.id % 3=0 AND a.id=b.id SET a.i_amount =588;
UPDATE test_order a
INNER JOIN test_order b ON b.id % 2=0 AND a.id=b.id SET a.i_amount =88;

 可以看到现在 数据 有那么一些些乱了,可以来讲讲 order by 排序了 :

这时,如果我们 进行 组合 排序, 按照 i_amount  排序 然后再按照 type 排序, 我们会发现 ,引擎有脾气,没有中索引,但是 在 extra上面 有说 用了 using filesort   


 

时间肯定是没有 直接用上 index 快的 :

 所以我们给它整活, 我们升级成组合索引 :

 

 这时候我们再执行,发现 可以命中了index 了:

好了,就先讲到这吧, 有空再讲其他。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/1205.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Spring事务和事务传播机制

目录 Spring中事务的实现 1、通过代码的方式手动实现事务 2、通过注解的方式实现声明式事务 2.1、Transactional作用范围 2.2、Transactional参数说明 2.3、注意事项 2.4、Transactional工作原理 事务隔离级别 1、事务特性 2、Spring中设置事务隔离级别 2.1、MySQL事…

Linux:函数指针做函数参数

#include <stdio.h> #include <stdlib.h> //创建带有函数指针做参数的函数框架api //调用者要先实现回调函数 //调用者再去调用函数框架 //所谓的回调是指 调用者去调用一个带有函数指针做参数的函数框架&#xff0c;函数框架反过来要调用调用者提供的回调函数 …

蓝桥杯冲击-02约数篇(必考)

文章目录 前言 一、约数是什么 二、三大模板 1、试除法求约数个数 2、求约数个数 3、求约数之和 三、真题演练 前言 约数和质数一样在蓝桥杯考试中是在数论中考察频率较高的一种&#xff0c;在省赛考察的时候往往就是模板题&#xff0c;难度大一点会结合其他知识点考察&#x…

全面剖析OpenAI发布的GPT-4比其他GPT模型强在哪里

最强的文本生成模型GPT-4一、什么是GPT-4二、GPT-4的能力三、和其他GPT模型比较3.1、增加了图像模态的输入3.2、可操纵性更强3.3、复杂任务处理能力大幅提升3.4、幻觉、安全等局限性的改善3.6、风险和缓解措施改善更多安全特性3.7、可预测的扩展四、与之前 GPT 系列模型比较五、…

QT入门Item Views之QListView

目录 一、QListView界面相关 1、布局介绍 二、代码展示 1、创建模型&#xff0c;导入模型 2、 设置隔行背景色 3、删除选中行 三、源码下载 此文为作者原创&#xff0c;创作不易&#xff0c;转载请标明出处&#xff01; 一、QListView界面相关 1、布局介绍 先看下界面…

高完整性系统工程(三): Logic Intro Formal Specification

目录 1. Propositions 命题 2.1 Propositional Connectives 命题连接词 2.2 Variables 变量 2.3 Sets 2.3.1 Set Operations 2.4 Predicates 2.5 Quantification 量化 2.6 Relations 2.6.1 What Is A Relation? 2.6.2 Relations as Sets 2.6.3 Binary Relations as…

ZYNQ硬件调试-------day2

ZYNQ硬件调试-------day2 1.ILA&#xff08;Integrated Logic Analyzer &#xff09; 监控逻辑内部信号和端口信号;可以理解为输出。可单独使用 2.VIO&#xff08;Virtual Input/Output &#xff09; 实时监控和驱动逻辑内部信号和端口信号&#xff0c;可以理解为触发输入。不可…

第十四届蓝桥杯三月真题刷题训练——第 14 天

目录 第 1 题&#xff1a;组队 题目描述 运行限制 代码&#xff1a; 第 2 题&#xff1a;不同子串 题目描述 运行限制 代码&#xff1a; 思路&#xff1a; 第 3 题&#xff1a;等差数列 题目描述 输入描述 输出描述 输入输出样例 运行限制 代码&#xff1a; 思…

Dubbo原理简介

Dubbo缺省协议采用单一长连接和NIO异步通讯&#xff0c;适合于小数据量大并发的服务调用&#xff0c;以及服务消费者机器数远大于服务提供者机器数的情况。 作为RPC&#xff1a;支持各种传输协议&#xff0c;如dubbo,hession,json,fastjson&#xff0c;底层采用mina,netty长连接…

nginx详解(概念、Linux安装、配置、应用)

1.nginx是什么 百度百科 看百度百科的解释&#xff0c;第一句话就是错的。“Nginx (engine x) 是一个高性能的HTTP和反向代理web服务器”&#xff0c;从语法来看&#xff0c;去掉形容词就是&#xff1a;Nginx是服务器&#xff0c;nginx怎么会是服务器呢&#xff0c;nginx只是一…

Matlab进阶绘图第8期—聚类/分类散点图

聚类/分类散点图是一种特殊的特征渲染散点图。 聚类/分类散点图通过一定的聚类、分类方法&#xff0c;将特征相近的离散点划分到同一个类别中&#xff0c;进而将每个离散点赋予类别标签&#xff0c;并利用不同的颜色对不同的类别进行区分。 本文使用Matlab自带的gscatter函数…

C语言变量和数据类型的使用

文章目录前言一、将变量输出打印到控制台1.整形变量的输出2.浮点型变量的输出1.flaot的输出2.doble的输出3.float和double输出的区别4.%f,%10.2f......二、数据类型的大小总结前言 上一篇文章我们学习了C语言变量和数据类型的基本概念那么今天我们就具体的来看看如何在代码中使…

css实现文字大小自适应

在页面编写中经常会碰到页面自适应的问题&#xff0c;也就是页面内部的元素会随着窗口的放大缩小而放大缩小&#xff0c;box可以通过calc 百分比的形式做到页面自适应&#xff0c;但是box内的字体却无法做到这点&#xff0c;往往box自适应大小了&#xff0c;内部的字体还是原来…

selenium(5)-------自动化测试脚本(python)

1)alert框的处理 前提:我们是不可以通过控制台直接定位元素的方式去选中这个alert框的&#xff0c;例如说xpath直接进行定位元素 1)先获得弹框的操作句柄:alertdriver.switch_to.alert 2)再次调用accept方法进行关闭弹窗:alert.accept() from selenium import webdriver import…

强化学习分类与汇总介绍

1.强化学习&#xff08;Reinforcement Learning, RL&#xff09; 强化学习把学习看作试探评价过程&#xff0c;Agent选择一个动作用于环境&#xff0c;环境接受该动作后状态发生变化&#xff0c;同时产生一个强化信号(奖或惩)反馈给Agent&#xff0c;Agent根据强化信号和环境当…

【python刷题】leecode官方提示“->“,“:“这些符号是什么意思?什么是Type Hints?

作者&#xff1a;20岁爱吃必胜客&#xff08;坤制作人&#xff09;&#xff0c;近十年开发经验, 跨域学习者&#xff0c;目前于海外某世界知名高校就读计算机相关专业。荣誉&#xff1a;阿里云博客专家认证、腾讯开发者社区优质创作者&#xff0c;在CTF省赛校赛多次取得好成绩。…

JavaSE基础总结

JDK与JRE JDK&#xff0c;全称Java Development Kit&#xff0c;Java开发工具包 JRE&#xff0c;全称Java Runntime Environment&#xff0c;Java运行环境 JDK包含后者JRE。 JDK也可以说是Java SDK&#xff08;Software Development kit&#xff0c;软件开发工具包&#xff09;…

JVM高频面试题

1、项目中什么情况下会内存溢出&#xff0c;怎么解决&#xff1f; &#xff08;1&#xff09;误用固定大小线程池导致内存溢出 Excutors.newFixedThreadPool内最大线程数是21亿(2) 误用带缓冲线程池导致内存溢出最大线程数是21亿(3)一次查询太多的数据&#xff0c;导致内存占用…

基于深度学习的农作物叶片病害检测系统(UI界面+YOLOv5+训练数据集)

摘要&#xff1a;农作物叶片病害检测系统用于智能检测常见农作物叶片病害情况&#xff0c;自动化标注、记录和保存病害位置和类型&#xff0c;辅助作物病害防治以增加产值。本文详细介绍基于YOLOv5深度学习模型的农作物叶片病害检测系统&#xff0c;在介绍算法原理的同时&#…

百度的文心一言 ,没有想像中那么差

robin 的演示 我们用 robin 的演示例子来对比一下 文心一言和 ChatGPT 的真实表现&#xff08;毕竟发布会上是录的&#xff09;。 注意&#xff0c;我使用的 GPT 版本是 4.0 文学创作 1 三体的作者是哪里人&#xff1f; 文心一言&#xff1a; ChatGPT&#xff1a; 嗯&a…