大数据连表查询如何优化?

【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 去掉括号

求老师多指教调优的思路,:pray::pray::pray:

  1. 预期的 SQL 执行时间需要达到多少
  2. 方便的话,麻烦提供一下表结构以及索引信息
  3. 可以导出一份相关表的统计信息,我在我本地环境运行 SQL 并试下改写的效果

导出统计信息方式:

1 期望 5 秒以内

2 文件如下

dc_flowdata_deliver_sale.sql (3.9 KB)

dc_organization_master.sql (2.8 KB)

3 如下

info.zip (1014.8 KB)

正常统计信息应该是 json 格式的,看你上传的 json 文件打开是16进制内容,麻烦重新上传一下

可以尝试一下以下调整:

  1. 添加 dc_f_d_s.br_status , dc_f_d_s._update_time 的联合索引
  2. 适当调大 tidb_hash_join_concurrency 以增加 hash join 的性能。(注意 TiDB 的内存是否足够,过大有可能会引起 OOM )

好的明天我试一试,先谢谢老师,辛苦啦

如果优化有结论,辛苦反馈优化后的结果。另外如果有新的问题,麻烦创建先的问题帖子。