TiDB 4.0-rc 执行计划中 (Build) 与 (Probe) 的 estRows 数值不一样

版本

4.0-rc

问题描述

+---------------------------+---------+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+------+
| id                        | estRows | actRows | task      | access object                                                                                                                                                                   | execution info                                                                  | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | memory        | disk |
+---------------------------+---------+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+------+
| Projection_9              | 0.00    | 5       | root      |                                                                                                                                                                                 | time:391.787286ms, loops:2, Concurrency:OFF                                     | dev.dc_F_D_sale.create_time                                                                                                                                                                                                                                                                                                                                                                                                                                                                | 4.1171875 KB  | N/A  |
| └─Union_10                | 0.00    | 5       | root      |                                                                                                                                                                                 | time:391.779852ms, loops:2                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A           | N/A  |
|   ├─IndexLookUp_25        | 0.00    | 5       | root      |                                                                                                                                                                                 | time:391.740658ms, loops:2, rpc num: 1, rpc time:389.761186ms, proc keys:419916 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | 12.1953125 KB | N/A  |
|   │ ├─Selection_23(Build) | 0.00    | 5       | cop[tikv] |                                                                                                                                                                                 | time:353ms, loops:414                                                           | eq(dev.dc_F_D_sale.br_status, "3"), eq(dev.dc_F_D_sale.is_del, "0"), like(dev.dc_F_D_sale.normal_organ_code, "%DS0046778%", 92), like(dev.dc_F_D_sale.normal_product_code, "%11705%", 92), like(dev.dc_F_D_sale.normal_seller_code, "%DI00018479%", 92), ne(dev.dc_F_D_sale.normal_organ_code, "99999"), ne(dev.dc_F_D_sale.normal_product_code, "99999")... | N/A           | N/A  |
|   │ │ └─IndexRangeScan_21 | 250.00  | 419726  | cop[tikv] | table:DcFDSale, partition:p202002, index:index1(seller_date, is_del, normal_product_code, normal_seller_code, normal_organ_code, br_status, distributor_code) | time:248ms, loops:414                                                           | range:[2020-02-29,2020-03-01], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A           | N/A  |
|   │ └─Selection_24(Probe) | 0.00    | 5       | cop[tikv] |                                                                                                                                                                                 | time:0s, loops:1                                                                | ne(dev.dc_F_D_sale.agent_status, "1"), ne(dev.dc_F_D_sale.data_flag, "1")                                                                                                                                                                                                                                                                                                                                                                                                  | N/A           | N/A  |
|   │   └─TableRowIDScan_22 | 0.00    | 5       | cop[tikv] | table:DcFDSale, partition:p202002                                                                                                                                  | time:0s, loops:1                                                                | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A           | N/A  |
|   └─IndexLookUp_49        | 0.00    | 0       | root      |                                                                                                                                                                                 | time:14.228493ms, loops:1, rpc num: 1, rpc time:12.081936ms, proc keys:7864     |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | 8.15625 KB    | N/A  |
|     ├─Selection_47(Build) | 0.00    | 10      | cop[tikv] |                                                                                                                                                                                 | time:9ms, loops:12                                                              | eq(dev.dc_F_D_sale.br_status, "3"), eq(dev.dc_F_D_sale.is_del, "0"), like(dev.dc_F_D_sale.normal_organ_code, "%DS0046778%", 92), like(dev.dc_F_D_sale.normal_product_code, "%11705%", 92), like(dev.dc_F_D_sale.normal_seller_code, "%DI00018479%", 92), ne(dev.dc_F_D_sale.normal_organ_code, "99999"), ne(dev.dc_F_D_sale.normal_product_code, "99999")... | N/A           | N/A  |
|     │ └─IndexRangeScan_45 | 250.00  | 7724    | cop[tikv] | table:DcFDSale, partition:p202003, index:index1(seller_date, is_del, normal_product_code, normal_seller_code, normal_organ_code, br_status, distributor_code) | time:7ms, loops:12                                                              | range:[2020-02-29,2020-03-01], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A           | N/A  |
|     └─Selection_48(Probe) | 0.00    | 0       | cop[tikv] |                                                                                                                                                                                 | proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:3, tasks:3                        | ne(dev.dc_F_D_sale.agent_status, "1"), ne(dev.dc_F_D_sale.data_flag, "1")                                                                                                                                                                                                                                                                                                                                                                                                  | N/A           | N/A  |
|       └─TableRowIDScan_46 | 0.00    | 10      | cop[tikv] | table:DcFDSale, partition:p202003                                                                                                                                  | proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:3, tasks:3                        | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A           | N/A  |
+---------------------------+---------+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+------+

