面试题：MySQL性能优化之索引优化

Question

在MySQL中，假设有一张员工表employees，包含字段id（主键）、name、age、department，经常会按照name和department联合查询。请阐述如何创建索引来优化这种查询，并且说明索引创建不当可能会带来哪些性能问题。

Answer 1

创建索引优化查询

可以在name和department字段上创建联合索引，使用如下SQL语句：

CREATE INDEX idx_name_department ON employees (name, department);

这样在按照name和department联合查询时，MySQL可以利用该索引快速定位到符合条件的记录，大大提高查询效率。

索引创建不当带来的性能问题

1. 插入、更新和删除性能下降：因为每当执行插入、更新或删除操作时，数据库不仅要更新数据，还要更新索引。过多或不合理的索引会导致这些操作需要更多的I/O和CPU资源，从而降低性能。例如，如果在一个经常进行插入操作的表上创建了大量索引，每次插入都需要对多个索引进行调整，插入速度会明显变慢。
2. 索引空间占用过大：索引本身需要占用存储空间。过多的索引会导致数据库文件大小显著增加，可能会超出磁盘空间限制，同时也会增加备份和恢复的时间与成本。
3. 查询优化器选择错误索引：当存在多个索引时，查询优化器可能会选择错误的索引来执行查询，尤其是在索引选择性较差（即索引列中不同值的比例较低）的情况下。这可能导致查询性能反而不如没有索引时，因为优化器选择了一个效率不高的索引进行扫描。