MySQL性能调优与设计——MySQL调优——慢查询

MySQL调优金字塔

很明显从图上可以看出&＃xff0c;越往上走&＃xff0c;难度越来越高&＃xff0c;收益却越来越小。

对于架构调优&＃xff0c;在系统设计时首先需要充分考虑业务的实际情况&＃xff0c;是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做&＃xff1b;然后考虑写的并发量有多大&＃xff0c;是否需要采用分布式&＃xff1b;最后考虑读的压力是否很大&＃xff0c;是否需要读写分离。对于核心应用或者金融类的应用&＃xff0c;需要额外考虑数据安全因素&＃xff0c;数据是否不允许丢失。所以在进行优化时&＃xff0c;首先需要关注和优化的应该是架构&＃xff0c;如果架构不合理&＃xff0c;即使是DBA能做的事情其实也是比较有限的。

对于MySQL调优&＃xff0c;需要确认业务表结构设计是否合理&＃xff0c;SQL语句优化是否足够&＃xff0c;该添加的索引是否都添加了&＃xff0c;是否可以剔除多余的索引等等。

对于硬件和OS调优&＃xff0c;需要对硬件和OS有着非常深刻的了解&＃xff0c;仅仅就磁盘一项来说&＃xff0c;一般非DBA能想到的调整就是SSD盘比用机械硬盘更好。DBA级别考虑的至少包括了使用什么样的磁盘阵列(RAID)级别、是否可以分散磁盘IO、是否使用裸设备存放数据&＃xff0c;使用哪种文件系统(目前比较推荐的是XFS)&＃xff0c;操作系统的磁盘调度算法选择&＃xff0c;是否需要调整操作系统文件管理方面比如atime属性等等。

查询性能优化

前面章节我们知道如何设计最优的库表结构、如何建立最好的索引&＃xff0c;这些对于高性能来说必不可少&＃xff0c;但这些还不够&＃xff0c;还需要合理的设计查询。如果查询写得很糟糕&＃xff0c;即使库表结构再合索引再合适&＃xff0c;也无法实现高性能。

慢查询

什么是慢查询

慢查询日志&＃xff0c;就是查询花费大量时间的日志&＃xff0c;是指MySQL记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志&＃xff0c;该日志能为SQL语句的优化带来很好的帮助。默认情况下&＃xff0c;慢查询日志是关闭的&＃xff0c;要使用慢查询日志功能&＃xff0c;需要开启慢查询日志功能(具体怎么开&＃xff0c;下面有&＃x1f447;)。

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

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询&＃xff0c;一般通过下面两个步骤来分析总是很有效:

1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行&＃xff0c;但有时候也可能是访问了太多的列。
2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

请求了不需要的数据

有些查询会请求超过实际需要的数据&＃xff0c;然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担&＃xff0c;并增加网络开销&＃xff0c;另外也会消耗应用服务器的CPU和内存资源。比如:

查询不需要的记录

一个常见的错误是常常会误以为MySQL会只返回需要的数据&＃xff0c;实际上MySQL却是先返回全部结果集再进行计算。我们经常会看到一些了解其他数据库系统的人会设计出这类应用程序。

以上SQL你认为MySQL会执行查询&＃xff0c;并只返回他们需要的20条数据&＃xff0c;然后停止查询。实际情况是MySQL会查询出全部的结果集&＃xff0c;客户端的应用程序会接收全部的结果集数据&＃xff0c;然后抛弃其中大部分数据。

总是取出全部列

每次看到SELECT*的时候都需要用怀疑的眼光审视&＃xff0c;是不是真的需要返回全部的列&＃xff1f;很可能不是必需的。取出全部列&＃xff0c;会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O、内存和CPU的消耗。因此&＃xff0c;一些DBA是严格禁止SELECT *的写法的&＃xff0c;这样做有时候还能避免某些列被修改带来的问题。

尤其是使用二级索引&＃xff0c;使用*的方式会导致回表&＃xff0c;导致性能低下。

什么时候可以使用SELECT*如果应用程序使用了某种缓存机制&＃xff0c;或者有其他考虑&＃xff0c;获取超过需要的数据也可能有其好处&＃xff0c;但不要忘记这样做的代价是什么。获取并缓存所有的列的查询&＃xff0c;相比多个独立的只获取部分列的查询可能就更有好处。

重复查询相同的数据

不断地重复执行相同的查询&＃xff0c;然后每次都返回完全相同的数据。比较好的方案是&＃xff0c;当初次查询的时候将这个数据缓存起来&＃xff0c;需要的时候从缓存中取出&＃xff0c;这样性能显然会更好。

