Like 查询优化

背景信息

单表数据量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%';

– show stats_healthy 信息 WX20190731-153102%402x

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

1赞

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

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

1赞

已上传新的数据

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

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

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

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

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%';
        

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

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

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

初步原因

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

操作步骤

  1. 导出当前统计信息;使用
 curl http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}
  1. 删除统计信息;
DROP STATS order;
  1. 重新收集统计信息;
-- 设置并发
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;
  1. 再次验证 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%';
  1. 然后将之前备份的统计信息和现在的统计信息上传,我们需要再分析一下。
2赞

按照操作完成了。

以下是现在的统计信息

  • 执行计划的结果
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’;

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

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

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

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

执行计划

实际耗时

我的理解

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

是的。你理解的是对的。

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

减少回表

好的,我试试,覆盖索引

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

执行如下命令

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

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

ADMIN 操作文档链接

  1. 检查一下 DDL 情况
-- 检查一下当前 DDL 操作具体状态
ADMIN SHOW DDL JOBS;
  1. 看一下 TiDB Server 的 log 有没有异常的报错。

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