MySQL数据库调优之关联查询、排序查询、分页查询、Group by优化

关联查询优化

1.准备工作

CREATE TABLE IF NOT EXISTS `type`(id INT(10) UNSIGNED NOT NULL  AUTO_INCREMENT,card INT(10) UNSIGNED NOT NULL,PRIMARY KEY(id));CREATE TABLE IF NOT EXISTS book(
bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(bookid)
)INSERT INTO TYPE(card) VALUES (FLOOR (1+(RAND()*20)));INSERT INTO book(card) VALUES (FLOOR (1+(RAND()*20)));

情况1：左外连接

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card=book.card;

在这里插入图片描述
添加索引后

CREATE INDEX Y ON book(card);

在这里插入图片描述
内连接

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

在这里插入图片描述
对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的

删除被驱动表book的index后
在这里插入图片描述
对于内连接来说，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现
在这里插入图片描述
对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”

join语句的原理

驱动表和被驱动表
驱动表是主表，被驱动表是从表，非驱动表。
对于内连接来说：

SELECT * FORM A JOIN B ON...

A一定是驱动表么？ 不一定，优化器会根据你查询语句做优化，决定先查那张表，先查询的那张表就是驱动表，反之就是被驱动表。通过explain关键字可以查看。

对于外连接来说：

SELECT * FROM A LEFT JOIN B ON ...
SELECT * FROM B RIGHT JOIN A ON ....

通常会认为A为驱动表，B为被驱动表，但也未必

Simple Nested-Loop Join(简单嵌套循环连接)

在这里插入图片描述
在这里插入图片描述
**

Index Nested-Loop join (索引嵌套循环连接)

**
index nested-loop join其优化的思路主要是为了减少内层表数据的匹配次数，所以要求·被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

在这里插入图片描述
在这里插入图片描述

Block Nested Loop Join(块嵌套循环连接)

在这里插入图片描述
JOIN BUFFER缓冲区缓存的不只是关联表的列，select后面的列也会缓存起来
在这里插入图片描述
在这里插入图片描述
参数设置
block_nested_loop=on

SHOW VARIABLES LIKE  '%optimizer_switch%'

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

join_buffer_size 默认256K

SHOW VARIABLES LIKE  '%join_buffer_size%'

在这里插入图片描述
JOIN小结
在这里插入图片描述

Hash Join
从Mysql的8.0.20版本开始将废弃BNLJ，因为从Mysql8.0.18版本开始就加入了hash join，默认都是用hash join

在这里插入图片描述
JOIN连接 小结
保证被驱动表的JOIN字段已经创建了索引
需要JOIN的字段，数据类型保持绝对的一致
left join时，选择小的作为驱动表，大表作为被驱动表。减少外层循环的次数
inner join 时，mysql会自动将小结果集的表选为驱动表 选择相信mysql优化策略。
能够直接多表关联的尽量直接关联，不用子查询（减少查询的趟数）
不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用JOIN来代替子查询。
衍生表建不了索引

子查询优化

Mysql从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一盒SELECT查询的结果作为另外一个SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作
子查询是Mysql的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。原因
1.执行子查询时，Mysql需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
2.子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，索引查询的性能会有一定的影响。
对于返回结果集比较大的子查询，其对查询性能的影响也越大。
在Mysql中，可以使用连接（JOIN）查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快。如果查询中有使用索引的话，性能就会更好。

尽量不要使用NOT IN 或者NOT EXISTS，用LEFT JOIN xxxxx ON xx WHERE xx IS NULL 替代

排序优化

问题：在where条件字段上加索引，但是为什么在Order by字段上还要加索引呢？
回答：
在mysql中，支持两种排序方式，分别是FileSort和Index排序
Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
FileSort排序则一般在内存中进行排序，占用CPU较多，如果待排结果较大，会产生临时文件I/0到磁盘进行排序的情况，效率更低。
优化建议：
1.SQL 中，可以在Where子句和Order子句中使用索引，目的时在WHERE子句中避免全表扫描，Order By子句避免使用FileSort排序。当然在某些情况下，全表扫描或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2.尽量使用Index完成Order by排序。如果Where和Order by后面是相同的列就使用单索引列；如果不同就使用联合索引。
3.无法使用Index时，需要对FileSort方式进行调优。
select的查询内容没有限制
在这里插入图片描述

