Firebird SQL中索引对INSERT、UPDATE和DELETE性能的负面影响

一般来说，索引对于任何严肃的数据库都是必需的，因为它们对于加速带有 WHERE 子句的查询（SELECT、UPDATE、MERGE、DELETE 等）至关重要。然而，每个索引都有其代价，当我们比较在带索引和不带索引的字段上执行 INSERT/UPDATE/DELETE 操作的速度时，这种代价就显而易见了。

在本文中，我们将演示带有几个唯一键的索引对操作速度的影响。

测试数据库

让我们创建一个简单的 Firebird 数据库：

Code

CREATE DATABASE “E:\TESTFIREBIRDINDEX.FDB” USER “SYSDBA” PASSWORD “masterkey”;
CREATE TABLE TABLEIND1 (
    I1            INTEGER NOT NULL PRIMARY KEY,
    NAME          VARCHAR(250),
    MANORWOMAN  SMALLINT
);
CREATE GENERATOR G1;
SET TERM ^ ;

create or alter procedure INS1MLN
returns (
    INSERTED_CNT integer)
as
BEGIN
inserted_cnt = 0;
WHILE (inserted_cnt <1000000) DO
BEGIN
 Insert into tableind1(i1, name, manorwoman) values(gen_id(g1,1), 'TEST name', (:inserted_cnt - (:inserted_cnt/2)*2));
 inserted_cnt=inserted_cnt+1;
END
suspend;
END^
SET TERM ; ^
GRANT INSERT ON TABLEIND1 TO PROCEDURE INS1MLN;
GRANT EXECUTE ON PROCEDURE INS1MLN TO SYSDBA;
COMMIT;

为了演示不良索引带来的问题，让我们执行以下操作：

首先，向表中插入 100 万条记录
然后更新这些记录
之后，删除所有记录
最后，运行 SELECT count(*) from the table

下面的命令执行上述操作：

Code

set stat on;  /*enable display of statistics in isql*/
select * from ins1mln;
update tableind1 SET MANORWOMAN  = 3;
delete from tableind1;
select count(*) from tableind1;

让我们运行它并保留结果以供进一步分析。

之后，让我们创建另一个具有相同结构的表–并为 MANORWOMAN 列添加索引

Code

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

正如你在上面的脚本中所见，我们只向此列插入 0 或 1 的整数值。这样的索引对于 select 查询是无用的，理论上，所有数据库开发人员都应该避免这种不良索引（除了表中值分布不平衡的特殊情况），但在实践中，有许多索引只有 2 个唯一值，甚至只有 1 个。

然后让我们使用此索引重复脚本并比较结果–参见下表：


不带 MANORWOMAN 索引	带 MANORWOMAN 索引
SQL> set stat on; /show statistics/ SQL *> select from ins1mln; INSERTED_CNT ============ 1000000 Current memory = 10487216 Delta memory = 80560 Max memory = 12569996 Elapsed time= 13.33 sec Buffers = 2048 Reads = 0 Writes 18756 Fetches = 7833503 SQL > update tableind1 SET MANORWOMAN = 3; Current memory = 76551788 Delta memory = 66064572 Max memory = 111442520 Elapsed time= 15.04 sec Buffers = 2048 Reads = 16166 Writes 15852 Fetches = 6032307 SQL> delete from tableind1; Current memory = 76550240 Delta memory = -1548 Max memory = 111442520 Elapsed time= 3.27 sec Buffers = 2048 Reads = 16147 Writes 16006 Fetches = 5032277 SQL> select count() from tableind1; COUNT ============ 0 Current memory = 76552064 Delta memory = 1824 Max memory = 111442520 Elapsed time= 1.35* sec Buffers = 2048 Reads = 16021 Writes 1791 Fetches = 2032278	SQL> set stat on; /show statistics/ SQL> *select from ins1mln; INSERTED_CNT ============ 1000000 Current memory = 10484140 Delta memory = 75524 Max memory = 12569996 Elapsed time= 23.94 sec Buffers = 2048 Reads = 1 Writes 23942 Fetches = 11459599 SQL> update tableind1 SET MANORWOMAN = 3; Current memory = 76548712 Delta memory = 66064572 Max memory = 111439444 Elapsed time= 29.30 sec Buffers = 2048 Reads = 16167 Writes 19492 Fetches = 10035948 SQL> delete from tableind1; Current memory = 76547164 Delta memory = -1548 Max memory = 111439444 Elapsed time= 3.41 sec Buffers = 2048 Reads = 16147 Writes 15967 Fetches = 5032277 SQL> select count() from tableind1; COUNT ============ 0 Current memory = 76548988 Delta memory = 1824 Max memory = 111439444 Elapsed time= 0.69* sec Buffers = 2048 Reads = 16021 Writes 1901 Fetches = 2032278

不带 MANORWOMAN 索引

带 MANORWOMAN 索引

