开发俏皮话

【让我 996 不算啥，我只怕测试也 996 给我提 bug！】

笔者瞩望

你好，无论我们在现实生活中是否相识，在 InfoQ 的世界里终会快乐相遇，在此提前预祝国庆节快乐，并且在属于我们的“1024”那天不在加班，早点回家陪陪老婆和孩子啊。

技术金字塔

本篇文章会按照自上而下以及自下而上的两种方向去“游览”【MySQL 技术金字塔】，两个方向分别是从成本出发的（潜台词就是便宜越好，照顾公司成本哦！），本章内容，可能有点多，希望大家慢慢消化，实在不行来片“吗丁啉”，哈哈，开玩笑了！

主要技术分布为 6 大部分，如下图金子图所示：

研发成本角度

从软件的【研发成本】的角度而言：伴随着优化的方向，从金字塔顶部像金字塔底部的方向进行过度，伴随着高度越来越低，成本会越来越低，这个方向其实是非常考验技术人员与项目管理者的能力的，但是它确实，老板对象看到的，哈哈。

实现效果角度

从软件的【技术可行性和效果】的角度而言：伴随着优化的方向，从金字塔低部像金字塔顶部的方向进行过度，伴随着高度越来越高，成本会越来越高，耗费的财力和人力也会相对的有所降低，但是如果多花钱，老板肯定不愿意，比如，请一些行业大牛或者一些牛掰的服务器等，可以看出来正好与上面的方向相反。

总结一下，以上这两点的方针，遵循着研发成本的越来越低+效果方案越来越高，那么我们就划分出一个公式，作为系统服务调优方法论，我们就按照金字塔层面，进行自下而上进行调优！我们接下来就来按照这个方向进行分析。

调优白皮书

SQL 调优

根据业务需求，不单纯的写好 SQL 语句，还要对 SQL 语句进行调优，使得其性能变得最佳化。

调优思路

由三个步骤组成：发现问题、分析问题和解决问题。

发现慢 SQL 及日志

查询慢 SQL 的日志是 MySQL 内置的一个功能，可以记录执行时间超过我们配置阈值的 SQL 语句。

参数与默认值：

修改数据库服务配置

1. 修改我们安装后的配置文件 my.cnf，在[mysqld]段落中加入以上参数配置：

2. 之后进行重启服务

修改全局会话配置

这种方式，不需要重启就可以生效，但是当服务器重启的时候，又会重新丢失配置。以上的配置可以

将慢查询 SQL 记录到 mysql 数据库中的 slow_log 表中以及对应的 slow_sql 的文件中去。

分析慢 SQL 及日志

分析慢 SQ 的查询日志

查询 slow_log 表，当根据上面的设置，当 log_output 设置为 TABLE 的时候，就会将 mysql 的慢查询日志记录到 mysql.slow_log 表中去，我们可以采用select * from mysql.slow_log去进行查询，可以根据此方面进行分析和统计 sql 的执行性能。

分析慢 SQL 日志文件

当 log_output 设置为 FILE 的时候，因为文件过大，不方便查看，所以可以采用专门的工具进行分析，这里主要介绍原生的 mysqldumpslow 工具进行分析，如下图所示：

mysqldumpslow --help：

• 查询出返回结果行数最多的 20 条 SQL：
  

• 根据时间进行排序，并且带有 left join 的 20 条 SQL：
  

explain 命令分析慢 SQL

explain 关键字进行执行慢 SQL 语句，进行指标分析：

返回的基本结果如下：

• id 字段：表示代表着语句 SQL 中每一个部分原子查询（维护）操作的标识单位，如果 explain 中的有多个 id 对应的数据项，那么切记一定要按照：倒叙进行执行：

• 数字越大的，越先执行分析

• 数字编号相同，从上到下进行分析

• select_type 字段

查询类型，如下几组值：

• table 字段：它表示当前这一行正在访问哪张表，如果 SQL 定义了别名，则展示表的别名

• partitions 字段：当前查询匹配记录的分区。对于未分区的表，返回 null。

• type 字段：连接类型，有如下几种取值，性能从好到坏排序 如下:

• system：该表只有一行(相当于系统表)，system 是 const 类型的特例。

• const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可。

• eq_ref：当使用了索引的全部组成部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 才会使用该类型，性能仅次于 system 及 const。

• 多表+单行匹配

