当前位置：网站首页>数据库索引对写入操作的影响

数据库索引对写入操作的影响

2022-07-21 21:13:00 【Navicat中国】

在上周的文章中，我们了解了不良索引的后果，以及如何选择将哪些列作为聚集索引的一部分。在本文中，我们将介绍为某些操作提供更好性能的相同索引如何增加其他操作的开销。

聚集索引如何影响 INSERT、UPDATE 和 DELETE

一般来说，在表上建立索引会带来额外的成本，即使用更多的数据页和内存。在聚集表上，索引的影响更加明显。聚集表是一种使用聚集索引来存储基于聚集索引键值排序的数据行的表。SELECT 语句在聚集表上的执行速度明显更快，而 INSERT、UPDATE 和 DELETE 需要更多时间，因为不仅要更新数据，而且还要更新索引。对于聚集索引，时间增加比单个索引更显着，因为记录必须在数据页中保持正确的顺序。无论是插入新记录，还是删除或更新现有记录，通常都需要对记录进行重新排序。

INSERT 往往在没有任何索引的表上执行得最快。这是因为既不需要重新排序也不需要更新索引。在同一个表上，执行 UPDATE 和 DELETE 是最昂贵的。原因是数据库需要大部分时间来查找表中的特定记录。

相反，对于具有非最佳聚集索引的表，其次是具有非聚集索引或根本没有索引的表，成本可能更高。

对于 SELECT 语句，你可以通过以下方式降低执行成本：

指定返回列的列表
在用主键列创建聚集索引的表上执行语句

DML 影响示例

我们可以在下面的 album 表中看到索引对 DML（Data Manipulation Language，数据操纵语言）语句的影响，其定义显示了大量的索引：

在 Navicat 中，我们可以在表设计器的索引选项卡中查看索引的详细信息：

你可以通过下拉列表选择索引类型和方法，这些下拉列表专门针对数据库类型进行定制。以下是 MySQL 7 的可用选项：

通过运行一个简单的基准测试，我们可以使用仅包含主索引的原始定义测试当前 album 表的插入率：

以下是计时结果：

在我的非正式简单批量测试中，将数据插入具有附加索引的表的速度要慢四倍。还有其他因素会导致速度变慢；然而，我的结果提供了一个有代表性的指标，即向表添加索引对写入性能有直接影响。

总结

如上图所示，索引可以加速某些查询并减慢其他查询。在本文中，我们提供了一些关于聚集索引和非聚集索引的基本指南，以及哪些列是构建索引的首选，哪些应该避免。在索引带来的好处和开销之间找到适当的平衡可为你的查询和存储过程提供最佳性能。如果你对 Navicat Premium 感兴趣，可以免费试用 14 天！