对于某些 sql，tidb 与 mysql 之间的对比优化

[来了老弟]

请帮忙在 mysql 和 tidb 上执行以下 sql 并返回下结果

SELECT count(*) FROM eventlog b LEFT JOIN t_equipment_digit a ON a.is_trend_monitored = 1  AND a.equipment_code = 'zxP111-15'  AND b.channelname = a.digit_identity WHERE b.time BETWEEN '2020-05-10 14:05:00'  AND '2020-06-10 15:05:00'  AND a.digit_identity IS NOT NULL;

[Hacker_cBh8J2wj]

@户口舟亢

SELECT count(*) FROM eventlog b LEFT JOIN t_equipment_digit a ON a.is_trend_monitored = 1 AND a.equipment_code = ‘zxP111-15’ AND b.channelname = a.digit_identity WHERE b.time BETWEEN ‘2020-05-10 14:05:00’ AND ‘2020-06-10 15:05:00’ AND a.digit_identity IS NOT NULL;

tidb上结果：1365974条，5.6秒执行完
mysql结果：1365974，60秒执行完
结果一样

[来了老弟]

好的，收到，

SELECT count(DISTINCT a.equipment_code, a.digit_name AS dn, a.digit_identity AS di, a.value_upperlimit AS vu, a.value_lowerlimit AS vl) FROM eventlog b LEFT JOIN t_equipment_digit a ON a.is_trend_monitored = 1 AND a.equipment_code = ‘zxP111-15’ AND b.channelname = a.digit_identity WHERE b.time BETWEEN ‘2020-05-10 14:05:00’ AND ‘2020-06-10 15:05:00’ AND a.digit_identity IS NOT NULL;

SELECT DISTINCT a.equipment_code, a.digit_name AS dn, a.digit_identity AS di, a.value_upperlimit AS vu, a.value_lowerlimit AS vl FROM eventlog b LEFT JOIN t_equipment_digit a ON a.is_trend_monitored = 1 AND a.equipment_code = ‘zxP111-15’ AND b.channelname = a.digit_identity WHERE b.time BETWEEN ‘2020-05-10 14:05:00’ AND ‘2020-06-10 15:05:00’ AND a.digit_identity IS NOT NULL LIMIT 20;

这个 sql 辛苦也在 tidb 和 mysql 跑一下，截图反馈下

[Hacker_cBh8J2wj]

结果都是 6

[来了老弟]

确认下，一共四个结果。返回的都是 6

[Hacker_cBh8J2wj]

@户口舟亢

SELECT DISTINCT a.equipment_code, a.digit_name AS dn, a.digit_identity AS di, a.value_upperlimit AS vu, a.value_lowerlimit AS vl FROM eventlog b LEFT JOIN t_equipment_digit a ON a.is_trend_monitored = 1 AND a.equipment_code = ‘zxP111-15’ AND b.channelname = a.digit_identity WHERE b.time BETWEEN ‘2020-05-10 14:05:00’ AND ‘2020-06-10 15:05:00’ AND a.digit_identity IS NOT NULL LIMIT 20;

哦哦，后面还有一个sql，后面这个sql结果都是8，前面的sql结果都是6

[来了老弟]

你好

# 在 tidb 和 mysql 中执行时间和返回条数？
SELECT count(DISTINCT a.equipment_code, a.digit_name AS dn, a.digit_identity AS di, a.value_upperlimit AS vu, a.value_lowerlimit AS vl) FROM eventlog b LEFT JOIN t_equipment_digit a ON a.is_trend_monitored = 1 AND a.equipment_code = ‘zxP111-15’ AND b.channelname = a.digit_identity WHERE b.time BETWEEN ‘2020-05-10 14:05:00’ AND ‘2020-06-10 15:05:00’ AND a.digit_identity IS NOT NULL;

# sql2 在 mysql tidb 中执行时间和返回条数是？
SELECT DISTINCT a.equipment_code, a.digit_name AS dn, a.digit_identity AS di, a.value_upperlimit AS vu, a.value_lowerlimit AS vl FROM eventlog b LEFT JOIN t_equipment_digit a ON a.is_trend_monitored = 1 AND a.equipment_code = ‘zxP111-15’ AND b.channelname = a.digit_identity WHERE b.time BETWEEN ‘2020-05-10 14:05:00’ AND ‘2020-06-10 15:05:00’ AND a.digit_identity IS NOT NULL LIMIT 20;

可以截图或者照相哈，一共四个结果，

