为提高效率,提问时请尽量提供详细背景信息,问题描述清晰可优先响应。以下信息点请尽量提供:
- 系统版本 & kernel 版本:centOS7.5
- TiDB 版本:2.1.10
- 磁盘型号:SAS
- 集群节点分布:两台TIDB,三台PD,三台TIKV ,其中两个pd在tidb上
- 数据量 & region 数量 & 副本数:总量五千万 , region:6300 , loead:2200
- 集群 QPS、.999-Duration、读写比例:
- 问题描述(我做了什么): 背景: 使用sql语句,一张六百万的表和2百万的表进行join,同时还有sum运算、GROUP BY。sql语句在tidb执行期间内存直接在十分钟内飙升了20个G,导致服务器宕机,此时sql查询时间为672s,查询slow_query表的慢日志找到对应的sql但是上面显示的内存占用才1.7G,我们配置文件里配置了单条sql占用的内存上限为5G mem-quota-query = 5368709120,但是从现象来看改配置没有阻止此次查询。请问这个内存问题需要怎样的优化。 服务器慢日志信息: ![](file:///C:UsersAdminDocumentsTencent Files857766397ImageC2C`ICFXO05H5T{0}(P]6T]I92.png) sql语句: INSERT INTO ods_product_collect_day ( data_time, cust_merch_id, cust_organ_id, level_code, manu_merch_id, manu_organ_id, manu_assoc_id, manu_assorgan_id, owner_merch_id, owner_organ_id, provid_merch_id, provid_organ_id, sale_merch_id, sale_organ_id, settle_merch_id, settle_organ_id, accept_merch_id, accept_organ_id, finance_merch_id, finance_organ_id, oper_review_merchant, oper_review_organ, prod_prod_code, expenses_item_id, period_code, payment_channel_id, collect_product_number, collect_order_number, collect_order_amount, reduce_product_number, reduce_order_number, reduce_order_amount, collect_merch_fee, collect_custom_fee, collect_bank_fee, collect_other_fee, refund_product_number, refund_order_number, refund_order_amount, refund_merch_fee, refund_merchant_fee, refund_custom_fee, refund_bank_fee, refund_other_fee, pay_order_number, pay_order_amount, pay_merch_fee, pay_custom_fee, pay_bank_fee, pay_other_fee, total_amount, total_amount2, grouping_dimension ) SELECT ooib.pay_date, ooib.cust_merch_id, ooib.cust_organ_id, ulc.level_code, ooib.manu_merch_id, ooib.manu_organ_id, ooib.manu_assoc_id, ooib.manu_assorgan_id, ooib.owner_merch_id, ooib.owner_organ_id, ooib.provid_merch_id, ooib.provid_organ_id, ooib.sale_merch_id, ooib.sale_organ_id, ooib.settle_merch_id, ooib.settle_organ_id, ooib.accept_merch_id, ooib.accept_organ_id, ooib.finance_merch_id, ooib.finance_organ_id, ooib.oper_review_merchant, ooib.oper_review_organ, ooib.prod_prod_code, ooib.setmanag_item_id, ooib.period_code, ooib.payment_channel_id, SUM(IF (ooib.pay_flag = “1”,ooib.quantity,0)) AS collect_product_number, SUM(IF (ooib.pay_flag = “1”,ooib.receipt_quantity,0)) AS collect_order_number, SUM(IF (ooib.pay_flag = “1”,ooib.order_amount, 0)) AS collect_order_amount, SUM(IF (ooib.reduce_amount != 0, ooib.quantity, 0)) AS reduce_product_number, SUM(IF(ooib.reduce_amount = 0 ,0,1)) AS reduce_order_number, SUM(IFNULL(ooib.reduce_amount, 0)) AS reduce_order_amount, SUM(IF (ooib.pay_flag = “1”,ooib.merch_fee, 0 )) AS collect_merch_fee, SUM(IF (ooib.pay_flag = “1”,ooib.custom_fee, 0)) AS collect_custom_fee, SUM(IF (ooib.pay_flag = “1”,ooib.bank_fee, 0)) AS collect_bank_fee, SUM(IF (ooib.pay_flag = “1”,ooib.other_fee, 0)) AS collect_other_fee, SUM(IF (ooib.pay_flag = “3”,ooib.quantity,0)) AS refund_product_number, SUM(IF (ooib.pay_flag = “3”,ooib.quantity,0)) AS refund_order_number, SUM(IF (ooib.pay_flag = “3”,-ooib.refund_actual_amount, 0)) AS refund_order_amount, SUM(IF (ooib.pay_flag = “3”,ooib.merch_fee, 0)) AS refund_merch_fee, SUM(IF (ooib.pay_flag = “3”,ooib.refund_merchant_fee, 0)) AS refund_merchant_fee, SUM(IF (ooib.pay_flag = “3”,ooib.custom_fee, 0)) AS refund_custom_fee, SUM(IF (ooib.pay_flag = “3”,ooib.bank_fee, 0)) AS refund_bank_fee, SUM(IF (ooib.pay_flag = “3”,ooib.other_fee, 0)) AS refund_other_fee, SUM(IF (ooib.pay_flag = “2”,ooib.quantity,0)) AS pay_order_number, SUM(IF (ooib.pay_flag = “2”,ooib.order_amount, 0)) AS pay_order_amount, SUM(IF (ooib.pay_flag = “2”,ooib.merch_fee, 0)) AS pay_merch_fee, SUM(IF (ooib.pay_flag = “2”,ooib.custom_fee, 0)) AS pay_custom_fee, SUM(IF (ooib.pay_flag = “2”,ooib.bank_fee, 0)) AS pay_bank_fee, SUM(IF (ooib.pay_flag = “2”,ooib.other_fee, 0)) AS pay_other_fee, SUM(IFNULL(ooib.reduce_amount , 0 )) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “1”,ooib.merch_fee, 0 ), 0)) + SUM(IF (ooib.pay_flag = “1”,ooib.custom_fee, 0)) + SUM(IF (ooib.pay_flag = “1”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “3”,-ooib.refund_actual_amount, 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “3”,ooib.merch_fee, 0), 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “3”,ooib.refund_merchant_fee, 0), 0)) + SUM(IF (ooib.pay_flag = “3”,ooib.custom_fee, 0)) + SUM(IF (ooib.pay_flag = “3”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.order_amount, 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “2”,ooib.merch_fee, 0), 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.custom_fee, 0)) AS total_amount, SUM(IF (ooib.pay_flag = “2”,ooib.order_amount, 0)) + SUM(IFNULL(ooib.reduce_amount , 0 )) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “1”,ooib.merch_fee, 0 ), 0)) + SUM(IF (ooib.pay_flag = “1”,ooib.custom_fee, 0)) + SUM(IF (ooib.pay_flag = “1”,ooib.other_fee, 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “3”,ooib.merch_fee, 0), 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “3”,ooib.refund_merchant_fee, 0), 0)) + SUM(IF (ooib.pay_flag = “3”,ooib.custom_fee, 0)) + SUM(IF (ooib.pay_flag = “3”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.order_amount, 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “2”,ooib.merch_fee, 0), 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.custom_fee, 0)) AS total_amount2, CONCAT(DATE_FORMAT(ooib.pay_date,"%Y%m%d"), ooib.cust_merch_id, ooib.cust_organ_id, ulc.level_code, ooib.manu_merch_id, ooib.manu_organ_id, ooib.manu_assoc_id, ooib.manu_assorgan_id, ooib.owner_merch_id, ooib.owner_organ_id, ooib.provid_merch_id, ooib.provid_organ_id, ooib.sale_merch_id, ooib.sale_organ_id, ooib.settle_merch_id, ooib.settle_organ_id, ooib.accept_merch_id, ooib.accept_organ_id, ooib.finance_merch_id, ooib.finance_organ_id, ooib.oper_review_merchant, ooib.oper_review_organ, ooib.setmanag_item_id, ooib.period_code, ooib.prod_prod_code, ooib.payment_channel_id) AS grouping_dimension FROM ods_order_item_base_two ooib JOIN user_level_code ulc ON ooib.read_cust_user_id = ulc.id where 1=1 AND ooib.status_id = ‘80’ GROUP BY ooib.pay_date, ooib.cust_merch_id, ooib.cust_organ_id, ulc.level_code, ooib.manu_merch_id, ooib.manu_organ_id, ooib.manu_assoc_id, ooib.manu_assorgan_id, ooib.owner_merch_id, ooib.owner_organ_id, ooib.provid_merch_id, ooib.provid_organ_id, ooib.sale_merch_id, ooib.sale_organ_id, ooib.settle_merch_id, ooib.settle_organ_id, ooib.accept_merch_id, ooib.accept_organ_id, ooib.finance_merch_id, ooib.finance_organ_id, ooib.oper_review_merchant, ooib.oper_review_organ, ooib.setmanag_item_id, ooib.period_code, ooib.prod_prod_code, ooib.payment_channel_id;
表结构信息:
CREATE TABLE ods_order_item_base_two
(
id
int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,
order_id
varchar(32) DEFAULT NULL COMMENT ‘订单表主键:收款来自子项表主键,退款为退款表主键’,
item_no
varchar(32) DEFAULT NULL COMMENT ‘订单子项流水’,
old_item_no
varchar(32) DEFAULT ‘’ COMMENT ‘原订单子项流水(退款才有)’,
trans_no
varchar(32) DEFAULT NULL COMMENT ‘交易流水号 订单号(源于order_header表)’,
pay_time
datetime DEFAULT NULL COMMENT ‘订单支付时间(展示用)’,
pay_date
date DEFAULT ‘0001-01-01’ COMMENT ‘订单支付时间(检索用)’,
account_time
date DEFAULT ‘0001-01-01’ COMMENT ‘支付平台对账日期’,
trade_time
date DEFAULT ‘0001-01-01’ COMMENT ‘支付平台交易日期’,
settle_time
date DEFAULT ‘0001-01-01’ COMMENT ‘结算日期’,
order_no
varchar(32) DEFAULT NULL COMMENT ‘业务流水号’,
expenses_subject_id
int(11) DEFAULT NULL COMMENT ‘科目id’,
setmanag_item_id
int(11) DEFAULT NULL COMMENT ‘项目ID(订单子项表-管理结算信息-项目ID)’,
period_code
varchar(20) DEFAULT NULL COMMENT ‘周期编码’,
manu_merch_id
int(11) DEFAULT NULL COMMENT ‘产品制造方-发布商户id’,
manu_organ_id
int(11) DEFAULT NULL COMMENT ‘产品制造方-发布机构id’,
manu_assoc_id
int(11) DEFAULT NULL COMMENT ‘产品制造方-联营商户id’,
manu_assorgan_id
int(11) DEFAULT NULL COMMENT ‘产品制造方-联营机构id’,
owner_merch_id
int(11) DEFAULT NULL COMMENT ‘产品归属方-商户id’,
owner_organ_id
int(11) DEFAULT NULL COMMENT ‘产品归属方-机构id’,
provid_merch_id
int(11) DEFAULT NULL COMMENT ‘产品提供方-商户id’,
provid_organ_id
int(11) DEFAULT NULL COMMENT ‘产品提供方-机构id’,
sale_merch_id
int(11) DEFAULT NULL COMMENT ‘销售方-商户id’,
sale_organ_id
int(11) DEFAULT NULL COMMENT ‘销售方-机构id’,
settle_merch_id
int(11) DEFAULT NULL COMMENT ‘产品资金结算方-商户id’,
settle_organ_id
int(11) DEFAULT NULL COMMENT ‘产品资金结算方-机构id’,
accept_merch_id
int(11) DEFAULT NULL COMMENT ‘产品业务受理方-商户id’,
accept_organ_id
int(11) DEFAULT NULL COMMENT ‘产品业务受理方-机构id’,
finance_merch_id
int(11) DEFAULT NULL COMMENT ‘当前管理商户-商户id’,
finance_organ_id
int(11) DEFAULT NULL COMMENT ‘当前管理机构-机构id’,
oper_review_merchant
int(11) DEFAULT NULL COMMENT ‘产品审核方-商户id’,
oper_review_organ
int(11) DEFAULT NULL COMMENT ‘产品审核方-机构id’,
prod_prod_code
varchar(50) DEFAULT NULL COMMENT ‘产品信息-产品编号’,
bill_instructions
varchar(500) DEFAULT ‘’ COMMENT ‘缴费说明’,
status_id
tinyint(3) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘订单状态 (收款:1 - ITEM_REVIEW待商户审核;2 - ITEM_NOPAY待支付;10 - ITEM_RUSHING 订单冲正中;80 - ITEM_COMPLETED完成;99 - ITEM_CANCELLED取消;退款:0 - ORDER_REFUNDACCEPTED退款受理中;1 - ORDER_REFUND退款中;80 - ORDER_COMPLETED完成订单;99 - ORDER_CANCELLED取消订单);)’,
order_type
varchar(20) DEFAULT NULL COMMENT ‘业务类型(BILLS_ORDER账单订单;SALES_ORDER销售订单;RECHARGE_ORDER充值订单;TRANSFER_ORDER转账订单)’,
cust_merch_id
int(11) DEFAULT NULL COMMENT ‘客户业务主体-商户id’,
cust_organ_id
int(11) DEFAULT NULL COMMENT ‘机构ID(订单子项表-客户业务主体-人员机构id)’,
read_cust_user_id
int(11) NOT NULL COMMENT ‘真实客户主体’,
merchant_code
int(11) DEFAULT NULL COMMENT ‘客商识别码’,
student_code
int(11) DEFAULT NULL COMMENT ‘学籍识别码’,
pay_flag
char(1) DEFAULT NULL COMMENT ‘收付标识(1-收 2-付 3-退)’,
receipt_quantity
int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘收款订单笔数’,
payment_quantity
int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘代付订单笔数’,
refund_quantity
int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘退款订单笔数’,
quantity
int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘数量(账单、充值缴费类默认1; 商品类购买的数量或退的数量)’,
amount
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘实际支付金额,保留小计(按交易金额比例折算)’,
order_amount
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘下单金额(=数量*折扣价)’,
reduce_amount
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘减免金额。人工调整金额(在下单金额基础上增或减的金额,以符号+/- 表示增减)’,
voucher_amount
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘抵用金额’,
pay_amount
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘实际支付金额’,
refund_actual_amount
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘实际退费金额’,
merch_fee
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘商户承担手续费’,
custom_fee
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘客户承担手续费’,
bank_fee
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘银行承担手续费’,
refund_merchant_fee
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘退还商户的手续费 (仅退费有:返回值,正值 )’,
other_fee
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘其它承担手续费’,
fee_settle_type
char(2) DEFAULT NULL COMMENT ‘手续费结算方式 01-后结算02-预充值(充值账号扣除) 03-实时结算’,
arrival_amount
decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘应到账金额(退款为负)’,
tran_remark
varchar(300) DEFAULT NULL COMMENT ‘交易人-留言’,
tran_postscript
varchar(300) DEFAULT NULL COMMENT ‘交易人-附言/备注’,
change_amount_explain
varchar(300) DEFAULT NULL COMMENT ‘调价说明’,
expenses_account_id
int(11) DEFAULT NULL COMMENT ‘费用账户ID’,
payment_method_id
varchar(20) DEFAULT NULL COMMENT ‘支付方式’,
payment_channel_id
varchar(10) DEFAULT NULL COMMENT ‘支付通道’,
payment_method_type
tinyint(3) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘支付分类 (0 - 线上;1 - 线下;)’,
invoice_header_no
varchar(50) DEFAULT NULL COMMENT ‘开票头流水’,
invoice_type
varchar(1) DEFAULT NULL COMMENT ‘发票类型 1电票 2 纸票’,
invoice_merchant_id
int(11) DEFAULT NULL COMMENT ‘开票商户’,
invoice_mechanism_id
int(11) DEFAULT NULL COMMENT ‘开票机构’,
invoice_status
varchar(1) NOT NULL DEFAULT ‘0’ COMMENT ‘开票状态 0 否 1 开票中 2 已开票 3 退票中’,
invoice_time
datetime DEFAULT NULL COMMENT ‘开票时间’,
non_tax_flag
char(1) DEFAULT NULL COMMENT ‘非税标识’,
non_tax_code
varchar(50) DEFAULT NULL COMMENT ‘非税标识码’,
created_time
datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
last_updated_time
datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’,
PRIMARY KEY (id
),
UNIQUE KEY uk_item_no
(item_no
),
KEY item_order_id
(order_id
),
KEY item_pay_date_index
(pay_date
),
KEY item_account_time_index
(account_time
),
KEY item_manu_merch_id_index
(manu_merch_id
),
KEY item_manu_organ_id_index
(manu_organ_id
),
KEY item_manu_assoc_id_index
(manu_assoc_id
),
KEY item_manu_assorgan_id_index
(manu_assorgan_id
),
KEY item_owner_merch_id_index
(owner_merch_id
),
KEY item_owner_organ_id_index
(owner_organ_id
),
KEY item_provid_merch_id_index
(provid_merch_id
),
KEY item_provid_organ_id_index
(provid_organ_id
),
KEY item_sale_merch_id_index
(sale_merch_id
),
KEY item_sale_organ_id_index
(sale_organ_id
),
KEY item_settle_merch_id_index
(settle_merch_id
),
KEY item_settle_organ_id_index
(settle_organ_id
),
KEY item_accept_merch_id_index
(accept_merch_id
),
KEY item_accept_organ_id_index
(accept_organ_id
),
KEY item_finance_merch_id_index
(finance_merch_id
),
KEY item_finance_organ_id_index
(finance_organ_id
),
KEY item_oper_review_merchant_index
(oper_review_merchant
),
KEY item_oper_review_organ_index
(oper_review_organ
),
KEY item_prod_prod_code_index
(prod_prod_code
),
KEY item_custMerchIdProdProdCode
(cust_merch_id
,prod_prod_code
),
KEY item_settle_time_index
(settle_time
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8580001 COMMENT=‘订单子项基础数据表(由订单系统-订单子项表丰富而来)’
CREATE TABLE user_level_code
(
id
int(11) NOT NULL AUTO_INCREMENT COMMENT ‘user_info_id’,
level_code
varchar(2000) DEFAULT NULL COMMENT ‘人员所在机构,多个机构以逗号隔开’,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3056864 COMMENT=‘人员多机构临时表’;