一、MySQL性能分析及调优简介

1.1 数据库服务调优的目标

尽可能节省系统资源，以便系统可以提供更大负荷的服务（吞吐量更大）； 合理的结构设计和参数调整，以提高用户操 响应的速度（响应速度更快）； 减少系统的瓶颈，提高MySQL数据库整体的性能；

1.2 数据库服务器的优化维度

如何定位调优问题：

• 用户的反馈（主要）；
• 日志分析（主要）；
• 服务器资源使用监控；
• 数据库内部运行状况监控；
• 其它；

调优的维度：

• 选择适合的 DBMS（数据库管理系统 - Database Management System）；
• 优化表设计 —— 表结构设计、存储引擎选取等；
• 优化逻辑查询；
• 优化物理查询；

物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过计算代价模型对各种可能的访问路径进行估算，从而找到执行方式中代价最小的作为执行计划（重点掌握索引的创建和使用）。

1.3 数据库服务器的优化步骤

数据库服务器的优化步骤：可分成 观察（Show status） 和 行动（Action） 两部分，下图中字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）：

通过观察了解数据库整体的运行状态，通过性能分析工具可以了解执行慢的SQL都有哪些，查看具体的SQL执行计划，甚至是SQL执行中的每一步的成本代价，这样才能定位问题所在，找到问题，再采取相应的行动。

调优流程图详解：

1. 首先在 S1 部分，需要观察服务器的状态是否存在周期性的波动。

   • 存在周期性波动，有可能是周期性节点的原因，如双十一，促销活动等。这样的话，可以通过 A1 加缓存这一步骤解决，或者更改缓存失效策略。

2. 如果缓存策略没有解决，或者不是周期性波动的原因，就需要进一步分析查询延迟和卡顿的原因。

   • 进入开启慢查询 S2 步骤，通过慢查询可用快速定位执行慢的SQL语句。通过设置 long_query_time 参数定义 “慢” 的阈值（阈值即边界值），如果SQL执行时间超过了阈值，则认为是慢查询。
   • 收集到这些慢查询的SQL语句之后，就可以通过分析工具对慢查询日志进行分析。

3. 在S3这一步骤中，通过对慢查询日志的分析，了解到执行慢的SQL。

   • 这样就可以针对性地用 EXPLAIN 查看对应SQL语句的执行计划，或者使用 show profile 查看SQL中每一个步骤的时间成本。这样就可以了解SQL查询慢是因为 执行时间长，还是 等待时间长。

4. 如果是SQL等待时间长，就进入 A2 步骤。进行调优服务器的参数，比如适当增加数据库缓冲池等。如果是SQL执行时间长，就进入 A3 步骤，从以下三个方面进行优化调整：

   • 索引设计优化，比如联合索引就比多个单个索引的查询效率要快一些。
   • Join表是否过多，Join表的数据最好不要超过三张，表的数据越多，嵌套循环就越多，查询时间也就越长。
   • 数据表设计优化，虽然一般设计数据表都遵循三范式，但是我们可以适当的增加数据冗余度，以空间换取时间提高数据的查询效率。

5. 如果 A2 和 A3 都不能解决问题，就要考虑数据库自身的SQL查询性能是否已经达到了瓶颈：

   • 如果确认没有达到性能瓶颈，就需要重新检查也就是重复上述步骤。
   • 如果已经达到了性能瓶颈，进入 A4 阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库，垂直分表和水平分表等。

以上就是数据库调优的流程思路。如果我们发现执行sQL时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的sQL，这三种分析工具你可以理解是sQL调优的三个步骤:慢查询、EXPLAIN和SHoWPROFILING。

二、MySQL 系统参数

2.1 MySQL 系统参数

系统参数是MySQL配置文件中的一组变量，用于控制数据库系统的行为。这些参数可以影响到数据库的各个方面，包括内存使用、查询优化、并发控制等。

在使用MySQL时，了解和掌握系统参数是非常重要的，因为它们可以影响到数据库的性能和稳定性。通过查看和调整系统参数，可以优化数据库的性能和稳定性。

MySQL提供了 SHOW VARIABLES; 语句，用来查看当前数据库实例的系统参数，其使用方法如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 查看当前数据库实例的所有系统参数
SHOW GLOBAL VARIABLES;		-- 全局（global） 参数
SHOW SESSION VARIABLES;		-- 所有参数，包含全局参数 和 session 参数, SESSION 可以省略

-- 查看某个指定的变量
SHOW GLOBAL VARIABLES LIKE 'key_buffer_size'; 
SHOW VARIABLES LIKE 'key_buffer_size'; 
select @@session.sort_buffer_size as value;   -- ‘sesson.’ 可以省略
select @@global.sort_buffer_size  as value;

-- 连接数据库 
use performance_schema;
-- 单表
select * from variables_info order by VARIABLE_SOURCE desc;  -- 所有变量
select * from session_variables;
select * from global_variables;
select * from persisted_variables;

-- 4张表连表
select
	sv.*, gv.VARIABLE_VALUE, pv.VARIABLE_VALUE, vi.*
from
  variables_info vi 
  left join session_variables   sv on vi.variable_name = sv.variable_name
  left join global_variables    gv on sv.variable_name = gv.variable_name
  left join persisted_variables pv on gv.variable_name = pv.variable_name
order by vi.variable_source desc, pv.variable_name desc, gv.variable_name desc, sv.variable_name desc;


-- 查看某类系统参数
SHOW GLOBAL VARIABLES LIKE '%slow%';  
SHOW VARIABLES LIKE '%slow%'; 

MySQL提供了 SET GLOBAL / SET SESSION 语句，用来动态修改/设置系统参数变量值 ，其使用方法如下：

1
2
3
4
5
6
7
8
9

-- 方式 一
set session sort_buffer_size = 1024 * 1024;    -- 当前连接session有效（重启后失效）。‘sesson’ 可以省略
set global  sort_buffer_size = 1024 * 1024;    -- 所有连接session有效（重启后失效）
set persist sort_buffer_size = 1024 * 1024;    -- 持久化（重启后有效）

-- 方式 二
set @@session.sort_buffer_size = 1024 * 1024;  -- 当前连接session有效。‘sesson’ 可以省略
set @@global.sort_buffer_size  = 1024 * 1024;  -- 所有连接session有效（重启后失效）
set @@persist.sort_buffer_size = 1024 * 1024;  --             持久化（重启后有效）

部分常用系统参数：

• innodb_buffer_pool_size：这个参数是Mysql数据库最重要的参数之一，表示InnoDB类型的表和索引的最大缓存。它不仅仅缓存索引数据，还会缓存表的数据。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。
• key_buffer_size：表示索引缓冲区的大小。索引缓冲区是所有的线程共享。增加索引缓冲区可以得到更好处理的索引（对所有读和多重写）。当然，这个值不是越大越好，它的大小取决于内存的大小。如果这个值太大，就会导致操作系统频繁换页，也会降低系统性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。
• table_open_cache：表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。物理内存越大，设置就越大。默认为2402，调到512-1024最佳。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。
• query_cache_size：表示查询缓冲区的大小。可以通过在MySQL控制台观察：
  • 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，就要增加Query_cache_size的值；
  • 如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓存；
  • Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。MySQL8.0之后失效。该参数需要和query_cache_type配合使用。
• query_cache_type 的值是0时，所有的查询都不使用查询缓存区，但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓存区内存。
  • 当query_cache_type=1时，所有的查询都将使用查询缓存区，除非在查询语句中指定SQL_NO_CACHE，如SELECT SQL_NO_CACHE * FROM tbl_name。
  • 当query_cache_type=2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓存区。使用查询缓存区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。
• sort_buffer_size：表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默认数值是2 097 144字节（约2MB）。对于内存在4GB左右的服务器推荐设置为6-8M，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。
• join_buffer_size = 8M：表示联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
• read_buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size=n可以临时设置该参数的值。默认为64K，可以设置为4M。
• innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。该参数的默认值为1。
  • 值为0时，表示每秒1次的频率将数据写入日志文件并将日志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
  • 值为1时，表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘进行同步。该模式是最安全的，但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入（flush）硬盘。
  • 值为2时，表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
• innodb_log_buffer_size：这是 InnoDB 存储引擎的事务日志所使用的缓冲区。为了提高性能，也是先将信息写入 Innodb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。
• max_connections：表示 允许连接到MySQL数据库的最大数量 ，默认值是 151 。如果状态变量connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。这个连接数 不是越大 越好 ，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。
• back_log：用于控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 + （max_connections / 5）， 对于Linux系统推荐设置为小于512的整数，但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。
• thread_cache_size：线程池缓存线程数量的大小，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程 。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。那么为了提高性能可以增大该参数的值。默认为60，可以设置为120。
• wait_timeout：指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
• interactive_timeout：表示服务器在关闭连接前等待行动的秒数。

2.2 MySQL系统统计参数

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

SHOW STATUS 语句语法如下：

1
2
3

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';
-- GLOBAL 表示全局
-- SESSION 表示当前会话

一些常用的统计参数如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- 连接MySQL服务器的次数
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW SESSION STATUS LIKE 'Connections';

-- MySQL服务器的上线(运行)时间；
SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW SESSION STATUS LIKE 'Uptime';

-- 慢查询的次
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW SESSION STATUS LIKE 'Slow_queries';

-- select查询返回的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';
SHOW SESSION STATUS LIKE 'Innodb_rows_read';

-- 执行INSERT操作插入的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted';
SHOW SESSION STATUS LIKE 'Innodb_rows_inserted';

-- 执行UPDATE操作更新的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_updated';
SHOW SESSION STATUS LIKE 'Innodb_rows_updated';

-- 执行DELETE操作删除的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_deleted';
SHOW SESSION STATUS LIKE 'Innodb_rows_deleted';

-- 查询操作的次数；
SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW SESSION STATUS LIKE 'Com_select';

-- 插入操作的次数；对于批量插入的INSERT操作，只累加一次；
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW SESSION STATUS LIKE 'Com_insert';

-- 更新操作的次数
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW SESSION STATUS LIKE 'Com_update';

-- 删除操作的次数
SHOW GLOBAL STATUS LIKE 'Com_delete';
SHOW SESSION STATUS LIKE 'Com_delete';

2.3 统计SQL的查询成本：last_query_cost

1

SHOW STATUS LIKE 'last_query_cost';

使用场景：该语句对于比较SQL查询开销是非常有用的，特别是有好几种查询方式可选的时候。

SQL 查询是一个动态的过程，从页加载的角度来看，可以得到以下两点结论：

1. 位置决定效率，如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多；

• 批量决定效率，如果从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取；

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的，首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

三、MySQL 执行语句进程信息查看

3.1 SHOW PROCESSLIST 显示进程列表

SHOW [FULL] PROCESSLIST; 用于查看当前MySQL服务器上的所有运行中的进程列表信息。这个命令可以帮助我们了解哪些 SQL 语句正在执行，它们的状态是什么，以及它们已经执行了多长时间等。

1
2
3
4
5
6
7

