大家好，这里是编程Cookbook。本文详细介绍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 WHEREid > 1000000ORDERBYidASCLIMIT10;通过
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;分库分表的注意事项：
尽量在当前架构下优化数据库性能，例如升级硬件、迁移历史数据。 分片键的选择要能有效分散数据，同时能支持大部分查询需求。 使用分布式中间件（如 ShardingSphere、MyCAT）来管理分库分表后的复杂性。
慢查询日志的适用场景
数据库性能调优 找出执行较慢的查询，优化索引设计或SQL语句。 排查系统瓶颈 通过 log_queries_not_using_indexes
找出未使用索引的查询，优化数据访问路径。数据模型优化 分析慢查询日志，可以评估表设计、字段类型是否合理。
慢查询日志的优缺点
优点：
帮助识别性能瓶颈。 提供查询优化的方向。 支持将日志存储为表，便于后续分析。 缺点：
开启后可能对性能产生一定影响，尤其是高并发场景。 日志文件可能过大，需要定期清理。
总结
慢查询日志是性能调优的重要工具，通过合理的日志配置和日志分析，可以有效发现并优化SQL查询性能问题。然而，在高并发环境下，应根据需求合理开启并定期清理日志，避免对数据库性能造成额外负担。
