SQL优化使用 ROW_NUMBER( ) OVER( ORDER BY id ) AS row_num，循环查询慢

mao_siyu · 2020 年1 月 15 日 07:02

【TiDB 版本】：

3.0.5

慢查询SQL

慢SQL 执行计划

mysql> EXPLAIN ANALYZE SELECT
    -> *
    -> FROM
    -> (
    -> SELECT
    -> code1,
    -> code2,
    -> normal_1,
    -> seller_1,
    -> ROW_NUMBER ( ) OVER ( ORDER BY id ) AS row_num
    -> FROM
    -> dc_f_d_s
    -> WHERE
    -> is_Del = '0'
    -> ) t
    -> WHERE
    -> t.row_num > 1570000
    -> LIMIT 10000;
+--------------------------------+------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------------------+
| id                             | count      | task | operator info                                                                                                                                                                                              | execution info                                                                     | memory               |
+--------------------------------+------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------------------+
| Projection_13                  | 10000.00   | root | pressure_pfizer.dc_f_d_s.code1, pressure_pfizer.dc_f_d_s.code2, pressure_pfizer.dc_f_d_s.normal_1, pressure_pfizer.dc_f_d_s.seller_1, row_num                                                              | time:5.676301489s, loops:11, rows:10000                                            | N/A                  |
| └─Limit_16                     | 10000.00   | root | offset:0, count:10000                                                                                                                                                                                      | time:5.675881734s, loops:11, rows:10000                                            | N/A                  |
|   └─Selection_17               | 10000.00   | root | gt(row_num, 1570000)                                                                                                                                                                                       | time:5.675858976s, loops:10, rows:10000                                            | N/A                  |
|     └─Window_18                | 10000.00   | root | row_number() over(order by pressure_pfizer.dc_f_d_s.id asc)                                                                                                                                                | time:5.58979399s, loops:1543, rows:1580032                                         | N/A                  |
|       └─Projection_23          | 1740137.10 | root | pressure_pfizer.dc_f_d_s.code1, pressure_pfizer.dc_f_d_s.code2, pressure_pfizer.dc_f_d_s.normal_1, pressure_pfizer.dc_f_d_s.seller_1, pressure_pfizer.dc_f_d_s.is_Del, pressure_pfizer.dc_f_d_s.id         | time:5.351048962s, loops:1666, rows:1704223                                        | N/A                  |
|         └─IndexLookUp_22       | 1740137.10 | root |                                                                                                                                                                                                            | time:5.472002572s, loops:1666, rows:1704223                                        | 34.88465213775635 MB |
|           ├─IndexScan_19       | 2526725.00 | cop  | table:dc_f_d_s, index:id, range:[NULL,+inf], keep order:true                                                                                                                                               | proc max:1.96s, min:254ms, p80:1.96s, p95:1.96s, rows:2526563, iters:2481, tasks:3 | N/A                  |
|           └─Selection_21       | 1740137.10 | cop  | eq(pressure_pfizer.dc_f_d_s.is_Del, "0")                                                                                                                                                                   | proc max:127ms, min:0s, p80:46ms, p95:65ms, rows:1704223, iters:10846, tasks:1880  | N/A                  |
|             └─TableScan_20     | 2526725.00 | cop  | table:dc_f_d_s, keep order:false                                                                                                                                                                           | proc max:125ms, min:0s, p80:45ms, p95:65ms, rows:2526563, iters:10846, tasks:1880  | N/A                  |
+--------------------------------+------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------------------+
9 rows in set (5.68 sec)

mysql>

【问题描述】：

单独执行SQL 时 SQL执行时间在 5秒左右，使用内存 33MB左右，但是批量执行时，消耗的内存与消耗的时间都会变的很大，不知道为什么会这样

GangShen · 2020 年1 月 15 日 07:18

批量查询是同时进行查询吗，还是循环执行？

t.row_num 这个条件会主键变大吗？

如果方便的话，提供一下慢日志

mao_siyu · 2020 年1 月 15 日 07:36

slow.log (1.0 MB)