你好,

可以先了解下 estRows 和 actRows 含义:

estRows:算子预计将会输出的数据条数,基于统计信息以及算子的执行逻辑估算而来。在 4.0 之前叫 count。

actRows:当前算子实际输出的数据条数。

从你的问题来看,优化器中估算的 estRows 和 实际执行统计得到的 actRows 相差很大,建议更新执行计划,或者通过参数调整执行计划收集策略。

https://book.tidb.io/session3/chapter1/sql-execution-plan.html

你好, 我已经执行过统计信息了, 这所以说这个执行计划的问题是,我在实际执行SQL时,也出现了问题

查询所有数据

mysql > SELECT
			 DcfdSale.*
	   FROM
		   dc_f_d_sale DcfdSale
			 LEFT JOIN dc_o_master DcoMaster ON DcfdSale.distributor_code = DcoMaster.organ_code  AND DcOrganizat
	   ionMaster.is_del = '0'
	   WHERE
		   DcfdSale.is_del = '0'
		   AND DcfdSale.br_status = '3'
		   AND DcfdSale.normal_seller_code <> '99999'
		   AND DcfdSale.normal_organ_code <> '99999'
		   AND DcfdSale.normal_product_code <> '99999'
		   AND DcfdSale.data_flag <> '1'
		   AND DcfdSale.agent_status <> '1'
		   AND DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00' AND '2020-03-01 23:59:59'
		   AND DcfdSale.normal_seller_code LIKE '%DI00018479%'
		   AND DcfdSale.normal_organ_code LIKE '%DS0046778%'
		   AND DcfdSale.normal_product_code LIKE '%11705%';
