《软件性能测试分析与调优实践之路》第二版-手稿节选-Mysql数据库性能定位与分析

在做MySQL数据的性能定位前,需要先知道MySQL查询时数据库内部的执行过程。只有弄清SQL的执行过程,才能对执行过程中的每一步的性能做定位分析。如图6-2-1所示。

图6-2-1

从图中可以看到,当查询出数据以后,会将数据先返回给执行器,此时执行器先将结果写到查询缓存里面,这样在下次查询相同的数据时,就可以直接从缓存中查询并且返回,同时将结果返回给客户端。分析器会对待查询的SQL语句做执行计划的分析,而优化器会对SQL语句做重新优化,以便SQL的查询性能达到最佳。

6.2.1  慢SQL

每条SQL语句在执行时都需要消耗一定的I/O资源,SQL语句执行的快慢直接决定了硬件资源被占用时长的长短,慢SQL一般指查询很慢的SQL语句。在MySQL数据库中,可以通过慢查询来查看所有执行超时的SQL语句。在默认情况下,一般慢SQL是关闭的,可以通过执行show variables like 'slow_query%' 来查看数据库是否开启了慢查询,如图6-2-2所示。

图6-2-2

从图6-2-2中看到slow_query_log的值为OFF表示慢查询未开启,可以通过执行命令“set global slow_query_log=1; ”或者“set global slow_query_log=ON;”来临时开启慢查询,如图6-2-3所示。

图6-2-3

如果需要永久开启,就需要修改/etc/my.cnf配置文件,在[mysqld]处加入如下配置,再重启数据库即可生效,如下所示。

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

slow_query_log=ON

slow_query_log_file=/var/lib/mysql/localhost-slow.log

修改完成重启数据库后,再次执行show variables like 'slow_query%',发现慢查询已经被开启,如图6-2-4所示。

图6-2-4

通过执行“show variables like 'long_query%';”可以查询慢查询的记录时间,如图6-2-5所示。慢查询的记录时间默认是10秒,可以通过执行“set long_query_time=需要修改的时长;”来修改慢查询的记录时间。

图6-2-5

通过执行“show status like 'slow_queries';”可以查看慢查询发生的次数,如图6-2-6所示。

图6-2-6

从慢查询日志中,我们也可以看到慢查询发生的详细信息,如图6-2-7所示。慢查询日志中会记录每次慢查询发生的时间、执行查询时的数据库用户、线程id、查询执行的SQL语句等信息。

图6-2-7

在获取到慢查询的SQL语句后,就可以借助数据库的执行计划来对慢查询的SQL语句做进一步的分析。(节选自《软件性能测试、分析与调优实践之路》(第2版),作者张永清,转载请注明出处)

6.2.2  执行计划

在MySQL中使用explain关键字可以模拟查看数据库是如何执行SQL查询语句,也就是常说的查看一条SQL语句在数据库中的执行计划。图6-2-8所示就是执行EXPLAIN SELECT * FROM test.test 后返回的SELECT * FROM test.test查询的执行计划。

图6-2-8

查询结果返回的字段说明如下所示。

(1)id。查询的顺序编号,表示查询中执行的顺序。id的值越大,执行的优先级越高;如果id相同,则从上往下执行。

(2)select_type。查询类型,常见查询类型说明如下:

  •         SIMPLE:表示简单查询方式。SQL语句中一般不会不使用UNION和子查询等。
  •        PRIMARY:表示包含子查询的SQL语句的最外层查询语句的查询类型,即当查询中包含子查询时,最外层的查询语句就会显示为PRIMARY。
  •          UNION:在查询语句中,如果在UNION关键字之后出现了第二个SELECT,则被标记为UNION。
  •          UNION RESULT:表示查询中有多个查询结果集执行UNION操作。
  •         DEPENDENT UNION:表示在子查询中存在UNION操作时,从UNION之后的第二个及之后的SELECT语句都是DEPENDENT UNION。
  •          DEPENDENT SUBQUERY:子查询中UNION 中第一个SELECT查询为DEPENDENT SUBQUERY。
  •          SUBQUERY:子查询内层查询的第一个SELECT。
  •          DERIVED:在查询语句中,如果from子句的子查询中出现了union关键字,则外层select查询将被标记为DERIVED。
  •          MATERIALIZED:表示子查询被物化,物化通过将子查询结果作为一个临时表来加快查询执行速,从而能够使得子查询只执行一次。
  •          UNCACHEABLE SUBQUERY:表示查询结果集无法缓存的子查询,需要逐次查询。
  •          UNCACHEABLE UNION:表示子查询不可被物化,需要逐次运行(即需要执行多次)    (节选自《软件性能测试、分析与调优实践之路》(第2版),作者张永清,转载请注明出处)

