面试题：MySQL通配符与性能优化及索引的关系

Question

在大型的`customer`表中有`customer_name`字段，表数据量达百万级。现需频繁执行根据`customer_name`使用通配符过滤的查询（例如查找名字以特定字符串开头的客户）。从性能优化角度出发，如何设计索引来提高这类查询的效率？在使用通配符（百分号和下划线）时，索引的生效情况是怎样的？请详细分析，并给出示例SQL语句说明索引创建及查询语句的优化。

Answer 1

索引设计提高查询效率

1. 前缀索引：由于表数据量达百万级，全列索引占用空间大且维护成本高。对于以特定字符串开头的查询，可创建前缀索引。例如，假设customer_name字段一般长度较长，若多数查询是以3个字符开头进行过滤，可创建长度为3的前缀索引。
   • 创建前缀索引SQL：

CREATE INDEX idx_customer_name_prefix ON customer(customer_name(3));

2. 覆盖索引：若查询不仅要返回customer_name，还需返回其他字段，创建覆盖索引可减少回表操作，进一步提高性能。假设还需返回customer_id字段：
   • 创建覆盖索引SQL：

CREATE INDEX idx_customer_name_cover ON customer(customer_name(3), customer_id);

通配符与索引生效情况分析

1. 百分号（%）在前：当通配符%在开头，如LIKE '%keyword'，索引无法生效。因为索引是有序存储，这种查询方式无法利用索引的有序性进行快速定位，数据库需要全表扫描。
2. 百分号（%）在后：当通配符%在结尾，如LIKE 'keyword%'，索引可以生效。数据库能利用索引找到以keyword开头的数据位置，然后快速定位后续符合条件的数据。
3. 下划线（_）：下划线_代表单个字符，如LIKE 'keywo_d'，只要_不在开头，索引是可以生效的。因为数据库能通过索引找到符合前面字符条件的数据，再对单个字符位置进行匹配。

示例SQL语句优化

1. 使用前缀索引查询：假设已创建上述长度为3的前缀索引idx_customer_name_prefix，查询以'Joh'开头的客户：

SELECT customer_name FROM customer WHERE customer_name LIKE 'Joh%';

2. 使用覆盖索引查询：假设已创建上述覆盖索引idx_customer_name_cover，查询以'Joh'开头的客户及其customer_id：

SELECT customer_id, customer_name FROM customer WHERE customer_name LIKE 'Joh%';