mysql高性能查询_读薄《高性能MySql》(四)查询性能优化

对 MySql 进行优化&＃xff0c;必须对 Scheme&＃xff0c;索引&＃xff0c;查询语句一同优化。

通过前面的章节我们掌握了 Scheme 和 索引的优化&＃xff0c;最后我们来看一下查询优化。

为了优化查询&＃xff0c;我们必须先了解查询是怎样执行的&＃xff0c;然后探讨优化器在哪些方面做得还不足&＃xff0c;以帮助 MySql 更有效的执行查询。

优化数据访问

在一条 Sql 语句执行的很慢的时候&＃xff0c;可以从以下两个方面来分析&＃xff1a;

是否在检索的时候访问了太多的行或者列

MySql 服务器是否在分析大量超过需要的行

请求了不需要的数据

万恶之源 SELECT *

一个很好用的观点就是在每次使用 SELECT * 取出全部行的时候都要审视一下自己是否需要全部数据。

取出所有列可能使得索引覆盖无效&＃xff0c;一些 DBA 是严格禁止 SELECT * 的写法的。

重复查询数据

有些地方可能会不小心的重复查询了相同的数据。比如在论坛中&＃xff0c;如果一个人回复多次&＃xff0c;很有可能会一不小心每次都去请求这个人的资料&＃xff0c;一个有效的方法就是使用缓存。

扫描额外的记录

确定查询只返回需要的数据以后&＃xff0c;接下来该看一下为了返回需要的记录是否扫描了太多行了。有两个指标我们需要关注&＃xff0c;一个是扫描的行数和返回行数的比值&＃xff0c;另外一个是扫描的访问类型。

扫描行数和返回行数的比值如果过低&＃xff0c;则需要扫描大量的数据才能返回结果&＃xff0c;通常可以使用如下的方法来保存数据&＃xff1a;

使用索引覆盖&＃xff0c;把所有的列放入索引中&＃xff0c;就无需扫描表了

改变数据库结构&＃xff0c;比如采用单独的表汇总表

重新写这个 SQL 语句

在 EXPLAIN 语句中的 type 列中反应了访问类型&＃xff0c;从慢到快分别是&＃xff1a;

全表扫描&＃xff0c;索引扫描&＃xff0c;范围扫描&＃xff0c;唯一索引查询&＃xff0c;常数引用。

如果查询没有使用合适的类型&＃xff0c;可以合理的添加索引。

重构查询方式

将一个复杂查询拆分成多个查询

MySql 从设计上让连接和断开都很快。如果只是返回一个小小的结果&＃xff0c;MySql 非常高效。

当然能一个查询就解决的要尽量写成一个查询&＃xff0c;只是告诉大家不要太惧怕把查询拆分开来会带来性能损失。

切分查询

有时候一个大查询会占用表锁很久&＃xff0c;影响业务。这时候可以将大查询分为小查询&＃xff0c;每次执行这个查询的一小部分。

比如定期清除大量数据的时候&＃xff0c;如果有一个大的语句一次性完成&＃xff0c;则可能会占住很多资源&＃xff0c;影响其他查询。

将删除改写成一次删除一小部分数据&＃xff0c;分散开来在不同时间执行&＃xff0c;可以将服务器压力分散到很长的一个时间段中。

分解关联查询

很多高性能应用会将一个大的关联查询分解成多个单表查询。

让缓存效率更高&＃xff0c;许多应用可以缓存单表查询的结果&＃xff0c;那么下次查询的时候可以减少这次查询

单个查询减少 锁的竞争

更容易对数据库进行拆分

减少冗余记录查询

MySql 查询过程

MySQL 通信协议

MySQL 客户端和服务端的通信是半双工的&＃xff0c;这意味着同一个时刻内&＃xff0c;客户端和服务端只有一方在发送数据。一旦一方开始发送数据&＃xff0c;另外一端必须接受完整个消息才能进行响应。

这就是为什么当查询语句特别长的时候&＃xff0c;max_allowed_packet 特别重要了。所以在必要的时候需要添加 LIMIT 限制。

查询状态

对于一个 MySQL 连接&＃xff0c;任何时刻都有一个状态&＃xff0c;该状态表示了 MySQL 当前正在做什么&＃xff0c;用 SHOW FULL PROCESSLIST 命令即可。

