SQL优化-实战SQL优化

最近收到公司部门慢SQL优化协助请求，整好结合这次慢SQL优化过程将相关知识做了分享

SQL优化-深入了解SQL处理流程原理（Server层与存储引擎交互、数据管理结构）-CSDN博客

SQL优化 - 索引成本计算和优化建议-CSDN博客

接收到原慢SQL内容以及相关资讯：

SQL内容：（索引内容，分析过程中给出），执行耗时：>30s

select
   .... 省略，一堆需要回表的字段，但是根据业务需求，是在没办法进行索引覆盖
from
  material_requirements a
	
  inner join material_requirements_detail b 
	on b.tenantsid = a.tenantsid
  and b.eoc_company_id = a.eoc_company_id
  and b.eoc_site_id = a.eoc_site_id
  and b.wo_no = a.wo_no
  and b.op_no = a.op_no
  and b.item_no = a.item_no
  and b.item_feature_no = a.item_feature_no
	
  inner join wo c 
	on c.tenantsid = a.tenantsid
  and c.eoc_company_id = a.eoc_company_id
  and c.eoc_site_id = a.eoc_site_id
  and c.wo_no = a.wo_no
	
  LEFT JOIN wo_demand AS c1 
	ON c1.tenantsid = c.tenantsid
  AND c1.wo_id = c.wo_id
	
  inner join working_dayplan_summary d 
	on d.tenantsid = a.tenantsid
  and d.eoc_company_id = a.eoc_company_id
  and d.eoc_site_id = a.eoc_site_id
  and d.wo_no = a.wo_no
  and d.op_no = a.op_no
  and d.plan_date = b.plan_date
where
  a.tenantsid = 426260985303616
  and a.eoc_company_id = '1'
  AND D.wo_no IN (
	      "2000-202309210001",
        "5101-230521001",
        "HYDJ-202308220001",
        "HYDJ-202308220002",
        "HYDJ-202308240001",
        ..... 此处省略，总计130个条件
        "tv01-202209050001",
        "tv01-202308040001",
        "tv01-202308070001",
        "tv01-202308090001",
        "tv01-202308100001",
        "tv02-202209050001",
        "tv04-202209050001",
        "tv05-202209050001",
        "tvv-202310170001"
)

1、EXPALIN执行计划分析

从执行计划上乍一看似乎都使用到了索引，除了存在Using where（附件添加过滤）也没什么其他条件，但是根据成本计算模型，b表rows最多，且是关联表查询，那就意味着b，c表在需要回表的情况下，可能成本都比较高。

以上只是一个估算的结果，针对这种多表查询优化就需要有耐心的去分析。

还有一点就是执行计划顺序在优化器作用下进行了关联顺序和条件的调整。

2、FORMAT=JSON分析

