Що таке Index Condition Pushdown і що означає «Using index condition» в EXPLAIN?

Question

Accepted Answer

Index Condition Pushdown (ICP) - оптимізація, при якій частина умови WHERE перевіряється ще на рівні індексу, до того як читати повний рядок з таблиці.
Без ICP рушій зберігання знаходить записи індексу за тією частиною умови, яку можна використати для пошуку, читає кожен відповідний рядок з таблиці й передає серверу, а той уже перевіряє решту умови.
З ICP сервер передає рушію і ті частини умови, які стосуються колонок індексу, але не можуть бути використані для пошуку. Рушій перевіряє їх на записі індексу й читає рядок лише якщо умова виконується.
Приклад:
CREATE INDEX people_idx ON people (zipcode, lastname, firstname);

SELECT * FROM people
WHERE zipcode = '01001'
  AND lastname LIKE '%енко%'
  AND address LIKE '%Хрещатик%';

zipcode = '01001' - пошук в індексі;
lastname LIKE '%енко%' - для пошуку непридатна (відсоток на початку), але lastname є в індексі. З ICP її перевіряють на записах індексу, і рядки з невідповідним прізвищем не читаються з таблиці;
address в індексі немає - перевіряється вже після читання рядка.

Якщо під zipcode 10 000 записів, а прізвище підходить у 200, ICP заощаджує 9 800 переходів у кластерний індекс.
В EXPLAIN це видно як Using index condition в Extra.
Коли ICP застосовується:

для типів доступу range, ref, eq_ref, ref_or_null;
для InnoDB - лише для вторинних індексів (для кластерного індексу рядок уже прочитано разом із записом);
умова не може містити підзапитів і збережених функцій.

Практичне значення:

складений індекс корисний навіть тоді, коли не всі його колонки використовуються для пошуку: «хвостові» колонки працюють як фільтр;
типовий випадок - діапазон у середині індексу: (user_id, created_at, status) при WHERE user_id = ? AND created_at > ? AND status = ? - status після діапазону не бере участі в пошуку, але завдяки ICP фільтрується в індексі;
проте ICP - не заміна правильного порядку колонок: пошук за всіма колонками все одно ефективніший за фільтрацію.

ICP увімкнено за замовчуванням (optimizer_switch: index_condition_pushdown=on). Вимикати його доводиться хіба що для діагностики.
Докладніше в документації: Index Condition Pushdown

Що таке Index Condition Pushdown і що означає «Using index condition» в EXPLAIN?

Схожі питання