MySQL 优化笔记：Explain、Profiles、PerformanceSchema、Trace 优化器追踪Fantasy！-

一、前言

• 概念：关系型数据库发展到今天，并且随着云计算的普及，对于基础运维的需求有了显著的减少，反而对于应用性能的优化变为重中之重。根据统计 80 % 的响应时间问题都是应用性能差的 SQL 语句造成的。今天让们一起来学习 MySQL 优化技术，收获的不止有优化。
• 优化公式：T = S / V 其中 T 表示查询所需要的时间 S 表示所需要的资源 V 表示单位时间内资源的使用量。那么我们可以通过减少 S 增加 V 两方面入手就可以达到优化目的。其中减少 S 是优化过程中的重头戏，也就是减少 IO 增加 S 就是提高资源利用率，充分利用软硬件资源。
• 目录介绍：今天我们从设计表原则开始，然后还会聊一些 MySQL 中的极限 和 innodb 启动过程相当于扩展知识，再着就是优化需要了解的基础知识，然后就是如何定位到问题，再者就是如何解决问题。

二、表设计规范

以下为建表规范：

• Innodb 引擎不使用外键约束，使用程序层面来维护数据的一致性。
• 存储精确浮点数必须使用 DECIMAL 来代替 FLOAT 和 DOUBLE 定点型以字符串格式存储不会出现精度丢失的问题。
• 整型定义中无需定义显示宽度，比如：使用 INT，而不是 INT(4) 前辈经验。
• 不建议使用 ENUM 类型，可以使用 TINYINT 代替使用。会插入无效值等一系列问题。
• 存储年时使用 YEAR(4)，不使用 YEAR(2)。YEAR(2) 已被删除，会自动转换为 YEAR(4)。
• 建议字段都定义为 NOT NULL，否则经常会出现奇怪的问题，比如 unqiue 失效(后面有案例)。
• 尽可能不要使用 TEXT、BLOB类型，如果必须使用，建议将过大的字段或不常用的描述型较大字段拆分到其它表中；另外禁止使用数据库存储图片。

以下为命名规范：

• 库、表、字段全部采用小写。

• 库名、表名、字段名、索引名均使用小写字母，并以 “_” 分割。

• 库名、表名、字段名建议不超过 12 个字符。

• 库名、表名、字段名需要见名知意，即可不使用注释。

• 对象命名规范总结：

  对象中文名	对象英文名	命名规范
  视图	view	view_
  函数	function	func_
  存储过程	procedure	proc_
  触发器	trigger	trig_
  普通索引	index	idx
  唯一索引	unique index	uniq_
  主键索引	primary index	pk_

以下是索引规范：

• 复合索引中的字段数建议不要超过 5 个。
• 单张表的索引数控制在 5 个以内。
• innodb 表建议有主键，主键列字段不要太大，会影响辅助索引占用的空间。
• 创建复合索引时，优先选择性能高的字段作为驱动表。
• UPDATE、DELETE 语句需要根据 WHERE 条件创建索引。
• 不建议使用 % 前缀的模糊查询，例如 LIKE %fantasy 会导全表扫描。
• 合理使用索引覆盖，可以避免回表查询。
• 避免在字段中使用函数，会导致查询时索引失效。

以下索引失效场景：

• 查询结果是原表中的大部分数据 15～30%以上，优化器就认为没有必要走索引了，与预读能力和一些参数有关。
• 对于表的内容频繁更新的情况下，统计信息不准确，可能会出现索引失效，一般是删除重建。
• 隐式转换导致索引失效，典型的例子是如果定义的数据类型为字符串类型，然后查询时使用数字传入到 mysql 这时 mysql 可能会进行隐式转换，索引就会失效。

三、MySQL 中极限及扩展

MySQL中的一些极限值：

• 一张表的字段最多为 1017 个，多出会报错。
• 辅助索引的个数：官方源码说明 辅助索引最多为 64 个。
• 复合索引的字段数：复合索引的字段数最多为 16 个。
• join 极限值：每个表 join 的最大个数是61个。

以下属于扩展知识 innodb 的启动过程(扩展知识可忽略)：

• innobase_init：MySQL在启动过程中会进行引擎初始化，innodb 引擎初始化的入口就是 innobase_init 函数。

1. Innobase_start_or_create_for_mysql：主要完成 Innodb 的启动过程，会初始化一些系统模块，如下：
   1. srv_general_init：初始化同步控制系统，内存管理系统，日志恢复变量等。
   2. srv_inif：初始化后台线程同步控制系统。
