数据库索引的弊端
数据库索引是提升查询性能的重要工具，对于在大表上执行复杂查询（如包含 JOIN、GROUP BY、WHERE 或 ORDER BY 的操作）尤其有效。
索引通过以不同的顺序存储部分数据的副本，从而实现更快速的访问，就像为一本书添加目录一样。
如果您想深入了解索引如何工作以及如何使用它们，可以查看我们关于索引的详细说明文章或视频课程。这些资源涵盖从索引和 B+树的工作原理到索引的添加时机全方面内容。
尽管索引带来的性能提升令人印象深刻，但为数据库中的每个表的每一列都添加索引并不是一个好主意，因为过多的二级索引可能会带来一些问题。
数据库索引的弊端
以下是使用过多二级索引可能导致的一些弊端：
1. 额外的存储需求
添加索引的首要问题是它需要占用额外的存储空间。具体占用量取决于表的大小和索引的列数，通常为表总大小的一小部分。基本索引仅需存储索引列的值以及指向表中行的指针：
- 对于包含整数的列，索引只需存储整数值。
- 对于包含字符串的列，索引需要存储字符串值及其长度。
当数据集较大时，为表添加多个索引可能会快速增加额外存储使用量。
2. 写操作变慢
添加索引后，表中插入、更新或删除行时索引需同步更新，这会导致写操作变慢。在添加索引之前，需仔细评估表的写操作频率，以及能否接受写入性能的下降。
例如，一个应用程序进行一次含百万条记录的批量插入：
- 无索引：耗时 10-15 秒。
- 添加多个索引后：插入时间增加到约 2 分钟。
尽管写入时间显著变慢，但索引提升了用户频繁查询的性能。这种权衡在批量插入操作发生频率较低且可以安排在低使用时间时是可以接受的。
如果插入操作由用户触发并需要等待，则需要重新评估写操作的影响。
3. 查找与移除未使用索引
保持数据库高效的关键是识别并移除未使用的索引。以下 SQL 查询可帮助发现未使用的索引（将 your_database_name 替换为您的数据库名称）：
SQLSELECT table_name, index_name, non_unique, seq_in_index, column_name, collation, cardinality, sub_part, packed, index_type, comment, index_comment
FROM information_schema.STATISTICS
WHERE table_schema = 'your_database_name'
AND index_name != 'PRIMARY'
AND (cardinality IS NULL OR cardinality = 0)
ORDER BY table_name, index_name, seq_in_index;
该查询检查每个索引的唯一值数量（cardinality）。值为 0 表示索引未使用。
移除未使用索引时，用以下 SQL：
SQLALTER TABLE your_table_name DROP INDEX your_index_name;
4. 数据库索引的审查
如果发现某些索引实际在使用，但经过思考后觉得相关权衡不值得，可以逐个审查这些索引以决定是否保留或移除。
获取当前数据库所有表的索引列表：
SQLSELECT * FROM information_schema.statistics;
5. 使用不可见索引测试移除影响
在实际移除索引之前，可以利用 MySQL 不可见索引（Invisible Indexes） 测试删除索引的效果。使用不可见索引时，索引保持原样，但对查询不可见。
设置不可见索引：
SQLALTER TABLE your_table_name ALTER INDEX your_index_name INVISIBLE;
测试完影响后，如果索引仍然需要，可以再次设为可见：
SQLALTER TABLE your_table_name ALTER INDEX your_index_name VISIBLE;
注意：
- 在 PlanetScale 中，不允许直接对生产分支进行 DDL 操作（除非禁用安全迁移，但这不推荐）。必须通过部署请求流程测试不可见索引设置。
- 如果需要还原修改或移除的索引，PlanetScale 支持通过“Revert”按钮快速恢复。
结论
尽管索引可以显著提高查询性能，但其添加需考虑权衡：
- 索引占用额外存储空间。
- 索引会减慢写操作性能。
- 索引可能会让查询优化器的工作复杂化。
索引是否值得添加，取决于应用程序的具体需求以及能接受的代价。当添加或移除索引时，始终应测试受影响的查询性能，以确保操作符合预期并不会造成性能下降。如果未看到显著的性能改善，放弃添加索引可能是更好的选择。
关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台
除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接
