如下是mysql5.7执行只用了0.18秒:
mysql5.7
SELECT
CONCAT(
t5.CODE
,
LPAD(t4.buildingNum, 2, 0),
t3.unitNum
,
t2.NUMBER_NAME
,
‘01’
) ASsip
,
t.MOBILE_NO
ASphone
,
t7.destinationIp AS auth
FROM
reg_user_info
t
LEFT JOINrh_user_house
t1 ON t1.R_USER_ID
= t.R_USER_ID
LEFT JOINhouse_number
t2 ON t2.NUMBER_ID
= t1.NUMBER_ID
LEFT JOINmate_unit_info
t3 ON t3.UNIT_ID
= t2.UNIT_ID
LEFT JOINtd_unit_channel
c ON c.unitId
= t3.UNIT_ID
LEFT JOINbuilding_info
t4 ON t4.BUILDING_ID
= t3.BUILDING_ID
LEFT JOINcell_info
t5 ON t5.CELL_ID
= t4.CELL_ID
LEFT JOIN td_indoor t7 ON t2.number_id = t7.roomId
WHERE
c.channelId
= ‘0351C45DA1B44FD1899F031E515E1583’
AND t1.call = 1;
±---------------±------------±-----+
| sip | phone | auth |
±---------------±------------±-----+
| 75517223070601 | 13513513500 | NULL |
±---------------±------------±-----+
1 row in set (0.18 sec)
相同的数据量在TIDB5.0.1环境配置了3个TIFLASH环境查询,居然要4.08秒!
tidb5.0.1
SELECT
CONCAT(
t5.CODE
,
LPAD(t4.buildingNum, 2, 0),
t3.unitNum
,
t2.NUMBER_NAME
,
‘01’
) ASsip
,
t.MOBILE_NO
ASphone
,
t7.destinationIp AS auth
FROM
reg_user_info
t
LEFT JOINrh_user_house
t1 ON t1.R_USER_ID
= t.R_USER_ID
LEFT JOINhouse_number
t2 ON t2.NUMBER_ID
= t1.NUMBER_ID
LEFT JOINmate_unit_info
t3 ON t3.UNIT_ID
= t2.UNIT_ID
LEFT JOINtd_unit_channel
c ON c.unitId
= t3.UNIT_ID
LEFT JOINbuilding_info
t4 ON t4.BUILDING_ID
= t3.BUILDING_ID
LEFT JOINcell_info
t5 ON t5.CELL_ID
= t4.CELL_ID
LEFT JOIN td_indoor t7 ON t2.number_id = t7.roomId
WHERE
c.channelId
= ‘0351C45DA1B44FD1899F031E515E1583’
AND t1.call = 1;
±---------------±------------±-----+
| sip | phone | auth |
±---------------±------------±-----+
| 75517223070601 | 13513513500 | NULL |
±---------------±------------±-----+
1 row in set (4.08 sec)