Like 查询优化

erixyuan · 2019 年7 月 31 日 06:30

背景信息

单表数据量1.3亿，进行like 查询的时候，时间很长，急需优化

集群信息

三台 4CPU 8G SSD500G

TiBD1 - PD,TiKV
TiBD2 - PD,TiKV,TiDB
TiBD3 - PD,TiKV,TiDB

TIDB版本

TiDB-SQL 问题

-- 表结构
CREATE TABLE `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `payment_order_id` varchar(64) NOT NULL DEFAULT '' COMMENT '',
  `mechanism_id` varchar(32) NOT NULL DEFAULT '' COMMENT '',
  `merchant_id` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  `merchant_name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  `out_trade_no` varchar(64) NOT NULL DEFAULT '' COMMENT '',
  `merchant_rate` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `merchant_fee` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `payment_channel_name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  `payment_status` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `pay_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payment_order_id` (`payment_order_id`),
  UNIQUE KEY `mch_trade_no` (`merchant_id`,`out_trade_no`),
  KEY `out_trade_no` (`out_trade_no`),
  KEY `creat_time_index` (`create_time`),
  KEY `condition` (`create_time`,`mechanism_id`,`payment_status`,`payment_channel_name`),
  KEY `pay_time_index` (`pay_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=46318751 COMMENT='订单表';

查询语句

select
            sum(od.amount)/100 payAmount,
            count(od.id) payCount,
            sum(od.merchant_fee)/100 fee
        FROM
          order as od 
        where od.`create_time` >= '2019-06-01 00:00:00'
        and od.`create_time` <= '2019-06-29 00:00:00'
        and od.payment_status = 1
        and od.`mechanism_id` = '1234'
        and od.`payment_channel_name` like 'ch1%';

执行计划截图

WX20190731-142849%402x2354×446 88.2 KB

– show stats_healthy 信息
WX20190731-153102%402x

执行时间100秒左右，请问应该怎么优化

小王同学 · 2019 年7 月 31 日 06:43

看执行计划里面有 pseudo，说明这个上面没有统计信息，可以对该表执行一下 analyze，收集一下统计信息

官方文档链接：https://pingcap.com/docs-cn/v3.0/reference/performance/statistics/

erixyuan · 2019 年7 月 31 日 07:34

已上传新的数据

小王同学 · 2019 年7 月 31 日 07:53

1.tidb 集群什么版本？
2.使用 use index 强制走下 condition 看看呢？

erixyuan · 2019 年7 月 31 日 08:03

1、是最新的版本 2、强制condition之后的执行计划，和原来的一模一样，没有变化

小王同学 · 2019 年7 月 31 日 10:01

1.cd /tidb-ansible/resources/bin & ./tidb-server -V 查看具体版本；

2.use index 后的 sql 以及执行计划请提供截图信息；

erixyuan · 2019 年7 月 31 日 10:06

cd /tidb-ansible/resources/bin & ./tidb-server -V 查看具体版本；

版本号：v3.0.1

use index 后的 sql 以及执行计划请提供截图信息；

desc select
            sum(od.amount)/100 payAmount,
            count(od.id) payCount,
            sum(od.merchant_fee)/100 fee
        FROM
          order as od force index (`condition`)
        where od.`create_time` >= '2019-06-01 00:00:00'
        and od.`create_time` <= '2019-06-29 00:00:00'
        and od.payment_status = 1
        and od.`mechanism_id` = '1234'
        and od.`payment_channel_name` like 'ch1%';

Lucien · 2019 年8 月 1 日 02:06

请上传该表详细的统计信息情况

SHOW STATS_HISTOGRAMS  where table_name='order';
show stats_ STATS_BUCKETS where table_name='order';

erixyuan · 2019 年8 月 1 日 02:13

SHOW STATS_HISTOGRAMS where table_name=‘order’;

WX20190801-1007581183×377 68.3 KB
show stats_ STATS_BUCKETS where table_name=‘order’;

WX20190801-101123%402x2176×1128 375 KB

query_result.csv (276.0 KB)

第二张表的数据统计太多了，截图不了全部，只截图了一部分, 我把结果生成了csv发上来

Lucien · 2019 年8 月 1 日 02:56

初步原因

现象看统计信息中没有 amount 和 merchat_fee 列的信息；
当前集群资源 4c8g 混合部署 TiDB，TiKV，在 TiDB 进行 sum 聚合计算时候 Mem 和 CPU 资源使用率可能存在瓶颈；
建议重新收集一次统计信息，如果是升级 3.0 前统计信息也会 Heath 并且存在同样问题，建议删除当前统计信息，重新收集，验证一下：

