mysql视图和分页导致了全表扫描

在mysql性能评估期间，我们看到代码中有一些完整的表扫描。我们创建了一个视图，它跨越不同的表，这些表是主-明细记录。例如，销售和销售线。
在这个主细节记录中，我们需要添加分页。因此，我们必须能够获得前10个销售（及其相应的销售线）。但也要争取前10个销售有一个销售线，其中1个单位价格高于10美元。
因此，我们创建了一个视图，在这个场景中，vw\ U 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

explain命令的结果如下

+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+
| 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（物化视图）的限制，我们正在进行一些全表扫描-表测试(如何让mysql使用索引进行视图查询，mysql视图是否总是进行全表扫描？）
创建accross表的覆盖索引似乎是不可能的，所以这个选项也不可能。
我们已经在视图中添加了force索引来提示正确的索引，但这并不能解决问题。
如何以下降的方式解决这个问题？创建临时表是否不会导致大量开销？这看起来很简单，但是分页要求让它变得非常困难。我们正在使用MySQL5.7（AWSRDS），所以MySQL8.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" |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+

mysql视图和分页导致了全表扫描

暂无答案！

相关问题

热门标签

最新问答