SQL索引优化的核心是通过合理的索引设计减少数据库扫描的数据量，从而提升查询速度。在使用索引前，我们需要先理解它的底层工作原理，才能避免盲目创建索引带来的性能损耗。
一、SQL索引的底层原理
主流关系型数据库（如MySQL、PostgreSQL）的索引大多基于B树或B+树结构实现，这是一种平衡多路搜索树，能保证数据有序且查询时间复杂度为O(log n)。
1. B+树索引的结构特点
B+树的所有数据都存储在叶子节点，非叶子节点仅存储索引键和子节点指针，这样的设计有两个核心优势：
- 非叶子节点不存储实际数据，单个节点可以存储更多的索引键，减少树的高度，降低磁盘IO次数
- 叶子节点通过双向链表连接，支持范围查询时直接遍历叶子节点，不需要回溯上层节点
2. 索引加速查询的逻辑
没有索引时，数据库执行查询需要进行全表扫描，逐行匹配条件，数据量越大耗时越长。创建索引后，数据库会先通过B+树快速定位到符合条件的索引条目，再通过索引中存储的行指针（或聚簇索引的主键）直接找到对应的数据行，跳过大量无关数据的扫描。
注意：聚簇索引的叶子节点直接存储整行数据，非聚簇索引的叶子节点存储主键值，需要二次查找才能获取完整数据。
二、SQL索引优化的实现方法
1. 合理选择索引列
并非所有列都适合创建索引，需要优先选择以下类型的列：
- 经常出现在WHERE、JOIN、ORDER BY、GROUP BY子句中的列
- 区分度高的列，比如用户表的用户ID，区分度远高于性别字段
- 外键关联的列，能加速表连接查询
可以通过以下语句查看列的区分度，选择合适的索引列：
-- 计算列的区分度，值越接近1区分度越高
SELECT
COUNT(DISTINCT column_name) / COUNT(*) AS cardinality
FROM table_name;2. 创建复合索引遵循最左前缀原则
复合索引是包含多个列的索引，使用时需要遵循最左前缀原则，即查询条件中必须包含复合索引的最左侧列，索引才能生效。
比如创建复合索引(a, b, c)，以下查询可以使用索引：
- WHERE a = 1
- WHERE a = 1 AND b = 2
- WHERE a = 1 AND b = 2 AND c = 3
以下查询无法使用索引：
- WHERE b = 2
- WHERE a = 1 AND c = 3
创建复合索引的示例：
-- 为用户订单表创建复合索引，加速用户查询自己的订单 CREATE INDEX idx_user_id_order_time ON order_table(user_id, order_time);
3. 避免索引失效的常见场景
即使创建了索引，不当的查询写法也会导致索引失效，需要特别注意以下情况：
| 场景 | 示例 | 优化方式 |
|---|---|---|
| 对索引列使用函数或运算 | WHERE YEAR(create_time) = 2023 | 改成WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01' |
| 使用LIKE左模糊匹配 | WHERE name LIKE '%张三' | 尽量使用右模糊匹配，如WHERE name LIKE '张三%' |
| 查询条件使用OR且部分列无索引 | WHERE a = 1 OR b = 2（b无索引） | 拆分查询或用UNION替换OR |
| 索引列参与类型转换 | WHERE user_id = '123'（user_id是整型） | 保持查询条件和列类型一致，改成WHERE user_id = 123 |
4. 定期维护索引
随着数据的增删改，索引会产生碎片，影响查询效率，需要定期维护：
- 删除冗余索引：比如已有索引(a,b)，就不需要再单独创建索引(a)，冗余索引会额外占用存储空间，拖慢写操作
- 重建碎片率高的索引：当索引碎片率超过30%时，可以通过重建索引优化
MySQL中重建索引的示例：
-- 重建指定表的索引 ALTER TABLE order_table ENGINE=InnoDB; -- 单独重建某个索引 DROP INDEX idx_user_id ON order_table; CREATE INDEX idx_user_id ON order_table(user_id);
三、索引优化的注意事项
索引不是越多越好，每个索引都会占用额外的存储空间，并且在插入、更新、删除数据时，数据库需要同步维护所有相关索引，会降低写操作的性能。一般来说，单表的索引数量建议控制在5个以内，写多读少的场景要适当减少索引数量。
另外，小表（数据量少于1000行）不需要创建索引，全表扫描的成本远低于维护索引的成本，创建索引反而会降低性能。