tidb union all使用问题

curry_Ni · 2020 年5 月 6 日 09:03

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

【TiDB 版本】：
【问题描述】：union all 第二个子查询不会走索引

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出打印结果，请务必全选并复制粘贴上传。

yilong · 2020 年5 月 6 日 09:31

请问哪个版本
麻烦反馈具体表结构和 sql，
麻烦反馈执行计划，具体指哪个子查询不会走索引，多谢

curry_Ni · 2020 年5 月 8 日 03:00

版本：3.0.9 具体表结构不方便展示我来具体描述一下问题。一张view，由两张部分union all组成，两部分分别都只是left join 关系，on条件都只有一个字段。

create view xxx as select a. ,xxx from table_1 as a left outer join table_2 as b on a.order_id = b.order_id --部分A union all select a. ,xxx from table_3 as a left outer join table_4 as b on a.store_id = b.store_id --部分B

索引情况：四张表on语句中的字段都有索引

执行的sql：select * from view where order_id = ‘xxxx’ and store_id in (‘aaa’,‘bbb’) 通过explain 部分A走索引部分B不走索引

情况2: 为了排查具体问题，我将view中的部分A与部分B交换位置，执行上述sql。通过explain 交换位置到上面的部分B走索引，部分A不走索引。情况完全倒置。

所以我觉得view中使用union all 导致下面的子查询不会走索引的结论，麻烦解答一下原因，感谢 [/quote]

winoros · 2020 年5 月 8 日 06:13

您好，请问可以提供一下 explain 的结果吗

curry_Ni · 2020 年5 月 8 日 06:59

更换两部分后的执行计划对比：

Union_18    755424.13   root    </br>
├─Projection_19 2.81    root    store.t3.id, store.t3.order_no, store.t3.store, store.t4.store_name, store.t3.cashier, store.t3.cashier_name, cast(user), cast(user_type), cast(store.t3.actual), store.t3.pay, store.t3.item, cast(freight), cast(activity), cast(coupon), cast(feedbackbonus), cast(giftcard_price), cast(categorycard_price), cast(activity_info), store.t3.state, store.t3.pay_type, store.t3.pay_type_name, store.t3.pay_time, store.t3.send_status, store.t3.create_time, store.t3.update_time, store.t3.goods_count</br>
│ └─Projection_20   2.81    root    store.t3.id, store.t3.order_no, store.t3.store, store.t4.name, store.t3.cashier, store.t3.cashier_name, NULL, NULL, store.t3.pay, store.t3.pay, store.t3.total, NULL, NULL, NULL, NULL, NULL, NULL, NULL, store.t3.state, store.t3.pay_type, store.t3.pay_type_name, store.t3.pay_time, store.t3.send_status, store.t3.create_time, store.t3.update_time, store.t3.goods_count</br>
│   └─MergeJoin_21  2.81    root    left outer join, left key:store.t3.store, right key:store.t4.store</br>
│     ├─IndexLookUp_37  2.81    root    </br>
│     │ ├─IndexScan_34  227.42  cop table:T3, index:store, state, pay_time, range:["0018","0018"], keep order:true</br>
│     │ └─Selection_36  2.81    cop in(store.t3.cashier, "0058", "0085", "0086", "0073", "0122", "0123")</br>
│     │   └─TableScan_35    227.42  cop table:sale_order, keep order:false</br>
│     └─Projection_42   1.00    root    store.t4.store, store.t4.name</br>
│       └─IndexLookUp_41    1.00    root    </br>
│         ├─IndexScan_39    1.00    cop table:T4, index:store, range:["0018","0018"], keep order:true, stats:pseudo</br>
│         └─TableScan_40    1.00    cop table:store, keep order:false, stats:pseudo</br>
└─Projection_61 755421.32   root    store.t1.id, cast(store.t1.order_no), cast(store.t1.store), cast(store.t1.store_name), cast(store.t1.cashier), cast(store.t1.cashier_name), cast(store.t1.user), store.t1.user_type, cast(store.t1.actual), store.t1.pay, store.t1.item, store.t1.freight, store.t1.activity, store.t1.coupon, store.t1.feedbackbonus, store.t1.giftcard_price, store.t1.categorycard_price, cast(store.t1.activity_info), store.t1.state, cast(store.t1.pay_type), cast(store.t1.pay_type_name), store.t1.pay_time, store.t1.send_status, store.t1.create_time, store.t1.update_time, store.t2.goods_count</br>
  └─IndexJoin_66    755421.32   root    left outer join, inner:IndexLookUp_65, outer key:store.t1.order_no, inner key:store.t2.sale_order_no</br>
    ├─Selection_77  288096.80   root    in(cast(store.t1.cashier), "0058", "0085", "0086", "0073", "0122", "0123"), in(cast(store.t1.store), "0018")</br>
    │ └─TableReader_79  360121.00   root    data:TableScan_78</br>
    │   └─TableScan_78  360121.00   cop table:T1, range:[0,+inf], keep order:false</br>
    └─IndexLookUp_65    2.61    root    </br>
      ├─IndexScan_63    2.61    cop table:T2, index:sale_order_no, range: decided by [eq(store.t2.sale_order_no, store.t1.order_no)], keep order:false</br>
      └─TableScan_64    2.61    cop table:offline_sale_order_goods, keep order:false

