创建数据库索引时，要考虑一下这5个维度
数据库索引是帮助提升查询性能的核心工具，尤其在处理复杂查询时（如 JOIN、GROUP BY、WHERE 或 ORDER BY 操作），能够显著减少查询时间。
索引的工作原理简单而高效：通过以不同的顺序存储部分数据的副本，快速定位目标数据，类似于书籍中的目录可以迅速找到某一章节的内容。
尽管索引在提高查询效率方面具有显著优势，但并不是每个表的每一列都需要索引，因为过多索引特别是二级索引可能会引发意想不到的问题。
索引的常见弊端
以下列出了创建过多二级索引可能带来的几方面问题：
1. 额外的存储开销
索引的首要代价是额外占用存储空间。索引需要存储索引列的值以及指向表中对应行的指针，这对存储有一定要求，具体取决于表的规模和索引涉及的列数。
- 对于整数类型列，索引通常只需存储整数值；
- 对于字符串类型列，还需存储字符串内容及其长度。
当数据量较大时，多个索引会迅速增加数据库整体存储需求。尽管现代硬件存储成本不断降低，但在存储资源有限，或数据量爆炸式增长的情况下，索引带来的额外存储消耗可能成为一项令人生畏的挑战。
2. 写操作性能下降
索引的另一个代价是写入性能的下降。在表中插入、更新或删除数据时，数据库必须同步更新相关索引，这使写操作变得更慢。对于需要频繁写入的场景，应仔细评估索引对写操作的影响。
举例来说，假设一个应用程序需要进行百万条记录的批量插入操作：
- 无索引情况下：插入耗时约 10-15 秒；
- 添加多个索引后：耗时可能提升至约 2 分钟。
这说明过多索引会显著降低写入性能。如果批量插入仅偶尔发生或可以在系统非高负载时间执行，这种权衡是可以接受的。然而，如果写操作由用户直接触发并需实时响应，则必须仔细评估索引的实际价值。
3. 索引的管理与清理
未使用的索引不仅占用存储空间，还可能让查询优化器产生不必要的复杂性。定期审核数据库中的索引是保持数据库高效运行的关键。以下 SQL 查询可帮助列出潜在未使用的索引：
SELECT 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 表示该索引未被使用。当确认某些索引确实无用后，可通过以下命令安全地将其移除：
ALTER TABLE your_table_name DROP INDEX your_index_name;
但在移除前，应始终慎重考虑可能的影响，并进行充分测试。
4. 审查索引效率
有些索引虽然正在被使用，但其性能提升可能微乎其微。如果相关索引的权衡不值得，应仔细审查并决定是否保留。这可以通过以下查询获取所有索引信息，进行逐一优化：
SELECT * FROM information_schema.statistics;
通过全面了解索引的使用情况，你可以更自信地决定是否需要调整或移除某些索引。
5. 测试移除对性能的影响
在实际移除索引之前，可利用 MySQL 的 不可见索引（Invisible Indexes） 功能先测试移除某索引对性能的影响。不可见索引不会影响数据写操作，但对查询不可见。启用不可见索引方法如下：
ALTER TABLE your_table_name ALTER INDEX your_index_name INVISIBLE;
如果测试后确定该索引仍然必需，可重新将其设为可见：
ALTER TABLE your_table_name ALTER INDEX your_index_name VISIBLE;
结论
索引可以显著提升查询性能，但其代价也不可忽视。
- 索引会占用额外存储空间，尤其当数据量较大时更需关注。
- 索引会减缓写操作性能，在频繁写入的场景（比如大量实时插入或更新）需格外谨慎。
- 在数据库优化过程中过多或未优化的索引可能会增加查询优化器的复杂性。
是否应该添加索引，最终取决于应用程序的需求以及对相关代价的容忍度。在对数据库性能进行优化时，应始终根据实际测试结果加以决策。只有通过充分的测试和权衡，才能确保索引的设置达到理想效果，同时避免不必要的性能损耗。在面对性能问题时，适度放弃添加索引往往比盲目追求查询速度更为明智。
关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台
除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接
