Mysql-індекси для чайників
Почну з того, що часто бачу помилки, пов'язані зі створенням індексів в MySQL. Багато розробники (і не тільки новачки в MySQL) створюють багато індексів на тих колонках, які будуть використовувати в вибірках, і вважають це оптимальною стратегією. Наприклад, якщо мені потрібно виконати запит типу AGE = 18 AND STATE = 'CA', то багато людей просто створять 2 окремих індексу на колонках AGE і STATE.
Набагато кращою (тут і далі прим. Перекладача: а зазвичай і єдиною вірною) стратегією є створення комбінованого індексу виду (AGE, STATE). Давайте розглянемо чому це так.
Зазвичай (але не завжди) індекси в MySQL є BTREE-індексами - такий тип індексу здатна швидко переглядати інформацію, що міститься в своїх префіксах, і перебирати діапазони відсортованих значень. Наприклад, коли Ви запитуєте AGE = 18 з BTREE-індексом по колонці AGE MySQL знайде в таблиці першу відповідає запиту рядок і продовжить пошук до тих пір, поки не знайде першу придатну рядок - тоді він зупиняє пошук, тому що вважає, що далі нічого підходящого не буде. Діапазони, наприклад запити виду BETWEEN 18 AND 20, працюють схожим чином - MySQL зупиняється на інших значеннях.
Дещо складніше ситуація з запитами типу AGE IN (18,20,30), тому що насправді MySQL доводиться кілька разів проходити за індексом.
Отже, ми обговорили як MySQL шукає за індексом, але не визначили що ж він повертає після пошуку - зазвичай (якщо мова не йде про покривають (covering) індексах) отримує «покажчик рядки», який може бути значенням первинного ключа (якщо використовується движок InnoDB ), фізичне усунення в файлі (для MyISAM) або що-небудь в цьому роді. Важливо, що внутрішній движок MySQL може з цього вказівником знайти повну рядок з усіма необхідними даними, що відповідають заданим значенням індексу.
А які є варіанти у MySQL, якщо Ви створили два окремих індексу? Він може або використовувати тільки один з них, щоб відібрати підходящі рядки (а потім відфільтрувати витягнуті дані, керуючись WHERE - але вже без використання індексів), або може отримати покажчики на рядки від всіх відповідних індексів і обчислити їх перетин, а потім вже повернути дані .
Який із способів буде більш підходящим залежить від вибірковості та кореляції індексів. Якщо після відпрацювання WHERE по першій колонці буде відібрано 5% рядків, а застосування далі WHERE по другій колонці відфільтровує рядки до 1% від загальної кількості, то застосування перетинів, звичайно, має сенс. Але якщо другий WHERE відфільтрує тільки до 4.5%, то зазвичай значно вигідніше використовувати тільки перший індекс і відфільтрувати непотрібні нас рядки після вилучення даних.
Давайте розглянемо декілька прикладів:
CREATE TABLE 'idxtest' (
'I1' int (10) UNSIGNED NOT NULL,
'I2' int (10) UNSIGNED NOT NULL,
'Val' varchar (40) DEFAULT NULL,
KEY 'i1' ( 'i1'),
KEY 'i2' ( 'i2'),
KEY 'combined' ( 'i1', 'i2')
) ENGINE = MyISAM DEFAULT CHARSET = latin1
Я створив колонки i1 і i2 незалежними один від одного, причому кожна з них відбирає близько 1% рядків в таблиці, яка містить в цілому 10 млн. Записів.
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest WHERE i1 = 50 AND i2 = 50;
Як Ви можете бачити MySQL вважав за краще використовувати комбінований індекс, і запит виконався менше, ніж за 10 мс!
А тепер припустимо, що у нас є індекс тільки по окремим колонкам (сказати оптимізаторові ігнорувати комбінований індекс):
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest IGNORE INDEX (combined) WHERE i1 = 50 AND i2 = 50;
Як Ви можете бачити в даному випадку MySQL виконав пошук перетинів індексів, а на виконання запиту знадобилося 70 мс - в 7 разів довше!
Тепер давайте подивимося, що буде, якщо використовувати тільки один індекс і фільтрувати отримані дані:
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest IGNORE INDEX (combined, i2) WHERE i1 = 50 AND i2 = 50;
На цей раз MySQL довелося обійти значно більше рядків, а виконання запиту зайняло 290 мс. Таким чином ми бачимо, що використання перетину індексів набагато краще, ніж використання одного індексу, але значно краще використовувати комбіновані індекси.
Однак на цьому проблеми з перетинами індексів не закінчуються. В даний час можливості використання цієї процедури в MySQL значно обмежені, тому MySQL використовує їх далеко не завжди:
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest IGNORE INDEX (combined) WHERE i1 = 50 AND i2 IN (49,50);
Як тільки запит по одній з колонок стає порівнянням, а перерахуванням, MySQL більше не зможе використовувати перетин індексів, не дивлячись на те, що в даному випадку при запиті i2 IN (49,50) це було б більш, ніж розумно, т.к . запит залишається досить селективним.
Тепер давайте проведемо ще один тест. Я очистив таблицю і знову наповнив її даними таким чином, щоб значення в i1 і i2 сильно корелювали. Насправді вони тепер взагалі рівні:
mysql> UPDATE idxtest SET i2 = i1;
Query OK, 10900996 rows affected (6 min 47.87 sec)
Rows matched: 11010048 Changed: 10900996 Warnings: 0
Давайте подивимося, що станеться в цьому випадку:
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest WHERE i1 = 50 AND i2 = 50;
Оптимізатор вирішив використовувати перетин індексів, хоча це було чи не найгіршим рішенням! Виконання запиту зайняло 360 мс. Також зверніть увагу на велику похибку в оцінці зразкового кількості рядків.
Це сталося через те, що MySQL вважає значення в колонках i1 і i2 незалежними, і тому вибирає перетин індексів. Насправді він не може припустити іншого, тому що ніякої статистики про кореляцію значень в колонках у нього немає.
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest IGNORE INDEX (i2) WHERE i1 = 50 AND i2 = 50;
А тепер, коли ми заборонили MySQL використовувати індекс по колонці i2 (а значить він не може і знайти перетин індексів), він використовує індекс по одній колонці, а не комбінований. Сталося так тому, що у MySQL є статистика про приблизний кількості відповідних рядків, і так як воно дорівнює для обох індексів, то MySQL вибрав менший за розміром. Виконання запиту знову зайняло 290 мс - в точності стільки ж, скільки і в минулий раз.
Змусимо MySQL використовувати тільки combined індекс:
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest IGNORE INDEX (i1, i2) WHERE i1 = 50 AND i2 = 50;
Видно, що MySQL приблизно на 20% помиляється в оцінці кількості перебираються рядків, що, звичайно, не так, тому що використовується той же префікс, що і при використанні індексу тільки по колонці i1. MySQL не знає цього, тому що переглядає статистику по окремим індексам і не намагається погоджувати їх.
Через те, що використовуваний комбінований індекс більше, ніж індекс по одній колонці, виконання запиту зайняло 300 мс.
Таким чином ми бачимо, що MySQL може вирішити використовувати перетин індексів навіть в тому випадку, якщо це найгірший варіант, хоча з технічної точки зору це, звичайно, буде кращий план, враховуючи, що інший статистики у нього немає.
Є прості способи змусити MySQL не використовувати перетин індексів, але, на жаль, мені не відомо як змусити його використовувати перетину, якщо він вважає цей варіант неоптимальним. Сподіваюся, що така можливість у майбутньому буде додана.
Нарешті, давайте розглянемо ситуацію, коли процедура знаходження перетину індексів працює значно краще, ніж комбіновані індекси за кількома колонок. Йдеться про випадок, коли ми використовуєте OR при вибірці між колонками. В цьому випадку комбінований індекс стає абсолютно марним, і у MySQL є вибір між повним скануванням таблиці (FULL SCAN) і виконанням об'єднання (UNION) значень замість пошуку перетину на даних, які він отримав з однієї таблиці.
Я знову змінив взначенія в шпальтах i1 і i2 таким чином, щоб в них містилися незалежні дані (типова ситуація для таблиць).
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest WHERE i1 = 50 OR i2 = 50;
Такий запити виконується 660 мс. Відключивши індекс по другій колонці ми отримаємо FULL SCAN:
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest IGNORE INDEX (i2) WHERE i1 = 50 OR i2 = 50;
Зверніть увагу, що MySQL вказав ключі i1, combined як можливі до використання, однак насправді такої можливості у нього немає. Виконання такого запити займає 3370 мс!
Також зверніть увагу на те, що виконання запиту зайняло в 5 разів більше часу незважаючи на те, що FULL SCAN пройшов приблизно в 50 разів більше рядків. Це показує дуже велику різницю в продуктивності між повним проходом по таблиці і доступі по ключу, який займає в 10 разів більше часу (в сенсі «вартості» доступу на рядок), незважаючи на те, що виконується в пам'яті.
У разі UNION оптізатор діє більш просунуте і цілком здатний впоратися з діапазонами:
mysql> EXPLAIN SELECT avg (length (val)) FROM idxtest WHERE i1 = 50 OR i2 IN (49,50);
У більшості випадків використання комбінованих індексів за кількома колонкам є кращим рішенням, якщо ви використовуєте AND між подібними колонками в WHERE. Використання перетину індексів в принципі покращує продуктивність, але вона все одно значно гірше, ніж при використанні комбінованих ключів. У разі, якщо Ви використовуєте OR між колонками Вам потрібно мати за індексом на кожній з колонок, щоб MySQL зміг знайти їх перетину, а комбіновані індекси не можуть використовуватися в таких запитах.
Всі індекси MySQL (PRIMARY, UNIQUE, і INDEX) зберігаються у вигляді B-дерев. Рядки автоматично стискаються з видаленням прогалин в префіксах і кінцевих пробілів (see Розділ 6.5.7, «Синтаксис оператора CREATE INDEX»).
Індекси використовуються для того, щоб:
SELECT MIN (key_part2), MAX (key_part2) FROM table_name where key_part1 = 10
- Проводити сортування або групування в таблиці, якщо ці операції робляться на крайньому зліва префікс використовується ключа (наприклад ORDER BY key_part_1, key_part_2). Якщо за всіма частинами ключа слід DESC, то даний ключ Новомосковскется в зворотному порядку (see Розділ 5.2.7, «Як MySQL оптимізує ORDER BY»).
- У деяких випадках запит можна оптимізувати для вилучення величин без звернення до файлу даних. Якщо все використовувані стовпці в деякій таблиці є числовими і утворюють крайній зліва префікс для деякого ключа, то щоб забезпечити більшу швидкість, шукані величини можуть бути вилучені безпосередньо з індексного дерева:
SELECT key_part3 FROM table_name WHERE key_part1 = 1
Припустимо, що викликається наступний оператор SELECT:
mysql> SELECT * FROM tbl_name WHERE col1 = val1 AND col2 = val2;
Якщо за стовпцями col1 і col2 існує многостолбцовий індекс, то відповідні рядки можуть вибиратися безпосередньо. У разі, коли за стовпцями col1 і col2 існують роздільні індекси, оптимізатор намагається знайти найбільш обмежує індекс шляхом визначення, який індекс знайде менше рядків, і використовує даний індекс для вибірки цих рядків.
Якщо дана таблиця має многостолбцовий індекс, то будь-який крайній зліва префікс цього індексу може використовуватися оптимізатором для знаходження рядків. Наприклад, якщо є індекс за трьома стовпцями (col1, col2, col3), то існує потенційна можливість індексованого пошуку по (col1), (col1, col2) і (col1, col2, col3).
В MySQL можна використовувати частковий індекс, якщо стовпчики не утворюють крайній зліва префікс цього індексу. Припустимо, що є команди SELECT, показані нижче:
mysql> SELECT * FROM tbl_name WHERE col1 = val1;
mysql> SELECT * FROM tbl_name WHERE col2 = val2;
mysql> SELECT * FROM tbl_name WHERE col2 = val2 AND col3 = val3;
mysql> SELECT * FROM tbl_name WHERE col1 = val1 AND col2 = val2;
Якщо індекс існує по (col1, col2, col3), то тільки перший і четвертий показані вище запити використовує даний індекс. Другий і третій запити дійсно включають індексовані стовпці, але (col2) і (col2, col3) не є крайньою зліва частиною префіксів (col1, col2, col3).
* При цьому, індекси будуть працювати не залежно від типу індексу, тобто і тип індексу: INDEX і тип індексу UNIQUE відпрацюють дуже швидко.
MySQL застосовує індекси також для порівнянь LIKE, якщо аргумент в вираженні LIKE являє собою постійну рядок, не починається з символу-шаблону. Наприклад, такі команди SELECT використовують індекси:
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat% _ck%";
У першій команді розглядаються тільки рядки з "Patrick" <= key_col <"Patricl", а во второй - только строки с "Pat" <= key_col <"Pau".
Наступні команди SELECT не використовуватимуть індекси:
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "% Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;
У першій команді величина LIKE починається з шаблонного символу. У другій команді величина LIKE не є константою.
У версії MySQL 4.0 проводиться інша оптимізація на вираженні LIKE. Якщо використовується вираз. LIKE "% string%" і довжина рядка (string) більше, ніж 3 символи, то MySQL буде застосовувати алгоритм Турбо Бойера-Мура для ініціалізації шаблону для рядка і потім використовувати цей шаблон, щоб виконати пошук швидше.
При пошуку з використанням column_name IS NULL будуть використовуватися індекси, якщо column_name є індексом.
MySQL зазвичай використовує той індекс, який знаходить найменшу кількість рядків. Індекс застосовується для стовпців, які порівнюються за допомогою наступних операторів: =,>,> =, <, <=, BETWEEN и LIKE с префиксом, не содержащим шаблонного символа, такого как something%.
Якщо індекс не охоплює всі рівні AND в вираженні WHERE, то він не застосовується для оптимізації даного запиту. Іншими словами: щоб індекс можна було використовувати, префікс цього індексу повинен входити в кожну групу AND.
Наступні вирази WHERE використовують індекси:
WHERE index_part1 = 1 AND index_part2 = 2 AND other_column = 3
. WHERE index = 1 OR A = 10 AND index = 2 / * індекс = 1 АБО індекс = 2 * /
. WHERE index_part1 = 'hello' AND index_part_3 = 5
/ * Оптимізовано як "index_part1 = 'hello'" * /
. WHERE index1 = 1 and index2 = 2 or index1 = 3 and index3 = 3;
/ * Можна використовувати індекс по index1, але не по index2 або index 3 * /
Наступні вирази WHERE не використовують індекси:
. WHERE index_part2 = 1 AND index_part3 = 2
/ * Index_part_1 не використовується * /
. WHERE index = 1 OR A = 10
/ * Індекс не використовується в обох частинах AND * /
. WHERE index_part1 = 1 OR index_part2 = 10
/ * Ні індексу, що покриває всі рядки * /
У деяких випадках MySQL не використовує індекс, навіть якщо це можливо. Кілька прикладів таких ситуацій наведено нижче: