数据准备
创建数据库
创建数据库表
查看代码
CREATE TABLE `t_user` (
`USER_ID` varchar(32) NOT NULL COMMENT '用户ID，主键',
`USERNAME` varchar(50) DEFAULT NULL COMMENT '用户名',
`PASSWORD` varchar(50) DEFAULT NULL COMMENT '用户密码',
`AGE` tinyint unsigned DEFAULT NULL COMMENT '用户年龄',
PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';
CREATE TABLE `t_role` (
`ROLE_ID` varchar(32) NOT NULL COMMENT '角色ID，主键',
`ROLE_NAME` varchar(50) NOT NULL COMMENT '角色名称',
PRIMARY KEY (`ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色表';
CREATE TABLE `t_user_role` (
`ID` varchar(32) NOT NULL COMMENT '关系表ID，主键',
`USER_ID` varchar(32) NOT NULL COMMENT '用户ID，外键指向t_user.USER_ID',
`ROLE_ID` varchar(32) NOT NULL COMMENT '角色ID，外键指向t_role.ROLE_ID',
PRIMARY KEY (`ID`),
UNIQUE KEY `user_role_idx` (`USER_ID`,`ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户角色关系表';创建存储过程
查看代码
DELIMITER $$
CREATE PROCEDURE InsertRandomUsers(IN start_id INT, IN end_id INT)
BEGIN
DECLARE i INT DEFAULT start_id;
WHILE i <= end_id DO
INSERT INTO t_user (USER_ID, USERNAME, PASSWORD, AGE)
VALUES (
CONCAT('user_', i),
CONCAT('username_', i),
CONCAT('password_', FLOOR(RAND() * 10000)),
FLOOR(RAND() * 100) -- 假设年龄在0到99之间随机
);
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE InsertRandomRoles(IN start_id INT, IN end_id INT)
BEGIN
DECLARE i INT DEFAULT start_id;
WHILE i <= end_id DO
INSERT INTO t_role (ROLE_ID, ROLE_NAME)
VALUES (
CONCAT('role_', i),
CONCAT('rolename_', i)
);
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE InsertRandomUserRoles(IN start_id INT, IN end_id INT)
BEGIN
DECLARE i INT DEFAULT start_id;
WHILE i <= end_id DO
INSERT INTO t_user_role (ID, USER_ID, ROLE_ID)
VALUES (
CONCAT('id_', i),
CONCAT('user_', FLOOR(RAND() * (end_id - start_id + 1)) + start_id),
CONCAT('role_', FLOOR(RAND() * (end_id - start_id + 1)) + start_id)
);
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;插入数据
查看代码
CALL InsertRandomUsers(1, 100000);
CALL InsertRandomRoles(1, 100000);
CALL InsertRandomUserRoles(1, 100000);
DROP PROCEDURE IF EXISTS InsertRandomUsers;
DROP PROCEDURE IF EXISTS InsertRandomRoles;
DROP PROCEDURE IF EXISTS InsertRandomUserRoles;
SELECT
(SELECT COUNT(*) FROM t_user) AS user_count,
(SELECT COUNT(*) FROM t_role) AS role_count,
(SELECT COUNT(*) FROM t_user_role) AS user_role_count;
开启慢查询日志
查询慢查询配置
默认情况下，MySQL 数据库不会启用慢查询日志（slow query log），因此需要手动开启。
要检查慢查询日志的配置，可以使用 show variables like 'slow_query_log%' 命令。此命令将返回两个主要变量：
slow_query_log：表示慢查询日志是否开启。slow_query_log_file：表示慢查询日志文件的位置。
此外，我们可以使用 show variables like 'long_query_time' 命令来查看超过多少秒的查询会被记录到慢查询日志中。long_query_time 变量表示查询超过多少秒时，将被记录到慢查询日志。（单位：秒，默认10秒）
设置慢查询配置
临时设置
通过MySQL命令行临时开启慢查询日志：
SET GLOBAL slow_query_log = 'ON';设置慢查询日志的记录时间阈值：
SET GLOBAL long_query_time = 0.1; -- 设置为0.1秒查询当前设置：
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
重新打开一个新的MySQL会话（连接），然后执行SHOW VARIABLES LIKE 'long_query_time%'命令，这样就可以看到全局设置的新值。
永久设置
找到MySQL的配置文件（通常是my.cnf或my.ini），在[mysqld]部分添加或修改以下设置：
[mysqld]
slow_query_log = ON
slow_query_log_file = /path/to/your/slow_query_log_file.log
long_query_time = 5保存并关闭配置文件。
重启MySQL服务以使更改生效。
模拟慢SQL
SELECT
*,
SLEEP( 0.1 )
FROM
t_user
WHERE
USERNAME = 'username_1000';
查看慢查询日志
打开slow_query_log_file对应的文件可以查看慢查询日志。
这个慢查询日志提供了执行时间较长的MySQL查询的详细信息。
慢SQL解析
启动信息：
/usr/sbin/mysqld, Version: 8.0.20 (MySQL Community Server - GPL). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock这部分信息说明MySQL服务是由
/usr/sbin/mysqld这个二进制文件启动的，版本是8.0.20社区版。服务监听在TCP端口3306上，并且使用了Unix域套接字/var/run/mysqld/mysqld.sock。日志条目头部：
Time Id Command Argument # Time: 2024-05-20T03:10:58.872155Z # User@Host: root[root] @ [192.168.100.1] Id: 9Time：日志条目的时间戳，显示查询发生的日期和时间（UTC时区）。User@Host：执行查询的用户和主机信息。这里显示root用户从192.168.100.1这个IP地址连接到MySQL服务器。Id：查询的线程ID。
查询性能信息：
# Query_time: 0.160266 Lock_time: 0.001067 Rows_sent: 1 Rows_examined: 100000Query_time：查询执行的总时间，这里是0.160266秒。Lock_time：查询获取锁的总时间，这里是0.001067秒。Rows_sent：查询返回的行数，这里是1行。Rows_examined：查询检查的行数，这里是100,000行。
查询内容：
- 具体执行的慢SQL。
使用EXPLAIN分析查询计划
EXPLAIN 用于显示 MySQL 优化器为 SQL 查询生成的执行计划。它显示查询的执行路径，包括表的访问顺序、使用的索引、连接类型、行数估算等。
理解EXPLAIN输出
当使用EXPLAIN关键字与SQL查询一起时，MySQL会返回一个执行计划，这个执行计划提供了MySQL优化器是如何解析查询的详细信息。这包括关于表如何连接以及它们的连接顺序等。
下面是关于EXPLAIN输出中各列的含义。
id
id列表示SELECT语句的标识符。这个标识符在查询执行计划中是唯一的，并且对于每个SELECT语句或者子查询都会有一个id值。
id列的值有以下几个作用：
- 标识查询的顺序：在执行计划中，
id列的值可以帮助我们理解查询的执行顺序。MySQL按照id值的大小顺序来执行查询，id值越大，查询被执行的越早。 - 标识子查询：如果查询包含子查询，那么主查询和子查询都会有自己的
id值。子查询的id值通常会比主查询的id值大。 - 标识联合查询：在联合查询（UNION）中，每个SELECT语句都会有一个唯一的
id值。如果MySQL需要对多个联合查询的结果进行合并，那么这些查询的id值可能会有相同的父级id值。
id列的值可能包含一些特殊的字符，比如NULL，这通常表示这是一个非唯一的查询，或者是一个无法确定的查询。
在涉及到多个表和子查询的复杂查询中，id列的值可以让我们能理解查询的执行流程。
EXPLAIN SELECT
ROLE_NAME
FROM
t_role
WHERE
ROLE_ID IN ( SELECT ROLE_ID FROM t_user_role WHERE USER_ID = ( SELECT USER_ID FROM t_user WHERE USERNAME = 'username_100' ) )
AND ROLE_NAME = 'rolename_53026';
id的值越大，越先执行，id的值相同表示这些行是同一级的查询，按顺序执行。
在上图中，
id列的值为3的行表示最内层的子查询。这个子查询是SELECT USER_ID FROM t_user WHERE USERNAME = 'username_100'，它正在t_user表上执行全表扫描（ALL）来查找USERNAME为'username_100'的USER_ID。这个查询是独立的，因为它是最内层的查询，所以它会最先执行。
接下来，id列的值为1的行表示主查询的一部分，SELECT ROLE_ID FROM t_user_role WHERE USER_ID = ...。这个查询依赖于最内层子查询的结果，因为它需要使用最内层查询返回的USER_ID。这里使用了一个索引查找（ref），因为USER_ID在t_user_role表上的user_role_idx索引上被查询。
最后，id列的值为1的另一行表示主查询的最外层，SELECT ROLE_NAME FROM t_role WHERE ROLE_ID IN ... AND ROLE_NAME = 'rolename_53026'。这个查询同样依赖于子查询的结果，因为它需要使用子查询返回的ROLE_ID列表。这里使用了eq_ref连接类型，因为它正在使用t_role表的主键（PRIMARY索引）来查找与子查询返回的ROLE_ID相匹配的行。
select_type
select_type列提供了关于SELECT语句类型的额外信息。这让我们知道查询是简单查询、联合查询还是子查询，以及MySQL是如何执行这些查询的。
select_type列可以取以下值：
- SIMPLE：简单的SELECT查询，不包含子查询和联合查询。
- PRIMARY：查询中包含子查询时，最外层的查询被标记为PRIMARY。
- SUBQUERY：子查询中的第一个SELECT，并且这个子查询不在FROM子句中。
- DERIVED：子查询中的第一个SELECT，并且这个子查询在FROM子句中。MySQL会将子查询的结果物化成一个临时表，然后对这个临时表进行查询。
- UNION：联合查询中的第二个或后面的SELECT语句，除了第一个SELECT语句会被标记为PRIMARY外，其他联合查询中的SELECT语句都会被标记为UNION。
- UNION RESULT：联合查询的结果，即从多个联合查询中合并结果的SELECT语句。
- DEPENDENT SUBQUERY：子查询中的第一个SELECT，依赖于外部查询，并且这个子查询不在FROM子句中。
- DEPENDENT UNION：联合查询中的第二个或后面的SELECT语句，依赖于外部查询。
- UNCACHEABLE SUBQUERY：子查询中的第一个SELECT，无法缓存其结果，并且必须为外部查询的每一行重新评估。
- UNCACHEABLE UNION：联合查询中的第二个或后面的SELECT语句，属于无法缓存的子查询。
理解select_type列的值可以帮助我们识别查询中的性能瓶颈。例如，如果查询中包含子查询，并且子查询被标记为DEPENDENT SUBQUERY，那么这可能是一个性能问题，因为子查询可能会为外部查询的每一行重新执行。在这种情况下，我们可能需要考虑重写查询，以减少子查询的依赖性，或者将子查询转换为连接查询，以提高性能。
同样，如果查询中包含联合查询，并且有多个UNION类型的SELECT语句，那么我们可能需要检查是否所有联合查询都是必要的，以及是否可以通过合并条件或使用其他方法来简化查询。
table
查询涉及的表名。
type
type列表示MySQL查询使用的连接类型，它描述了MySQL决定如何查找表中的行。这个列非常关键，因为它给出了查询性能的直观指示。以下是从最好到最差的连接类型列表及其解释：
- system：这种类型是
const类型的特例，当表中只有一行数据时会发生。这是一个非常快且效率极高的查询类型，因为MySQL知道结果只有一行，且这行数据已经在系统表中。 - const：当查询通过主键或唯一索引与常数比较时，MySQL能够快速地找到一行匹配的记录。因为只有一行，所以这个查询非常快。
- eq_ref：在使用唯一索引的情况下，对于每个索引键，只从表中查询一条记录。这种情况通常发生在使用主键或唯一索引进行的联接操作中。
- ref：使用非唯一索引扫描，可能会返回多条匹配的记录。这种类型通常出现在使用非唯一索引或者唯一索引的非主键列进行的查找中。
- fulltext：使用全文索引进行搜索。
- ref_or_null：这种类型类似于
ref，但是MySQL会额外搜索包含NULL值的行。 - index_merge：当查询使用了两个以上的索引时，MySQL会将它们合并在一起使用。
- unique_subquery：子查询中使用的是唯一索引。
- index_subquery：子查询中使用的是非唯一索引。
- range：使用索引来检索指定范围的行，例如
BETWEEN、>、<等操作符。 - index：全索引扫描，这通常比ALL要好，因为索引通常比数据表小。
- ALL：全表扫描，这意味着MySQL没有使用索引，而是扫描了整个表来找到匹配的行。这是最慢的连接类型，通常需要优化。
优化建议：
- 尽可能使用
system、const、eq_ref和ref，避免使用ALL。 - 如果查询使用了
range或index，考虑添加索引或优化查询条件。 - 如果查询中出现了
Using filesort或Using temporary，这通常意味着查询效率低下，需要特别关注。 - 如果
type为index_merge，确保这种索引合并策略是必要的，并且能够提高性能。
possible_keys
possible_keys列显示了MySQL在执行查询时可能会使用到的索引。这个列是MySQL优化器根据查询的条件和使用的表来确定的。possible_keys列包含了多个索引的名称，这些索引可能有助于查询的执行。
possible_keys列的值是基于以下几个方面来确定的：
- 查询条件：优化器会分析查询中的WHERE子句，找出可以用来加速条件判断的索引。
- 表结构：优化器会考虑表的索引定义，包括主键索引、唯一索引、普通索引和全文索引。
- 连接条件：在多表连接查询中，优化器会考虑所有涉及表的索引，以及它们如何相互关联。
possible_keys列提供了以下信息：
- 如果
possible_keys列包含索引，这意味着查询可以使用这些索引来提高性能。 - 如果
possible_keys列是空的，这意味着MySQL没有找到任何可以用于加速查询的索引。 - 如果
possible_keys列包含了多个索引，MySQL会根据查询的具体情况来选择最有效的索引。
需要注意的是，possible_keys列只是列出了一组可能的索引，并不保证MySQL一定会使用这些索引。MySQL实际上会使用key列来指示它决定使用的索引。如果key列是空的，那么MySQL可能会选择不使用索引，而是进行全表扫描。
在优化查询时，我们可以检查possible_keys列，确保MySQL考虑到了所有相关的索引。如果发现possible_keys列列出的索引没有在key列中使用，我们可能需要检查查询的条件，确保它们能够利用索引。此外，如果possible_keys列列出了多个索引，我们可能需要考虑索引的选择性，以便确定哪个索引最适合我们的查询。
key
key列显示了MySQL实际决定使用的索引来优化查询。这个列是MySQL查询优化器基于查询的成本分析选择出来的，它告诉我们MySQL在执行查询时实际采用了哪个索引。
key列的值可能包含以下几种情况：
- 单个索引：如果
key列显示了一个索引的名称，那么MySQL在执行查询时只使用了一个索引。 - 复合索引：如果
key列显示了多个索引的名称，以逗号分隔，那么MySQL在执行查询时使用了复合索引。 - NULL：如果
key列的值为NULL，那么MySQL没有使用任何索引，这可能是因为表上没有合适的索引，或者查询的条件不适合使用已有的索引。
key列的值是优化器从possible_keys列列出的候选索引中选择出来的。
在优化查询时：
- 如果
key列显示了索引，这意味着MySQL认为使用这个索引是最优的。还可以进一步检查索引是否适合查询的条件，以及是否有更好的索引可以选择。 - 如果
key列是空的，而possible_keys列列出了索引，这可能是一个优化机会。我们可能需要检查查询的条件，确保它们能够利用索引，或者考虑添加或修改索引。 - 如果
key列是空的，而possible_keys列也是空的，这通常意味着查询无法通过索引来优化，我们可能需要考虑重写查询或者优化表结构。
key_len
key_len列表示MySQL决定使用的索引的长度（字节数）。key_len的值通常与索引的字段类型、字符集和是否为NULL有关。
key_len的计算考虑了以下几个因素：
- 字段类型：不同类型的字段占用不同的字节数。例如，INT类型通常占用4个字节，VARCHAR(255)在UTF-8字符集下可能占用255*3个字节。
- 字符集：字符类型的字段会受到字符集的影响。例如，UTF-8字符集下，每个字符可能占用3个字节，而latin1字符集下，每个字符只占用1个字节。
- 是否为NULL：如果一个字段允许为NULL，MySQL需要在索引中额外占用一个字节来标记NULL值。
- 复合索引：如果使用了复合索引，
key_len将是所有参与索引字段长度的总和。
ref
ref列显示了哪些列或常量被用于索引查找。这个列提供了关于MySQL如何使用索引来执行查询的额外信息。
ref列的值通常与type列的值相关联，因为type列描述了查询使用了哪种连接类型，而ref列描述了这种连接类型是如何实现的。
ref列的值可能包含以下几种情况：
- 常数：如果
ref列显示为一个常数（如const），这意味着查询使用了常数值来检索记录。 - 列名：如果
ref列显示为一个列名，这意味着查询使用了这个列的值来检索记录。这通常发生在使用非唯一索引进行查找时。 - 函数或表达式：如果
ref列显示为一个函数或表达式（如func），这意味着查询使用了函数或表达式来检索记录。 - NULL：如果
ref列的值为NULL，这意味着查询没有使用到二级索引，而是使用了其他方式来检索记录，比如全表扫描或索引合并。
rows
rows列显示了MySQL估计为了找到所需的记录需要读取的行数。这个数字是基于查询的成本分析得出的估计值，而不是一个确切的计数。
filtered
filtered列提供了一个百分比，这个百分比表示存储引擎返回的数据在经过服务器层过滤后，剩下满足条件的记录数量的估计。这个列是在MySQL 5.1及其之后的版本中引入的，用于提供更详细的查询性能分析。
- 如果
filtered列的值很高（接近100%），这意味着存储引擎返回的绝大部分数据都满足查询条件，服务器层不需要做太多的过滤工作。 - 如果
filtered列的值很低，这意味着存储引擎返回的数据中只有一小部分满足查询条件，服务器层需要做大量的过滤工作，这可能会导致性能问题。
Extra
Extra列提供了关于MySQL如何解析查询的额外信息。这个列包含了MySQL优化器和执行引擎使用的多种策略的描述，这些策略可能会影响查询的性能。Extra列的信息通常是解释查询性能问题的关键，因为它提供了查询执行的细节。
Extra列可能包含以下几种情况：
- Using filesort：MySQL需要额外的一次排序来满足ORDER BY子句，而不是按照索引顺序来检索行。这通常发生在没有使用索引进行排序的情况下，可能会导致性能瓶颈。
- Using index：查询使用了覆盖索引，即索引包含了查询所需的所有列，不需要回表查询数据。这是一种非常高效的情况。
- Using temporary：MySQL需要创建临时表来处理查询，这通常发生在GROUP BY或ORDER BY语句中，可能会导致性能问题。
- Using where：MySQL使用了WHERE子句来限制哪些行与下一个表匹配或发送给客户端。这是正常的情况，但如果WHERE子句的条件很复杂，可能会影响性能。
- Using index condition：MySQL使用了索引下推优化，在存储引擎层进行数据过滤，而不是在服务层过滤，这可以提高性能。
- Using join buffer (Block Nested Loop)：MySQL使用了连接缓冲区来处理连接操作，这通常发生在没有有效索引的连接操作中。
- Impossible WHERE：WHERE子句的条件导致了没有数据可以匹配，这种情况通常意味着查询逻辑有误。
- Select tables optimized away：优化器已经从查询中消除了某些表，例如，当子查询只引用了来自一个表的列时。
Extra列的值可以帮助我们理解查询的性能特征：
- 如果
Extra列包含Using filesort或Using temporary，这通常意味着查询效率较低，需要特别关注。 - 如果
Extra列包含Using index，这意味着查询非常高效，因为MySQL可以直接从索引中获取所需的数据。 - 如果
Extra列包含Using where，这通常是正常的，但如果WHERE子句非常复杂，可能需要优化。 在优化查询时，我们应该关注Extra列的值： - 如果
Extra列的值与我们的预期不符，可能需要检查查询的条件，以确保它们能够有效地使用索引。 - 如果
Extra列包含性能警告，如Using filesort或Using temporary，这可能是一个优化机会，我们可能需要重写查询或优化表结构。
实战EXPLAIN
使用主键索引
EXPLAIN SELECT
USER_ID
FROM
t_user
WHERE
USER_ID = 'user_10086';
id：1，查询是简单的单表查询，没有子查询或联合查询。
select_type：SIMPLE，表示这是一个简单的SELECT查询，没有子查询或联合查询。
table：t_user，表示查询的是t_user表。
type：const，这意味着MySQL通过索引一次就找到了所需的记录，通常是因为使用了主键或唯一索引。这是一个非常快且效率极高的查询类型。
possible_keys：PRIMARY，表示可能使用的索引是主索引。
key：PRIMARY，表示实际使用的索引是主索引。
key_len：130，表示实际使用的索引长度为130个字节。这个长度可能是基于字段类型和字符集计算的。
ref：未显示，这可能是因为查询使用了常数条件。
rows：1，表示MySQL估计只需要读取一行数据。
filtered：100.00，表示所有读取的行都满足查询条件。
Extra：Using index，表示查询使用了覆盖索引，不需要回表查询数据。这是一种非常高效的情况。
不使用索引
EXPLAIN SELECT
USER_ID
FROM
t_user
WHERE
USERNAME = 'username_1000';
type：ALL，这是一个全表扫描的类型，意味着MySQL没有使用索引来定位数据，而是直接扫描了整个表。全表扫描通常比使用索引慢得多。
rows：95996，表示MySQL估计需要读取95996行数据来找到满足条件的记录。这是一个非常大的数字，意味着查询效率可能很低。
filtered：10.00，表示所有读取的行中只有10%满足查询条件。这是一个相对较低的过滤率，意味着大部分读取的行都不符合查询条件。
Extra：Using where，表示查询使用了WHERE子句来限制哪些行与下一个表匹配或发送给客户端。这是一个正常的情况，但如果WHERE子句的条件很复杂，可能会影响性能。
JSON格式
EXPLAIN FORMAT=JSON SELECT ...
这个参数会让MySQL以JSON格式返回执行计划。JSON格式的输出包含了更详细的信息，如查询条件、各个阶段的成本估计等。
EXPLAIN FORMAT=JSON SELECT
USER_ID
FROM
t_user
WHERE
USERNAME = 'username_1000';返回结果：
查看代码
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "10458.27"
},
"table": {
"table_name": "t_user",
"access_type": "ALL",
"rows_examined_per_scan": 95996,
"rows_produced_per_join": 9599,
"filtered": "10.00",
"cost_info": {
"read_cost": "9498.32",
"eval_cost": "959.96",
"prefix_cost": "10458.28",
"data_read_per_join": "4M"
},
"used_columns": [
"USER_ID",
"USERNAME"
],
"attached_condition": "(`sql_cengxuyuan`.`t_user`.`USERNAME` = 'username_1000')"
}
}
}使用PROFILING分析执行耗时
EXPLAIN语句用于查看查询语句的执行计划，它提供了查询执行过程中的一些信息，如如何连接表、使用哪些索引等。但EXPLAIN并不能提供关于查询实际执行时的资源消耗情况，如实际执行时间、CPU和内存使用情况等。
为了获取这些详细信息，MySQL提供了PROFILING功能。PROFILING功能可以在会话级别开启，用于记录当前会话中所有SQL语句的资源消耗情况。
开启PROFILING功能
使用以下SQL语句可以检查PROFILING功能是否开启：
SHOW VARIABLES LIKE '%profil%';
have_profiling: 此变量指示服务器是否支持性能分析功能。值为YES表示我们的MySQL服务器支持PROFILING功能。
profiling: 此变量指示当前会话的性能分析功能是否开启。值为ON表示对于当前会话，性能分析功能是开启的，这意味着所有执行的SQL语句都会被记录其资源消耗情况。
profiling_history_size: 此变量指定了性能分析历史记录中保存的SQL语句条数。值为15表示在SHOW PROFILES命令的输出中，将会显示最近执行的15条SQL语句的资源消耗概要。
如果PROFILING未开启，执行以下SQL语句：
SET profiling = ON;执行后，对于当前会话，所有后续执行的SQL语句都将记录其资源消耗情况。
执行SQL语句
开启PROFILING后，执行多次分析的SQL语句。
查看SQL语句的资源消耗
使用SHOW PROFILES可以查看最近执行的一些SQL语句的资源消耗概要：
SHOW PROFILES;这将显示一个列表，包括每条语句的执行时间和一个唯一的QUERY_ID。
SHOW PROFILE将提供包括CPU时间、内存使用、IO操作、上下文切换等详细信息。通过这些信息，可以深入分析SQL语句的性能瓶颈，并据此进行优化。
需要注意的是，PROFILING功能仅在MySQL服务器的会话级别有效，且会对服务器性能有一定影响，因此建议在生产环境中谨慎使用，主要用于开发和测试环境中的性能调优。
此外，从MySQL 5.7.8开始，PROFILING功能已被弃用，并在MySQL 8.0中被移除，取而代之的是性能 schema 和 sys schema，它们提供了更为详细和精细的性能诊断数据。
查看具体SQL语句的详细分析
如果要查看某一条SQL语句的详细分析报告，可以使用：
SHOW PROFILE FOR QUERY QUERY_ID;其中QUERY_ID是SHOW PROFILES中显示的对应SQL语句的ID。
SHOW PROFILE FOR QUERY 98;
这个输出列出了查询执行过程中的各个阶段以及每个阶段消耗的时间。
starting: 表示开始执行查询的时间点。Executing hook on transaction: 执行事务钩子（hook）的时间。这是MySQL用于插件或外部工具在事务开始之前执行代码的机制。checking permissions: 检查查询权限的时间。Opening tables: 打开表的时间。这个阶段包括打开所需表并获取其元数据。init: 初始化查询执行结构的时间。System lock: 获取系统级别的锁的时间，例如在修改表结构时。optimizing: 优化查询执行计划的时间。statistics: 收集统计信息用于查询优化的时间。preparing: 准备执行计划的时间，包括解析查询和创建执行计划。executing: 执行查询的时间，这是实际执行SQL语句的时间，通常是整个查询中耗时最长的部分。end: 查询执行结束的时间点。query end: 查询结束后的清理工作的时间。waiting for handler commit: 等待存储引擎提交事务的时间。closing tables: 关闭表的时间。freeing items: 释放查询执行过程中使用的内存结构的时间。cleaning up: 清理查询执行过程中使用的其他资源的时间。
这些阶段提供了查询执行的详细分解，可以帮助我们识别查询性能的瓶颈。例如，如果executing阶段耗时很长，那么可能是查询优化或存储引擎层面的性能问题。如果Opening tables阶段耗时很长，那么可能是表结构或文件系统层面的性能问题。
要具体显示所有的开销信息，加一个ALL。
SHOW PROFILE ALL FOR QUERY 98;
SHOW PROFILE ALL 用于显示在 MySQL 中执行查询时的各种性能指标和资源消耗。
| 字段 | 含义 |
|---|---|
| Status | 当前查询的状态，表示查询执行的不同阶段，例如 "starting"、"executing"、"sending data" 等。 |
| Duration | 该状态持续的时间（秒），表示查询在某一特定状态下花费的时间。 |
| CPU_user | 查询在用户态（user mode）下消耗的 CPU 时间。 |
| CPU_system | 查询在内核态（system mode）下消耗的 CPU 时间。 |
| Context_voluntary | 查询执行过程中发生的自愿上下文切换的次数，通常是由于线程主动放弃 CPU 使用权，例如等待 I/O 操作时。 |
| Context_involuntary | 查询执行过程中发生的非自愿上下文切换的次数，通常是由于线程被操作系统强制中断，例如 CPU 时间片耗尽。 |
| Block_ops_in | 查询执行过程中发生的块设备输入操作次数。 |
| Block_ops_out | 查询执行过程中发生的块设备输出操作次数。 |
| Messages_sent | 查询执行过程中发送的消息数量。 |
| Messages_received | 查询执行过程中接收的消息数量。 |
| Page_faults_major | 查询执行过程中发生的主要缺页错误次数，主要缺页错误指需要从磁盘加载内存页的情况。 |
| Page_faults_minor | 查询执行过程中发生的次要缺页错误次数，次要缺页错误指页面已经在内存中但需要进行某些调整（如修改页面表）。 |
| Swaps | 查询执行过程中发生的交换（swap）操作次数，交换是将内存页移到交换空间（通常是磁盘）以释放内存的操作。 |
| Source_function | 引发当前状态的源码函数名。 |
| Source_file | 引发当前状态的源码文件名。 |
| Source_line | 引发当前状态的源码行号。 |
使用Optimizer Trace分析查询优化过程
Optimizer Trace 记录并显示 MySQL 优化器在生成查询执行计划过程中所做的每一步决策和评估。它输出优化器在各个阶段的详细操作，包括查询解析、条件处理、访问方法选择、索引评估等。
启用 Optimizer Trace
可以使用以下命令查看当前的设置
SHOW VARIABLES LIKE 'optimizer_trace';
需要开启 Optimizer Trace。执行以下命令：
SET optimizer_trace="enabled=on";这个设置将允许 MySQL 跟踪和记录优化器在解析和优化 SQL 语句时的详细信息。
执行要跟踪的 SQL 语句
接下来，运行想要分析的 SQL 语句。例如：
SELECT
USER_ID
FROM
t_user
WHERE
USERNAME = 'username_1000';查看 Optimizer Trace 的结果
在执行完 SQL 语句后，通过查询 information_schema.optimizer_trace 来查看跟踪结果：
SELECT * FROM information_schema.optimizer_trace;这个查询将返回一个 JSON 格式的结果，其中包含优化器选择执行计划的详细信息。
Optimizer Trace 的输出会包含以下三个主要阶段：
join_preparation（准备阶段）：
- 在这个阶段，优化器会解析 SQL 语句，识别表和列，并确定可能的访问路径。
- 主要任务包括列出所有的可能访问路径（如全表扫描、索引扫描等），并为每个表生成一组访问方法。
join_optimization（分析阶段）：
- 在分析阶段，优化器会评估不同的执行计划，计算每种执行计划的代价（cost），并选择代价最低的计划。
- 这个阶段会详细记录每种执行计划的评估过程，包括选择的索引、连接顺序以及各种优化策略（如子查询优化、条件下推等）。
join_execution（执行阶段）：
- 在执行阶段，实际的 SQL 语句执行过程会被记录下来，包括每个步骤的具体执行操作。
- 这个阶段会展示 SQL 语句如何在数据库中被执行，展示实际的执行路径。
查看代码
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t_user`.`USER_ID` AS `USER_ID` from `t_user` where (`t_user`.`USERNAME` = 'username_1000')"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`t_user`.`USERNAME` = 'username_1000')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal('username_1000', `t_user`.`USERNAME`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal('username_1000', `t_user`.`USERNAME`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal('username_1000', `t_user`.`USERNAME`)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`t_user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [
{
"table": "`t_user`",
"table_scan": {
"rows": 95996,
"cost": 216.25
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`t_user`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 95996,
"access_type": "scan",
"resulting_rows": 95996,
"cost": 9815.9,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 95996,
"cost_for_plan": 9815.9,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`t_user`.`USERNAME` = 'username_1000')",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`t_user`",
"attached": "(`t_user`.`USERNAME` = 'username_1000')"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`t_user`",
"original_table_condition": "(`t_user`.`USERNAME` = 'username_1000')",
"final_table_condition ": "(`t_user`.`USERNAME` = 'username_1000')"
}
]
},
{
"refine_plan": [
{
"table": "`t_user`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}三个阶段的详细分析
上面这个JSON输出展示了MySQL优化器在处理一个特定SQL查询时的决策过程。
下面是对每个部分的详细解析：
- join_preparation（准备阶段）:
- 这个阶段主要是对SQL查询进行解析和重写，生成内部表示形式。
select#: 表示查询的编号。expanded_query: 展示了原始查询的扩展形式，这里是一个简单的选择查询，从t_user表中选择USER_ID，其中USERNAME等于'username_1000'。
- join_optimization（优化阶段）:
- 这个阶段是优化器的核心部分，它评估不同的执行计划并选择最佳的一个。
condition_processing: 这个步骤处理WHERE子句中的条件。original_condition: 原始条件是(t_user.USERNAME= 'username_1000')。transformation: 应用了等价传播、常量传播和琐碎条件移除的转换，但这些转换没有改变条件的结果。
table_dependencies: 描述了查询中的表依赖关系。table: 查询涉及的表是t_user。row_may_be_null: 表明t_user表中的行不可能为NULL。
rows_estimation: 估算查询涉及的表中的行数和扫描成本。table_scan: 对t_user表进行全表扫描，估算的行数是95996，成本是216.25。
considered_execution_plans: 考虑到的执行计划。best_access_path: 最佳访问路径是全表扫描。access_type: 访问类型是scan，即全表扫描。cost: 估算的成本是9815.9，这是选择这个计划的总成本。chosen: 表示这个计划被选中。
attaching_conditions_to_tables: 将条件附加到表上。attached_conditions_summary: 条件(t_user.USERNAME= 'username_1000')被附加到了t_user表上。
finalizing_table_conditions: 最终确定的表条件。final_table_condition: 最终t_user表的条件没有变化。
- join_execution（执行阶段）:
- 这个阶段是执行被优化器选定的执行计划，并返回查询结果。
- 在这个例子中，执行阶段的步骤列表为空，这可能是因为执行计划非常简单，没有额外的步骤可以记录。
关闭 Optimizer Trace
完成调试后，可以通过设置 session 变量来禁用 Optimizer Trace。
SET optimizer_trace="enabled=off";通过 Optimizer Trace，我们可以详细跟踪 MySQL 优化器在解析和优化 SQL 语句时所做的每一步决策。这不仅帮助我们理解优化器的行为，还可以帮助我们诊断和优化性能问题。