MySQL_14_查询优化

最新推荐文章于 2024-07-18 20:04:15 发布

爱喝可乐的w

最新推荐文章于 2024-07-18 20:04:15 发布

阅读量521

点赞数

分类专栏：互联网精神 MySQL 文章标签： mysql 服务器数据库

本文链接：https://blog.csdn.net/qq_45686105/article/details/124015634

版权

互联网精神同时被 2 个专栏收录

116 篇文章 2 订阅

订阅专栏

MySQL

19 篇文章 0 订阅

订阅专栏

MySQL_14_查询优化

查询优化

查询优化

查询优化：通过各种方案使SQL查询更高效

1）查询优化分为：逻辑优化（重写SQL）、物理优化（索引）

2）查询优化本质：降低查询语句所需的开销

查询开销指标：响应时间、扫描行数、返回行数

1）响应时间：排队时间和执行时间

2）扫描行数：为完成查询扫描数据行的总数

3）返回行数：真实返回数据行的总数

//尽量使返回行数接近于扫描行数，且行的访问代价并不一定相等

查询开销高的基本原因是：访问数据过多

1）重写查询：使用更优化的查询语句；

2）覆盖索引：将使用的列均设为索引，以避免回表；

3）修改表结构：修改表结构以增高查询效率（或添加辅助表）；

查询流程

查询流程：客户端与MySQL服务器连接后，查询语句的执行流程

查询流程如下：

1）客户端发送查询语句请求至MySQL服务器；

2）检查是否命中缓存，命中则直接返回结果并结束查询；

3）对查询语句进行语法和语义分析，并解析成解析树并传递给预处理器优化；

4）优化器根据解析树生成优化后的执行计划，并传递给查询执行引擎；

5）查询执行引擎调用存储引擎API执行查询，并返回结果；

//MySQL 8.0之后的版本删除命中缓存查询（功能过于鸡肋）

如：MySQL服务器接收到客户端查询语句请求后的流程
在这里插入图片描述
//实际查询流程中可能并不按此线性流程执行（可能部分模块同时执行）

MySQL客户端和服务器之间通信协议为半双工

1）客户端只能用单个数据包将查询语句传递给服务器；

2）服务器用多个数据包将结果返回给客户端，且客户端必须完整接收结果；

//在客户端接收结果时，MySQL会对所有查询所使用的资源上锁

MySQL客户端通过线程连接服务器，服务器每刻都处于以下状态之一：

线程状态	说明
Sleep	等待客户端请求
Query	正在执行查询正在将结果发送给客户端
Locked	等待锁（锁的类型由存储引擎决定）
Analyzing and statistics	生成查询执行计划（根据存储引擎统计信息）
Copying to tmp table [on disk]	正在执行查询并将结果集复制到临时表
Sorting result	正在对结果集进行排序
Sending data	正在传送数据正在生成结果集正在将结果发送给客户端

查询缓存

查询缓存（Query Cache）：将查询结果缓存并在其他查询流程开始前检测

1）查询缓存会在不同客户端之间共享；

2）本质：基于对大小写敏感的哈希查找实现；

3）查询命中查询缓存会直接返回缓存，但会再检查次用户权限；

//MySQL 8.X删除查询缓存（功能过于鸡肋）

查询缓存限制（失效）：

1）查询语句必须完全相同：包括注释、大小写和空格数量；

2）不能包含函数和变量：无法确定其结果；

3）不适用频繁更新的表：缓存命中率较低；

解析器

解析器（Parser）：通过关键词对查询语句进行语法/语义分析以生成解析树

1）语法分析：根据MySQL语法规则检测查询语句是否合法；

2）语义分析：根据MySQL关键词分析查询语句的语法；

3）解析树：承载查询语句以便后续优化和执行

预处理器会根据解析树做进一步处理：

1）预处理器根据MySQL规则检测解析树是否合法，并进行优化；

2）预处理器再次检测用户权限；

优化器

优化器（Optimizer）：根据解析树生成执行计划

1）生成执行计划的同时会优化执行语句（逻辑优化，保证成本最小）

2）优化器决定的执行计划未必是最优的（优化器存在误判）

