Inner join查询慢

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

  • 【TiDB 版本】:v3.0.5
  • 【问题描述】:inner join查询很慢,mysql只要40秒 sql: SELECT sum(ol.total_price) AS totalCommodityPrice FROM t_tj_order o INNER JOIN t_tj_order_list ol ON o.id = ol.order_id INNER JOIN t_store s ON o.store_id=s.id INNER JOIN t_commodity c ON ol.commodity_id=c.id WHERE o.order_time BETWEEN ‘2019-04-01 00:00:00’ AND ‘2019-04-30 00:00:00’ AND c.commodity_factory_id = 978600912392220034 AND o.order_status = 0 执行计划如下:

±-----------------------------------±-----------±-----±------------------------------------------------------------------------------------------------------------------+ | id | count | task | operator info | ±-----------------------------------±-----------±-----±------------------------------------------------------------------------------------------------------------------+ | StreamAgg_19 | 1.00 | root | funcs:sum(test.ol.total_price) | | └─HashLeftJoin_108 | 51138.61 | root | inner join, inner:IndexReader_100, equal:[eq(test.o.store_id, test.s.id)] | | ├─HashLeftJoin_36 | 51138.61 | root | inner join, inner:IndexLookUp_89, equal:[eq(test.ol.order_id, test.o.id)] | | │ ├─IndexJoin_60 | 4575868.23 | root | inner join, inner:IndexLookUp_59, outer key:test.c.id, inner key:test.ol.commodity_id | | │ │ ├─TableReader_76 | 881.00 | root | data:Selection_75 | | │ │ │ └─Selection_75 | 881.00 | cop | eq(test.c.commodity_factory_id, 978600912392220034) | | │ │ │ └─TableScan_74 | 44622.00 | cop | table:c, range:[0,+inf], keep order:false | | │ │ └─IndexLookUp_59 | 1.00 | root | | | │ │ ├─Selection_58 | 1.00 | cop | not(isnull(test.ol.commodity_id)) | | │ │ │ └─IndexScan_56 | 1.00 | cop | table:ol, index:commodity_id, order_id, range: decided by [eq(test.ol.commodity_id, test.c.id)], keep order:false | | │ │ └─TableScan_57 | 1.00 | cop | table:t_tj_order_list, keep order:false, stats:pseudo | | │ └─IndexLookUp_89 | 51138.61 | root | | | │ ├─IndexScan_86 | 51245.50 | cop | table:o, index:order_time, range:[2019-04-01,2019-04-30], keep order:false | | │ └─Selection_88 | 51138.61 | cop | eq(test.o.order_status, 0), not(isnull(test.o.store_id)) | | │ └─TableScan_87 | 51245.50 | cop | table:t_tj_order, keep order:false | | └─IndexReader_100 | 29708.00 | root | index:IndexScan_99 | | └─IndexScan_99 | 29708.00 | cop | table:s, index:store_code, range:[NULL,+inf], keep order:false | ±-----------------------------------±-----------±-----±------------------------------------------------------------------------------------------------------------------+

