高效处理MySQL慢查询分析和性能优化

介绍

构建基础数据

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for sbtest1
-- ----------------------------
DROP TABLE IF EXISTS `sbtest1`;
CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_last_k` (`k`),
  KEY `idx_last_c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

使用Sysbench构建单表50万条数据；

在没有任何优化的情况下，单表查询耗时38.222s;

定位慢查询的原因

要开启慢查询日志（默认是关闭的），通过slow_query_log参数进行设置。在MySQL命令终端中执行以下命令：

-- 开启慢查询日志，默认情况下是关闭的
SET GLOBAL slow_query_log = ON;

-- 设置慢查询阈值，单位为秒，默认值为10秒。这里设置为1秒，表示查询耗时超过1秒将被记录到慢查询日志中
SET GLOBAL long_query_time = 1;

-- 查看慢查询日志的文件名和路径 
SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';

+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| slow_query_log_file | /data1/mysql_root/data/slow_query.log |
+---------------------+---------------------------------------+

检查慢查询的详细指标，可以看到下面 slow_query_log =ON，long_query_time =1 ，都是因为我们调整过的参数

+---------------------------------------------+---------------------------------------+
| Variable_name                               | Value                                 |
+---------------------------------------------+---------------------------------------+
| binlog_rows_query_log_events                | OFF                                   |
| cdb_query_memory_log_enabled                | OFF                                   |
| ft_query_expansion_limit                    | 20                                    |
| have_query_cache                            | YES                                   |
| innodb_backquery_enable                     | OFF                                   |
| innodb_backquery_history_limit              | 8000000                               |
| innodb_backquery_trackpoint_clean_interval  | 1                                     |
| innodb_backquery_trackpoint_create_interval | 1                                     |
| innodb_backquery_window                     | 900                                   |
| log_queries_not_using_indexes               | OFF                                   |
| log_throttle_queries_not_using_indexes      | 0                                     |
| long_query_time                             | 1.000000                              |
| query_alloc_block_size                      | 8192                                  |
| query_cache_limit                           | 1048576                               |
| query_cache_min_res_unit                    | 4096                                  |
| query_cache_size                            | 1048576                               |
| query_cache_type                            | OFF                                   |
| query_cache_wlock_invalidate                | OFF                                   |
| query_prealloc_size                         | 8192                                  |
| slow_query_log                              | ON                                    |
| slow_query_log_file                         | /data1/mysql_root/data/slow_query.log |
| txsql_opt_query_cache_for_high_perf_machine | OFF                                   |
+---------------------------------------------+---------------------------------------+

表格展示了上述关于MySQL数据库的一些配置变量及其对应的值以及所有变量的解释：

EXPLAIN 语句分析

使用where语句和like关键字进行简单的查询。

select * from sbtest1 WHERE k LIKE '%37%';

我们发现耗时大概需要1.515s，这种单表的查询效率是不能被允许的。

使用 explain 进行分析

我们来详细的分析一下查询执行计划的详细信息：

从给出的执行计划来看，这个查询对sbtest1表进行了全表扫描（ALL），没有使用任何索引，估计需要检查492867行数据，其中约11.11%的行满足WHERE子句的条件。这意味着查询性能不佳，我们可以考虑优化查询或添加合适的索引以提高性能。

我们先看一下执行计划列表中，各字段的含义：

id：查询的标识符，用于区分多个子查询或连接的执行计划。

select_type：查询类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。

table：查询涉及的表名。

partitions：查询涉及的分区，如果没有分区则为NULL。

type：连接类型，如ALL（全表扫描）、INDEX（索引扫描）、range（范围扫描）等。这里显示的是ALL，表示进行了全表扫描。

possible_keys：可能使用的索引列表，如果没有可能的索引则为NULL。

key：实际使用的索引，如果没有使用索引则为NULL。

key_len：使用的索引长度，如果没有使用索引则为NULL。

ref：与索引一起使用的列或常量，如果没有使用索引则为NULL。

rows：MySQL估计需要检查的行数。

