一、索引设计：精准覆盖，加速检索

1. 复合索引的顺序优化

复合索引（多列索引）的顺序直接影响查询效率。遵循“最左前缀原则”，将高选择性列（区分度高）放在索引左侧。例如，对于用户表（user_id, username, status），若常用查询条件为WHERE status = 'active' AND username LIKE '张%'，则应创建索引(status, username)而非(username, status)，因为status的过滤性更强。

2. 避免过度索引

索引虽能加速查询，但会降低写入性能（增删改需同步更新索引）。建议仅对高频查询、排序或分组的列创建索引。可通过EXPLAIN分析查询执行计划，确认索引是否被有效利用。

3. 覆盖索引的妙用

覆盖索引指查询列全部包含在索引中，无需回表查询数据行。例如，对索引(username, email)执行SELECT username, email FROM users WHERE username = 'John'，可直接从索引获取数据，避免回表。

二、查询优化：重构SQL，减少开销

4. 避免SELECT *

仅查询必要字段，减少网络传输和内存占用。例如，将SELECT * FROM orders改为SELECT order_id, customer_id, total_amount FROM orders。

5. 合理使用JOIN

• 小表驱动大表：在JOIN中，将小表放在右侧，减少外层循环次数。
• 索引匹配：确保JOIN字段有索引，避免全表扫描。
• 避免笛卡尔积：确保JOIN条件完整，避免遗漏ON子句。

6. 分页查询优化

大偏移量分页（如LIMIT 10000, 20）性能差，可改用“延迟关联”或“子查询优化”：

-- 传统方式（慢）
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;
-- 优化方式（快）
SELECT o.* FROM orders o 
JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 20) AS tmp 
ON o.id = tmp.id;

三、缓存策略：减少重复计算

7. 查询缓存的合理配置

MySQL的查询缓存（Query Cache）可缓存SELECT结果，但需注意：

• 适用场景：读多写少、查询结果稳定的表。
• 禁用场景：写频繁的表（缓存失效开销大）。
• 配置参数：query_cache_type=ON，query_cache_size按需设置。

8. 应用层缓存

对高频且结果稳定的查询（如配置数据），可使用Redis等缓存中间件。例如：

# Python示例：Redis缓存
import redis
import json
r = redis.Redis(host='localhost', port=6379)
cache_key = "user_profile:123"
# 从缓存读取
data = r.get(cache_key)
if not data:
    # 缓存未命中，查询数据库
    user = db.query("SELECT * FROM users WHERE id = 123")
    r.set(cache_key, json.dumps(user), ex=3600)  # 缓存1小时
else:
    user = json.loads(data)

四、子查询优化：重构为连接或临时表

9. 子查询转JOIN

子查询可能引发多次表扫描，改用JOIN更高效。例如：

-- 子查询（慢）
SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');
-- JOIN优化（快）
SELECT o.* FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE c.status = 'active';

10. 使用临时表优化复杂子查询

对多层嵌套子查询，可拆分为临时表：

-- 原始查询（复杂）
SELECT * FROM products 
WHERE category_id IN (
    SELECT id FROM categories 
    WHERE parent_id IN (
        SELECT id FROM categories WHERE name = 'Electronics'
    )
);
-- 临时表优化（清晰）
CREATE TEMPORARY TABLE temp_categories AS 
SELECT id FROM categories WHERE name = 'Electronics';
SELECT p.* FROM products p 
JOIN categories c ON p.category_id = c.id 
WHERE c.parent_id IN (SELECT id FROM temp_categories);

五、定期表分析与优化：保持健康状态

11. 使用ANALYZE TABLE更新统计信息

MySQL依赖统计信息选择执行计划。表数据变更后，运行ANALYZE TABLE orders更新索引分布统计，避免优化器误选全表扫描。

12. 定期优化表结构

• 碎片整理：对频繁更新的表，执行OPTIMIZE TABLE orders回收碎片空间。
• 归档历史数据：将冷数据移至归档表，减少主表体积。

13. 监控慢查询日志

启用慢查询日志（slow_query_log=ON，long_query_time=2），分析执行时间超过阈值的SQL，针对性优化。

结语

MySQL查询优化需综合索引设计、SQL重构、缓存策略和定期维护。通过实践上述13大秘籍，可显著提升查询效率，降低系统负载。实际优化中，建议结合EXPLAIN、SHOW PROFILE等工具，持续监控和调整。