操作步骤

导出当前统计信息；使用

 curl http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}

删除统计信息；

DROP STATS order;

重新收集统计信息；

-- 设置并发
set @@session.tidb_build_stats_concurrency = 8;
set @@session.tidb_distsql_scan_concurrency = 20;
set @@session.tidb_index_serial_scan_concurrency = 2;
-- 执行收集操作
ANALYZE TABLE order;

再次验证 SQL 执行计划和响应时间情况

explain analyze select
    sum(od.amount)/100 payAmount,
    count(od.id) payCount,
    sum(od.merchant_fee)/100 fee
FROM
  order as od 
where od.`create_time` >= '2019-06-01 00:00:00'
and od.`create_time` <= '2019-06-29 00:00:00'
and od.payment_status = 1
and od.`mechanism_id` = '1234'
and od.`payment_channel_name` like 'ch1%';

然后将之前备份的统计信息和现在的统计信息上传，我们需要再分析一下。

erixyuan · 2019 年8 月 1 日 04:17

按照操作完成了。

以下是现在的统计信息

执行计划的结果

explain analyze select
    sum(od.amount)/100 payAmount,
    count(od.id) payCount,
    sum(od.merchant_fee)/100 fee
FROM
  order as od 
where od.`create_time` >= '2019-06-01 00:00:00'
and od.`create_time` <= '2019-06-29 00:00:00'
and od.payment_status = 1
and od.`mechanism_id` = '1234'
and od.`payment_channel_name` like 'ch1%';

show STATS_BUCKETS where table_name=‘order’;
附件2.csv (233.3 KB)
SHOW STATS_HISTOGRAMS where table_name=‘order’;

31187×362 61.4 KB

以下是之前备份的统计信息

执行计划的结果

WX20190731-180600%402x2396×352 69.6 KB
show STATS_BUCKETS where table_name=‘order’;
query_result.csv (276.0 KB)
SHOW STATS_HISTOGRAMS where table_name=‘order’;

WX20190801-1007581183×377 68.3 KB

CodeLess-PingCAP · 2019 年8 月 1 日 07:24

看执行计划整个消耗在了 sum count（40s）。也就是在 TiDB 层。 4cpu 的机器混合部署 TiDB&PD&TiKV 不是很合理。因为 TiDB （特点 - 偏计算）和 TiKV（特点 - 计算+ IO）。可以看下 CPU 的资源只用情况。

erixyuan · 2019 年8 月 1 日 07:52

执行计划中的时间那一列，貌似不是指他所用的时间吧。是指到这一步所用的时间，例如截图中IndexLookUp_30的时间40.59秒，HassAgg_29的时间是40.63秒，我理解是HassAgg_29指用了40.63-40.59秒。

erixyuan · 2019 年8 月 1 日 08:35

和我跑的结果不太复合哦，按照您的说法，那整个耗时应该是 40.6s + 40.59s了。但是我实际跑sql的情况下是40+s的时候就出结果了。以下是我重新跑的一次截图，麻烦您看看。

执行计划

实际耗时

我的理解

加入execution info 列显示的是执行计划中每一步消耗的时间，那总的sql 的耗时，就是起码3m以上了
但是实际跑的结果是1m，反而和执行计划中的projection_5 是对齐的
多次测试的结果都是这样的结果

CodeLess-PingCAP · 2019 年8 月 1 日 11:22

是的。你理解的是对的。

Lucien · 2019 年8 月 1 日 11:27

索引调整，创建一个复合索引：mechanism_id,payment_status,create_time,payment_channel_name,amount,merchant_fee

减少回表

erixyuan · 2019 年8 月 1 日 11:29

好的，我试试，覆盖索引

erixyuan · 2019 年8 月 2 日 02:10

这边重新建立所以的时候出现了异常

执行如下命令

drop index condition on order ;
alter table order add index `condition` (`create_time`,`mechanism_id`,`payment_status`,`payment_channel_name`, `amount`, `merchant_fee` );

执行重新add的时候，连接忽然中断了，再次执行的时候，出现以下异常

ERROR 1061 (42000): index already exist condition

检查表结构，这个索引是还没有在表结构中的，请问应该怎么处理呢

Lucien · 2019 年8 月 2 日 02:15

ADMIN 操作文档链接

检查一下 DDL 情况

-- 检查一下当前 DDL 操作具体状态
ADMIN SHOW DDL JOBS;

看一下 TiDB Server 的 log 有没有异常的报错。

erixyuan · 2019 年8 月 2 日 02:18

看到了，新增索引的命令是在后台执行的？