2. buf_pool_init：innodb buffer pool 初始化，会根据日志文件 innodb_buffer_pool_size 和 innodb_buffer_pool_instances 中的数据为依据。
3. log_init：初始化日志系统，初始化 innodb 所有的日志系统。
4. Io_handler_thread：创建 IO 异步线程，作用是等待 buffer pool 发出的读写指令，读写相关的数据页面。
5. recv_sys_init：初始化日志恢复系统，当数据库异常宕机时，会根据 redo、undo 日志进行解析内容和恢复。
6. open_or_create_data_files：创建或者打开系统数据文件 ibdata 如果文件存在则打开并读取相关信息，如果不存在则会创建一个新的 ibdata 文件，此时相当于初始化一个新的数据库实例。
7. srv_undo_tablespaces_init：初始化 undo 文件并加载到文件系统中，默认存储在 Ibdata 文件中，5.6 版本以后可以 innodb_undo_tablespaces 来设置 undo 与 Ibdata 文件分开存储。
8. New or Open Database:
   1. New Database：
      1. fsp_header_init：初始化文件，在系统文件 ibdata 中分配空间，便于存储一些系统模块、回滚段和数据字典等信息。
      2. trx_sys_create_sys_pages：上面函数已经开辟了存储空间，接下来直接进行事务系统存储初始化等操作。
      3. dict_create：记录存储数据字典 ROWID、表ID、索引ID等，并将系统表加载到内存中。
   2. Open Database：
      1. recv_recovery_from_checkpoint_start：扫描日志文件，分析日志的完整性按照页面号归类并作 REDO 操作。
      2. dict_boot：将所有的系统表加载到内存中。
      3. trx_sys_init_at_db_start：初始化事务系统，将所有回滚段中需要处理的事务加载到内存中，为“回滚”操作做准备。
      4. recovery_finish：将 trx_sys_init_at_db_start 加载需要回滚的事务进行回滚。
9. buf_dblwr_create：创建两次写缓存 double_write。
10. srv_master_thread：创建 master 线程，主要功能是每隔一秒进行一次后台循环，所做的事情主要是后台删除废弃表、检测日志空间是否足够、日志刷盘、做检测点。
11. srv_purge_coordinator_thread：与下一个函数 srv_worker_thread 配合实现 PURGE 操作。
12. srv_worker_thread：与上一个函数 srv_purge_coordinator_thread 配合实现 PURGE 操作
13. buf_flush_page_cleaner_thread：在后台每隔一秒钟，试图刷一次 Buffer 页面。

四、优化基础

以下是索引的基础知识：

• 聚簇索引：建表时要指定主键列，InnoDB 会将主键作为聚簇索引列，如果没有指定主键，引擎会选择唯一值的列作为聚簇索引。如果以上都没有，系统生成一个内部的 rowid 做为聚簇索引。有了聚簇索引后，插入的数据行，在同一个区内，都会按照 ID 值的顺序，有序的在磁盘中存储数据。
• 辅助索引：如果有经常需要查询的字段并且该字段为非聚簇索引，那么我们可以人为创建索引。使用辅助索引检索时，会通过叶节点找到聚簇索引的键，然后通过聚簇索引找到完整的数据记录，过程称为回表查询。如果辅助索引的树高度为 M，而聚簇索引的高度为 N，那么最终会进行 M+N 次 IO 才能定位到最终的数据。如果辅助索引可以完全覆盖查询那么就不会进行回表查询。

以下是两个需要注意的问题：

• 主键隐患：刚才我们在表设计规范中学习到，每张表都要创建主键，为什么呢？除了规范，从存储方式来讲，在 innodb 引擎中，表都是按照主键的顺序存放的，这就是聚簇索引也叫索引组织表 IOT 而且 innodb 引擎也对主键有自己的维护机制，刚才也谈到了二级索引(辅助索引) 从存储角度来讲，二级索引默认包含主键列，如果主键太长，会使得二级索引很占空间。

• 唯一性索引产生的重复数据：
  创建一张测试表，只有两个字段，此时没有任何约束，给 id 字段添加 unique 唯一索引，验证不可以存储重复 id 然后删除 unique 约束，再 id 和 name 创建复合唯一索引。

  
  目前两个字段已经有了复合唯一性索引，

  insert into test_unique values (1, null),(1, null),(1, null); 

  id	name
  1	NULL
  1	NULL
  1	NULL
  1	fantasy

  我们发现唯一约束失效了，如果多字段情况下很有可能会产生类似的重复数据，而这一切的罪魁祸首就是 null

关于 Null 的一些问题：

• Null 和空字符串是两个完全独立的对象，尽管表现方式都一样，都是没有数据。
• count(*) 和 count(id) 两个输出的结果不同，区别就是前者会统计 null 后者不会，但是两者的性能相同。

SQL 的解析过程：

• 第一步，对 SQL 文法检查，查看是否有文法错误，比如 from、select 拼写错误；
• 第二步，对象检测，在数据字典中校验 SQL 语句涉及的对象是否存在；
• 第三步，将对象名称进行转换，将同义词转换为对象；
• 第四步，检查语句的用户是否具有访问对象的权限；
• 第五步，生成执行计划。
  

SQL语句执行顺序：

• 关于执行顺序我们可以使用非常简单的方式来验证，就是写一个 SQL 语句可以在关键字部分设置错误语法根据报错信息来推理。感兴趣可以试一试，在这里不多描述。
• 经过测试 SQL语句执行顺序是这样的：
  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. ORDER BY
  6. SELECT
  7. LIMIT
• 我们可以看到解析过程和执行顺序差距还是蛮大的，归根结底，两者做的事情不一样，解析是在 SQL 文本的解析，而执行顺序则是在解析的基础上做数据的提取。

SQL 执行计划：

• 下面介绍的是 MySQL 中的 explain 可以输出 SQL 的执行计划。

• 为什么要使用 SQL 执行计划？
  答：分析优化器内置 cost 计算模型评估计算，最终选择后的执行 SQL 语句的顺序和状态。