Sleep

线程正在等待客户端发送新的请求。

Query

线程正在执行查询或者在将结果发送给客户端

Locked

该线程在等待表锁

Analyzing and statistics

线程正在收集存储引擎的统计信息&＃xff0c;并且生成执行计划。

Copying to tmp table

线程正在把数据复制到一个临时表中&＃xff0c;一般在 Group By 或者排序的时候会出现这个状态。

Sorting result

线程正在排序数据

Sending data

线程可能在多个状态之间传送数据&＃xff0c;或者在向客户端返回数据。

MySQL 对关联表顺序优化

MySQL 的优化器会对查询进行静态和动态优化&＃xff0c;期中我们只挑最重要的优化讲&＃xff0c;也就是对关联表顺序的优化。

我们先来看一个 UNION 的例子&＃xff0c;对于 UNION 查询&＃xff0c;MySQL 会将单个查询结构放入一个临时表(注意临时表是没有索引的)中&＃xff0c;然后再重新读出临时表数据来完成 UNION 查询。

MySQL 关联执行策略很简单&＃xff0c;对于任何关联都执行嵌套循环关联操作&＃xff0c;即先从一个表读出数据&＃xff0c;然后嵌套循环到下一个表中取出匹配的行&＃xff0c;依次下去&＃xff0c;直到找到所有的表中匹配的行为止。然后根据各个表匹配的行&＃xff0c;返回查询中需要的各个列。MySQL 会尝试在最后一个关联表中找到所有匹配的行&＃xff0c;如果最后一个关联表无法找到更多的行后&＃xff0c;MySQL 返回到上一层次关联表&＃xff0c;看是否能找到更多的匹配记录&＃xff0c;依次类推迭代查询。

关联查询优化器

MySQL 优化器决定了多个表关联的顺序&＃xff0c;关联优化器可以选择一个代价最小的关联顺序。

有时候优化器选择的不是最优的顺序&＃xff0c;这时候可以使用 STRAUGHT_JOIN 关键字进行查询&＃xff0c;让优化器按照你认为最优的顺序查询&＃xff0c;但是一般来说人判断的都没有优化器好。

优化器会尝试在所有的顺序中选择一个成本最小的关联顺序&＃xff0c;但是当表非常多的时候&＃xff0c;比如有 n 张表进行关联&＃xff0c;就要进行 n&＃xff01; 次比较。当表超过 optimizer_search_depth 的时候&＃xff0c;就会选择贪婪搜索模式了。

MySQL 查询优化器限制

子查询

MySQL 的子查询优化的相当糟糕&＃xff0c;最糟糕的一类是子查询中 WHERE 条件包含了 IN() 的子查询。比如用下面的语句查询

SELECT * FROM film WHERE film.id in (SELECT file_id from film_actor WHERE actor_id &＃61; 1)

我们可能会认为 MySQL 会执行后面的语句选择出 id 后才执行前面的查询&＃xff0c;但是 MySQL 会将外层查询压入子查询中

SELECT * FORM film WHERE EXISTS(SELECT * FROM film_actor WHERE actor_id &＃61; 1 AND film_actor.film.id &＃61; film.id)

这个查询会对 film 进行全表扫描&＃xff0c;性能非常糟糕。

所以我们最好用联合查询来代替这个查询。

这个问题直到 MySQL 5.5 还存在&＃xff0c;MySQL 另外一个分支 MariaDB 在原有的基础上做了大量的改进&＃xff0c;例如这里带 IN 的子查询。

当一个查询能被写成子查询和联合查询的时候&＃xff0c;最好通过一些测试来判断哪个写法更快一些

UNION

有时候 MySQL 无法将闲置条件由外层推到内层&＃xff0c;这使得本能限制扫描行数的 LIMIT 在内层查询中不起作用。

如果希望 UNION 的各个子句能根据 LIMIT 只取出部分结果集&＃xff0c;或者希望能先排好序再分别使用这些子句&＃xff0c;那么需要分别对这些查询使用 LIMIT 和 ORDER BY。

(SELECT * FROM XXX LIMIT 20) UNION ALL (SELECT * FROM XXX LIMIT 20)

