数据库服务器的优化步骤

当我们遇到数据库调优问题的时候,该如何思考了?

整个流程划分成了观察(Show status) 和行动(Action)两个部分。字母S的部分代表观察(会使用相应的分析工具),字母A代表的部分是行动(对应分析可以采取的行动)。

我们可以通过观察了解数据库整体的运行状态,通过性能分析工具可以让我们了解执行慢的SQL都有哪些,查看具体的SQL执行计划,甚至SQL执行中的每一步的成本代价,这样才能定位问题所在,找到了问题,在采取相应的行动。

首先在S1部分,我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动,有可能是周期性节点的原因,比如双十一、促销活动。这样的话,我们可以通过A1这一步骤解决,也就是加缓存,或者更改缓存失效策略。

如果缓存策略没有解决,或者不是周期性波动的原因,我么就需要进一步分析查询延迟和卡顿的原因。接下来进入S2这一步,我们需要开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。我们可以通过设置long_query_time参数定义 “慢”的阈值,如果SQL执行时间超过了long_query_time,则会认为是慢查询。当收集上来这些慢查询之后,我们就可以通过分析工具对慢查询日志进行分析。

在S3这一步骤中,我们就知道了执行慢的SQL,这样就可以调优服务器的参数,比如适当增加数据库缓冲池等。如果是SQL执行时间长,就进入A3步骤,这一步中我们需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。

如果A2和A3都不能解决问题,我们需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈,如果确认没有达到性能瓶颈,就需要重新检查,重复以上的步骤。如果已经达到了性能瓶颈,进入A4阶段,需要考虑增加服务器,采用读写分离的架构,或者考虑对数据库进行分库分表,比如垂直分库、垂直分表和水平分表等。

以上就是数据库调优的流程思路。如果我们发现执行SQL时存在不规则延迟或卡顿的时候,就可以采用分析工具帮我们定位有问题的sQL,这三种分析工具可以理解为时SQL调优的三个步骤:慢查询、EXPLAIN和SHOW PROFILING。

使用慢查询定位执行慢的SQL

慢查询可以帮我们找到执行慢的SQL,在使用前,我们需要看下慢查询是否已经开启,使用下面这条命令即可:

1
mysql > show variables like '%slow_query_log';

能看到slow_query_log = OFF,也就是说慢查询日志此时时关上的。我们可以把慢查询日志打开,注意设置变量值的是否需要使用global,否则会报错:

1
mysql> set global slow_query_log='ON';

然后我们再来看下慢查询日志是否开启,以及慢查询日志文件的位置:

你能看到这时慢查询分析已经开启,同时文件保存在DESKTOP-4BK02RP-slow文件中。

接下来我们来看下慢查询的时间阈值设置,使用如下命令:

1
mysql> show variables like '%long_query_time%';

如果我们想要把时间缩短,比如设置为3秒,可以怎样设置:

1
mysql> set global long_query_time = 3;

我们可以使用MySQL自带的mysqldumpslow工具统计慢查询日志(这个工具是个perl脚本,你需要先安装好perl)。

mysqldumpslow命令的具体参数如下:

  • -s:采用order排序的方式,排序方式可以有以下几种。分别是c(访问次数)、t(查询时间)、I(锁定时间)、ac(平均查询次数)、al(平均锁定时间)、ar(平均返回记录数)和at(平均查询时间)。其中at为默认排序方式。
  • -t:返回当前N条数据。
  • -g:后面可以是正则表大师,对大小写不敏感。

比如我们想要按照查询时间排序,查看前两条SQL语句,这样写成即可:

1
perl mysqldumpslow.pl -s t -t 2 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-4BK02RP-slow.log"

你能看到开启了慢查询日志,并设置了相应的慢查询时间阈值之后,只要大于这个阈值的SQL语句店铺会保存在慢查询日志中,然后我们就可以通过mysqldumpslow工具提取想要查找的SQL语句了。

如何使用EXPLAIN查看执行计划

定位了慢查询的SQL之后,我们就可以使用EXPLAIN工具做针对性的分析,比如我们想要了解product_comment和user表进行联查的时候采用的执行计划,可以使用下面这条语句:

1
EXPLAIN SELECT comment_id,product_id,comment_text,product_comment.user_id,user_name FROM product_comment JOIN user on product_comment.user_id = user.user_id;

EXPLAIN 可以帮助我们了解数据表的读取顺序,SELECT字句的类型、数据表的访问类型、可使用的索引、实际使用的索引、使用索引的长度、上一个表的连接匹配条件、被优化器查询的行的数量以及额外的信息(比如是否使用了外部排序,是否使用了临时表)等。