mysql> SHOW PROCESSLIST;
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Query   | 0    | init  | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
1 row in set (0.00 sec)

其中每行显示的信息包括：

• Id: 线程的唯一标识符。
• User: 执行该命令的用户名。
• Host: 用户的主机名。通常是客户端IP地址、端口号或者都有。
• db: 当前线程正在工作的数据库。如果没有选定数据库，则为NULL。
• Command: 线程正在执行的命令类型。例如：Sleep，Query，Locked等。
• Time: 命令开始执行以来的秒数。对于’Sleep’命令，这是线程进入睡眠状态的时间。
• State: 显示线程的状态信息。这对找出性能问题特别有用。
• Info: 显示线程正在执行的查询。若没有查询在执行则该列为NULL。

如果没有 FULL 关键字， SHOW PROCESSLIST 仅显示 Info 字段中每个语句的前 100 个字符。

线程 Command 有以下值：

• Binlog Dump：这是用于将二进制日志内容发送到副本的复制源上的线程。
• Change user：线程正在执行更改用户操作。
• Close stmt：线程正在关闭预备语句。
• Connect：被连接到源的复制接收器线程以及复制工作器线程使用。
• Connect Out：副本正在连接到其源。
• Create DB：线程正在执行创建数据库操作。
• Daemon：此线程对服务器内部而言，不是为客户端连接提供服务的线程。
• Debug：线程正在生成调试信息。
• Delayed insert：线程是一个延迟插入处理器。
• Drop DB：线程正在执行删除数据库操作。
• Error：
• Execute：线程正在执行预备语句。
• Fetch：线程正在获取执行预备语句的结果。
• Field List：线程正在检索表列的信息。
• Init DB：线程正在选择默认数据库。
• Kill：线程正在杀死另一个线程。
• Long Data：线程正在检索执行预备语句结果中的长数据。
• Ping：线程正在处理服务器ping请求。
• Prepare：线程正在准备预备语句。
• Processlist：线程正在生成关于服务器线程的信息。
• Query：用户客户端在执行查询时使用，由单线程复制应用程序线程使用，以及由复制协调器线程使用。
• Quit：线程正在终止。
• Refresh：线程正在刷新表、日志或缓存，或重置状态变量或复制服务器信息。
• Register Slave：线程正在注册副本服务器。
• Reset stmt：线程正在重置预备语句。
• Set option：线程正在设置或重置客户端语句执行选项。
• Shutdown：线程正在关闭服务器。
• Sleep：线程正在等待客户端发送新的语句给它。
• Statistics：线程正在生成服务器状态信息。
• Time：未使用。

线程状态 State 分类有以下值:

• After create：当线程创建一个表（包括内部临时表）时，会出现这种情况，在创建表的函数结束时。即使由于某些错误无法创建表，也会使用此状态。
• altering table：服务器正在执行就地ALTER TABLE。
• Analyzing：线程正在计算MyISAM表键分布（例如，用于ANALYZE TABLE）。
• checking permissions：线程正在检查服务器是否具有执行语句所需的权限。
• Checking table：线程正在执行表检查操作。
• cleaning up：线程已处理一条命令，并准备释放内存并重置某些状态变量。
• closing tables：线程正在将更改后的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果不是，验证你是否没有满磁盘，并且磁盘没有在非常繁重的使用中。
• committing alter table to storage engine：服务器已完成就地ALTER TABLE并正在提交结果。
• converting HEAP to ondisk：线程正在将内部临时表从MEMORY表转换为磁盘上的表。
• copy to tmp table：线程正在处理ALTER TABLE语句。在已创建新结构的表之后但在将行复制进去之前发生此状态。
• **对于此状态的线程，可以使用性能模式来获取复制操作的进度。
• Copying to group table：如果语句具有不同的ORDER BY和GROUP BY条件，则将按组排序行并复制到临时表中。
• Copying to tmp table：服务器正在将数据复制到内存中的临时表。
• Copying to tmp table on disk：服务器正在将数据复制到磁盘上的临时表。临时结果集已经变得过大。因此，线程正在将临时表从内存格式更改为基于磁盘的格式以节省内存。
• Creating index：线程正在处理MyISAM表的ALTER TABLE … ENABLE KEYS。
• Creating sort index：线程正在处理使用内部临时表解析的SELECT。
• creating table：线程正在创建表。这包括创建临时表。
• Creating tmp table：线程正在在内存或磁盘上创建临时表。如果在内存中创建了表，但稍后将其转换为磁盘上的表，则在该操作期间的状态为Copying to tmp table on disk。
• deleting from main table：服务器正在执行多表删除的第一部分。它只从第一个表中删除，并保存列和偏移量以用于从其他（引用）表中删除。
• deleting from reference tables：服务器正在执行多表删除的第二部分，并从其他表中删除匹配的行。
• discard_or_import_tablespace：线程正在处理ALTER TABLE … DISCARD TABLESPACE或ALTER TABLE … IMPORT TABLESPACE语句。
• end：在ALTER TABLE，CREATE VIEW，DELETE，INSERT，SELECT或UPDATE语句的结束但在清理之前发生。
  • 对于结束状态，可能正在发生以下操作：
  • 将事件写入二进制日志
  • 释放内存缓冲区，包括blob：executing：线程已开始执行语句。
