MySQL数据库——常见慢查询优化方式
本文详细介绍MySQL的慢查询相关概念，分析步骤及其优化方案等。
文章目录
- 什么是慢查询日志？
- 慢查询日志的相关参数
- 如何启用慢查询日志？
- 方式一：修改配置文件
- 方式二：通过命令动态启用
- 分析慢查询日志
- 方式一：直接查看日志文件
- 方式二：使用`EXPLAIN`分析查询
- 常见的慢查询优化
- 1. 数据类型优化
- 2. 索引优化
- 3. SQL 查询优化
- 4. 分库分表
- 慢查询日志的适用场景
- 慢查询日志的优缺点
- 总结
什么是慢查询日志？
慢查询日志是MySQL提供的一种日志记录机制，用于记录执行时间超过指定阈值（long_query_time）的SQL语句。通过慢查询日志，可以识别和优化性能较差的SQL查询，是数据库性能调优的重要工具。
- 关键点：
- 默认阈值：
long_query_time默认值为 10秒，表示运行时间超过10秒的SQL会被记录。 - 默认状态：MySQL 默认未开启慢查询日志，需要手动启用。
- 日志存储方式：支持存储为文件或表。
- 默认阈值：
慢查询日志的相关参数
MySQL慢查询日志的核心参数及其含义如下：
-
启用和路径配置：
slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。slow-query-log-file：日志文件路径和名称（MySQL 5.6及以上版本）。log-slow-queries：旧版（MySQL 5.6以下）的日志存储路径参数。
-
时间阈值：
long_query_time：慢查询的时间阈值，单位是秒。运行时间超过该阈值的查询将被记录到慢查询日志中。
-
其他参数：
log_queries_not_using_indexes：未使用索引的查询也会记录到慢查询日志中，帮助识别潜在的索引问题（可选）。log_output：定义日志的存储方式：'FILE'：将日志写入文件（默认）。'TABLE'：将日志记录到mysql.slow_log表中。'FILE,TABLE'：同时使用文件和表存储。
如何启用慢查询日志？
方式一：修改配置文件
- 打开 MySQL 配置文件（
my.cnf或my.ini）。 - 添加以下配置：
slow_query_log = 1 slow_query_log_file = /path/to/mysql-slow.log long_query_time = 2 log_queries_not_using_indexes = 1 log_output = 'FILE' - 重启 MySQL 服务以生效。
方式二：通过命令动态启用
使用 MySQL 提供的全局变量来开启慢查询日志：
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL log_output = 'FILE';
注意：动态配置的参数在重启后失效，需将参数写入配置文件以持久化。
分析慢查询日志
方式一：直接查看日志文件
慢查询日志文件以文本格式存储，可以使用 cat、tail 或日志分析工具查看。
方式二：使用EXPLAIN分析查询
EXPLAIN 命令用于模拟优化器的查询执行计划，帮助分析SQL语句的性能问题。
例如：
EXPLAIN SELECT * FROM res_user ORDER BY modifiedtime LIMIT 0,1000;
-
EXPLAIN列说明：
table：查询涉及的表。type：访问类型，从高到低依次为：const、eq_ref、ref、range、index、ALL。rows：预计扫描的行数。key：使用的索引。Extra：补充信息，比如是否使用了临时表或文件排序。
-
type的类型和效率：ALL：全表扫描，效率最低。index：全索引扫描。range：索引范围扫描。ref：非唯一索引扫描或唯一索引前缀扫描。eq_ref：唯一索引扫描，效率较高。const/system：常量查询，效率最高。
常见的慢查询优化
优化 MySQL 的慢查询是提升数据库性能的关键环节。以下是常见的慢查询优化方法，按步骤和具体技术进行详细介绍：
1. 数据类型优化
- 使用占用空间更小的字段类型：
- 优先使用
TINYINT、SMALLINT，而非INT。 - 固定长度的字符串使用
CHAR，而非VARCHAR。 - 使用
TIMESTAMP而非DATETIME，减少存储空间。TIMESTAMP占用 4 字节，而DATETIME占用 8 字节。TIMESTAMP的时间范围为 1970-2038，而DATETIME为 1000-9999，TIMESTAMP更节省空间并且在 UTC 时间格式下自动处理时区转换。
- 精度要求较高时使用
DECIMAL或BIGINT：- 如果需要精确的数字存储，特别是涉及到小数的场景，使用
DECIMAL类型而非FLOAT或DOUBLE。例如，对于要求两位小数的金额字段，可以将值乘以 100 保存为BIGINT。
- 如果需要精确的数字存储，特别是涉及到小数的场景，使用
- 优先使用
2. 索引优化
索引是优化慢查询最常见和高效的方法。以下是索引优化的几种方式：
-
创建适合的索引：
- 对
WHERE子句中频繁使用的列建立索引。 - 对
GROUP BY、ORDER BY和JOIN操作中涉及的列建立索引。
CREATE INDEX idx_column_name ON table_name(column_name); - 对
-
联合索引：
如果查询中涉及多个条件，可以创建联合索引。注意最左前缀原则。CREATE INDEX idx_multi_columns ON table_name(column1, column2); -
覆盖索引：
通过索引覆盖查询的所有字段，减少回表操作。SELECT col1, col2 FROM table_name WHERE col1 = 1; -
避免冗余索引：
合理设计索引，避免不必要的重复索引。例如(a, b)的索引已经可以覆盖a的查询，没必要再单独为a创建索引。
3. SQL 查询优化
优化 SQL 查询语句本身是提高性能的重要手段。
-
避免
SELECT *：
只查询必要的字段，减少数据传输量。SELECT col1, col2 FROM table_name WHERE condition; -
避免子查询，改用 JOIN：
子查询在某些情况下会导致性能下降，特别是嵌套子查询。-- 子查询 SELECT * FROM table_name WHERE col1 IN (SELECT col1 FROM other_table); -- 改为 JOIN SELECT t1.* FROM table_name t1 JOIN other_table t2 ON t1.col1 = t2.col1; -
合理使用 LIMIT：
对分页查询，尽量使用LIMIT + 游标（id > n）的方法，减少使用LIMIT + OFFSET的方式，尤其是当 偏移量（OFFSET）非常大时。LIMIT + OFFSET的性能瓶颈：- 数据库需要从头开始扫描，跳过
OFFSET指定的记录。 - 偏移量越大，查询耗时越长。
- 即使只返回少量数据，数据库仍需加载并跳过大量无关记录。
示例：-- 查询第 1000000 页，每页 10 条记录 SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 10;- 数据库会先找到前 1000000 条记录，跳过它们，然后再返回第 1000000 条后的 10 条记录。
- 随着 OFFSET 增大，性能会急剧下降。
优化方案：使用
LIMIT + 游标（id > n）：-
通过游标条件
id > n，可以直接定位到需要的记录，避免跳过大量无关记录。示例：
假设表orders中的主键是id，查询从第 1000000 条开始的 10 条记录：-- 优化后的查询 SELECT * FROM orders WHERE id > 1000000 ORDER BY id ASC LIMIT 10;- 通过
id > 1000000确定游标位置，直接从符合条件的记录开始扫描。 - 查询性能与
OFFSET无关，扫描范围大大缩小。
- 通过
- 数据库需要从头开始扫描，跳过
-
避免函数操作：
不要在WHERE子句中对列使用函数，会导致索引失效。SELECT * FROM table_name WHERE DATE(column_name) = '2023-01-01'; -- 慢 SELECT * FROM table_name WHERE column_name >= '2023-01-01' AND column_name < '2023-01-02'; -- 快 -
减少
OR的使用：
OR通常会导致全表扫描，可以用UNION或IN代替。-- 原始查询：使用 OR，可能导致全表扫描 SELECT * FROM table_name WHERE col1 = 1 OR col1 = 2; -- 优化方式 1：使用 IN，能够高效利用单列索引 SELECT * FROM table_name WHERE col1 IN (1, 2); -- 优化方式 2：使用 UNION，将查询拆分成两个独立的部分 (SELECT * FROM table_name WHERE col1 = 1) UNION (SELECT * FROM table_name WHERE col1 = 2); -
优化
LIKE查询：
LIKE查询如果以%开头会导致全表扫描，因为无法使用索引。可以优化为前缀匹配或使用全文索引。示例：
-- 非优化：前缀为 %，无法使用索引 SELECT * FROM table_name WHERE col1 LIKE '%keyword%'; -- 优化：前缀匹配，能够使用索引 SELECT * FROM table_name WHERE col1 LIKE 'keyword%'; -- 使用全文索引（适用于大文本字段） ALTER TABLE table_name ADD FULLTEXT(col1); SELECT * FROM table_name WHERE MATCH(col1) AGAINST('keyword');
4. 分库分表
分库分表是一种应对大规模数据存储和高并发访问的解决方案。
-
何时分库分表：
根据《阿里巴巴 Java 开发手册》的建议，单表行数超过 500 万行或单表容量超过 2GB 时，考虑分库分表。 -
分库分表的好处：
- 提升查询效率：通过拆分单表或数据库，将数据分散到多个存储节点上，减少单节点的存储和查询压力。
- 提升并发性能：多个节点可以同时处理查询或写入操作，分担压力。
- 减少锁冲突：分库分表后，每个表的并发操作减少，减少锁等待和冲突。
-
分库分表的方式：
-
垂直拆分（按功能分库）：
按业务模块划分数据库，将不同的业务表存储在不同的库中。库1：用户数据（users, profiles） 库2：订单数据（orders, order_items） 库3：商品数据（products, categories） -
水平拆分（按数据分片分库分表）：
将单表数据按照一定规则（如用户 ID、订单 ID 等）拆分到多个表或库中。- 范围分片：根据 ID 范围分配数据。
orders_0: ID 1-10000 orders_1: ID 10001-20000 - 哈希分片：对分片键取模，将数据分散到多个库或表中。
-- 按订单 ID 取模分表 SELECT * FROM orders_hash WHERE MOD(order_id, 4) = 0;
- 范围分片：根据 ID 范围分配数据。
-
-
分库分表的注意事项：
- 尽量在当前架构下优化数据库性能，例如升级硬件、迁移历史数据。
- 分片键的选择要能有效分散数据，同时能支持大部分查询需求。
- 使用分布式中间件（如 ShardingSphere、MyCAT）来管理分库分表后的复杂性。
慢查询日志的适用场景
- 数据库性能调优
- 找出执行较慢的查询，优化索引设计或SQL语句。
- 排查系统瓶颈
- 通过
log_queries_not_using_indexes找出未使用索引的查询，优化数据访问路径。
- 通过
- 数据模型优化
- 分析慢查询日志，可以评估表设计、字段类型是否合理。
慢查询日志的优缺点
-
优点：
- 帮助识别性能瓶颈。
- 提供查询优化的方向。
- 支持将日志存储为表，便于后续分析。
-
缺点：
- 开启后可能对性能产生一定影响，尤其是高并发场景。
- 日志文件可能过大，需要定期清理。
总结
慢查询日志是性能调优的重要工具，通过合理的日志配置和日志分析，可以有效发现并优化SQL查询性能问题。然而，在高并发环境下，应根据需求合理开启并定期清理日志，避免对数据库性能造成额外负担。