MySQL 中如何进行 SQL 调优?
重点
平时进行 SQL 调优,主要是通过观察慢 SQL,然后利用 explain 分析查询语句的执行计划,识别性能瓶颈,优化查询语句。
1) 合理设计索引,利用联合索引进行覆盖索引的优化,避免回表的发生,减少一次查询和随机 I/O
- 回表：索引无法满足查询所需的所有列数据，需要回到主表获取额外的数据。
- 避免回表：创建覆盖索引(索引包含了查询所需的所有列)，让查询可以直接从索引中获取所有数据，无需访问主表。
例子：
建表和建立索引：
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
gender CHAR(1),
city VARCHAR(50)
);
CREATE INDEX idx_name_age_gender ON user(name, age, gender);
- 建立了联合索引：
name，age，gender。
若执行SELECT city FROM user WHERE name = 'John' AND age = 25; 因为 select 需要 返回city。 索引中没有city列的数据，还需要根据索引条目中包含的主键信息（虽然例子中没有显式指定，但通常索引会包含指向主键的指针）回到 user 表的主键索引中，去查找完整的行数据，这个“回到主表查找 city 列”的过程就是回表 。
2) 避免 SELECT *,只查询必要的字段
3) 避免在 SQL 中进行函数计算等操作,使得无法命中索引
4) 避免使用 %LIKE,导致全表扫描
5) 注意联合索引需满足最左匹配原则
解释最左匹配原则：最左匹配原则是指在使用联合索引时，必须按照索引的顺序从左到右使用，不能跳过索引中的列。
1. SQL 实战理解 最左匹配原则
建表语句:假设我们有一个用户订单表，包含用户ID、订单日期和订单金额三个字段，我们对这三个字段创建一个联合索引。
CREATE TABLE user_orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
order_date DATE,
order_amount DECIMAL(10,2),
INDEX idx_user_date_amount (user_id, order_date, order_amount)
);
Python脚本生成测试数据：
from datetime import datetime, timedelta
import random
# 生成INSERT语句
def generate_insert_statements():
start_date = datetime(2023, 1, 1)
statements = []
for _ in range(4200):
user_id = random.randint(1, 1000)
days = random.randint(0, 365)
order_date = (start_date + timedelta(days=days)).strftime('%Y-%m-%d')
order_amount = round(random.uniform(10.0, 1000.0), 2)
insert_sql = f"INSERT INTO user_orders (user_id, order_date, order_amount) VALUES ({user_id}, '{order_date}', {order_amount});"
statements.append(insert_sql)
# 将所有INSERT语句写入文件
with open('insert_data.sql', 'w') as f:
f.write('\n'.join(statements))
print("INSERT语句已生成到 insert_data.sql 文件中")
if __name__ == "__main__":
generate_insert_statements()
测试不同查询场景：
-- 完全满足最左匹配原则（使用全部索引列）
EXPLAIN SELECT * FROM user_orders
WHERE user_id = 100 AND order_date = '2023-05-01' AND order_amount = 500;
-- 满足最左匹配原则（使用索引的前两列）
EXPLAIN SELECT * FROM user_orders
WHERE user_id = 100 AND order_date = '2023-05-01';
explain 结果：
-- 满足最左匹配原则（只使用第一列）
EXPLAIN SELECT * FROM user_orders
WHERE user_id = 100;
explain 结果：
-- 不满足最左匹配原则（跳过`user_id`）
EXPLAIN SELECT * FROM user_orders
WHERE order_date = '2023-05-01' AND order_amount = 500;
explain 结果：
-- 不满足最左匹配原则（只使用order_date）
EXPLAIN SELECT * FROM user_orders
WHERE order_date = '2023-05-01';
explain 结果：
-- 不满足最左匹配原则（只使用order_amount）
EXPLAIN SELECT * FROM user_orders
WHERE order_amount = 500;
explain 结果：
- 从上述explain 的结果看出，不满足最左匹配原则，
filitered都很低。
6) 不要对无索引字段进行排序操作
-
强制使用文件排序(filesort)：
当对无索引字段排序时，MySQL无法利用索引的有序性，必须将数据加载到内存中进行排序，这就是filesort，filesort是一个非常耗费资源的操作。 -
内存开销大
如果排序数据量小，MySQL会在内存中完成排序，如果数据量超过sort_buffer_size，会发生磁盘文件排序，磁盘排序涉及临时文件的创建和多次IO，性能更差！
SQL实战演示
-- 创建测试表
CREATE TABLE worker(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2),
department VARCHAR(50),
INDEX idx_salary (salary) -- 只对salary创建索引
);
-- 插入测试数据
INSERT INTO worker(name, salary, department) VALUES
('张三', 5000, '技术部'),
('李四', 6000, '市场部'),
('王五', 4500, '技术部'),
('赵六', 7000, '销售部');
-- 会使用索引排序的情况：
-- 只查询索引列
SELECT salary FROM employees ORDER BY salary;
-- 或者
SELECT id, salary FROM employees ORDER BY salary;
-- 结果显示: Using index for order by
会导致filesort的情况：
-- 特例：查询所有列（SELECT *）
SELECT * FROM employees ORDER BY salary;
- 当使用
SELECT *时，需要回表获取所有列的数据，这种情况下，MySQL认为使用索引排序的成本比filesort更高。
-- 对无索引的department字段排序
EXPLAIN SELECT * FROM employees ORDER BY department;
-- 结果显示: Using filesort
7) 连表查询需要注意不同字段的字符集是否一致,否则也会导致全表扫描
除此之外,还可以利用缓存来优化,一些变化少或者访问频繁的数据设置到缓存中,减轻数据库的压力,提升查询的效率。
还可以通过业务来优化,例如少展示一些不必要的字段,减少多表查询的情况,将列表查询替换成分页分批查询等等。