SQL执行的顺序是根据id从大到小执行的,也就是id越大越先执行,当id相同时,从上到下执行。

数据表的访问类型所对应的type列是我们比较关注的信息。type可能有以下几种情况:

在这些情况里,all是最坏的情况,因为采用了全表扫描的方式。index和all差不多,只不过index对索引表进行群扫描,这样做的好处是不在需要对数据进行排序,但是开销依然很大。如果我们在extra列中看到Using index,说明采用了索引覆盖,也就是索引可以覆盖所需的SELECT字段,就不要进行非标,这样就减少了数据查询的开销。

比如我们对product_comment数据表进行查询,设计了联合索引composite_index(user_id,comment_text),然后对数据表中comment_id、comment_text、user_id这三个字段进行查询,最后用EXOLAIN看下执行计划:

1
EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment 

你能看到这里的访问方式采用了index的方式,key列采用了联合索引,进行扫描。Extral列为Using index,告诉我们索引可以覆盖SELECT之中的字段 ,也不需要回表查询了。

range表示采用了索引范围扫描,这里不进行举例,从这一级别开始,索引的作用会越来越明显,因此我们需要尽量让SQL查询可以使用到range这一级别以及以上的type访问方式。

index_merge说明查询使用了两个或两个以上的索引,最后去了交集或者并集。比如想要对comment_id=500000或者user_id=500000的数据进行查询,数据表中comment_id为主键,user_id是普通索引,我们可以查看下执行计划:

1
EXPLAIN SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id = 500000 OR user_id = 500000

你能看到这里同时使用到了两个索引,分别是主键和user_id,采用的数据表访问类型是index_merge,通过union的方式对两个索引检索的数据进行合并。

ref类型表示采用了非唯一索引,或者是唯一索引的非唯一性前缀。比如我们想要对user_id=500000的评论进行查询,使用EXPLAIN查看执行计划:

1
EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE user_id = 500000 

这里user_id为普通索引(因为user_id在商品评论表中可能是从重复的),因此采用的访问类型是ref,同时在ref列中显示const,表示连接匹配条件是常量,用于索引列的查找。

eq_ref类型时使用主键或唯一索引时产生的访问方式,通常使用在多表联查中。假设我们对product_comment和user表进行联查,关联条件时两张表的user_id相等,使用EXPLAIN进行执行计划查看:

1
EXPLAIN SELECT * FROM product_comment JOIN user WHERE product_comment.user_id = user.user_id 

const类型表示我们使用了主键或者唯一索引(所有的部分)与常量值进行比较,比如我们想要查看comment_id=500000,查看执行计划:

1
EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE comment_id = 500000 

需要说明的是const类型和eq_ref都是使用了主键或唯一索引,不过这两个类型有所区别,const死于常量比较,查询效率更快,而eq_ref通常用于多表联查中。

system类型一般用于MyISAM或Memory表,属于const类型的特例,当表中只有一方时连接类型为system。

1
EXPLAIN SELECT * FROM test_myisam

你能看到除了all类型外,其他类型都可以使用到索引,但不同的连接方式的效率也会有所不同,效率从低到高一次为all<index<range<index_merge<ref<eq_ref<const/system。我们在查看执行计划的时候,通常希望执行计划至少可以使用到range级别以上的连接方式,如果只使用到了all或者index连接方式,我们可以从SQL语句和索引设计的角度上进行改进。

使用 SHOW PROFILE 查看SQL的具体执行成本

SHOW PROLILE相比EXPLAIN能看到更进一步的执行解析,包括SQL都做了什么、所花费的时间等。默认情况下,profiling是关闭的,我们可以在会话级别开启这个功能。

1
mysql > show variables like 'profiling';

通过设置profiling=’ON’ 来开启show profile:

1
mysql > set profiling = 'ON';

我们可以看下当前会话都有哪些profiles,使用下面这条命令:

1
mysql> show profiles;

你能看到当前会话一共有两个查询,如果我们想要查看上一个查询的开销,可以使用:

1
mysql> show profile;

我们也可以查看指定的QueryID的开销,比如show profile for query 2 查询结果是一样的。在SHOW PROFILE中我们查看不同部分的开销,比如cpu、block.io等:

通过上面的结果,我们可以弄清楚每一步骤的耗时,以及在不同部分,比如CPU、block.io的执行时间,这样我们就可以判断出来SQL到底慢在哪里。

不过SHOW PROFILE命令将被弃用,我们可以从information_schema中的profiling数据表进行查看。

总结