+------------------+------------------+-------------+--------------------+------------------------------------+-------------------+---------------------+---------------------+--------------+--------------------------------+---------------------+-----------------+-----------+-------------------+------------------+--------------------+-----------+--------------+----------------+-----------+------------------------------------------------------------------------------------------------------------------+-------------+--------+--------+-----------------+------------------+---------------------+------------------+---------------------+-----------------+-------------+----------------------------------+-----------+----------------------+--------------+---------------+--------------+--------------+---------------+---------------+------------------+------------+--------+---------------+------------------+------------------+-------------------+
| distributor_code | distributor_name | seller_name | normal_seller_code | organ_name                         | normal_organ_code | relation_organ_code | relation_organ_name | product_code | product_name                   | normal_product_code | normal_quantity | quantity  | seller_mapping_id | organ_mapping_id | product_mapping_id | br_status | organ_enable | product_enable | data_flag | source_file_id                                                                                                   | seller_date | source | amount | is_disable | create_user | create_time    | update_user | update_time    | version_no | is_del | id                          | price     | br_check_description | normal_price | normal_amount | agent_status | belong_month | organ_address | specification | vender_info      | odd_num    | lot_no | validity_date | create_ntid | update_ntid | seller_year_month |
+------------------+------------------+-------------+--------------------+------------------------------------+-------------------+---------------------+---------------------+--------------+--------------------------------+---------------------+-----------------+-----------+-------------------+------------------+--------------------+-----------+--------------+----------------+-----------+------------------------------------------------------------------------------------------------------------------+-------------+--------+--------+-----------------+------------------+---------------------+------------------+---------------------+-----------------+-------------+----------------------------------+-----------+----------------------+--------------+---------------+--------------+--------------+---------------+---------------+------------------+------------+--------+---------------+------------------+------------------+-------------------+
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 3.000000        | 3.000000  | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | 7db2dfb919ba467fac5a460693639538 | 25.010000 | <null>               | 21.000000    | 63.000000     |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7041502502 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 10.000000       | 10.000000 | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | b00634a5067e47dca8ed725dda8e33ee | 25.010000 | <null>               | 21.000000    | 210.000000    |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7041515850 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | -4.000000       | -4.000000 | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | 12e3158f56f14d23b2a7135792ac0113 | 25.010000 | <null>               | 21.000000    | -84.000000    |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7040889785 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 3.000000        | 3.000000  | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | ebc90f649573466da4aeeb5bd70f1d98 | 25.010000 | <null>               | 21.000000    | 63.000000     |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7041512649 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 5.000000        | 5.000000  | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | 0b684d9ff48847f4a149b8676093534e | 25.010000 | <null>               | 21.000000    | 105.000000    |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7041508067 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 4.000000        | 4.000000  | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | 9ce33f6d143141b7a92900dd915d6683 | 25.010000 | <null>               | 21.000000    | 84.000000     |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7041497319 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 5.000000        | 5.000000  | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | ff6840b41790461891b3884112e58570 | 25.010000 | <null>               | 21.000000    | 105.000000    |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7041148821 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 3.000000        | 3.000000  | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | a5a2c0eb8961494abf0b6db4fbd428a3 | 25.010000 | <null>               | 21.000000    | 63.000000     |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7041501016 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 10.000000       | 10.000000 | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | 74535f7f34ec42c9a622c8353e6857eb | 25.010000 | <null>               | 21.000000    | 210.000000    |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7041502935 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 10.000000       | 10.000000 | <null>            | <null>           | <null>             | 3         | <null>       | <null>         |           | 6ca439da1e9bc28891ea7aa2fe94096e4caec26fe08d79881d1c77b500cce858f7e59a5d645f1989d76cbb013b32b9d1f5f788f4d1e0fbb7 | 2020-03-01  | 1      | <null> | 0               | admin            | 2020-04-03 14:06:27 | admin            | 2020-04-03 14:06:27 | <null>          | 0           | d2ecb5f072254d3881a36b2635a9676a | 25.010000 | <null>               | 21.000000    | 210.000000    |              | <null>       | <null>        | 片片片片      | 公司公司公司公司 | 7041495292 | AL5866 | 2021-11-30    | admin            | admin            | 202003            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 5.000000        | <null>    | <null>            | <null>           | <null>             | 3         | <null>       | <null>         | 0         | 6ca439da1e9bc2880f4507d67525ce0cda8501ce1b2b8cc069fecb2c76a10c5cb4b6c3f638b2e09975f84a74fb235b7bca09d9983216a948 | 2020-02-29  | <null> | <null> | 0               | DCS              | 2020-03-02 17:24:00 | DCS              | 2020-03-03 07:48:57 | <null>          | 0           | 3B1AE21D555CEA1194210050568165D0 | <null>    | <null>               | 25.010000    | 103.550000    | 0            | <null>       | <null>        | 片片片片      | <null>           | 7041464574 | AL5866 | 2021-11-30    |                  |                  | 202002            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 10.000000       | <null>    | <null>            | <null>           | <null>             | 3         | <null>       | <null>         | 0         | 6ca439da1e9bc2880f4507d67525ce0cda8501ce1b2b8cc069fecb2c76a10c5cb4b6c3f638b2e09975f84a74fb235b7bca09d9983216a948 | 2020-02-29  | <null> | <null> | 0               | DCS              | 2020-03-02 17:24:00 | DCS              | 2020-03-03 07:48:57 | <null>          | 0           | 3D1AE21D555CEA1194210050568165D0 | <null>    | <null>               | 25.010000    | 207.100000    | 0            | <null>       | <null>        | 片片片片      | <null>           | 7041467507 | AL5866 | 2021-11-30    |                  |                  | 202002            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 4.000000        | <null>    | <null>            | <null>           | <null>             | 3         | <null>       | <null>         | 0         | 6ca439da1e9bc2880f4507d67525ce0cda8501ce1b2b8cc069fecb2c76a10c5cb4b6c3f638b2e09975f84a74fb235b7bca09d9983216a948 | 2020-02-29  | <null> | <null> | 0               | DCS              | 2020-03-02 17:24:00 | DCS              | 2020-03-03 07:48:57 | <null>          | 0           | 401AE21D555CEA1194210050568165D0 | <null>    | <null>               | 25.010000    | 82.840000     | 0            | <null>       | <null>        | 片片片片      | <null>           | 7041468664 | AL5866 | 2021-11-30    |                  |                  | 202002            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 5.000000        | <null>    | <null>            | <null>           | <null>             | 3         | <null>       | <null>         | 0         | 6ca439da1e9bc2880f4507d67525ce0cda8501ce1b2b8cc069fecb2c76a10c5cb4b6c3f638b2e09975f84a74fb235b7bca09d9983216a948 | 2020-02-29  | <null> | <null> | 0               | DCS              | 2020-03-02 17:24:00 | DCS              | 2020-03-03 07:48:57 | <null>          | 0           | 431AE21D555CEA1194210050568165D0 | <null>    | <null>               | 25.010000    | 103.550000    | 0            | <null>       | <null>        | 片片片片      | <null>           | 7041471748 | AL5866 | 2021-11-30    |                  |                  | 202002            |
| DI00018479       | <null>           | <null>      | DI00018479         | 大大大大大大大大大大大大大大大大大 | DS0046778         | <null>              | <null>              | 0009743571   | 立立立立立立立立立立立立立立立 | 11705               | 72.000000       | <null>    | <null>            | <null>           | <null>             | 3         | <null>       | <null>         | 0         | 6ca439da1e9bc2880f4507d67525ce0cda8501ce1b2b8cc069fecb2c76a10c5cb4b6c3f638b2e09975f84a74fb235b7bca09d9983216a948 | 2020-02-29  | <null> | <null> | 0               | DCS              | 2020-03-02 17:24:00 | DCS              | 2020-03-03 07:48:57 | <null>          | 0           | 451AE21D555CEA1194210050568165D0 | <null>    | <null>               | 25.010000    | 1491.120000   | 0            | <null>       | <null>        | 片片片片      | <null>           | 7041486739 | AL5866 | 2021-11-30    |                  |                  | 202002            |
+------------------+------------------+-------------+--------------------+------------------------------------+-------------------+---------------------+---------------------+--------------+--------------------------------+---------------------+-----------------+-----------+-------------------+------------------+--------------------+-----------+--------------+----------------+-----------+------------------------------------------------------------------------------------------------------------------+-------------+--------+--------+-----------------+------------------+---------------------+------------------+---------------------+-----------------+-------------+----------------------------------+-----------+----------------------+--------------+---------------+--------------+--------------+---------------+---------------+------------------+------------+--------+---------------+------------------+------------------+-------------------+

