order by 影响执行计划较大

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:
    v4.0.7
  • 【问题描述】:
    表数据量 1亿3千万+

其中 device_code 字段有索引,主键 id 字段成增加趋势(非自增,但可认为有序,且与时间相关)

EXPLAIN ANALYZE
SELECT
    id,
    device_code
    FROM
    iot_xxxxx
    WHERE
    device_code =  'AABBCCDD'
    ORDER BY id DESC
  LIMIT 10;

结果

从执行计划看起来跳过 where 条件通过 id 做了全表扫描。

通过 hint 强制不使用主键索引,结果符合预期

EXPLAIN ANALYZE
SELECT
    /*+ IGNORE_INDEX(iot_xxxxx, primary) */
    id,
    device_code
    FROM
    iot_xxxxx
    WHERE
    device_code =  'AABBCCDD'
    ORDER BY id DESC
  LIMIT 10;

SHOW STATS_HEALTHY; 显示该表健康度99

1 个赞

你好,从两次的执行计划结果来看,每次的估算值和实际值差别都很大:
1)第一次走 primary key 时,执行计划中estRows 仅 2560,但 actRows 却有 1.3 亿+
2)第二次加了 hint 走 device_code 上索引时,执行计划中 estRows 有 5.2w+,但 actRows 仅 47
所以应该是优化器错误估算了成本导致选择了走 primary key。虽然 SHOW STATS_HEALTHY 显示该表健康度 99 ,但该结果只能粗略反应表上统计信息的准确度,怀疑索引列上的统计信息还是不准确,建议重新收集下索引列统计信息,再看下执行效果。

1 个赞

@elvizlai 请使用 show stats_histograms where table_name = xxxx 看一下这个表相关的统计信息。

顺便可以用 https://docs.pingcap.com/zh/tidb/stable/statistics#导出统计信息 这个命令导出表相关的统计信息,以及给一下这个表的表结构。方便我们后续查问题。

1 个赞

DDL

