MySql View и подкачка страниц вызывают полное сканирование таблицы
Во время нашего обзора производительности MySQL мы увидели, что в нашем коде было несколько полных сканирований. Мы создали представление, которое охватывает различные таблицы, которые являются основными записями. Например, продажи и линии продаж.
На этой основной детализации у нас есть требование добавить подкачку. Таким образом, мы должны быть в состоянии получить первые 10 продаж (и их соответствующие строки продаж). Но также получить первые 10 продаж, у которых есть линия продаж, где 1 цена за единицу товара превышает 10$.
Итак, в этом сценарии мы создали представление vw_sales, которое объединяет линии продажи и продажи, и мы в состоянии удовлетворить эти требования.
Создать таблицы можно посмотреть здесь
CREATE TABLE `sale_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`amount` decimal(20,2) NOT NULL,
`created_on` datetime NOT NULL,
`tenant_id` bigint(20) NOT NULL,
`version` int(11) NOT NULL,
`timestamp` datetime NOT NULL,
`culture` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_sale_on_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `sales_line_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`sale_id` bigint(20) NOT NULL,
`quantity` bigint(20) NOT NULL,
`unit_price` decimal(20,2) NOT NULL,
`offer_product_id` bigint(20) NOT NULL,
`product_id` bigint(20) DEFAULT NULL,
`tenant_id` bigint(20) NOT NULL,
`version` int(11) NOT NULL,
`timestamp` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_sales_line_on_sale` (`sale_id`),
KEY `fk_sales_line_on_product` (`offer_product_id`),
KEY `fk_sales_line_on_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Вид создается как здесь
CREATE OR REPLACE
ALGORITHM = MERGE
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `vw_sales` AS
SELECT
`sale_test`.`id` AS `id`,
`sale_test`.`amount` AS `amount`,
`sale_test`.`created_on` AS `createdon`,
`sale_test`.`tenant_id` AS `tenantid`,
`sales_line_test`.`id` AS `saleslineid`,
`sales_line_test`.`quantity` AS `quantity`,
`sales_line_test`.`unit_price` AS `unitprice`
FROM
`sale_test` FORCE INDEX (`fk_sale_on_tenant`)
INNER JOIN `sales_line_test` `sales_line_test` ON (`sales_line_test`.`sale_id` = `sale_test`.`id`);
Мы добавили пейджинг, как показано ниже
SELECT
*,
CASE
WHEN @previous = id THEN @rank
WHEN @previous:=id THEN @rank:=@rank + 1
END AS rank
FROM
vw_sales,
(SELECT @previous:=0) previous,
(SELECT @rank:=- 1) rank
WHERE
id = 26
ORDER BY id ASC
Результат с командой объяснения выглядит следующим образом
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | "Using filesort" |
| 1 | PRIMARY | <derived3> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | <derived4> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 1 | 100.00 | "Using where" |
| 4 | DERIVED | sale_test | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 4 | DERIVED | sales_line_test | NULL | ref | fk_sales_line_on_sale | fk_sales_line_on_sale | 8 | order_management.sale_test.id | 1 | 100.00 | NULL |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | "No tables used" |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | "No tables used" |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
Однако мы видим, что из-за ограничения в MySql (материализованные представления) мы вызываем некоторые полные просмотры таблицы -table sale_test. (ссылка. Как мне заставить MySQL использовать INDEX для запроса представления? Всегда ли MySQL view выполняет полное сканирование таблицы?)
Создание индекса покрытия по таблицам не представляется возможным, поэтому эти параметры также невозможны.
Мы уже добавили индекс силы внутри представления, чтобы намекнуть на правильный индекс, но это не решает проблему.
Как это решается спусковым способом? Создание временной таблицы не вызывает много накладных расходов? Это кажется таким простым, но требования к пейджингу делают его очень сложным. Мы работаем с mysql 5.7 (AWS RDS), поэтому оконные функции, добавленные в mysql 8.0, нам не подходят.
К вашему сведению: без представления мы видим, что индекс используется
Запрос:
SELECT
`sale_test`.`id` AS `id`,
`sale_test`.`amount` AS `amount`,
`sale_test`.`created_on` AS `createdon`,
`sale_test`.`tenant_id` AS `tenantid`,
`sales_line_test`.`id` AS `saleslineid`,
`sales_line_test`.`quantity` AS `quantity`,
`sales_line_test`.`unit_price` AS `unitprice`,
CASE
WHEN @previous = `sale_test`.`id` THEN @rank
WHEN @previous:= `sale_test`.`id` THEN @rank:=@rank + 1
END AS rank
FROM
`sale_test` FORCE INDEX (FK_SALE_ON_TENANT)
INNER JOIN
`sales_line_test` `sales_line_test` ON (`sales_line_test`.`sale_id` = `sale_test`.`id`),
(SELECT @previous:=0) previous,
(SELECT @rank:=- 1) rank
WHERE
`sale_test`.`tenant_id` = 26
Результат
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | <derived3> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | sale_test | NULL | ref | fk_sale_on_tenant | fk_sale_on_tenant | 8 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | sales_line_test | NULL | ref | fk_sales_line_on_sale | fk_sales_line_on_sale | 8 | order_management.sale_test.id | 1 | 100.00 | NULL |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | "No tables used" |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | "No tables used" |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+