MySQL进阶_3.性能分析工具的使用

第一节、数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，可以按照以下流程进行分析。整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

第二节、查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。

SHOW [GLOBAL|SESSION] STATUS LIKE '性能参数';

性能参数包括：

• Connections：连接MySQL服务器的次数
• Uptime：MySQL服务器的上线时间
• Slow_queries：慢查询的次数
• Innodb_rows_read：Select查询返回的行数
• Innodb_rows_inserted：执行INSERT操作插入的行数
• Innodb_rows_updated：执行UPDATE操作更新的行数
• Innodb_rows_deleted：执行DELETE操作删除的行数
• Com_select：查询操作的次数
• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次
• Com_update：更新操作的次数
• Com_delete：删除操作的次数
• last_query_cost：一条SQL查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划,如果我们想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是 SQL语句所需要读取的页的数量。

第三节、 慢查询日志

• 目前公司里有DBA做这些事情，如果以后有需要再详细学习。
• MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句。具体指运行时间超过long_query_time值的SQL，会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒)的语句，认为是超出了我们的最大忍耐时间值。|
• 它的主要作用是：帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。
• 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
• 慢查询日志支持将日志记录写入文件。

第四节、 查看 SQL 执行成本

Show Profile是MysQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

如上图，可以看到一条SQL语句执行过程中的各个步骤，以及每个步骤的耗时。还可以添加查询参数，比如：cpu。

第五节、 分析查询语句：EXPLAIN

• 目前在公司里应该还用不到这部分内容，如果以后用到再来补充。

定位了查询慢的SQL之后，我们就可以使用EXPLAIN或 DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。

5.1 基本语法

EXPLAIN SELECT name FROM comment WHERE id = 3;

输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进使查询执行起来更高效。其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、 REPLACE以及UPDATE语句等都可以加上EXPLAIN，用来查看这些语句的执行计划，只是平时我们对SELECT语句更感兴趣。注意:执行EXPLAIN时并没有真正的执行后面的语句，因此可以安全的查看执行计划。

5.2 EXPLAIN各列作用

列名	说明
id	在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

为了清楚了解各个列的作用，创建两个表s1和s2.

CREATE TABLE s1 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;CREATE TABLE s2 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

1.table

不论查询语句有多复杂，里边包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

2.id

• id如果相同，可以认为是一组，从上往下顺序执行
• 在所有组中，id值越大，优先级越高，越先执行
• id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

3.select_type

一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句。而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录。对于在同一个SELECT关键字中的表来说，它们的id值是相同的。MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

4.type

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称"访问类型”，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。

访问方法包括： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL。

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null >> index_merge >unique_subquery > index_subquery > range > index > ALL。> 其中比较重要的几个提取出来（红色的字体）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是> const级别。（阿里巴巴开发手册要求）

5.possible_keys和key

在EXPLAIN语句输出的执行计划中， possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。

6.key_len

实际使用到的索引长度（字节数）。帮助检查是否充分的利用上了索引，值越大越好。

7.rows

预估的需要读取的记录条数，值越小越好。

8.filtered

某个表经过搜索条件过滤后剩余记录条数的百分比

9.Extra

用来说明一些额外信息，包含不适合在其他列中显示但十分重要的额外信息，可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。