3）优化器分为：静态优化（编译时）、动态优化（运行时）

//SHOW STATUS LIKE ‘Last_query_cost’;可查最后一次查询成本

//优化器的估计成本并不一定是实际执行成本

优化器在以下情况会误判查询成本：

1）存在缓存：优化器不考虑任何层面的缓存；

2）并发查询：优化器不考虑任何并发执行的查询；

3）乐观评估：优化器不考虑任何不受控制的操作成本

4）统计信息不准确：优化器依靠存储引擎统计信息评估各查询成本；

常用场景

（1）重定义表的关联顺序

1）场景：表的实际关联顺序并总是按照查询语句指定的顺序进行；

2）优化：优化器会选择小表作为驱动表（减少外层循环次数）；

（2）外连接变为内连接

1）场景：外连接并不总是按查询语句指定的方式连接；

2）优化：优化器会尽量使用内连接代替外连接，并优化内连接顺序；

（3）简化并规范表达式

1）场景：查询语句的表达式不简化和规范时

2）优化：优化器会使用等价的简化且规范表达式替换查询语句的表达式；

（4）转化为常数

1）场景：将查询语句中部分转化为常数

2）优化：优化器检测查询语句中表达式和语句是否可转换为常数；

（5）覆盖索引

1）场景：判断是否可调用覆盖索引

2）优化：优化器判断当前查询语句的列是否被索引列包含；

（6）转化子查询

1）场景：子查询并不总按照子查询方式进行

2）优化：优化器常使用等价的关联查询替代子查询

//子查询使用临时表存储数据，但临时表无法创建/使用索引（成本高）

（7）提前终止查询

1）场景：查询能提前终止

2）优化：优化器发现满足查询语句需求时，可立刻终止查询

（8）等值传播

1）场景：关联查询时列可互相获得对方WHERE条件

2）优化：优化器在等值关联时，将列的WHERE条件传递给其他列

HINT

优化器提示（HINT）：控制优化器是否启用部分功能

（1）HIGH_PRIORITY和LOW_PRIORITY

1）功能：指定语句的优先级（仅对使用表锁的存储引擎）

2）可用于SELECT、INSERT、UPDATE和DELETE语句

（2）DELAYED

1）功能：结果立刻返回客户端，将插入放入缓冲区（表空闲时再插入）

2）可用于INSERT和REPLACE语句

//会导致LAST_INSERT_ID（）函数失效

（3）STRAIGHT_JOIN

1）功能1：查询语句中表只按照指定顺序关联（用于SELECT）

2）功能2：表的关联顺序不用被优化（用于JOIN）

3）可用于：SELECT和JOIN语句

（4）SQL_SMALL_RESULT和SQL_BIG_RESULT

1）功能：告知优化器是否将结果集放在内存的临时表中

2）可用于SELECT语句

（5）SQL_BUFFER_RESULT

1）功能：将结果集放入到内存的临时中再返回至客户端（尽快释放锁）

2）可用于SELECT语句

（6）SQL_CACHE和SQL_NO_CACHE

1）功能：是否将结果存储至查询缓存

2）可用于SELECT语句

（7）SQL_CALC_FOUND_ROWS

1）功能：使结果集尽可能包含更多的信息

2）可用于SELECT语句

（8）IGNORE INDEX和FORCE INDEX

1）功能：强制使用/不适用索引

2）可用于FROM语句

性能分析

EXPLAIN

EXPLAIN：分析指定语句以返回其执行计划

1）仅模拟执行指定语句，并非实际执行；

2）EXPLAIN不考虑缓存、优化器、触发器和存储过程的存在；

EXPLAIN分析格式：EXPLAIN 执行语句\G

1）也可使用“；”作为结束符，但\G增加可读性；

2）可在EXPLAIN后指定“FROMAT=JSON”获取更多信息；

分析返回的分析数据字段含义如下：

字段	含义
id	执行语句的唯一ID
select_type	查询类型
table	作用于的表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能使用的索引
key	实际使用的索引
key_len	实际使用的索引长度
ref	与索引进行等值匹配的对象信息
rows	估计读取的记录条数
filtered	条件过滤剩余数据的百分比
Extra	额外信息