filtered：MySQL估计满足WHERE子句条件的行数占总行数的百分比。

Extra：额外的执行计划信息，如Using where（使用了WHERE子句）、Using index（使用了覆盖索引）等。

以上的字段中，我们其实主要关注几个重点的性能指标即可。

EXPLAIN 重点关注指标

在使用 EXPLAIN 分析 MySQL 查询时，以下字段尤为重要：

select_type: 描述查询的类型，如 SIMPLE、PRIMARY 等，帮助理解查询结构。

type: 指定表的访问类型，如 ALL (全表扫描)，需尽量避免。

possible_keys: 可能使用的索引，表示优化器考虑的索引。

key: 实际使用的索引。

rows: 预计扫描的行数，越小越好。

Extra: 提供额外信息，如 Using where 表示使用了过滤条件。

指标 select_type

select_type字段在MySQL的EXPLAIN输出中表示查询的类型。

指标 type

type字段在MySQL的EXPLAIN输出中表示MySQL如何访问表中的行。

这个表格可以帮助你理解MySQL如何执行不同类型的查询，从而更好地进行SQL性能优化。通常，我们希望查询能够使用索引来避免全表扫描（ALL），因为索引扫描通常比全表扫描更快。

指标 possible_keys

possible_keys字段在MySQL的EXPLAIN输出中表示MySQL认为可能用于执行查询的索引。这个字段并不表示实际使用的索引，而是表示MySQL在查询优化阶段考虑过的索引选项。

当你在EXPLAIN输出中看到possible_keys字段时，它将显示一个逗号分隔的索引列表，这些索引是MySQL认为可能有助于查询优化的。如果这个字段为空，那么表示MySQL没有找到任何可能有助于查询的索引。

例如，如果possible_keys字段的值是idx_column1,idx_column2，这意味着MySQL认为在执行查询时可能会使用到idx_column1和idx_column2这两个索引中的一个或两个。

请记住，possible_keys只是一个提示，实际使用的索引由key字段表示。如果key字段为空，而possible_keys不为空，这可能意味着虽然MySQL考虑了某些索引，但最终决定不使用它们，可能是因为全表扫描比使用索引更快，或者因为查询条件不适合使用索引。

指标 key

key字段在MySQL的EXPLAIN输出中表示MySQL实际决定使用的索引。这个字段显示了在查询执行过程中实际被用来检索数据的索引名称。以下是key字段可能包含的内容，但请注意，它并不是一个枚举类型，而是根据实际查询和表结构动态确定的。

由于key字段反映的是实际使用的索引，因此它的值取决于具体的查询和表上的索引。以下是一些可能的索引类型，它们可以作为key字段的值：

key字段的值将是上述类型之一，或者是表上定义的具体索引名称。如果key字段为空，这意味着MySQL在执行查询时没有使用任何索引。

例如，如果key字段的值是idx_column1，这意味着MySQL在执行查询时使用了名为idx_column1的索引。

请注意，key字段的值是在查询执行时根据查询优化器的决策确定的，它可能随着查询的不同而变化。因此，理解key字段的含义和可能的值有助于分析查询性能，并在必要时进行优化。

指标 rows

rows字段在MySQL的EXPLAIN输出中表示MySQL估计为了找到所需的行而必须检查的行数。

rows字段的值是一个估计值，MySQL会根据表的统计信息和查询条件来计算这个数值。这个估计值是基于当前数据库状态的一个近似值，实际的行数可能会有所不同。

例如，如果rows字段的值是1000，这意味着MySQL估计在执行查询时可能需要检查大约1000行数据。

请注意，rows字段的值可能会受到以下因素的影响：

• 表的大小和统计信息的准确性。
• 查询条件的复杂性。
• 索引的使用情况。
• 数据库的配置和优化设置。

通过观察rows字段的值，你可以对查询的性能有一个基本的了解。如果这个数值非常大，那么可能需要考虑优化查询，例如通过添加索引、改进查询条件或者重构查询逻辑来减少需要检查的行数。

指标 Extra