• Execution of init_command：线程正在执行init_command系统变量的值中的语句。
• freeing items：线程已执行一条命令。这个状态通常在cleaning up之前出现。
• FULLTEXT initialization：服务器正在准备进行自然语言全文搜索。
• init：在初始化ALTER TABLE，DELETE，INSERT，SELECT或UPDATE语句之前发生。在这种状态下，服务器采取的操作包括刷新二进制日志和InnoDB日志。
• Killed：有人向线程发送了一个KILL语句，它应该在下次检查kill标志时中止。该标志在MySQL的每一个主要循环中都会被检查，但在某些情况下，线程可能还需要短暂的时间才能死亡。如果线程被其他线程锁定，则在其他线程释放其锁定后立即生效。
• Locking system tables：线程正在尝试锁定一个系统表（例如，一个时区或日志表）。
• logging slow query：线程正在将语句写入慢查询日志。
• login：连接线程的初始状态，直到客户端成功进行身份验证。
• manage keys：服务器正在启用或禁用表索引。
• Opening system tables：线程正在尝试打开一个系统表（例如，一个时区或日志表）。
• Opening tables：线程正在尝试打开表。这应该是非常快速的过程，除非有什么阻止打开。例如，ALTER TABLE或LOCK TABLE语句可以阻止打开表，直到语句完成。还值得检查您的table_open_cache值是否足够大。
  • 对于系统表，使用Opening system tables状态代替。
• optimizing：服务器正在为查询执行初始优化。
• preparing：此状态在查询优化期间发生。
• preparing for alter table：服务器正在准备执行就地ALTER TABLE。
• Purging old relay logs：线程正在删除不需要的中继日志文件。
• query end：处理查询后但在freeing items状态之前发生此状态。
• Receiving from client：服务器正在从客户端读取数据包。
• Removing duplicates：查询正在以SELECT DISTINCT的方式使用，以致MySQL无法在早期阶段优化掉DISTINCT操作。由于此原因，MySQL需要一个额外的阶段来移除所有重复的行，然后再将结果发送给客户端。
• removing tmp table：线程在处理完SELECT语句后正在移除内部临时表。如果没有创建临时表，则不使用此状态。
• rename：线程正在重命名表。
• rename result table：线程正在处理ALTER TABLE语句，已经创建了新表，并正将其重命名以替换原始表。
• Reopen tables：线程获取了表的锁，但在获取锁后发现表的底层结构已更改。它已释放了锁，关闭了表，正在尝试重新打开它。
• Repair by sorting：修复代码使用排序来创建索引。
• Repair done：线程已完成MyISAM表的多线程修复。
• Repair with keycache：修复代码使用一次通过键缓存创建一个键。这比Repair by sorting慢得多。
• Rolling back：线程正在回滚事务。
• Saving state：对于MyISAM表操作，如修复或分析，线程正在将新表状态保存到.MYI文件头。状态包括行数、AUTO_INCREMENT计数器和键分布等信息。
• Searching rows for update：线程正在进行第一阶段，查找所有匹配的行，然后更新它们。如果UPDATE改变了用于查找所涉及行的索引，则必须这样做。
• Sending data：在MySQL 8.0.17之前：线程正在读取和处理SELECT语句的行，并将数据发送给客户端。因为在此状态期间发生的操作倾向于执行大量的磁盘访问（读取），所以它通常是给定查询在其生命周期内运行时间最长的状态。MySQL 8.0.17及更高版本：此状态不再单独指示，而是包含在Executing状态中。
• Sending to client：服务器正在将数据包写入客户端。
• **setup：线程开始进行ALTER TABLE操作。
• Sorting for group：线程正在执行排序以满足GROUP BY。
• Sorting for order：线程正在执行排序以满足ORDER BY。
• Sorting index：线程正在对索引页进行排序，以便在MyISAM表优化操作期间更有效地访问。
• Sorting result：对于SELECT语句，此为与Creating sort index类似，但适用于非临时表。
• starting：语句执行开始的第一阶段。
• statistics：服务器正在计算统计信息以制定查询执行计划。如果线程在此状态下停留了很长时间，那么服务器可能在磁盘绑定中执行其他工作。
• System lock：线程已调用mysql_lock_tables()并且自那时起线程状态未更新。这是一个非常通用的状态，可能出于许多原因。
  • 例如，线程将请求或正在等待表的内部或外部系统锁。当InnoDB在执行LOCK TABLES期间等待表级锁时，可能会发生这种情况。如果由于请求外部锁而导致这种状态，并且您没有使用访问相同MyISAM表的多个mysqld服务器，则可以使用–skip-external-locking选项禁用外部系统锁。然而，默认情况下是禁用外部锁定的，所以有可能这个选项没有效果。对于SHOW PROFILE，此状态意味着线程正在请求锁（不是等待它）。
  • 对于系统表，使用Locking system tables状态代替。
• update：线程准备开始更新表。
• Updating：线程正在搜索要更新的行并更新它们。
• updating main table：服务器正在执行多表更新的第一部分。它只更新第一个表，并保存列和偏移量以用于更新其他（引用）表。
• updating reference tables：服务器正在执行多表更新的第二部分，并更新其他表中的匹配行。
• **User lock：线程将请求或正在等待使用GET_LOCK()调用请求的咨询锁。对于SHOW PROFILE，这种状态意味着线程正在请求锁（而不是等待它）。
• User sleep：线程已调用SLEEP()。
• Waiting for commit lock：FLUSH TABLES WITH READ LOCK正在等待提交锁。
• waiting for handler commit：线程正在等待事务提交，与查询处理的其他部分相比。
• Waiting for tables：线程收到通知，表的底层结构已经更改，它需要重新打开表以获取新的结构。但是，要重新打开表，它必须等待所有其他线程都关闭了所述表。
  • 如果另一个线程在所述表上使用了FLUSH TABLES或以下语句之一：FLUSH TABLES tbl_name，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE，OPTIMIZE TABLE，则会发生此通知。
