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;

在这里插入图片描述

Exists和In的区别

问题：
不太理解哪种情况下应该使用EXISTS，哪种情况应该用IN，选择的标准是看能否使用表的索引么？
回答：
索引是一个前提，其实选择与否还是要看表的大小。可以将选择的标准理解为小表驱动大表。在这种方式下效率是高的。

在这里插入图片描述

Count（*）与Count(具体字段)效率

问题： 在mysql中统计数据表的行数，可以使用三种方式：SELECT COUNT(*)\SELECT COUNT(1)和SELECT COUNT(具体字段)，使用三者之间的查询效率是怎么样的？
前提： 如果要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果是一样的。
在这里插入图片描述

关于SELECT (*)

在表查询中，建议明确字段，不要用作为查询的字段列表，推荐使用SELECT <字段列表> 查询。
原因：
mysql在解析的过程中，会通过查询数据字典将按序转化为所有列名，这样会大大的耗费资源和时间。无法使用覆盖索引

LIMIT 1 对优化的影响

针对的是会扫描全表的SQL语句，如果你可以确定结果集只有一条，那么加上limit 1 时**，当找到结果的时候就不会继续扫描了，这样会加快查询速度**。

如果数据表已经对字段建立了唯一索引，那么可以使用索引进行查询，不会全表扫描的话，就不需要加上limit 1了

多使用COMMIT

在这里插入图片描述

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

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

相关文章

堆C++（Acwing）

堆C++（Acwing）

代码&#xff1a; #include <iostream> #include <algorithm> #include <string.h>using namespace std;const int N 100010;int h[N], hp[N], ph[N], cnt;void heap_swap(int a, int b) {swap(ph[hp[a]] ,ph[hp[b]]);swap(hp[a], hp[b]);swap(h[a], h[b])…
阅读更多...
1904_ARM Cortex M系列芯片特性小结

1904_ARM Cortex M系列芯片特性小结

1904_ARM Cortex M系列芯片特性小结 全部学习汇总&#xff1a; g_arm_cores: ARM内核的学习笔记 (gitee.com) ARM Cortex M系列的MCU用过好几款了&#xff0c;也涉及到了不同的内核。不过&#xff0c;关于这些内核的基本的特性还是有些不了解。从ARM的官方网站上找来了一个对比…
阅读更多...
[力扣 Hot100]Day33 排序链表

[力扣 Hot100]Day33 排序链表

题目描述 给你链表的头结点 head &#xff0c;请将其按 升序 排列并返回 排序后的链表 。 出处 思路 归并排序即可。 代码 class Solution { public:ListNode* merge(ListNode *h1,ListNode *h2) {ListNode *head nullptr;if(h1->val<h2->val){head h1;h1h1-…
阅读更多...
Sora：颠覆性AI视频生成工具

Sora：颠覆性AI视频生成工具

Sora是一款基于人工智能&#xff08;AI&#xff09;技术的视频生成工具&#xff0c;它彻底改变了传统视频制作的模式&#xff0c;为创作者提供了高效、便捷、高质量的视频内容生成方式。通过深度学习和自然语言处理等先进技术&#xff0c;Sora实现了从文字描述到视频画面的自动…
阅读更多...
计算机体系架构初步入门

计算机体系架构初步入门

&#x1f3ac;个人简介&#xff1a;一个全栈工程师的升级之路&#xff01; &#x1f4cb;个人专栏&#xff1a;高性能&#xff08;HPC&#xff09;开发基础教程 &#x1f380;CSDN主页 发狂的小花 &#x1f304;人生秘诀&#xff1a;学习的本质就是极致重复! 目录 1 计算机五大…
阅读更多...
数据结构-列表LinkedList

数据结构-列表LinkedList

一,链表的简单的认识. 数组,栈,队列是线性数据结构,但都算不上是动态数据结构,底层都是依托静态数组,但是链表是确实真正意义上的动态数组. 为什么要学习链表? 1,链表时最简单的动态数据结构 2,掌握链表有助于学习更复杂的数据结构,例如,二叉树,trie. 3,学习链表有助于更深入…
阅读更多...
【深度学习笔记】卷积神经网络——多输入多输出通道

【深度学习笔记】卷积神经网络——多输入多输出通道

多输入多输出通道 虽然我们在subsec_why-conv-channels中描述了构成每个图像的多个通道和多层卷积层。例如彩色图像具有标准的RGB通道来代表红、绿和蓝。 但是到目前为止&#xff0c;我们仅展示了单个输入和单个输出通道的简化例子。 这使得我们可以将输入、卷积核和输出看作二…
阅读更多...
EasyRecovery2024电脑版软件评测与使用教程

EasyRecovery2024电脑版软件评测与使用教程

一、EasyRecovery电脑版软件评测 EasyRecovery电脑版是一款功能强大、操作简便的数据恢复软件。它适用于多种场景&#xff0c;无论是误删除、格式化、分区丢失还是硬件故障&#xff0c;都能提供有效的恢复方案。该软件界面直观&#xff0c;即便没有技术背景的用户也能轻松完成…
阅读更多...
使用 React 和 MUI 创建多选 Checkbox 树组件

使用 React 和 MUI 创建多选 Checkbox 树组件

在本篇博客中&#xff0c;我们将使用 React 和 MUI&#xff08;Material-UI&#xff09;库来创建一个多选 Checkbox 树组件。该组件可以用于展示树形结构的数据&#xff0c;并允许用户选择多个节点。 前提 在开始之前&#xff0c;确保你已经安装了以下依赖&#xff1a; Reac…
阅读更多...
GEE入门篇|遥感专业术语（实践操作3）：时间分辨率（Temporal Resolution）

