面试题：MySQL索引在复杂多条件查询及动态查询中的应用

Question

在一个电商系统中，有商品表products，包含众多字段如product_id（主键）、category_id、brand_id、price、stock、is_on_sale等。业务需求包括：1. 根据不同的category_id、brand_id组合以及价格区间、库存数量范围进行商品查询；2. 部分查询条件可能是动态的（即用户可以选择不同的条件组合进行查询）。请设计一套完整的索引策略，既要满足各种复杂查询需求，又要考虑到系统性能和索引维护成本。并说明在实际应用中，如何通过数据库层面和应用层面的优化来确保查询效率，尤其是对于动态查询部分。

Answer 1

索引策略设计

1. 复合索引：
   • 创建一个复合索引 (category_id, brand_id, price, stock)。这样的索引结构可以有效地支持根据不同的 category_id、brand_id 组合以及价格区间、库存数量范围的查询。例如，在查询语句 SELECT * FROM products WHERE category_id =? AND brand_id =? AND price BETWEEN? AND? AND stock BETWEEN? AND?; 中，这个复合索引可以利用索引的前缀匹配原则快速定位到符合条件的数据。
2. 单列索引：
   • 对于 is_on_sale 字段，创建单列索引 (is_on_sale)。因为在某些查询场景下，可能仅根据商品是否在售进行筛选，单列索引可以满足这一需求。

数据库层面优化

1. 查询优化器：合理使用数据库的查询优化器。不同的数据库（如 MySQL、Oracle 等）都有自己的查询优化器。确保数据库配置参数合理，使优化器能够准确估计成本，选择最优的执行计划。例如，在 MySQL 中，可以通过调整 innodb_buffer_pool_size 等参数来提高查询性能。
2. 分区表：如果数据量非常大，可以考虑对 products 表进行分区。比如按 category_id 或 brand_id 进行分区。这样在查询时，数据库可以快速定位到相关分区，减少扫描的数据量，提高查询效率。例如，对于按 category_id 分区的表，当查询某个 category_id 的商品时，只需要在对应的分区中查找，而不需要扫描整个表。

应用层面优化

1. 缓存：在应用层使用缓存机制，如 Redis。对于一些不经常变化的查询结果进行缓存。例如，当用户查询某个 category_id 和 brand_id 组合且价格、库存范围固定的商品列表时，如果结果在一定时间内不会改变，可以将查询结果缓存到 Redis 中。当下次相同查询再次发起时，直接从缓存中获取数据，减少数据库的压力，提高查询效率。
2. 预查询：对于一些常见的动态查询条件组合，可以在系统空闲时进行预查询，并将结果缓存起来。当用户真正发起查询时，直接从缓存中获取结果，从而提高响应速度。例如，统计出每周热门的 category_id 和 brand_id 组合，在系统凌晨空闲时进行预查询并缓存结果，白天用户查询相关组合时可以快速响应。