面试题：MySQL中索引失效场景下的执行计划分析与优化策略

Question

已知有一个表`employees`，包含`employee_id`、`name`、`department`、`salary`等字段，在`department`和`salary`字段上分别建有索引。以下查询语句在某些情况下索引会失效：`SELECT * FROM employees WHERE department LIKE '%tech%' AND salary > 5000;`。请分析在哪些场景下索引会失效，使用`EXPLAIN`分析执行计划，阐述如何改写查询语句或调整表结构、索引策略来优化查询性能。

Answer 1

索引失效场景分析

1. LIKE '%tech%'部分：在department字段使用LIKE '%tech%'，这属于模糊匹配，且通配符在开头。数据库索引无法在这种情况下有效利用，因为索引是基于有序数据结构，无法快速定位以tech结尾的数据，导致全表扫描，索引失效。
2. 联合索引使用问题：虽然department和salary分别建有索引，但该查询语句中，由于department字段索引失效，无法利用联合索引优化，即使salary字段条件本身可使用索引，也会因前面的条件导致整体索引失效。

EXPLAIN分析执行计划

通过EXPLAIN SELECT * FROM employees WHERE department LIKE '%tech%' AND salary > 5000;，可能看到执行计划如下：

• id：表示查询的序列号。
• select_type：通常为SIMPLE，表示简单查询。
• table：显示操作的表为employees。
• partitions：如果表是分区表，会显示相关分区信息，否则为NULL。
• type：可能为ALL，表示全表扫描，因为索引失效。
• possible_keys：显示可能使用的索引，这里会列出department和salary索引。
• key：为NULL，表示实际没有使用索引。
• key_len：NULL。
• ref：NULL。
• rows：表示预估需要扫描的行数，可能是全表行数。
• filtered：预估满足条件的行占总行数的百分比。

优化方案

改写查询语句

1. 改变LIKE匹配方式：如果业务允许，将LIKE '%tech%'改为LIKE 'tech%'，这样可以利用department字段的索引。例如：SELECT * FROM employees WHERE department LIKE 'tech%' AND salary > 5000;
2. 使用全文索引：如果模糊匹配必须以通配符开头，可以考虑使用全文索引。首先，修改表结构添加全文索引：

ALTER TABLE employees ADD FULLTEXT(department);

然后使用MATCH AGAINST语法查询：

SELECT * FROM employees WHERE MATCH(department) AGAINST('tech' IN NATURAL LANGUAGE MODE) AND salary > 5000;

调整表结构和索引策略

1. 联合索引：如果经常以department和salary同时作为查询条件，可以创建联合索引。先删除原有的两个单索引（如果不需要单索引查询）：

DROP INDEX department_idx ON employees;
DROP INDEX salary_idx ON employees;

然后创建联合索引：

CREATE INDEX combined_idx ON employees(department, salary);

这样在查询时，如果department字段条件可使用索引（如LIKE 'tech%'），则可以利用联合索引对salary字段条件进一步筛选，提高查询性能。但要注意联合索引的最左前缀原则。 2. 冗余字段和索引：如果某些查询非常频繁，可以考虑添加冗余字段。例如，添加一个department_short字段，存储department字段的短摘要信息，并且针对这个新字段创建合适的索引，用于满足特定的查询需求，避免对原字段进行复杂的模糊匹配导致索引失效。