Extra字段在MySQL的EXPLAIN输出中提供了关于查询执行的额外信息，这些信息有助于理解查询是如何执行的，以及是否存在可以优化的地方。以下是Extra字段可能包含的一些值及其解释，以表格形式呈现：

请注意，Extra字段的值可能会随着MySQL版本的不同而有所变化，而且某些值可能不会在所有情况下出现。理解这些值有助于识别查询的潜在性能问题，并进行相应的优化。

查询优化

了解完上诉的重点指标后，我们考虑一下，如何对当前的查询进行优化。

增加索引

给字段增加索引的 SQL 语句示例如下：

CREATE INDEX index_name ON table_name(column_name);

示例

假设你有一个名为 employees 的表，并且想为 last_name 字段增加索引，可以使用以下语句：

CREATE INDEX idx_last_name ON employees(last_name);

这会在 employees 表的 last_name 列上创建一个名为 idx_last_name 的索引。这个索引可以加快基于 last_name 列的查询速度。

索引类型

在 MySQL 中，常见的索引类型包括：

普通索引 (Normal Index): 默认的索引类型，用于加速查询。

CREATE INDEX idx_name ON table_name(column_name);

唯一索引 (Unique Index): 确保列中的所有值都是唯一的。

CREATE UNIQUE INDEX idx_name ON table_name(column_name);

主键索引 (Primary Key Index): 特殊的唯一索引，用于标识表中的每一行。

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

全文索引 (Fulltext Index): 用于全文搜索。

CREATE FULLTEXT INDEX idx_name ON table_name(column_name);

组合索引 (Composite Index): 基于多个列创建的索引。

CREATE INDEX idx_name ON table_name(column1, column2);

创建索引

先测试一下以下三条查询的执行计划

explain select * from sbtest1 WHERE k LIKE '%37%';
explain select * from sbtest1 WHERE k LIKE '%37';
explain select * from sbtest1 WHERE k LIKE '37%';

我们给字段创建一个普通索引。

CREATE INDEX idx_last_k ON sbtest1(k);

再次执行3条 like 查询语句

我们会发现，只有“%”的标记在后面时候，索引是的生效的，从这个执行计划可以看出，查询对sbtest1表进行了全表扫描，并且使用了WHERE子句进行过滤。但是没有使用索引。

我们先确认一下索引是否生效。

SHOW INDEXES FROM sbtest1;

从这个表格可以看出，sbtest1表有两个索引：一个是主键索引PRIMARY，包含列id；另一个是索引idx_last_k，包含列k。这两个索引都是B树索引，且都可见。

如果需要优化查询性能，可以考虑以下方法：

1. 使用索引：确保查询条件中涉及的列有适当的索引。例如，如果经常根据k列的值进行查询，那么idx_last_k索引是有用的。
2. 优化查询条件：尽量使用精确的查询条件，避免使用模糊查询或范围查询，因为这些查询可能无法充分利用索引。
3. 考虑创建复合索引：如果经常同时根据多个列进行查询，可以考虑创建一个包含这些列的复合索引。
4. 定期维护索引：定期检查和维护索引，确保它们仍然有效和高效。

我们还一种查询方式，不是用 * 而是查询具体的字段

explain select id, k from sbtest1 where k like '37%';

从这个执行计划可以看出，查询对sbtest1表进行了索引扫描，并且使用了WHERE子句进行过滤。由于使用了索引，这可能会导致查询性能较好。但是，如果表中的数据量非常大，即使使用了索引，全表扫描也可能比使用索引更快。

我们再给另一个字段c也增加索引

执行计划计划

explain select c from sbtest1 where c like '37%';
explain select c from sbtest1 where c like '%37%';

结合C字段和K字段的执行计划结果，我们可以观察到，在为不同数据类型添加索引条件后，查询性能表现出显著的差异。那么，我们应该如何针对不同情况来合理地增加索引条件呢？

合理地增加索引条件

在MySQL数据库中，索引的作用是加速数据检索操作，减少查询时间。然而，索引的设置需要考虑多种因素，以避免不必要的开销。

一、适合加索引的字段类型