select查询内容与索引保持一致(索引覆盖)
在这里插入图片描述
增加limit过滤条件，使用上了索引
在这里插入图片描述
order by时顺序错误，索引失效
在这里插入图片描述
在这里插入图片描述
上述案例违反了索引的最左匹配的原则
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
上述三种情况，索引有在使用中

order by 时规则不一致，索引失效（顺序错，不索引；方向反，不索引）

EXPLAIN SELECT * FROM student ORDER BY age DESC,classid ASC LIMIT 10;EXPLAIN SELECT * FROM student ORDER BY classid DESC,NAME ASC LIMIT 10;EXPLAIN SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10;EXPLAIN SELECT * FROM student ORDER BY age DESC,classid DESC LIMIT 10;

只要最后一种倒着来的使用上了索引

无过滤，不索引


#无过滤，不索引EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid; #先使用where 过滤数据 ken_len=5EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid,NAME; #先使用where 过滤数据 ken_len=5EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age; #没用EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 10; #用了索引

小结
在这里插入图片描述
在这里插入图片描述
结论：
1.两个索引同时存在，mysql会自动选择最优方案。但是，随着数据量的变化，选择的索引也会随之变化
2.当【范围条件】和【group by或者order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上，反之，亦然。

fileSort算法：双路排序和单路排序

排序的字段若如果不在索引列上，则filesort会有两种算法：双路排序和单路排序

在这里插入图片描述
在这里插入图片描述
Order by时select 是一个大忌，最好只query需要的字段
原因：
当query的字段大小总和小于max_length_for_sort_data*,而且排序的字段不是TEXT|BLOB类型时，会用改进的算法–单路排序，否则用老算法–多路排序.
两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/0，但是用单路排序的风险更大一些，所以要提高sort_buffer_size的容量

Group By优化

group by 使用索引的原则几乎与order by一致，group by即使没有过滤条件使用到索引，也可以直接使用索引。
group by 先排序再分组，遵循索引创建的最佳左前缀法则
当无法使用索引列时，增大max_length_for_sort_data和sort_buffer_size的参数设置
where的效率高于having,能写在where 限定的条件，就不要写在having中了。

减少使用order by，和业务沟通，能不排序就不排序，或者将排序放在程序端去做。

order by\group by\distinct 这些语句比较耗费CPU，数据库的CPU资源是宝贵的。

包含order by\group by\distinct 这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好的提高性能。一个常见又比较头疼的问题就是limit 2000000,10 此时，需要mysql排序前20000010条记录，仅仅返回2000000-20000010的记录，其他记录丢弃，查询排序的代价非常的大。

EXPLAIN SELECT * FROM student  LIMIT 2000000,10;

优化思路1：
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列的内容

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id=a.id;

在这里插入图片描述
优化思路二：
改方案适合于主键自增的表，可以把limit查询转换成某个位置的查询

EXPLAIN SELECT * FROM student  WHERE id> 2000000 LIMIT 10;

在这里插入图片描述

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

如若内容造成侵权/违法违规/事实不符，请联系瞎胡闹网进行投诉反馈，一经查实，立即删除！

相关文章

JavaSE-04笔记【面向对象01】

JavaSE-04笔记【面向对象01】

文章目录 1. final 关键字1.1 采用final修饰的类不能被继承1.2 采用 final 修饰的方法不能被覆盖1.3 采用 final 修饰的变量(基本类型)不能被修改1.4 采用final 修饰的变量必须显示初始化1.5 如果修饰的引用，那么这个引用只能指向一个对象，也就是说这个引…
阅读更多...
利用LaTex批量将eps转pdf、png转eps、eps转png、eps转svg、pdf转eps

利用LaTex批量将eps转pdf、png转eps、eps转png、eps转svg、pdf转eps

1、eps转pdf 直接使用epstopdf命令（texlive、mitex自带）。 在cmd中进入到eps矢量图片的目录，使用下面的命令： for %f in (*.eps) do epstopdf "%f" 下面是plt保存eps代码： import matplotlib.pyplot as…
阅读更多...
STP基本计算过程——选举非根交换机的根端口（二）

