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

Hacker_cBh8J2wj · 2020 年7 月 15 日 03:01

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

【TiDB 版本】：4.0.2
【问题描述】：同一个sql，mysql比tidb性能好很多，mysql 里8毫秒，tidb里要5秒。

sql如下：
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

mysql执行计划如下：

tidb执行计划如下：

来了老弟 · 2020 年7 月 15 日 03:41

hi

可以提供下mysql 和 tidb 中的表结构吗？

Hacker_cBh8J2wj · 2020 年7 月 15 日 04:06

tidb 里的表结构如下：
CREATE TABLE t_equipment_digit (
id varchar(64) COLLATE utf8_general_ci NOT NULL COMMENT ‘id’,
equipment_code varchar(30) COLLATE utf8_general_ci NOT NULL COMMENT ‘设备编码’,
digit_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘名称’,
digit_identity varchar(60) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘标识’,
data_type tinyint(4) DEFAULT NULL COMMENT ‘数据类型’,
value_upperlimit decimal(12,4) DEFAULT NULL COMMENT ‘数据上限’,
value_lowerlimit decimal(12,4) DEFAULT NULL COMMENT ‘数据下限’,
digit_type_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘数字类型id’,
digit_type_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘数字类型名称’,
energy_type_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘子类型id’,
description varchar(256) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘描述’,
create_by varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘创建者’,
create_date datetime DEFAULT NULL COMMENT ‘创建时间’,
update_by varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘更新者’,
update_date timestamp NULL DEFAULT NULL COMMENT ‘更新时间’,
del_flag char(1) COLLATE utf8_general_ci DEFAULT ‘0’ COMMENT ‘删除标记’,
unit_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘计量单位id’,
unit varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘计量单位’,
energy_type_name varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘子类型名称’,
fault_def_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘故障类型id’,
fault_def_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘故障类型名称’,
is_monitored tinyint(4) DEFAULT NULL COMMENT ‘是否监视’,
icon_url varchar(255) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘图标url’,
is_trend_monitored tinyint(4) DEFAULT NULL COMMENT ‘是否趋势监控’,
is_data_distribute tinyint(4) DEFAULT NULL COMMENT ‘是否数据下发’,
plc_datatype varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘PLC数据类型’,
plc_address varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘PLC点位地址’,
plc_bit int(11) DEFAULT NULL COMMENT ‘plc点位序号’,
PRIMARY KEY (id),
KEY idx_digit_type_id (digit_type_id),
KEY idx_digit_identity (digit_identity),
KEY idx_mutlti1 (equipment_code,digit_identity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT=‘设备数字点’;

CREATE TABLE eventlog (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘id’,
time datetime DEFAULT NULL COMMENT ‘采集时间’,
channelname varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘数据监控点标识’,
channelvalue varchar(80) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘采集值’,
state int(11) DEFAULT NULL COMMENT ‘state’,
create_by varchar(64) COLLATE utf8_general_ci DEFAULT ‘0’ COMMENT ‘创建者’,
create_date datetime DEFAULT NULL COMMENT ‘创建时间’,
update_by varchar(64) COLLATE utf8_general_ci DEFAULT ‘0’ COMMENT ‘更新者’,
update_date datetime DEFAULT NULL COMMENT ‘修改时间’,
del_flag char(1) COLLATE utf8_general_ci DEFAULT ‘0’ COMMENT ‘删除标记’,
PRIMARY KEY (id),
KEY idx_time_channelname (time,channelname),
KEY idx_channelname (channelname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=123804003 COMMENT=‘设备采集数据表’;

Hacker_cBh8J2wj · 2020 年7 月 15 日 04:07

mysql里的表结构如下，跟tidb一样的：
CREATE TABLE t_equipment_digit (
id varchar(64) NOT NULL COMMENT ‘id’,
equipment_code varchar(30) NOT NULL COMMENT ‘设备编码’,
digit_name varchar(50) DEFAULT NULL COMMENT ‘名称’,
digit_identity varchar(60) DEFAULT NULL COMMENT ‘标识’,
data_type tinyint(4) DEFAULT NULL COMMENT ‘数据类型’,
value_upperlimit decimal(12,4) DEFAULT NULL COMMENT ‘数据上限’,
value_lowerlimit decimal(12,4) DEFAULT NULL COMMENT ‘数据下限’,
digit_type_id varchar(64) DEFAULT NULL COMMENT ‘数字类型id’,
digit_type_name varchar(50) DEFAULT NULL COMMENT ‘数字类型名称’,
energy_type_id varchar(64) DEFAULT NULL COMMENT ‘子类型id’,
description varchar(256) DEFAULT NULL COMMENT ‘描述’,
create_by varchar(64) DEFAULT NULL COMMENT ‘创建者’,
create_date datetime DEFAULT NULL COMMENT ‘创建时间’,
update_by varchar(64) DEFAULT NULL COMMENT ‘更新者’,
update_date timestamp NULL DEFAULT NULL COMMENT ‘更新时间’,
del_flag char(1) DEFAULT ‘0’ COMMENT ‘删除标记’,
unit_id varchar(64) DEFAULT NULL COMMENT ‘计量单位id’,
unit varchar(30) DEFAULT NULL COMMENT ‘计量单位’,
energy_type_name varchar(30) DEFAULT NULL COMMENT ‘子类型名称’,
fault_def_id varchar(64) DEFAULT NULL COMMENT ‘故障类型id’,
fault_def_name varchar(50) DEFAULT NULL COMMENT ‘故障类型名称’,
is_monitored tinyint(4) DEFAULT NULL COMMENT ‘是否监视’,
icon_url varchar(255) DEFAULT NULL COMMENT ‘图标url’,
is_trend_monitored tinyint(4) DEFAULT NULL COMMENT ‘是否趋势监控’,
is_data_distribute tinyint(4) DEFAULT NULL COMMENT ‘是否数据下发’,
plc_datatype varchar(30) DEFAULT NULL COMMENT ‘PLC数据类型’,
plc_address varchar(50) DEFAULT NULL COMMENT ‘PLC点位地址’,
plc_bit int(11) DEFAULT NULL COMMENT ‘plc点位序号’,
PRIMARY KEY (id),
KEY idx_digit_type_id (digit_type_id) USING BTREE,
KEY idx_digit_identity (digit_identity),
KEY idx_mutlti1 (equipment_code,digit_identity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘设备数字点’;

CREATE TABLE eventlog (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘id’,
time datetime DEFAULT NULL COMMENT ‘采集时间’,
channelname varchar(50) DEFAULT NULL COMMENT ‘数据监控点标识’,
channelvalue varchar(80) DEFAULT NULL COMMENT ‘采集值’,
state int(11) DEFAULT NULL COMMENT ‘state’,
create_by varchar(64) DEFAULT ‘0’ COMMENT ‘创建者’,
create_date datetime DEFAULT NULL COMMENT ‘创建时间’,
update_by varchar(64) DEFAULT ‘0’ COMMENT ‘更新者’,
update_date datetime DEFAULT NULL COMMENT ‘修改时间’,
del_flag char(1) DEFAULT ‘0’ COMMENT ‘删除标记’,
PRIMARY KEY (id),
KEY idx_time_channelname (time,channelname) USING BTREE,
KEY idx_channelname (channelname) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=124153277 DEFAULT CHARSET=utf8 COMMENT=‘设备采集数据表’;

来了老弟 · 2020 年7 月 15 日 06:30

你好，请问下 tidb 和 mysql 中的两表的数据量是否是一致的呢？可以在 tidb 中执行下 analyze table eventlog / t_equipment_digit ，在执行该语句看结果时间要多久

如果时间没有太大改变辛苦将 tidb 中两张表的统计信息导出，我们看下。方式如下：

http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}

Hacker_cBh8J2wj · 2020 年7 月 15 日 06:43

tidb和mysql中都是2500多万，mysql中稍微多点，大概多50万记录。analyze后tidb执行时间没变，还是需要5秒多。另外，mysql机器比tidb任何一台单机的配置都差很多。

来了老弟 · 2020 年7 月 15 日 06:45

http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name} 导出执行计划看一下

Hacker_cBh8J2wj · 2020 年7 月 15 日 06:46

统计信息太长了，不能复制，截图又截不全，我想想办法

来了老弟 · 2020 年7 月 15 日 06:50

使用 curl http://172.16.4.107:24080/stats/dump/test/sbtest1 > a.txt 看是否可行？

Hacker_cBh8J2wj · 2020 年7 月 15 日 07:23

eventlog.txt (1.6 MB)
t_equipment_digit.txt (3.5 MB)

Hacker_cBh8J2wj · 2020 年7 月 15 日 07:24

已经上传两张表的统计信息

Hacker_cBh8J2wj · 2020 年7 月 15 日 07:58

看了下执行计划，tidb和mysql的执行计划差不多啊。为啥查询性能差别这么大。不过刚测试了sum 、count这些聚合查询，性能比mysql好很多。难道oltp 要做特殊优化？

来了老弟 · 2020 年7 月 15 日 08:10

统计信息已收到，这边会找研发童鞋帮忙看下，可能需要提供一些信息，到时会即使更新此帖

Hacker_cBh8J2wj · 2020 年7 月 15 日 08:15

发现个很奇怪的问题：
原始sql的时间范围是这样：b.time BETWEEN ‘2020-05-10 14:05:00’ AND ‘2020-06-10 15:05:00’ ；
改成这样（时间范围缩短为一个小时，以前是一个月零一个小时）：b.time BETWEEN ‘2020-06-10 14:05:00’ AND ‘2020-06-10 15:05:00’ ，这样b表过滤后的数据量变小。造成的结果是：mysql执行起来变慢（比以前查一个月的慢很多，要5秒），tidb查询起来变快（毫秒级）。

Hacker_cBh8J2wj · 2020 年7 月 15 日 08:18

tidb有没有类似 mysql 缓冲池的概念？是不是因为缓冲池没设置，默认的太小？或者进程独享缓存有没有这个概念？

来了老弟 · 2020 年7 月 15 日 08:36

为了避免混淆，此帖仅讨论一楼提到的问题，目前我们会关注下在当前条件下，tidb 的执行计划是不是最优的，是否有优化的空间。

tidb 没有此概念

来了老弟 · 2020 年7 月 15 日 14:16

hi，请提供下

explain analyze 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 · 2020 年7 月 16 日 01:21

|Limit_12|11.02|8|root||time:5.240160647s, loops:5|offset:0, count:20|N/A|N/A|
|---|---|---|---|---|---|---|---|---|
|└─HashAgg_15|11.02|8|root||time:5.240154784s, loops:5, PartialConcurrency:4, FinalConcurrency:4|group by:base_comm_equipment_db_pub.t_equipment_digit.digit_identity, base_comm_equipment_db_pub.t_equipment_digit.digit_name, base_comm_equipment_db_pub.t_equipment_digit.equipment_code, base_comm_equipment_db_pub.t_equipment_digit.value_lowerlimit, base_comm_equipment_db_pub.t_equipment_digit.value_upperlimit, funcs:firstrow(base_comm_equipment_db_pub.t_equipment_digit.equipment_code)->base_comm_equipment_db_pub.t_equipment_digit.equipment_code, funcs:firstrow(base_comm_equipment_db_pub.t_equipment_digit.digit_name)->base_comm_equipment_db_pub.t_equipment_digit.digit_name, funcs:firstrow(base_comm_equipment_db_pub.t_equipment_digit.digit_identity)->base_comm_equipment_db_pub.t_equipment_digit.digit_identity, funcs:firstrow(base_comm_equipment_db_pub.t_equipment_digit.value_upperlimit)->base_comm_equipment_db_pub.t_equipment_digit.value_upperlimit, funcs:firstrow(base_comm_equipment_db_pub.t_equipment_digit.value_lowerlimit)->base_comm_equipment_db_pub.t_equipment_digit.value_lowerlimit|1.26922607421875 MB|N/A|
|  └─IndexHashJoin_47|63234.11|1365974|root||time:5.236464375s, loops:1335, Concurrency:4|inner join, inner:IndexLookUp_36, outer key:base_comm_equipment_db_pub.t_equipment_digit.digit_identity, inner key:base_comm_equipment_db_pub.eventlog.channelname|55.483553886413574 MB|N/A|
|    ├─IndexLookUp_73(Build)|12.69|19|root||time:4.553956ms, loops:3, rpc num: 1, rpc time:2.23854ms, proc keys:20||5.91796875 KB|N/A|
|    │ ├─IndexRangeScan_70(Build)|17.00|20|cop[tikv]|table:a, index:idx_mutlti1(equipment_code, digit_identity)|time:0s, loops:1|range:["zxP111-15" -inf,"zxP111-15" +inf], keep order:false|N/A|N/A|
|    │ └─Selection_72(Probe)|12.69|19|cop[tikv]||time:0s, loops:1|eq(base_comm_equipment_db_pub.t_equipment_digit.is_trend_monitored, 1)|N/A|N/A|
|    │   └─TableRowIDScan_71|17.00|20|cop[tikv]|table:a|time:0s, loops:1|keep order:false|N/A|N/A|
|    └─IndexLookUp_36(Probe)|4981.55|1365974|root||time:3.925164033s, loops:1335, rpc num: 6, rpc max:561.366409ms, min:1.423447ms, avg:272.513396ms, p80:485.440057ms, p95:561.366409ms, proc keys max:1029711, p95:1029711||27.465017318725586 MB|N/A|
|      ├─Selection_34(Build)|10552.56|3128611|cop[tikv]||proc max:421ms, min:0s, p80:359ms, p95:421ms, iters:3076, tasks:6|not(isnull(base_comm_equipment_db_pub.eventlog.channelname))|N/A|N/A|
|      │ └─IndexRangeScan_32|10552.56|3128611|cop[tikv]|table:b, index:idx_channelname(channelname)|proc max:363ms, min:0s, p80:328ms, p95:363ms, iters:3076, tasks:6|range: decided by [eq(base_comm_equipment_db_pub.eventlog.channelname, base_comm_equipment_db_pub.t_equipment_digit.digit_identity)], keep order:false|N/A|N/A|
|      └─Selection_35(Probe)|4981.55|1365974|cop[tikv]||proc max:87ms, min:0s, p80:57ms, p95:73ms, iters:4539, tasks:332|ge(base_comm_equipment_db_pub.eventlog.time, 2020-05-10 14:05:00.000000), le(base_comm_equipment_db_pub.eventlog.time, 2020-06-10 15:05:00.000000)|N/A|N/A|
|        └─TableRowIDScan_33|10552.56|3128611|cop[tikv]|table:b|proc max:87ms, min:0s, p80:56ms, p95:72ms, iters:4539, tasks:332|keep order:false|N/A|N/A|

Hacker_cBh8J2wj · 2020 年7 月 16 日 01:24

来了老弟 · 2020 年7 月 16 日 02:27

ok，感谢配合，收到