【数据库】组合索引生效规则及索引失效

索引演示示例

# 创建表结构
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  DepartmentID INT,
  Salary DECIMAL(10, 2),
  HireDate DATE
);

# 插入示例数据
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary, HireDate) VALUES
(1, 'John', 'Doe', 10, 50000.00, '2023-01-01'),
(2, 'Jane', 'Smith', 10, 60000.00, '2023-02-01'),
(3, 'Alice', 'Johnson', 20, 55000.00, '2023-03-01'),
(4, 'Bob', 'Brown', 20, 70000.00, '2023-04-01'),
(5, 'Charlie', 'Davis', 30, 80000.00, '2023-05-01'),
(6, 'Emily', 'Davis', 10, 52000.00, '2023-06-01'),
(7, 'Michael', 'Brown', 10, 58000.00, '2023-07-01'),
(8, 'Sarah', 'Miller', 20, 62000.00, '2023-08-01'),
(9, 'David', 'Wilson', 20, 65000.00, '2023-09-01'),
(10, 'Jessica', 'Taylor', 30, 72000.00, '2023-10-01'),
(11, 'Matthew', 'Anderson', 30, 75000.00, '2023-11-01'),
(12, 'Olivia', 'Jackson', 10, 54000.00, '2023-12-01'),
(13, 'Daniel', 'Thomas', 10, 56000.00, '2024-01-01'),
(14, 'Sophia', 'Moore', 20, 60000.00, '2024-02-01'),
(15, 'Ethan', 'Martin', 20, 68000.00, '2024-03-01'),
(16, 'Mia', 'Thompson', 30, 78000.00, '2024-04-01'),
(17, 'James', 'White', 30, 82000.00, '2024-05-01'),
(18, 'Ava', 'Harris', 10, 53000.00, '2024-06-01'),
(19, 'Noah', 'Robinson', 10, 57000.00, '2024-07-01'),
(20, 'Isabella', 'Clark', 20, 63000.00, '2024-08-01');

组合索引

-- 创建组合索引
CREATE INDEX idx_lastname_dept ON Employees (LastName, DepartmentID);
CREATE INDEX idx_salary_dept ON Employees (Salary, DepartmentID);
CREATE INDEX idx_lastname_firstname ON Employees (LastName, FirstName);

推荐文章：MySQL高级(一) EXPLAIN用法和结果分析：https://blog.csdn.net/why15732625998/article/details/80388236

• id 表的读取顺序
• select_type 查询类型，区别普通查询、联合查询、子查询等的复杂查询
• type 查询级别 system > const > eq_ref > ref > range > index > all
• possible_keys 哪些索引可以使用
• key 哪些索引被实际使用
• rows 每张表有多少行被优化器查询

组合索引的生效规则主要基于以下几点：

一、左前缀原则

• 左前缀匹配：查询必须从索引的最左边的列开始，并且是连续的。这意味着如果要使组合索引生效，查询条件至少需要包含索引中最左边的列。

  EXPLAIN SELECT * FROM Employees WHERE LastName = 'Jane';
  EXPLAIN SELECT * FROM Employees WHERE LastName = 'Jane' AND DepartmentID = 10;
  

• 不遵循左前缀的情况：如果查询没有使用索引最左边的列或者跳过了中间的列，则不会使用整个索引

  EXPLAIN SELECT * FROM Employees WHERE DepartmentID = 10;
  

二、范围条件的影响

• 范围条件限制后续列的使用：一旦在组合索引中遇到了范围条件（如>, <, BETWEEN等），那么该范围条件后的所有列将不再参与索引的选择过程。这是因为数据库引擎在处理范围条件时，无法再有效地利用后续的列来进行精确匹配。

EXPLAIN SELECT * FROM Employees WHERE Salary > 60000 AND DepartmentID = 30;

Using index condition 说明 MySQL 正在利用索引条件下推（Index Condition Pushdown, ICP）技术来优化查询。在这种情况下，即使 DepartmentID = 30 不是通过索引来直接定位数据的，MySQL 也会将这个条件“下推”到存储引擎层，在存储引擎层进行额外的过滤。

三、覆盖索引