CREATE TABLE `iot_xxxx` (
  `id` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `device_code` varchar(20) COLLATE utf8_general_ci DEFAULT NULL,
  `device_raw_no` varchar(50) COLLATE utf8_general_ci DEFAULT NULL,
  `raw_data_source` varchar(5) COLLATE utf8_general_ci DEFAULT NULL,
  `raw_data` text COLLATE utf8_general_ci DEFAULT NULL,
  `raw_data_content` text COLLATE utf8_general_ci DEFAULT NULL,
  `create_code` varchar(20) COLLATE utf8_general_ci DEFAULT NULL,
  `create_name` varchar(50) COLLATE utf8_general_ci DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_device_code` (`device_code`),
  KEY `index_device_raw_no` (`device_raw_no`),
  KEY `idx_create_date` (`create_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

dump
xxx.json (2.1 MB)

1 个赞

你好,附件的 json 文件里的表名和上述 SQL 中的表名并不相同,请问是不是导错统计信息了。

1 个赞

没错,我手动把导出文件的数据库名跟表名修改了。

1 个赞

你好,我这边将你的统计信息导入之后测试了一下,确实复现了你的问题:

MySQL [xxxx_hidden]>  show stats_histograms where table_name='iot_xxxx_hidden';
+-------------+-----------------+----------------+---------------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name     | Table_name      | Partition_name | Column_name         | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation |
+-------------+-----------------+----------------+---------------------+----------+---------------------+----------------+------------+--------------+-------------+
| xxxx_hidden | iot_xxxx_hidden |                | create_code         |        0 | 2020-10-13 17:00:57 |              0 |  138947078 |            0 |           0 |
| xxxx_hidden | iot_xxxx_hidden |                | create_name         |        0 | 2020-10-13 17:00:57 |              0 |  138947078 |            0 |           0 |
| xxxx_hidden | iot_xxxx_hidden |                | device_code         |        0 | 2020-10-13 17:00:56 |            244 |    3749628 |        14.56 |    0.138517 |
| xxxx_hidden | iot_xxxx_hidden |                | idx_create_date     |        1 | 2020-10-13 17:00:58 |        5031368 |          0 |            0 |           0 |
| xxxx_hidden | iot_xxxx_hidden |                | PRIMARY             |        1 | 2020-10-13 17:01:01 |      138950159 |          0 |            0 |           0 |
| xxxx_hidden | iot_xxxx_hidden |                | index_device_code   |        1 | 2020-10-13 17:00:59 |            244 |    3749778 |            0 |           0 |
| xxxx_hidden | iot_xxxx_hidden |                | index_device_raw_no |        1 | 2020-10-13 17:01:00 |            400 |          0 |            0 |           0 |
+-------------+-----------------+----------------+---------------------+----------+---------------------+----------------+------------+--------------+-------------+
7 rows in set (0.00 sec)
MySQL [xxxx_hidden]> EXPLAIN ANALYZE
    -> SELECT
    ->     id,
    ->     device_code
    ->     FROM
    ->     iot_xxxx_hidden
    ->     WHERE
    ->     device_code =  'AABBCCDD'
    ->     ORDER BY id DESC
    ->   LIMIT 10;
+-----------------------------------+---------+---------+-----------+------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+---------------+------+
| id                                | estRows | actRows | task      | access object                            | execution info                                                                                                                           | operator info                                                           | memory        | disk |
+-----------------------------------+---------+---------+-----------+------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+---------------+------+
| Limit_12                          | 10.00   | 0       | root      |                                          | time:3.968323ms, loops:1                                                                                                                 | offset:0, count:10                                                      | N/A           | N/A  |
| └─Projection_32                   | 10.00   | 0       | root      |                                          | time:3.966001ms, loops:1, Concurrency:OFF                                                                                                | xxxx_hidden.iot_xxxx_hidden.id, xxxx_hidden.iot_xxxx_hidden.device_code | 1.33203125 KB | N/A  |
|   └─IndexLookUp_31                | 10.00   | 0       | root      |                                          | time:3.963528ms, loops:1, cop_task: {num: 1, max:3.724741ms, proc_keys: 0, rpc_num: 1, rpc_time: 3.677773ms, copr_cache_hit_ratio: 0.00} |                                                                         | 176 Bytes     | N/A  |
|     ├─IndexFullScan_28(Build)     | 2440.00 | 0       | cop[tikv] | table:iot_xxxx_hidden, index:PRIMARY(id) | time:1ms, loops:1                                                                                                                        | keep order:true, desc                                                   | N/A           | N/A  |
|     └─Selection_30(Probe)         | 10.00   | 0       | cop[tikv] |                                          | time:0ns, loops:0                                                                                                                        | eq(xxxx_hidden.iot_xxxx_hidden.device_code, "AABBCCDD")                 | N/A           | N/A  |
|       └─TableRowIDScan_29         | 2440.00 | 0       | cop[tikv] | table:iot_xxxx_hidden                    | time:0ns, loops:0                                                                                                                        | keep order:false                                                        | N/A           | N/A  |
+-----------------------------------+---------+---------+-----------+------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+---------------+------+
6 rows in set (0.00 sec)
MySQL [xxxx_hidden]> EXPLAIN ANALYZE
    -> SELECT
    ->     /*+ IGNORE_INDEX(iot_xxxx_hidden, primary) */
    ->     id,
    ->     device_code
    ->     FROM
    ->     iot_xxxx_hidden use index(index_device_code)
    ->     WHERE
    ->     device_code =  'AABBCCDD'
    ->     ORDER BY id DESC
    ->   LIMIT 10;   
+----------------------------------+-----------+---------+-----------+-------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+-----------+------+
| id                               | estRows   | actRows | task      | access object                                               | execution info                                                                                                                           | operator info                                           | memory    | disk |
+----------------------------------+-----------+---------+-----------+-------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+-----------+------+
| TopN_9                           | 10.00     | 0       | root      |                                                             | time:3.681191ms, loops:1                                                                                                                 | xxxx_hidden.iot_xxxx_hidden.id:desc, offset:0, count:10 | 0 Bytes   | N/A  |
| └─IndexLookUp_17                 | 10.00     | 0       | root      |                                                             | time:3.640389ms, loops:2, cop_task: {num: 1, max:3.439511ms, proc_keys: 0, rpc_num: 1, rpc_time: 3.426396ms, copr_cache_hit_ratio: 0.00} |                                                         | 204 Bytes | N/A  |
|   ├─IndexRangeScan_14(Build)     | 584763.72 | 0       | cop[tikv] | table:iot_xxxx_hidden, index:index_device_code(device_code) | time:0s, loops:1                                                                                                                         | range:["AABBCCDD","AABBCCDD"], keep order:false         | N/A       | N/A  |
|   └─TopN_16(Probe)               | 10.00     | 0       | cop[tikv] |                                                             | time:0ns, loops:0                                                                                                                        | xxxx_hidden.iot_xxxx_hidden.id:desc, offset:0, count:10 | N/A       | N/A  |
|     └─TableRowIDScan_15          | 584763.72 | 0       | cop[tikv] | table:iot_xxxx_hidden                                       | time:0ns, loops:0                                                                                                                        | keep order:false                                        | N/A       | N/A  |
+----------------------------------+-----------+---------+-----------+-------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+-----------+------+
5 rows in set (0.00 sec)

分别走主键索引和字段 device_code 索引时,estRows 值估算前者明显小于后者,优化器依然认为走主键成本更低;在重新收集表统计信息后,发现优化器可以正确选到 device_code 索引。

1 个赞

这个指的是 analyze table iot_xxxx_hidden; 吗?我这边执行过,不起作用。

1 个赞

麻烦再反馈下现在这张表上 show stats_histograms 的结果

1 个赞

执行分析

xxx_db_hidden> analyze table iot_xxxx_hidden;
Query OK, 0 rows affected
Time: 180.408s

查询测试

+-------------------------------+---------+-----------+-----------+-----------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+------+
| id                            | estRows | actRows   | task      | access object                                             | execution info                                                                                                                                                                                                                                                                | operator info                                                                                                                                                                                                                                                                                                                                                                           | memory               | disk |
+-------------------------------+---------+-----------+-----------+-----------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+------+
| Limit_12                      | 10.00   | 10        | root      |                                                           | time:1m1.637779558s, loops:2                                                                                                                                                                                                                                                  | offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                      | N/A                  | N/A  |
| └─Projection_32               | 10.00   | 10        | root      |                                                           | time:1m1.637777416s, loops:1, Concurrency:OFF                                                                                                                                                                                                                                 | x_db_hidden.xx_table_hidden.id, x_db_hidden.xx_table_hidden.device_code, x_db_hidden.xx_table_hidden.device_raw_no, x_db_hidden.xx_table_hidden.raw_data_source, x_db_hidden.xx_table_hidden.raw_data, x_db_hidden.xx_table_hidden.raw_data_content, x_db_hidden.xx_table_hidden.create_code, x_db_hidden.xx_table_hidden.create_name, x_db_hidden.xx_table_hidden.create_date | 8.1015625 KB         | N/A  |
|   └─IndexLookUp_31            | 10.00   | 10        | root      |                                                           | time:1m1.637772021s, loops:1, cop_task: {num: 149, max: 1.045883573s, min: 553.963µs, avg: 267.478923ms, p95: 796.64212ms, max_proc_keys: 970261, p95_proc_keys: 960000, tot_proc: 37.67s, tot_wait: 415ms, rpc_num: 149, rpc_time: 39.85337689s, copr_cache_hit_ratio: 0.56} |                                                                                                                                                                                                                                                                                                                                                                                         | 223.7852659225464 MB | N/A  |
|     ├─IndexFullScan_28(Build) | 2572.81 | 143014477 | cop[tikv] | table:xx_table_hidden, index:idx_create_date(create_date) | proc max:1.054s, min:426ms, p80:751ms, p95:879ms, iters:140334, tasks:149                                                                                                                                                                                                     | keep order:true, desc                                                                                                                                                                                                                                                                                                                                                                   | N/A                  | N/A  |
|     └─Selection_30(Probe)     | 10.00   | 0         | cop[tikv] |                                                           | time:0ns, loops:0                                                                                                                                                                                                                                                             | eq(x_db_hidden.xx_table_hidden.device_code, "IOT2007170002")                                                                                                                                                                                                                                                                                                                           | N/A                  | N/A  |
|       └─TableRowIDScan_29     | 2572.81 | 0         | cop[tikv] | table:xx_table_hidden                                     | time:0ns, loops:0                                                                                                                                                                                                                                                             | keep order:false                                                                                                                                                                                                                                                                                                                                                                        | N/A                  | N/A  |
+-------------------------------+---------+-----------+-----------+-----------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+------+

show stats_histograms

+--------------+-----------------+----------------+---------------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name      | Table_name      | Partition_name | Column_name         | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation |
+--------------+-----------------+----------------+---------------------+----------+---------------------+----------------+------------+--------------+-------------+
| xxx_db_hidden | xxx_table_hidden |                | device_raw_no       | 0        | 2020-10-14 09:44:09 | 404            | 0          | 10.97        | 0.049449    |
| xxx_db_hidden | xxx_table_hidden |                | create_name         | 0        | 2020-10-14 09:44:11 | 0              | 145999793  |  0.0         | 0.0         |
| xxx_db_hidden | xxx_table_hidden |                | create_code         | 0        | 2020-10-14 09:44:11 | 0              | 145999793  |  0.0         | 0.0         |
| xxx_db_hidden | xxx_table_hidden |                | create_date         | 0        | 2020-10-14 09:44:11 | 5269504        | 0          |  8.0         | 0.99999     |
| xxx_db_hidden | xxx_table_hidden |                | device_code         | 0        | 2020-10-14 09:44:08 | 245            | 4150964    | 14.57        | 0.122135    |
| xxx_db_hidden | xxx_table_hidden |                | id                  | 0        | 2020-10-14 09:44:07 | 145999793      | 0          | 20.0         | 0.999993    |
| xxx_db_hidden | xxx_table_hidden |                | PRIMARY             | 1        | 2020-10-14 09:44:46 | 146001830      | 0          |  0.0         | 0.0         |
| xxx_db_hidden | xxx_table_hidden |                | index_device_code   | 1        | 2020-10-14 09:44:45 | 245            | 4151063    |  0.0         | 0.0         |
| xxx_db_hidden | xxx_table_hidden |                | index_device_raw_no | 1        | 2020-10-14 09:44:55 | 404            | 0          |  0.0         | 0.0         |
| xxx_db_hidden | xxx_table_hidden |                | idx_create_date     | 1        | 2020-10-14 09:45:51 | 5177576        | 0          |  0.0         | 0.0         |
+--------------+-----------------+----------------+---------------------+----------+---------------------+----------------+------------+--------------+-------------+
1 个赞

上面的执行计划中怎么会走到 create_date 字段上索引,SQL 文本和之前不一样吧?

1 个赞

不好意思。上述测试查询执行的时候用了另外的排序条件。

我们最初排查这个问题的时候,用的 order by id,后面换了 order by create_date 字段做尝试,结果并无改善。

现附上基于 id 做排序的测试查询参考:

+-------------------------------+---------+-----------+-----------+------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+------+
| id                            | estRows | actRows   | task      | access object                            | execution info                                                                                                                                                                                                                                                                            | operator info                                                                                                                                                                                                                                                                                                                                                                           | memory                | disk |
+-------------------------------+---------+-----------+-----------+------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+------+
| Limit_12                      | 10.00   | 10        | root      |                                          | time:1m17.961477442s, loops:2                                                                                                                                                                                                                                                             | offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                      | N/A                   | N/A  |
| └─Projection_32               | 10.00   | 10        | root      |                                          | time:1m17.961475499s, loops:1, Concurrency:OFF                                                                                                                                                                                                                                            | x_db_hidden.xx_table_hidden.id, x_db_hidden.xx_table_hidden.device_code, x_db_hidden.xx_table_hidden.device_raw_no, x_db_hidden.xx_table_hidden.raw_data_source, x_db_hidden.xx_table_hidden.raw_data, x_db_hidden.xx_table_hidden.raw_data_content, x_db_hidden.xx_table_hidden.create_code, x_db_hidden.xx_table_hidden.create_name, x_db_hidden.xx_table_hidden.create_date | 8.1015625 KB          | N/A  |
|   └─IndexLookUp_31            | 10.00   | 10        | root      |                                          | time:1m17.961468701s, loops:1, cop_task: {num: 149, max: 1.537453865s, min: 476.485197ms, avg: 768.005788ms, p95: 1.270541039s, max_proc_keys: 1101408, p95_proc_keys: 960000, tot_proc: 1m49.364s, tot_wait: 583ms, rpc_num: 149, rpc_time: 1m54.431846721s, copr_cache_hit_ratio: 0.00} |                                                                                                                                                                                                                                                                                                                                                                                         | 223.23488426208496 MB | N/A  |
|     ├─IndexFullScan_28(Build) | 2572.81 | 143181408 | cop[tikv] | table:xx_table_hidden, index:PRIMARY(id) | proc max:1.38s, min:436ms, p80:1.065s, p95:1.157s, iters:140496, tasks:149                                                                                                                                                                                                                | keep order:true, desc                                                                                                                                                                                                                                                                                                                                                                   | N/A                   | N/A  |
|     └─Selection_30(Probe)     | 10.00   | 0         | cop[tikv] |                                          | time:0ns, loops:0                                                                                                                                                                                                                                                                         | eq(x_db_hidden.xx_table_hidden.device_code, "IOT2007170002")                                                                                                                                                                                                                                                                                                                           | N/A                   | N/A  |
|       └─TableRowIDScan_29     | 2572.81 | 0         | cop[tikv] | table:xx_table_hidden                    | time:0ns, loops:0                                                                                                                                                                                                                                                                         | keep order:false                                                                                                                                                                                                                                                                                                                                                                        | N/A                   | N/A  |
+-------------------------------+---------+-----------+-----------+------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+------+
1 个赞

@elvizlai
请问 tidb_opt_correlation_exp_factortidb_opt_correlation_threshold 两个值分别是多少?

1 个赞

没有做过这方面参数的调整

mysql root@host:(none)> select @@tidb_opt_correlation_exp_factor;
+-----------------------------------+
| @@tidb_opt_correlation_exp_factor |
+-----------------------------------+
| 1                                 |
+-----------------------------------+
1 row in set
Time: 0.031s
mysql root@host:(none)> select @@tidb_opt_correlation_threshold;
+----------------------------------+
| @@tidb_opt_correlation_threshold |
+----------------------------------+
| 0.9                              |
+----------------------------------+
1 row in set
Time: 0.026s

在未来的 5.0 中,可以使用 未来会上线的功能文档草稿 中的 CREATE STATISTICS corr_stats_1(correlation) ON iot_xxxx_hidden(id, device_code); 语法创建额外的统计信息,这样优化器就可以自动选择正确了。

目前可以使用 https://docs.pingcap.com/zh/tidb/stable/sql-plan-management#执行计划绑定-sql-binding 来手动进行规避。

如果这个表是在5.0中全新创建的,可能也并不需要手动注册 extended statistics,我们需要再确认一下。

1 个赞

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。