TiDBSQL測(cè)試之like查詢(xún)的疑問(wèn)
手上最近有一個(gè)很核心的新項(xiàng)目，業(yè)務(wù)有一部分承接自老系統(tǒng)，老系統(tǒng)是基于Oracle的，雖然平時(shí)的并發(fā)量和互聯(lián)網(wǎng)業(yè)務(wù)比起來(lái)不算高，但SQL的復(fù)雜性很高，5、6張表關(guān)聯(lián)是家常便飯。在集團(tuán)去'O'的背景下，新項(xiàng)目的數(shù)據(jù)庫(kù)選項(xiàng)主要考慮NewSQL--TiDB。
由于新項(xiàng)目上線(xiàn)的時(shí)間較緊且業(yè)務(wù)邏輯復(fù)雜，開(kāi)發(fā)同事表示新項(xiàng)目SQL的復(fù)雜度不會(huì)低于老系統(tǒng)，現(xiàn)在的擔(dān)心點(diǎn)就是TiDB執(zhí)行復(fù)雜SQL時(shí)的性能，最近在測(cè)試時(shí)發(fā)現(xiàn)了這樣一個(gè)現(xiàn)象（TiDB為2.1版本）：
MySQL [db1]> select
-> DISTINCT v.vehicle_no vehicleNo,
-> '2019-05-31 08:59:00' as expireDate,
-> b.batch_name as batchName,
-> r.is_distribute isDistribute,
-> date_format(r.date_created, 'yyyy-MM-dd') dateCreated,
-> r.tmr_id tmrId,
-> t.customer_id customerId,
-> r.task_group_id taskGroupId,
-> 'test' as codeDesc,
-> 'test' as robotName,
-> c.campaign_name campaignName,
-> v.policy_end_date policyEndDate,
-> 'test' as listRank,
-> s.special_dial_org_name specialName,
-> 'test' as secondOrg
-> from t_pub_task t,
-> t_pub_robot_communicate r,
-> t_pub_campaign c,
-> t_pub_batch b left join
-> t_aas_dialorg_custcount_source s on b.tcims_batch_id = s.batch_id,
-> t_pc_vehicle v
-> where r.task_group_id = t.task_group_id
-> and t.vehicle_id is not null
-> and t.vehicle_id = v.nets_vehicle_id
-> and t.batch_id = b.batch_id
-> and t.campaign_id = c.campaign_id
-> and t.team_id = '1000002832'
-> and v.nets_cust_id = t.customer_id
-> and r.list_type = 7
-> and c.biz_model = '1'
-> and r.tmr_id IS NULL
-> AND r.is_distribute = 'N'
-> AND (r.date_created >= date_format('20090531092304', '%Y-%m-%d 00:00:00'))
-> AND (r.date_created < DATE_ADD(date_format('20190531092304', '%Y-%m-%d 00:00:00'), interval 1 day))
-> AND r.robot_id = '22222'
-> AND (C.EXPIRED_DATE = '2011-04')
-> AND b.batch_name like '%test%'
-> AND t.org_id = '201'
-> AND exists (select e.list_rank
-> from t_pub_wx_entry_auto_call e
-> where t.TASK_GROUP_ID = e.task_group_id
-> and e.list_rank = 'A');
Empty set (3.54 sec)
執(zhí)行計(jì)劃：
“真誠(chéng)服務(wù)，讓網(wǎng)絡(luò)創(chuàng)造價(jià)值”是我們的服務(wù)理念，創(chuàng)新互聯(lián)公司團(tuán)隊(duì)十載如一日始終堅(jiān)持在網(wǎng)站建設(shè)領(lǐng)域,為客戶(hù)提供優(yōu)質(zhì)服。不管你處于什么行業(yè)，助你輕松跨入“互聯(lián)網(wǎng)+”時(shí)代，PC網(wǎng)站+手機(jī)網(wǎng)站+公眾號(hào)+微信小程序。
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | count | task | operator info |
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_25 | 1.00 | root | group by:b.batchname, c.campaignname, codedesc, datecreated, expiredate, listrank, r.isdistribute, r.taskgroupid, r.tmrid, robotname, s.specialname, secondorg, t.customerid, v.policyenddate, v.vehicleno, funcs:firstrow(v.vehicleno), firstrow(expiredate), firstrow(b.batchname), firstrow(r.isdistribute), firstrow(datecreated), firstrow(r.tmrid), firstrow(t.customerid), firstrow(r.taskgroupid), firstrow(codedesc), firstrow(robotname), firstrow(c.campaignname), firstrow(v.policyenddate), firstrow(listrank), firstrow(s.specialname), firstrow(secondorg) |
| └─Projection_26 | 0.81 | root | v.vehicle_no, "2019-05-31 08:59:00", b.batch_name, r.is_distribute, date_format(r.date_created, "yyyy-MM-dd"), r.tmr_id, t.customer_id, r.task_group_id, "test", "test", c.campaign_name, v.policy_end_date, "test", s.special_dial_org_name, "test" |
| └─IndexJoin_32 | 0.81 | root | semi join, inner:IndexLookUp_31, outer key:t.task_group_id, inner key:e.task_group_id |
| ├─IndexJoin_42 | 1.02 | root | inner join, inner:TableReader_41, outer key:t.vehicle_id, inner key:v.nets_vehicle_id, other cond:eq(t.customer_id, v.nets_cust_id) |
| │ ├─HashRightJoin_48 | 1.02 | root | inner join, inner:IndexJoin_103, equal:[eq(t.batch_id, b.batch_id)] |
| │ │ ├─IndexJoin_103 | 1.01 | root | inner join, inner:TableReader_102, outer key:t.campaign_id, inner key:c.campaign_id |
| │ │ │ ├─IndexJoin_111 | 1.01 | root | inner join, inner:IndexLookUp_110, outer key:r.task_group_id, inner key:t.task_group_id |
| │ │ │ │ ├─IndexLookUp_169 | 0.48 | root | |
| │ │ │ │ │ ├─IndexScan_166 | 93.00 | cop | table:r, index:LIST_TYPE, range:[7,7], keep order:false |
| │ │ │ │ │ └─Selection_168 | 0.48 | cop | eq(r.is_distribute, "N"), eq(r.robot_id, "22222"), ge(r.date_created, 2009-05-31 00:00:00.000000), isnull(r.tmr_id), lt(r.date_created, 2019-06-01 00:00:00) |
| │ │ │ │ │ └─TableScan_167 | 93.00 | cop | table:T_PUB_ROBOT_COMMUNICATE, keep order:false |
| │ │ │ │ └─IndexLookUp_110 | 1904.23 | root | |
| │ │ │ │ ├─IndexScan_107 | 1.86 | cop | table:t, index:TASK_GROUP_ID, STATUS, range: decided by [r.task_group_id], keep order:false |
| │ │ │ │ └─Selection_109 | 1904.23 | cop | eq(t.org_id, "201"), eq(t.team_id, "1000002832"), not(isnull(t.vehicle_id)) |
| │ │ │ │ └─TableScan_108 | 1.86 | cop | table:T_PUB_TASK, keep order:false |
| │ │ │ └─TableReader_102 | 175.57 | root | data:Selection_101 |
| │ │ │ └─Selection_101 | 175.57 | cop | eq(c.biz_model, "1"), eq(c.expired_date, "2011-04") |
| │ │ │ └─TableScan_100 | 1.00 | cop | table:c, range: decided by [t.campaign_id], keep order:false |
| │ │ └─HashLeftJoin_205 | 9608583.20 | root | left outer join, inner:TableReader_233, equal:[eq(b.tcims_batch_id, s.batch_id)] |
| │ │ ├─TableReader_231 | 9608583.20 | root | data:Selection_230 |
| │ │ │ └─Selection_230 | 9608583.20 | cop | like(b.batch_name, "%test%", 92) |
| │ │ │ └─TableScan_229 | 12010729.00 | cop | table:b, range:[-inf,+inf], keep order:false |
| │ │ └─TableReader_233 | 10000.00 | root | data:TableScan_232 |
| │ │ └─TableScan_232 | 10000.00 | cop | table:s, range:[-inf,+inf], keep order:false, stats:pseudo |
| │ └─TableReader_41 | 1.00 | root | data:TableScan_40 |
| │ └─TableScan_40 | 1.00 | cop | table:v, range: decided by [t.vehicle_id t.customer_id], keep order:false |
| └─IndexLookUp_31 | 28728956.15 | root | |
| ├─IndexScan_28 | 1.00 | cop | table:e, index:TASK_GROUP_ID, range: decided by [t.task_group_id], keep order:false |
| └─Selection_30 | 28728956.15 | cop | isnull(e.list_rank) |
| └─TableScan_29 | 1.00 | cop | table:T_PUB_WX_ENTRY_AUTO_CALL, keep order:false |
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+MySQL [db1]> select
-> DISTINCT v.vehicle_no vehicleNo,
-> '2019-05-31 08:59:00' as expireDate,
-> b.batch_name as batchName,
-> r.is_distribute isDistribute,
-> date_format(r.date_created, 'yyyy-MM-dd') dateCreated,
-> r.tmr_id tmrId,
-> t.customer_id customerId,
-> r.task_group_id taskGroupId,
-> 'test' as codeDesc,
-> 'test' as robotName,
-> c.campaign_name campaignName,
-> v.policy_end_date policyEndDate,
-> 'test' as listRank,
-> s.special_dial_org_name specialName,
-> 'test' as secondOrg
-> from t_pub_task t,
-> t_pub_robot_communicate r,
-> t_pub_campaign c,
-> t_pub_batch b left join
-> t_aas_dialorg_custcount_source s on b.tcims_batch_id = s.batch_id,
-> t_pc_vehicle v
-> where r.task_group_id = t.task_group_id
-> and t.vehicle_id is not null
-> and t.vehicle_id = v.nets_vehicle_id
-> and t.batch_id = b.batch_id
-> and t.campaign_id = c.campaign_id
-> and t.team_id = '1000002832'
-> and v.nets_cust_id = t.customer_id
-> and r.list_type = 7
-> and c.biz_model = '1'
-> and r.tmr_id IS NULL
-> AND r.is_distribute = 'N'
-> AND (r.date_created >= date_format('20090531092304', '%Y-%m-%d 00:00:00'))
-> AND (r.date_created < DATE_ADD(date_format('20190531092304', '%Y-%m-%d 00:00:00'), interval 1 day))
-> AND r.robot_id = '22222'
-> AND (C.EXPIRED_DATE = '2011-04')
-> AND b.batch_name like '%t%'
-> AND t.org_id = '201'
-> AND exists (select e.list_rank
-> from t_pub_wx_entry_auto_call e
-> where t.TASK_GROUP_ID = e.task_group_id
-> and e.list_rank = 'A');
Empty set (0.67 sec)
執(zhí)行計(jì)劃：
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | count | task | operator info |
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_25 | 1.00 | root | group by:b.batchname, c.campaignname, codedesc, datecreated, expiredate, listrank, r.isdistribute, r.taskgroupid, r.tmrid, robotname, s.specialname, secondorg, t.customerid, v.policyenddate, v.vehicleno, funcs:firstrow(v.vehicleno), firstrow(expiredate), firstrow(b.batchname), firstrow(r.isdistribute), firstrow(datecreated), firstrow(r.tmrid), firstrow(t.customerid), firstrow(r.taskgroupid), firstrow(codedesc), firstrow(robotname), firstrow(c.campaignname), firstrow(v.policyenddate), firstrow(listrank), firstrow(s.specialname), firstrow(secondorg) |
| └─Projection_26 | 0.81 | root | v.vehicle_no, "2019-05-31 08:59:00", b.batch_name, r.is_distribute, date_format(r.date_created, "yyyy-MM-dd"), r.tmr_id, t.customer_id, r.task_group_id, "test", "test", c.campaign_name, v.policy_end_date, "test", s.special_dial_org_name, "test" |
| └─IndexJoin_32 | 0.81 | root | semi join, inner:IndexLookUp_31, outer key:t.task_group_id, inner key:e.task_group_id |
| ├─IndexJoin_42 | 1.02 | root | inner join, inner:TableReader_41, outer key:t.vehicle_id, inner key:v.nets_vehicle_id, other cond:eq(t.customer_id, v.nets_cust_id) |
| │ ├─HashRightJoin_48 | 1.02 | root | inner join, inner:IndexJoin_103, equal:[eq(t.batch_id, b.batch_id)] |
| │ │ ├─IndexJoin_103 | 1.01 | root | inner join, inner:TableReader_102, outer key:t.campaign_id, inner key:c.campaign_id |
| │ │ │ ├─IndexJoin_111 | 1.01 | root | inner join, inner:IndexLookUp_110, outer key:r.task_group_id, inner key:t.task_group_id |
| │ │ │ │ ├─IndexLookUp_169 | 0.48 | root | |
| │ │ │ │ │ ├─IndexScan_166 | 93.00 | cop | table:r, index:LIST_TYPE, range:[7,7], keep order:false |
| │ │ │ │ │ └─Selection_168 | 0.48 | cop | eq(r.is_distribute, "N"), eq(r.robot_id, "22222"), ge(r.date_created, 2009-05-31 00:00:00.000000), isnull(r.tmr_id), lt(r.date_created, 2019-06-01 00:00:00) |
| │ │ │ │ │ └─TableScan_167 | 93.00 | cop | table:T_PUB_ROBOT_COMMUNICATE, keep order:false |
| │ │ │ │ └─IndexLookUp_110 | 1904.23 | root | |
| │ │ │ │ ├─IndexScan_107 | 1.86 | cop | table:t, index:TASK_GROUP_ID, STATUS, range: decided by [r.task_group_id], keep order:false |
| │ │ │ │ └─Selection_109 | 1904.23 | cop | eq(t.org_id, "201"), eq(t.team_id, "1000002832"), not(isnull(t.vehicle_id)) |
| │ │ │ │ └─TableScan_108 | 1.86 | cop | table:T_PUB_TASK, keep order:false |
| │ │ │ └─TableReader_102 | 175.57 | root | data:Selection_101 |
| │ │ │ └─Selection_101 | 175.57 | cop | eq(c.biz_model, "1"), eq(c.expired_date, "2011-04") |
| │ │ │ └─TableScan_100 | 1.00 | cop | table:c, range: decided by [t.campaign_id], keep order:false |
| │ │ └─HashLeftJoin_205 | 9608583.20 | root | left outer join, inner:TableReader_233, equal:[eq(b.tcims_batch_id, s.batch_id)] |
| │ │ ├─TableReader_231 | 9608583.20 | root | data:Selection_230 |
| │ │ │ └─Selection_230 | 9608583.20 | cop | like(b.batch_name, "%t%", 92) |
| │ │ │ └─TableScan_229 | 12010729.00 | cop | table:b, range:[-inf,+inf], keep order:false |
| │ │ └─TableReader_233 | 10000.00 | root | data:TableScan_232 |
| │ │ └─TableScan_232 | 10000.00 | cop | table:s, range:[-inf,+inf], keep order:false, stats:pseudo |
| │ └─TableReader_41 | 1.00 | root | data:TableScan_40 |
| │ └─TableScan_40 | 1.00 | cop | table:v, range: decided by [t.vehicle_id t.customer_id], keep order:false |
| └─IndexLookUp_31 | 16.50 | root | |
| ├─IndexScan_28 | 1.00 | cop | table:e, index:TASK_GROUP_ID, range: decided by [t.task_group_id], keep order:false |
| └─Selection_30 | 16.50 | cop | eq(e.list_rank, "A") |
| └─TableScan_29 | 1.00 | cop | table:T_PUB_WX_ENTRY_AUTO_CALL, keep order:false |
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+上述兩條SQL是綁定變量為不同值的同一條SQL
第一條為 b.batch_name like '%test%
第二條為 b.batch_name like '%t%
兩條SQL執(zhí)行計(jì)劃一致（只有l(wèi)ike的值不同），第二條的篩選條件更寬松，臨時(shí)結(jié)果集更大，join時(shí)的消耗按理說(shuō)應(yīng)該大于第一條，也就是說(shuō)第二條執(zhí)行時(shí)間應(yīng)該更長(zhǎng)，可測(cè)試結(jié)果卻是相反的！
后續(xù)多次測(cè)試中發(fā)現(xiàn)，當(dāng)把like后的值改為多個(gè)字母時(shí)（如'aa'，'abc'等等），其執(zhí)行時(shí)間都在3.5s左右；
當(dāng)把like后的值改為單個(gè)字母或者中文時(shí)（如'a'，'業(yè)務(wù)'），其執(zhí)行時(shí)間大幅降低至0.7s左右，完全和我的認(rèn)知相反。
為了確定是不是like查詢(xún)的問(wèn)題，又做了如下測(cè)試：
MySQL [db1]> select count() from (select batch_name from t_pub_batch where batch_name like '%t%') a;
+----------+
| count() |
+----------+
| 25739 |
+----------+
1 row in set (3.05 sec)
MySQL [db1]> select count() from (select batch_name from t_pub_batch where batch_name like '%test%') a;
+----------+
| count() |
+----------+
| 2 |
+----------+
1 row in set (3.04 sec)
可見(jiàn)，TiDB在處理這兩個(gè)值的like查詢(xún)時(shí)效率沒(méi)有明顯差異，也就是說(shuō)問(wèn)題不是出在like查詢(xún)這里，like ‘%t%的結(jié)果集也確實(shí)要大很多，那么問(wèn)題來(lái)了，究竟是什么原因?qū)е律鲜鯯QL執(zhí)行時(shí)間違反常理的呢？先做個(gè)mark，記錄下這個(gè)問(wèn)題，探索答案中。。
本文標(biāo)題：TiDBSQL測(cè)試之like查詢(xún)的疑問(wèn)
分享地址：http://aaarwkj.com/article8/ipdoip.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、網(wǎng)站改版、品牌網(wǎng)站設(shè)計(jì)、搜索引擎優(yōu)化、網(wǎng)站建設(shè)、服務(wù)器托管
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
網(wǎng)頁(yè)設(shè)計(jì)公司知識(shí)
- 如何選擇成都網(wǎng)頁(yè)設(shè)計(jì)公司 2016-09-25
- 專(zhuān)做網(wǎng)頁(yè)設(shè)計(jì)的公司排名，怎么選擇網(wǎng)頁(yè)設(shè)計(jì)公司 2014-01-09
- 選擇專(zhuān)業(yè)的成都網(wǎng)頁(yè)設(shè)計(jì)公司技巧 2016-10-09
- 沈陽(yáng)網(wǎng)頁(yè)設(shè)計(jì)公司強(qiáng)烈推薦的13條黃金法則 2021-09-04
- 外貿(mào)網(wǎng)站頁(yè)面設(shè)計(jì)四大要素-成都網(wǎng)頁(yè)設(shè)計(jì)公司 2023-03-11
- 網(wǎng)頁(yè)設(shè)計(jì)公司提醒您網(wǎng)站外包需要提前溝通的問(wèn)題 2022-05-05
- 上海網(wǎng)頁(yè)設(shè)計(jì)公司:什么是提高網(wǎng)站安全性和改善用戶(hù)體驗(yàn)的方法？ 2020-11-14
- 成都網(wǎng)頁(yè)設(shè)計(jì)公司告訴你響應(yīng)式網(wǎng)站改怎么設(shè)計(jì)！ 2013-05-24
- 靜安網(wǎng)頁(yè)設(shè)計(jì)公司：網(wǎng)站文章中有多少鏈接才算合理？ 2020-11-16
- 上海網(wǎng)頁(yè)設(shè)計(jì)公司如何把企業(yè)網(wǎng)站打造成品牌 2020-11-17
- 如何尋找專(zhuān)業(yè)的網(wǎng)頁(yè)設(shè)計(jì)公司 2016-11-01
- 怎么快速提高網(wǎng)站設(shè)計(jì)水平-大連網(wǎng)頁(yè)設(shè)計(jì)公司分享 2022-07-02