Продвинутые стратегии индексирования
От вопроса "какой столбец проиндексировать?" к вопросу "какой индекс реально использует оптимизатор и почему?"
- проектировать составной индекс и применять правило самого левого префикса,
- строить покрывающий индекс, чтобы запрос выполнялся как сканирование только по индексу,
- рассуждать о селективности и понимать, почему оптимизатор иногда игнорирует индекс,
- использовать функциональный (по выражению) индекс в MySQL 8,
- находить и переписывать несаргируемые предикаты, которые убивают индекс.
Это продвинутый курс: вы уже знаете, что B-дерево-индекс ускоряет поиск и диапазонные сканирования, что PRIMARY KEY в InnoDB - это кластерный индекс, и как читать план EXPLAIN. Мы строим прямо на этом.
0. Пример базы данных
Все запросы ниже выполняются над одной маленькой схемой магазина. Представьте, что в каждой таблице миллионы строк - индексирование имеет смысл только на большом объёме, - но мы показываем несколько строк, чтобы результаты оставались читаемыми.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
last_name VARCHAR(50),
first_name VARCHAR(50),
city VARCHAR(50),
email VARCHAR(120)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(20),
total DECIMAL(10,2)
);
INSERT INTO customers VALUES
(1,'Smith','Anna','Berlin','anna@shop.io'),
(2,'Smith','Ben','Berlin','ben@gmail.com'),
(3,'Jones','Cara','Almaty','cara@mail.kg'),
(4,'Adams','Dan','Berlin','dan@gmail.com');
INSERT INTO orders VALUES
(10,1,'2024-02-11','shipped', 42.00),
(11,1,'2024-03-02','shipped', 18.50),
(12,2,'2024-02-20','pending', 99.00),
(13,3,'2023-12-30','shipped', 7.25),
(14,4,'2024-05-05','cancelled',60.00);Две таблицы, связь один-ко-многим (orders.customer_id ссылается на customers.customer_id) и столбцы с очень разной селективностью: у status всего несколько значений, а email почти уникален. Оба факта важны ниже.