Union_19    356150.74   root    </br>
├─Projection_20 7.74    root    store.t1.id, store.t1.order_no, store.t1.store, store.t1.store_name, store.t1.cashier, store.t1.cashier_name, store.t1.user, store.t1.user_type, cast(store.t1.actual), store.t1.pay, store.t1.item, store.t1.freight, store.t1.activity, store.t1.coupon, store.t1.feedbackbonus, store.t1.giftcard_price, store.t1.categorycard_price, store.t1.activity_info, store.t1.state, store.t1.pay_type, store.t1.pay_type_name, store.t1.pay_time, store.t1.send_status, store.t1.create_time, store.t1.update_time, store.t2.goods_count</br>
│ └─IndexJoin_25    7.74    root    left outer join, inner:IndexLookUp_24, outer key:store.t1.order_no, inner key:store.t2.sale_order_no</br>
│   ├─IndexLookUp_42    2.95    root    </br>
│   │ ├─IndexScan_39    364.43  cop table:T1, index:store, pay_time, range:["0018","0018"], keep order:false</br>
│   │ └─Selection_41    2.95    cop in(store.t1.cashier, "0058", "0085", "0086", "0073", "0122", "0123")</br>
│   │   └─TableScan_40  364.43  cop table:offline_sale_order, keep order:false</br>
│   └─IndexLookUp_24    2.61    root    </br>
│     ├─IndexScan_22    2.61    cop table:T2, index:sale_order_no, range: decided by [eq(store.t2.sale_order_no, store.t1.order_no)], keep order:false</br>
│     └─TableScan_23    2.61    cop table:offline_sale_order_goods, keep order:false</br>
└─Projection_45 356143.00   root    store.t3.id, cast(store.t3.order_no), cast(store.t3.store), cast(store.t4.name), cast(store.t3.cashier), cast(store.t3.cashier_name), cast(user), cast(user_type), cast(store.t3.pay), store.t3.pay, store.t3.total, cast(freight), cast(activity), cast(coupon), cast(feedbackbonus), cast(giftcard_price), cast(categorycard_price), cast(activity_info), store.t3.state, cast(store.t3.pay_type), cast(store.t3.pay_type_name), store.t3.pay_time, store.t3.send_status, store.t3.create_time, store.t3.update_time, store.t3.goods_count</br>
  └─Projection_46   356143.00   root    store.t3.id, store.t3.order_no, store.t3.store, store.t4.name, store.t3.cashier, store.t3.cashier_name, NULL, NULL, store.t3.pay, store.t3.pay, store.t3.total, NULL, NULL, NULL, NULL, NULL, NULL, NULL, store.t3.state, store.t3.pay_type, store.t3.pay_type_name, store.t3.pay_time, store.t3.send_status, store.t3.create_time, store.t3.update_time, store.t3.goods_count</br>
    └─HashLeftJoin_49   356143.00   root    left outer join, inner:Selection_67, equal:[eq(store.t3.store, store.t4.store)]</br>
      ├─Selection_64    284914.40   root    in(cast(store.t3.cashier), "0058", "0085", "0086", "0073", "0122", "0123"), in(cast(store.t3.store), "0018")</br>
      │ └─TableReader_66    356143.00   root    data:TableScan_65</br>
      │   └─TableScan_65    356143.00   cop table:T3, range:[0,+inf], keep order:false</br>
      └─Selection_67    37.60   root    in(cast(store.t4.store), "0018")</br>
        └─TableReader_69    47.00   root    data:TableScan_68</br>
          └─TableScan_68    47.00   cop table:T4, range:[0,+inf], keep order:false, stats:pseudo

yilong · 2020 年5 月 8 日 12:30

请有结果后，帮忙答复下解决方案，多谢。

francis0407 · 2020 年5 月 9 日 06:47

可以提供一下表结构、统计信息以及原始的 SQL 吗？我本地复现一下这个问题

curry_Ni · 2020 年5 月 11 日 03:15

Projection_5    952680.51   root    store.t1.id, store.t1.order_no, store.t1.store_id, store.t1.store_name, store.t1.cashier_id, store.t1.cashier_name, store.t1.user_id, store.t1.user_type, store.t1.actual, store.t1.pay, store.t1.item, store.t1.freight, store.t1.activity, store.t1.coupon, store.t1.feedbackbonus, store.t1.giftcard_price, store.t1.categorycard_price, store.t1.activity_info, store.t1.state, store.t1.pay_type, store.t1.pay_type_name, store.t1.pay_time, store.t1.send_status, store.t1.create_time, store.t1.update_time, store.t2.count
   └─MergeJoin_6   952680.51   root    left outer join, left key:store.t1.order_no, right key:store.t2.sale_order_no
     ├─IndexLookUp_14  363326.00   root    
     │ ├─IndexScan_12  363326.00   cop table:T1, index:order_no, range:[NULL,+inf], keep order:true
     │ └─TableScan_13  363326.00   cop table:offline_sale_order, keep order:false
     └─Projection_19   1010719.00  root    store.t2.sale_order_no, store.t2.count
       └─IndexLookUp_18    1010719.00  root    
         ├─IndexScan_16    1010719.00  cop table:T2, index:sale_order_no, range:[NULL,+inf], keep order:true
         └─TableScan_17    1010719.00  cop table:offline_sale_order_goods, keep order:false