15 rows in set
Time: 0.434s
mysql >

查询单列数据

mysql > SELECT
			 DcfdSale.create_time
	   FROM
		   dc_f_d_sale DcfdSale
			 LEFT JOIN dc_o_master DcoMaster ON DcfdSale.distributor_code = DcoMaster.organ_code  AND DcOrganizat
	   ionMaster.is_del = '0'
	   WHERE
		   DcfdSale.is_del = '0'
		   AND DcfdSale.br_status = '3'
		   AND DcfdSale.normal_seller_code <> '99999'
		   AND DcfdSale.normal_organ_code <> '99999'
		   AND DcfdSale.normal_product_code <> '99999'
		   AND DcfdSale.data_flag <> '1'
		   AND DcfdSale.agent_status <> '1'
		   AND DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00' AND '2020-03-01 23:59:59'
		   AND DcfdSale.normal_seller_code LIKE '%DI00018479%'
		   AND DcfdSale.normal_organ_code LIKE '%DS0046778%'
		   AND DcfdSale.normal_product_code LIKE '%11705%'
+---------------------+
| create_time    |
+---------------------+
| 2020-03-02 17:24:00 |
| 2020-03-02 17:24:00 |
| 2020-03-02 17:24:00 |
| 2020-03-02 17:24:00 |
| 2020-03-02 17:24:00 |
+---------------------+
5 rows in set
Time: 0.415s
mysql >

你好

可否提供原始数据,这边方便复现下~

实在抱歉 这个数据不能够提供,公司有信息安全要求

Hi @mao_siyu,请问集群是从旧版本 TiDB 集群升级到 4.0 RC 的还是一个新的 4.0 RC 集群?这个帖子中有 2 个问题:

  1. estRows 和 actRows 相差太大的问题,体现在对 DcfdSale.seller_date BETWEEN '2020-02-29 00:00:00' AND '2020-03-01 23:59:59' 的估算上,是否能够提供一下 DcFDSale 的表结构以及统计信息?
  2. 查询单列和查询所有列 DcfdSale.* 和查询单列 DcfdSale.create_time 结果不一致的问题,能否提供一下 dc_f_d_saledc_o_master 这两个表的表结构?

查看表结构可以通过 show create table xx 的方式

获取统计信息可以通过 curl -G "http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}" > ${table_name}.stats.json

是一个新的4.0 RC 集群,使用 tiup 安装的


表结构与统计信息.zip (7.8 KB)

文件密码通过微信发给 @rongyilong-PingCAP

好的~