你好,我这边将你的统计信息导入之后测试了一下,确实复现了你的问题:
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 索引。