独一无二的「MySQL调优金字塔」相信也许你拥有了它,你就很可能拥有了全世界。

开发俏皮话

让我 996 不算啥,我只怕测试也 996 给我提 bug!

笔者瞩望

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

技术金字塔

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

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

研发成本角度

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

实现效果角度

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

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

调优白皮书

SQL 调优

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

调优思路

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

发现慢 SQL 及日志

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

参数与默认值:

修改数据库服务配置
  1. 修改我们安装后的配置文件 my.cnf,在[mysqld]段落中加入以上参数配置:
  1. 之后进行重启服务
修改全局会话配置

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

将慢查询 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 调优金字塔」相信也许你拥有了它,你就很可能拥有了全世界。】进行深入介绍,此外还会伴有对索引原理的深入理解和分析。

  • 发表于:
  • 本文为 InfoQ 中文站特供稿件
  • 首发地址https://www.infoq.cn/article/5e1aec4933a497f773d5406e6
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券