面试题：MySQL列前缀索引应用场景分析

Question

在一个电子商务数据库中，有一个`products`表，包含`product_name`（`VARCHAR(100)`）、`price`（`DECIMAL(10, 2)`）等列。在什么业务场景下，你会考虑对`product_name`列创建前缀索引，如何确定合适的前缀长度，并且说明这种索引创建方式对插入、更新操作有何影响？

Answer 1

业务场景

1. 模糊查询：当经常使用LIKE 'keyword%'这样以某个词开头的模糊查询时，前缀索引可以提高查询效率。例如，用户在电商平台搜索框输入关键词，系统需要快速匹配出相关产品名称，此时前缀索引能发挥作用。
2. 排序：如果需要按照product_name的部分内容进行排序，前缀索引也有助于提升排序性能。

确定合适前缀长度

1. 数据分布分析：通过SELECT COUNT(DISTINCT LEFT(product_name, length)) / COUNT(*) FROM products;来计算不同前缀长度下的唯一值比例。这里length从较小值如3开始逐渐增加。当唯一值比例接近全列唯一值比例时，此时的前缀长度比较合适。
2. 权衡查询性能和索引空间：较长的前缀长度能提供更精确的匹配，但会占用更多索引空间，影响插入和更新性能。要在查询性能提升和索引存储开销之间找到平衡。

对插入、更新操作的影响

1. 插入操作：
   • 性能下降：插入新记录时，数据库不仅要写入数据，还要更新前缀索引。较长的前缀长度使得索引更新成本更高，导致插入操作变慢。
2. 更新操作：
   • 性能下降：如果更新涉及product_name列，同样需要更新前缀索引。而且可能因为索引结构的调整，导致其他相关记录的索引位置变化，进一步增加更新操作的开销，使得更新性能降低。