1. 经常用于查询条件的字段

WHERE 子句中的字段：WHERE 子句是最常见的查询条件。如果某个字段经常出现在 WHERE 子句中，并且数据量较大时，通过对该字段加索引，可以避免全表扫描，大幅提升查询效率。

JOIN 条件中的字段：JOIN 操作用于将多张表的数据结合在一起。如果两个表之间经常通过某个字段进行关联（如 JOIN 操作中的连接条件），那么为这些字段加索引可以加速多表关联的查询。

2. 经常用于排序的字段

ORDER BY 子句中的字段：如果查询结果需要经常按某个字段排序，那么为该字段加索引可以显著提高排序操作的速度。索引将使数据库能够直接读取已经排序好的数据，而不必在查询后进行排序。

3. 唯一性要求的字段

主键字段：主键（PRIMARY KEY）字段本质上是一种唯一索引。每个表只能有一个主键，并且主键列中的值必须唯一且不能为空。MySQL会自动为主键列创建索引。

唯一索引字段：对于需要唯一性约束的字段，如用户名、电子邮件地址等，可以使用唯一索引（UNIQUE INDEX）。这样不仅保证了数据的唯一性，还能提高查询效率。

4. 经常用于聚合操作的字段

GROUP BY 子句中的字段：在 GROUP BY 子句中使用的字段经常用于对数据进行分组。如果该字段加上索引，可以减少分组时的计算量，加快查询速度。

5. 选择性高的字段

选择性 指的是字段中唯一值的数量与总记录数的比率。选择性高的字段通常是那些有许多不同值的字段，例如身份证号码、产品编号等。为这些字段加索引可以显著提高查询效率，因为索引可以有效减少需要检索的记录数量。

6. 字符串前缀索引

对于较长的字符串字段，可以使用前缀索引（只索引字段的前几个字符）。这在搜索特定前缀时非常有用，既节省了存储空间，也提高了查询速度。

二、适合加索引的场景

1. 大数据量表

当表的数据量非常大时，查询操作可能会非常缓慢。此时，合理设计索引结构可以显著减少查询的时间成本。例如，电商平台的订单表通常包含上百万甚至上亿条记录，在这种场景下，对订单ID、用户ID等常用查询字段进行索引优化是非常有必要的。

2. 复杂查询

当查询涉及多个条件、多个表的关联、排序或分组时，索引能够显著提升查询的效率。特别是在数据仓库或数据分析系统中，复杂的SQL查询非常普遍，此时索引的作用尤为重要。

3. 频繁查询

如果某些查询在应用中被频繁执行，那么对相关字段加索引可以大幅提升系统的整体性能。比如，在社交媒体应用中，对用户状态、好友列表、消息记录等信息的查询是非常频繁的，为这些字段加索引可以显著提高响应速度。

4. 唯一性验证

在需要确保字段值唯一的场景中，索引不仅可以加速查询，还能确保数据的完整性。例如，电子商务网站中，每个商品SKU（Stock Keeping Unit，库存单位）都需要是唯一的，为其加上唯一索引可以同时满足这两个需求。

三、不适合加索引的场景

1. 小表

如果表中的数据量很小（比如仅有几十或几百条记录），那么加索引的作用并不明显。因为全表扫描的代价很低，索引的维护开销可能反而会成为性能的瓶颈。

2. 频繁更新的字段

对更新非常频繁的字段加索引可能会降低写入性能。每次更新该字段时，索引也需要同步更新，这会带来额外的开销，特别是在数据写入操作频繁的场景中。

3. 低选择性的字段

低选择性字段（例如布尔类型、性别等）加索引的效果有限。这类字段的值范围通常很小，使用索引无法显著减少数据扫描的范围，因此在大多数情况下，这些字段不适合加索引。

索引的使用需要综合考虑查询频率、数据量、字段选择性等因素。合理使用索引可以显著提高查询性能，但滥用索引则可能适得其反，增加系统的负担。在实际应用中，应根据具体的业务需求和性能测试结果来决定是否添加索引以及如何设计索引结构。

