Loading

高性能MySQL——查询性能优化

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

查询性能低下的基本原因就是：访问的数据太多了。这并不是单单指你的SQL语句最终需要获取的行数太多了，一个坏的数据库设计可能在你需要访问10行数据时扫描上万行数据。所以你需要从两方面考虑：

  1. 确认应用程序是否正在检索大量超过需要的数据
  2. 确认MySQL服务器层是否在分析大量超过需要的数据行

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

  1. 查询不需要的记录
  2. 多表关联时返回全部列
  3. 总是取出全部列
  4. 重复查询相同数据

关键问题在于，你的每次查询，数据库是否返回了你需要的数据之外的数据。但如果你的应用层采用了缓存设计，那么向数据库请求预期之外的数据也情有可原。

2. MySQL是否在扫描额外的记录

这一点，我们可以通过分析一个查询的开销来判断，有三个可考量的参数：

  1. 响应时间
  2. 扫描的行数
  3. 返回的行数

响应时间

响应时间 = 排队时间 + 服务时间，排队时间是指服务器已经收到了一个查询请求但还在等待必要的资源，如锁、IO。

可以通过估算当前SQL语句要查询的索引，估计要多少次随机IO，得到一个估计响应时间来与实际的作比较。

扫描行数和返回行数

在关联查询、排序、多表连接时可能需要扫描多行才能返回一行，在全表扫描中，甚至扫描了整个表才返回一行数据。

扫描行数和实际返回行数的比值应该尽量小，一般在1:1到10:1之间。

扫描的行数和访问类型

EXPLAIN语句中的type列反映了访问类型，有这几种：全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用。速度从慢到快，扫描行数从大到小。

对于WHERE条件的过滤，MySQL中有从好到坏这么几种情况：

  1. 在索引中直接就能过滤掉WHERE条件，一般是索引等值查询（type=ref|eq_ref），索引的范围查询（type=range），高版本中的ICP优化查询（Extra=Using index condition）。这种情况下，MySQL服务器层不需要再次使用WHERE条件检查，存储引擎所返回的就是最终返回给客户端的行。
  2. 使用索引覆盖扫描（索引能覆盖查询的行和条件中的行），无需再次查询表获取行，但是需要服务器通过Where来过滤掉不需要的索引项。（Extra中包含Using index和Using where）
  3. 需要从行中拉取数据，并且从服务器层中应用WHERE条件，这代表索引无法覆盖（或者压根没索引）。

讲实话，我昨天早上就读到这里了，但是我没咋懂，因为对MySQL的EXPLAIN返回结果和索引查询机制不太了解，于是我去读了下官方文档并写了几篇文章。先说，我自己觉得我写的有些混乱并且有些地方还是模糊，没有吃透，不过如果看官方文档感觉哪里不太懂，我这两篇也是个参考。

总之，一个好的索引能让查询时MySQL以最少的扫描行数获得需要的行。

优化方式：

  1. 尽量使用索引覆盖扫描
  2. 改变库表结构，加入一些汇总表，而不是查询原表
  3. 重写复杂的查询，让MySQL优化器能以更加优化的方式执行这个查询

重构查询的方式

MySQL创建连接很高效，外加应用层多会使用连接池复用数据库连接，所以，我们并不需要在一次查询中让MySQL返回尽量多的数据，有时候，将一个大查询分解成若干个小查询可以让MySQL获得更高的并发。

切分查询

对于删除一百万条数据的删除语句，如果你不切分，会长时间占用锁，导致其它请求没法被MySQL处理，并且造成大量的IO操作，影响服务器性能。这时就可以将它分解成每次删除一万个的SQL，发送10次。

分解关联查询

将一个关联查询分解成几次小查询，第一次查出一张表，然后利用这张表中的数据再去查询数据库，以此类推。

对于现在的ORM框架，比如MyBatis提供的lazyFetch功能，这一点很容易实现。

有几点好处：

  1. 更好的利用缓存：最小SQL，返回单表的最小数据，于是更方便缓存，稍后复用
  2. 减少锁竞争：三个查询分开发送，把每次占用锁的时间减小，更不容易锁争用
  3. 天生适合分布式：在分布式横行的今天，一旦要关联查询就很难分库分表
  4. 对单条语句优化更容易：关联查询受制于JOIN连接语法的限制，单条语句的限制更少
  5. ...

查询执行基础

这一小节是介绍查询在MySQL中如何被执行，又着重介绍了优化器都会做什么类型的优化，我觉得这些东西看了就行就没有记太多，记了这篇笔记又显得啰嗦。你可以直接跳过这一小节。

