慢查询
慢查询为什么会慢
什么是慢查询
MySQL慢查询就是在MySQL慢查询日志中记录查询比较慢的语句，需要通过命令或者配置开启慢查询日志。
从查询语句的执行过程来谈一谈为什么会慢
想要知道一条查询语句为什么会慢，我们需要了解一条查询语句是怎么执行的，只有了解了它的每一个执行步骤及每一步可能出现的问题，优化查询的时候才能做到有的放矢。
林晓峰老师在他的《MySQL45讲》剖析了一条SQL查询语句的执行过程，这里我简单的做一个总结。一条SQL查询语句的执行流程主要分为以下几步：
- 客户端与连接器建立连接，发出查询请求
- 查询缓存，如果命中则直接返回结果
- 没有命中缓存，真正执行SQL语句，分析器对SQL语句进行分析
- 经过分析器处理，MySQL知道要做什么了，但在开始执行之前，优化器先进行优化处理
- 优化器处理之后，执行器开始真正的执行，执行结束后把结果返回给客户端
以上就是一条MySQL查询语句执行的全部流程，对于每一步细节，本文先不做讨论。接下来我们回到本章的重点，分析一条查询语句为什么会慢。
（1）网络延迟
客户端与MySQL服务器端建立连接，然后向服务器端发送查询请求，服务器将查询结果返回给客户端，因此网络的原因会导致一条SQL查询语句迟迟得不到响应。
（2）CPU性能瓶颈
CPU饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
（3）IO性能瓶颈
磁盘IO瓶颈发生在装入数据远大于内存容量的时候
（4）服务器参数设置不合适
针对以上三个问题可以使用Linux的命令查看系统的性能状态
（5） 数据库正在刷新脏页，例如 redo log写满了需要同步到磁盘
（6）SQL语句写的比较烂
- 查询了太多的数据，包括查询了多的行或者太多的列
- 扫描额外的记录
如何排查慢查询
慢查询日志
什么是慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
慢查询日志相关参数
-
slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。
-
log-slow-queries：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
-
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
-
long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
-
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
-
log_output：日志存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。
如何配置慢查询日志
方法一：使用命令的方式,只在当前连接有效
查看慢查询日志是否开启
show variables like ‘slow_query%’;
开启慢查询日志：
set global slow_query_log=1;
方法二：修改配置文件，windows下是my.ini,ubuntu 下是my.cnf(centos中文件位于/etc/my,cnf)，修改后需要重启MySQL服务才可以生效。
配置如下：
# General and Slow logging.
log-output=FILE ##日志的输出格式，FILE为输出到日志文件 TABLE为输出到表，可以用逗号隔开同时制定两个
general-log=0 ##开启全局日志
slow-query-log=1 ## 开启慢查询
slow_query_log_file="mysql-slow.log" ##指定慢查询日志文件的位置
long_query_time=10 ##查询时间超过多少记录到慢查询日志
log-queries-not-using-indexes=1 ##未使用索引的查询也被记录到慢查询日志中（可选项）
常用工具的使用
日志分析工具mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
mysqldumpslow位于mysql安装包的bin目录下。
常用参数：
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
应用举例：
- 得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
- 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
- 得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
- 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
如何优化慢查询
扫描太多的行
案例：
使用select语句查询大量的结果，然后获取前面N行后关闭结果集（例如在新闻网站取出100条数据，但是只是在页面显示前面10条）。这种情况下，MySQL会查询出全部结果集，客户端应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。
解决方案：
在查询后面加上LIMIT
多表关联时返回太多的列
如下，你想要查询所有在电影Academy Dinosaur中出现的演员，如果按照以下的写法
select * from sakila.actor
INNER JOIN sakila.film_actor USING(actor_id)
INNER JOIN sakila.film USING(film_id)
WHERE sakila.film.title='Acdemy Dinosaur';
这将返回这三个表的全部的列。正确的方式应该是像下面这样
select sakila.actor.* from sakila.actor
INNER JOIN sakila.film_actor USING(actor_id)
INNER JOIN sakila.film USING(film_id)
WHERE sakila.film.title='Acdemy Dinosaur';
每次用到select * 的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？
使用select *会导致以下问题
（1）取出全部的列，会让优化器无法完成索引覆盖扫描这类优化；
（2）会给服务器带来额外的I/O、内存和CPU的消耗
重复查询相同数据
即不断的重复执行相同的查询，然后每次都会返回完全相同的数据。
场景：
在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能会反复查询这个数据。
解决方案：第一次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能会更好。
常用的图片存储方式是，将图片存储到图片服务器，然后在MySQL中保存图片的URL，用户第一次访问的时候将图片缓存到本地，再次使用到的时候从本地加载。
是否扫描额外的记录
上面提到的是查询返回的数据过多，这里讨论的是为了返回这些查询结果是否扫描了过多的数据。
对于MySQL，最简单的衡量查询开销的三个指标如下：
- 响应时间
- 扫描的行数
- 返回的行数
这三个指标都会被记录到慢查询日志中，所以慢查询日志是找出扫描行数过多的查询的好办法
分析查询时，查看该查询扫描的行数是非常有帮助的。在一定的程度上能够说明该查询找到需要的数据的效率高不高。
可以使用EXPLAIN命令查看扫描的行数和扫描的类型
如果发现查询需要扫描大量的数据但是只返回少量的行，可以使用下面的技巧来优化：
（1）使用索引覆盖扫描，把所有需要的列都放在索引中，这样存储引擎无需回表获取对应的行就可以返回结果了
（2）改变库表结构
（3）重构这个复杂的查询，让MySQL优化器能以更优化的防止来执行这个查询
重构查询方式
切分查询
有时候对于一个大查询我们需要"分而治之",将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
举例：
删除旧的数据。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据，占满整个事务日志、耗尽系统资源、阻塞很多小但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小的影响MySQL的性能，同时还可以减少MySQL复制的延迟。
例如我们需要每个月运行一次下面的查询
DELETE FROM message WHERE created < DATA_SUB(NOW(),INTERVAL 3 MONTH);
可以用类似下面的方法来完成同样的操作
rows_affected=0
do{
rows_affected=do_query(
"DELETE FROM message WHERE created < DATA_SUB(NOW(),INTERVAL 3 MONTH)
LIMIT 10000")
}while rows_affected>0
如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到很长的一段时间中，就可以大大降低对服务器的影响，还可以减少删除时锁的持有时间。
分解关联查询
很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。例如下面这个查询：
SELECT * FROM tag
JOIN tag_post.tag_id=tag.id
JOIN post ON tag_post_id=post.id
WHERE tag.tag='mysql'
可以分解成下面这些查询来代替：
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123,456,90908,8902);
这样做的好处：
- 让缓存效率更高。很多应用程序可以方便地缓存单表查询对应的结果对象。例如上面查询中的tag已经被缓存，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了id为123,567的内容，那么第三个查询的IN()中就可以少几个ID。 另外，对MySQL的查询缓存来说，如果关联中的某某个表发生了变化，那么就无法使用查询缓存了，而拆分后如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存的结果。
- 将查询分解够，执行单个查询可以减少锁的竞争
- 在应用层做关联，更容易对数据库进行拆分，更容易做到高性能和可扩展。
- 查询效率也可能会有所提升。在这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
- 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复的访问一部分数据。从这点看，这样的重构还可能会减少网络的内存和消耗。
- 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。
在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多，比如：当应用能够方便地缓存单个查询结果的时候，当可以将数据分布到不同的MySQL服务器上的时候，当能够使用IN()的方式代替关联查询的时候、当查询中使用同一个数据的时候。
TODO
- 慢查询日志
- 重构查询的方式
- 查询语句的执行流程
- 查询语句的优化方式
参考资料
- MySQL慢查询日志总结
- MySQL实战45讲
- 《高性能MySQL》
150
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
