时间戳

首页 > 解决方案 > MySql 视图和分页导致全表扫描

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

问题描述

在我们的 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;

视图创建为here

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（物化视图）的限制，我们导致了一些全表扫描 - 表 sale_test。（参考。如何让 MySQL 使用 INDEX 进行视图查询？，MySQL 视图是否总是进行全表扫描？）

跨表创建覆盖索引似乎是不可能的，所以这个选项也是不可能的。

我们已经在视图中添加了 force 索引以提示正确的索引，但这并不能解决问题。

这是如何以下降的方式解决的？创建一个临时表不会造成很多开销吗？这看起来很简单，但是分页要求使它变得非常困难。我们正在使用 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" |
+----+-------------+-----------------+------------+--------+-----------------------+-----------------------+---------+-------------------------------+------+----------+------------------+

标签： mysqlpagingsql-viewfull-table-scan

解决方案

推荐阅读