相关表结构如下 CREATE TABLE t_tj_order ( id bigint(32) NOT NULL AUTO_INCREMENT COMMENT ‘主键id’, order_code varchar(20) DEFAULT NULL COMMENT ‘订单号’, store_id bigint(32) DEFAULT NULL COMMENT ‘商铺id’, order_time date DEFAULT NULL COMMENT ‘订单时间’, mode_type int(11) DEFAULT ‘0’ COMMENT ‘订单类型:(0:普通订单,1:补货单)’, order_type int(32) DEFAULT NULL COMMENT ‘订单类型(1=PC下单,2=APP下单, 10-门店订货下单)’, print_num int(11) DEFAULT NULL COMMENT ‘打印份数’, print_type int(11) DEFAULT NULL COMMENT ’ ', total_amount decimal(15,2) DEFAULT ‘0.00’ COMMENT ’ ', final_amount decimal(15,2) DEFAULT ‘0.00’ COMMENT ’ ', order_amount decimal(19,2) DEFAULT NULL COMMENT ’ ‘, order_remark varchar(1000) DEFAULT NULL COMMENT ‘备注’, create_time datetime DEFAULT NULL COMMENT ‘创建日期’, update_id bigint(32) DEFAULT NULL COMMENT ‘更新者’, update_time datetime DEFAULT NULL COMMENT ‘更新时间’, order_status int(2) DEFAULT NULL COMMENT ‘订单状态(0正常,1删除,2取消)’, create_id bigint(32) DEFAULT NULL COMMENT ‘创建者’, settle_status int(11) DEFAULT ‘0’ COMMENT ’ 0:正常 1:未结算’, delivery_batch varchar(2) DEFAULT ‘0’ COMMENT ‘0-无需批次配送, 1-1配,2-2配,3-3配,9-临时批次’, change_price_status tinyint(2) DEFAULT ‘0’ COMMENT ‘是否可变价’, PRIMARY KEY (id), UNIQUE KEY idx_order_code (order_code), KEY idx_t_order_otime (order_time), KEY idx_t_order_sid_otime (store_id,order_time), KEY idx_t_store_sid (store_id), KEY idx_create_id (create_id) USING BTREE, KEY idx_create_time (create_time) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=38532530 DEFAULT CHARSET=utf8

CREATE TABLE t_tj_order_list ( id bigint(20) NOT NULL, order_id bigint(20) NOT NULL, commodity_id bigint(20) DEFAULT NULL, commodity_num decimal(19,2) DEFAULT ‘0.00’, total_price decimal(15,4) DEFAULT ‘0.0000’, commodity_price decimal(14,2) DEFAULT ‘0.00’, type smallint(6) DEFAULT ‘1’ COMMENT ‘1-订单商品,2-赠品,3-配货商品’, remark varchar(1000) DEFAULT NULL, PRIMARY KEY (id), KEY order_id_idx (order_id), KEY idx_t_order_list_gift_cid_oid (commodity_id,order_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE t_store ( id bigint(20) unsigned NOT NULL COMMENT ‘客户ID’, store_code varchar(20) NOT NULL COMMENT ‘客户编号’, store_name varchar(128) NOT NULL COMMENT ‘客户名称’, store_short_name varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT ’ ', store_type_id bigint(20) unsigned NOT NULL COMMENT ’ ', store_type_name varchar(16) DEFAULT NULL COMMENT ‘客户类型名称’, store_status tinyint(3) NOT NULL COMMENT ’ ', print_delivery_queue int(11) DEFAULT NULL COMMENT ‘打印顺序’, store_channel_id bigint(20) DEFAULT NULL COMMENT ’ ', store_channel_name varchar(50) DEFAULT NULL COMMENT ’ ', PRIMARY KEY (id), UNIQUE KEY store_code (store_code), KEY store_type_id (store_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘主表-宽表’;

CREATE TABLE t_commodity ( id bigint(20) unsigned NOT NULL COMMENT ‘商品ID’, commodity_code varchar(16) NOT NULL COMMENT ‘商品编码’, commodity_code_siss varchar(30) NOT NULL COMMENT ‘同步到思讯pos自编码’, commodity_name varchar(100) NOT NULL COMMENT ‘商品名称’, commodity_spec varchar(128) NOT NULL COMMENT ‘商品规格’, commodity_first_kind_id bigint(20) unsigned NOT NULL COMMENT ‘商品一级分类ID’, commodity_first_kind_name varchar(16) NOT NULL COMMENT ‘商品一级分类名称’, commodity_second_kind_id bigint(20) unsigned NOT NULL COMMENT ‘商品二级分类ID’, commodity_second_kind_name varchar(16) NOT NULL COMMENT ‘商品二级分类名称’, commodity_third_kind_id bigint(20) unsigned NOT NULL COMMENT ‘商品三级分类ID’, commodity_third_kind_name varchar(160) NOT NULL COMMENT ‘商品三级分类名称’, commodity_unit_name varchar(8) NOT NULL COMMENT ‘商品计量单位’, commodity_factory_id bigint(20) unsigned DEFAULT NULL COMMENT ‘商品工厂ID(t_factory表主键)’, commodity_factory_name varchar(16) DEFAULT NULL COMMENT ‘商品工厂名称’, commodity_factory_code varchar(16) DEFAULT NULL COMMENT ‘商品工厂编码(t_factory表factory_code)’, origin varchar(100) DEFAULT NULL COMMENT ‘产地’, update_time datetime DEFAULT NULL COMMENT ‘更新时间’, PRIMARY KEY (id), UNIQUE KEY commodity_code (commodity_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘子表-宽表’;

数据量如下: t_tj_order表 6242601条数据
t_tj_order_list表 96019000条数据 t_store表 29708条数据 t_commodity表 44622条数据

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出打印结果,请务必全选并复制粘贴上传。

日志内容可否格式化输出一下。另外,这条 SQL 在 TiDB 耗时是多少? 用 explain analyze 执行一下。

这个是昨晚执行的,请问一下你说的日志内容格式化是指执行计划吗

贴上来的日志格式不便于肉眼观察。explain analyze 和 explain 不一样。执行一下 explain analyze .

±-----------------------------------±-----------±-----±------------------------------------------------------------------------------------------------------------------±------------------------------------------------------------------------------------------±----------------------+ | id | count | task | operator info | execution info | memory | ±-----------------------------------±-----------±-----±------------------------------------------------------------------------------------------------------------------±------------------------------------------------------------------------------------------±----------------------+ | StreamAgg_19 | 1.00 | root | funcs:sum(test.ol.total_price) | time:35m30.126881273s, loops:2, rows:1 | N/A | | └─HashLeftJoin_108 | 295851.77 | root | inner join, inner:IndexReader_100, equal:[eq(test.o.store_id, test.s.id)] | time:35m30.058782242s, loops:945, rows:965280 | 247.265625 KB | | ├─HashLeftJoin_36 | 295851.77 | root | inner join, inner:IndexLookUp_89, equal:[eq(test.ol.order_id, test.o.id)] | time:35m30.103744416s, loops:946, rows:965280 | 11.964836120605469 MB | | │ ├─IndexJoin_60 | 4575868.23 | root | inner join, inner:IndexLookUp_59, outer key:test.c.id, inner key:test.ol.commodity_id | time:35m28.199946333s, loops:24059, rows:24634715 | 1.3595721535384655 GB | | │ │ ├─TableReader_76 | 881.00 | root | data:Selection_75 | time:77.362478ms, loops:2, rows:902 | 16.689453125 KB | | │ │ │ └─Selection_75 | 881.00 | cop | eq(test.c.commodity_factory_id, 978600912392220034) | time:75ms, loops:48, rows:902 | N/A | | │ │ │ └─TableScan_74 | 44622.00 | cop | table:c, range:[0,+inf], keep order:false | time:74ms, loops:48, rows:44622 | N/A | | │ │ └─IndexLookUp_59 | 1.00 | root | | time:35m20.109071844s, loops:24059, rows:24634715 | 149.8904037475586 MB | | │ │ ├─Selection_58 | 1.00 | cop | not(isnull(test.ol.commodity_id)) | proc max:5.681s, min:0s, p80:870ms, p95:2.959s, rows:24634715, iters:24458, tasks:96 | N/A | | │ │ │ └─IndexScan_56 | 1.00 | cop | table:ol, index:commodity_id, order_id, range: decided by [eq(test.ol.commodity_id, test.c.id)], keep order:false | proc max:5.665s, min:0s, p80:833ms, p95:2.946s, rows:24634715, iters:24458, tasks:96 | N/A | | │ │ └─TableScan_57 | 1.00 | cop | table:t_tj_order_list, keep order:false, stats:pseudo | proc max:11.024s, min:0s, p80:2.184s, p95:3.271s, rows:24634715, iters:78630, tasks:15663 | N/A | | │ └─IndexLookUp_89 | 295851.77 | root | | time:6.815118994s, loops:300, rows:305336 | 20.100608825683594 MB | | │ ├─IndexScan_86 | 296470.18 | cop | table:o, index:order_time, range:[2019-04-01,2019-04-30], keep order:false | time:671ms, loops:303, rows:306027 | N/A | | │ └─Selection_88 | 295851.77 | cop | eq(test.o.order_status, 0), not(isnull(test.o.store_id)) | proc max:1.799s, min:0s, p80:606ms, p95:1.106s, rows:305336, iters:411, tasks:36 | N/A | | │ └─TableScan_87 | 296470.18 | cop | table:t_tj_order, keep order:false | proc max:1.66s, min:0s, p80:603ms, p95:1.105s, rows:306027, iters:411, tasks:36 | N/A | | └─IndexReader_100 | 29708.00 | root | index:IndexScan_99 | time:51.050821ms, loops:31, rows:29708 | 262.1279296875 KB | | └─IndexScan_99 | 29708.00 | cop | table:s, index:store_code, range:[NULL,+inf], keep order:false | time:42ms, loops:34, rows:29708 | N/A | ±-----------------------------------±-----------±-----±------------------------------------------------------------------------------------------------------------------±------------------------------------------------------------------------------------------±----------------------+

贴了文本,再贴个图,不晓得您能不能看到

图可以看到,我看下。