「高性能MySQL阅读笔记」第六章查询性能优化

唐·王惜之

已于 2023-03-31 15:37:09 修改

阅读量74

点赞数

分类专栏：《高性能MySQL》第三版阅读笔记文章标签： mysql 性能优化数据库

于 2023-03-31 12:48:42 首次发布

本文链接：https://blog.csdn.net/weixin_36642193/article/details/129877443

版权

《高性能MySQL》第三版阅读笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

第六章查询性能优化

6.1 为什么查询性能会慢

6.2 慢查询基础：优化数据访问

6.2.1 是否向数据库请求了不需要的数据

经典案例：
- 查询不需要的记录
- 多表关联时返回全部列
- 总是取出全部列
- 重复查询相同的数据

6.2.2 MySQL是否在扫描额外的数据

对于MySQL ,最简单的衡量查询开销的三个指标如下：

响应时间
扫描的行数
返回的行数
- 一般mysql能够使用如下三种方式应用wheret条件，从好到坏依次为：
  - 在索引中使用where条件过滤不匹配的记录。这是在存储引擎完成的
  - 使用索引覆盖扫描（Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在服务器层完成的，但无须再回表查询记录
  - 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）,这是在服务器层完成的，先从表中读取数据再过滤。
- 如果发现查询需要扫描大量的数据但只返回少数的行，可以尝试下面的技巧去优化：
  - 使用索引覆盖扫描，把所有需要用的列都放到索引中
  - 改变库表结果。
  - 重写这个查询

6.3 重构查询的方式

6.3.1 一个复杂查询还是多个简单查询

看情况，一般来说多个简单查询效率，缓存利用率会比一个复杂的查询好

6.3.2 切分查询

分而治之，将大查询切分为小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

分页查询
分时间段、数据量删除，每次只删除小部分数据

6.3.3 分解关联查询

分解关联查询的方重构查询有如下优势：

让缓存效率更高
将查询分解后，执行单个查询可以减少锁的竞争
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
查询本身效率也可能会有所提升
可以减少冗余记录的查询
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联

6.4 查询执行的基础

客户端发送一条查询给服务器
服务器先检查查询缓存，如果命中了缓存，再由优化器生成对应的执行计划
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
将结果返回给客户端

6.4.1 MySQL 客户端/服务器通信协议

”半双工“：任何一个时刻，要么由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
查询状态：SHOW FULL PROCESSLIST
- Sleep
- Query
- Locked
- Analyzing and statistics
- Copying to tmp table[on disk]
- Sorting result
- Sending data

6.4.2 查询缓存

6.4.3 查询执行引擎

语法解析器和预处理
查询优化器：尝试预测一个查询使用某种执行计划的成本，并选择其中成本最小的一个。
- 有很多种原因会导致My SQL优化器选择错误的执行计划，如下所示：
  - 统计信息不准确
  - 执行计划中的成本估算不等同于实际执行的成本
  - MySQL的最优可能和你想的不一样
  - MySQL 从不考虑其他并发执行的查询，这可能会影响到当前查询的速度
  - MySQL也不是任何时候都是基于成本的优化
  - MySQL不会考虑不受其控制的操作的成本
- 优化分为两种：- 静态优化、动态优化
- 下面是一些MySQL 能够处理的优化类型：
  - 重新定义关联表的顺序
  - 将外链接转化成内连接
  - 使用等价变换规则
    - 例如：(a < b and b=c) and a=5 将被改写为 b>5 and b=c and a= 5
  - 优化count() 、min()、max()
    - 最小值 B-Tree最左边索引的第一行记录
    - 最大值 B-Tree最右边索引的第一行记录
  - 预估并转化为常数表达式
  - 覆盖索引扫描
  - 子查询优化
  - 提前终止查询
    - select * from user where 1=2
    - select * from user limit 1
  - 等值传播
    - 如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE 条件传递到另一个列上。
    - select film_id from film inner join actor USING(film_id) where film_id >500 等同于…WHERE film.film_id > 500 AND actor.film_id > 500,如果使用其他数据库系统可能还需要显示手动改写SQL。
  - 列表IN()的比较
    - mysql将in()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(log n)复杂度的操作，等价地转换成OR查询的复杂度为O(n),对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。
数据和索引的统计信息
- 统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。
- 因为服务器层没有任何统计信息，所以MySQL查询优化器在生成查询执行计划时需要向存储引擎获取相关统计信息。优化器根据统计信息来选择一个最优的执行计划。
MySQL如何执行关联查询
- 当前MySQL 关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻求匹配的行，依次下去，直到找到所有表中匹配的行为为止。
- 例如：SELECT tbl1.col1,tbl2.col2 FROM tbl1 INNER JOIN tbl2 USING(col3) WHERE tbl1.col1 IN(5,6) ,假设MySQL按照查询中的表顺序进行关联，则可以用下面的伪代码表示MySQL将如何完成这个查询：
```
outer_iter = iterator over tbl1 where col1 IN(5,6) 
outer_row = outer_iter.next      
while outer_row    
	inner_iter = iterator over tbl2 where col3 = outer_row.col3 
	inner_row = inner_iter.next
	while inner_row
		output [outer_row.col1,inner_row.col2]
	end
	outer_row = outer_iter.next
end
```
执行计划
- MySQL生成查询的一颗指令树，然后通过存储引擎执行完这颗指令树并返回结果。
- 如果对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出来的查询。
- MySQL的执行计划是一颗左侧深度优先的树，如下图：
- 关联查询优化器
- 排序优化

6.4.4 查询执行引擎

根据执行计划调用存储引擎的API，这里的执行计划是一个数据结构

6.4.5 返回结果给客户端

MySQL将结果集返回给客户端是一个增量、逐步返回的过程。

6.5 MySQL 查询优化器的局限性

6.5.1 关联子查询

6.5.2 UNION的限制

6.5.3 索引合并优化

6.5.4 等值传递

6.5.5 并行执行

6.5.6 哈希关联

6.5.7 松散索引扫描

6.5.8 最大值和最小值优化

6.5.9 在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。如下语句无法更新

	update tbl as outer_tbl 
	set cnt = (SELECT count(*) FROM tbl as inner_tbl 
	where inner_tbl.type = outer_tbl.type)

修改为如下语句则可以更新，因为子查询变成了一张临时表

update tbl 
	inner join(select type,count(*) as cnt from tbl group by type) as der USING(type)  
	set tbl.cnt = der.cnt

6.6 查询优化器的提示（hint）

HIGH_PROIORITY 和 LOW_PROIORITY
- 这两个提示只对使用表锁的存储引擎有效，千万不要在InnoDB或者其他细粒度锁机制和并发控制的引擎中使用。即使在MyISAM中使用也要注意，因为这两个提示会导致并发插入被禁用，可能会严重降低性能。
DEALAYED
- 这两个提示对INSERT 和 REPLACE有效
STRAIGHT_JOIN
- 放在SELECT语句的 SELECT关键字后面，让查询中的所有表按照语句中出现的顺序进行关联。
- 放到任何两个关联表的名字之间，固定其前后两个表的关联顺序。
SQL_SMALL_RESULT和 SQL_BIG_RESULT
SQL_CACHE和SQL_NO_CACHE
SQL_CALC_FOUND_ROWS
FOR UPDATE 和 LOCK IN SHARE MODE
USE INDEX 、I GNORE 和 FORCE INDEX