MySQL ALGORITHM = UNDEFINED/MERGE/TEMPORTARY 详解视图的三种算法与优化方案

旧城等待， 2022-12-09 11:55 348阅读 0赞

CREATE 
       [ALGORITHM = {
       MERGE  | TEMPTABLE | UNDEFINED}]
    VIEW [database_name].[view_name] 
    AS
    [SELECT  statement]

创建视图语句 create view 可选项 ALGORITHM 子句表示视图处理算法  
共三个参数： MERGE | TEMPTABLE | UNDEFINED

以下为官方文档解释：

For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.

For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.

For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used.

翻译一下、

MERGE  
引用视图和视图定义的语句的文本被合并，使视图定义的部分取代语句的相应部分。

TEMPTABLE  
视图中的结果被检索到一个临时表中，然后用来执行语句。

UNDEFINED  
MySQL选择使用哪种算法。如果可能的话，它更倾向于MERGE而不是TEMPTABLE，因为MERGE通常更有效率，而且如果使用临时表，视图无法更新。

**官方给出的说法：MERGE通常更有效率**

## 问题： ##

最近手头的一个项目在做压测、测试同事反映一个视图有些慢  
视图结构如下

CREATE
    DEFINER = 'user_mf'@'%' VIEW mf_vehicle.vi_vehiclelist AS SELECT `a`.`Id` AS `Id`, `a`.`Code` AS `Code`, `a`.`OwnerID` AS `FinancecompanyId`, `a`.`OwnerName` AS `FinancecompanyName`, `cs`.`Id` AS `ManufactureId`, `cs`.`Name` AS `ManufactureName`, `cs`.`Initial` AS `Initial`, `a`.`StatusCode` AS `StatusCode`, `mf_config`.`fn_GetCodeName`('vehicle', '车辆状态', `a`.`StatusCode`) AS `Status`, `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆状态', `a`.`StatusCode`) AS `NewStatus`, `b`.`BrandID` AS `BrandID`, `b`.`Brand` AS `Brand`, `b`.`VehicleTypeID` AS `VehicleTypeID`, `b`.`VehicleType` AS `VehicleType`, `a`.`VehicleItemID` AS `VehicleItemID`, `a`.`Color` AS `Color`, `a`.`CarNumber` AS `CarNumber`, `a`.`ParkingCity` AS `ParkingCity`, `a`.`CreatedAt` AS `CreatedAt`, `a`.`VehicleCategory` AS `VehicleCategory`, `mf_config`.`fn_GetCodeName`('vehicle', '车辆类型', `a`.`VehicleCategory`) AS `Category`, `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆类型', `a`.`VehicleCategory`) AS `NewCategory`,
      (SELECT
          COUNT(`ai`.`Id`)
        FROM `mf_sale`.`tb_sale_auctionitems` `ai`
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0))) AS `Transactions`,
      (SELECT
          COUNT(`ar`.`Id`)
        FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
          LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
            ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
            AND (`ar`.`Deleted` = 0))))
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0)
        AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,
      `a`.`Deposit` AS `Deposit`,
      `a`.`FloorPrice` AS `FloorPrice`,
      `a`.`EndTime` AS `EndTime`,
      `a`.`PurchaseLimit` AS `PurchaseLimit`,
      `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
      `a`.`Deleted` AS `Deleted`,
      `a`.`ClientId` AS `ClientId`
    FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
      LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
        ON (((`a`.`VehicleItemID` = `b`.`Id`)
        AND (`b`.`Deleted` = 0))))
      LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
        ON (((`a`.`ManufactureId` = `cs`.`Id`)
        AND (`cs`.`Deleted` = 0))))
    WHERE (`a`.`Deleted` = 0);

那我们看一下执行计划

EXPLAIN   SELECT  * FROM   mf_vehicle.vi_vehiclelist;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70_pic_center]

问题来了、 派生表哪里来的？  
一时间没有想清楚、  
因为在优化的过程中试着修改查询语句、 就把视图的语句拿出来看执行计划

EXPLAIN
    SELECT
      `a`.`Id` AS `Id`,
      `a`.`Code` AS `Code`,
      `a`.`OwnerID` AS `FinancecompanyId`,
      `a`.`OwnerName` AS `FinancecompanyName`,
      `cs`.`Id` AS `ManufactureId`,
      `cs`.`Name` AS `ManufactureName`,
      `cs`.`Initial` AS `Initial`,
      `a`.`StatusCode` AS `StatusCode`,
      `mf_config`.`fn_GetCodeName`('vehicle', '车辆状态', `a`.`StatusCode`) AS `Status`,
      `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆状态', `a`.`StatusCode`) AS `NewStatus`,
      `b`.`BrandID` AS `BrandID`,
      `b`.`Brand` AS `Brand`,
      `b`.`VehicleTypeID` AS `VehicleTypeID`,
      `b`.`VehicleType` AS `VehicleType`,
      `a`.`VehicleItemID` AS `VehicleItemID`,
      `a`.`Color` AS `Color`,
      `a`.`CarNumber` AS `CarNumber`,
      `a`.`ParkingCity` AS `ParkingCity`,
      `a`.`CreatedAt` AS `CreatedAt`,
      `a`.`VehicleCategory` AS `VehicleCategory`,
      `mf_config`.`fn_GetCodeName`('vehicle', '车辆类型', `a`.`VehicleCategory`) AS `Category`,
      `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆类型', `a`.`VehicleCategory`) AS `NewCategory`,
      (SELECT
          COUNT(`ai`.`Id`)
        FROM `mf_sale`.`tb_sale_auctionitems` `ai`
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0))) AS `Transactions`,
      (SELECT
          COUNT(`ar`.`Id`)
        FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
          LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
            ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
            AND (`ar`.`Deleted` = 0))))
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0)
        AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,
      `a`.`Deposit` AS `Deposit`,
      `a`.`FloorPrice` AS `FloorPrice`,
      `a`.`EndTime` AS `EndTime`,
      `a`.`PurchaseLimit` AS `PurchaseLimit`,
      `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
      `a`.`Deleted` AS `Deleted`,
      `a`.`ClientId` AS `ClientId`
    FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
      LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
        ON (((`a`.`VehicleItemID` = `b`.`Id`)
        AND (`b`.`Deleted` = 0))))
      LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
        ON (((`a`.`ManufactureId` = `cs`.`Id`)
        AND (`cs`.`Deleted` = 0))))
    WHERE (`a`.`Deleted` = 0);