• 覆盖索引：如果查询的所有列都在索引中定义了，那么数据库可以直接从索引中读取数据，而不需要回表访问原数据行。这称为覆盖索引，它可以极大地提高查询性能。

EXPLAIN SELECT Salary, DepartmentID FROM Employees WHERE Salary = 70000 AND DepartmentID = 20;
EXPLAIN SELECT LastName, FirstName FROM Employees WHERE LastName = 'Johnson' AND FirstName = 'Alice';

这个查询可以从索引idx_name(column1, column2)中直接获取column1和column2的数据，而不需要访问表中的其他列。因为减少了I/O操作，显著提高查询性能。

四、优化器决策

• 优化器选择：最终是否使用某个索引还取决于数据库查询优化器的判断。优化器会根据统计信息、执行计划成本等因素来决定最佳的执行计划。有时候即使符合以上规则，优化器也可能选择不使用索引，因为可能有更优的执行路径。

索引失效

某些语法或查询条件可能会导致索引不生效。以下是一些常见的情况，其中索引可能不会被使用：

1、使用函数

如果在列上应用了函数，索引通常不会生效。例如：

EXPLAIN SELECT * FROM Employees WHERE UPPER(LastName) = 'Johnson';
# 可改写查询为:
EXPLAIN SELECT * FROM Employees WHERE LastName = 'Johnson';

2、不等操作符

使用不等操作符（如!=, <>）通常会导致索引不生效。例如：

EXPLAIN SELECT * FROM Employees WHERE salary != 50000;

在这种情况下，数据库引擎可能会选择全表扫描。

3、LIKE 通配符

当LIKE语句以通配符开头时，索引通常不会生效。例如：

-- `LIKE`语句以通配符开头数据库引擎无法利用索引来快速定位匹配的行
EXPLAIN SELECT * FROM Employees WHERE LastName LIKE '%Doe%';
-- 可改写查询为:
EXPLAIN SELECT * FROM Employees WHERE LastName LIKE 'Doe%';

4、OR 条件

当使用OR条件时，如果其中一个条件不能使用索引，那么整个查询可能都不会使用索引。例如：

EXPLAIN SELECT * FROM Employees WHERE (salary > 50000 OR HireDate = '2024-08-01');

5、范围条件后的列

在组合索引中，一旦遇到范围条件（如>, <, BETWEEN），后续的列将不会被用于索引的选择过程。例如：

EXPLAIN SELECT * FROM Employees WHERE Salary > 60000 AND DepartmentID = 30;

在这个查询中，Salary上的范围条件使得DepartmentID这一列不会被用于索引选择。

6、数据类型不匹配

如果查询中的数据类型与列的数据类型不匹配，索引可能不会生效。

-- 如果`DepartmentID`是整数类型，而查询中使用了字符串，这会导致索引失效
SELECT * FROM Employees WHERE DepartmentID = '20';
-- 可改写查询为:
SELECT * FROM Employees WHERE DepartmentID = 20;

7、隐式类型转换

隐式类型转换也可能导致索引失效。

-- 如果`HireDate`是日期类型，而查询中使用了字符串，这会导致隐式类型转换从而可能使索引失效
SELECT * FROM Employees WHERE HireDate = '2023-01-01';
-- 正确的做法:
SELECT * FROM Employees WHERE HireDate = DATE '2023-01-01';

8、大量结果集

如果查询返回的结果集非常大（例如超过表的一定比例），数据库优化器可能会选择全表扫描而不是使用索引。这是因为全表扫描在这种情况下可能更高效。

9、使用 NOT IN 和 NOT EXISTS

使用NOT IN和NOT EXISTS子查询时，索引可能不会生效。

SELECT * FROM Employees WHERE DepartmentID NOT IN (SELECT DepartmentID FROM Departments WHERE location = 'New York');

10、使用 ORDER BY 和 GROUP BY

如果ORDER BY或GROUP BY中的列不在索引中，或者顺序与索引顺序不一致，索引可能不会生效。

-- 如果只有`LastName`上有索引，而没有包含`FirstName`，那么 `ORDER BY`可能不会使用索引
SELECT * FROM Employees ORDER BY LastName, FirstName;
-- 可以创建一个复合索引来解决这个问题:
CREATE INDEX idx_LastName_FirstName ON Employees (LastName, FirstName);