GangShen · 2020 年1 月 15 日 07:56

这个提供的是 tidb 的日志，不是慢日志

mao_siyu · 2020 年1 月 15 日 08:09

抱歉刚刚上传错了

tidb_slow_query.log (4.6 MB)

GangShen · 2020 年1 月 15 日 08:27

用 pt 工具解析慢日志文件，看到 select row_number() 这个语句排名是第10个

怀疑是其他的 sql 影响的，看相同sql 内存使用差异不大

mao_siyu · 2020 年1 月 15 日 08:28

谢谢老师，这个工具能发一下吗？

GangShen · 2020 年1 月 15 日 08:30

这是 Percona 出的 pt-query-digest 工具，用于分析 MySQL 慢日志的，可以搜索下载一下

目前 TiDB 的慢日志格式是兼容 pt-query-digest 工具的

mao_siyu · 2020 年1 月 15 日 08:30

好的非常感谢老师

GangShen · 2020 年1 月 15 日 12:00

mao_siyu · 2020 年1 月 16 日 11:03

老师，因为业务的原因，这个表的数据，虽着时间增长，会越来越大，性能会越来越差，有什么调优方案吗？

对应的慢SQL

GangShen · 2020 年1 月 16 日 11:16

麻烦提供一下sql 语句、explain analyze 执行计划的结果、表结构

mao_siyu · 2020 年1 月 16 日 11:22

EXPLAIN ANALYZE
    -> SELECT
    ->     *
    -> FROM
    ->     (
    ->     SELECT
    ->         dist_code,
    ->         no_han_code,
    ->         no_prod_code,
    ->         no_quantity,
    ->         se_date,
    ->         ROW_NUMBER ( ) OVER ( ORDER BY id ) AS row_num
    ->     FROM
    ->         dc_f_d_s
    ->     WHERE
    ->         Is_Del = '0'
    ->     ) t
    -> WHERE
    ->     t.row_num > 1980000
    ->     LIMIT 10000;
+--------------------------------+------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+-----------------------+
| id                             | count      | task | operator info                                                                                                                                                                                                                                                                                                                                                                                               | execution info                                                                        | memory                |
+--------------------------------+------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+-----------------------+
| Projection_13                  | 10000.00   | root | pressure_p.dc_f_d_s.dist_code, pressure_p.dc_f_d_s.no_han_code, pressure_p.dc_f_d_s.no_prod_code, pressure_p.dc_f_d_s.no_quantity, pressure_p.dc_f_d_s.se_date, row_num                                                                                                | time:9.080916252s, loops:11, rows:10000                                               | N/A                   |
| └─Limit_16                     | 10000.00   | root | offset:0, count:10000                                                                                                                                                                                                                                                                                                                                                                                       | time:9.080273652s, loops:11, rows:10000                                               | N/A                   |
|   └─Selection_17               | 10000.00   | root | gt(row_num, 1980000)                                                                                                                                                                                                                                                                                                                                                                                        | time:9.080256719s, loops:10, rows:10000                                               | N/A                   |
|     └─Window_18                | 10000.00   | root | row_number() over(order by pressure_p.dc_f_d_s.id asc)                                                                                                                                                                                                                                                                                                                            | time:8.970318183s, loops:1944, rows:1990656                                           | N/A                   |
|       └─Projection_23          | 2638344.01 | root | pressure_p.dc_f_d_s.dist_code, pressure_p.dc_f_d_s.no_han_code, pressure_p.dc_f_d_s.no_prod_code, pressure_p.dc_f_d_s.no_quantity, pressure_p.dc_f_d_s.se_date, pressure_p.dc_f_d_s.is_del, pressure_p.dc_f_d_s.id | time:8.223819354s, loops:2625, rows:2686602                                           | N/A                   |
|         └─IndexLookUp_22       | 2638344.01 | root |                                                                                                                                                                                                                                                                                                                                                                                                             | time:8.548054674s, loops:2625, rows:2686602                                           | 100.61843204498291 MB |
|           ├─IndexScan_19       | 3511297.00 | cop  | table:dc_f_d_s, index:id, range:[NULL,+inf], keep order:true                                                                                                                                                                                                                                                                                                                           | proc max:1.126s, min:170ms, p80:1.126s, p95:1.126s, rows:3509960, iters:3445, tasks:4 | N/A                   |
|           └─Selection_21       | 2638344.01 | cop  | eq(pressure_p.dc_f_d_s.is_del, "0")                                                                                                                                                                                                                                                                                                                                               | proc max:313ms, min:0s, p80:28ms, p95:39ms, rows:2686602, iters:17847, tasks:3439     | N/A                   |
|             └─TableScan_20     | 3511297.00 | cop  | table:dc_f_d_s, keep order:false                                                                                                                                                                                                                                                                                                                                                            | proc max:312ms, min:0s, p80:28ms, p95:38ms, rows:3509960, iters:17847, tasks:3439     | N/A                   |
+--------------------------------+------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+-----------------------+
9 rows in set (9.08 sec)