GEE入门篇|遥感专业术语（实践操作3）：时间分辨率（Temporal Resolution）

目录 时间分辨率&#xff08;Temporal Resolution&#xff09; 1.Landsat 2.Sentinel-2 时间分辨率&#xff08;Temporal Resolution&#xff09; 时间分辨率是指特定传感器图像流的重访时间或时间节奏&#xff0c;重访时间是指卫星连续访问地球表面同一位置…
阅读更多...
公众号平台迁移公证怎么操作？

公众号平台迁移公证怎么操作？

公众号迁移有什么作用&#xff1f;只能变更主体吗&#xff1f;公众号账号迁移的作用可不止是变更主体哦&#xff01;还可以把多个公众号的粉丝、文章合并起来&#xff0c;打造一个超级大 V&#xff1b;还可以变更公众号主体、名称、类型&#xff0c;增加留言功能&#xff1b;个…
阅读更多...
javascript监听浏览器离开、进入行为

javascript监听浏览器离开、进入行为

document.addEventListener(visibilitychange, () > {if (document.visibilityState hidden) {alert(离开)}if (document.visibilityState visible) {alert(进入)}}) visibilitychange是浏览器新添加的一个事件&#xff0c;当其选项卡的内容变得可见或被隐藏时&#xff0…
阅读更多...
PyPDF2：项目实战源码分享（PDF裁剪）

PyPDF2：项目实战源码分享（PDF裁剪）

目录&#x1f4d1; 1. 背景&#x1f4d1;2. 源码模块解析&#x1f4d1;2.1 读取PDF页数2.2 获取指定页的宽高尺寸2.3 裁剪单页PDF2.4 批量裁剪PDF 总结&#x1f4d1; 1. 背景&#x1f4d1; 接PyPDF2模块推荐博文中提到的实际需求&#xff08;将银行网站下载来的多页且单页多张…
阅读更多...
10s了解 共享动画

10s了解 共享动画

1. 目的&#xff1a; 界面切换&#xff0c;两控件变化关联&#xff0c;看起来更丝滑流程 2.怎么配置 为关联两控件加上相同transitionName 3.在Navigation开启共享动画 跳转到的界面 接收共享动画 4.在Activity中开启共享动画 同3 &#xff0c;在共享动画两个View加上相同的t…
阅读更多...
政安晨【示例演绎虚拟世界开发】（一）：Cocos Creator 的 Hello World

政安晨【示例演绎虚拟世界开发】（一）：Cocos Creator 的 Hello World

政安晨的个人主页&#xff1a;政安晨 欢迎 &#x1f44d;点赞✍评论⭐收藏 收录专栏: AI虚拟世界大讲堂 希望政安晨的博客能够对您有所裨益&#xff0c;如有不足之处&#xff0c;欢迎在评论区提出指正。 前言 Cocos Creator是一款非常强大的游戏开发引擎&#xff0c;它有着优秀…
阅读更多...
[HTML]Web前端开发技术29（HTML5、CSS3、JavaScript ）JavaScript基础——喵喵画网页

[HTML]Web前端开发技术29（HTML5、CSS3、JavaScript ）JavaScript基础——喵喵画网页

希望你开心&#xff0c;希望你健康&#xff0c;希望你幸福&#xff0c;希望你点赞&#xff01; 最后的最后&#xff0c;关注喵&#xff0c;关注喵&#xff0c;关注喵&#xff0c;佬佬会看到更多有趣的博客哦&#xff01;&#xff01;&#xff01; 喵喵喵&#xff0c;你对我真的…
阅读更多...
centos7部署单机项目和自启动

centos7部署单机项目和自启动

centos7部署单机项目和服务器自启动 1.安装jdk和tomact1.1上传jdk、tomcat安装包1.2解压两个工具包1.3.配置并且测试jdk安装1.4.启动tomcat1.5.防火墙设置1.6配置tomcat自启动 2.安装mysql2.1卸载mariadb&#xff0c;否则安装MySql会出现冲突(先查看后删除再查看)2.2在线下载My…
阅读更多...
Linux学习笔记9——adduser，passwd用户创建

Linux学习笔记9——adduser，passwd用户创建

Linux是一个多用户的操作系统&#xff0c;允许多用户访问&#xff0c;对系统进行一些操作&#xff0c;其中根用户为root拥有系统一切权限 其中&#xff0c;useradd是新建用户&#xff0c;passwd是给新建用户添加密码&#xff0c;su新建用户名&#xff0c;是切换到该用户对系统进…
阅读更多...
雪地奔驰高级版/SnowRunner【带修改器】（全DLCs）

雪地奔驰高级版/SnowRunner【带修改器】（全DLCs）

包含DLC • SnowRunner – Sabertooth Livery • SnowRunner – Navistar 5000-MV Tractor • SnowRunner – High Roller Pack • SnowRunner – Loaded Dice Vinyl Wrap • SnowRunner – Scorched Vinyl Wrap • SnowRunner – True Colors Vinyl Wrap • SnowRunner…
阅读更多...
企业网站建设需要多少钱？定制开发费用报价在3000-4000元

企业网站建设需要多少钱？定制开发费用报价在3000-4000元

建立一个网站需要多少钱&#xff1f; 网站建设的价格划分也有很多。 这里首先要提的是市面上常见的一种低成本建站方式——模板网站&#xff0c;就是那种直接制作网站原型就可以无限复制的网站。 或者可以在几分钟内建立一个由软件生成的网站。 成本低得惊人&#xff0c;从500元…
阅读更多...
最新文章

Copyright @ 2022~2023