是否再扫描额外的记录

在确定查询只返回需要的数据以后&＃xff0c;接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL&＃xff0c;最简单的衡量查询开销的三个指标如下:

响应时间、扫描的行数、返回的行数

没有哪个指标能够完美地衡量查询的开销&＃xff0c;但它们大致反映了MySQL在内部执行查询时需要访问多少数据&＃xff0c;并可以大概推算出查询运行的时间。这三个指标都会记录到MySQL的慢日志中&＃xff0c;所以检查慢日志记录是找出扫描行数过多的查询的好办法。

响应时间

响应时间是两个部分之和:服务时间和排队时间。

服务时间是指数据库处理这个查询真正花了多长时间。

排队时间是指服务器因为等待某些资源而没有真正执行查询的时间—-可能是等I/O操作完成&＃xff0c;也可能是等待行锁&＃xff0c;等等。

扫描的行数和返回的行数

分析查询时&＃xff0c;查看该查询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询找到需要的数据的效率高不高。

理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种“美事”并不多。例如在做一个关联查询时&＃xff0c;服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小&＃xff0c;一般在1:1和10:1之间&＃xff0c;不过有时候这个值也可能非常非常大。

扫描的行数和访问类型

在评估查询开销的时候&＃xff0c;需要考虑一下从表中找到某一行数据的成本。MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果&＃xff0c;也有些访问方式可能无须扫描就能返回结果。

在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种&＃xff0c;从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些&＃xff0c;速度是从慢到快&＃xff0c;扫描的行数也是从小到大。你不需要记住这些访问类型&＃xff0c;但需要明白扫描表、扫描索引、范围访问和单值访问的概念。

如果查询没有办法找到合适的访问类型&＃xff0c;那么解决的最好办法通常就是增加一个合适的索引&＃xff0c;为什么索引对于查询优化如此重要了。索引让 MySQL以最高效、扫描行数最少的方式找到需要的记录。

一般 MySQL能够使用如下三种方式应用WHERE条件&＃xff0c;从好到坏依次为:

• 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。

select .... from where a>100 and a <200

• 使用覆盖索引扫描来返回记录&＃xff0c;直接从索引中过滤不需要的记录并返回命中的结果。这是在 MySQL服务器层完成的&＃xff0c;但无须再回表查询记录。

• 从数据表中返回数据(存在回表)&＃xff0c;然后过滤不满足条件的记录。这在 MySQL服务器层完成&＃xff0c;MySQL需要先从数据表读出记录然后过滤。

好的索引可以让查询使用合适的访问类型&＃xff0c;尽可能地只扫描需要的数据行。

如果发现查询需要扫描大量的数据但只返回少数的行&＃xff0c;那么通常可以尝试下面的技巧去优化它:

1. 使用索引覆盖扫描&＃xff0c;把所有需要用的列都放到索引中&＃xff0c;这样存储引擎无须回表获取对应行就可以返回结果了
2. 改变库表结构。例如使用单独的汇总表。
3. 重写这个复杂的查询&＃xff0c;让MySQL优化器能够以更优化的方式执行这个查询。

慢查询配置

我们已经知道慢查询日志可以帮助定位可能存在问题的SQL语句&＃xff0c;从而进行SQL语句层面的优化。但是默认值为关闭的&＃xff0c;需要我们手动开启。

show VARIABLES like &＃39;slow_query_log&＃39;;


-- 开启1 关闭0
set GLOBAL slow_query_log&＃61;1;


但是多慢算慢&＃xff1f;MySQL中可以设定一个阈值&＃xff0c;将运行时间超过该值的所有SQL语句都记录到慢查询日志中。long_query_time参数就是这个阈值。默认值为10&＃xff0c;代表10秒。

show VARIABLES like &＃39;%long_query_time%&＃39;;


当然也可以设置。

-- 默认10秒&＃xff0c;这里为了演示方便设置为0
set global long_query_time&＃61;0;


同时对于运行的SQL语句没有使用索引&＃xff0c;则MySQL数据库也可以将这条SQL语句记录到慢查询日志文件&＃xff0c;控制参数是&＃xff1a;

-- 设置方式同上
show VARIABLES like &＃39;%log_queries_not_using_indexes%&＃39;;


慢查询日志的存放路径&＃xff1a;

show VARIABLES like &＃39;%slow_query_log_file%&＃39;;