面试题：MySQL B+树索引监控与性能优化关联

Question

假设你通过SHOW STATUS等方式监控到MySQL B+树索引的读命中次数持续偏低，写操作频繁且索引维护成本高，你将如何结合业务场景对索引进行优化，以提高整体数据库性能？请举例说明业务场景及对应的优化策略。

Answer 1

1. 分析业务场景

• 场景一：日志记录类业务
  • 业务特点：大量插入操作，例如系统日志、访问日志记录等，写操作频繁，读操作通常为按时间范围查询。
  • 当前索引问题：如果对每条日志记录的所有字段都建立索引，会导致写操作时索引维护成本极高，且读命中次数低，因为查询一般仅涉及时间字段。
  • 优化策略：
    • 减少不必要索引：删除对很少用于查询条件字段的索引。比如日志表中可能有一些辅助信息字段，如客户端IP等，如果很少基于此查询，可去掉该字段索引。
    • 建立复合索引：基于常用查询条件建立复合索引。例如，若经常按时间范围查询，可建立以时间字段为第一列的复合索引，如CREATE INDEX idx_time_log ON log_table (log_time, other_column);，这里other_column为可能参与查询的其他字段（若有）。
• 场景二：电商订单业务
  • 业务特点：订单数据不断新增，同时有按订单状态、下单时间等条件查询订单的需求。
  • 当前索引问题：若为每个字段都单独建立索引，写操作时索引维护开销大，且读操作时可能由于索引选择不合理导致读命中次数低。
  • 优化策略：
    • 索引合并与重建：如果有多个单字段索引，且这些字段经常一起用于查询，可考虑合并为复合索引。例如，经常按订单状态和下单时间查询，原分别有idx_status和idx_order_time两个单字段索引，可合并为CREATE INDEX idx_status_time ON order_table (order_status, order_time);
    • 覆盖索引：对于一些查询，若结果集仅包含索引列的数据，可创建覆盖索引。比如查询订单号和订单状态，可建立CREATE INDEX idx_order_status_num ON order_table (order_number, order_status);，这样查询时直接从索引中获取数据，减少回表操作，提高查询性能。

2. 其他通用优化措施

• 定期重建或优化索引：使用OPTIMIZE TABLE或ALTER TABLE... ENGINE=InnoDB等语句对表和索引进行优化，以整理碎片，提高索引效率。例如，对于日志表log_table，可定期执行OPTIMIZE TABLE log_table;
• 调整索引类型：如果某些字段取值范围有限，可考虑使用哈希索引（前提是业务查询多为等值查询）。比如订单状态字段，只有几种固定值，可尝试将其索引类型调整为哈希索引，提高查询速度，减少索引维护成本。例如，先删除原索引DROP INDEX idx_status ON order_table;，然后创建哈希索引CREATE INDEX idx_status ON order_table (order_status) USING HASH;