如果在EXPLAIN不能准确的判断问题原因，因为生产区没有开启OPTIMIZER_TRACE，那可以借助FORMAT=JSON来看更详细的索引和成本信息。

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19390.51"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "c",  // 驱动表
            "access_type": "range",
            "possible_keys": [
              "tenantsid",
              "wo_wo_no_IDX",
              "idx_tenantsid_eoc_company_id_wo_id",
              "idx_tenantsid_eoc_company_id_wo_no",
              "idx_tenantsid_create_date"
            ],
            "key": "wo_wo_no_IDX",  // 使用索引
            "used_key_parts": [
              "wo_no"   // 索引字段使用
            ],
            "key_length": "203",
            "rows_examined_per_scan": 130,  // 扫描行数
            "rows_produced_per_join": 0,
            "filtered": "0.13",
			// 索引过滤，索引下推
            "index_condition": "(`bm_mosc`.`c`.`wo_no` in ('2000-202309210001','5101-230521001','HYDJ-202308220001','HYDJ-202308220002','HYDJ-202308240001','HYDJ-202308240003','HYDJ-202308300001','HYDJ-202308300003','HYDJ-202308300006','HYDJ-202308300008','HYDJ-202309180001','HYDJ-202309180002','HYDJ-202309180003','HYDJ-202309180004','HYDJ-202309180005','HYDJ-202309180006','HYDJ-202309180007','HYDJ-202309180009','HYDJ-202309180010','HYDJ-202309180011','HYDJ-202309190001','HYDJ-202309190002','HYDJ-202309190003','HYDJ-202309190004','HYDJ-202309200001','HYDJ-202309220001','HYDJ-202309220003','HYDJ-202309250001','HYDJ-202309280001','HYDJ-202310170002','HYDJ-202310170003','HYDJ-202310230002','HYDJ-202310230003','HYDJ-202310230004','HYDJ-202310230005','HYDJ-202310230006','HYDJ-202310230007','HYDJ-202310230008','HYDJ-202310230009','HYDJ-202311070001','HYDJ-202311070002','HYDJ-202311070003','HYDJ-202311070004','HYDJ-202311070005','HYDJ-202311090002','HYDJ-202311090003','HYDJ-202311230001','HYDJ-202311230002','HYDJ-202311290001','HYDJ-202311290002','HYDJ-202311290003','HYDJ-202311290004','HYDJ-202311290005','HYDJ-202311290006','HYDJ-202311290007','HYDJ-202312210001','M511-202302240003','M511-202305300001','S600-202211180002','S600-202211180003','S600-202211180004','S600-202212070001','S600-202212080001','S600-202212080002','S600-202212080003','S600-202212300001','S600-202301100001','S600-202301100002','S600-202301100003','S600-202301100004','S600-202301300001','S600-202301300002','S600-202301310001','S600-202301310002','S600-202302200001','S600-202302200002','S600-202303210001','S600-202304130001','S600-202304130002','S600-202304140001','S600-202305090001','S600-202305100001','S600-202305190001','S600-202305300002','S600-202305310001','S600-202305310002','S600-202306010001','S600-202306300001','S600-202306300002','S600-202306300003','S600-202307040001','S600-202307040002','S600-202307040003','S600-202307100001','S600-202307100002','S600-202307100003','S600-202307120001','S600-202307120002','S600-202307120003','S600-202307270001','S600-202308070002','S600-202308080001','S600-202308210001','S600-202308220001','S600-202308290001','S600-202308290002','S600-202308300001','S600-202308300002','bike-202209050001','bike-202209050002','bike-202211030001','book-202306080001','cup-202307030001','jack-202303290001','mg01-202312190001','p12-202209060002','p12-202209070001','p12-202307190001','p12-202307190002','p子-202209270001','p子-202211030001','tv01-202209050001','tv01-202308040001','tv01-202308070001','tv01-202308090001','tv01-202308100001','tv02-202209050001','tv04-202209050001','tv05-202209050001','tvv-202310170001'))",
            // 成本数据
			"cost_info": {
              "read_cost": "311.98",
              "eval_cost": "0.03",
              "prefix_cost": "312.01",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "wo_id",
              "tenantsid",
              "eoc_company_id",
              "eoc_site_id",
              "wo_no",
              "item_no",
              "item_name",
              "item_spec",
              "item_feature_no",
              "plan_qty",
              "unit_no",
              "unit_name",
              "plan_start_date",
              "plan_complete_date",
              "demand_doc_no"
            ],
			// 附加条件，即非索引使用条件过滤，这个需要在server层进行
            "attached_condition": "((`bm_mosc`.`c`.`tenantsid` = 426260985303616) and (`bm_mosc`.`c`.`eoc_company_id` = 1))"
          }
        },
        {
          "table": {
            "table_name": "d",   // 被驱动表
            "access_type": "ref",
            "possible_keys": [
              "summary_b001"
            ],
            "key": "summary_b001",
            "used_key_parts": [
              "tenantsid",
              "wo_no"
            ],
            "key_length": "211",
            "ref": [
              "const",
              "bm_mosc.c.wo_no"
            ],
            "rows_examined_per_scan": 2,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.46",
              "eval_cost": "0.09",
              "prefix_cost": "312.57",
              "data_read_per_join": "556"
            },
            "used_columns": [
              "working_dayplan_summary_id",
              "tenantsid",
              "wo_no",
              "op_no",
              "plan_date",
              "eoc_company_id",
              "eoc_site_id",
              "plan_qty"
            ],
            "attached_condition": "<if>(is_not_null_compl(d), ((`bm_mosc`.`d`.`eoc_site_id` = `bm_mosc`.`c`.`eoc_site_id`) and (`bm_mosc`.`d`.`eoc_company_id` = 1)), true)"
          }
        },
        {
          "table": {
            "table_name": "a",
            "access_type": "ref",
            "possible_keys": [
              "material_requirements_k001"
            ],
            "key": "material_requirements_k001",
            "used_key_parts": [
              "tenantsid",
              "eoc_company_id",
              "wo_no",
              "op_no"
            ],
            "key_length": "457",
            "ref": [
              "bm_mosc.d.tenantsid",
              "bm_mosc.d.eoc_company_id",
              "bm_mosc.d.wo_no",
              "bm_mosc.d.op_no"
            ],
            "rows_examined_per_scan": 7,
            "rows_produced_per_join": 3,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "3.49",
              "eval_cost": "0.70",
              "prefix_cost": "316.75",
              "data_read_per_join": "13K"
            },
            "used_columns": [
              "material_requirements_id",
              "tenantsid",
              "project_no",
              "wo_no",
              "op_no",
              "op_name",
              "item_no",
              "item_name",
              "item_spec",
              "item_feature_no",
              "item_property",
              "composition_qty",
              "inventory_qty",
              "wait_stock_in_qty",
              "eoc_company_id",
              "eoc_site_id",
              "eoc_region_id"
            ],
            "attached_condition": "<if>(is_not_null_compl(a), ((`bm_mosc`.`a`.`eoc_site_id` = `bm_mosc`.`c`.`eoc_site_id`) and (`bm_mosc`.`a`.`op_no` = `bm_mosc`.`d`.`op_no`) and (`bm_mosc`.`d`.`op_no` = `bm_mosc`.`a`.`op_no`)), true)"
          }
        },
        {
          "table": {
            "table_name": "b",
            "access_type": "ref",
            "possible_keys": [
              "material_requirements_detail_k001"
            ],
            "key": "material_requirements_detail_k001",
            "used_key_parts": [
              "tenantsid",
              "eoc_company_id",
              "eoc_site_id"
            ],
            "key_length": "414",
            "ref": [
              "bm_mosc.a.tenantsid",
              "bm_mosc.a.eoc_company_id",
              "bm_mosc.a.eoc_site_id"
            ],
            "rows_examined_per_scan": 2359,
            "rows_produced_per_join": 8230,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "6706.99",
              "eval_cost": "1646.14",
              "prefix_cost": "8669.88",
              "data_read_per_join": "15M"
            },
            "used_columns": [
              "material_requirements_detail_id",
              "tenantsid",
              "wo_no",
              "op_no",
              "item_no",
              "item_feature_no",
              "plan_date",
              "demand_date",
              "delivery_demand_date",
              "demand_qty",
              "distribute_qty",
              "issue_qty",
              "lack_qty",
              "eoc_company_id",
              "eoc_site_id"
            ],
            "attached_condition": "<if>(is_not_null_compl(b), ((`bm_mosc`.`b`.`plan_date` = `bm_mosc`.`d`.`plan_date`) and (`bm_mosc`.`b`.`item_feature_no` = `bm_mosc`.`a`.`item_feature_no`) and (`bm_mosc`.`b`.`item_no` = `bm_mosc`.`a`.`item_no`) and (`bm_mosc`.`b`.`op_no` = `bm_mosc`.`a`.`op_no`) and (`bm_mosc`.`b`.`wo_no` = `bm_mosc`.`a`.`wo_no`)), true)"
          }
        },
        {
          "table": {
            "table_name": "c1",
            "access_type": "ref",
            "possible_keys": [
              "wo_demand_pk",
              "wo_id"
            ],
            "key": "wo_id",
            "used_key_parts": [
              "tenantsid",
              "wo_id"
            ],
            "key_length": "16",
            "ref": [
              "const",
              "bm_mosc.c.wo_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 8933,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "8933.86",
              "eval_cost": "1786.77",
              "prefix_cost": "19390.52",
              "data_read_per_join": "28M"
            },
            "used_columns": [
              "wo_demand_id",
              "tenantsid",
              "wo_id",
              "customer_no",
              "customer_name"
            ]
          }
        }
      ]
    }
  }
}

