Tác động tiêu cực của chỉ mục đến hiệu suất INSERT, UPDATE và DELETE trong Firebird SQL

Nói chung, chỉ mục là cần thiết cho bất kỳ cơ sở dữ liệu nghiêm túc nào, vì chúng rất quan trọng để tăng tốc các truy vấn có mệnh đề WHERE (SELECT, UPDATE, MERGE, DELETE, v.v.). Tuy nhiên, mỗi chỉ mục đều có chi phí, và chi phí này trở nên rõ ràng khi chúng ta so sánh tốc độ của các thao tác INSERT/UPDATE/DELETE trên các trường có và không có chỉ mục.

Trong bài viết này, chúng tôi sẽ chứng minh tác động của chỉ mục với một vài khóa duy nhất đến tốc độ của các thao tác.

Cơ sở dữ liệu thử nghiệm

Hãy tạo cơ sở dữ liệu Firebird đơn giản:

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;

Để chứng minh vấn đề với các chỉ mục kém, hãy thực hiện các thao tác sau:

Đầu tiên, chúng ta chèn 1 triệu bản ghi vào bảng
Sau đó, chúng ta cập nhật các bản ghi này
Tiếp theo, chúng ta xóa tất cả các bản ghi
Và cuối cùng, chạy SELECT count(*) từ bảng

Các lệnh dưới đây thực hiện các thao tác được mô tả:

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;

Hãy chạy nó và giữ kết quả để phân tích thêm.

Sau đó, hãy tạo một bảng khác có cùng cấu trúc - và thêm chỉ mục cho cột MANORWOMAN

Code

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

Như bạn có thể thấy ở trên trong tập lệnh, chúng ta chỉ chèn các giá trị nguyên 0 hoặc 1 vào cột này. Chỉ mục như vậy là vô dụng cho các truy vấn select, và về lý thuyết, tất cả các nhà phát triển cơ sở dữ liệu nên tránh các chỉ mục kém như vậy (ngoại trừ trường hợp rất đặc biệt với phân bố giá trị không cân bằng trong bảng), nhưng trong thực tế, có nhiều chỉ mục có 2 giá trị duy nhất hoặc thậm chí chỉ có 1 trong số chúng.

Sau đó, hãy lặp lại tập lệnh với chỉ mục này và so sánh kết quả - xem bảng sau:


Không có chỉ mục cho MANORWOMAN	Có chỉ mục cho 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

Không có chỉ mục cho MANORWOMAN

Có chỉ mục cho 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

Vì vậy, chỉ mục kém làm giảm hiệu suất khoảng 2 lần khi chèn hoặc cập nhật. Ngoài ra, chúng ta có thể thấy rằng chỉ mục không tối ưu làm tăng đáng kể số lượng ghi và truy xuất bản ghi.

Hãy lấy số liệu thống kê cho cơ sở dữ liệu mẫu này (với chỉ mục kém cho MANORWOMAN) và cố gắng tìm một số chi tiết. Để thu thập số liệu thống kê, chúng ta chạy lệnh sau:

Code

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

Phần thống kê bảng TABLEIND1 và chỉ mục trông hấp dẫn, nhưng nó cung cấp cho chúng ta thông tin hữu ích gì?

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

Để hiểu ý nghĩa của các con số và giá trị phần trăm được hiển thị, chúng ta có thể sử dụng HQbird Database Analyst, công cụ cung cấp diễn giải trực quan về số liệu thống kê cơ sở dữ liệu:

Bằng cách nhấp vào Reports/View recommendations, chúng ta có thể tìm thấy giải thích phù hợp cho chỉ mục này:

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%

Trong các cơ sở dữ liệu sản xuất, chúng ta thường thấy nhiều chỉ mục kém, có thể ảnh hưởng lớn đến hiệu suất cơ sở dữ liệu. Trong ví dụ này, chúng ta có thể thấy một bảng có 13 triệu bản ghi có 7 chỉ mục kém, rất có thể là vô dụng và làm giảm đáng kể hiệu suất Firebird:

Nếu HQbird Database Analyst đánh dấu các chỉ mục là «Useless» (tức là chúng chỉ có 1 giá trị), các chỉ mục như vậy được khuyến nghị nên xóa ngay lập tức, nếu có thể.

Nếu chỉ mục được đánh dấu là Bad (có một vài giá trị), cũng nên cân nhắc xóa nó, nhưng cần thận trọng: có thể chỉ mục kém được sử dụng trong một truy vấn SQL cụ thể nào đó, yêu cầu sự kết hợp cụ thể của các chỉ mục (bao gồm cả chỉ mục kém) để chạy nhanh.

Chắc chắn, các truy vấn SQL này nên được viết lại để sử dụng một kế hoạch thực thi tối ưu hơn mà không có chỉ mục kém. Để tìm các truy vấn như vậy, cần thực hiện kiểm tra các kế hoạch SQL: cách dễ nhất là chạy phiên trace với HQbird PerfMon và bật ghi nhật ký kế hoạch SQL, sau đó tìm kiếm các kế hoạch SQL cho các chỉ mục kém cụ thể.