【MySQL】第八篇:MySQL的查询优化

2024-08-27 23:48

本文主要是介绍【MySQL】第八篇:MySQL的查询优化,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

建表语句:建表语句

一、关联表查询优化

1.1、left join

结论:

  • 在优化关联查询时,只有在被驱动表上建立索引才有效!
  • 在优化关联查询时,要使用小表(驱动表)驱动大表(被驱动表),这样效率更高
  • left join 时,左侧的为驱动表,右侧为被驱动表;类比得出, right join 时,左侧为被驱动表;
    在这里插入图片描述

1.2、inner join

结论:inner join 时,mysql 会自己帮你把小结果集的表选为驱动表。
在这里插入图片描述

二、Not In 优化

结论:在范围判断时,尽量不要使用 not in 和 not exists,使用 left join on xxx is null 代替。
优化前语句:

select age as '年龄', count(*) as '人数' from t_emp where id not in (select ceo from t_dept where ceo is not null) group by age;

优化后语句:

select age as '年龄',count(*) as '人数' from emp e left join dept d on e.id=d.ceo where d.id is null group by age;

三、排序优化

创建索引:

create index idx_cid_name on city(city_id, name);

3.1、查询字段需使用覆盖索引

在这里插入图片描述

3.2、顺序错,必排序

结论:排序字段顺序不能乱
在这里插入图片描述

3.3、方向反,必排序

在这里插入图片描述

四、分组优化

group by 使用索引的原则基本与 order by 一致

using filesort 原理

mysql 的排序算法

  • 双路排序
    MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和 order by 列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。 从磁盘取排序字段,在 buffer 进行排序,再从磁盘取其他字段。 简单来说,取一批数据,要对磁盘进行了两次扫描,众所周知,I\O 是很耗时的,所以在 mysql4.1 之后,出现了第二种改进的算法,就是单路排序。

  • 单路排序
    从磁盘读取查询需要的所有列,按照 order by 列在 buffer 对它们进行排序,然后扫描排序后的列表进行输出, 它的效率更快一些,避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间, 因为它把每一行都保存在内存中了。

单路排序的问题
由于单路是后出的,总体而言好过双路。但是存在以下问题:
在 sort_buffer 中,方法 B 比方法 A 要多占用很多空间,因为方法 B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量,导致每次只能取 sort_buffer 容量大小的数据,进行排序(创建 tmp 文件,多路合并),排完再取取 sort_buffer 容量大小,再排…从而多次 I/O。

结论:本来想省一次 I/O 操作,反而导致了大量的 I/O 操作,反而得不偿失

如何优化

  • 增大 sort_butter_size 参数的设置
    不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程 1M-8M 之间调整
  • 增大 max_length_for_sort_data 参数的设置
    mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data。 提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总容量超出 sort_buffer_size 的概率就增大,明显症状是高的磁盘 I/O 活动和低的处理器使用率。(1024-8192 之间调整)。
  • 减少 select 后面的查询的字段。
    当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法——单路排序, 否则用老算法——多路排序。两种算法的数据都有可能超出 sort_buffer 的容量,超出之后,会创建 tmp 文件进行合并排序,导致多次 I/O,
    但是用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size。

这篇关于【MySQL】第八篇:MySQL的查询优化的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1113134

相关文章

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MyBatis-Plus通用中等、大量数据分批查询和处理方法

《MyBatis-Plus通用中等、大量数据分批查询和处理方法》文章介绍MyBatis-Plus分页查询处理,通过函数式接口与Lambda表达式实现通用逻辑,方法抽象但功能强大,建议扩展分批处理及流式... 目录函数式接口获取分页数据接口数据处理接口通用逻辑工具类使用方法简单查询自定义查询方法总结函数式接口