• 查询SQL语句执行计划方式 explain + SQL 语句

  

• 我们可以看到输出的执行计划有很多字段，下面就几个关键字段来解读：

  字段	功能
  id	执行计划中查询的序列号
  select_type	语句所使用的查询类型
  table	查询时使用的表
  type	查询类型（全表/索引）
  possible_keys	预测会使用的索引
  key	实际使用的索引
  key_len	索引覆盖长度
  rows	本次查询扫描行数
  Extra	额外信息

• id 执行顺序解读
  

  id	select_type	table	partitions	type
  1	SIMPLE	t	NULL	ALL
  1	SIMPLE	tc	NULL	ALL
  1	SIMPLE	cu	NULL	ALL

  上面是一条多表连接查询语句，可以看到 id 都为 1 按照 MySQL 官网介绍 id 值相同时自上而下顺序执行。

  

  id	select_type	table	partitions	type
  1	PRIMARY	tc	NULL	ALL
  2	SUBQUERY	t	NULL	ALL
  3	SUBQUERY	c	NULL	ALL

  上图是一条包含子查询的 SQL 语句此时 id 都不相同，根据官网介绍 id 值不同时id 值越大越先执行。

  那么结论来了：

  1. id相同时，执行顺序由上至下；

  2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；

  3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行；

• select_type SQL 语句都查询类型，归结以下几种：

  1. SIMPLE：简单的SELECT，不使用UNION或子查询等；

  2. PRIMARY：子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY；

  3. UNION：UNION中的第二个或后面的SELECT语句；

  4. DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询；

  5. UNION RESULT：UNION的结果，union 语句中第二个select开始后面所有 select；

  6. SUBQUERY：子查询中的非最外层；

  7. DEPENDENT SUBQUERY：子查询中的第一个SELECT，依赖于外部查询；

  8. DERIVED：派生表的 SELECT, FROM 子句的子查询；

  9. UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行。

  10. MATERIALIZED ：物化子查询，在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ，后续对子查询结果集的访问将直接通过临时表获得。

• 接下来介绍的是 type 字段属于 SQL 执行的几种级别，分别为 const(system) > eq_ref > ref > range > index > all 可以看到性能属于 const 聚簇索引等值查询性能最好，最差的为 ALL 全表扫描，解下来我们一起了解在什么情况下可以达到什么级别

  index：全索引扫描 将所有索引扫描一遍

  -- tno 字段有索引 explain select tno from teacher; 

  id	select_type	table	partitions	type
  1	SIMPLE	teacher	NULL	index

  range：索引范围查询（>, <, >=,<=,like, in, or, between, and）

  -- tno 字段有索引 explain select * from teacher where tno >= 2; explain select * from teacher where tno  in (1,2); 

  id	select_type	table	partitions	type
  1	SIMPLE	teacher	NULL	range

  ref：辅助索引的等值查询

  explain select * from course where cname = "DBA"; 

  id	select_type	table	partitions	type
  1	SIMPLE	course	NULL	ref

  eq_ref：多连接中，非驱动表连接条件是主键或唯一键

  -- course 为驱动表，teacher 为非驱动表，此时 tno 为 Teacher 的 主键 explain select course.tno from course join teacher on course.tno=teacher.tno; 

  id	select_type	table	partitions	type
  1	SIMPLE	course	NULL	index
  1	SIMPLE	teacher	NULL	eq_ref

  const(system)：聚簇索引等值查询

  explain select * from course where cno = 1001; 

  id	select_type	table	partitions	type
  1	SIMPLE	course	NULL	const

  想必此时你已经对 SQL 执行计划有了了解，我们优化 SQL 语句的底线就是全表扫描了，太耗费资源。后面我们还会介绍一种比 explain 更详细的方法优化器追踪 trace。

• key_len 字段解读：可以使用它来判断是否全部使用联合索引如何计算呢？我回顾一下数据类型默认字符集为：UTF8

  数据类型	not null 约束	无 not null 约束
  Tinyint	1	1+1
  int	4	4+1
  bigint	8	8+1
  char(10)	3*10	30+1
  varchar(10)	3*10+2	30+2+1

  上表是一个示例，也就是 ken_len 的计算方法，我们使用的是 utf8 字符集允许 1 个字符占 3 个字节，那么我们使用 char(10) 它的计算方法就为 3 x 10 = 30 如果无 not null 约束的话，那 null 还需要一个字节的空间来存储就为 3 x 10 + 1 = 31 varchar(10) 的话也很好计算 3 x 10 + 2 为什么要加 2 ？因为 varchar 类型会用两个字节的空间来存储长度，现在想必你应该对 key_len 的计算已经了解，那么我们做个小案例吧。

  create table key_test( a int not null, b int , c char(10) not null , d varchar(10) ) charset=utf8mb4; -- 默认字符集为 utf8mb4 最多存储4个字节 

  -- 添加一个复合索引 alter table key_test add index idx_s7(a,b,c,d); 

  explain select * from key_test where a = 3 and b = 6 and c = 'EDG' and d = 'ch'; 

  id	select_type	table	partitions	type	possible_keys	key	key_len
  1	SIMPLE	key_test	NULL	ref	idx	idx	92

  可以看到 key_len 为 92 我们计算一下

  字段	数据类型	计算过程
  a	Int not noll	4
  b	Int	4+1
  c	char(10) not null	4*10
  d	varchar(10)	4*10+2+1

  结论：explain 执行结果一致都为92，则说明查询过程中使用了全部索引 如果现在依然感觉很模糊，那么接着看吧，在表设计规范里已经提到过隐式转换会导致索引失效，d 字段为字符串类型，我们使用数字来查会触发隐式转换，索引会失效，请看下面 SQL 语句。

  desc select * from key_test where a = 3 and b = 6 and c = 'EDG' and d = 777; 

  id	select_type	table	partitions	type	possible_keys	key	key_len
  1	SIMPLE	key_test	NULL	ref	idx_s7	idx_s7	49

  可以看到 key_len 现在为 49 按照 92 - 43 = 49 刚好是 d 字段索引失效的 key_len 值，我们就可以使用这种方法来判断复合索引是否都有效果，毕竟创建索引是有代价的，我们要保证每个索引都有效准确。

