【TiDB 版本】：
v3.0.9
【前置条件】：
查询语句
EXPLAIN ANALYZE
SELECT
dc_f_d_s._id AS Id,
dc_f_d_s._is_del AS IsDel,
dc_f_d_s._create_user AS CreateUser,
dc_f_d_s._create_time AS CreateTime,
dc_f_d_s._update_user AS UpdateUser,
dc_f_d_s._update_time AS UpdateTime,
dc_f_d_s._version_no AS VersionNo,
dc_f_d_s._is_disable AS IsDisable,
dc_f_d_s.distributor_code AS distributorCode,
dc_f_d_s.seller_name AS sellerName,
dc_f_d_s.organ_name AS organName,
dc_f_d_s.quantity AS quantity,
dc_f_d_s.normal_quantity AS normalQuantity,
dc_f_d_s.seller_date AS sellerDate,
dc_f_d_s.organ_enable AS organEnable,
dc_f_d_s.product_enable AS productEnable,
dc_f_d_s.data_flag AS dataFlag,
dc_f_d_s.source_file_id AS sourceFileId,
dc_f_d_s.source AS source,
dc_f_d_s.amount AS amount,
dc_f_d_s.product_code AS productCode,
dc_f_d_s.normal_seller_code AS normalSellerCode,
dc_f_d_s.normal_price AS normalPrice,
dc_f_d_s.normal_organ_code AS normalOrganCode,
dc_f_d_s.price AS price,
dc_f_d_s.br_check_description AS brCheckDescription,
dc_f_d_s.normal_amount AS normalAmount,
dc_f_d_s.normal_product_code AS normalProductCode,
dc_f_d_s.product_name AS productName,
dc_f_d_s.agent_status AS agentStatus,
dc_f_d_s.organ_address AS organAddress,
dc_f_d_s.specification AS specification,
dc_f_d_s.vender_info AS venderInfo,
dc_f_d_s.odd_num AS oddNum,
dc_f_d_s.belong_month AS belongMonth,
dc_f_d_s.lot_no AS lotNo,
dc_f_d_s.validity_date AS validityDate,
dc_orga_m.organ_name AS distributorName,
dc_orga_m.organ_Used_Names AS distributorUsedNames,
dc_orga_m.organ_type AS organType,
dc_orga_m.nature AS nature,
dc_orga_m.province AS province,
dc_orga_m.city AS city,
dc_orga_m.district AS district,
dc_orga_m0.organ_name AS normalSellerName,
dc_orga_m0.organ_used_names AS normalSellerUsedNames,
dc_orga_m0.organ_type AS normalSellerOrganType,
dc_orga_m0.nature AS normalSellerNature,
dc_orga_m0.province AS normalSellerProvince,
dc_orga_m0.city AS normalSellerCity,
dc_orga_m0.district AS normalSellerDistrict,
dc_orga_m0.is_territory AS normalSellerIsTerritory,
dc_orga_m0.head_Office_Code AS normalSellerHeadOfficeCode,
dc_orga_m0.head_Office_name AS normalSellerHeadOfficeName,
dc_orga_m1.organ_name AS normalOrganName,
dc_orga_m1.organ_used_names AS normalOrganUsedNames,
dc_orga_m1.organ_type AS normalOrganOrganType,
dc_orga_m1.nature AS normalOrganNature,
dc_orga_m1.province AS normalOrganProvince,
dc_orga_m1.city AS normalOrganCity,
dc_orga_m1.district AS normalOrganDistrict,
dc_orga_m1.is_territory AS normalOrganIsTerritory,
dc_orga_m1.head_Office_Code AS normalOrganHeadOfficeCode,
dc_orga_m1.head_Office_name AS normalOrganHeadOfficeName
FROM
-- dc_f_d_s 表中有 300万数据/每日
dc_f_d_s
-- dc_orga_m 表中有80万数据
LEFT JOIN ( SELECT organ_code, organ_name, organ_Used_Names, organ_type, nature, province, city, district FROM dc_orga_m WHERE _is_del = '0' ) dc_orga_m ON dc_f_d_s.distributor_code = dc_orga_m.organ_code
LEFT JOIN ( SELECT organ_code, organ_name, organ_used_names, organ_type, nature, province, city, district, is_territory, head_Office_Code, head_Office_name FROM dc_orga_m WHERE _is_del = '0' ) dc_orga_m0 ON dc_f_d_s.normal_seller_code = dc_orga_m0.organ_code
LEFT JOIN ( SELECT organ_code, organ_name, organ_used_names, organ_type, nature, province, city, district, is_territory, head_Office_Code, head_Office_name FROM dc_orga_m WHERE _is_del = '0' ) dc_orga_m1 ON dc_f_d_s.normal_organ_code = dc_orga_m1.organ_code
WHERE
1 = 1
AND ( dc_f_d_s.agent_status <> '1' OR dc_f_d_s.agent_status IS NULL )
AND dc_f_d_s.normal_product_code <> '99999'
AND dc_f_d_s.normal_organ_code <> '99999'
AND dc_f_d_s.normal_seller_code <> '99999'
AND dc_f_d_s.br_status = '3'
AND ( ( dc_f_d_s.data_flag <> '1' AND dc_f_d_s.data_flag <> '2' ) OR dc_f_d_s.data_flag IS NULL )
AND dc_f_d_s._update_time >= '2020-01-03 15:03:39'
LIMIT 1300000,10000;
执行计划
+----------------------------------------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+-----------------------+
| id | count | task | operator info | execution info | memory |
+----------------------------------------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+-----------------------+
| Projection_25 | 10000.00 | root | 字段太长省略................ | time:12.818718121s, loops:12, rows:10000 | N/A |
| └─Limit_28 | 10000.00 | root | offset:1300000, count:10000 | time:12.816417232s, loops:12, rows:10000 | N/A |
| └─HashLeftJoin_34 | 1310000.00 | root | left outer join, inner:TableReader_118, equal:[eq(pressure_test.dc_f_d_s.normal_organ_code, pressure_test.dc_orga_m.organ_code)] | time:12.814578845s, loops:1280, rows:1310000 | 151.3218536376953 MB |
| ├─Limit_37 | 1310000.00 | root | offset:0, count:1310000 | time:12.797283804s, loops:1281, rows:1310000 | N/A |
| │ └─HashLeftJoin_43 | 1310000.00 | root | left outer join, inner:TableReader_103, equal:[eq(pressure_test.dc_f_d_s.normal_seller_code, pressure_test.dc_orga_m.organ_code)] | time:12.790096075s, loops:1280, rows:1310000 | 151.3743438720703 MB |
| │ ├─Limit_46 | 1310000.00 | root | offset:0, count:1310000 | time:9.884467619s, loops:1281, rows:1310000 | N/A |
| │ │ └─HashLeftJoin_52 | 1310000.00 | root | left outer join, inner:TableReader_88, equal:[eq(pressure_test.dc_f_d_s.distributor_code, pressure_test.dc_orga_m.organ_code)] | time:9.877744038s, loops:1280, rows:1310000 | 119.53673934936523 MB |
| │ │ ├─Limit_53 | 1310000.00 | root | offset:0, count:1310000 | time:6.438666326s, loops:1281, rows:1310000 | N/A |
| │ │ │ └─TableReader_59 | 1310000.00 | root | data:Limit_58 | time:6.43335531s, loops:1280, rows:1310000 | 522.0541105270386 MB |
| │ │ │ └─Limit_58 | 1310000.00 | cop | offset:0, count:1310000 | proc max:304ms, min:101ms, p80:231ms, p95:304ms, rows:1392360, iters:2490, tasks:19 | N/A |
| │ │ │ └─Selection_57 | 1310000.00 | cop | eq(pressure_test.dc_f_d_s.br_status, "3"), ge(pressure_test.dc_f_d_s._update_time, 2020-01-03 15:03:39.000000), ne(pressure_test.dc_f_d_s.normal_organ_code, "99999"), ne(pressure_test.dc_f_d_s.normal_product_code, "99999"), ne(pressure_test.dc_f_d_s.normal_seller_code, "99999"), or(and(ne(pressure_test.dc_f_d_s.data_flag, "1"), ne(pressure_test.dc_f_d_s.data_flag, "2")), isnull(pressure_test.dc_f_d_s.data_flag)), or(ne(pressure_test.dc_f_d_s.agent_status, "1"), isnull(pressure_test.dc_f_d_s.agent_status)) | proc max:304ms, min:101ms, p80:231ms, p95:304ms, rows:1392360, iters:2490, tasks:19 | N/A |
| │ │ │ └─TableScan_56 | 4064001.00 | cop | table:dc_f_d_s, range:[-inf,+inf], keep order:false | proc max:250ms, min:87ms, p80:199ms, p95:250ms, rows:2458311, iters:2490, tasks:19 | N/A |
| │ │ └─TableReader_88 | 772894.27 | root | data:Selection_87 | time:4.743909822s, loops:756, rows:772916 | 44.283196449279785 MB |
| │ │ └─Selection_87 | 772894.27 | cop | eq(pressure_test.dc_orga_m._is_del, "0"), not(isnull(pressure_test.dc_orga_m.organ_code)) | proc max:266ms, min:23ms, p80:266ms, p95:266ms, rows:772916, iters:819, tasks:4 | N/A |
| │ │ └─TableScan_86 | 820360.00 | cop | table:dc_orga_m, range:[-inf,+inf], keep order:false | proc max:252ms, min:22ms, p80:252ms, p95:252ms, rows:820360, iters:819, tasks:4 | N/A |
| │ └─TableReader_103 | 772894.27 | root | data:Selection_102 | time:7.660244492s, loops:756, rows:772916 | 54.591779708862305 MB |
| │ └─Selection_102 | 772894.27 | cop | eq(pressure_test.dc_orga_m._is_del, "0"), ne(pressure_test.dc_orga_m.organ_code, "99999"), not(isnull(pressure_test.dc_orga_m.organ_code)) | proc max:277ms, min:22ms, p80:277ms, p95:277ms, rows:772916, iters:819, tasks:4 | N/A |
| │ └─TableScan_101 | 820360.00 | cop | table:dc_orga_m, range:[-inf,+inf], keep order:false | proc max:255ms, min:22ms, p80:255ms, p95:255ms, rows:820360, iters:819, tasks:4 | N/A |
| └─TableReader_118 | 772894.27 | root | data:Selection_117 | time:4.824462604s, loops:756, rows:772916 | 54.62267589569092 MB |
| └─Selection_117 | 772894.27 | cop | eq(pressure_test.dc_orga_m._is_del, "0"), ne(pressure_test.dc_orga_m.organ_code, "99999"), not(isnull(pressure_test.dc_orga_m.organ_code)) | proc max:271ms, min:22ms, p80:271ms, p95:271ms, rows:772916, iters:819, tasks:4 | N/A |
| └─TableScan_116 | 820360.00 | cop | table:dc_orga_m, range:[-inf,+inf], keep order:false | proc max:244ms, min:20ms, p80:244ms, p95:244ms, rows:820360, iters:819, tasks:4 | N/A |
+----------------------------------------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+-----------------------+
21 rows in set (29.44 sec)
MySQL [pressure_test]>
【问题描述】：
目前我能发现的问题如下：
1 WHERE 中不应该过滤 NULL, 这里我会改业务数据
2 >= 这种写法 会改为 BETWWEN … AND
3 去掉括号
