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

旧城等待， 2022-12-09 11:55 427阅读 0赞 
  1. CREATE 
  2.    [ALGORITHM = {
  3.    MERGE  | TEMPTABLE | UNDEFINED}]
  4. VIEW [database_name].[view_name] 
  5. AS
  6. [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通常更有效率

问题：

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

  1. CREATE
  2. 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`,
  3.   (SELECT
  4.       COUNT(`ai`.`Id`)
  5.     FROM `mf_sale`.`tb_sale_auctionitems` `ai`
  6.     WHERE ((`ai`.`VehicleID` = `a`.`Id`)
  7.     AND (`ai`.`Deleted` = 0))) AS `Transactions`,
  8.   (SELECT
  9.       COUNT(`ar`.`Id`)
  10.     FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
  11.       LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
  12.         ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
  13.         AND (`ar`.`Deleted` = 0))))
  14.     WHERE ((`ai`.`VehicleID` = `a`.`Id`)
  15.     AND (`ai`.`Deleted` = 0)
  16.     AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,
  17.   `a`.`Deposit` AS `Deposit`,
  18.   `a`.`FloorPrice` AS `FloorPrice`,
  19.   `a`.`EndTime` AS `EndTime`,
  20.   `a`.`PurchaseLimit` AS `PurchaseLimit`,
  21.   `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
  22.   `a`.`Deleted` AS `Deleted`,
  23.   `a`.`ClientId` AS `ClientId`
  24. FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
  25.   LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
  26.     ON (((`a`.`VehicleItemID` = `b`.`Id`)
  27.     AND (`b`.`Deleted` = 0))))
  28.   LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
  29.     ON (((`a`.`ManufactureId` = `cs`.`Id`)
  30.     AND (`cs`.`Deleted` = 0))))
  31. WHERE (`a`.`Deleted` = 0);

那我们看一下执行计划

  1. EXPLAIN   SELECT  * FROM   mf_vehicle.vi_vehiclelist;