[Hacker_cBh8J2wj]

@户口舟亢

SELECT DISTINCT a.equipment_code, a.digit_name AS dn, a.digit_identity AS di, a.value_upperlimit AS vu, a.value_lowerlimit AS vl FROM eventlog b LEFT JOIN t_equipment_digit a ON a.is_trend_monitored = 1 AND a.equipment_code = ‘zxP111-15’ AND b.channelname = a.digit_identity WHERE b.time BETWEEN ‘2020-05-10 14:05:00’ AND ‘2020-06-10 15:05:00’ AND a.digit_identity IS NOT NULL LIMIT 20;

sql1—tidb:

sql1—mysq;:

sql2—tidb:

sql2—mysql:

[来了老弟]

ok，感谢反馈，收到

[来了老弟]

辛苦大佬帮忙在 mysql 中执行下下面的 sql，并将文本信息返回下

explain format=json SELECT DISTINCT a.equipment_code, a.digit_name AS dn, a.digit_identity AS di, a.value_upperlimit AS vu, a.value_lowerlimit AS vl FROM eventlog b LEFT JOIN t_equipment_digit a ON a.is_trend_monitored = 1 AND a.equipment_code = ‘zxP111-15’ AND b.channelname = a.digit_identity WHERE b.time BETWEEN ‘2020-05-10 14:05:00’ AND ‘2020-06-10 15:05:00’ AND a.digit_identity IS NOT NULL LIMIT 20;

[Hacker_cBh8J2wj]

{
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “2509.96”
},
“duplicates_removal”: {
“using_temporary_table”: true,
“using_filesort”: false,
“nested_loop”: [
{
“table”: {
“table_name”: “a”,
“access_type”: “range”,
“possible_keys”: [
“idx_digit_identity”,
“idx_mutlti1”
],
“key”: “idx_mutlti1”,
“used_key_parts”: [
“equipment_code”,
“digit_identity”
],
“key_length”: “275”,
“rows_examined_per_scan”: 20,
“rows_produced_per_join”: 2,
“filtered”: “10.00”,
“index_condition”: “(((base_comm_equipment_db_pub.a.digit_identity is not null) and (base_comm_equipment_db_pub.a.equipment_code = ‘zxP111-15’)) and (base_comm_equipment_db_pub.a.digit_identity is not null))”,
“cost_info”: {
“read_cost”: “28.61”,
“eval_cost”: “0.40”,
“prefix_cost”: “29.01”,
“data_read_per_join”: “10K”
},
“used_columns”: [
“id”,
“equipment_code”,
“digit_name”,
“digit_identity”,
“value_upperlimit”,
“value_lowerlimit”,
“is_trend_monitored”
],
“attached_condition”: “(base_comm_equipment_db_pub.a.is_trend_monitored = 1)”
}
},
{
“table”: {
“table_name”: “b”,
“access_type”: “ref”,
“possible_keys”: [
“idx_time_channelname”,
“idx_channelname”
],
“key”: “idx_channelname”,
“used_key_parts”: [
“channelname”
],
“key_length”: “153”,
“ref”: [
“base_comm_equipment_db_pub.a.digit_identity”
],
“rows_examined_per_scan”: 1033,
“rows_produced_per_join”: 1033,
“filtered”: “50.00”,
“index_condition”: “(base_comm_equipment_db_pub.b.channelname = base_comm_equipment_db_pub.a.digit_identity)”,
“distinct”: true,
“cost_info”: {
“read_cost”: “2067.46”,
“eval_cost”: “206.75”,
“prefix_cost”: “2509.96”,
“data_read_per_join”: “823K”
},
“used_columns”: [
“id”,
“time”,
“channelname”
],
“attached_condition”: “(base_comm_equipment_db_pub.b.time between ‘2020-05-10 14:05:00’ and ‘2020-06-10 15:05:00’)”
}
}
]
}
}
}

[来了老弟]

你好，这边定位可能是 tidb 对 mysql losse index 方面需要做一些优化，可以关注下这个 pr：https://github.com/pingcap/tidb/issues/14460

[Hacker_cBh8J2wj]

也就是说，目前tidb对这种情况 还没做优化处理吧？

[来了老弟]

首先感谢你的反馈，一楼的问题是需要研发童鞋帮忙优化一下的。

[Hacker_cBh8J2wj]

哦哦，是不是说，后面的tidb新版本里面可能会包含这种情况的优化。现在的版本 还没涉及到。

[来了老弟]

是的。