一、知识点
1. 索引的概念
索引是数据库中用于加速数据检索的辅助数据结构，类似于书的目录。它基于表的一列或多列创建，通过指针快速定位到物理行。
2. 索引的主要类型
类型 | 说明 | 特点 |
聚集索引 (Clustered Index) | 数据行的物理存储顺序与索引顺序一致 | 每个表只能有一个，叶子节点存储完整的数据行 |
非聚集索引 (Non-clustered Index) | 索引逻辑顺序与数据物理顺序不同 | 叶子节点存储指向数据行的指针（或聚集索引键） |
唯一索引 (Unique Index) | 保证索引列不包含重复值 | 可用于强制唯一性约束 |
复合索引 (Composite Index) | 基于多个列的索引 | 遵循“最左前缀”原则，列顺序很重要 |
位图索引 (Bitmap Index) | 适用于低基数（少量不同值）列 | 适合数据仓库，OLTP中不常用 |
3. B+树索引结构
根节点：存储索引值的范围指针。
内部节点：进一步划分范围，引导查找。
叶子节点：存储索引值及对应的行指针（对于非聚集索引）或实际数据行（对于聚集索引）。
特点：所有叶子节点在同一层，且通过链表相连，支持范围查询。
4. 索引的优缺点
优点 | 缺点 |
大大加快 SELECT 查询速度 | 占用额外的存储空间 |
加速 ORDER BY 和 GROUP BY | 降低 INSERT、UPDATE、DELETE 的性能（需同步维护索引） |
唯一索引可保证数据唯一性 | 过多索引会增加查询优化器的选择难度 |
覆盖索引可避免回表，提高I/O效率 | 索引碎片会产生额外维护成本 |
5. 索引失效的常见场景
对索引列使用函数（如
WHERE YEAR(date)=2024）隐式类型转换（如
varchar列与int比较）LIKE 以通配符开头（如
WHERE name LIKE '%张'）OR 条件中部分列无索引（可能走全表扫描）
使用
!=、<>、NOT IN、IS NOT NULL（当选择性低时）复合索引未使用最左前缀列
在索引列上进行算术运算（如
WHERE salary*1.1 > 5000）
6. 索引优化原则
在选择性高（重复值少）的列上建索引；选择性低的列（如性别）一般不建。
复合索引的列顺序：等值查询列在前，范围查询列在后。
尽量使用覆盖索引（索引中包含查询所需的所有列），避免回表。
避免在索引列上使用函数或隐式类型转换。
定期重建或重组索引以减少碎片。
二、具体事例
假设有一张订单表 Orders：
sql
CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE, CustomerID INT, Status VARCHAR(20), Amount DECIMAL(10,2));
并创建了以下索引：
sql
CREATE INDEX idx_customer ON Orders(CustomerID);CREATE INDEX idx_date_status ON Orders(OrderDate, Status);
示例1：有效使用索引
sql
SELECT OrderID, Amount FROM Orders WHERE CustomerID = 123;
→ 使用 idx_customer 快速定位到客户123的订单。
示例2：索引失效（函数）
sql
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;
→ 对 OrderDate 使用函数，无法使用索引。应改为：
sql
SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';
示例3：覆盖索引
sql
SELECT OrderDate, Status FROM Orders WHERE OrderDate = '2024-05-01' AND Status = '已发货';
→ 如果 idx_date_status 已经包含 OrderDate 和 Status，则只需扫描索引即可返回结果，无需访问表（覆盖索引）。
示例4：复合索引最左前缀
sql
SELECT FROM Orders WHERE Status = '已发货'; -- 未使用 idx_date_status，因为未提供 OrderDate（最左列）SELECT FROM Orders WHERE OrderDate = '2024-05-01'; -- 可以使用 idx_date_status（最左列存在）SELECT * FROM Orders WHERE OrderDate = '2024-05-01' AND Status = '已发货'; -- 完全使用 idx_date_status
