一、覆盖索引：消除回表操作的性能利器

当查询语句所需字段全部包含在索引中时，数据库无需回表查询数据页，这种设计称为覆盖索引。以电商订单表为例，假设存在复合索引(user_id, order_status, create_time)，以下查询可完全利用覆盖索引：

-- 优化前：需要回表查询所有字段
SELECT * FROM orders 
WHERE user_id=1001 AND order_status='paid' 
ORDER BY create_time DESC LIMIT 10;
-- 优化后：仅查询索引包含字段
SELECT user_id, order_status, create_time FROM orders 
WHERE user_id=1001 AND order_status='paid' 
ORDER BY create_time DESC LIMIT 10;

通过EXPLAIN分析执行计划，优化后的查询Extra列显示Using index，表明成功使用覆盖索引。实际测试显示，在千万级数据表中，此类优化可使查询响应时间从120ms降至8ms。

二、联合索引设计：遵循最左匹配原则

联合索引的构建需要综合考虑字段选择性和查询频率。以用户行为日志表为例，假设包含(user_id, action_type, action_time)三个字段：

字段顺序策略：将区分度最高的字段置于左侧。若user_id有10万种取值，action_type仅5种，则索引应设计为(user_id, action_type)而非反向组合
最左匹配原则：以下查询可有效利用索引：
```sql
— 匹配前两列
SELECT * FROM logs
WHERE user_id=1001 AND action_type=’click’;

— 仅匹配第一列
SELECT * FROM logs WHERE user_id=1001;

但以下查询无法利用索引：
```sql
-- 跳过最左字段
SELECT * FROM logs WHERE action_type='click';

索引选择性计算：通过SELECT COUNT(DISTINCT user_id)/COUNT(*) FROM logs计算字段选择性，优先将选择性高于0.1的字段放在索引左侧

三、索引失效场景深度解析

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

函数操作：
```sql
— 错误示例：对索引列使用函数
SELECT * FROM users WHERE YEAR(create_time)=2023;

— 正确改写：使用范围查询
SELECT * FROM users
WHERE create_time BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;

2. **隐式类型转换**：当字符串列与数字比较时，如`WHERE phone=13800138000`（phone为varchar类型），会导致索引失效
3. **OR条件陷阱**：除非所有OR条件都包含索引列，否则不会使用索引：
```sql
-- 错误示例：OR条件导致索引失效
SELECT * FROM products 
WHERE category_id=1 OR price>100;
-- 正确改写：拆分为UNION查询
SELECT * FROM products WHERE category_id=1
UNION ALL
SELECT * FROM products WHERE price>100 AND category_id!=1;

四、分页查询优化：突破LIMIT性能瓶颈

传统分页LIMIT offset, size在大数据量时性能急剧下降，优化方案包括：

子查询优化法：
```sql
— 原始查询：需要扫描offset+size条记录
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;

— 优化方案：先通过索引定位ID，再回表查询
SELECT * FROM orders
WHERE id IN (
SELECT id FROM orders
ORDER BY create_time DESC LIMIT 10000, 20
) ORDER BY create_time DESC;

2. **延迟关联法**：
```sql
-- 先查询主键，再关联查询完整数据
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders 
    ORDER BY create_time DESC LIMIT 10000, 20
) tmp ON o.id=tmp.id;

游标分页法：记录上次查询的最大ID，下次查询使用WHERE id > last_id条件

五、综合优化案例：订单查询系统重构

某电商系统订单查询存在性能问题，原始SQL如下：

SELECT * FROM orders 
WHERE DATE(create_time)='2023-01-01' 
AND status IN ('paid','shipped') 
ORDER BY total_amount DESC 
LIMIT 20 OFFSET 1000;

优化步骤：

创建复合索引(status, create_time, total_amount)

重写查询避免函数操作：

SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'
AND status IN ('paid','shipped') 
ORDER BY total_amount DESC 
LIMIT 20 OFFSET 1000;

对于深度分页场景，改用延迟关联：

SELECT o.* FROM orders o
JOIN (
 SELECT id FROM orders 
 WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'
 AND status IN ('paid','shipped') 
 ORDER BY total_amount DESC 
 LIMIT 20 OFFSET 1000
) tmp ON o.id=tmp.id;

优化后查询时间从3.2秒降至120毫秒，CPU使用率下降65%。

六、性能监控与持续优化

建立完善的监控体系是保障数据库性能的关键：

慢查询日志：设置long_query_time=1记录执行超时的SQL
执行计划分析：定期使用EXPLAIN FORMAT=JSON分析复杂查询

索引使用率监控：通过performance_schema统计索引使用情况：

SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL;

自动化巡检：编写脚本定期检测未使用的索引和缺失的索引建议