（1）id：执行语句的唯一ID

1）若id相同则为同组，且执行顺序从上往下；

2）不同组中，id值越大其执行越优先；

//id应尽量少，代替其执行次数少

（2）select_type：查询类型

查询类型	说明
SIMPLE	简单查询（未使用UNION）
PRIMARY	外层查询
UNION RESULT	结果集用于联结
UNION	UNION的结果集
DEPENDENT UNION	依赖其他查询的延迟查询
UNCACHEABLE UNION	延迟查询且不缓存
SUBQUERY	子查询中首查询
DEPENDENT SUBQUERY	子查询中延迟查询（依赖于其他查询）
UNCACHEABLE SUBQUERY	子查询且不缓存
DERIVED	临时表查询
MATERIALIZED	IN条件的子查询

（3）table/type：作用于的表明、针对单表的访问方法

1）无论执行语句中包含多少表，其最终都为对单表的访问；

2）访问方法有以下12种（性能依次降低）：

访问方法	含义
system	系统数据（常通过内存获取）
const	常量数据
eq_ref	主键或非空唯一索引等值扫描
ref	非主键且非唯一的等值扫描
fulltext	全文索引扫描
ref_or_null	主键或唯一索引等值扫描
index_merge	合并索引扫描
unique_subquery	唯一索引子查询
index_subquery	索引子查询
range	范围扫描
index	索引树扫描
ALL	全表扫描

（4）Extra：额外信息

额外信息	说明
Using filesort	数据进行文件排序（FileSort）
Using temporary	使用临时表存储中间结果
Using where	使用WHERE过滤数据
Using index	使用覆盖索引
Using index condition	部分数据命中索引进行IPC
NOT EXISTS	NOT EXISTS替代IS NULL
Zero limit	LIMIT 0时
Using join buffer(Block Nested Loop)	子或关联查询时使用缓存优化

PROFILE

PROFILE：将MySQL上所有的执行语句相关信息记录到临时表

1）临时表按照执行顺序排序存储各个执行语句；

1)开启PROFILE记录（默认关闭）：SET profiling='ON';

//可查询该变量验证是否开启

2)查看PROFILE记录：SHOW profiles;

//默认仅返回所有执行语句的总开销

3)查看最近一次查询的各个阶段的开销：SHOW profile 资源;

资源	说明
ALL	全部显示
CPU	仅显示CPU相关
IPC	仅显示接收/发送数据
SWAPS	仅限交换次数
MEMORY	仅显示内存
BLOCK IO	仅显示IO
PAGE FAULTS	仅显示页面错误
CONTEXT SWITCHES	仅显示上下文切换

//若省略资源，则仅显示各个阶段的总开销

//可在最后添加“FOR QUERY 数值N”以显示多个查询开销

慢查询

慢查询：将执行超过指定时间的操作记录到日志文件

1）慢查询日志的开销可忽略不计，但长期开启需部署日志轮转工具；

常用命令如下：

1)开启慢查询（默认关闭，临时）

SET GLOBAL slow_query_log='ON';

//也可通过修改配置文件slow_query_log实现永久开启

2)指定慢查询限定时间为N秒(可为小数)

SET GLOBAL long_query_time=数值N

//查看该变量可得知慢查询限定时间（默认10秒）

3)指定慢查询日志文件路径

SET GLOBAL long_query_time=数值N

//查看该变量可得知慢查询日志文件路径

4)返回已记录慢查询总数

SHOW STATUS LIKE 'slow_queries';

5)记录未使用索引的查询

SET GLOBAL log_queries_not_using_indexes='ON';

mysqldumpslow命令：分支慢查询日志文件

指令格式：mysqldumpslow 选项慢查询日志文件路径

选项	含义
-s	以指定方式排序慢查询
-t N	返回前N条慢查询
-g 正则表达式	正则表达式匹配含特定关键词的慢查询

-s选项有以下种排序方式：

排序方式	说明
c	访问次数
l	锁定时间
r	返回记录
t	查询时间
al	平均锁定时间
ar	平均返回记录数
at	平均查询时间（默认方式）
ac	平均查询次数