• Waiting for table flush：线程正在执行FLUSH TABLES并等待所有线程关闭其表，或者线程收到通知说表的底层结构已经更改，它需要重新打开表以获取新的结构。但是，要重新打开表，它必须等待所有其他线程都关闭了所述表。
  • 如果另一个线程在所述表上使用了FLUSH TABLES或以下语句之一：FLUSH TABLES tbl_name，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE，OPTIMIZE TABLE，则会发生此通知。
• Waiting for lock_type lock：服务器正在等待获得THR_LOCK锁或元数据锁定子系统的锁，其中lock_type指示锁的类型。
  • 这种状态表示等待一个THR_LOCK：
• Waiting for table level lock：这些状态表示等待元数据锁：
• Waiting for event metadata lock：Waiting for global read lock：Waiting for schema metadata lock：Waiting for stored function metadata lock：Waiting for stored procedure metadata lock：Waiting for table metadata lock：Waiting for trigger metadata lock：有关表锁指标的信息。有关元数据锁定的信息。要查看哪些锁阻止了锁请求。
• Waiting on cond：线程在等待某个条件变为真的通用状态。没有特定的状态信息可用。
• Writing to net：服务器正在将数据包写入网络。

Tips: INFORMATION_SCHEMA 库中的 PROCESSLIST在8.0以后的版本被标记为弃用，并将在未来的 MySQL 版本中被移除。因此，使用此表的 SHOW PROCESSLIST 的实现也已被弃用。建议改用 performance_schema 实现的 processlist。

在结构上，两张表的结构基本相同，高版本中performance_schema下的表比infomation_schema中表多一个execution_engine(执行引擎)字段。

3.2 MySQL 系统 performance_schema.processlist 表及其应用

替代 SHOW PROCESSLIST 的 SQL：

1
2
3
4
5

-- 查看performance_schema的属性
SHOW VARIABLES LIKE 'performance_schema';

-- 替代 SHOW PROCESSLIST 的 SQL
select * from performance_schema.processlist\G;

四、定位执行慢的SQL：慢查询日志

4.1 MySQL慢查询日志简介

MySQL的 慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 的值的SQL，则会被记录到慢查询日志中，long_query_time 的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了最大忍耐时间值。

默认情况下，MySQL数据库没有开启慢查询日志，需要手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

4.2 慢查询日志相关参数介绍

开启slow_query_log：

1

set global slow_query_log='ON';

查看下慢查询日志是否开启，以及慢查询日志文件的位置：

1
2

show variables like '%slow_query_log%';
SHOW VARIABLES LIKE '%slow%' ;     		-- 查询慢查询日志所在目录

修改long_query_time 阈值:

1
2
3
4
5
6

-- 测试发现：设置global的方式对当前session的long_query_time失效，对新连接的客户端有效，所以可以一并 执行下述语句
mysql> set global long_query_time=1;  -- 对当前session的long_query_time 无效，对新连接的客户端有效
mysql> show global variables like '%long_query_time%'; 

mysql> set long_query_time=1;  			-- 对当前session的long_query_time 有效
mysql> show variables like '%long_query_time%';

查看慢查询数目：

1

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

除了上述变量，控制慢查询日志的还有一个系统变量: min_examined_row_limit，这个变量的意思是，查询扫描过的最少记录数。

这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件，如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中; 反之，则不被记录到慢查询日志中。

1
2
3
4
5
6
7

mysql> show variables like 'min%';
+-------------------------+-------+
| Variable_namel          | Value |
+-------------------------+-------+
| min_examined_row_limit  | 0     |
+-------------------------+-------+
1 row in set,1 warning ( 0.00 sec)

min_examined_row_limit 的值默认是0，与 long_query_time=10 合在一起，表示只要查询的执行时间超过10秒，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。

也可以根据需要，通过修改 my.ini 文件，来修改查询时长，或者通过SET指令，用SQL语句修改 min_examined_row_limit 的值。

慢查询日志分析工具：mysqldumpslow 在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 查看mysqldumpslow的帮助信息
mysqldumpslow --help

#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 

#得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 

#另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

关闭慢查询日志

方式1：永久性方式

1
2
3
4
5

[mysqld] 
slow_query_log=OFF
# 或
[mysqld] 
# slow_query_log=ON

方式2：临时性方式

1

SET GLOBAL slow_query_log=off;

删除慢查询日志:

使用SHOW语句显示慢查询日志信息，具体SQL语句如下:

1

SHow VARIABLES LIKE 'slow_query_log%';

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件即可。

使用命令mysqladmin flush-logs 来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件:

1

mysqladmin -uroot -p flush-logs slow	# 该命令会见所有mysql 日志刷如磁盘中

Tips: 慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。

五、MySQL执行语句的资源使用与耗时情况分析

5.1 SQL性能分析之 SHOW PROFILES

MySQL show profile 和 show profiles 命令用于展示SQL语句执行过程中的资源使用情况，包括CPU的使用，CPU上下文切换，IO等待，内存使用等信息。可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量，默认情况下处于关闭状态，并保存最近15次的运行结果。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

show variables like 'profiling';
-- 开启
set profiling = 'ON';

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

type : {
	ALL 				-- 显示所有参数的开销信息
	BLOCK IO 			-- 显示IO的相关开销
	CONTEXT SWITCHES 	-- 上下文切换相关开销
	CPU 				-- 显示CPU相关开销信息
	IPC 				-- 显示发送和接收相关开销信息
	MEMORY 				-- 显示内存相关开销信息
	PAGE FAULTS 		-- 显示页面错误相关开销信息
	SOURCE 				-- 显示和Source_function, Source_file,Source_line相关的开销信息
	SWAPS 				-- 显示交换次数相关的开销信息
}