SQL> set stat on; /*show statistics*/
SQL > select * from ins1mln;
INSERTED_CNT
============
1000000
Current memory = 10487216
Delta memory = 80560
Max memory = 12569996
Elapsed time= 13.33 sec
Buffers = 2048
Reads = 0
Writes 18756
Fetches = 7833503
SQL > update tableind1 SET MANORWOMAN = 3;
Current memory = 76551788
Delta memory = 66064572
Max memory = 111442520
Elapsed time= 15.04 sec
Buffers = 2048
Reads = 16166
Writes 15852
Fetches = 6032307
SQL> delete from tableind1;
Current memory = 76550240
Delta memory = -1548
Max memory = 111442520
Elapsed time= 3.27 sec
Buffers = 2048
Reads = 16147
Writes 16006
Fetches = 5032277
SQL> select count(*) from tableind1;
COUNT
============
0
Current memory = 76552064
Delta memory = 1824
Max memory = 111442520
Elapsed time= 1.35 sec
Buffers = 2048
Reads = 16021
Writes 1791
Fetches = 2032278

SQL> set stat on; /*show statistics*/
SQL> select * from ins1mln;
INSERTED_CNT
============
1000000
Current memory = 10484140
Delta memory = 75524
Max memory = 12569996
Elapsed time= 23.94 sec
Buffers = 2048
Reads = 1
Writes 23942
Fetches = 11459599
SQL> update tableind1 SET MANORWOMAN = 3;
Current memory = 76548712
Delta memory = 66064572
Max memory = 111439444
Elapsed time= 29.30 sec
Buffers = 2048
Reads = 16167
Writes 19492
Fetches = 10035948
SQL> delete from tableind1;
Current memory = 76547164
Delta memory = -1548
Max memory = 111439444
Elapsed time= 3.41 sec
Buffers = 2048
Reads = 16147
Writes 15967
Fetches = 5032277
SQL> select count(*) from tableind1;
COUNT
============
0
Current memory = 76548988
Delta memory = 1824
Max memory = 111439444
Elapsed time= 0.69 sec
Buffers = 2048
Reads = 16021
Writes 1901
Fetches = 2032278

因此，不良索引在插入或更新时会使性能降低大约 2 倍。此外，我们还可以看到，非最优索引大大增加了写入次数和记录读取次数。

让我们获取此示例数据库（带有 MANORWOMAN 的不良索引）的统计信息，并尝试找到一些细节。为了收集统计信息，我们运行以下命令：

Code

gstat -r e:\testfirebirdindex.fdb > e:\teststat.txt

TABLEIND1 表和索引统计部分看起来很有趣，但它给了我们什么有用的信息呢？

Code

TABLEIND1 (128)
    Primary pointer page: 166, Index root page: 167
    Average record length: 0.00, total records: 1000000
    Average version length: 27.00, total versions: 1000000, max versions: 1
    Data pages: 16130, data page slots: 16130, average fill: 93%
    Fill distribution:
       0 - 19% = 1
      20 - 39% = 0
      40 - 59% = 0
      60 - 79% = 0
      80 - 99% = 16129

    Index RDB$PRIMARY1 (0)
      Depth: 3, leaf buckets: 1463, nodes: 1000000
      Average data length: 1.00, total dup: 0, max dup: 0
      Fill distribution:
           0 - 19% = 0
          20 - 39% = 0
          40 - 59% = 0
          60 - 79% = 1
          80 - 99% = 1462

    Index TABLEIND1_IDX1 (1)
      Depth: 3, leaf buckets: 2873, nodes: 2000000
      Average data length: 0.00, total dup: 1999997, max dup: 999999
      Fill distribution:
           0 - 19% = 0
          20 - 39% = 1
          40 - 59% = 1056
          60 - 79% = 0
          80 - 99% = 1816

为了理解所显示数字和百分比值的含义，我们可以使用 HQbird Database Analyst，它提供了数据库统计信息的可视化解释：

通过点击 Reports/View recommendations，我们可以找到针对此索引的适当解释：

Bad indices count: 1. By `bad` we name indices with many duplicate keys (90% of all keys) and big groups of equal keys (30% of all keys). Big groups of equal keys slowdown garbage collection - MaxEquals here is % of max groups of keys having equal values. Index search for such an index is not efficient. You can drop such indices (if they are not on FK constraints).

Index ( Relation) Duplicates MaxEquals TABLEIND1.IDX1 (TABLEIND1) : 100%, 50%

在生产数据库中，我们经常可以看到许多不良索引，它们会极大地影响数据库性能。在这个例子中，我们可以看到一张拥有 1300 万条记录的表，其中有 7 个不良索引，这些索引（很可能）是无用的，并且大大降低了 Firebird 的性能：

如果 HQbird Database Analyst 将索引标记为「Useless」（即它们只有 1 个值），则建议在可能的情况下立即删除这些索引。

如果索引被标记为 Bad（其中有几个值），也应考虑删除，但要谨慎：有可能不良索引被用于某个特定的 SQL 查询中，该查询需要特定的索引组合（包括不良索引）才能快速运行。

当然，这些 SQL 查询应该被重写，以使用没有不良索引的更优执行计划。为了找到此类查询，应该进行 SQL 计划审计：最简单的方法是使用 HQbird PerfMon 运行跟踪会话并启用 SQL 计划日志记录，然后搜索特定不良索引的 SQL 计划。