本文具体先容MySQL的慢查询相干概念，分析步调及其优化方案等。
什么是慢查询日志 ？
慢查询日志是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;
- 分库分表的注意事项：
- 只管在当前架构下优化数据库性能，比方升级硬件、迁移汗青数据。
- 分片键的选择要能有用分散数据，同时能支持大部门查询需求。
- 使用分布式中央件（如 ShardingSphere、MyCAT）来管理分库分表后的复杂性。
慢查询日志的实用场景
- 数据库性能调优
- 排查体系瓶颈
- 通过 log_queries_not_using_indexes 找出未使用索引的查询，优化数据访问路径。
- 数据模子优化
- 分析慢查询日志，可以评估表操持、字段范例是否公道。
慢查询日志的优缺点
- 长处：
- 资助辨认性能瓶颈。
- 提供查询优化的方向。
- 支持将日志存储为表，便于后续分析。
- 缺点：
- 开启后大概对性能产生肯定影响，尤其是高并发场景。
- 日志文件大概过大，须要定期整理。
总结
慢查询日志是性能调优的紧张工具，通过公道的日志设置和日志分析，可以有用发现并优化SQL查询性能标题。然而，在高并发环境下，应根据需求公道开启并定期整理日志，克制对数据库性能造成额外负担。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
发表于 2025-11-16 00:20:52