Projection_5    445445.00   root    store.t3.id, store.t3.order_no, store.t3.store_id, store.t4.name, store.t3.cashier_id, store.t3.cashier_name, NULL, NULL, store.t3.pay, store.t3.pay, store.t3.total, NULL, NULL, NULL, NULL, NULL, NULL, NULL, store.t3.state, store.t3.pay_type, store.t3.pay_type_name, store.t3.pay_time, store.t3.send_status, store.t3.create_time, store.t3.update_time, store.t3.goods_count
   └─HashLeftJoin_12   445445.00   root    left outer join, inner:TableReader_28, equal:[eq(store.t3.store_id, store.t4.store_id)]
     ├─TableReader_26  356356.00   root    data:TableScan_25
     │ └─TableScan_25  356356.00   cop table:T3, range:[0,+inf], keep order:false
     └─TableReader_28  47.00   root    data:TableScan_27
       └─TableScan_27  47.00   cop table:T4, range:[0,+inf], keep order:false, stats:pseudo

GangShen · 2020 年5 月 11 日 03:18

好的，我们再分析一下

curry_Ni · 2020 年5 月 11 日 04:22

Projection_8    41.75   root    store.t1.id, store.t1.order_no, store.t1.store_id, store.t1.store_name, store.t1.cashier_id, store.t1.cashier_name, store.t1.user_id, store.t1.user_type, store.t1.actual, store.t1.pay, store.t1.item, store.t1.freight, store.t1.activity, store.t1.coupon, store.t1.feedbackbonus, store.t1.giftcard_price, store.t1.categorycard_price, store.t1.activity_info, store.t1.state, store.t1.pay_type, store.t1.pay_type_name, store.t1.pay_time, store.t1.send_status, store.t1.create_time, store.t1.update_time, store.t2.goods_count
2   └─IndexJoin_13  41.75   root    left outer join, inner:IndexLookUp_12, outer key:store.t1.order_no, inner key:store.t2.sale_order_no
3     ├─IndexLookUp_30  15.92   root    
4     │ ├─IndexScan_27  505.25  cop table:T1, index:store_id, pay_time, range:["0018","0018"], keep order:false
5     │ └─Selection_29  15.92   cop in(store.t1.cashier_id, "0058", "0085", "0086", "0073", "0122", "0123", "0064", "0055", "0213", "0070", "0166", "0102", "0116", "0042", "0176", "0227", "0077")
6     │   └─TableScan_28    505.25  cop table:offline_sale_order, keep order:false
7     └─IndexLookUp_12  2.61    root    
8       ├─IndexScan_10  2.61    cop table:T2, index:sale_order_no, range: decided by [eq(store.t2.sale_order_no, store.t1.order_no)], keep order:false
9       └─TableScan_11  2.61    cop table:offline_sale_order_goods, keep order:false

Projection_9    8.81    root    store.t3.id, store.t3.order_no, store.t3.store_id, store.t4.name, store.t3.cashier_id, store.t3.cashier_name, NULL, NULL, store.t3.pay, store.t3.pay, store.t3.total, NULL, NULL, NULL, NULL, NULL, NULL, NULL, store.t3.state, store.t3.pay_type, store.t3.pay_type_name, store.t3.pay_time, store.t3.send_status, store.t3.create_time, store.t3.update_time, store.t3.goods_count
2   └─MergeJoin_10  8.81    root    left outer join, left key:store.t3.store_id, right key:store.t4.store_id
3     ├─IndexLookUp_26  8.81    root    
4     │ ├─IndexScan_23  239.24  cop table:T3, index:store_id, state, pay_time, range:["0018","0018"], keep order:true
5     │ └─Selection_25  8.81    cop in(store.t3.cashier_id, "0058", "0085", "0086", "0073", "0122", "0123", "0064", "0055", "0213", "0070", "0166", "0102", "0116", "0042", "0176", "0227", "0077")
6     │   └─TableScan_24    239.24  cop table:sale_order, keep order:false
7     └─Projection_31   1.00    root    store.t4.store_id, store.t4.name
8       └─IndexLookUp_30    1.00    root    
9         ├─IndexScan_28    1.00    cop table:T4, index:store_id, range:["0018","0018"], keep order:true, stats:pseudo
10        └─TableScan_29    1.00    cop table:store, keep order:false, stats:pseudo

GangShen · 2020 年5 月 11 日 04:40

感谢反馈信息