(3)table。查询涉及的表名或者表的别名。

(4)type。表示表连接的类型,包括的类型说明如下。这些类型的性能从高到低的顺序是null→system→const→eq_ref→ref→fulltext→ref_or_null→index_merge→unique_subquery→index_subquery→range→index→ALL。

  •         null:表示不访问任何的表。
  •         system:表示表中只有一条记录,相当于系统表。一般可以认为是const类型的特例。
  •         const:表示主键或者唯一索引的常量查询,表中最多只有1行记录符合查询要求。通常const使用到主键或者唯一索引进行定值查询、常量查询,查询的速度非常快。
  •          eq_ref:表示join 查询过程中,关联条件字段使用主键或者唯一索引,出来的行数不止一行。eq_ref是一种查询性能很高的 join 操作。
  •          ref:表示非聚集索引的常量查询。
  •          fulltext:表示查询的过程中,使用了fulltext类型的索引。
  •          ref_or_null:跟ref查询类似,在ref的查询基础上会多加一个null值的条件查询。
  •          index_ merg:表示索引联合查询。
  •          unique_ subquery:表示查询使用主键的子查询。
  •         index_subquery:表示查询使用非聚集索引的子查询。
  •          range:表示查询通过使用索引范围的查询。一般包括:=、<>、>、>=、<、<=、IS NULL、BETWEEN、IN、<=> 等范围。
  •          index:表示通过索引进行扫描查询。
  •          ALL:表示全表扫描,性能最差。

(5)possible_keys。查询时预计可能会使用的索引。这里说的索引只是可能会用到,实际查询不一定会用到。

(6)key。实际查询时真实使用的索引。

(7)key_len。使用的索引长度。

(8)ref 。关联信息。

(9)rows。查询时扫描的数据记录行数。

(10)extra。表示查询特性的使用情况。常用的查询特性如下所示:

  •          Using index:表示使用了索引(通常也可以叫覆盖索引)。
  •          Using index condition:表示使用了索引进行过滤。
  •          Using MRR:表示使用了索引进行内部排序。
  •          Using where:表示使用了where条件。
  •          Using temporary:表示使用了临时表。
  •          Using filesort:表示使用文件排序(一般指无法利用索引来完成的排序)。

6.2.3  MySQL数据库性能定位步骤

MySQL数据库性能定位的常见步骤总结如图6-2-8所示。

图6-2-8

(1)首先通过本书第2章中服务器的性能监控与分析,找到当前服务器的资源使用情况,重点关注CPU、内存、磁盘等使用率。

(2)根据服务器资源的使用率情况,初步判断当前MySQL数据库可能在执行的操作类型。(节选自《软件性能测试、分析与调优实践之路》(第2版),作者张永清,转载请注明出处)

  •         如果是内存和CPU使用率过高,或者磁盘读取数据频繁,说明可能存在大量SQL查询或者统计计算操作。
  •          如果是磁盘写入频繁,说明可能存在大量频繁的insert或者update 操作。

(3)通过在数据库中执行SHOW FULL PROCESSLIST 命令观察当前正在运行的SQL 命令,可以每间隔5~10s 多执行几次该命令,找到哪些SQL操作是持续一直在运行中。

  •          如果是insert 或者update 语句,通过查看本书的6.1.2小节中的知识讲解,来查看MySQL数据库当前运行的事务与锁,获取insert 或者update的SQL操作是否存在锁等待等情况,从而导致insert和update 一直处于等待中。
  •        如果是查询操作,可以结合慢查询日志一起,找到慢查询的SQL语句,使用explain关键字查看SQL的执行计划来定位SQL为什么查询慢。

(节选自《软件性能测试、分析与调优实践之路》(第2版),作者张永清,转载请注明出处)

 

热门相关:神话版三国   大唐第一世家   信息全知者   梁医生又在偷偷套路我   超级融合