在这里插入图片描述

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

  1. EXPLAIN
  2. SELECT
  3.   `a`.`Id` AS `Id`,
  4.   `a`.`Code` AS `Code`,
  5.   `a`.`OwnerID` AS `FinancecompanyId`,
  6.   `a`.`OwnerName` AS `FinancecompanyName`,
  7.   `cs`.`Id` AS `ManufactureId`,
  8.   `cs`.`Name` AS `ManufactureName`,
  9.   `cs`.`Initial` AS `Initial`,
  10.   `a`.`StatusCode` AS `StatusCode`,
  11.   `mf_config`.`fn_GetCodeName`('vehicle', '车辆状态', `a`.`StatusCode`) AS `Status`,
  12.   `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆状态', `a`.`StatusCode`) AS `NewStatus`,
  13.   `b`.`BrandID` AS `BrandID`,
  14.   `b`.`Brand` AS `Brand`,
  15.   `b`.`VehicleTypeID` AS `VehicleTypeID`,
  16.   `b`.`VehicleType` AS `VehicleType`,
  17.   `a`.`VehicleItemID` AS `VehicleItemID`,
  18.   `a`.`Color` AS `Color`,
  19.   `a`.`CarNumber` AS `CarNumber`,
  20.   `a`.`ParkingCity` AS `ParkingCity`,
  21.   `a`.`CreatedAt` AS `CreatedAt`,
  22.   `a`.`VehicleCategory` AS `VehicleCategory`,
  23.   `mf_config`.`fn_GetCodeName`('vehicle', '车辆类型', `a`.`VehicleCategory`) AS `Category`,
  24.   `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆类型', `a`.`VehicleCategory`) AS `NewCategory`,
  25.   (SELECT
  26.       COUNT(`ai`.`Id`)
  27.     FROM `mf_sale`.`tb_sale_auctionitems` `ai`
  28.     WHERE ((`ai`.`VehicleID` = `a`.`Id`)
  29.     AND (`ai`.`Deleted` = 0))) AS `Transactions`,
  30.   (SELECT
  31.       COUNT(`ar`.`Id`)
  32.     FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
  33.       LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
  34.         ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
  35.         AND (`ar`.`Deleted` = 0))))
  36.     WHERE ((`ai`.`VehicleID` = `a`.`Id`)
  37.     AND (`ai`.`Deleted` = 0)
  38.     AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,
  39.   `a`.`Deposit` AS `Deposit`,
  40.   `a`.`FloorPrice` AS `FloorPrice`,
  41.   `a`.`EndTime` AS `EndTime`,
  42.   `a`.`PurchaseLimit` AS `PurchaseLimit`,
  43.   `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
  44.   `a`.`Deleted` AS `Deleted`,
  45.   `a`.`ClientId` AS `ClientId`
  46. FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
  47.   LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
  48.     ON (((`a`.`VehicleItemID` = `b`.`Id`)
  49.     AND (`b`.`Deleted` = 0))))
  50.   LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
  51.     ON (((`a`.`ManufactureId` = `cs`.`Id`)
  52.     AND (`cs`.`Deleted` = 0))))
  53. WHERE (`a`.`Deleted` = 0);

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

  1. EXPLAIN
  2. SELECT  * FROM (
  3. SELECT
  4.   `a`.`Id` AS `Id`,
  5.   `a`.`Code` AS `Code`,
  6.   `a`.`OwnerID` AS `FinancecompanyId`,
  7.   `a`.`OwnerName` AS `FinancecompanyName`,
  8.   `cs`.`Id` AS `ManufactureId`,
  9.   `cs`.`Name` AS `ManufactureName`,
  10.   `cs`.`Initial` AS `Initial`,
  11.   `a`.`StatusCode` AS `StatusCode`,
  12.   `mf_config`.`fn_GetCodeName`('vehicle', '车辆状态', `a`.`StatusCode`) AS `Status`,
  13.   `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆状态', `a`.`StatusCode`) AS `NewStatus`,
  14.   `b`.`BrandID` AS `BrandID`,
  15.   `b`.`Brand` AS `Brand`,
  16.   `b`.`VehicleTypeID` AS `VehicleTypeID`,
  17.   `b`.`VehicleType` AS `VehicleType`,
  18.   `a`.`VehicleItemID` AS `VehicleItemID`,
  19.   `a`.`Color` AS `Color`,
  20.   `a`.`CarNumber` AS `CarNumber`,
  21.   `a`.`ParkingCity` AS `ParkingCity`,
  22.   `a`.`CreatedAt` AS `CreatedAt`,
  23.   `a`.`VehicleCategory` AS `VehicleCategory`,
  24.   `mf_config`.`fn_GetCodeName`('vehicle', '车辆类型', `a`.`VehicleCategory`) AS `Category`,
  25.   `mf_config`.`fn_GetCodeName`('vehicle', '新车车辆类型', `a`.`VehicleCategory`) AS `NewCategory`,
  26.   (SELECT
  27.       COUNT(`ai`.`Id`)
  28.     FROM `mf_sale`.`tb_sale_auctionitems` `ai`
  29.     WHERE ((`ai`.`VehicleID` = `a`.`Id`)
  30.     AND (`ai`.`Deleted` = 0))) AS `Transactions`,
  31.   (SELECT
  32.       COUNT(`ar`.`Id`)
  33.     FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
  34.       LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
  35.         ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
  36.         AND (`ar`.`Deleted` = 0))))
  37.     WHERE ((`ai`.`VehicleID` = `a`.`Id`)
  38.     AND (`ai`.`Deleted` = 0)
  39.     AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,
  40.   `a`.`Deposit` AS `Deposit`,
  41.   `a`.`FloorPrice` AS `FloorPrice`,
  42.   `a`.`EndTime` AS `EndTime`,
  43.   `a`.`PurchaseLimit` AS `PurchaseLimit`,
  44.   `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
  45.   `a`.`Deleted` AS `Deleted`,
  46.   `a`.`ClientId` AS `ClientId`
  47. FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
  48.   LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
  49.     ON (((`a`.`VehicleItemID` = `b`.`Id`)
  50.     AND (`b`.`Deleted` = 0))))
  51.   LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
  52.     ON (((`a`.`ManufactureId` = `cs`.`Id`)
  53.     AND (`cs`.`Deleted` = 0))))
  54. WHERE (`a`.`Deleted` = 0) ) z ;

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

优化方案

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

  1. CREATE  OR REPLACE 
  2. ALGORITHM = MERGE
  3. 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`,
  4.   (SELECT
  5.       COUNT(`ai`.`Id`)
  6.     FROM `mf_sale`.`tb_sale_auctionitems` `ai`
  7.     WHERE ((`ai`.`VehicleID` = `a`.`Id`)
  8.     AND (`ai`.`Deleted` = 0))) AS `Transactions`,
  9.   (SELECT
  10.       COUNT(`ar`.`Id`)
  11.     FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
  12.       LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
  13.         ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
  14.         AND (`ar`.`Deleted` = 0))))
  15.     WHERE ((`ai`.`VehicleID` = `a`.`Id`)
  16.     AND (`ai`.`Deleted` = 0)
  17.     AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,
  18.   `a`.`Deposit` AS `Deposit`,
  19.   `a`.`FloorPrice` AS `FloorPrice`,
  20.   `a`.`EndTime` AS `EndTime`,
  21.   `a`.`PurchaseLimit` AS `PurchaseLimit`,
  22.   `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
  23.   `a`.`Deleted` AS `Deleted`,
  24.   `a`.`ClientId` AS `ClientId`
  25. FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
  26.   LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
  27.     ON (((`a`.`VehicleItemID` = `b`.`Id`)
  28.     AND (`b`.`Deleted` = 0))))
  29.   LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
  30.     ON (((`a`.`ManufactureId` = `cs`.`Id`)
  31.     AND (`cs`.`Deleted` = 0))))
  32. WHERE (`a`.`Deleted` = 0);

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

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

====》 fn_GetTransactions

  1. (SELECT
  2.       COUNT(`ar`.`Id`)
  3.     FROM (`mf_sale`.`tb_sale_auctionitems` `ai`
  4.       LEFT JOIN `mf_sale`.`tb_sale_auctionresult` `ar`
  5.         ON (((`ai`.`AuctionID` = `ar`.`AuctionID`)
  6.         AND (`ar`.`Deleted` = 0))))
  7.     WHERE ((`ai`.`VehicleID` = `a`.`Id`)
  8.     AND (`ai`.`Deleted` = 0)
  9.     AND (`ai`.`StatusCode` = 5))) AS `ClinchNum`,

====》 fn_GetClinchNum
再次重建视图

  1. CREATE OR REPLACE
  2. ALGORITHM = MERGE
  3. 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`,
  4.   `mf_config`.`fn_GetTransactions`(`a`.`Id`) AS `Transactions`,
  5.   `mf_config`.`fn_GetClinchNum`(`a`.`Id`) AS `ClinchNum`,
  6.   `a`.`Deposit` AS `Deposit`,
  7.   `a`.`FloorPrice` AS `FloorPrice`,
  8.   `a`.`EndTime` AS `EndTime`,
  9.   `a`.`PurchaseLimit` AS `PurchaseLimit`,
  10.   `a`.`PurchaseLimitNum` AS `PurchaseLimitNum`,
  11.   `a`.`Deleted` AS `Deleted`,
  12.   `a`.`ClientId` AS `ClientId`
  13. FROM ((`mf_vehicle`.`tb_vehicle_vehicleinfo` `a`
  14.   LEFT JOIN `mf_vehicle`.`tb_vehicle_vehicleitem` `b`
  15.     ON (((`a`.`VehicleItemID` = `b`.`Id`)
  16.     AND (`b`.`Deleted` = 0))))
  17.   LEFT JOIN `mf_customer`.`tb_customer_manufacture` `cs`
  18.     ON (((`a`.`ManufactureId` = `cs`.`Id`)
  19.     AND (`cs`.`Deleted` = 0))))
  20. WHERE (`a`.`Deleted` = 0);

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

  1. EXPLAIN
  2.   SELECT * FROM vi_vehiclelist vv;

在这里插入图片描述派生表消失、优化成功

重点

MERGE通常更有效率

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

发表评论

表情：
评论列表 （有 0 条评论，427人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySQL视图详解

    我写本文主要目的，是在网上看见了 ![在这里插入图片描述][fa94b97e03084be6a7da1e30b03163a5.png] 所以，文本主要探讨的问题如下，

    傷城~傷城~/ 0 赞/ 9 阅读

    相关 Mysql视图详解

    1、 什么是MySQL视图？ MySQL视图是一种虚拟表，它是基于SQL查询结果生成的，可以将查询结果存储在一个虚拟表中。用户可以像操作表一样操作视图，但视图没有实际的数

    我就是我我就是我/ 0 赞/ 14 阅读

    相关 MySQL的视图详解

    视图 1:概述 1:视图:view,可以理解为一种有结构(二维表),但是这个结构中不真实存放着数据,虚拟表的结构来源不是自己定义,而是从对应的基表(视图的数据来源)中产生的

    拼搏现实的明天。拼搏现实的明天。/ 0 赞/ 299 阅读