为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
- 【TiDB 版本】：4.0.2
- 【问题描述】：联表查询与mysql相比差距太远，tidb执行需要200ms，mysql仅需30ms，数据量一致
SELECT
si.sign_id AS signId,
si.course_id AS courseId,
si.user_id AS userId,
si.username AS username,
si.status AS STATUS,
si.create_time AS createTime,
si.update_time AS updateTime,
si.has_create_finance AS hasCreateFinance,
si.deleted AS deleted,
si.order_id AS orderId,
si.vip_id AS vipId,
si.goods_id AS goodsId,
si.user_account_id AS userAccountId
FROM
sign_info si
LEFT JOIN customer_order co ON co.order_id = si.order_id
WHERE
co.deleted = 0
AND co.status= 2
AND si.status= 2
AND si.deleted = TRUE
AND co.phone_or_pc != 3
ORDER BY
si.update_time DESC
LIMIT
5;
tidb执行计划：
|Projection_9|5.00|5|root||time:180.689781ms, loops:2, Concurrency:OFF|ykt_db.sign_info.sign_id, ykt_db.sign_info.course_id, ykt_db.sign_info.user_id, ykt_db.sign_info.username, ykt_db.sign_info.status, ykt_db.sign_info.create_time, ykt_db.sign_info.update_time, ykt_db.sign_info.has_create_finance, ykt_db.sign_info.deleted, ykt_db.sign_info.order_id, ykt_db.sign_info.vip_id, ykt_db.sign_info.goods_id, ykt_db.sign_info.user_account_id|1.9970703125 KB|N/A|
|---|---|---|---|---|---|---|---|---|
|└─Limit_15|5.00|5|root||time:180.68214ms, loops:2|offset:0, count:5|N/A|N/A|
| └─IndexJoin_81|5.00|5|root||time:180.679925ms, loops:1, Concurrency:4|inner join, inner:IndexLookUp_80, outer key:ykt_db.sign_info.order_id, inner key:ykt_db.customer_order.order_id|267.8046875 KB|N/A|
| ├─Projection_94(Build)|5.00|271|root||time:182.494508ms, loops:5, Concurrency:OFF|ykt_db.sign_info.sign_id, ykt_db.sign_info.course_id, ykt_db.sign_info.user_id, ykt_db.sign_info.username, ykt_db.sign_info.status, ykt_db.sign_info.create_time, ykt_db.sign_info.update_time, ykt_db.sign_info.order_id, ykt_db.sign_info.vip_id, ykt_db.sign_info.has_create_finance, ykt_db.sign_info.deleted, ykt_db.sign_info.goods_id, ykt_db.sign_info.user_account_id|9.921875 KB|N/A|
| │ └─IndexLookUp_93|5.00|271|root||time:182.471801ms, loops:5, rpc num: 1, rpc time:176.22403ms, proc keys:271823||2.4293956756591797 MB|N/A|
| │ ├─IndexRangeScan_90(Build)|827.78|271823|cop[tikv]|table:si, index:idx_sign_info_status_deleted_update_time(status, deleted, update_time)|time:159ms, loops:270|range:[2 1,2 1], keep order:true, desc|N/A|N/A|
| │ └─Selection_92(Probe)|5.00|271|cop[tikv]||proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:21, tasks:5|not(isnull(ykt_db.sign_info.order_id))|N/A|N/A|
| │ └─TableRowIDScan_91|827.78|2016|cop[tikv]|table:si|proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:21, tasks:5|keep order:false|N/A|N/A|
| └─IndexLookUp_80(Probe)|1.00|33|root||time:4.822801ms, loops:3, rpc num: 2, rpc max:1.821645ms, min:1.162642ms, avg:1.492143ms, p80:1.821645ms, p95:1.821645ms, proc keys max:128, p95:128||11.46875 KB|N/A|
| ├─IndexRangeScan_77(Build)|1.00|192|cop[tikv]|table:co, index:PRIMARY(order_id)|proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:5, tasks:2|range: decided by [eq(ykt_db.customer_order.order_id, ykt_db.sign_info.order_id)], keep order:false|N/A|N/A|
| └─Selection_79(Probe)|1.00|33|cop[tikv]||time:0s, loops:2|eq(ykt_db.customer_order.deleted, 0), eq(ykt_db.customer_order.status, 2), ne(ykt_db.customer_order.phone_or_pc, 3)|N/A|N/A|
| └─TableRowIDScan_78|1.00|64|cop[tikv]|table:co|time:0s, loops:2|keep order:false|N/A|N/A|
mysql执行计划：
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "140402.34"
},
"ordering_operation": {
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "si",
"access_type": "ref",
"possible_keys": [
"idx_sign_info_order_id",
"idx_sign_info_status_deleted_update_time"
],
"key": "idx_sign_info_status_deleted_update_time",
"used_key_parts": [
"status",
"deleted"
],
"key_length": "4",
"ref": [
"const",
"const"
],
"rows_examined_per_scan": 142160,
"rows_produced_per_join": 142160,
"filtered": "100.00",
"cost_info": {
"read_cost": "4140.00",
"eval_cost": "28432.00",
"prefix_cost": "32572.00",
"data_read_per_join": "119M"
},
"used_columns": [
"sign_id",
"course_id",
"user_id",
"username",
"status",
"create_time",
"update_time",
"order_id",
"vip_id",
"has_create_finance",
"deleted",
"goods_id",
"user_account_id"
],
"attached_condition": "((`ykt_db`.`si`.`status` <=> 2) and (`ykt_db`.`si`.`deleted` <=> TRUE))"
}
},
{
"table": {
"table_name": "co",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"idx_customer_order_deleted_status_phone_or_pc"
],
"key": "PRIMARY",
"used_key_parts": [
"order_id"
],
"key_length": "8",
"ref": [
"ykt_db.si.order_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 71691,
"filtered": "50.43",
"cost_info": {
"read_cost": "142160.00",
"eval_cost": "14338.26",
"prefix_cost": "203164.00",
"data_read_per_join": "1G"
},
"used_columns": [
"order_id",
"status",
"deleted",
"phone_or_pc"
],
"attached_condition": "((`ykt_db`.`co`.`status` = 2) and (`ykt_db`.`co`.`deleted` = 0) and (`ykt_db`.`co`.`phone_or_pc` <> 3))"
}
}
]
}
}
}
若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出打印结果，请务必全选并复制粘贴上传。