面试题：MySQL复杂场景下索引对性能的影响

Question

在一个包含多表关联（例如订单表、用户表、商品表）且数据量较大的复杂场景中，简述索引是如何影响查询性能的，分别举例说明合适的索引设计和不当的索引设计对性能的影响。

Answer 1

索引对查询性能的影响

1. 合适的索引设计提升性能：在多表关联且数据量大的场景下，合适的索引能大幅提升查询性能。例如在订单表（orders）中，若经常依据用户ID（user_id）与用户表（users）关联，并且按订单创建时间（create_time）查询近期订单。给orders表的user_id和create_time字段创建联合索引（user_id, create_time）。当执行类似SELECT * FROM orders JOIN users ON orders.user_id = users.id WHERE orders.create_time > '2023 - 01 - 01' AND orders.user_id = 123;这样的查询时，数据库能利用此联合索引快速定位到符合条件的订单记录，减少全表扫描，从而显著提高查询速度。因为索引有序存储，数据库可以通过索引快速找到满足条件的行，减少磁盘I/O操作。
2. 不当的索引设计降低性能：若在订单表中对订单描述（order_description）这种长文本且很少用于查询过滤的字段创建索引，不仅会增加索引存储开销，还可能降低查询性能。因为长文本索引占用空间大，维护索引的成本高。当对表进行插入、更新或删除操作时，数据库需要同时更新索引，这会耗费更多时间。例如执行UPDATE orders SET order_description = 'new description' WHERE order_id = 456;操作时，由于存在该不当索引，数据库更新索引的时间会拖慢整个更新操作的速度。另外，如果索引列选择性差（如性别字段，只有男、女两种值），索引也无法有效过滤数据，查询时仍可能进行全表扫描，无法提升性能。