什么是执行计划？
执行计划是数据库系统根据查询语句的结构和表的统计信息生成的一种操作指南，用于引导执行器从存储引擎中以何种顺序、何种方式读取数据。
执行计划有什么用？
执行计划能清晰的给用户展示语句具体的执行流程，能有效帮助用户去优化 SQL 质量。
如何生成执行计划？
MySQL 里面可以通过在查询语句前加一个 explain 指令。查询语句仅限 SELECT、UPDATE、DELETE、INSERT、REPLACE
输出格式：TRADITIONAL（表格格式）、TREE（树形格式）、JSON
可通过 FORMAT 选项指定。
示例：EXPLAIN FORMAT=JSON SELECT * FROM xxx;
表格格式：
树形格式：
-> Nested loop inner join (cost=2.15 rows=0.1) -> Filter: ((oobo.IsDeleted = 0) and (oobo.WarehouseId = (select #2))) (cost=2.09 rows=0.05) -> Index range scan on oobo using IX_Out_OutBoundOrders_No over (No = 'TTTNO2022080500000015') OR (No = 'TTTNO2022080500000016') OR (No = 'TTTNO2022080500000017'), with index condition: (oobo.`No` in ('TTTNO2022080500000015','TTTNO2022080500000016','TTTNO2022080500000017')) (cost=2.09 rows=3) -> Select #2 (subquery in condition; run only once) -> Filter: (Bee_MdmWarehouses.`Name` = '') (cost=15.90 rows=12) -> Table scan on Bee_MdmWarehouses (cost=15.90 rows=119) -> Covering index lookup on oobod using IX_Out_OutBoundOrderDetails_OutBoundOrderId (OutBoundOrderId=oobo.Id) (cost=4.98 rows=2)
复制代码
JSON 格式：
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "429640.64" }, "table": { "table_name": "t", "access_type": "index", "possible_keys": [ "IX_Basic_KeyOperationLogs_LogKey_TraceId_WarehouseId" ], "key": "IX_Basic_KeyOperationLogs_LogKey_TraceId_WarehouseId", "used_key_parts": [ "LogKey", "TraceId", "WarehouseId" ], "key_length": "1175", "rows_examined_per_scan": 1612255, "rows_produced_per_join": 161225, "filtered": "10.00", "using_index": true, "cost_info": { "read_cost": "413518.09", "eval_cost": "16122.55", "prefix_cost": "429640.64", "data_read_per_join": "435M" }, "used_columns": [ "TraceId" ], "attached_condition": "(`YHWmsOperations_lastday`.`t`.`TraceId` = 'xxxyum')" } }}
复制代码
执行计划怎么看？
先初步认识执行计划中的各项列信息：
id 列：
含义：表示执行顺序，规矩是谁大谁先执行。如果 Id 相同，则自上而下按顺序执行。
示例1
示例2
select_type 列：
含义：表示查询类型。查询类型有很多，如下图所示：
但实际常见的也就如下几个：
table 列：
含义：表名。如果表没有取别名或者表是一个视图，则显示为完整名
partition 列：
表示当前查询用到哪个分区。
type 列：
表示扫描类型。
常见的显示值有：system、const、eq_ref、ref、range、index、all
其中，system 比较特殊，是当这个表只有 1 行数据时才会显示为 system。
const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件的查询 sql 中
eq_ref：常出现在主键或唯一索引上进行等值查找，只会出现在被驱动表上。
ref：常出现在普通索引等值查找
range：常用于范围查询
index：全索引扫描
all：全表扫描
性能从高到低排列：system > const > eq_ref > ref > range > index > all
possible_keys 列：
含义：表示可能会用到哪些索引；仅具有参考意义
key 列：
表示实际用到了哪个索引；
key_len 列：
表示当前选用的索引长度；
注意：如果执行计划显示使用了组合索引，那么 key_len 取决于命中组合索引中哪些列。
例子：
name 字段，varchar(16)
code 字段，varchar(32)
rq 字段：date（3 字节）
字段编码均为 utf8mb4
组合索引 IX_TEST (name,code,rq)
where name = 'test'：key_len=16*4=64
where name = 'test' and code = 'tgb001' and rq = '2024-12-03'：key_len=16*4+32*4+3=195
row 列：
表示预估要扫描的行数；数值来源于统计信息，非精确行数。
filtered 列：
表示 按条件过滤的表行的估计百分比；粗略预估，非准确数值。
比如估算 row 行数有 1000 行，filterd 显示 50%表示 过滤掉了 50%的数据。
Extra 列：
含义：一些额外的提示信息。
由于涉及到太多的信息这里就不一一列举，只展示下列常见的几个：
正面：
中性：
负面：
-- Impossible WHEREEXPLAIN SELECT * FROM Bee_MdmWarehouses WHERE 1=2;
-- Using indexEXPLAIN SELECT COUNT(*) FROM OP_InventoryAccountAdvances;
-- Using index for group-byEXPLAIN SELECT account.WarehouseId FROM OP_InventoryAccountAdvances account GROUP BY account.WarehouseId;
-- Using whereexplain select count(*) from OP_InventoryAccountAdvances oiaa where 1=1 AND warehouseid = '3a005819-15b1-8fe1-33b5-1831545559d1' and isdeleted = 1;
-- Using filesortexplain select * from OP_InventoryAccountAdvances oiaa order by oiaa.CustomerId;
-- Using temporaryexplain select count(*) from OP_InventoryAccountAdvances oiaa group by oiaa.isdeleted;
-- Using join buffer-- alter table Basic_CargoSettings alter index IX_Basic_CargoSettings_CargoId_WarehouseId invisible;explain select * from Basic_Cargos bc join Basic_CargoSettings bcs on bc.Id = bcs.CargoId where bc.Code = 'LZM0010';
复制代码
哪些列最值得关注？
Id 列，需要知道整个 SQL 的执行顺序是怎样的。因为顺序问题，在多表关联场景下可能会选择错误的驱动表，从而导致性能差。
type 列，需要知道用什么方式查找数据的，全表扫描？or 索引扫描？
key 列，是否有命中索引？命中的索引是否为最佳索引？
extra 列，根据辅助信息进一步分析优化
/* CorrelationId:645bfeb9544c19c846fa96f95c26ce8f /YH/Wms/Dapper/SqlScripts/OutBounds/OutBoundOrders/QuerySignedBatchDetailsVerification.sql */ SELECT oobo.Id AS OutBoundId, oobo.Id AS CustomerId, oobo.ProjectId AS ProjectId, -- 业务机会ID oobo.Status AS Status, -- 出库单状态 ap.IsIntercepted AS IsIntercepted, -- 是否拦截 oobnooi.IsSpecialPreSaleOrder, -- 是否极效前置订单 oobbo.`No` AS BatchNo -- 批次号 FROM Out_OutBoundOrders oobo LEFT JOIN Out_VirtualPackages ovp ON ovp.OutBoundOrderId = oobo.Id and ovp.IsDeleted = 0 LEFT JOIN Out_OutBoundNoticeOrderOtherInfos oobnooi ON oobnooi.OutBoundNoticeOrderId = oobo.OutBoundNoticeOrderId and oobnooi.IsDeleted = 0 LEFT JOIN Out_OutBoundBatchOrderDetails oobbod ON oobbod.OutBoundOrderId = oobo.Id AND oobbod.IsDeleted = 0 LEFT JOIN Out_OutBoundBatchOrders oobbo ON oobbo.Id = oobbod.OutBoundBatchOrderId AND oobo.IsDeleted = 0 LEFT JOIN Out_VirtualActualRelations ovar ON ovp.Id = ovar.VirtualPackageId AND ovp.IsDeleted = 0 LEFT JOIN Out_ActualPackages ap ON ovar.ActualPackageId = ap.Id and ap.IsDeleted = 0 WHERE oobo.OutBoundType = 10 AND oobo.IsDeleted = 0 AND oobo.WarehouseId = '3a0e34ee-44b0-2e79-b8b9-fd1b45dd6380' AND oobo.LogisticsNo = 'BHXJ5467';
复制代码
/* CorrelationId:8ec66cdd006ec05252556827f37168bc /YH/Wms/QueryManagement/SqlScripts/OutBounds/OutBoundWavingOrders/QueryWaveCollectInfo.sql */SELECT DISTINCT obwo.id AS OutBoundOrderId, obwo.CustomerId AS CustomerId, ctr.NAME AS CustomerName, obwo.CarrierId AS CarrierId, car.NAME AS CarrierName, obwo.WarehouseIdFROM Out_OutBoundWavingOrders obwo LEFT JOIN Out_OutBoundOrders obn ON obn.id = obwo.id AND obn.IsDeleted = 0 LEFT JOIN Out_OutBoundNoticeOrders oobno ON oobno.id = obn.OutBoundNoticeOrderId AND oobno.IsDeleted = 0 LEFT JOIN Bee_MdmCustomers ctr ON ctr.id = obwo.CustomerId AND ctr.IsDeleted = 0 LEFT JOIN Bee_MdmCarriers car ON car.id = obwo.CarrierId AND car.IsDeleted = 0 LEFT JOIN Out_OutBoundOrderOtherInfos oooi ON obn.Id = oooi.OutBoundOrderId AND oooi.IsDeleted = 0 LEFT JOIN Out_OutBoundNoticeOrderOtherInfos noticeOrderOtherInfos ON oobno.Id = noticeOrderOtherInfos.OutBoundNoticeOrderId AND noticeOrderOtherInfos.IsDeleted = 0WHERE obwo.IsDeleted = 0 AND obwo.WarehouseId = '3a0e34ee-44b0-2e79-b8b9-fd1b45dd6380' AND obn.Status = 20 AND ( obn.IsNeedAllocateAdvanceAccount = 0 OR ( obn.IsNeedAllocateAdvanceAccount = 1 AND obn.IsAllocatedAdvanceAccount = 1 AND obn.PaymentStatus IN(0, 1) ) ) AND obwo.OutBoundType = 10 AND obwo.CarrierId = '3a05764c-a5ba-5e0b-2086-2a07f25c404d' AND oooi.OrderCargoCategory IN (0, 1, 2, 3) AND noticeOrderOtherInfos.IsPrePack = 0 AND oooi.SpecialOrderTag = 0;
复制代码
误区解读：
没用！多表关联查询的 SQL 中，表的书写顺序 不一定 等于真正执行的顺序。关联驱动的真正执行顺序，是取决于最终执行计划的！
评论