如何定位并解决慢查询？
1. 开启/检查慢日志
- 看一下是否开启慢日志
|
1 2 3 |
SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time'; SHOW VARIABLES LIKE 'slow_query_log_file'; |
- 如果未开启，临时开启（生产环境建议永久配置）：
|
1 2 |
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; |
2. 分析日志
mysqldumpslow（MySQL 自带）
|
1 2 3 4 |
# 按执行次数排序前10条 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 按总耗时排序前10条 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log |
3. 用explain分析执行计划
在SQL前面加explain
|
1 2 3 4 |
EXPLAIN SELECT id, order_no FROM orders WHERE user_id = 100 AND create_time >= '2024-01-01' ORDER BY create_time DESC; |
- 重点查看四个字段
| 字段 | 看什么 |
|---|---|
| type | 是否出现 ALL（全表扫描） |
| rows | 扫描行数是否过大 |
| key | 是否使用到了正确索引 |
| Extra | 是否出现 Using filesort 或 Using temporary |
SQL优化？
一、基础优化
1. 避免select *
|
1 2 3 4 |
-- ? 不推荐 SELECT * FROM users; -- ? 推荐 SELECT id, name, email FROM users; |
2. 使用合适的where条件
- 尽量在where中使用索引字段
- 避免对字段进行函数操作或类型转换(导致索引失效)
|
1 2 3 4 5 6 7 8 |
-- ? 索引失效 SELECT * FROM orders WHERE YEAR(create_time) = 2024; -- ? 使用范围查询，可走索引 SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'; |
3. 合理使用索引
- 对经常用于where、join、order by、group by的列建立索引
- 比卖你过度索引(影响写入性能)
- 考虑使用复合索引（最左前缀原则）
4. 避免全表扫描
通过explain检查是否使用了索引
|
1 |
EXPLAIN SELECT * FROM products WHERE category_id = 10; |
二、JOIN优化（多表查询）
1. 大表驱动小表
- 在MySQL中，通常将小结果姐放在left，大表在right
2. 确保JOIN字段都有索引
- 两个表关联字段都应该有索引
3. 避免多层嵌套JOIN
- 复杂JOIN可拆分为多个简单查询
三、子查询 vsJOIN
子查询在某些数据库中效率较低，可以尝试改成JOIN
|
1 2 3 4 5 6 7 8 9 |
-- ? 子查询（可能低效） SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100); -- ? 改写为 JOIN SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100; |
分页优化
- 深分页（如LIMIT 100000,20）性能查，因为要跳过大量的数据
- 优化方案：
- 使用游标分页（基于上一页最后一条记录的ID或时间）：
- 优化方案：
|
1 2 3 4 5 6 |
? SELECT * FROM messages WHERE id > 100000 ORDER BY id LIMIT 20; ? |
如何创建、使用索引？
索引介绍
| 索引类型 | 说明 |
|---|---|
| 主键索引 | 聚簇索引，数据按主键物理存储，每一张表只能一个 |
| 唯一索引 | 不允许出现重复值 |
| 普通索引 | 最基本的索引，允许重复和null |
| 全文索引 | 用于文本搜索 |
| 前缀索引 | 对字符串类的前N个字段创建索引，节省空间 |
| 覆盖索引 | 非独立类型，查询字段全部包含在索引中，无需回表 |
一、创建索引
1. 创建普通索引
|
1 2 3 4 |
-- 方法1：CREATE INDEX（推荐用于已有表） CREATE INDEX index_name ON table_name (column_name); -- 示例：在 users 表的 email 字段上创建索引 CREATE INDEX idx_email ON users (email); |
2. 创建唯一索引
|
1 |
CREATE UNIQUE INDEX idx_username ON users (username); |
3. 创建复合索引
- 符合索引使用时必须遵循最左前缀原则，查询时必须包含最左边的列才能生效
|
1 2 |
-- 按顺序：先按 category_id，再按 created_at 排序 CREATE INDEX idx_category_created ON products (category_id, created_at); |
4. 在建表时直接定义索引
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(20), created_at DATETIME, -- 主键自动创建聚簇索引（InnoDB） INDEX idx_user_status (user_id, status), -- 普通复合索引 UNIQUE INDEX uk_order_no (order_no) -- 唯一索引 ); |
5. 添加主键（自动添加聚簇索引）
|
1 |
ALTER TABLE table_name ADD PRIMARY KEY (id); |
