MySQL查询性能提升的四大核心技巧

一、索引优化：查询加速的基石

1.1 索引设计原则

• 最左前缀原则：复合索引(a,b,c)只能优化a、a,b或a,b,c查询
• 选择性原则：选择区分度高的列建索引（如ID、手机号）
• 覆盖索引：索引包含查询所需全部字段，避免回表

-- 低效写法（需回表）
SELECT * FROM users WHERE username = 'admin';

-- 高效写法（覆盖索引）
ALTER TABLE users ADD INDEX idx_username_email(username, email);
SELECT username, email FROM users WHERE username = 'admin';

1.2 索引失效场景

场景	示例	解决方案
使用函数	WHERE YEAR(create_time) = 2023	改用范围查询
隐式类型转换	WHERE phone = 13800138000	确保类型一致
前导模糊查询	WHERE name LIKE '%张'	改用后缀模糊查询
OR条件未全索引	WHERE a=1 OR b=2（仅a有索引）	分别建索引或改用UNION

二、查询重构：从SQL写法提升效率

2.1 避免SELECT *

-- 反例（读取不必要字段）
SELECT * FROM orders WHERE user_id = 100;

-- 正例（只查所需字段）
SELECT order_id, amount, status FROM orders WHERE user_id = 100;

性能对比：

• 数据量100万行时，查询速度提升40%-60%
• 网络传输量减少50%以上

2.2 分页优化

-- 低效写法（越往后越慢）
SELECT * FROM products LIMIT 100000, 20;

-- 高效写法（索引分页）
SELECT * FROM products WHERE id > 100000 LIMIT 20;

-- 终极方案（延迟关联）
SELECT * FROM products p 
JOIN (SELECT id FROM products ORDER BY create_time DESC LIMIT 100000, 20) t
ON p.id = t.id;

2.3 JOIN优化技巧

graph LR
    A[小表驱动大表] --> B[确保关联字段有索引]
    B --> C[避免多表JOIN时超过3个表]
    C --> D[考虑拆分为多个查询]

-- 低效JOIN（大表驱动）
SELECT * FROM large_table l JOIN small_table s ON l.id = s.id;

-- 高效JOIN（小表驱动）
SELECT * FROM small_table s JOIN large_table l ON s.id = l.id;

三、执行计划分析：找出性能瓶颈

3.1 EXPLAIN关键指标解读

EXPLAIN SELECT * FROM users WHERE age > 20 ORDER BY name;

列名	优化重点	理想值
type	访问类型	const/eq_ref/range
key	实际使用的索引	显示索引名
rows	预估检查行数	越小越好
Extra	额外信息	避免Using filesort

3.2 常见性能问题诊断

1. 全表扫描：

   -- type=ALL表示全表扫描
   EXPLAIN SELECT * FROM products WHERE category = '电子';

解决方案：ALTER TABLE products ADD INDEX idx_category(category);

2. 文件排序：

   -- Extra显示Using filesort
   EXPLAIN SELECT * FROM orders ORDER BY create_time;

解决方案：ALTER TABLE orders ADD INDEX idx_create_time(create_time);

四、数据库设计优化：从源头提升性能

4.1 范式与反范式平衡

场景	推荐设计	示例
高频查询需要多表关联	适当反范式化	用户表冗余常用部门信息
写多读少	严格遵循三范式	日志记录表
统计分析	使用星型模型	数据仓库中的事实表+维度表

4.2 数据类型选择

场景	推荐类型	优势
短字符串（<255字）	VARCHAR	节省空间
状态标志	TINYINT	比ENUM更灵活
大文本	TEXT（与主表分离）	避免拖慢主表查询
时间戳	TIMESTAMP/DATETIME	支持时间计算

4.3 分区表实战

-- 按范围分区（适合时间序列数据）
CREATE TABLE logs (
    id INT AUTO_INCREMENT,
    log_time DATETIME,
    content TEXT,
    PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查询特定分区（性能提升显著）
SELECT * FROM logs PARTITION(p2023) WHERE log_time BETWEEN '2023-01-01' AND '2023-06-30';

五、实战案例：电商查询优化

5.1 原始低效查询

SELECT * FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'paid'
AND u.vip_level > 3
ORDER BY o.create_time DESC
LIMIT 0, 20;

5.2 优化步骤

1. 添加索引：

   ALTER TABLE orders ADD INDEX idx_status_create_time(status, create_time);
   ALTER TABLE users ADD INDEX idx_vip_level(vip_level);

2. 重构查询：

   SELECT 
     o.order_id, o.amount, o.create_time,
     u.username, u.avatar,
     p.product_name, p.price
   FROM orders o FORCE INDEX(idx_status_create_time)
   JOIN users u FORCE INDEX(idx_vip_level) ON o.user_id = u.user_id
   JOIN products p ON o.product_id = p.product_id
   WHERE o.status = 'paid'
   AND u.vip_level > 3
   ORDER BY o.create_time DESC
   LIMIT 0, 20;

3. 终极优化（数据量极大时）：

   SELECT /*+ SEMIJOIN(MATERIALIZATION) */ 
     o.order_id, o.amount, o.create_time,
     u.username, u.avatar,
     p.product_name, p.price
   FROM orders o
   JOIN (
     SELECT user_id FROM users WHERE vip_level > 3
   ) u ON o.user_id = u.user_id
   JOIN products p ON o.product_id = p.product_id
   WHERE o.status = 'paid'
   ORDER BY o.create_time DESC
   LIMIT 0, 20;

六、监控与持续优化

6.1 慢查询日志配置

# my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1  # 超过1秒的记录
log_queries_not_using_indexes = 1

6.2 性能分析工具

1. pt-query-digest：

   pt-query-digest /var/log/mysql/mysql-slow.log

2. MySQL Workbench可视化分析
3. Percona Monitoring and Management

6.3 定期维护建议

1. 每周执行：

   ANALYZE TABLE orders, users, products;

2. 每月执行（业务低峰期）：

   OPTIMIZE TABLE large_table;

3. 索引碎片检查：

   SELECT table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_mb
   FROM mysql.innodb_index_stats
   WHERE stat_name = 'size' AND database_name = DATABASE();

通过系统性地应用这四大优化技巧，可以使MySQL查询性能获得显著提升。建议从执行计划分析入手，找出瓶颈后针对性应用索引优化、查询重构等方法，最后通过合理的数据库设计预防性能问题。记住，没有放之四海皆准的优化方案，需要结合具体业务场景和数据特点持续调优。