为了更好清楚执行的SQL内容，这里根据执行计划将优化器处理后的SQL复现出来：

步骤1：

从上面可以看出来c表是驱动表，被驱动表是d，所以初始查询应该是这样（先不考虑查出栏位）：

select *** from wo c left join working_dayplan_summary d 

因为执行计划中是按照顺序执行的，所以这边可以直接用left join连接。

步骤2：

在c表执行计划中，使用到了wo_wo_no_IDX索引，且索引栏位为wo_no ，同时存在索引过滤index_condtion

select *** from wo c left join working_dayplan_summary d 
where c.wo_no in (.....)   // 索引查询和 过滤（因为MySQL在索引条件中加了附加条件）
and c.eoc_company_id = 1   // server层过滤
and c.tenantsid = 426260985303616   // server层过滤

步骤3：

在d表执行计划中，使用到了summary_b001索引，且索引栏位为tenantsid+wo_no ，同时存在附件条件attached_condition

select *** from wo c 

left join working_dayplan_summary d 
on d.tenantsid = c.tenantsid  // 索引查询
and d.wo_no = c.wo_no         // 索引查询
and d.eoc_site_id = c.eoc_site_id  // server层过滤
and d.eoc_company_id = 1           // server层过滤


where c.wo_no in (.....)   // 索引查询和 过滤（因为MySQL在索引条件中加了附加条件）
and c.eoc_company_id = 1   // server层过滤
and c.tenantsid = 426260985303616   // server层过滤

