探秘MySQL——排查与调优

一、问题排查一：SQL执行出错

使用工具： Navicat for MySQL

当执行了一条错误的SQL语句，会显示错误信息，包含了错误码、错误详情。

错误详情中会显示出错的原因和具体位置，方便我们进行位置的定位和排查。

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from admin' at line 1

一般来说，根据错误详情就能直接判断出错原因，但有时候，可能会出现一些我们从来没遇到过的问题，此时，借助互联网，对问题进行一个检索，并且还要甄别网络上提供的解决方式是否靠谱，可以多多尝试。

二、问题排查二：慢查询

使用工具： 慢查询日志

0.几个重要参数

# 是否开启慢查询日志，默认OFF，开启则设置为 ON
slow_query_log 

# 慢查询日志文件存储位置
slow_query_log_file 

# 是否把没有使用到索引的SQL记录到日志中，默认OFF，开启则设置为 ON
log_queries_not_using_indexes 

# 超过多少秒的查询才会记录到日志中，注意单位是秒
long_query_time 

1.配置慢查询日志

命令行配置（重启失效）

在数据库中执行以下语句：

SET GLOBAL slow_query_log_file = 'D:/setup/mysql8.0.27/log/slow.log';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL long_query_time = 0.001;

通过查询看看是否配置成功：

SELECT @@global.slow_query_log_file;

修改配置文件（永久生效）

【先留个坑在这】

2.查看慢查询日志

打开慢查询日志，查看里面的一条记录：

# Time: 2023-03-12T09:32:24.818300Z
# User@Host: root[root] @ localhost [::1]  Id:    11
# Query_time: 0.003975  Lock_time: 0.000116 Rows_sent: 393  Rows_examined: 786
SET timestamp=1678613544;
select * from text
order by application_no;

• 可以看到以下信息：
  查询用户User
  查询耗费时间Query_time
  锁等待时间Lock_time
  结果集行数Rows_sent
  累积扫描行数Rows_examined
  具体的SQL语句

这些信息可以协助我们排查慢查询问题。

3.问题排查1：Look_time耗时

Look_time耗时反映了事务的并发性能，如果慢查询日志中出现了很多这样的记录，说明是事务的并发性能出现了问题。

• 执行命令查询MySQL整体的锁状态：

show status like 'innodb_row_lock_%';

如果查询出来的值比较大，就意味着你当前MySQL服务器承载的并发压力过高，此时就急需进行系统的高并发优化。

4.问题排查2：索引

如果不是事务并发问题，那么很有可能是SQL本身有问题，比如可能是索引方面需要优化。

这个问题参考我的另一篇博客：这里

5.问题排查3：拆解复杂SQL

如果一条复杂的SQL出现效率问题，为了准确地定位问题所在，可以采取分解SQL子句的方式进行问题排查。

• 用explain检查每个SQL子句，初步定位问题；
• SQL子句逐条合并，每合并一次用explain检查一次。

使用explain分析SQL

在 MySQL 中，使用 EXPLAIN 语句分析 SQL 查询时，会返回一系列列，这些列用于描述 MySQL 执行查询的计划（如索引使用、表连接方式等）。以下是每一列的具体含义：

1. id

• 表示查询中每个操作的唯一标识符，用于区分不同的查询单元（如子查询、联合查询等）。
• 规则：
  • 相同 id：表示这些操作属于同一查询单元，按顺序执行。
  • 不同 id：id 越大，优先级越高，先执行（通常对应子查询）。
  • NULL：表示该操作是其他查询的结果集（如 UNION 的临时表）。

2. select_type

• 表示查询的类型，描述当前查询单元的性质。常见值：
  • SIMPLE：简单查询，无子查询或 UNION。
  • PRIMARY：主查询（包含子查询时，最外层的查询）。
  • SUBQUERY：子查询（不在 FROM 子句中）。
  • DERIVED：衍生表（FROM 子句中的子查询，MySQL 会将其作为临时表处理）。
  • UNION：UNION 中第二个及之后的查询。
  • UNION RESULT：UNION 的结果集（id 通常为 NULL）。

3. table

• 表示当前操作涉及的表名（或衍生表的别名，如 derived2）。

4. partitions

• 表示查询匹配的分区（仅对分区表有效）。非分区表显示 NULL。

5. type

• 表示表的访问类型（非常重要），反映 MySQL 如何查找表中的行，性能从优到差排序：

  • system：表中只有一行（如系统表），是 const 的特例。
  • const：通过主键或唯一索引查询，最多返回一行，速度极快。
  • eq_ref：多表连接时，被连接表通过主键或唯一索引匹配，每行只匹配一次（如 a.id = b.a_id 且 b.a_id 是唯一索引）。
  • ref：非唯一索引匹配，可能返回多行（如普通索引查询）。
  • ref_or_null：类似 ref，但包含 NULL 值的查询（如 WHERE col = 'x' OR col IS NULL）。
  • index_merge：使用多个索引的组合查询（如 WHERE a = 1 OR b = 2，且 a 和 b 分别有索引）。
  • range：索引范围查询（如 WHERE id BETWEEN 1 AND 10 或 id > 5）。
  • index：扫描整个索引树（比 ALL 快，因为索引文件通常比数据文件小）。
  • ALL：全表扫描（性能最差，应尽量避免）。

  优化目标：至少达到 range 级别，最好是 ref 或 const。

6. possible_keys

• 表示 MySQL 认为可能使用的索引（基于查询条件判断），但实际可能不使用。
• 若为 NULL，表示没有可使用的索引。

7. key