表结构

CREATE TABLE `dc_f_d_s` (
  `dist_code` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `dist_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `se_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `no_se_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `han_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `no_han_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `re_han_code` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `re_han_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `prod_code` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `prod_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `no_prod_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `no_quantity` decimal(20,6) DEFAULT NULL COMMENT '',
  `quantity` decimal(20,6) DEFAULT NULL COMMENT '',
  `se_mapping_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `han_mapping_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `prod_mapping_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `br_status` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `han_enable` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `prod_enable` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `data_flag` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `source_file_id` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `se_date` date DEFAULT NULL COMMENT '',
  `source` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `amount` decimal(64,6) DEFAULT NULL COMMENT '',
  `is_disable` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '',
  `create_user` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `create_time` datetime DEFAULT NULL COMMENT '',
  `update_user` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `update_time` datetime DEFAULT NULL COMMENT '',
  `version_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `is_del` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '',
  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `price` decimal(64,6) DEFAULT NULL COMMENT '',
  `br_check_description` varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `no_price` decimal(20,6) DEFAULT NULL COMMENT '',
  `no_amount` decimal(20,6) DEFAULT NULL COMMENT '',
  `agent_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `belong_month` date DEFAULT NULL COMMENT '',
  `han_address` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `specification` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `vender_info` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `odd_num` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `lot_no` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `validity_date` date DEFAULT NULL COMMENT '',
  PRIMARY KEY (`id`),
  KEY `no_se_code_fk_index` (`no_se_code`),
  KEY `no_han_code_fk_index` (`no_han_code`),
  KEY `no_prod_code_fk_index` (`no_prod_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='';

GangShen · 2020 年1 月 16 日 13:02

从执行计划看，主要是在子查询比较耗时，is_del=‘0’ 这个条件过滤性太低，导致删选之后的数据量还是很大，可以考虑从业务角度增加 where 筛选条件对数据进行进一步过滤

mao_siyu · 2020 年3 月 19 日 07:24

老师我们的业务现在已经定下来了，只能是这样，还有其它的优化方法吗？

不懂就问 · 2020 年3 月 19 日 08:51

随着数据的写入，前面的数据需要一直保留吗？业务的查询逻辑需要访问最开始的数据吗？

mao_siyu · 2020 年3 月 19 日 08:54

是的老师，这个表中存放的是销售的数据要一直保留的，而且经常做全表查询，我们这个业务已经商讨过了，是不能在做更改的

不懂就问 · 2020 年3 月 19 日 09:12

暂时没有合适的方案。

mao_siyu · 2020 年3 月 19 日 09:13

那这些大表，我想分页查询怎么办啊，分页还得必须要有相应的过滤条件？

不懂就问 · 2020 年3 月 19 日 09:58

分页查询这块性能不太好，一般都需要配合业务做一些调整。看咱们这边能不能微调下业务侧的逻辑来支持更好的查询性能。

SQL优化 使用 ROW_NUMBER( ) OVER( ORDER BY id ) AS row_num， 循环查询慢