下面所要讲述的查询优化技巧都是和MySQL的优化器有关的，我们需要一些办法来让优化器产生最优的查询计划，所以我们要了解MySQL是如何执行查询的。如下图：

img

  1. 接受客户端的请求，如果缓存中包含请求的结果，直接返回
  2. 否则通过解析、预处理、优化步骤，生成查询执行计划
  3. 查询执行引擎根据查询执行计划使用存储引擎API调度存储引擎，具体的数据操作是由存储引擎完成的
  4. 查询执行引擎返回结果给客户端

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

半双工的通信

MySQL的客户端和服务器的通信是类似于“半双工”的，这意味着，一旦一端开始发送数据，不管是客户端的查询语句还是服务器端的查询结果集，另一端都只能接收数据，在数据接完之前，什么也不能做

也许它们底层通信是全双工的（TCP），但是为了维护简单性，它们在这之上模拟了半双工的通信

所以，MySQL在不同语言上的驱动程序都有两种实现方式可选：

  1. 一次性将管道中的数据读完，然后缓存起来以待使用
    就像下面php中的mysql_query，执行之后已经读取了返回的全部数据，下面的mysql_fetch_array实际上已经是从本地的缓存里取数据了
    img

  2. 用到一点取一点
    下面的mysql_unbuffered_query就不会缓存，而是由mysql_fetch_array去一点儿一点儿的读取远端发过来的数据。

    img

各有优点，第一种方式可以趁早结束该查询的读取，尽早让服务器释放资源。第二种方式可以加快query函数的响应速度，因为它不用维护缓存。

查询状态

对于每一个MySQL连接线程，都有一个状态，可以通过SHOW FULL PROCESSLIST获得

  1. Sleep：等待客户端发起新请求
  2. Query：正在执行客户端的请求
  3. Locked：正在等待MySQL层面的表锁
  4. Analyzing and statistics：正在收集存储引擎的统计信息以生成查询计划
  5. Copying to tmp table：在执行查询的过程中产生临时表复制，正在执行复制
  6. Sorting result：正在对结果集进行排序
  7. Sending data：线程可能在多个状态之间传送数据、生成结果集或向客户端返回数据

查询优化处理

我们略过缓存、解析、预处理这些步骤，在这些步骤中我们没什么可以改变的，直接看优化器的部分。

优化器将前面阶段种产生的语法树按照它认为最优的方式转换成执行计划，它的原理就是预测可能的执行计划的执行成本，并选择其中最小的一个。

可以通过SHOW STATUS LIKE 'Last_query_cost';来获得上一条语句的执行成本。

img

执行成本的值代表MySQL认为执行该查询会访问多少个数据页的随机查找，它会参考存储引擎收集的统计信息值。

优化器很智能，比我们聪明，但它有时也可能选不出最优的执行计划，所以我们还是有机会通过一些手法来让优化器选择特定的执行计划来得到我们认为的最优解：

  1. 统计信息并不准确：InnoDB的部分统计信息都是估计值
  2. 估计成本不是实际成本：优化器并不考虑缓存，不考虑页是否是顺序的
  3. 并不是你所认为的最优
  4. 不考虑并发
  5. 优化器特定条件下会忽略成本：比如遇到MATCH子句并且存在全文索引会直接选择全文索引而忽略成本
  6. 优化器不考虑不受它控制的成本：如存储过程
  7. 优化器要兼顾效率，所以没法估算所有可能的执行计划，可能错过某些实际上更优的执行计划

MySQL查询优化器的局限性

关联子查询

下面这条语句的目的是查询出film表中所有演员id为1的演员演的电影：

EXPLAIN SELECT * FROM film
WHERE film_id IN (
    SELECT film_id FROM film_actor WHERE actor_id=1
)\G;

我们自然的想到该语句的最佳执行策略应该是先执行子查询中的语句，内部是一个能用到索引的，查询速度非常快的子查询。而子查询返回了外层需要查询的主键，外层也只需要对这些主键进行一系列的等值查询即可。

实际上MySQL5.5中却是这样执行的：

img

先对外层进行全表查询，得到外层的整个表，然后对于返回的每一行，执行内层的查询，校验外层的film_id是否等于内层的film_id，也就是说被转换成了这种SQL：

SELECT * FROM film WHERE
EXISTS (
    SELECT * FROM film_actor WHERE actor_id = 1 
    AND film.film_id = film_actor.film_id
);