• 表示 MySQL 实际使用的索引（NULL 表示未使用索引）。
• 若 possible_keys 不为空但 key 为 NULL，可能是因为索引选择性低（如全表数据大部分符合条件，MySQL 认为全表扫描更快）。

8. key_len

• 表示实际使用的索引长度（字节数），用于判断索引的使用情况（如联合索引是否部分使用）。
• 长度越短，效率越高（相同条件下）。

9. ref

• 表示与索引匹配的列或常量（如 const 表示常量，table.col 表示其他表的列）。
• 用于说明 key 索引是如何被使用的。

10. rows

• 表示 MySQL 估计需要扫描的行数（非精确值），用于评估查询效率。
• 数值越小，查询效率越高。

11. filtered

• 表示符合查询条件的行占扫描行数的百分比（估计值）。
• 例如，rows=100 且 filtered=50，表示约 50 行符合条件。
• 该值与 rows 结合，可估算实际参与连接的行数（rows * filtered / 100）。

12. Extra

• 包含额外的执行计划信息（非常重要），常见值：
  • Using index：使用了覆盖索引（查询的列均可从索引中获取，无需回表查数据），性能优。
  • Using where：使用 WHERE 条件过滤行（但未使用索引）。
  • Using index condition：使用索引下推（ICP），在存储引擎层过滤部分数据，减少回表。
  • Using temporary：需要创建临时表存储结果（如 GROUP BY 非索引列），性能差。
  • Using filesort：需要额外排序（如 ORDER BY 非索引列），性能差（内存排序或磁盘排序）。
  • Using join buffer：多表连接时未使用索引，使用连接缓冲区，性能差。
  • Impossible WHERE：WHERE 条件永远为假（如 WHERE 1=0），无数据返回。
  • Distinct：查询使用 DISTINCT，找到第一个匹配行后停止搜索。

以下通过具体 SQL 示例和 EXPLAIN 输出，帮助理解各列的含义。假设我们有两张表：

• users（用户表）：id（主键）、name（普通索引）、age、city
• orders（订单表）：id（主键）、user_id（普通索引，关联 users.id）、amount、create_time

示例 1：简单查询（单表 + 索引）

EXPLAIN SELECT id, name FROM users WHERE name = '张三';

输出（简化）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	ref	idx_name	idx_name	767	const	1	100	Using index

解析：

• id=1：单一查询单元。
• select_type=SIMPLE：简单查询（无子查询/联合）。
• table=users：操作的表是 users。
• type=ref：使用非唯一索引（idx_name）匹配，可能返回多行（这里估算 1 行）。
• possible_keys=idx_name：MySQL 认为可能用 idx_name 索引。
• key=idx_name：实际使用了 idx_name 索引。
• key_len=767：name 是 VARCHAR 类型，索引长度为 767 字节（假设 utf8mb4 编码）。
• ref=const：索引匹配的是常量（'张三'）。
• rows=1：估算扫描 1 行。
• filtered=100：所有扫描行都符合条件。
• Extra=Using index：使用覆盖索引（查询的 id 和 name 都在索引中，无需回表查数据）。

示例 2：多表连接（带索引）

EXPLAIN SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.create_time > '2023-01-01';

输出（简化）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	o	range	idx_user_id, idx_create_time	idx_create_time	5	NULL	100	100	Using where; Using index
1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	4	test.o.user_id	1	100	Using index

解析：

• 两表 id 相同（1）：属于同一查询单元，按顺序执行（先 orders 后 users）。
• select_type=SIMPLE：无复杂查询结构。
• table=o（orders）：
  • type=range：使用 idx_create_time 索引做范围查询（create_time > '2023-01-01'）。
  • possible_keys 包含 idx_user_id 和 idx_create_time：MySQL 认为两个索引都可能用。
  • key=idx_create_time：实际选择了 idx_create_time（更适合范围条件）。
  • Extra=Using where; Using index：用索引过滤条件，且覆盖索引（假设 idx_create_time 包含 user_id 和 amount）。
• table=u（users）：
  • type=eq_ref：多表连接时，u.id 是主键，通过 o.user_id 匹配，每行只匹配一次。
  • ref=test.o.user_id：索引匹配的是 orders 表的 user_id 列。
  • Extra=Using index：users 表用主键索引覆盖查询（id 和 name 都在主键索引中）。

示例 3：全表扫描（性能差）

EXPLAIN SELECT * FROM users WHERE age > 30;

输出（简化）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	ALL	NULL	NULL	NULL	NULL	1000	30	Using where

解析：

• type=ALL：全表扫描（age 无索引）。
• possible_keys=NULL：无可用索引。
• key=NULL：未使用索引。
• rows=1000：估算扫描全表 1000 行。
• filtered=30：约 30% 的行符合 age > 30 条件。
• Extra=Using where：用 WHERE 条件过滤，但无索引支持，需全表扫描后过滤。

示例 4：临时表和文件排序（性能问题）

EXPLAIN SELECT city, COUNT(*) FROM users GROUP BY city;

输出（简化）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	ALL	NULL	NULL	NULL	NULL	1000	100	Using temporary; Using filesort

解析：

• type=ALL：city 无索引，全表扫描。
• Extra=Using temporary：GROUP BY 非索引列，需创建临时表存储中间结果。
• Extra=Using filesort：需要对临时表中的 city 排序（无索引支持，性能差）。

通过这些示例可以直观看到：type、key、Extra 是判断查询性能的核心列，全表扫描（ALL）、临时表（Using temporary）、文件排序（Using filesort）都是需要优化的场景。

参考

博客
慢查询
mysql配置文件