有意思的事发生了、 执行计划变了、 派生表没了、  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70_pic_center 1]这时候就意识到、 问题不在查询语句上、 在视图的创建语句上、  
将视图包成一个表、 执行select \* from 发现、 执行计划与 直接查询视图结果一致

EXPLAIN
    SELECT  * FROM (
    SELECT
      `a`.`Id` AS `Id`,
      `a`.`Code` AS `Code`,
      `a`.`OwnerID` AS `FinancecompanyId`,
      `a`.`OwnerName` AS `FinancecompanyName`,
      `cs`.`Id` AS `ManufactureId`,
      `cs`.`Name` AS `ManufactureName`,
      `cs`.`Initial` AS `Initial`,
      `a`.`StatusCode` AS `StatusCode`,
      `mf_config`.`fn_GetCodeName`('vehicle', '车辆状态', `a`.`StatusCode`) AS `Status`,
      `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆状态', `a`.`StatusCode`) AS `NewStatus`,
      `b`.`BrandID` AS `BrandID`,
      `b`.`Brand` AS `Brand`,
      `b`.`VehicleTypeID` AS `VehicleTypeID`,
      `b`.`VehicleType` AS `VehicleType`,
      `a`.`VehicleItemID` AS `VehicleItemID`,
      `a`.`Color` AS `Color`,
      `a`.`CarNumber` AS `CarNumber`,
      `a`.`ParkingCity` AS `ParkingCity`,
      `a`.`CreatedAt` AS `CreatedAt`,
      `a`.`VehicleCategory` AS `VehicleCategory`,
      `mf_config`.`fn_GetCodeName`('vehicle', '车辆类型', `a`.`VehicleCategory`) AS `Category`,
      `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆类型', `a`.`VehicleCategory`) AS `NewCategory`,
      (SELECT
          COUNT(`ai`.`Id`)
        FROM `mf_sale`.`tb_sale_auctionitems` `ai`
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0))) AS `Transactions`,
      (SELECT
          COUNT(`ar`.`Id`)
        FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
          LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
            ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
            AND (`ar`.`Deleted` = 0))))
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0)
        AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,
      `a`.`Deposit` AS `Deposit`,
      `a`.`FloorPrice` AS `FloorPrice`,
      `a`.`EndTime` AS `EndTime`,
      `a`.`PurchaseLimit` AS `PurchaseLimit`,
      `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
      `a`.`Deleted` AS `Deleted`,
      `a`.`ClientId` AS `ClientId`
    FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
      LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
        ON (((`a`.`VehicleItemID` = `b`.`Id`)
        AND (`b`.`Deleted` = 0))))
      LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
        ON (((`a`.`ManufactureId` = `cs`.`Id`)
        AND (`cs`.`Deleted` = 0))))
    WHERE (`a`.`Deleted` = 0) ) z ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70_pic_center 2]查看视图的创建语句 ALGORITHM = UNDEFINED  
由此可见 mysql默认选择了  
ALGORITHM = TEMPORTARY  
派生表的由来搞清楚了、 问题是如何优化

--------------------

## 优化方案 ##

**优化的方式就是让视图走MERGE 的算法**  
试着重建一下视图

CREATE  OR REPLACE 
    ALGORITHM = MERGE
    DEFINER = 'user_mf'@'%' VIEW mf_vehicle.vi_vehiclelist AS SELECT `a`.`Id` AS `Id`, `a`.`Code` AS `Code`, `a`.`OwnerID` AS `FinancecompanyId`, `a`.`OwnerName` AS `FinancecompanyName`, `cs`.`Id` AS `ManufactureId`, `cs`.`Name` AS `ManufactureName`, `cs`.`Initial` AS `Initial`, `a`.`StatusCode` AS `StatusCode`, `mf_config`.`fn_GetCodeName`('vehicle', '车辆状态', `a`.`StatusCode`) AS `Status`, `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆状态', `a`.`StatusCode`) AS `NewStatus`, `b`.`BrandID` AS `BrandID`, `b`.`Brand` AS `Brand`, `b`.`VehicleTypeID` AS `VehicleTypeID`, `b`.`VehicleType` AS `VehicleType`, `a`.`VehicleItemID` AS `VehicleItemID`, `a`.`Color` AS `Color`, `a`.`CarNumber` AS `CarNumber`, `a`.`ParkingCity` AS `ParkingCity`, `a`.`CreatedAt` AS `CreatedAt`, `a`.`VehicleCategory` AS `VehicleCategory`, `mf_config`.`fn_GetCodeName`('vehicle', '车辆类型', `a`.`VehicleCategory`) AS `Category`, `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆类型', `a`.`VehicleCategory`) AS `NewCategory`,
      (SELECT
          COUNT(`ai`.`Id`)
        FROM `mf_sale`.`tb_sale_auctionitems` `ai`
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0))) AS `Transactions`,
      (SELECT
          COUNT(`ar`.`Id`)
        FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
          LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
            ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
            AND (`ar`.`Deleted` = 0))))
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0)
        AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,
      `a`.`Deposit` AS `Deposit`,
      `a`.`FloorPrice` AS `FloorPrice`,
      `a`.`EndTime` AS `EndTime`,
      `a`.`PurchaseLimit` AS `PurchaseLimit`,
      `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
      `a`.`Deleted` AS `Deleted`,
      `a`.`ClientId` AS `ClientId`
    FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
      LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
        ON (((`a`.`VehicleItemID` = `b`.`Id`)
        AND (`b`.`Deleted` = 0))))
      LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
        ON (((`a`.`ManufactureId` = `cs`.`Id`)
        AND (`cs`.`Deleted` = 0))))
    WHERE (`a`.`Deleted` = 0);

语句执行成功但是编辑视图、 查看sql语句、 还是 ALGORITHM = UNDEFINED  
试着把子查询去掉换成函数实现

(SELECT
          COUNT(`ai`.`Id`)
        FROM `mf_sale`.`tb_sale_auctionitems` `ai`
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0))) AS `Transactions`,

====》 fn\_GetTransactions

(SELECT
          COUNT(`ar`.`Id`)
        FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
          LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
            ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
            AND (`ar`.`Deleted` = 0))))
        WHERE ((`ai`.`VehicleID` = `a`.`Id`)
        AND (`ai`.`Deleted` = 0)
        AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,

====》 fn\_GetClinchNum  
再次重建视图

CREATE OR REPLACE
    ALGORITHM = MERGE
    DEFINER = 'user_mf'@'%' VIEW mf_vehicle.vi_vehiclelist AS SELECT `a`.`Id` AS `Id`, `a`.`Code` AS `Code`, `a`.`OwnerID` AS `FinancecompanyId`, `a`.`OwnerName` AS `FinancecompanyName`, `cs`.`Id` AS `ManufactureId`, `cs`.`Name` AS `ManufactureName`, `cs`.`Initial` AS `Initial`, `a`.`StatusCode` AS `StatusCode`, `mf_config`.`fn_GetCodeName`('vehicle', '车辆状态', `a`.`StatusCode`) AS `Status`, `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆状态', `a`.`StatusCode`) AS `NewStatus`, `b`.`BrandID` AS `BrandID`, `b`.`Brand` AS `Brand`, `b`.`VehicleTypeID` AS `VehicleTypeID`, `b`.`VehicleType` AS `VehicleType`, `a`.`VehicleItemID` AS `VehicleItemID`, `a`.`Color` AS `Color`, `a`.`CarNumber` AS `CarNumber`, `a`.`ParkingCity` AS `ParkingCity`, `a`.`CreatedAt` AS `CreatedAt`, `a`.`VehicleCategory` AS `VehicleCategory`, `mf_config`.`fn_GetCodeName`('vehicle', '车辆类型', `a`.`VehicleCategory`) AS `Category`, `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆类型', `a`.`VehicleCategory`) AS `NewCategory`,
      `mf_config`.`fn_GetTransactions`(`a`.`Id`) AS `Transactions`,
      `mf_config`.`fn_GetClinchNum`(`a`.`Id`) AS `ClinchNum`,
      `a`.`Deposit` AS `Deposit`,
      `a`.`FloorPrice` AS `FloorPrice`,
      `a`.`EndTime` AS `EndTime`,
      `a`.`PurchaseLimit` AS `PurchaseLimit`,
      `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
      `a`.`Deleted` AS `Deleted`,
      `a`.`ClientId` AS `ClientId`
    FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
      LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
        ON (((`a`.`VehicleItemID` = `b`.`Id`)
        AND (`b`.`Deleted` = 0))))
      LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
        ON (((`a`.`ManufactureId` = `cs`.`Id`)
        AND (`cs`.`Deleted` = 0))))
    WHERE (`a`.`Deleted` = 0);

执行成功、 修改成功  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70_pic_center 3]查看执行计划

EXPLAIN
      SELECT * FROM vi_vehiclelist vv;

![在这里插入图片描述][20200922153015656.png_pic_center]派生表消失、优化成功

--------------------

## 重点 ##

**MERGE通常更有效率**

**可能影响 ALGORITHM = MERGE 的因素  
汇总函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）  
DISTINCT  
GROUP BY  
HAVING  
LIMIT  
UNION或UNION ALL  
选择列表中的子查询  
对用户变量的赋值  
只引用字面值（在这种情况下，没有底层表）。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/8b395897022a49b489994e9990f3dda4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/63d51f63cdec4b28809d84b154706f3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/8eb413fc8a294946b8909ae5f672db41.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/6f864d5e63ef4974a371fca1ea2ee623.png
[20200922153015656.png_pic_center]: /images/20221123/035bd916c06f45a4b37da90482ebd774.png