可以通过EXPLAIN EXTENDED然后再SHOW WARNINGS的方式验证，一旦你使用EXPLAIN EXTENDED去解释一个查询的执行，就会产生一个警告，该警告信息的MESSAGE就是查询被优化后的样子（在MySQL8中默认开启EXTENDED选项）：

img

这种情况下，我们需要编写其它等价的SQL替优化器来选择另一个执行方式：

EXPLAIN SELECT film.* FROM film
INNER JOIN film_actor USING (film_id)
WHERE actor_id=1;

这回，优化器最后选择的执行计划比以前强多了

img

MySQL5.5以后中对优化器进行了大量改进，这个问题在高版本好像已经不用担心了，我在8.0.29中测试，没有发现什么问题：

img

最后，关于MySQL到底什么时候会把我们的查询优化成什么样子，更烂还是更好，这没法说的那么绝对，最简单的办法就是使用EXPLAIN+EXPLAIN EXTENDED去查看执行信息，然后做出相应改进，不要听信网上那种特别绝对的“不要用XXX”、“再见XXX，你好XXX”，都挺弱智的。

UNION的限制

下面情况下，MySQL会选择将两个表的所有数据读出，放到一个临时表中，然后取出前二十条进行合并。

img

如果你把LIMIT下放到两个子查询中，MySQL就会从两个表中分别取出20条，然后放到临时表中进行合并。

img

查询优化器的提示（hint）

  1. 索引提示：USING INDEX/IGNORE INDEX
  2. 优化器提示：用一些提示来开启或关闭某种优化，见8.9.3 Optimizer Hints

书上还列出了一部分类似MySQL关键字的hint，但我在官方文档中找不到几种介绍的部分。

使用提示的一个问题就是，当前版本优化器面临的性能问题，可能下一个版本就会被解决，新的版本可能提供了更好更强的优化，但你的提示让该优化失效了。

优化特定类型的查询

COUNT优化

在MyISAM中，无条件的COUNT(*)很快，因为可以直接使用存储引擎记录好的行数。

简单的优化

对于下面的语句，WHERE条件有限制，所以还是会对ID>5的所有行进行扫描：

SELECT COUNT(*) FROM city WHERE ID > 5;

可以写成：

SELECT (SELECT COUNT(*) FROM city) - COUNT(*) 
FROM city WHERE ID <= 5;

这样一来，子查询中的COUNT全表是很快的，第二个外层查询中的COUNT只需要过滤五条数据，也很快。

使用近似值

我们经常不太关心获取的COUNT是否准确，所以只需要一个估计值就可以了，EXPLAIN优化器估算出来的行数就是一个估计值，而且，EXPLAIN并不需要真正的执行SQL语句。

更复杂的优化

  1. 索引覆盖扫描
  2. 建立汇总表
  3. 增加外部缓存系统（Redis、Memcached）

优化关联查询

  1. 被关联的表在ON和USING子句的列上有索引
  2. 确保GROUP BY和ORDER BY上只涉及到一个表中的列，这样才可能使用索引来优化

优化子查询

在老版本的MySQL中可以考虑使用关联查询替代子查询，新版本不需要

优化LIMIT

延迟关联

下面的语句，使用title排序，title列有一个索引，但返回的列中包含该索引覆盖不到的列，上面的查询无论在MySQL5.5还是8.0.29上都使用了filesort

SELECT film_id, description FROM film
ORDER BY title LIMIT 50, 5;

img

我们可以通过延迟关联技术来解决这一问题：

SELECT film_id, description FROM film
INNER JOIN (
    SELECT film_id FROM film
    ORDER BY title LIMIT 50, 5
) AS tmp USING (film_id);

关联查询内部的查询只需要对title索引进行覆盖扫描即可，也不用排序，然后拿出需要的film_id和第一个表做一个等值ref查询即可。

记录书签

LIMIT的一个问题是如果offset太大的话，那么为了到达固定的offset，前面的行都要被读取但白白浪费，所以如果offset有可能很大的话，必须优化。

如果ID是自动递增的主键，你可以通过记录上次查询到的最大ID，然后再下次查询时提供它，这样，你可以使用主键来快速跳过一些值，然后直接应用LIMIT，LIMIT的offset多大都不会影响性能。

SELECT * FROM rental
WHERE rental_id > 16030
ORDER BY rental_id LIMIT 20;

使用用户自定义变量

posted @ 2022-07-02 10:45  yudoge  阅读(368)  评论(0)  编辑  收藏  举报