show profiles;
show profile cpu,block io for query 2;

日常开发需注意的结论:

• converting HEAP to MyISAM︰查询结果太大，内存不够，数据往磁盘上搬了。
• creating tmp table:创建临时表。先拷贝数据到临时表，用完后再删除临时表。
• Copying to tmp table on disk:把内存中临时表复制到磁盘上，警惕!
• locked 。

如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

其相关功能可以从 information_schema 中的 profiling 数据表进行查看。

1

select * from information_schema.profiling;

Tips：SHOW PROFILE 命令 和 information_schema.profiling 将被弃用，其相关功能迁移到了 Performance Schema 中。

5.2 SQL性能分析之 Performance Schema

1. 配置performance_schema

查看 performance_schema 功能是否开启

1

show variables like 'performance_schema';

配置参数：performance_schema=ON，该参数配置在my.cnf文件中，生效需要重启MySQL， 也可在控制台动态设置实时生效。

2. 配置表setup_actors

默认情况下，performance_schema功能打开后，将会收集所有用户的SQL执行历史事件，因为收集的信息太多，对数据库整体性能有一定影响，而且也不利于排查指定SQL的性能问题，因此需要修改setup_actors表的配置，只收集特定用户的历史事件信息。setup_actors表配置如下：

1
2
3
4
5
6
7
8

mysql> select * from performance_schema.setup_actors;
+-----------+------+------+---------+---------+
| HOST      | USER | ROLE | ENABLED | HISTORY |
+-----------+------+------+---------+---------+
| %         | %    | %    | NO      | NO      |
| localhost | root | %    | YES     | YES     |
+-----------+------+------+---------+---------+
2 rows in set (0.00 sec)

只收集本地root用户的SQL执行历史事件。

3. 配置表setup_instruments

启用statement和stage监视器。

1
2
3

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/%';

4. 配置表setup_consumers

启用events_statements_，events_stages_ 开头的事件类型消费。

1
2
3

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，比如：select * from blog;

5. 获取业务SQL的事件ID

1
2
3
4
5
6
7

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%blog%';
+----------+----------+-------------------------+
| EVENT_ID | Duration | SQL_TEXT                |
+----------+----------+-------------------------+
|      243 | 0.002698 | select * from blog.blog |
+----------+----------+-------------------------+
1 row in set (0.00 sec)

6. 根据事件ID，获取各阶段执行耗时

根据上一步获取的事件ID(EVENT_ID)，查询该SQL各个阶段的耗时情况。如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=243;
+--------------------------------+----------+
| Stage                          | Duration |
+--------------------------------+----------+
| stage/sql/starting             | 0.000117 |
| stage/sql/checking permissions | 0.000010 |
| stage/sql/Opening tables       | 0.000031 |
| stage/sql/init                 | 0.000055 |
| stage/sql/System lock          | 0.000024 |
| stage/sql/optimizing           | 0.000003 |
| stage/sql/statistics           | 0.000020 |
| stage/sql/preparing            | 0.000015 |
| stage/sql/executing            | 0.000001 |
| stage/sql/Sending data         | 0.002321 |
| stage/sql/end                  | 0.000004 |
| stage/sql/query end            | 0.000019 |
| stage/sql/closing tables       | 0.000018 |
| stage/sql/freeing items        | 0.000048 |
| stage/sql/cleaning up          | 0.000001 |
+--------------------------------+----------+
15 rows in set (0.00 sec)

六、EXPLAIN 分析 SQL 语句

6.1 EXPLAIN

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

MySQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（它认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)。

这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。

MySQL提供了 EXPLAIN语句 来帮助查看某个查询语句的具体执行计划，看懂 EXPLAIN语句 的各个输出项，可以有针对性的提升我们查询语句的性能。

Tips1:

• MySQL 5.6.3 以前只能EXPLAIN SELECT；
• MYSQL 5.6.3 以后就可以 EXPLAIN SELECT，UPDATE，DELETE;

Tips2:

• MySQL 5.7以前的版本中，想要显示partitions需要使用explain partitions命令；想要显示 filtered 需要使用explain extended命令。
• MySQL 5.7版本后，默认explain直接显示partitions和filtered中的信息。

Tips2:

• EXPLAIN不考虑各种cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定文函数对查询的影响情况
• 部分统计信息是估算的，并非精确值

6.2 EXPLAIN的基本语法

1
2
3

EXPLAIN SELECT select_options 
# 或者
DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

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

id: 在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id

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

select_type： 一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT天键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select.type的属性，意思是只要知道了某个小查询的select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，看一下select_type都能取哪些值，请看官方文档:

partitions: 代表分区表中的命中情况，非分区表，该项为NULL，一般情况下查询语句的执行计划的partitions列的值都是NULL。

type（重点）： 执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称 访问类型，其中的type列就代表了这个访问方法是什么，是较为重要的一个指标。比如，看到type列的值是 ref，表明MySQL即将使用 ref访问方法来执行对s1表的查询。

完整的访问方法如下: 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

Tips:SQL性能优化的目标：至少要达到 range级别，要求是ref级别，最好是const级别。（阿里巴巴开发手册要求）

possible_keys和key： 在EXPLAIN语句输出的执行计划中, possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。

一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

key列 表示实际用到的索引有哪些，如果为NULL，则没有使用索引。

key_len（重点）： key_len的长度计算公式：

1
2
3
4
5
6
7

varchar(10)变长字段且允许NULL = 10 * ( character set： utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) 

varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL) 

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

ref: 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、eq_ref、ref、ref_of_null、unique_subquery、index_subquery其中之一时，ref 列展示的就是与索引列作等值匹配的结构是什么，比如只是一个常数或者是某个列。

rows（重点）: 预估的需要读取的记录条数，值越小越好。

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

对个单表查询来说，这个filtered列的值没有什么意义，更关注连接查询。

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

• No tables used ——当查询语句没有 from 子句时将会提示该额外信息
• Impossible WHERE ——查询语句的 where 子句永远为 false 时将会提示该额外信息
• Using where ——当使用全表扫描来执行对给某个表的查询，并且该语句的 where 子句中有针对该表的搜索条件时，会提示该额外信息；当使用索引访问来执行对某个表的查询，并且该语句的 where 子句中有除了该索引包含的列之外的其它搜索条件时，也会提示该额外信息
• No matching min/max row ——当查询列表处有 MIN 或 MAX 聚合函数，但是并没有符合 WHERE 子句中的搜索条件的记录时
• Using index ——当查询列表以及搜索条件中只包含某个索引的列，也就是在可以使用覆盖索引的情况下，将提示该信息。比方说下边这个查询中只需要用到 idx_key1 而不需要回表操作。
• Using index condition ——有些搜索条件中虽然出现了索引列，但却不能使用到索引。称为索引条件下推。
• Using join buffer ——在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的 ‘基于块的嵌套循环算法’
• Not exists ——当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 ‘NULL’ 值的，将提示该额外信息。
• Using intersect（…） ——说明准备使用 Intersect 索引合并的方式执行查询，括号中的 … 表示需要进行索引合并的索引名称；Using union（…） ——说明准备使用 Union 索引合并的方式执行查询；Using sort_union（…），说明准备使用 Sort-Union 索引合并的方式执行查询。
• Zero limit ——当 limit 子句的参数为 0 时，表示压根儿不打算从表中读取任何记录，将提示该额外信息
• Using filesort ——很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序。如果某个查询需要文件排序的方式执行查询，将提示该信息
• Using temporary ——在许多查询执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT，GROUP BY， UNION` 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，将提示该信息。

6.3 EXPLAIN四种输出格式

EXPLAIN可以输出四种格式：传统格式、JSON格式、TREE格式 以及 可视化输出，用户可以根据需要选择适用于自己的格式： 1、 传统格式（默认）

2、JSON格式

JSON格式：在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON，用于查看执行成本cost_info：

3、TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

4、可视化输出

可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。

6.4 SHOW WARNINGS的使用

1
2
3
4
5
6
7
8
9

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

-- 查看优化后的执行语句
mysql> SHOW WARNINGS\G
***************************1. roW ***************************
  Level : Note
   Code : 1003
Message: /* select#1 */ select 'atguigu'.'s1'.'key1' AS 'key1', 'atguigu'.'s2'.'key1' AS 'key1' from 'atguigu'.'s1' join 'atguigu'.'s2' where (( 'atguigu'.'s1'.'key1' = 'atguigu'.'s2'.'key1') and ( 'atguigu'.'s2'.'common_field' is not null))
1 row in set (8.00 sec)

6.5 分析优化器执行计划：trace

OPTIMIZER_TRACE 是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到 INFORMATION_SCHEMALI.OPTIMIZER_TRACE 表中。

此功能默认关闭，开启trace，并设置格式为JSON，同时设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示：

1
2
3
4
5
6
7
8

-- 开启
SET optimizer_trace="enabled=on",end_markers_in_json=on; 
-- 设置大小
set optimizer_trace_max_mem_size=1000000;

-- 使用
select * from student where id < 10;
select * from information_schema.optimizer_trace\G

七、MySQL监控分析视图-sys schema

7.1 Sys schema视图摘要简介

关于MySQL的性能监控和问题诊断，一般都从 performance_schema 中去获取想要的数据，在MySQL5.7.7版本中新增sys schema，它将 performance_schernla 和 information_schema 中的数据以更容易理解的方式总结归纳为 视图，其目的就是为了降低查询 performance_schema 的复杂度，让DBA能够快速的定位问题。下面看看这些库中都有哪些监控表和视图，掌握了这些，在开发和运维的过程中就起到了事半功倍的效果。

Sys schema视图摘要

• 主机相关：以host_summary开头，主要汇总了IO延迟的信息。
• Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
• l/o相关：以io开头，汇总了等待I/O、I/o使用量情况。
• 内存使用情况：以memorv开头，从主机、线程、事件等角度展示内存的使用情况
• 连接与会话信息：processlist和session相关视图，总结了会话相关信息。
• 表相关：以schema_table开头的视图，展示了表的统计信息。
• 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
• 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
• 用户相关：以user开头的视图，统计了用户使用的文件I/o、执行语句统计信息。
• 等待事件相关信息：以wait开头，展示等待事件的延迟情况。

7.2 Sys schema视图使用场景

索引情况：

1
2
3
4
5
6

--1. 查询冗余索引 
select * from sys.schema_redundant_indexes; 
-- 2. 查询未使用过的索引 
select * from sys.schema_unused_indexes; 
-- 3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='dbname' ;

表相关:

1
2
3
4
5
6
7

-- 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
-- 2. 查询占用bufferpool较多的表 
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
-- 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关:

1
2
3
4
5
6
7
8

-- 1. 监控SQL执行的频率 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
-- 2. 监控使用了排序的SQL 
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1; 
-- 3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

IO相关:

1
2
3

-- 1. 查看消耗磁盘IO的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb相关:

1
2

-- 1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

风险提示:

• 通过sys库去查询时，MySQL会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁的去查询sys或者performance_schema、information_schema来完成监控、巡检等工作。

八、数据库其它调优策略

8.1 库级优化

1）读写分离

如果读和写的业务量都很大，并且它们都在同一个数据库服务器中进行操作，那么数据库的性能就会出现瓶颈，这时为了提升系统的性能，优化用户体验，可以采用读写分离的方式降低主数据库的负载，比如：用主数据库(master)完成写操作，用从数据库(slave）完成读操作。

2）数据分片（分库分表）

对数据库分库分表，当数据量级达到千万级以上时，有时候需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。

如果使用的是MySQL，就可以使用MySQL自带的分区表功能，当然也可以考虑自己做垂直拆分（分库）、水平拆分（分表)、垂直+水平拆分(分库分表)。

垂直拆分 如果数据库中的数据表过多，可以采用 垂直分库 的方式，将关联的数据表部署在同一个数据库上。

如果数据表中的列过多，可以采用 垂直分表 的方式，将一张数据表分拆成多张数据表，把经常一起使用的列放到同一张表里。

水平拆分 尽量控制单表数据宜的大小，建议控制在1000万以内。1000万并不是MsQL数据库的限制，过大会造成修改表结构、备份、恢复都会有很大的问题。此时可以用历史数据归档（应用天日志数据），水平分表（应用于业务数据）等手段来控制数据量大小。

这里主要考虑业务数据的水平分表策略。将大的数据表按照某个属性维度分拆成不同的小表，每张小表保持相同的表结构。比如可以按照年份来划分，把不向年份的数据放到不同的数据表中。

水平分表仅是解决了单一表数据过大的问题，但由于表的数据还是在风一台机器上，其实对于提升MysQL并发能力没有什么意义，所以水平拆分最好分库，从而达到分布式的目的。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐·尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选客户端分片架构，这样可以减少一次和中间件的网络I/O。

8.2 优化MySQL服务器

1、优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈直接决定MySQL数据库的运行速度和效率，针对性能瓶颈提高硬件配置，可以提高MySQL数据库查询、更新的速度。

• 配置较大的内存
• 配置高速磁盘系统
• 合理分布磁盘I/O
• 配置多处理器

8.3 优化数据库结构

1、拆分表：冷热数据分离

拆分表的思路是，把1个包含很多字段的表拆分成2个或者多个相对较小的表。这样做的原因是，这些表中某些字段的操作频率很高（热数据），经常要进行查询或者更新操作，而另外一些字段的使用频率却很低（冷数据），冷热数据分离，可以减小表的宽度。如果放在一个表里面，每次查询都要读取大记录，会消耗较多的资源。

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节。表越宽，把表装载进内存缓冲池时所占用的内存也就越大，也会消耗更多的IO。冷热数据分离的目的是:

• ①减少磁盘lO，保证热数据的内存缓存命中率。
• ②更有效的利用缓存，避免读入无用的冷数据。

2、增加中间表

把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

3、增加冗余字段

4、优化数据类型

优先选择符合存储需要的最小的数据类型。

情况1：对整数类型数据进行优化。

遇到整数类型的字段可以用INT 型。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数据超出取值范围的问题。刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以的。但在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。

对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型UNSIGNED来存储。因为无符号相对于有符号，同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255，多出一倍的存储空间。

情况2：既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型 。

跟文本类型数据相比，大整数往往占用更少的存储空间，因此，在存取和比对的时候，可以占用更少的内存空间。所以，在二者皆可用的情况下，尽量使用整数类型，这样可以提高查询的效率。如：将IP地址转换成整型数据。

情况3：避免使用TEXT、BLOB数据类型 情况4：避免使用ENUM类型 情况5：使用TIMESTAMP存储时间 情况6：用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

总之，遇到数据量大的项目时，一定要在充分了解业务需求的前提下，合理优化数据类型，这样才能充分发挥资源的效率，使系统达到最优 。

8.4 优化插入记录的速度

MyISAM引擎的表：

• 禁用索引
• 禁用唯一性检查
• 使用批量插入
• 使用LOAD DATA INFILE 批量导入

InnoDB引擎的表：

• 禁用唯一性检查
• 禁用外键检查
• 禁止自动提交

8.5 使用非空约束

在设计字段的时候，如果业务允许，建议尽量使用非空约束

8.6 分析表、检查表与优化表

MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布，检查表主要是检查表是否存在错误，优化表主要是消除删除或者更新造成的空间浪费。

1、分析表

1

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

默认的，MySQL服务会将 ANALYZE TABLE语句写到binlog中，以便在主从架构中，从服务能够同步数据。可以添加参数LOCAL 或者 NO_WRITE_TO_BINLOG取消将语句写到binlog中。

使用ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能作用于视图。

ANALYZE TABLE分析后的统计结果会反应到cardinality的值，该值统计了表中某一键所在的列不重复的值的个数。 该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。

2、检查表

1

CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

MySQL中可以使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。CHECK TABLE语句在执行过程中也会给表加上只读锁。

3、优化表

1

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

MySQL中使用OPTIMIZE TABLE语句来优化表。但是，OPTILMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段。一个表使用了这些字段的数据类型，若已经删除了表的一大部分数据，或者已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

OPTIMIZE TABLE 语句对InnoDB和MyISAM类型的表都有效。该语句在执行过程中也会给表加上只读锁。

8.7 其它调优策略

• 服务器语句超时处理
• 创建全局通用表空间
• MySQL8.0新特性：隐藏索引对调优的帮助
• 使用 Redis或 Memcached 作为缓存