面试题：MySQL索引在高并发查询优化及索引失效场景下的处理

Question

在一个高并发的电商系统中，有商品表products（包含字段product_id、product_name、price、stock等），经常会有大量的查询请求，如按价格区间查询商品、按库存查询商品等。同时，业务需求会导致部分查询出现索引失效的情况。请阐述如何设计索引以应对高并发查询，以及当索引失效时，有哪些优化手段可以保证查询性能？举例说明索引失效的场景及对应的解决办法。

Answer 1

索引设计应对高并发查询

1. 复合索引：
   • 对于按价格区间查询商品，例如查询价格在[min_price, max_price]之间的商品，可以创建复合索引 (price, product_id) 。price放在前面是因为查询条件主要基于价格，product_id作为辅助列，这样在查询时可以快速定位到价格区间，并且在数据有重复时能进一步区分。
   • 对于按库存查询商品，比如查询库存大于某个值的商品，可以创建复合索引 (stock, product_id) 。库存列在前，同样是为了快速定位符合库存条件的数据，product_id用于进一步区分重复数据。
2. 覆盖索引：
   • 如果经常查询商品的名称和价格，可以创建覆盖索引 (product_name, price) 。这样查询时，索引中就包含了所需的列，不需要回表操作，减少了I/O开销，在高并发场景下能提高查询性能。

索引失效时的优化手段

1. 查询重写：
   • 例如原查询语句是 SELECT * FROM products WHERE LEFT(product_name, 3) = 'abc'; 这种使用函数的方式会导致索引失效。可以重写为 SELECT * FROM products WHERE product_name LIKE 'abc%'; 这样就能利用索引。
2. 使用覆盖索引：
   • 当回表操作开销较大导致性能下降时，即使原索引失效，通过创建覆盖索引，使查询能直接从索引获取数据，而不需要回表，从而提升性能。
3. 缓存：
   • 对于一些经常查询且数据变动不频繁的结果，可以使用缓存（如Redis）。先从缓存中查询，如果缓存中有结果，直接返回，减少对数据库的查询压力，即使索引失效，也能保证快速响应。

索引失效场景及解决办法举例

1. 函数操作场景：
   • 场景：SELECT * FROM products WHERE ROUND(price, 0) = 100; 对price列使用ROUND函数，会导致索引失效。因为数据库无法使用索引快速定位到满足函数计算后结果的数据。
   • 解决办法：将查询改为 SELECT * FROM products WHERE price >= 99.5 AND price < 100.5; 避免使用函数，利用价格区间来替代函数操作，这样可以利用price列上的索引。
2. 数据类型不匹配场景：
   • 场景：表中price字段为DECIMAL类型，查询语句为 SELECT * FROM products WHERE price = '100'; 这里将数值类型与字符串类型进行比较，会导致索引失效。因为数据库会进行隐式类型转换，转换后无法利用索引。
   • 解决办法：将查询改为 SELECT * FROM products WHERE price = 100; 确保数据类型匹配，这样就能使用price列上的索引。