TD超级表查询比mysql慢

开源版问题专区
1

TD超级表有138万条数据，分页查询从10000条开始的10条数据，耗时700-800毫秒；mysql表中有100万条数据，分页查询从10000条开始的10条数据，耗时80毫秒左右，2个数据库表结构相同，为什么TD表查询比mysql慢这么多，怎么优化？

查询语句：

SELECT * FROM table LIMIT 10000, 10;

TD表结构：
CREATE STABLE IF NOT EXISTS devops.ops_compress_task (
ts TIMESTAMP,
task_no VARCHAR(32) COMPOSITE KEY,
video_name VARCHAR(255),
video_path VARCHAR(255),
video_size BIGINT,
video_width SMALLINT,
video_height SMALLINT,
compress_path VARCHAR(255),
compress_width INT,
compress_height INT,
compress_size BIGINT,
compress_rate FLOAT,
start_time TIMESTAMP,
end_time TIMESTAMP,
task_progress FLOAT,
task_status TINYINT,
upload_status TINYINT,
fail_reason VARCHAR(2048)
)
TAGS (
plan_id SMALLINT
);

mysql 表结构：

CREATE TABLE ops_compress_task (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
plan_id bigint(20) UNSIGNED NULL DEFAULT 0 COMMENT ‘计划ID’,
file_no varchar(128) CHARACTER SET utf8 COLLATE utf8_esperanto_ci NULL DEFAULT NULL COMMENT ‘文件ID’,
task_no char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘任务ID’,
video_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘视频名称’,
video_path varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ‘原视频文件路径’,
video_duration int(1) UNSIGNED NULL DEFAULT 0 COMMENT ‘视频时长’,
video_size bigint(20) UNSIGNED NULL DEFAULT 0 COMMENT ‘视频大小’,
video_width int(1) NULL DEFAULT NULL COMMENT ‘视频宽度’,
video_height int(1) NULL DEFAULT NULL COMMENT ‘视频高度’,
video_code varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ‘视频编码’,
video_bitrate decimal(20, 2) NULL DEFAULT NULL COMMENT ‘视频码率’,
start_time datetime(0) NULL DEFAULT NULL COMMENT ‘视频压缩开始时间’,
end_time datetime(0) NULL DEFAULT NULL COMMENT ‘视频压缩完成时间’,
compress_path varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ‘压缩后文件路径’,
compress_code tinyint(16) NULL DEFAULT NULL COMMENT ‘压缩编码类型:0.h264,1.h265’,
compress_iframe smallint(1) NULL DEFAULT 50 COMMENT ‘压缩I帧间隔’,
compress_profile tinyint(1) NULL DEFAULT NULL COMMENT ‘码率控制方式：0.CBR恒定码率，1.VBR可变码率，2.CQP恒定QP’,
compress_bitrate decimal(20, 2) NULL DEFAULT NULL COMMENT ‘压缩码率’,
compress_minqp smallint(1) NULL DEFAULT 0 COMMENT ‘最小Q值’,
compress_maxqp smallint(1) NULL DEFAULT 0 COMMENT ‘最大Q值’,
compress_height int(1) NULL DEFAULT NULL COMMENT ‘视频压缩高度’,
compress_width int(1) NULL DEFAULT NULL COMMENT ‘视频压缩宽度’,
compress_size bigint(20) UNSIGNED NULL DEFAULT 0 COMMENT ‘视频大小’,
compress_rate varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT ‘0.00’ COMMENT ‘最大压缩比’,
task_type tinyint(4) NULL DEFAULT 1 COMMENT ‘任务类型：1.视频；2.图片’,
task_progress decimal(5, 2) NULL DEFAULT NULL COMMENT ‘压缩任务进度’,
task_status tinyint(1) NULL DEFAULT 0 COMMENT ‘压缩任务状态 0 未开始 1 开始压缩 2 压缩完成。3 压缩失败’,
fail_reason varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT ‘’ COMMENT ‘压缩失败原因’,
upload_status tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT ‘回传状态：0.未回传;2.已回传’,
create_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT ‘压缩任务创建时间’,
PRIMARY KEY (id) USING BTREE,
INDEX task_no(task_no) USING BTREE,
INDEX taskStatus(task_status) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = ‘压缩任务’ ROW_FORMAT = Dynamic;

2

由于 MySQL 和 TD 的数据存储结构不同导致的，MySQL 为行存储，而 TD 为处理时序数据场景存储为列存储。
可以从两方面做数据的优化。1. 仅查询需要的字段，不要全部查询。2. 在项目中预查询一批数据，通过缓存加载的方式减少请求耗时。

3

您好，关于您的问题：TDengine 分页查询慢是因为跨 vgroup 的全局归并再丢弃前 10000 行，代价高。
查询优化 ：按标签查询，用上次返回的最后一条的 ts 作为游标
例如：SELECT * FROM meters_stable
WHERE tagA = ?
AND ts < ? – lastTs，从上一页最后一条记录取
ORDER BY ts DESC
LIMIT 10;

4

按上面修改后确实可以提高查询效率，但是还是需要0.5秒。另外，我查第一页10条数据：SELECT * FROM meters_stable limit 10；需要1.29秒? 与mysql查询效率还是相差很远？还有其他优化方法吗？

5

1、避免裸 SELECT * FROM super_table LIMIT N
加时间过滤，约束扫描范围（非常关键）
2、限定子表范围，而不是扫所有子表
– 示例：只查某个 tag 组合对应的子表
SELECT *
FROM meters_stable
WHERE location = ‘beijing’ – 这是 TAG

AND ts >= now - 1h

LIMIT 10;
3、用合适的 schema / tag 设计减少“全表扫”

  1. 按业务维度拆库/拆表
    比如按租户/环境拆 DB，而不是一个 super table 装天下。
  2. 合理利用 TAG 作为过滤条件
    常用的查询维度（城市/设备类型/租户ID）都放到 TAG；
    查询时一定带上 TAG 条件。
6

如果数据量超过几千万，加上tag和ts范围去筛选，我觉得可以理解。关键我现在所有测试数据量实际只有20几万条，取一个前10条数据，查询需要1.29秒不应该这么慢吧？

1 个赞
7

首先请理解 td 的数据查询模式，跟子表数量，字段数量都有关系，如果不去通过时间和tag 过滤，需要“从大量子表、每个表的时间序中去找前 10 行”，再合并不同的列数据，所以建议您在 td 上避免裸 SELECT * FROM super_table LIMIT N 这样的查询。另外 td 是有缓存能力的，再次执行同样的查询时间会有接近指数级的降低。