为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
- 【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条数据
若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出打印结果,请务必全选并复制粘贴上传。