TiDBSQL测试之like查询的疑问
手上最近有一个很核心的新项目，业务有一部分承接自老系统，老系统是基于Oracle的，虽然平时的并发量和互联网业务比起来不算高，但SQL的复杂性很高，5、6张表关联是家常便饭。在集团去'O'的背景下，新项目的数据库选项主要考虑NewSQL--TiDB。
由于新项目上线的时间较紧且业务逻辑复杂，开发同事表示新项目SQL的复杂度不会低于老系统，现在的担心点就是TiDB执行复杂SQL时的性能，最近在测试时发现了这样一个现象（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)
执行计划：
“真诚服务，让网络创造价值”是我们的服务理念，创新互联公司团队十载如一日始终坚持在网站建设领域,为客户提供优质服。不管你处于什么行业，助你轻松跨入“互联网+”时代，PC网站+手机网站+公众号+微信小程序。
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)
执行计划：
+-----------------------------------------------+-------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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执行计划一致（只有like的值不同），第二条的筛选条件更宽松，临时结果集更大，join时的消耗按理说应该大于第一条，也就是说第二条执行时间应该更长，可测试结果却是相反的！
后续多次测试中发现，当把like后的值改为多个字母时（如'aa'，'abc'等等），其执行时间都在3.5s左右；
当把like后的值改为单个字母或者中文时（如'a'，'业务'），其执行时间大幅降低至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执行时间违反常理的呢？先做个mark，记录下这个问题，探索答案中。。
分享题目：TiDBSQL测试之like查询的疑问
本文路径：http://cdiso.cn/article/ipdoip.html
