Jeden z czytelników zapytał się, jak wygląda sprawa z wydajnością ograniczeń wprowadzanych w kluczach obcych. Zgodnie z dokumentacją, InnoDB umożliwia zdefiniowania dla klucza obcego ograniczeń typu ON UPDATE CASDADE czy też ON DELETE CASCADE. Są to mechanizmy bardzo wygodne dla projektanta bazy danych, gdyż dzięki nim baza do pewnego stopnia sama z siebie pilnuje integralności danych. W przypadku dwóch tabel połączonych takim kluczem obcym, skasowanie lub modyfikacja rekordu w pierwszej skutkować będzie odpowiednią operacją na rekordzie w drugiej tabeli. Odciążamy w ten sposób aplikację, programiści nie muszą samodzielnie dbać o integralność danych. Czy jest jednak jakiś dodatkowy koszt wprowadzenia takiego ułatwienia? Do tej pory to zagadnienie nie leżało w kręgu moich zainteresowań, ale przecież można to prosto sprawdzić. Poniżej zaprezentuję to, w jaki sposób. Uogólniając, ten sam sposób postępowania można wykorzystać do sprawdzenia różnicy w wydajności dowolnej, odpowiadającej sobie pary zapytań.
Tworzymy dwie bazy danych. Pierwsza, o nazwie `fk`, zawiera trzy tabele (bo o takim przykładnie mowa była w mailu):
*************************** 1. row ***************************
Table: tab1
Create Table: CREATE TABLE `tab1` (
`a` int(10) NOT NULL,
`b` varchar(32) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2
1 row in set (0.00 sec)
mysql> show create table tab2\G
*************************** 1. row ***************************
Table: tab2
Create Table: CREATE TABLE `tab2` (
`a` int(10) NOT NULL,
`b` int(10) NOT NULL,
KEY `b` (`b`),
KEY `a` (`a`),
CONSTRAINT `tab2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `tab1` (`a`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin2
1 row in set (0.00 sec)
mysql> show create table tab3\G
*************************** 1. row ***************************
Table: tab3
Create Table: CREATE TABLE `tab3` (
`a` int(10) NOT NULL,
`b` varchar(32) NOT NULL,
KEY `a` (`a`),
CONSTRAINT `tab3_ibfk_1` FOREIGN KEY (`a`) REFERENCES `tab2` (`b`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin2
1 row in set (0.00 sec)
Druga, o nazwie `nonfk`, zawiera takie same trzy tabele jak powyższe, z wyjątkiem tego, że nie tworzymy tam kluczy obcych.
Przy użyciu programu mk-query-profiler profilujemy dwie transakcje. Pierwszą:
DELETE FROM tab1 WHERE a=1;
DELETE FROM tab2 WHERE a=1;
DELETE FROM tab3 WHERE a=1;
COMMIT;
wykonujemy na bazie `nonfk`:
+----------------------------------------------------------+
| 5 (0.0154 sec) |
+----------------------------------------------------------+
__ Overall stats _______________________ Value _____________
Total elapsed time 0.016
Questions 5
COMMIT 1
DELETE 3
DELETE MULTI 0
INSERT 0
INSERT SELECT 0
REPLACE 0
REPLACE SELECT 0
SELECT 0
UPDATE 0
UPDATE MULTI 0
Data into server 114
Data out of server 62
Optimizer cost 52.495
__ Table and index accesses ____________ Value _____________
Table locks acquired 3
Table scans 0
Join 0
Index range scans 0
Join without check 0
Join with check 0
Rows sorted 0
Range sorts 0
Merge passes 0
Table scans 0
Potential filesorts 0
__ Row operations ____________________ Handler ______ InnoDB
Reads 8 3
Fixed pos (might be sort) 0
Next row (table scan) 0
Bookmark lookup 6
First in index (full scan?) 0
Next in index 2
Prev in index 0
Writes
Delete 3 3
Update 0 0
Insert 0 0
Commit 8
InnoDB row locks
Number of locks waited for 0
Total ms spent acquiring locks 0
__ I/O Operations _____________________ Memory ________ Disk
Key cache
Key reads 0 0
Key writes 0 0
Temp tables 0 0
Temp files 0
InnoDB buffer pool
Reads 41 0
Random read-aheads 0
Sequential read-aheads 0
Write requests 14 0
Reads/creates blocked by flushes 0
InnoDB log operations
Log writes 1 2
Log writes blocked by flushes 0
__ InnoDB Data Operations ____ Pages _____ Ops _______ Bytes
Reads 0 0 0
Writes 0 2 1536
Doublewrites 0 0
Creates 0
Fsyncs 2
OS fsyncs 2 1536
Drugą transakcję:
DELETE FROM tab1 WHERE a=1;
COMMIT;
wykonujemy na bazie `fk`:
+----------------------------------------------------------+
| 3 (0.0181 sec) |
+----------------------------------------------------------+
__ Overall stats _______________________ Value _____________
Total elapsed time 0.019
Questions 3
COMMIT 1
DELETE 1
DELETE MULTI 0
INSERT 0
INSERT SELECT 0
REPLACE 0
REPLACE SELECT 0
SELECT 0
UPDATE 0
UPDATE MULTI 0
Data into server 52
Data out of server 35
Optimizer cost 31.497
__ Table and index accesses ____________ Value _____________
Table locks acquired 1
Table scans 0
Join 0
Index range scans 0
Join without check 0
Join with check 0
Rows sorted 0
Range sorts 0
Merge passes 0
Table scans 0
Potential filesorts 0
__ Row operations ____________________ Handler ______ InnoDB
Reads 2 1
Fixed pos (might be sort) 0
Next row (table scan) 0
Bookmark lookup 2
First in index (full scan?) 0
Next in index 0
Prev in index 0
Writes
Delete 1 3
Update 0 0
Insert 0 0
Commit 4
InnoDB row locks
Number of locks waited for 0
Total ms spent acquiring locks 0
__ I/O Operations _____________________ Memory ________ Disk
Key cache
Key reads 0 0
Key writes 0 0
Temp tables 0 0
Temp files 0
InnoDB buffer pool
Reads 33 0
Random read-aheads 0
Sequential read-aheads 0
Write requests 14 0
Reads/creates blocked by flushes 0
InnoDB log operations
Log writes 1 2
Log writes blocked by flushes 0
__ InnoDB Data Operations ____ Pages _____ Ops _______ Bytes
Reads 0 0 0
Writes 0 2 1536
Doublewrites 0 0
Creates 0
Fsyncs 2
OS fsyncs 2 1536
Jak widać, transakcja na tabelach bez klucza obcego wymagała większej ilości odczytów z buffer pool (41) niż transakcja z kluczem obcym (33). Większa też jest oczywiście liczba zapytań. Patrząc jednak na czas wykonywania zapytania widać, że transakcja bez kluczy obcych była szybsza (0.0154 sekundy) niż transakcja z kluczem obcym (0.0181 sekundy).
Podobna sytuacja występuje, jeśli wykonamy zapytanie typu:
i jego odpowiednik na tabelach bez kluczy obcych. Wynik mk-query-profile dla tego zapytania dla tabel z kluczami obcymi to:
| 1 (0.0061 sec) |
+----------------------------------------------------------+
__ Overall stats _______________________ Value _____________
Total elapsed time 0.006
Questions 1
COMMIT 0
DELETE 1
DELETE MULTI 0
INSERT 0
INSERT SELECT 0
REPLACE 0
REPLACE SELECT 0
SELECT 0
UPDATE 0
UPDATE MULTI 0
Data into server 33
Data out of server 11
Optimizer cost 10.499
__ Table and index accesses ____________ Value _____________
Table locks acquired 1
Table scans 0
Join 0
Index range scans 0
Join without check 0
Join with check 0
Rows sorted 0
Range sorts 0
Merge passes 0
Table scans 0
Potential filesorts 0
__ Row operations ____________________ Handler ______ InnoDB
Reads 102 100
Fixed pos (might be sort) 0
Next row (table scan) 0
Bookmark lookup 2
First in index (full scan?) 1
Next in index 99
Prev in index 0
Writes
Delete 99 297
Update 0 0
Insert 0 0
Commit 2
InnoDB row locks
Number of locks waited for 0
Total ms spent acquiring locks 0
__ I/O Operations _____________________ Memory ________ Disk
Key cache
Key reads 0 0
Key writes 0 0
Temp tables 0 0
Temp files 0
InnoDB buffer pool
Reads 2357 0
Random read-aheads 0
Sequential read-aheads 0
Write requests 797 0
Reads/creates blocked by flushes 0
InnoDB log operations
Log writes 47 2
Log writes blocked by flushes 0
__ InnoDB Data Operations ____ Pages _____ Ops _______ Bytes
Reads 0 0 0
Writes 0 2 25088
Doublewrites 0 0
Creates 0
Fsyncs 2
OS fsyncs 2 25088
a dla tabel bez klucza obcego:
| 3 (0.0026 sec) |
+----------------------------------------------------------+
__ Overall stats _______________________ Value _____________
Total elapsed time 0.020
Questions 3
COMMIT 0
DELETE 3
DELETE MULTI 0
INSERT 0
INSERT SELECT 0
REPLACE 0
REPLACE SELECT 0
SELECT 0
UPDATE 0
UPDATE MULTI 0
Data into server 99
Data out of server 41
Optimizer cost 31.497
__ Table and index accesses ____________ Value _____________
Table locks acquired 3
Table scans 0
Join 0
Index range scans 0
Join without check 0
Join with check 0
Rows sorted 0
Range sorts 0
Merge passes 0
Table scans 0
Potential filesorts 0
__ Row operations ____________________ Handler ______ InnoDB
Reads 306 300
Fixed pos (might be sort) 0
Next row (table scan) 0
Bookmark lookup 6
First in index (full scan?) 3
Next in index 297
Prev in index 0
Writes
Delete 297 297
Update 0 0
Insert 0 0
Commit 6
InnoDB row locks
Number of locks waited for 0
Total ms spent acquiring locks 0
__ I/O Operations _____________________ Memory ________ Disk
Key cache
Key reads 0 0
Key writes 0 0
Temp tables 0 0
Temp files 0
InnoDB buffer pool
Reads 2397 0
Random read-aheads 0
Sequential read-aheads 0
Write requests 906 0
Reads/creates blocked by flushes 0
InnoDB log operations
Log writes 53 6
Log writes blocked by flushes 0
__ InnoDB Data Operations ____ Pages _____ Ops _______ Bytes
Reads 0 0 0
Writes 0 6 30208
Doublewrites 0 0
Creates 0
Fsyncs 6
OS fsyncs 6 30208
Jak widać, pomimo tego, że operacji odczytu i zapisu było więcej, to zapytania na tabelach bez klucza obcego wykonywały się szybciej (0.0026 sekundy) niż na tabelach z kluczem obcym (0.0061 sekundy).
Dla porządku, sprawdziłem jeszcze jak wygląda sytuacja w przypadku większej ilości powtórzeń. Średni czas usuwania jednego rekordu z wykorzystaniem kluczy obcych to 0.00215849 sekundy, bez kluczy obcych to 0.00181325. Usuwanie 100 rekordów to odpowiednio 0.0074432 i 0.0026644 sekundy. Jak widać, różnice są i faktycznie widać spory narzut na obsługę kluczy obcych. Trzeba jednak pamiętać, że te czasy mogą wyglądać inaczej w przypadku innego serwera MySQL. Ilość operacji dyskowych, koniecznych do ręcznego usunięcia trzech rekordów jest większa, niż w przypadku gdy korzystamy z kluczy obcych. Jeśli serwer cierpi na nadmiar operacji dyskowych, może się okazać, że dołożenie tych kilku dodatkowych spowoduje spore problemy. Dodatkowo, rezygnując ze stosowania kluczy obcych i ograniczeń typu ON DELETE CASCADE czy ON UPDATE CASCADE dodatkową pracę musi wykonać programista. Coś za coś. Decyzję o tym, czy i w jakim zakresie stosować te ograniczenia musi i tak podjąć projektant aplikacji bazodanowej.
Komentarze