İndekslerin Firebird SQL'de INSERT, UPDATE ve DELETE performansına olumsuz etkisi

Genel olarak, endeksler ciddi bir veritabanı için gereklidir, çünkü WHERE koşulu içeren sorguları (SELECT, UPDATE, MERGE, DELETE vb.) hızlandırmak için kritik öneme sahiptirler. Ancak, her endeksin bir maliyeti vardır ve bu maliyet, endeksli ve endekssiz alanlardaki INSERT/UPDATE/DELETE işlemlerinin hızını karşılaştırdığımızda açıkça görülür.

Bu makalede, birkaç benzersiz anahtara sahip endeksin işlem hızı üzerindeki etkisini göstereceğiz.

Test veritabanı

Basit bir Firebird veritabanı oluşturalım:

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;

Kötü endekslerle ilgili sorunu göstermek için aşağıdaki işlemleri gerçekleştirelim:

Önce tabloya 1 milyon kayıt ekleyelim
Sonra bu kayıtları güncelleyelim
Ardından tüm kayıtları silelim
Ve son olarak, tablodan SELECT count(*) çalıştıralım

Aşağıdaki komutlar açıklanan işlemleri gerçekleştirir:

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;

Çalıştıralım ve sonuçları daha sonraki analizler için saklayalım.

Bundan sonra aynı yapıya sahip başka bir tablo oluşturalım - ve MANORWOMAN sütunu için endeks ekleyelim

Code

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

Yukarıdaki betikte görebileceğiniz gibi, bu sütuna yalnızca 0 veya 1 tamsayı değerleri ekliyoruz. Böyle bir endeks select sorguları için işe yaramaz ve teorik olarak tüm veritabanı geliştiricileri bu tür kötü endekslerden kaçınmalıdır (tablodaki değerlerin dengesiz dağılımı gibi çok özel durumlar hariç), ancak pratikte 2 benzersiz değere hatta 1 değere sahip birçok endeks vardır.

Sonra bu endeksle betiği tekrarlayalım ve sonuçları karşılaştıralım - aşağıdaki tabloya bakın:


MANORWOMAN için endeks olmadan	MANORWOMAN için endeks ile
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 için endeks olmadan

MANORWOMAN için endeks ile

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

Yani, kötü endeks, ekleme veya güncelleme sırasında performansı yaklaşık 2 kat azaltır. Ayrıca, optimal olmayan endeksin yazma ve kayıt getirme sayısını büyük ölçüde artırdığını görebiliriz.

Bu örnek veritabanı için (MANORWOMAN için kötü endeksle) istatistikleri alalım ve bazı ayrıntılar bulmaya çalışalım. İstatistikleri toplamak için aşağıdaki komutu çalıştırıyoruz:

Code

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

TABLEIND1 tablosu ve endeks istatistikleri bölümü ilgi çekici görünüyor, ancak bize ne gibi yararlı bilgiler veriyor?

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

Gösterilen sayıların ve yüzde değerlerinin anlamını anlamak için, veritabanı istatistiklerinin görsel yorumunu sunan HQbird Database Analyst kullanabiliriz:

Reports/View recommendations seçeneğine tıklayarak bu endeks için uygun açıklamayı bulabiliriz:

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%

Üretim veritabanlarında sıklıkla veritabanı performansını büyük ölçüde etkileyebilecek birçok kötü endeks görebiliriz. Bu örnekte, 13 milyon kayıtlı ve büyük olasılıkla işe yaramaz olan ve Firebird performansını büyük ölçüde azaltan 7 kötü endekse sahip bir tablo görebiliriz:

HQbird Database Analyst endeksleri «Useless» (yani yalnızca 1 değere sahip) olarak vurguluyorsa, bu tür endekslerin mümkünse derhal kaldırılması önerilir.

Endeks Bad (birkaç değer) olarak vurgulanıyorsa, silinmesi de düşünülmelidir, ancak dikkatli olunmalıdır: kötü endeksin, belirli bir SQL sorgusunda hızlı çalışması için belirli bir endeks kombinasyonu (kötü olan dahil) gerektiren bazı özel sorgularda kullanılması mümkündür.

Elbette, bu SQL sorguları kötü endeks olmadan daha optimal bir yürütme planı kullanacak şekilde yeniden yazılmalıdır. Bu tür sorguları bulmak için SQL planlarının denetimi yapılmalıdır: en kolay yol, HQbird PerfMon ile bir izleme oturumu çalıştırmak ve SQL plan günlüğünü etkinleştirmek, ardından belirli kötü endeksler için SQL planlarını aramaktır.