Indeksy typu B-tree, które są stosowane w MySQL a o których pisałem niedawno, mają bardzo przydatną cechę – chodzi o to, że dane w indeksie przechowywane są w sposób posortowany. Z punktu widzenia administratora MySQL jest to cecha niezwykle pożyteczna, gdyż można ją wykorzystać do przyspieszenia działania zapytań, w których wyniki są sortowane. Wspominałem o tym przy okazji postu dotyczącego algorytmu filesort, tu temat trochę rozwiniemy.

Przeanalizujmy sytuację na przykładzie tabeli `rental` w bazie `sakila`. Tabela ta wygląda następująco:

mysql> SHOW CREATE TABLE rental\G
*************************** 1. row ***************************
Table: rental
Create Table: CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`),
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0,00 sec)

Sprawdzamy jak wygląda plan poniższego zapytania:

mysql> EXPLAIN SELECT * FROM rental WHERE inventory_id=2 ORDER BY customer_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_inventory_id
key: idx_fk_inventory_id
key_len: 3
ref: const
rows: 5
Extra: Using where; Using filesort
1 row in set (0,00 sec)

Jak widać, stosowany jest indeks idx_fk_inventory_id,  sortowanie natomiast odbywa się przy użyciu algorytmu filesort.

Dodamy nowy indeks:

mysql> ALTER TABLE rental ADD INDEX idx_inventory_customer_rental (inventory_id, customer_id, rental_date);

Teraz sytuacja wygląda inaczej:

mysql> EXPLAIN SELECT * FROM rental WHERE inventory_id=2 ORDER BY customer_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_inventory_id,idx_inventory_customer_rental
key: idx_inventory_customer_rental
key_len: 3
ref: const
rows: 5
Extra: Using where
1 row in set (0,00 sec)

Widać, że filesort nie jest już stosowany, sortowanie odbywa się przy pomocy indeksu ‚idx_inventory_consumer_rental’. Co się stanie, gdy dodamy kolejny warunek sortowania?

mysql> EXPLAIN SELECT * FROM rental WHERE inventory_id=2 ORDER BY customer_id, rental_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_inventory_id,idx_inventory_customer_rental
key: idx_inventory_customer_rental
key_len: 3
ref: const
rows: 5
Extra: Using where
1 row in set (0,00 sec)

Nadal wykorzystywany jest indeks – dzieje się tak dlatego, że obejmuje on także kolumnę rental_date.
Zasady, na jakich MySQL korzysta z indeksów do sortowania wyników są podobne do zasad opisanych w poprzednim poście. Przykładowo, jeśli pominiemy w zapytaniu sortowanie po kolumnie `customer_id`, to automatycznie pojawia się „nieciągłość” i indeks nie zostanie wykorzystany. Nie można użyć pierwszej i trzeciej kolumny indeksu, z pominięciem drugiej:

mysql> EXPLAIN SELECT * FROM rental WHERE inventory_id=2 ORDER BY rental_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_inventory_id,idx_inventory_customer_rental
key: idx_fk_inventory_id
key_len: 3
ref: const
rows: 5
Extra: Using where; Using filesort
1 row in set (0,01 sec)

Podobnie jest jeśli w warunku WHERE mamy dwie kolumny, a sortujemy po trzeciej. Jeśli dopasowanie jest pełne, indeks do sortowania jest wykorzystany:

mysql> EXPLAIN SELECT * FROM rental WHERE inventory_id=2 AND customer_id=161 ORDER BY rental_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_inventory_id,idx_fk_customer_id,idx_inventory_customer_rental
key: idx_inventory_customer_rental
key_len: 5
ref: const,const
rows: 1
Extra: Using where
1 row in set (0,00 sec)

Jeśli jednak druga kolumna nie ma pełnego dopasowania, wykorzystywany jest filesort:

mysql> EXPLAIN SELECT * FROM rental WHERE inventory_id=2 AND customer_id>161 ORDER BY rental_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_inventory_id,idx_fk_customer_id,idx_inventory_customer_rental
key: idx_fk_inventory_id
key_len: 3
ref: const
rows: 5
Extra: Using where; Using filesort
1 row in set (0,00 sec)

Jeśli stosujemy JOINy, aby móc skorzystać z indeksu do sortowania wyników, wyniki muszą być sortowane po kolumnie, która znajduje się w pierwszej tabeli JOINa. Problem z tym, że czasami optimizer MySQL tą kolejność tabel widzi inaczej, niż administrator i w efekcie zapytanie z indeksu nie korzysta, choć teoretycznie by mogło:

mysql> EXPLAIN SELECT actor_id, title FROM sakila.film_actor INNER JOIN sakila.film USING(film_id) ORDER BY actor_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1026
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using index
2 rows in set (0,00 sec)

Co w takiej sytuacji zrobić, będzie temat kolejnego postu.

Poprawne indeksy to podstawa wydajnej pracy każdej bazy danych. Analizując strukturę bazy trzeba zwrócić uwagę na to, czy nie da się przyspieszyć operacji sortowania poprzez dodanie odpowiedniego indeksu. Jeśli zapytania wykorzystują indeksy do wyszukiwania danych, to dobrze. Może się jednak okazać, że wystarczy dodać do jakiegoś indeksu kolejną kolumnę a MySQL będzie mógł z niego skorzystać także w czasie sortowania wyników, co przełoży się na znaczny wzrost wydajności.