南光集团的ERP系统中，库存查询（如按仓库、商品类型、状态查询）存在性能瓶颈。请设计数据库优化方案（如索引优化、分库分表、物化视图），说明如何提升查询效率，并简述优化后的性能测试方法。
南光集团信息技术类难度：中等
答案
1) 【一句话结论】针对库存查询性能瓶颈，通过构建多维度复合索引提升单表查询效率，结合分库分表解决数据量过大问题，并引入物化视图预计算高频查询结果，综合提升查询响应速度。
2) 【原理/概念讲解】
数据库索引类似“图书馆的书签”，能快速定位满足条件的记录，避免全表扫描。例如按仓库、商品类型、状态查询，创建复合索引（仓库ID、商品类型ID、状态）可加速查询。
分库分表是将数据按维度拆分到多个数据库或表，当数据量超单库容量（假设单库最大1000万行）时，按仓库ID拆分数据，减少单表数据量，提升查询效率。
物化视图是预计算并存储复杂查询结果，类似“预打印的报告”，减少实时计算时间，适用于高频复杂查询（如每日库存汇总）。
3) 【对比与适用场景】
| 优化方案 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 索引优化 | 为表字段创建数据结构，加速查询 | 改善读性能，可能影响写性能（索引维护） | 单表查询效率低（多条件过滤） | 避免过度索引，影响写操作 |
| 分库分表 | 按维度拆分数据到多个库/表 | 减少单表数据量，提升并发 | 数据量超单库容量（百万级以上） | 跨库查询复杂，需分布式事务支持（可选） |
| 物化视图 | 预计算并存储复杂查询结果 | 预计算，减少实时计算 | 高频复杂查询（汇总、统计） | 需定期刷新，避免数据延迟 |
4) 【示例】
假设库存表（inventory）字段：warehouse_id（仓库ID）、product_type（商品类型）、status（状态）、quantity（数量）。
- 索引优化：创建复合索引
CREATE INDEX idx_warehouse_product_status ON inventory(warehouse_id, product_type, status); - 分库分表：按
warehouse_id分库，仓库1-1000在db1，1001-2000在db2，创建分表策略（如inventory_warehouse1、inventory_warehouse2），查询时按warehouse_id路由。 - 物化视图：预计算每日库存汇总
CREATE MATERIALIZED VIEW daily_inventory_summary AS SELECT warehouse_id, product_type, SUM(quantity) AS total_qty FROM inventory GROUP BY warehouse_id, product_type;并设置定期刷新REFRESH MATERIALIZED VIEW daily_inventory_summary;
5) 【面试口播版答案】
面试官您好，针对库存查询性能瓶颈，我的优化思路是：首先，通过构建多维度复合索引提升单表查询效率，比如为仓库、商品类型、状态字段创建复合索引，避免全表扫描；其次，当数据量过大时，采用分库分表策略，按仓库维度拆分数据，减少单表数据量；最后，引入物化视图预计算高频查询结果，比如每日库存汇总，减少实时计算时间。具体来说，比如创建索引idx_warehouse_product_status，分库分表按warehouse_id拆分表，物化视图预存汇总结果。这样能显著提升查询响应速度，测试时可通过压力测试工具（如JMeter）模拟并发查询，对比优化前后的响应时间、QPS等指标。
6) 【追问清单】
- 问：如何选择索引字段？多条件查询时，索引列顺序如何确定？
回答要点：根据查询条件频率和字段选择性，优先选择选择性高（不同值多的）字段，并按查询条件顺序排列，比如仓库ID（维度）在前，商品类型（次维度）次之，状态（过滤条件）最后。 - 问：分库分表后，跨仓库的汇总查询如何处理？
回答要点：可通过分布式事务或中间层（如消息队列）聚合数据，或者优化物化视图，按汇总维度预计算跨库结果。 - 问：物化视图的刷新频率如何确定？会不会影响实时性？
回答要点：根据数据变化频率，比如库存数据变化慢（每日更新），可设置每日刷新；若变化快，可设置实时刷新或增量刷新（只更新变化数据）。 - 问：索引优化是否会影响写性能？如何平衡？
回答要点：索引维护会增加写操作开销，可通过监控索引使用情况，避免过度索引，或者采用覆盖索引（查询所需字段都在索引中）减少回表。
7) 【常见坑/雷区】
- 过度索引：创建过多索引导致写性能下降，应仅针对高频查询字段建索引。
- 分库分表策略不当：未按查询模式分表，导致查询仍需扫描多个表，反而降低效率。
- 物化视图未及时刷新：导致查询结果与实时数据偏差，影响业务决策。
- 索引选择错误：如选择低选择性字段作为索引前缀，导致索引失效。
- 未考虑并发写：分库分表后，并发写操作可能因锁竞争导致性能下降，需评估并发场景。