五、定位性能问题

• 介绍：我们在前面已经了解关于优化的基础内容索引、SQL 解析过程、SQL 执行顺序以及执行计划，相信你已经对索引有了一定程度的认知，那么我们现在一起来学习，如果定位到性能问题，如何去发现问题。

• MySQL Profile 定位到性能瓶颈：Profile 是用来分析执行计划的开销，可以查看内存、CPU 等使用情况。可以帮助我们来评估 SQL 语句的性能。下面是 MySQL 官网对 profile 的介绍，总结一下就可以定位到当前会话的资源消耗情况。

  The SHOW PROFILE and SHOW PROFILES statements display profiling information that indicates resource usage for statements executed during the course of the current session.

  下面我们来学习如何使用 profiling：

  -- 查询 profile 的状态是否开启 0 表示未开启 select @@profiling; 

  @@profiling
  0

  set profiling=1; 

  
  开启 profile 后 MySQL 提醒一个警告，查看后发现：

  @@profiling’ is deprecated and will be removed in a future release.
  @@profiling’已被弃用，并将在未来的版本中被删除。

  现在我使用的环境是 5.7.28 相信 8.0 应该已经被删除，我们可以了解一下(用起来也挺香的)，还有第二种方法那就是 performance_schema。我们先学习 profile 如何使用，再循序渐进学习 performance_schema 相关知识。

  select @@profiling; 

  @@profiling
  1

  现在已经开启 profiling 现在我们运行 SQL 语句

  select count(*) from information_schema.COLUMNS; 

  count(*)
  3817

  然后运行 show profiles; 即可得到刚刚运行的 SQL 语句

  show profiles; 

  Query_ID	Duration	Query
  71	0.000104	SHOW WARNINGS
  72	0.000145	/* ApplicationName=DataGrip 2019.3.4 */ select count(*) from information_schema.COLUMNS

  
  开启 profiling 后会记录运行的 SQL 语句，使用 show profiles; 可以查询到记录的 SQL 语句，然后使用 show profile cpu for query Query_ID 即可查询到 SQL 语句资源使用情况，其中 cpu 是可以换做其它指标的比如 ALL 将会输出 SQL 语句使用的各种资源情况不过太多，眼睛看不过来，所以我们一般都会使用查看某个指标而不是全部。

  选项	解释
  ALL	显示所有开销信息
  BLOCK IO	查看块操作数的相关开销信息
  CONTEXT SWITCHS	上下文切换相关的开销信息
  CPU	显示CPU相关开销信息
  IPC	显示发送和接收相关开销信息
  MEMORY	显示内存相关的开销信息
  PAGE FAULTS	显示页面错误相关的开销信息
  SOURCE	显示 Source_function、Source_file、Source_line 相关开销信息
  SWAPS	显示交换次数相关的开销信息

