optymalizacje

Dosyć często w bazach danych używa się dosyć krótkich pól tekstowych – varchar, lub char.
Nie wszyscy jednak wiedzą iż pola te mogą podlegać kompresji, jest tylko jeden warunek.
Jeżeli wielkość kolumny nie przekroczy 8 znaków, dane nie będą kompresowane.
Dla deweloperów może to jednak mieć duże znaczenie. W procesie analizy danych i co za tym idzie tworzenia struktur dla nich staramy się często zminimalizować ich ilość aby nie rezerwować/zajmować niepotrzebnie przestrzeni pamięciowej.

Załóżmy iż chcemy w bazie przechować okrojoną datę w formie RRRR-MM. Na potrzeby tego rodzaju danych programista stworzy kolumne o danych takich jak:

CREATE TABLE test (
testId INT UNSIGNED NOT NULL AUTO_INCREMENT,
shortDate CHAR(7) NOT NULL,
relatedId INT UNSIGNED NOT NULL,
testOptions INT UNSIGNED NOT NULL,
PRIMARY KEY(testId),
KEY (shortDate)
) Engine=MyISAM;

Tym silnika niekoniecznie tutaj ma znaczenie, warto sprawdzic i dla MyISAM oraz dla InnoDB.

Oczywiscie typ char został wybrany ponieważ zawsze dane mają taką samą długość co ułatwia mysqlowi zarządzanie rekordem a przy okazji nie marnuje miejsca.

Po wrzuceniu 1M rekordów za pomocą:

DELIMITER //
CREATE PROCEDURE fillTable()
BEGIN
SET @x:=0;
REPEAT INSERT INTO test
SET shortDate=DATE_FORMAT(NOW() – INTERVAL FLOOR(0 + RAND() * (10000)) DAY, ‘%Y-%m’),
relatedId=FLOOR(1 + RAND() * 10000), testOptions=FLOOR(1 + RAND() * x’7fffffff’);
SET @x = @x + 1;
UNTIL @x > 1000000 END REPEAT;
END //
DELIMITER ;

Wywolanie dodawania rekordow:

call fillTable();

otrzymujemy taką oto wielkość indeksu:

select TABLE_NAME,ENGINE,ROW_FORMAT,DATA_LENGTH,INDEX_LENGTH FROM information_schema.tables WHERE TABLE_SCHEMA=’test’;

+————+——–+————+————-+————–+
| TABLE_NAME | ENGINE | ROW_FORMAT | DATA_LENGTH | INDEX_LENGTH |
+————+——–+————+————-+————–+
| test       | MyISAM | Fixed      |    20000020 |     26477568 |
+————+——–+————+————-+————–+

Jedna drobna zmiana, zdawało by się – na gorsze

ALTER TABLE test CHANGE shortDate shortDate CHAR(8) NOT NULL;

Spójrzmy teraz co stało się z wielkościami indeksów:
select TABLE_NAME,ENGINE,ROW_FORMAT,DATA_LENGTH,INDEX_LENGTH FROM information_schema.tables WHERE TABLE_SCHEMA=’test’;

+————+——–+————+————-+————–+
| TABLE_NAME | ENGINE | ROW_FORMAT | DATA_LENGTH | INDEX_LENGTH |
+————+——–+————+————-+————–+
| test | MyISAM | Fixed | 21000021 | 13034496 |
+————+——–+————+————-+————–+

Co zaszło? Otóż pomimo straty jednego bajta na rekord uzyskaliśmy całkiem sporo na indeksach (50%), co za tym idzie? Mniej operacji IO i szybciej uzyskujemy wyniki… Oczywiscie zmiana jest pozytywna wtedy gdy mocno są obciążone dyski CPU zaś trochę się nudzi.