MySQL Execution Plan--IN子查询包含超多值引发的查询异常1

佚名 6年前 (2019-04-23) 随笔 201人围观抢沙发百度已收录

=======================================================================

SQL语句：

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

SELECT wave_no,
SUM(IF(picking_qty IS NULL, 0, picking_qty)) AS PICKED_QTY, SUM(IF(differ_qty IS NULL, 0, differ_qty)) AS PICKED_DIFFER_QTY, SUM(IF(relocate_qty IS NULL, 0, relocate_qty)) AS PICKED_RELOCATE_QTY FROM picking_locate_d WHERE yn = 0 AND wave_no IN ( 'BC76361213164811', 'BC76361213164810', 'BC76361213154684', 'BC76361213155125' ) AND org_No= '661' AND distribute_No = '763' AND warehouse_No = '612' GROUP BY wave_no;

执行计划：

+----+-------------+------------------+------------+-------+---------------+-------------+---------+------+-------+----------+------------------------------------+
| id | select_type | table            | partitions | type  | possible_keys | key         | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+------------------+------------+-------+---------------+-------------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | picking_locate_d | NULL       | range | idx_wave_no   | idx_wave_no | 153     | NULL | 16000 |     0.10 | Using index condition; Using where |
+----+-------------+------------------+------------+-------+---------------+-------------+---------+------+-------+----------+------------------------------------+

执行计划JOSN：

EXPLAIN: {
  "query_block": { "select_id": 1, "cost_info": { "query_cost": "9548371.80" }, "grouping_operation": { "using_filesort": false, "table": { "table_name": "picking_locate_d", "access_type": "index", "possible_keys": [ "idx_wave_no" ], "key": "idx_wave_no", "used_key_parts": [ "wave_no" ], "key_length": "153", "rows_examined_per_scan": 37518548, "rows_produced_per_join": 1875, "filtered": "0.01", "cost_info": { "read_cost": "9547996.61", "eval_cost": "375.19", "prefix_cost": "9548371.80", "data_read_per_join": "11M" }, "used_columns": [ "id", "wave_no", "picking_qty", "differ_qty", "relocate_qty", "org_no", "distribute_no", "warehouse_no", "yn" ], "attached_condition": "( (`report`.`picking_locate_d`.`yn` = 0) and (`report`.`picking_locate_d`.`wave_no` in ('BC76361213164811','BC76361213164810','BC76361213155124','BC76361213154684','BC76361213155125')) and (`report`.`picking_locate_d`.`org_no` = '661') and (`report`.`picking_locate_d`.`distribute_no` = '763') and (`report`.`picking_locate_d`.`warehouse_no` = '612') )"  } } } }

=======================================================================

将wave_no IN修改为CONCAT(wave_no,'') IN进行测试

SQL语句：

SELECT wave_no,
SUM(IF(picking_qty IS NULL, 0, picking_qty)) AS PICKED_QTY,
SUM(IF(differ_qty IS NULL, 0, differ_qty)) AS PICKED_DIFFER_QTY,
SUM(IF(relocate_qty IS NULL, 0, relocate_qty)) AS PICKED_RELOCATE_QTY FROM picking_locate_d
WHERE  yn = 0
AND CONCAT(wave_no,'') IN
(
'BC76361213164811',
'BC76361213164810',
'BC76361213154684',
'BC76361213155125'
)
AND org_No= '661'
AND distribute_No = '763'
AND warehouse_No = '612'
GROUP BY wave_no

执行计划：

+----+-------------+------------------+------------+-------+---------------+-------------+---------+------+----------+----------+-------------+
| id | select_type | table            | partitions | type  | possible_keys | key         | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+------------------+------------+-------+---------------+-------------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | picking_locate_d | NULL       | index | idx_wave_no   | idx_wave_no | 153     | NULL | 37541282 |     0.01 | Using where |
+----+-------------+------------------+------------+-------+---------------+-------------+---------+------+----------+----------+-------------+