• performance_schema 定位性能瓶颈：属于视图表，而且统计数据过程中需要占用系统资源，所以不建议在线上业务上使用它做监控之类的应用或者就不要在业务线上使用，会降低系统性能，经过测试大约会降低 10% 的系统性能，所以说在业务线上谨慎使用。以下是开启配置方法及 MySQL 官网相关描述。下面请跟我一起完成配置吧！

  update performance_schema.setup_instruments set ENABLED = 'YES',  TIMED   = 'YES' where NAME LIKE '%statement/%'; update performance_schema.setup_instruments set ENABLED = 'YES',     TIMED   = 'YES' where NAME LIKE '%stage/%'; update performance_schema.setup_consumers set ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%'; update performance_schema.setup_consumers set ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%'; 

  采集功能已经开启，所有 SQL 语句查都会记录可以试着运行几条DQL语句

  SELECT EVENT_ID, TRUNCATE(TIMER_WAIT / 1000000000000, 6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE '%where%'; 

  EVENT_ID	Duration	SQL_TEXT
  2090	0.000264	/* ApplicationName=DataGrip 2019.3.4 */ update performance_schema.setup_consumers set ENABLED = ‘YES’ WHERE NAME LIKE ‘%events_statements_%’
  2209	0.000250	/* ApplicationName=DataGrip 2019.3.4 */ update performance_schema.setup_consumers set ENABLED = ‘YES’ WHERE NAME LIKE ‘%events_stages_%’
  2347	0.001330	/* ApplicationName=DataGrip 2019.3.4 */ select * from performance_schema.setup_instruments where NAME LIKE ‘%stage/%’
  2472	0.000315	/* ApplicationName=DataGrip 2019.3.4 */ select * from school where id = 2
  2722	0.000796	/* ApplicationName=DataGrip 2019.3.4 */ select END_EVENT_ID, SQL_TEXT<br/>from performance_schema.events_statements_history_long<br/>where SQL_TEXT like '%select%'
  2847	0.000767	/* ApplicationName=DataGrip 2019.3.4 */ select END_EVENT_ID,

  可以根据 WHERE 中的条件快速查找到需要优化的 SQL 语句然后记住 EVENT_ID 比如我们现在需要查看 EVENT_ID 为 2722 的 SQL 语句的消耗资源情况。

  SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT / 1000000000000, 6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID = 2722; 

  Stage	Duration
  stage/sql/starting	0.000106
  stage/sql/checking permissions	0.000007
  stage/sql/Opening tables	0.000023
  stage/sql/init	0.000030
  stage/sql/System lock	0.000005
  stage/sql/optimizing	0.000006
  stage/sql/statistics	0.000013
  stage/sql/preparing	0.000009
  stage/sql/executing	0.000000
  stage/sql/Sending data	0.000493
  stage/sql/end	0.000001
  stage/sql/query end	0.000004
  stage/sql/closing tables	0.000005
  stage/sql/freeing items	0.000086
  stage/sql/cleaning up	0.000000

  到此为止，相当于 performance_schema 性能监测环境是配置完成，日常使用中我们主要查看的是 stage/sql/Sending data 关于 I/O 相关阶段，SQL 语句慢此选项都会比较大，其它字段见名知意。另外，performance_schema 的数据不会持久化存储在磁盘中，而是存储在内存中 MySQL 重启后也会自动刷新，我们配置的性能监测环境也会失效。可以利用这点来关闭监测环境。

• 到此为止我们已经介绍两种定位到性能的方法 profiles 和 performance_schema 在使用过程中两者可互补使用。这也是性能检测和 explain 的区别，我们可以通过性能检测得到每个阶段所有的时间和消耗的资源，而 explain 只是优化器方面信息，得到的是 SQL 执行计划。现在我们再介绍一个与之相似的但结果更加准确丰富优化器检测手段：优化器追踪 trace。

  trace 是 5.6 版本新增的功能，可以帮助我们理解优化器选择 A 执行计划而不是选择 B 执行计划，trace 会给我们一个比 explain 更加详细的信息。下面是配置方法：

  

  -- 查看优化器状态 show variables like 'optimizer_trace'; -- 开启会话级别的 trace 仅在本会话有效 set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 设置优化器追踪的内存大小 set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; 

  以上就是优化器 trace 会话级别的开启方法，关闭会话后使用的资源会释放。接下来执行需要追踪优化器的 SQL 语句系统就会追踪到优化器的数据。再通过查询元数据表即可得到答案。因为开启的是会话级别所以关闭 DBMS 的连接即可关闭，也可以手动关闭。

  -- 关闭本会话的 trace set session optimizer_trace="enabled=off" 

  -- 查询 information_schema.optimizer_trace 得到结果 SELECT trace FROM information_schema.OPTIMIZER_TRACE; 

  
  上面就是优化器追踪的结果，通常我们是直接导出文件然后再分析，运行如下 SQL 语句。

  -- 将查询结果导入到 /data/trace.trace SELECT TRACE INTO DUMPFILE "/data/trace.json" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 

  通常会报错：大概意思是导出文件必须要按照 secure_file_priv 参数指定的路径
  ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

  -- 查询 secure_file_priv 参数指定的目录 show global variables like '%secure_file_priv%'; 

  Variable_name	Value
  secure_file_priv	/var/lib/mysql-files/

  -- 修改 SQL 语句中的路径即可导出文件 SELECT TRACE INTO DUMPFILE "/var/lib/mysql-files/trace.json" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 

  经过以上过程相信大家已经掌握定位优化追踪 teace 会话级配置、执行 SQL、导出文件、分析文件中的数据即可。下图是得到 trace 的 Json 数据信息可分为三部分。

  

  • 准备阶段：对应文本中的 join_preparation
  • 优化阶段：对应文本中的 join_optimization
  • 执行阶段：对应文本中的 join_execution

  ---

  注：其中我们重点关注的是 rows_estimation 和 considered_execution_plans 是关于优化器计算代价选择的执行分案。由于篇幅原因我后面还会再写一篇博客将 trace 单独拎出来介绍，直接使用 Python 分析结果省的我们花时间分析，目前在筹备中....

  总结：至此我们已经学习四种定位 SQL 问题的方法 explain 和 trace(优化器追踪) 主要分析 SQL 执行过程，profiles 和 performance_schema 主要是关于 SQL 语句的性能考究。没有绝对的解决方案，适合的场景下使用适合的方法。

• 补充：通过 performance_schema 相信大家对 MySQL 元数据有一定的了解，performance_schema 主要包含数据库关于性能的数据，information_schema 包含数据库中的维护信息，两者都属于虚拟表没有存储数据，只是存储生成数据的方法(概念有点像 Python 中的生成器) 但是从 performance_schema 中获取想要的数据比较复杂，到 5.7 版本已经有 80 多张表，每张表都是对统计信息的罗列，而且这些表和 information_schema 也有关联使用起来非常不方便，所以在 5.7 版本出现了 Sys Schema 将 information_schema 和 performance_schema 中的数据以更加容易的方式归纳提炼出来。下面介绍几个常用的应用场景。

  ---

  查看数据库中每张表的访问量，通常数据库由少数人来维护，突然某个实例的 QPS 上升，我们可以通过 schema_table_statistics 快速定位到访问量的增长情况。

  select table_schema, table_name, (io_read_requests + io_write_requests) as io_num from sys.schema_table_statistics; 

  table_schema	table_name	io_num
  new_data	new	704
  new_data	new_correlation	427
  new_data	tb_users	12
  new_data	django_migrations	11
  booktest	auth_group	10
  booktest	auth_group_permissions	10

  冗余的索引和未使用的索引检查，创建索引的代价蛮高的，我们要帮助每个索引都准确有效，通过 schema_redundant_indexes 快速帮助我们定位到索引的使用情况。

  select * from sys.schema_redundant_indexes; 

  表自增 ID 监控，随着数据增长，可能会出现某长表的自增快要超过阈值了，继而导致业务问题。通过 schema_auto_increment_columns 精确查询到每张表的自增 ID 信息。

  select * from sys.schema_auto_increment_columns; 

  table_schema	table_name	column_name	data_type	column_type	is_signed	is_unsigned	max_value	auto_increment	auto_increment_ratio
  new_data	new_correlation	id	int	int(11)	1	0	2147483647	1119611	0.0005
  meiduo_mall_prepare	tb_areas	id	int	int(11)	1	0	2147483647	820001	0.0004
  BookTest	auth_group	id	int	int(11)	1	0	2147483647	1	0.0000
  BookTest	auth_group_permissions	id	int	int(11)	1	0	2147483647	1	0.0000
  BookTest	auth_permission	id	int	int(11)	1	0	2147483647	25	0.0000
  BookTest	auth_user	id	int	int(11)	1	0	2147483647	1	0.0000
  BookTest	auth_user_groups	id	int	int(11)	1	0	2147483647	1	0.0000
  BookTest	auth_user_user_permissions	id	int	int(11)	1	0	2147483647	1	0.0000

  查询数据库中走全表扫描的 SQL 语句，有些语句因为某种问题走全表扫描，这些 SQL 会导致数据库的性能极具下降，并发量大的情况下甚至可以使服务器响应变慢，直到夯住。使用 statements_with_full_table_scans 帮助我们找出走全表扫描的 SQL 语句。

  select * from sys.statements_with_full_table_scans where db = 'new_data'; 

  query	db	exec_count	total_latency	no_index_used_count	no_good_index_used_count	no_index_used_pct	rows_sent	rows_examined	rows_sent_avg	rows_examined_avg	first_seen	last_seen	digest
  SELECT `new_correlation` . `id … w_correlation` . `new_id` = ?	new_data	2	78.59 ms	2	0	100	0	316920	0	158460	2020-06-17 08:39:19	2020-06-17 08:44:31	dca696ca75eb6e6cfaa9120f4ee82a96
  SELECT `django_migrations` . ` … ame` FROM `django_migrations`	new_data	1	283.00 us	1	0	100	33	33	33	33	2020-06-17 08:38:47	2020-06-17 08:38:47	3ce5fc5bdb2cd93d45573041efa28fdb
  SELECT `new` . `id` , `new` . … ` . `new_seenum` DESC LIMIT ?	new_data	3	106.59 ms	3	0	100	24	9081	8	3027	2020-06-17 08:39:01	2020-06-17 08:44:31	6986c7aa0ec5d45ab155e3ede0b0ee9a

  查看实例消耗的磁盘 I/O 某天数据库响应变慢了，这时我们需要关心数据库到底慢在哪里？通过 io_global_by_file_by_bytes 可以快速定位到具体文件消耗的 I/O 从而帮助我们排查问题。

  select file, avg_write + avg_read as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10; 

  file	avg_io
  @@basedir/data/ib_logfile0	1331
  @@basedir/data/sys/schema_tables_with_full_table_scans.frm	1023
  @@basedir/data/sys/schema_unused_indexes.frm	1006
  @@basedir/data/sys/x@0024schema_tables_with_full_table_scans.frm	994
  @@basedir/data/help/helplist.frm	955
  @@basedir/data/mysql/tables_priv.MYD	947
  @@basedir/data/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm	906
  @@basedir/data/performance_schema/table_lock_waits_summary_by_table.frm	897
  @@basedir/data/mysql/proxies_priv.MYD	837
  @@basedir/data/mysql/user.MYD	748

  注意：不要在线上业务大量使用元数据表，来做监控或者巡检，因为查询信息时 MySQL 会消耗大量资源去收集相关信息，有让业务请求堵塞的风险。在使用前务必了解清楚。 MySQL 官网 Sys-Schema

六、SQL 查询优化

1. 感言：首先 MySQL 优化是门比较复杂的技术，为什么这样讲呢？涉及到的东西特别多，想想一名 DBA 要看多少书。本篇博客只是为大家引出 MySQL 优化，当你需要做优化的时候不至于没有一点头绪，可以顺者我的博客找找思路。后续我还会继续更新博文，找点比较酷的事情做。感谢关注，祝进步！

2. 派生表（Derived Table）：当 from 后面的对象是子查询返回的结果时，此时就会出现派生表。请看示例。

   
   可以看到 from 后面直接跟了一个子查询，此时子查询就是一个派生表。

3. 深入派生表：使用 explain 查看派生表执行计划。

   explain select * from (select * from new where id < 500) news where new_cate_id = 2; 

   ---

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	new	NULL	index_merge	PRIMARY,new_new_cate_id_3e5fac50_fk_cate_id	new_new_cate_id_3e5fac50_fk_cate_id,PRIMARY	8,4	NULL	21	100	Using intersect(new_new_cate_id_3e5fac50_fk_cate_id,PRIMARY); Using where

   ---

   发现 select_type 依然是 SIMPLE 没有出现派生表，原因是 5.7 版本后 MySQL 会对派生表进行优化，那么现学现用使用 trace 查看优化器的解析过程。
   
   看来是优化器对 SQL 语句进行优化，没有走派生表，可以关闭优化器相关派生表的功能。

   -- 查询优化器相关功能开启状态 select @@optimizer_switch; 

   @@optimizer_switch

   index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,deriv ed_merge=on

   可以看到 derived_merge 开启状态，我们关闭即可。

   set optimizer_switch = 'derived_merge=off'; 

   再次 explain 刚刚的 SQL 查询语句 select_type = DERIVED 此时 SQL 触发派生表 结果如下：

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	PRIMARY	<derived2>	NULL	ref	<auto_key0>	<auto_key0>	4	const	10	100	NULL
   2	DERIVED	new	NULL	range	PRIMARY	PRIMARY	4	NULL	950	100	Using where

   为什么要学习派生表呢？因为它可能会带来性能隐患。如果服务剩余资源比较紧的情况下还可能会导致查询失败。我们可以从 explain 中得到派生表的执行过程：首先执行 from 后的子查询语句，然后将查询结果写入临时表，再回读，按照条件查询。 可以了解到派生表会生成临时文件不会应用外部的过滤条件，生成的文件可能会很大，总结一句：派生表有潜在的性能隐患，尽量不要使用。

   -- 刚才将优化器关闭的同学可以打开了！！！ set optimizer_switch = 'derived_merge=on'; 

4. MySQL 中的半连接(semi join)：半连接听起来好像很高大上，其实就是我们比较普遍的查询方式。

   -- 类似这种子查询语句 将 test1 和 test2 连接起来 select * from test1 where id in (select * from test2 where id < 100); 

   接下来我们来使用一个比较常用的例子来介绍半连接，首先需要搭建环境：

   -- 创建测试表 create table users( user_id int(11) unsigned not null primary key , user_name varchar(64) default null ) engine=innodb default charset=UTF8; 

   -- 创建存储过程 delimiter $$ drop procedure if exists insert_df$$ create procedure insert_df() begin declare         init_data integer default 1; while init_data < 20000 do insert into users values (init_data, concat('user', init_data)); set init_data = init_data + 1; end while; end $$; 

   -- 运行存储过程 call insert_df(); 

   select count(*) from users; 

   count(*)
   19999

   环境搭建完成，接下来我们执行一条半连接 SQL 语句，我们发现竟然足足使用 3.5 秒

   -- 代号：Q1 select count(u.user_id) from users u where u.user_name in (select u2.user_name from users u2 where u2.user_id < 2000); 

   
   做同样的事情我们稍微修改一下 SQL 语句，仅仅用时 0.03 秒，保守估计性能提升 50 倍。

   -- 代号-Q2 select * from users u where (u.user_name in (select t.user_name from users t where t.user_id < 2000) or (u.user_name in (select t.user_name from users t where t.user_id < -1))); 

   
   从结果中我们可以看到只是添加 or 条件，性能就提升 50 多倍为什么呢？接下来先介绍 MySQL 执行监控计数器，来对比两条 SQL 语句的执效率对比情况。

   -- 重置计数器 保证计数器每次从新的开始 flush status 

   重置计数器后直接运行需要查询的 SQL 语句，然后使用 Handler_read 可以得到结果。

   -- 查看计数器 show status like 'Handler_read%'; 

   Variable_name	Value
   Handler_read_first	2
   Handler_read_key	2
   Handler_read_last	0
   Handler_read_next	1999
   Handler_read_prev	0
   Handler_read_rnd	0
   Handler_read_rnd_next	22000

   ---

   介绍 Handler_read 中的重要参数含义：

   1. Handler_read_key：通过 index 获取数据的次数。如果较高，说明查询和表索引使用正确。
   2. Handler_read_next：通过索引读取下一条数据的次数。如果用范围约束或者执行索引扫描来查询索引列，该值会增加。
   3. Handler_read_rnd_next：从数据节点读取下一条数据的次数。如果正在进行大量的表扫描，该值会比较高。通常说明表索引使用不正确或写入的查询没有利用索引。
   4. Handler_read_first：索引中第一个条目被读取的次数。如果这个值很高，说明服务器正在进行大量的全索引扫描。

   ---

   了解完 Handler_read 计数器后我们来对比刚才两条 SQL 语句的性能

   Variable_name	Value1-Q1	Value2-Q2
   Handler_read_first	2	2
   Handler_read_key	2	20001
   Handler_read_last	0	0
   Handler_read_next	1999	1999
   Handler_read_prev	0	0
   Handler_read_rnd	0	0
   Handler_read_rnd_next	22000	20000

   value1 是第一条 SQL 语句（3.5s）的计数器结果 Q1，value2 是第二条 SQL 语句 （0.03s）的计数结果 Q2，忘记的可以翻到前面熟悉一下。

   结论：现在我们发现 Q1 的 Handler_read_key 远远小于 Q2 根据上面的参数说明，了解到 Q2 查询时索引使用正确。下面我们简单分析一下，为什么 Q2 会有更多的索引读？

   -- 查看 Q1 执行计划 explain select count(u.user_id) from users u where u.user_name in (select u2.user_name from users u2 where u2.user_id < 2000); 

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	<subquery2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	100	NULL
   1	SIMPLE	u	NULL	ALL	NULL	NULL	NULL	NULL	19762	10	Using where; Using join buffer (Block Nested Loop)
   2	MATERIALIZED	u2	NULL	range	PRIMARY	PRIMARY	4	NULL	1999	100	Using where

   -- 查看 Q2 执行计划 explain select count(u.user_id) from users u where (u.user_name in (select t.user_name from users t where t.user_id < 2000) or (u.user_name in (select t.user_name from users t where t.user_id < -1))); 

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	PRIMARY	u	NULL	ALL	NULL	NULL	NULL	NULL	19762	100	Using where
   3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
   2	SUBQUERY	t	NULL	range	PRIMARY	PRIMARY	4	NULL	1999	100	Using where

   发现 Q1 查询第一步进行物化(MATERIALIZED) 使用临时表后面的查询全都走全表扫描是物化导致索引失效吗？那么使用我们前面学习到的 trace 优化器追踪来对 Q1 一探究竟。

   
   直接上结果，感兴趣的朋友可以自己试试，前面已经演示过 trace 从结果来看优化器在物化后又进行了 semi join 半连接优化，那么问题原因就是 Q1 物化使用临时表(临时表会自己创建索引)，又进行半连接优化，后面的查询就走了全表扫描。那么如果关闭半连接优化效果会不会好些呢？

   -- 关闭半连接优化 set optimizer_switch='semijoin=off'; 

   
   关闭半连接优化器后，原本需要 3.5s 的查询现在只需要 0.02s 执行时间大大降低，那么初步印象已经形成：半连接存在性能隐患，可以选择关闭优化器的半连接优化。

   -- 关闭半连接优化 set optimizer_switch='semijoin=off'; 

   ---

5. 反连接 (antijoin)：相信了解半连接，反连接也很好理解就是 not in 或者 not exists 子句形式，就用到了反连接。

   ---

6. 行值表达式 (Row Value Expressions)：听起来好像有点抽象，行值表达式也叫行值构造器，通常我们所操作的SQL表达式都只能针对一行中的单一字段进行操作比较，而行值表达式可以针对一行中的多个字段进行操作比较。

   说了比较抽象那么看一个例子吧！比如有三名学生分别为：张三(大数据) 王五(软件工程) 李四(信息对抗) 因为不能保证其它专业没有相同的名字所以我们要查询三位同学的 SQL 应该如下：where course in (‘大数据’, ‘软件工程’, ‘信息对抗’) and username in (‘张三‘, ‘李四’, ‘王五’) 此时如果使用行值表达式 此时如果使用行值表达式 where (course, username) in ((‘大数据’, ‘张三’), (‘软件工程’, ‘李四’), (‘信息对抗’, ‘王五’)) 也就是说此时查询条件是多维的 可以使用行值表达式。

   因为行列表达式在 MySQL 不同版本中有比较大的差异，我们用刚才半连接创建的 users 表再次做一个小测试：

   -- 5.7 版本 explain select * from users where (user_id, user_name) in ((1, 'user1'), (2, 'user2'), (3, 'user4')); 

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	users	NULL	range	PRIMARY,idx_users	idx_users	199	NULL	3	100	Using where; Using index

   -- charset = utf8 desc users; 

   Field	Type	Null	Key	Default	Extra
   user_id	int(11) unsigned	NO	PRI	NULL
   user_name	varchar(64)	YES		NULL

   还记得 key_len 的计算方法吗？ user_id 类型为 int 占 4 个字节，varchar(64) 64 * 3 = 192 再加上 varchar 类型需要额外两个字节空间来存储长度，并且可以为 null 还需要一个字节空间那么最后 user_name 的 key_len 为 64*3+2+1 = 195 结果说明在 5.7 版本使用行值表达式索引能够被充分利用。

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	users	NULL	range	PRIMARY,idx_users	idx_users	195	NULL	3	100	Using where; Using index

   表格为 5.6 版本相同环境下运行的结果 key_len 为 195 则说明 user_id 索引失效，5.7 版本优化器做了改动。为什么优化需要了解这些呢？其实呢，任何数据库的优化器都不是万能的。 了解优化器的特性后并规避其短处，才能写出最优SQL语句。