同样的方式复现出执行SQL:

select 
  。。。。。
 from  wo c   -- 成本312.01，扫描行数 130

 left join working_dayplan_summary d  -- 成本312.57 ，扫描行数 2
 on d.tenantsid = c.tenantsid  -- 索引查询
 and d.wo_no = c.wo_no         -- 索引查询 
 and d.eoc_site_id = c.eoc_site_id    -- server过滤
 and d.eoc_company_id = 1             -- server过滤 
 
 left join material_requirements a     成本316.75  ，扫描行数 7
 on a.tenantsid = d.tenantsid            -- 索引查询
 and a.eoc_company_id=d.eoc_company_id   -- 索引查询
 and a.wo_no = d.wo_no                   -- 索引查询
 and a.op_no = d.op_no                   -- 索引查询
 and a.eoc_site_id = c.eoc_site_id       -- server过滤 
 
 LEFT JOIN material_requirements_detail b   成本8669.88   ，扫描行数 2359
 on b.tenantsid = a.tenantsid             -- 索引查询
 and b.eoc_company_id = a.eoc_company_id  -- 索引查询
 and b.eoc_site_id = a.eoc_site_id        -- 索引查询
 and b.wo_no = a.wo_no                    -- server过滤    
 and b.op_no = a.op_no                    -- server过滤 
 and b.item_no =a.item_no                 -- server过滤 
 and b.item_feature_no = a.item_feature_no  -- server过滤  
 and b.plan_date = d.plan_date            -- server过滤   
  
 LEFT JOIN wo_demand c1                   --成本19390.52    ，扫描行数 130
 on c1.wo_id = c.wo_id                    -- 索引查询
 and c1.tenantsid = 426260985303616       -- 索引查询
 
 where 
c.wo_no in ( "2000-202309210001",
        "5101-230521001",
        ............
        "tvv-202310170001") 

and c.eoc_company_id = 1    
and c.tenantsid = 426260985303616

为了更好的理解执行计划内容，复现了下优化器作用后的SQL，真实SQL分析过程中，可以直接看执行计划内容来做判断。

3、执行计划以及索引分析

以上内容可以发现：

1、c表使用到了索引wo_wo_no_IDX，索引栏位为wo_no，看下c表索引状况：

发现除了wo_wo_no_IDX，之外还有其他索引可以使用，比如为什么不适用tenantsid索引呢，使用tenantsid、eoc_company_id做索引查询，wo_no做索引下推呢？

这里就要看下的当前索引栏位基数（Cardinality）了，也就是选择性高低，看的出来wo_no基数非常大，即不重复的数据。那就意味着我一个wo_no条件检索后，能过滤掉更多的数据。现在总表数据为77217 ，那就意味着一个wo_no条件查出来数据约?77217 /?76488 ~ 1条数据，而一个tenantsid查出来?77217 /?22 ~ 3509数据，所以相对来说?wo_no栏位作为索引检索选择性更高