构建高性能索引策略

构建高性能的索引策略需要综合考虑数据库的查询模式、数据特性、应用场景以及索引的维护成本。

一、理解查询模式和工作负载

1.分析查询频率和类型

查询频率：识别系统中执行频率最高的查询语句，对这些查询所涉及的字段进行索引优化。

查询类型：了解查询的类型，如简单查询、复杂关联查询、聚合查询、排序查询等，根据不同类型的查询来定制索引策略。

2. 考虑写操作的开销

频繁更新：对于经常更新的表或字段，索引会增加写操作的成本，因此在设计时需要权衡查询加速与更新开销之间的关系。

批量插入和删除：大批量的插入或删除操作会导致索引频繁重建，影响性能。在这种情况下，可以考虑在批量操作完成后再重建索引。

二、选择合适的索引类型

1. 主键索引 (Primary Key Index)

唯一性和高效性：主键索引不仅唯一，而且通常是聚簇索引（Clustered Index），这意味着数据物理上按主键排序。每张表必须有一个主键索引，它的选择对整个表的性能影响最大。

2. 唯一索引 (Unique Index)

数据完整性保障：用于确保字段中的数据唯一，同时提高查询性能。适用于如用户名、邮箱等需要唯一的字段。

3. 普通索引 (Non-Unique Index)

加速查询：用于加速查询的标准索引，适用于需要频繁查询但不需要唯一性的字段。

4. 复合索引 (Composite Index)

多字段查询优化：当查询经常涉及多个字段时，可以创建复合索引。复合索引将多个字段组合在一起，按照指定顺序进行索引，能够有效提高多条件查询的性能。

CREATE INDEX idx_table ON table(id, title);

这个复合索引适用于同时查询 id 和 title 的情况。

5. 前缀索引 (Prefix Index)

大字段优化：对于长度较长的字符串字段，如 URL、电子邮件地址等，使用前缀索引可以在保证查询效率的同时减少索引大小。

CREATE INDEX idx_url_prefix ON web_pages(url(20));

为 url 字段的前20个字符创建前缀索引，适用于按URL前缀进行查询的场景。

6. 覆盖索引 (Covering Index)

减少查询回表：如果索引能够覆盖查询所需的所有字段，则查询可以直接从索引中获取数据，而无需回到表中读取。这种索引叫做覆盖索引。

CREATE INDEX idx_covering ON orders(order_id, customer_id, order_date);

对于只查询 order_id、customer_id 和 order_date 的语句，这个索引能够直接返回结果，避免回表，提高查询效率。

7. 全文索引 (Full-Text Index)

全文检索：适用于文本搜索，如博客内容、商品描述等。MySQL 的全文索引能够加速对文本字段的关键词搜索。

CREATE FULLTEXT INDEX idx_content ON articles(content);

为 content 字段创建全文索引，以优化文本内容的搜索性能。

三、索引设计的原则与最佳实践

1. 索引列的选择

高选择性字段优先：选择性高的字段能显著减少需要扫描的行数，因此应优先为高选择性字段加索引。

频繁使用的字段：经常用于 WHERE、JOIN、ORDER BY、GROUP BY 等子句的字段应优先考虑加索引。

避免为低选择性字段加索引：如性别、布尔值等字段，这类字段的索引往往效果不佳。

2. 合理使用复合索引

顺序重要性：在创建复合索引时，字段的顺序至关重要。通常应将选择性最高的字段放在最前面，以最大限度地提高查询效率。

覆盖多个查询场景：设计复合索引时，尽量考虑覆盖多个查询场景，以减少单独创建索引的数量。

3. 控制索引的数量和大小

避免过多索引：虽然索引可以提高查询性能，但每个索引都会增加写操作的开销。因此，需要在查询加速和写性能之间找到平衡。

定期清理不必要的索引：随着业务的发展，一些索引可能会变得不再必要或效果不佳，应定期审查并移除这些索引。

4. 使用索引分析工具

