优化慢sql通常的几个步骤

最新推荐文章于 2024-07-18 16:29:57 发布

青春丨猪头丨少年

最新推荐文章于 2024-07-18 16:29:57 发布

阅读量427

点赞数 16

分类专栏： Mysql 文章标签： mybatis java spring

本文链接：https://blog.csdn.net/qq_40592590/article/details/139965882

版权

Mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

优化慢 SQL 是数据库性能调优的重要一环。以下是一些常见的步骤，可以帮助你系统地分析和优化慢 SQL：

步骤一：分析和识别慢 SQL

启用慢查询日志（MySQL等数据库）：
- 开启慢查询日志记录。
- 设置慢查询阈值，如超过1秒的查询被记录下来，以便后续分析。

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

查看慢查询日志：
- 通过慢查询日志找到耗时较长的 SQL 语句。
使用数据库自带的监控工具：
- 利用 MySQL 的 SHOW PROCESSLIST 和 EXPLAIN 语句。
- 利用 PostgreSQL 的 pg_stat_activity 视图。

步骤二：分析查询计划

使用 EXPLAIN 或 EXPLAIN ANALYZE 分析 SQL 查询计划，理解查询的执行步骤和瓶颈所在。

EXPLAIN SELECT * FROM your_table WHERE condition;

输出解释：

id：查询中执行的唯一标识符。
select_type：查询的类型。
table：表名。
type：连接类型，比如 ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（引用索引）、eq_ref（唯一索引扫描）、const（常量扫描）等，最好优化是优化到range及以上
possible_keys：查询中可能使用的索引。
key：实际使用的索引。
rows：需要扫描的行数。
Extra：附加信息，如 "Using where", "Using filesort", "Using temporary"。

步骤三：优化数据结构

索引优化：
- 确保常用查询字段上建立了合适的索引，特别是涉及 WHERE、JOIN、ORDER BY、GROUP BY 等条件的字段。
- 使用覆盖索引，减少回表操作。

CREATE INDEX idx_your_column ON your_table(your_column);

分析现有索引的使用情况：
- 删除冗余或不再使用的索引。
- 避免过多的索引，以减少数据库写操作的开销。
范式化与反范式化：
- 根据查询需求，适当进行范式化或反范式化。
- 视情况使用冗余字段或分区表。

步骤四：优化 SQL 语句

优化查询条件：
- 使用合理的过滤条件，减少扫描行数。
- 避免使用 %LIKE 模糊匹配前缀。

-- 使用B-Tree索引范围扫描
SELECT * FROM your_table WHERE column_name LIKE 'prefix%';

分解复杂查询：
- 将复杂的查询分解为多个简单查询。
- 使用子查询或临时表。
避免函数操作：
- 避免在 WHERE 子句中使用函数，以便索引生效。

-- 不推荐：使用函数对索引无效
SELECT * FROM your_table WHERE DATE(column_name) = '2023-01-01';

-- 推荐：优化条件使索引生效
SELECT * FROM your_table WHERE column_name >= '2023-01-01 00:00:00' AND column_name < '2023-01-02 00:00:00';

步骤五：合理利用缓存

查询缓存：
- 开启并优化查询缓存。
应用层缓存：
- 利用 Redis/Memcached 等分布式缓存，存储热门数据，减少数据库压力。

步骤六：分区和分表

分区表：
- 对于超大表，使用分区表，按时间、ID等字段分区。

CREATE TABLE your_table (
  id INT,
  value VARCHAR(100),
  created_at DATETIME
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023)
);