那既然这样，我们首要的就是保障wo_no做索引条件，但是可以让tenantsid和eoc_company_id进行索引下推或者直接联合索引

那可以建立索引（wo_no，tenantsid，eoc_company_id），一定要wo_no在首位，但是因为这边扫描数据行数130笔，且成本不是很高，即使加了上述索引扫描行数结果也不会变，固对后续查询的和回表成本影响也不会表，因此提效不明显。

2、d表使用到了索引summary_b001，索引查询栏位tenantsid、wo_no，但是eoc_site_id和eoc_company_id栏位是在server层过滤的，也就是没用到索引，先看下索引情况

因为d表只有这一个可用索引，且因为最左原则关系只能使用到tenantsid、wo_no进行搜索，但是从索引上来看应该可以使用到eoc_site_id、eoc_company_id作为下推条件，但是优化器确让其走了Server过滤。这个具体原因不太清楚，猜想应该是因为当前关联每次循环扫描数据量为2条，且都能满足eoc_site_id、eoc_company_id条件，所以下推没有意义。对于这块可以提效的点就是建立联合索引满足最左原则（tenantsid、wo_no、eoc_site_id、eoc_company_id），但是如果上述推断正确此处也没什么明显提效。

3、a表使用到索引material_requirements_k001，索引栏位tenantsid、eoc_company_id、wo_no、op_no，但是eoc_site_id条件走的server过滤，看下索引情况

当前索引的问题就是没有覆盖到eoc_site_id，导致了server层过滤，同属上面的结论，建立联合索引或者索引覆盖，使其能索引下推，当然如果搜索扫描结果能都满足eoc_site_id条件，可能也不会走下推。在索引最后加上eoc_site_id。

4、b表使用到了索引material_requirements_detail_k001，索引使用栏位tenantsid，eoc_company_id，eoc_site_id，Server层过滤栏位wo_no、op_no、item_no、item_feature_no、plan_date ，扫描行数2359，成本8669.88，哦豁！！！，看着这兴奋起来了，问题来了，关联查询可以理解为嵌套循环，这一次每笔数据都需要扫描2359行，就简单按照c表行数来算130 *?2359 这让后续的关联咋玩。看索引情况：

这里可以又发现了一个问题？ 好像这边也可以满足索引下推的条件，为什么又没有做索引下推呢？

居然两次都遇到这样的问题，受不了了，自己做了很多的验证，发现关联查询条件好像使用索引下推用不起来，但是通过官网来看，并没有这样的约束。于是找了DBA和其他架构师咨询了下，可惜并没得到想要的结论，大致意思就是优化器模型会考虑更多因素，最终选择最优效率执行路径，所以我们不能用满足的条件来当作必然的条件。唉，既然这样那就后续花时间在研究下吧。

既然没有使用到索引下推，那就直接联合索引吧，这里可以看出来因为最左原则约束，使用到索引有限，通过Cardinality来看，这些wo_no、op_no、item_no、item_feature_no、plan_date更应该被用作索引

所以两种方式

• 调整索引：将当前索引调整为wo_no、op_no、item_no、item_feature_no、plan_date作为前缀，满足更高的选择性。
• 联合索引：使用联合索引覆盖这些栏位，且满足最左原则（最终效率更高）

5、c1表索引wo_id，索引栏位tenantsid、wo_id。这个没啥好说的，使用到了索引，也没有需要过滤的，但是由于b表扫描行数大，所以导致其成本也就高，因为需要循环读取。

4、优化处理

目前分析结果来看，b表问题很大，需要进行索引优化，建立联合索引：

material_requirements_detail_k002（ tenantsid，eoc_company_id，eoc_site_id，wo_no，op_no，item_no，item_feature_no，delivery_demand_date，plan_date，demand_date，project_no ）

如果索引长度过长，建议将选择性底的栏位过剔除，做Server过滤。建立索引目都在索引检索时能过滤掉更多的数据，从而减少回表以及后续关联查询成本，即减少CPU、IO成本。

验证结果：

扫描行数从2000+降到了6

b表成本从7621.60 降到了346 ， c表成本从16997.50降到了378

?

?执行效率从30s+ 降到了100ms

至于为什么没有按照理论的推断使用索引下推问题，再去研究下吧