溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
手上最近有一個很核心的新項目，業(yè)務有一部分承接自老系統(tǒng)，老系統(tǒng)是基于Oracle的，雖然平時的并發(fā)量和互聯網業(yè)務比起來不算高，但SQL的復雜性很高，5、6張表關聯是家常便飯。在集團去'O'的背景下，新項目的數據庫選項主要考慮NewSQL--TiDB。
由于新項目上線的時間較緊且業(yè)務邏輯復雜，開發(fā)同事表示新項目SQL的復雜度不會低于老系統(tǒng)，現在的擔心點就是TiDB執(zhí)行復雜SQL時的性能，最近在測試時發(fā)現了這樣一個現象（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í)行計劃：
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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í)行計劃：
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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í)行計劃一致（只有l(wèi)ike的值不同），第二條的篩選條件更寬松，臨時結果集更大，join時的消耗按理說應該大于第一條，也就是說第二條執(zhí)行時間應該更長，可測試結果卻是相反的！
后續(xù)多次測試中發(fā)現，當把like后的值改為多個字母時（如'aa'，'abc'等等），其執(zhí)行時間都在3.5s左右；
當把like后的值改為單個字母或者中文時（如'a'，'業(yè)務'），其執(zhí)行時間大幅降低至0.7s左右，完全和我的認知相反。
為了確定是不是like查詢的問題，又做了如下測試：
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)
可見，TiDB在處理這兩個值的like查詢時效率沒有明顯差異，也就是說問題不是出在like查詢這里，like ‘%t%的結果集也確實要大很多，那么問題來了，究竟是什么原因導致上述SQL執(zhí)行時間違反常理的呢？先做個mark，記錄下這個問題，探索答案中。。
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