并发执行

MySQL 无法利用多核特性来并发执行查询。

最大值和最小值

对于 MIN 和 MAX 查询&＃xff0c;MySQL 的优化做的不是很好&＃xff0c;

SELECT MIN(id) FROM actor

因为 id 是递增的&＃xff0c;所以只需要扫描一行即可&＃xff0c;但是 MySQL 仍然会做全表扫描。可以改下面的写法

SELECT id FROM actor LIMIT 1

特定优化查询

一般来说&＃xff0c;使用 Percona Toolkit 中的 pt-query-advisor 能够解析查询日志&＃xff0c;分析查询模式&＃xff0c;然后给出详细的建议来帮助你优化 SQL 语句。

优化 COUNT 查询

当 COUNT 的值不可能为空的时候&＃xff0c;MySQL 会转向统计行数。如果我们想要统计行数的时候&＃xff0c;最好直接使用 COUNT(*)。

使用近似值

有时候某些业务不需要精确值&＃xff0c;此时可以用近似值来代替&＃xff0c;EXPLAIN 出来的优化器估算的行数就是一个不错的近似值&＃xff0c;执行 EXPLAIN 不需要去真正的执行查询&＃xff0c;效率高很多。

优化关联查询

确保 ON 或者 USING 上的列有索引&＃xff0c;在创建索引的时候需要考虑到关联列的顺序&＃xff0c;比如说表 A&＃xff0c;B 用列 c 进行关联的时候&＃xff0c;如果优化器的关联顺序是 B&＃xff0c;A&＃xff0c;则只需要在 A 上建立索引即可。

确保任何的 GROUP BY 和 ORDER BY 只涉及到一个表中的列

优化子查询

关于子查询给出的最主要的优化方法是&＃xff1a;尽量使用关联查询代替子查询&＃xff0c;因为 MySQL 的子查询优化的非常烂。不过这条意见只在旧版本有用&＃xff0c;在 MySQL 5.6 以上和 MariaDB 中&＃xff0c;可以忽略掉这条优化。

优化 GROUP BY 和 DISTINCT

MySQL 经常用同样的方法来优化这两个查询&＃xff0c;它们都会用索引来优化&＃xff0c;这也是最有效的优化办法。

当无法使用索引的时候&＃xff0c;MySQL 会用临时表或者文件排序来执行 GROUP BY。

如果需要对关联查询做分组&＃xff0c;那么通常采用标识列来进行分组效率会比较高。

优化 LIMIT 分页

当系统需要进行分页操作的时候通常会使用 LIMIT 加 偏移量的操作&＃xff0c;同时加上合适的 ORDER BY 语句。如果有对应的索引&＃xff0c;效率通常会不错。

但是当偏移量非常大的时候&＃xff0c;LIMIT 10000,20&＃xff0c;这种语句会导致扫描了10020 行&＃xff0c;但是只返回 20 行。

优化这种查询的方法有&＃xff1a;

使用索引覆盖&＃xff0c;只搜索索引覆盖的行然后通过一次查询把所有需要的数据查找出来

通过延迟关联&＃xff0c;后面会讨论这个方法

优化 SQL_CALC_FOUND_ROWS

分页的时候有时候会通过在 LIMIT 语句中加上 SQL_CALC_FOUND_ROWS。这样就可以获取去掉 LIMIT 条件后查询的行数&＃xff0c;加上这个提示以后&＃xff0c;不管是否需要&＃xff0c;都会把全部的行都扫描一遍&＃xff0c;而不是在满足了 LIMIT 的大小后停止扫描&＃xff0c;这样会带来很大开销。

解决这个问题有两个方法

采用 EXPLAIN ROW 中的近似值&＃xff0c;有时候不需要那么精准的数据

先获得比较多的缓存集&＃xff0c;比如设置一个 100 页和一个 100 页以后的按钮&＃xff0c;当用户需要 100 页后的按钮再去获取。

优化 UNION 查询

除非确实需要服务器消除重复的行&＃xff0c;否则必须要使用 UNION ALL。

如果没有 ALL 关键字&＃xff0c;MySQL 会给临时表加上 DISTINCT 选项&＃xff0c;然后做一次查重操作&＃xff0c;这将带来极大的开销。