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 |
±-----------------------------------±-----------±-----±------------------------------------------------------------------------------------------------------------------±------------------------------------------------------------------------------------------±----------------------+

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

图可以看到,我看下。