EXPLAIN 分析查询：使用 EXPLAIN 语句分析查询的执行计划，了解哪些索引在查询中被使用，以及是否有索引扫描、回表等操作，以此优化索引策略。

EXPLAIN SELECT * FROM employees WHERE department_id = 3;

通过 EXPLAIN 查看查询执行计划，判断索引使用情况。

慢查询日志：开启 MySQL 的慢查询日志，定期分析哪些查询执行时间长，从而识别出需要优化的查询和索引。

四、动态调整与索引维护

1. 动态调整索引策略

监控与调优：根据系统负载和查询模式的变化，动态调整索引策略。可以通过监控查询性能、分析查询日志、使用性能分析工具等手段，持续优化索引结构。

2. 定期重建索引

重建索引：在数据频繁更新的情况下，索引的碎片化会导致查询性能下降，定期重建索引可以改善这一问题。

ALTER TABLE employees DROP INDEX idx_department_id, ADD INDEX idx_department_id(department_id);

通过删除和重新创建索引来优化性能。

五、避免常见的索引陷阱

1. 索引失效问题

隐式转换：查询中如果字段类型不一致，MySQL 可能会对字段进行隐式转换，导致索引失效。确保查询条件中的数据类型与字段类型匹配，以避免这种情况。

SELECT * FROM users WHERE phone_number = 1234567890;  -- 可能导致索引失效
SELECT * FROM users WHERE phone_number = '1234567890'; -- 确保数据类型匹配

使用函数操作：在 WHERE 子句中对索引字段使用函数（如 UPPER()、LOWER() 等）会导致索引失效，应避免在索引字段上使用这些操作。

SELECT * FROM users WHERE UPPER(username) = 'JOHN';  -- 索引失效

2. 不当的查询优化

索引覆盖查询：在设计查询时，尽量利用索引覆盖查询，以避免不必要的回表操作。可以通过调整查询字段来实现这一点。

SELECT order_id, customer_id FROM orders WHERE order_date = '2024-08-01';

此查询可以使用覆盖索引，避免回表。

SQL查询规范标准

遵循这些优化后的规范和标准，可以提高SQL查询的性能和效率，减少不必要的性能开销。

1. 避免使用SELECT *

• 原因：使用SELECT *可能导致不必要的数据传输和回表查询，降低性能。
• 建议：明确指定需要的字段，避免全表扫描和回表查询。

2. JOIN语句中的SELECT *

• 原因：可能导致全表扫描，尤其是在大宽表中。
• 建议：在JOIN操作中明确指定字段，减少不必要的数据传输。

3. 无WHERE条件的SELECT * FROM t_name

• 原因：会导致全表扫描，降低查询性能。
• 建议：始终为查询添加适当的过滤条件，避免全表扫描。

4. MySQL中的TEXT类型字段存储

• 原因：TEXT类型字段读取效率低，可能影响其他字段的存取效率。
• 建议：避免在频繁查询的表中使用TEXT类型字段，或将TEXT字段拆分到单独的表中。

5. 在取出字段上使用相关函数

• 原因：某些函数（如NOW(), RAND(), SYSDATE()）可能导致不确定的结果或性能问题。
• 建议：避免在查询中使用这些函数，特别是在WHERE子句中。

6. 分页查询语句的排序条件

• 原因：不带排序条件的分页查询可能导致乱序。
• 建议：始终为分页查询添加排序条件，确保结果的有序性。

7. 使用IN()/UNION替换OR

• 原因：IN()和UNION通常比OR更高效。
• 建议：使用IN()或UNION替换OR，并注意IN子句中的元素数量，避免过多元素导致性能下降。

8. 避免使用%前缀进行模糊前缀查询

• 原因：可能导致全表扫描，降低查询性能。
• 建议：考虑使用全文索引或其他更高效的搜索方法。

总结

构建高性能的索引策略需要综合考虑数据特点、查询模式和系统负载。通过合理选择索引类型、设计复合索引、动态调整索引结构，并避免常见的索引陷阱，可以显著提升数据库的查询性能。持续监控和优化索引策略也是确保系统在不同负载下保持高效的关键。