执行计划JSON：

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "9549155.40"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "picking_locate_d",
        "access_type": "index",
        "possible_keys": [
          "idx_wave_no"
        ],
        "key": "idx_wave_no",
        "used_key_parts": [
          "wave_no"
        ],
        "key_length": "153",
        "rows_examined_per_scan": 37522447,
        "rows_produced_per_join": 3752,
        "filtered": "0.01",
        "cost_info": {
          "read_cost": "9548404.95",
          "eval_cost": "750.45",
          "prefix_cost": "9549155.40",
          "data_read_per_join": "22M"
        },
        "used_columns": [
          "id",
          "wave_no",
          "picking_qty",
          "differ_qty",
          "relocate_qty",
          "org_no",
          "distribute_no",
          "warehouse_no",
          "yn"
        ],
        "attached_condition": "(
            (`report`.`picking_locate_d`.`yn` = 0) 
            and (concat(`report`.`picking_locate_d`.`wave_no`,'') in ('BC76361213164811','BC76361213164810','BC76361213154684','BC76361213155125')) 
            and (`report`.`picking_locate_d`.`org_no` = '661') 
            and (`report`.`picking_locate_d`.`distribute_no` = '763') 
            and (`report`.`picking_locate_d`.`warehouse_no` = '612')
        )"
      }
    }
  }
}

=======================================================================

去除org_No/distribute_No/warehouse_No任意列的过滤条件，如去除AND org_No= '661'

SQL语句

SELECT wave_no,
SUM(IF(picking_qty IS NULL, 0, picking_qty)) AS PICKED_QTY,
SUM(IF(differ_qty IS NULL, 0, differ_qty)) AS PICKED_DIFFER_QTY,
SUM(IF(relocate_qty IS NULL, 0, relocate_qty)) AS PICKED_RELOCATE_QTY FROM picking_locate_d
WHERE  yn = 0
AND wave_no IN
(
'BC76361213164811',
'BC76361213164810',
'BC76361213154684',
'BC76361213155125'
)
## AND org_No= '661'
AND distribute_No = '763'
AND warehouse_No = '612'
GROUP BY wave_no;

执行计划：

+----+-------------+------------------+------------+-------+---------------+-------------+---------+------+----------+----------+-------------+
| id | select_type | table            | partitions | type  | possible_keys | key         | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+------------------+------------+-------+---------------+-------------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | picking_locate_d | NULL       | index | idx_wave_no   | idx_wave_no | 153     | NULL | 37541843 |     0.01 | Using where |
+----+-------------+------------------+------------+-------+---------------+-------------+---------+------+----------+----------+-------------+

执行计划JSON

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "38400.01"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "picking_locate_d",
        "access_type": "range",
        "possible_keys": [
          "idx_wave_no"
        ],
        "key": "idx_wave_no",
        "used_key_parts": [
          "wave_no"
        ],
        "key_length": "153",
        "rows_examined_per_scan": 16000,
        "rows_produced_per_join": 15,
        "filtered": "0.10",
        "index_condition": "(
            (`report`.`picking_locate_d`.`wave_no` in ('BC76361213164811','BC76361213164810','BC76361213154684','BC76361213155125')) 
            and (`report`.`picking_locate_d`.`distribute_no` = '763') 
            and (`report`.`picking_locate_d`.`warehouse_no` = '612')
        )",
        "cost_info": {
          "read_cost": "38396.81",
          "eval_cost": "3.20",
          "prefix_cost": "38400.01",
          "data_read_per_join": "98K"
        },
        "used_columns": [
          "id",
          "wave_no",
          "picking_qty",
          "differ_qty",
          "relocate_qty",
          "org_no",
          "distribute_no",
          "warehouse_no",
          "yn"
        ],
        "attached_condition": "(`report`.`picking_locate_d`.`yn` = 0)"
      }
    }
  }
}