• 多表+联合索引+多行匹配

• ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。

• 根据索引(非主键，非唯一索引)，匹配到多行

• 多表关联查询，单个索引，多行匹配

• 多表关联查询，联合索引，多行匹配

• ref_or_null：该类型类似于 ref，但是 MySQL 会额外搜索哪些行包含了 NULL。这种类型常⻅于解析子查询。

• index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引。

• unique_subquery：该类型和 eq_ref 类似，但是使用了 IN 查询，且子查询是主键或者唯一索引。例如:

• index_subquery

和 unique_subquery 类似，只是子查询使用的是非唯一索引

• range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常⻅的范围扫描是带有 BETWEEN 子句或 WHERE 子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。

• index：全索引扫描，和 ALL 类似，只不过 index 是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发:

• 如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain 的 Extra 列的结果是 Using index。index 通常比 ALL 快，因为索引的大小通常小于表数据。

• 按索引的顺序来查找数据行，执行了全表扫描。此时，explain 的 Extra 列的结果不会出现 Uses index。

• ALL：全表扫描，性能最差。

• possible_keys：展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。

• key：表示 MySQL 实际选择的索引

• key_len：索引使用的字节数。由于存储格式，当字段允许为 NULL 时，key_len 比不允许为空时大 1 字节。

• key_len 计算公式：表示将哪个字段或常量和 key 列所使用的字段进行比较。 如果 ref 是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在 EXPLAIN 语句之后紧跟一个 SHOW WARNING 语句。

• rows：SQL 执行后会扫描的行数，数值越小越好。

• filtered：符合查询条件的数据百分比，最大 100。用 rows × filtered 可获得和下一张表连接的行数。例如 rows = 1000， filtered = 50%，则和下一张表连接的行数是 500。

• MySQL5.7 之前，想要显示此字段需使用 explain extended 命令; MySQL.5.7 及更高版本，explain 默认就会展示 filtered

• Extra(重点分析)：展示有关本次查询的附加信息，取值如下:
  

• Child of 'table' pushed join@1：此值只会在 NDB Cluster 下出现。

• const row not found：查询语句 SELECT ... FROM tbl_name，而表是空的

• Deleting all rows：对于 DELETE 语句，某些引擎(例如 MyISAM)支持以一种简单而快速的方式删除所有的数据，如果使用了这种优化，则显示此值.

• Distinct：查找 distinct 值，当找到第一个匹配的行后，将停止为当前行组合搜索更多行 FirstMatch(tbl_name)当前使用了半连接 FirstMatch 策略.

• Full scan on NULL key：子查询中的一种优化方式，在无法通过索引访问 null 值的时候使用

• Impossible HAVING：HAVING 子句始终为 false，不会命中任何行

• Impossible WHERE：WHERE 子句始终为 false，不会命中任何行

• Impossible WHERE noticed after reading const tables：MySQL 已经读取了所有 const(或 system)表，并发现 WHERE 子句始终为 falseLooseScan(m..n)当前使用了半连接 LooseScan 策略，

• No matching min/max row：没有任何能满足例如 SELECT MIN(...) FROM ... WHERE condition 中的 condition 的行 160

• No matching row in const table：对于关联查询，存在一个空表，或者没有行能够满足唯一索引条件

• No matching rows after partition pruning：对于 DELETE 或 UPDATE 语句，优化器在 partition pruning(分区修剪)之后，找不到要 delete 或 update 的内容

• No tables used：当此查询没有 FROM 子句或拥有 FROM DUAL 子句时出现。例如:explain select 1

• Not exists：MySQL 能对 LEFT JOIN 优化，在找到符合 LEFT JOIN 的行后，不会为上一行组合中检查此表中的更多行。例如:

• unique row not found：对于形如 SELECT ... FROM tbl_name 的查询，但没有行能够满足唯一索引或主键查询的条件。

好了看到这里你是否会觉得已经眼花缭乱了？现在开始重头戏，上面的可以作为知识扩展和了解，但下面的内容建议你一定要理解哦，会对性能优化有很大的帮助哦！

• Using filesort（重点）

• 出现的原因：当 SQL 查询中包含 ORDER BY 子句的操作后，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择 相应的排序算法来实现。

• 数据较少时从内存排序，当超过 Memory_Sort 的阈值的时候就会从磁盘排序，性能超级低哦！

