一、索引优化：从基础设计到高级策略

1.1 覆盖索引：消除回表开销的黄金法则

当查询所需字段全部包含在索引中时，数据库无需回表查询数据页，这种设计称为覆盖索引。以电商订单查询场景为例：

-- 低效查询：需要回表获取user_name字段
SELECT order_id, user_id, user_name FROM orders WHERE user_id = 1001;
-- 优化方案：创建包含所有查询字段的复合索引
CREATE INDEX idx_user_order ON orders(user_id, order_id, user_name);
-- 优化后查询：直接通过索引获取数据
SELECT order_id, user_id, user_name FROM orders USE INDEX(idx_user_order) WHERE user_id = 1001;

通过EXPLAIN分析执行计划，优化后的查询Extra列将显示Using index，表明成功使用覆盖索引。实际应用中，建议通过pt-index-usage工具分析索引使用情况，识别未被利用的冗余索引。

1.2 联合索引：遵循最左匹配原则的排序艺术

联合索引的字段顺序直接影响查询效率，需遵循”高区分度优先”原则。以用户表为例：

-- 错误设计：将低区分度字段放在首位
CREATE INDEX idx_bad ON users(gender, last_login_time, user_id);
-- 正确设计：高区分度字段优先
CREATE INDEX idx_good ON users(user_id, last_login_time, gender);

当执行WHERE user_id = 1001 AND last_login_time > '2023-01-01'查询时，idx_good索引可有效过滤数据，而idx_bad索引仅能利用gender字段，过滤效果极差。对于范围查询后的字段，建议将其放在索引末尾，如(user_id, create_time DESC, status)。

1.3 索引失效的典型场景与规避方案

以下操作会导致索引失效，需特别注意：

• 隐式类型转换：WHERE phone = '13800138000'（phone为varchar类型）
• 函数操作：WHERE DATE(create_time) = '2023-01-01'
• 模糊查询前缀缺失：WHERE name LIKE '%张%'
• OR条件混合非索引列：WHERE user_id = 1001 OR age > 30

优化方案示例：

-- 优化前（索引失效）
SELECT * FROM orders WHERE DATE(create_time) = '2023-01-01';
-- 优化后（使用范围查询）
SELECT * FROM orders 
WHERE create_time >= '2023-01-01 00:00:00' 
  AND create_time < '2023-01-02 00:00:00';

二、查询重构：突破性能瓶颈的关键技术

2.1 分页查询优化：从OFFSET到游标分页

传统LIMIT分页在深度分页时性能急剧下降，以百万级数据表为例：

-- 低效方案：需要扫描offset+n条记录
SELECT * FROM large_table ORDER BY id LIMIT 1000000, 10;
-- 优化方案：使用子查询定位ID范围
SELECT * FROM large_table 
WHERE id > (SELECT id FROM large_table ORDER BY id LIMIT 1000000, 1) 
ORDER BY id LIMIT 10;
-- 更优方案：游标分页（需业务支持）
SELECT * FROM large_table 
WHERE id > last_seen_id 
ORDER BY id LIMIT 10;

游标分页方案将查询时间复杂度从O(n)降至O(1)，特别适合移动端无限滚动场景。

2.2 JOIN操作优化：选择正确的连接方式

不同JOIN类型的性能差异显著，需根据数据分布选择：

• INNER JOIN：优先使用，确保结果集最小化
• LEFT JOIN：当右表可能无匹配时使用，注意索引覆盖
• STRAIGHT_JOIN：强制指定连接顺序，解决优化器选择错误

优化示例：

-- 优化前（可能使用全表扫描）
SELECT * FROM orders o LEFT JOIN users u ON o.user_id = u.id;
-- 优化后（强制使用小表驱动大表）
SELECT * FROM users u STRAIGHT_JOIN orders o ON u.id = o.user_id;

通过EXPLAIN查看type列，确保连接操作显示为eq_ref或ref级别。

2.3 子查询重构：从相关子查询到派生表

相关子查询会导致重复执行，应尽量转换为JOIN或派生表：

-- 低效方案：相关子查询
SELECT * FROM products p 
WHERE price > (SELECT AVG(price) FROM products WHERE category_id = p.category_id);
-- 优化方案：使用JOIN
SELECT p.* FROM products p 
JOIN (
    SELECT category_id, AVG(price) as avg_price 
    FROM products 
    GROUP BY category_id
) c ON p.category_id = c.category_id 
WHERE p.price > c.avg_price;

三、执行计划分析：精准定位性能瓶颈

3.1 EXPLAIN关键字段解读

• type：访问类型（system > const > eq_ref > ref > range > index > ALL）
• key：实际使用的索引
• rows：预估扫描行数
• Extra：重要提示（Using index/Using where/Using temporary）

3.2 慢查询日志分析

配置慢查询日志并定期分析：

-- 开启慢查询日志（需权限）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 设置阈值(秒)
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- 使用pt-query-digest分析日志
pt-query-digest /var/lib/mysql/mysql-slow.log > report.txt

3.3 性能监控工具链

• 内置工具：SHOW STATUS、SHOW PROCESSLIST
• 专业工具：Performance Schema、Sys Schema
• 可视化工具：结合日志服务与监控告警系统构建实时看板

四、高级优化技术

4.1 索引下推（ICP）优化

MySQL 5.6+支持的ICP技术可将WHERE条件过滤下推到存储引擎层，减少上层数据传输量。示例：

-- 启用ICP前：先回表再过滤
-- 启用ICP后：在索引层完成部分过滤
SELECT * FROM users 
WHERE name LIKE '张%' AND age = 30;

4.2 多值比较优化

对于IN列表查询，保持列表长度适中（建议<100）：

-- 优化前（可能使用全表扫描）
SELECT * FROM products WHERE category_id IN (1,2,3,...,1000);
-- 优化后（拆分为多个查询或使用临时表）
-- 方案1：拆分查询
SELECT * FROM products WHERE category_id IN (1,2,3,...,100);
SELECT * FROM products WHERE category_id IN (101,102,...,200);
-- 方案2：使用临时表
CREATE TEMPORARY TABLE temp_categories (id INT PRIMARY KEY);
INSERT INTO temp_categories VALUES (1),(2),(3)...;
SELECT p.* FROM products p JOIN temp_categories t ON p.category_id = t.id;

4.3 数据库参数调优

关键参数配置建议：

[mysqld]
# 连接数相关
max_connections = 500
thread_cache_size = 50
# 缓冲池配置
innodb_buffer_pool_size = 4G  # 建议为物理内存的50-70%
innodb_buffer_pool_instances = 8  # 每个实例至少1GB
# 查询缓存（MySQL 8.0已移除）
query_cache_size = 0  # 5.6+版本建议关闭
# 日志配置
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

五、优化实践路线图

1. 基础排查：通过慢查询日志定位TOP10问题SQL
2. 执行计划分析：使用EXPLAIN确认索引使用情况
3. 索引优化：根据查询模式调整索引设计
4. 查询重构：应用分页优化、JOIN优化等技术
5. 参数调优：根据服务器配置调整关键参数
6. 持续监控：建立性能基线并设置异常告警

实际优化案例中，某电商系统通过上述方法将订单查询响应时间从3.2秒降至85毫秒，CPU使用率下降42%。建议开发者建立定期优化机制，结合业务发展持续调整数据库设计，始终保持系统处于最佳性能状态。