Датотечни системи и организација на податоци - Индекси

Преглед на шемата на AirportDB

Во нашите case-сценарија табелата booking со ~3.5 милиони редови е главната цел за индексирање. Повеќето сценарија ја вклучуваат.

Типови на индекси во MySql

B-Tree (BTree) index — default индекс во MySql
Најчест тип. Работи добро за: = , <, >, BETWEEN, LIKE 'prefix%', ORDER BY, GROUP BY.

Composite index Покрива повеќе услови. Редоследот во овој тип на индекс е критичен — индексот може да даде подобри перформанси амо ако WHERE ги содржи условите одлево надесно (leftmost prefix rule). Пример:

CREATE INDEX idx_comp ON tabela (col_a, col_b, col_c);

Може да се користи за:

WHERE col_a = ?
WHERE col_a = ? AND col_b = ?
WHERE col_a = ? AND col_b = ? AND col_c = ?
WHERE col_a = ? ORDER BY col_b

НЕ може за:

WHERE col_b = ?
WHERE col_c = ?

Covering Index
Индекс кој ги содржи сите колони од еден прашалник. Ако индексот ги има сите тие колони, MySQL не мора воопшто да ја чита самата табела (clustered data). Се што му треба, го зема директно од индексот. - Со овој тип на индекс се намалува бројот на И/О операции, што доведува до побрзо извршување, но е поголем индекс што зафаќа повеќе простор и ги успорува сите INSERT/UPDATE операции.
Пример за креирање:

CREATE INDEX idx_cov ON booking (flight_id, price, seat);

Hash индекс
Само за точна еднаквост (=). Не поддржува range, ORDER BY, GROUP BY. Во MySQL InnoDB НЕ може да се креира рачно - InnoDB го прави адаптивен hash автоматски. Може рачно само во MEMORY engine.
Пример за креирање:

CREATE TABLE temp_lookup (
  id INT NOT NULL,
  val VARCHAR(50),
  PRIMARY KEY (id)
) ENGINE=MEMORY;
 
CREATE INDEX idx_hash ON temp_lookup (vrednost) USING HASH;

Некои други типови: FULLTEXT индекс (за пребарување на текст), SPATIAL индекс (геолокациски).

Сценарија и индексирање на прашалници

Сценарио 1: Агент на шалтер го пребарува целото патничко досие за конкретен патник

Табели: booking JOIN flight JOIN passengerdetails
Цел: B-Tree индекс на еден столбец (booking.passenger_id)

Најпрво, за тестирање, ќе ја извршиме следната скрипта која безбедно ќе ги избрише сите предефинирани индекси кои веќе ги има во Airportdb од страна на MySql, без нарушување на foreign-key constraints://

Погледни ја скриптата овде Прашалник без индекс:

 
EXPLAIN ANALYZE
SELECT
    b.booking_id,
    b.seat,
    b.price,
    f.flightno,
    f.departure,
    f.arrival,
    pd.firstname,
    pd.lastname,
    pd.country
FROM booking b
JOIN flight f         ON b.flight_id    = f.flight_id
JOIN passengerdetails pd ON b.passenger_id = pd.passenger_id
WHERE b.passenger_id = 10001
ORDER BY f.departure DESC;