STP基本计算过程——选举非根交换机的根端口（二）

点赞关注，持续更新STP专题 选举非根交换机的根端口 1.STP为每个非根交换机选举根端口（Root Port）。 2. 交换机的每个端口都有一个端口开销（Port Cost）的参数，此参数表示数据从该端口发送时的开销值&#x…
阅读更多...
2024年度中国5G随身WiFi品牌排行榜

2024年度中国5G随身WiFi品牌排行榜

【中国品牌网中国3C质量评测中心权威榜单联合发布】 第一名：格行 优势：作为随身WiFi行业的佼佼者，格行凭借其15年的物联网行业经验，在技术研发、产品创新及客户服务上均享有盛誉。其5G随身WiFi产品网络稳定，客户满意度…
阅读更多...
windows 中， bash: conda: command not found（已解决）

windows 中， bash: conda: command not found（已解决）

git bash 中运行conda命令，出现这种错误，原因是你没有在git bash中 配置conda，导致git bash无法找到conda 那就配置一下，找到你的conda的安装位置下的bash.sh文件，一般在安装位置（我的安装在C盘的自定义路径…
阅读更多...
一个div最简方法画太极图

一个div最简方法画太极图

一个div最简方法画太极图 直接上代码&#xff0c;一目了然 html <div class"太极图"/>css .太极图 {position: relative;width: 400px;height: 400px;background: linear-gradient(to right,white 50%,black 50%);border-radius: 50%;box-shadow:0 0 12px …
阅读更多...
第10讲用户登录SpringSecurity查库实现

第10讲用户登录SpringSecurity查库实现