• 并且，Explain 命令并不会显示的告诉 MySQL 数据库客户端用哪种排序。

1. 根据联接类型浏览所有行，并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。

2. 然后关键字被排序，并按排序顺序检索行。

• Using index（重点）

• (俗称：单覆盖索引哦！)，仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。

• 当查询仅使用属于单个索引的列时，可以使用此策略。例如:

• Using index condition（重点）

• (俗称：覆盖下推哦！) 表示先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。索引信息将可以延迟“下推”读取整个行的数据。

explain 在不同版本的变化

show warning 取值

SQL 性能分析

我们主要介绍一下三种：

• SHOW PROFILE

• INFORMATION_SCHEMA.PROFILING

• PERFORMANCE_SCHEMA

show profile

它是 MySQL 的一个性能分析命令，可以跟踪 SQL 各种资源消耗。使用格式如下:

SHOW PROFILE [type [, type] ... ] [FOR QUERY n][LIMIT row_count [OFFSET offset]]

默认情况下，show profile 只展示 Status 和 Duration 两列，如果想展示更多信息，可指定 type，使用步骤如下:

• 查看是否支持 show profile 功能，yes 标志支持。

select @@have_profiling;

• 查看当前是否启用了 SHOW PROFILE，0 表示未启用，1 表示已启用

select @@profiling;

• 设置为当前会话开启或关闭性能分析，设成 1 表示开启，0 表示关闭

set profiling=1

• 为最近发送的 SQL 语句做一个概要的性能分析。展示的条目数目由 profiling_history_size 会话变量控制，该变量的默认值为 15。最大值为 100。将值设置为 0 具有禁用分析的实际效果。

Show profiles 命令

首先使用show profiles分析指定查询:

使用 show profile 进行分析，默认情况下，只展示 Status 和 Duration 两列，如果想展示更多信息，可指定 type。

使用 SHOW PROFILE FOR QUERY 1;，1 代表的 query_id(show profiles)

展示 CPU 相关的开销

分析完成后，记得关闭掉 SHOW PROFILE 功能:

profiling

INFORMATION_SCHEMA.PROFILING 用来做性能分析，内容对应 SHOW PROFILE 和 SHOW PROFILES 语句产生的信息，SHOW PROFILE 本质上使用的也是 INFORMATION_SCHEMA.PROFILING 表。

profiling 表字段

profiling 查询

INFORMATION_SCHEMA.PROFILING 表已被废弃，在未来可能会被删除。未来将可使用 Performance Schema 替代，

performance_schema

performance_schema 是 MySQL 建议的性能分析方式，未来 show profile/show profiles、 information_schema.profiling 都会废弃。

performance_schema 在 MySQL5.6 及更高版本才能使用。可以使用 show variables 进行查看。

下面来用 performance_schema 去实现 show profile 类似的效果: 查看是否开启性能监控

查看启用情况，MySQL 5.7 开始默认启用。

你也可以执行类似如下的 SQL 语句，只监控指定用户执行的 SQL:

这样，就只会监控 localhost 机器上 test_user 用户发送过来的 SQL。其他主机、其他用户发过来的 SQL 统统不监控，执行如下 SQL 语句，开启相关监控项:

使用开启监控的用户，执行 SQL 语句，比如:

执行如下 SQL，获得语句的 EVENT_ID。

这一步类似于 show profiles。 执行如下 SQL 语句做性能分析，这样就可以知道这条语句各种阶段的信息了。

三种方式对比与选择

• SHOW PROFILE：简单、方便，已废弃

• INFORMATION_SCHEMA：PROFILING，它和 SHOW PROFILE 本质一样

• PERFORMANCE_SCHEMA：未来之光，但目前来说使用不够方便

MySQL 官方文档声明 SHOW PROFILE 已被废弃，并建议使用 Performance Schema 作为替代品。，目前可以继续用 SHOW PROFILE 了解 PERFORMANCE_SCHEMA，为未来做好准备

optimizer_trace 相关参数

缓存技术分析

MySQL 的 IO 持久化的将耗费大量资源。所以采用基于内存的 redis 会更好！

总结分析

具体的分析性能介绍后续会在【举世无双的「MySQL 调优金字塔」相信也许你拥有了它，你就很可能拥有了全世界。】进行深入介绍，此外还会伴有对索引原理的深入理解和分析。