SQL性能优化实践: 索引和查询调优
一、索引（Index）基础与核心原理
1.1 索引的存储结构与工作原理
B+树（B-plus Tree）是关系型数据库最常用的索引结构。以MySQL的InnoDB引擎为例，其聚簇索引（Clustered Index）的叶子节点直接存储行数据，非聚簇索引（Non-clustered Index）则存储主键值。这种设计带来显著的性能差异：通过主键查询仅需1次IO，而二级索引需要2次IO。
-- 创建复合索引示例
CREATE INDEX idx_user_order
ON orders(user_id, order_date)
USING BTREE;
1.2 索引类型与适用场景
根据2023年Percona的基准测试，合理使用覆盖索引（Covering Index）可使查询速度提升5-8倍。以下是指标对比：
| 索引类型 | 查询耗时(ms) | 索引大小(MB) |
|---|---|---|
| 无索引 | 1200 | - |
| 单列索引 | 350 | 85 |
| 覆盖索引 | 45 | 120 |
二、查询调优关键技术
2.1 执行计划（Execution Plan）深度解析
使用EXPLAIN命令分析以下查询：
EXPLAIN SELECT * FROM products
WHERE category_id = 5 AND price > 100
ORDER BY create_time DESC LIMIT 10;
-- 输出关键字段解读
/*
type: range（范围扫描）
key: idx_category_price（使用索引）
rows: 1500（扫描行数）
Extra: Using where; Using filesort
*/
2.2 索引失效的六大典型场景
- 隐式类型转换：WHERE user_id = '123'（user_id为整型）
- 前导通配符查询：LIKE '%keyword%'
- 索引列参与运算：WHERE YEAR(create_time) = 2023
- OR连接非索引列：WHERE a=1 OR b=2
- 不符合最左前缀原则：INDEX(a,b,c)但查询条件只有b
- 统计信息过期导致优化器误判
三、实战优化案例分析
3.1 分页查询深度优化方案
传统分页在百万级数据时性能急剧下降：
-- 低效写法
SELECT * FROM logs
ORDER BY id LIMIT 1000000, 20;
-- 优化方案：游标分页
SELECT * FROM logs
WHERE id > 1000000
ORDER BY id LIMIT 20;
3.2 时间范围查询优化实践
某物联网平台的时间序列数据表优化案例：
- 原始查询：全表扫描耗时8.2秒
- 添加组合索引（device_id, timestamp）后：耗时降至120ms
- 引入分区表（Partitioning）按周划分：最终耗时23ms
四、监控与维护体系
4.1 索引使用效率分析
-- MySQL索引使用统计查询
SELECT
object_schema,
object_name,
index_name,
rows_read,
rows_inserted
FROM performance_schema.table_io_waits_summary_by_index_usage;
通过定期分析可发现：某表的idx_status索引在过去30天零访问，建议删除以节省存储空间和维护成本。
4.2 索引碎片整理策略
当索引碎片率超过30%时需进行重建。使用OPTIMIZE TABLE命令可使索引大小减少40%，查询性能提升约25%。建议在业务低峰期执行：
ALTER TABLE orders ENGINE=InnoDB;
ANALYZE TABLE orders;
SQL优化, 数据库索引, 查询性能, 执行计划, MySQL调优