用户登录SpringSecurity查库实现 security包下新建MyUserDetailServiceImpl Service public class MyUserDetailServiceImpl implements UserDetailsService {AutowiredSysUserService sysUserService;Overridepublic UserDetails loadUserByUsername(String username) throw…
阅读更多...
DBAPI如何使用数组类型参数

DBAPI如何使用数组类型参数

DBAPI如何使用数组类型参数 需求 根据多个id去查询学生信息 API创建 在基本信息标签&#xff0c;创建参数ids &#xff0c;参数类型选择 Array<bigint> 在执行器标签&#xff0c;填写sql&#xff0c;使用in查询 select * from student where id in <foreach ope…
阅读更多...
仿12306校招项目-前后端运行

仿12306校招项目-前后端运行

目录 1.git 克隆 2.设置JDK版本 3.sql脚本导入数据 4.启动中间件 5.运行后端 6.运行前端 1.git 克隆 打开 IntelliJ IDEA&#xff0c;菜单栏顶部找到 Git -> Clone 选项。找到 Clone 这个按钮输入 gitgitee.com:nageoffer/12306.git或者https://gitee.com/nageoffer/…
阅读更多...
使用Python制作进度条有多少种方法？看这一篇文章就够了！

使用Python制作进度条有多少种方法？看这一篇文章就够了！

前言 偶然间刷到一个视频&#xff0c;说到&#xff1a;当程序正在运算时&#xff0c;会有一个较长时间的空白期&#xff0c;谁也不知道程序运行的进度如何&#xff0c;不如给他加个进度条。 于是我今个就搜寻一下&#xff0c;Python版的进度条都可以怎么写&#xff01; 送书…
阅读更多...
数据结构-二分搜索树（Binary Search Tree）

数据结构-二分搜索树（Binary Search Tree）

一,简单了解二分搜索树 树结构: 问题:为什么要创造这种数据结构 1,树结构本身是一种天然的组织结构,就好像我们的文件夹一样,一层一层的. 2,树结构可以更高效的处理问题 二,二分搜索树的基础 1、二叉树 2,二叉树的重要特性 满二叉树 总结: 1. 叶子结点出现在二叉树的最…
阅读更多...
上班族如何选择待办事项时间管理APP

上班族如何选择待办事项时间管理APP

上班族如何选择待办事项时间管理APP&#xff1f;对于我们这些上班族来说&#xff0c;待办事项时间管理是一种非常重要的效率提升和成长的方式。作为一名追求进步的上班族&#xff0c;我总是想要在繁忙的工作之余&#xff0c;找到高效方法来提升工作效率。那么&#xff0c;上班族…
阅读更多...
故障诊断 | 一文解决，PSO-BP粒子群算法优化BP神经网络模型的故障诊断（Matlab）

故障诊断 | 一文解决，PSO-BP粒子群算法优化BP神经网络模型的故障诊断（Matlab）

文章目录 效果一览文章概述模型描述源码设计参考资料效果一览 文章概述 故障诊断 | 一文解决，PSO-BP粒子群算法优化BP神经网络模型的故障诊断（Matlab） 粒子群优化算法（Particle Swarm Optimization, PSO）是一种群体智能优化算法，用于求解优化问题。BP神经网络是一种用于模…
阅读更多...
【JavaEE】_smart tomcat常见问题

【JavaEE】_smart tomcat常见问题

目录 1. 插件安装故障问题 2. 端口占用问题 3. 乱码问题 1. 插件安装故障问题 如果由于网络问题在IDEA中无法直接安装插件&#xff0c;可以去IDEA官网进行下载&#xff1a; 进入官网后点击Install安装&#xff0c;得到一个jar包&#xff1a; 把jar包拖拽到idea上即可自动安装…
阅读更多...
ONLYOFFICE 桌面编辑器现已更新至v8.0啦

ONLYOFFICE 桌面编辑器现已更新至v8.0啦

希望你开心&#xff0c;希望你健康&#xff0c;希望你幸福&#xff0c;希望你点赞&#xff01; 最后的最后&#xff0c;关注喵&#xff0c;关注喵&#xff0c;关注喵&#xff0c;佬佬会看到更多有趣的博客哦&#xff01;&#xff01;&#xff01; 喵喵喵&#xff0c;你对我真的…
阅读更多...
SQL-Labs靶场“46-50”关通关教程

SQL-Labs靶场“46-50”关通关教程

君衍. 一、四十六关 ORDER BY数字型注入1、源码分析2、rand()盲注3、if语句盲注4、时间盲注5、报错注入6、Limit注入 二、四十七关 ORDER BY单引号报错注入1、源码分析2、报错注入3、时间盲注 三、四十八关 ODRER BY数字型盲注1、源码分析2、rand()盲注3、if语句盲注4、时间盲注…
阅读更多...
300分钟吃透分布式缓存-13讲：如何完整学习MC协议及优化client访问？

300分钟吃透分布式缓存-13讲：如何完整学习MC协议及优化client访问？

协议分析 异常错误响应 接下来&#xff0c;我们来完整学习 Mc 协议。在学习 Mc 协议之前&#xff0c;首先来看看 Mc 处理协议指令&#xff0c;如果发现异常&#xff0c;如何进行异常错误响应的。Mc 在处理所有 client 端指令时&#xff0c;如果遇到错误&#xff0c;就会返回 …
阅读更多...
杰发科技AC7801——SRAM 错误检测纠正

杰发科技AC7801——SRAM 错误检测纠正

0.概述 7801暂时无错误注入&#xff0c;无法直接进中断看错误情况&#xff0c;具体效果后续看7840的带错误注入的测试情况。 1.简介 2.特性 3.功能 4.调试 可以看到在库文件里面有ecc_sram的库。 在官方GPIO代码里面写了点测试代码 成功打开2bit中断 因为没有错误注入&#x…
阅读更多...
信息系统项目管理师（高项）—学习笔记

信息系统项目管理师（高项）—学习笔记

第一章信息化发展 1.1 信息与信息化 1.1.1 信息 信息是物质、能量及其属性的标示的集合&#xff0c;是确定性的增加。 它以物质介质为载体&#xff0c;在传递和反映世界各种事物存在方式、运动状态等的表征。 信息不是物质&#xff0c;也不是能力&#xff0c;它以一种普遍…
阅读更多...
二 线性代数-向量

二 线性代数-向量

1、向量的表示方法&#xff1a; 其中的 i、j、k是坐标轴方向的单位向量。 2、向量的模&#xff1a; 用坐标计算的方法&#xff1a; 3、向量的运算&#xff1a; 3.1 向量的加法减法&#xff1a; 3.2 向量的数乘&#xff1a; 拉格朗日乘数法的 基础 公式。 3.3 向量的数量积&a…
阅读更多...
最新文章

Copyright @ 2022~2023