Zrobiłem malutki teścik zastosowania indexów na wybranej tabeli w bazie Mysql. Chciałem zobaczyć jakie będą różnice czasowe zapytań podczas zastosowania różnych indexów na mojej tabeli.
Tabela ma postać:
CREATE TABLE IF NOT EXISTS `media` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`acl_user_id` INT(11) DEFAULT NULL,
`parent_id` INT(11) DEFAULT NULL,
`size` INT(11) DEFAULT NULL,
`extension` ENUM('mp3','mp4','mpg', 'mpeg', 'jpg', 'png', 'bmp' ) DEFAULT NULL,
`type` ENUM('audio','video','image', 'avatar')NOT NULL,
`status` ENUM('active','inactive','deleted', 'processing', 'banned' ) NOT NULL ,
`date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
`date_activated` DATETIME DEFAULT NULL,
`date_deleted` DATETIME DEFAULT NULL,
`date_banned` DATETIME DEFAULT NULL,
`name` VARCHAR(255) NOT NULL,
`path` VARCHAR(255) DEFAULT NULL,
`description` VARCHAR(255) DEFAULT NULL ,
`guid` CHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_polish_ci; |
CREATE TABLE IF NOT EXISTS `media` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`acl_user_id` INT(11) DEFAULT NULL,
`parent_id` INT(11) DEFAULT NULL,
`size` INT(11) DEFAULT NULL,
`extension` ENUM('mp3','mp4','mpg', 'mpeg', 'jpg', 'png', 'bmp' ) DEFAULT NULL,
`type` ENUM('audio','video','image', 'avatar')NOT NULL,
`status` ENUM('active','inactive','deleted', 'processing', 'banned' ) NOT NULL ,
`date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
`date_activated` DATETIME DEFAULT NULL,
`date_deleted` DATETIME DEFAULT NULL,
`date_banned` DATETIME DEFAULT NULL,
`name` VARCHAR(255) NOT NULL,
`path` VARCHAR(255) DEFAULT NULL,
`description` VARCHAR(255) DEFAULT NULL ,
`guid` CHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_polish_ci;
Użyłem procedury sql do wypełnienia tej tabeli pół milionem losowych danych.
W przypadku braku indexów wypełnianie tabeli danymi trwało: 4 min 17.78 sec
Czas na pytania bez indexów:
SELECT COUNT(id) FROM media WHERE STATUS = 'active'; (trwało: 1.08 sec)
SELECT id FROM media WHERE SIZE = 30;(trwało: 0.99 sec)
SELECT COUNT(id) FROM media WHERE name = 'nazawa'; (trwało: 0.93 sec)
SELECT COUNT(id) FROM media WHERE SIZE = 30 AND STATUS = 'active' AND name = 'nazawa'; (trwało: 1.10 sec) |
select count(id) from media where status = 'active'; (trwało: 1.08 sec)
select id from media where size = 30;(trwało: 0.99 sec)
select count(id) from media where name = 'nazawa'; (trwało: 0.93 sec)
select count(id) from media where size = 30 and status = 'active' and name = 'nazawa'; (trwało: 1.10 sec)
Następnie dodałem następujące indexy:
ALTER TABLE media ADD INDEX idx_media_status (STATUS);
ALTER TABLE media ADD INDEX idx_media_size (`size`);
ALTER TABLE media ADD INDEX idx_media_name (`name`); |
ALTER TABLE media ADD INDEX idx_media_status (status);
ALTER TABLE media ADD INDEX idx_media_size (`size`);
ALTER TABLE media ADD INDEX idx_media_name (`name`);
(* dodawanie indeksów do istniejących pół miliona wierszy trwało odpowiednio: 18.11 sec, 23.24 sec oraz 39.00 sec)
W przypadku, gdy indexy istniały już w schemacie, napełnienie tabeli o pół mioliona danych trwało: 5 min 13.60 sec
Ponowiłem zapytania z wprowadzonymi indexami. Oto wyniki:
SELECT COUNT(id) FROM media WHERE STATUS = 'active'; (trwało: 0.02 sec)
SELECT id FROM media WHERE SIZE = 30;(trwało: 0.01 sec)
SELECT COUNT(id) FROM media WHERE name = 'nazawa'; (trwało: 0.02 sec)
SELECT COUNT(id) FROM media WHERE SIZE = 30 AND STATUS = 'active' AND name = 'nazawa'; (trwało: 0.10 sec) |
select count(id) from media where status = 'active'; (trwało: 0.02 sec)
select id from media where size = 30;(trwało: 0.01 sec)
select count(id) from media where name = 'nazawa'; (trwało: 0.02 sec)
select count(id) from media where size = 30 and status = 'active' and name = 'nazawa'; (trwało: 0.10 sec)
Dodałem jeszcze jeden klucz, aby zoptymalizować ostatnie zapytanie, z którego bardzo często będę korzystał w aplikacji:
ALTER TABLE media ADD INDEX idx_media_name_status_size (`name`, STATUS, `size`); |
ALTER TABLE media ADD INDEX idx_media_name_status_size (`name`, status, `size`);
A oto wyniki zapytania:
SELECT COUNT(id) FROM media WHERE SIZE = 30 AND STATUS = 'active' AND name = 'nazawa'; (0.03 sec) |
select count(id) from media where size = 30 and status = 'active' and name = 'nazawa'; (0.03 sec)
Jak widać z powyższych przykładów stosowanie indexów ma ogromny wpływ na wydajność. Podobne wnioski można zauważyć na wielu blogach, jednak najlepiej wykonać taki prosty test samemu, aby przekonać się o sile stosowania indexów. Pamiętajmy jednak, aby stosować indexy z umiarem – ich wprowadzenie opóźnia zapis danych do bazy. Ponadto, tak jak mówi dokumentacja mysql, należy zauważyć różnicę pomiędzy indexem dla jednego pola i dla wielu:
ALTER TABLE media ADD INDEX idx_media_size (`size`); |
ALTER TABLE media ADD INDEX idx_media_size (`size`);
a
ALTER TABLE media ADD INDEX idx_media_name_status_size (`name`, STATUS, `size`); |
ALTER TABLE media ADD INDEX idx_media_name_status_size (`name`, status, `size`);
są to dwa różne klucze, służące różnym typom zapytań.
Dla ludzi, którzy mają problem z wygenerowaniem sobie najnowszej dokumentacji Zend Framework w polskim języku udostępniam quickstart, pochodzący z wersji 2.0.0dev1.
