TiDB深度翻译出现内存溢出问题

 

发现问题

项目的某个功能模块的数据量比较大，在进行翻页（翻页到500多页时）的时候，接口出现“数据访问异常”的错误。

 通过查询应用日志得知真正的错误信息是：

Caused by: java.sql.SQLException: Out Of Memory Quota![conn_id=288730]

意思是执行SQL时，出现了超出内存限制。

分析问题

分析问题之前，我大概说一下数据保存架构。公司目前是将数据进行保存在多个Mysql数据库的分库分表中的，然后由Mysql同步数据到TiDB中，所有的查询全部都走TiDB。有关TiDB有兴趣的可以去查看它的官网文档TiDB 简介 | PingCAP Docs ， 大概的架构如下图：

 

找到具体执行的SQL，发现是单表查询，但是那个表是一个大表，有多达96个字段，在执行的SQL里面有查出86个字段，大概语句如下：

select
    d.id,
    d.createTime
    d.c1,
    d.c2,
    d.c3,
    ...
    d.c84
from
    table_a d
WHERE
    d.c1= 'xxxxx'
    and d.c2 = 1
    and d.c3 = 2
order by
    createTime asc ,
    id asc
LIMIT 5000,10

由于涉及到公司业务，不方便展示真是的SQL，我这里使用c1、c2...来表示字段了，大家能明白这个意思就可以了。

当我拿到真实执行的SQL在数据库的客户端执行时，同样也是提示上面那个错误。

由此可知，这问题的确时出现在TIDB数据库， 于是我查看了TiDB官网文档TiDB 内存控制文档 | PingCAP Docs ，得知TiDB默认单条SQL的内存限制是1GB，然后我查看公司生产环境的配置，发现公司限制的是300M

show config where name like '%mem_quota_query%'

 然后我在当前session设置单条SQL的内存限制为1GB

set @@tidb_mem_quota_query = 1 << 30;

再通过explain analyze来分析执行SQL,发现果然内存是超过300M的，如下图：

 并且内存限制改为1GB时，SQL确实可以正常执行了。

解决问题

通过以上分析，其实解决方案就呼之欲出了，可以有两个解决办法。

1. 通过增加TiDB单条SQL的内存限制
2. 通过改写SQL,让其在执行时不占用那么多内存

方案1治标不治本，当前SQL可以增加内存限制，但不能保证其他的SQL同样不会超过当前设置的内存限制，并且条件不同，占用内存限制就会不同，所以这里选方案2.

为什么上面的SQL会占用到这么大的内存，明明我们只需要拿10条数据出来而已，这里就是因为用到了分页查询和排序，数据库需要先从所有记录中找出前50010条记录，再在内存里面截取最后10条，这里查询的字段太多了，所有导致内存会超过限制，当然主要还是深度翻页了，导致加载的数据越来越多。所以我们可以考虑把SQL拆分为两条SQL，先通过分页获取主键ID,然后把对应的主键的记录查找出来。

## 第一步 先分页条件查询出来id
select id from table_a where c1 = 'xxx'
    and c2 = 1
    and c3 = 2
order by
    createTime asc ,
    id asc
LIMIT 5000 , 10

## 第二步 根据第一步查询出来的id数组来查询素材详情信息
select
    d.id,
    d.createTime
    d.c1,
    d.c2,
    d.c3,
    ...
    d.c84
from
    table_a d
WHERE
    d.id in (...)

这样基本能解决SQL执行的内存超出限制问题，使用explain analyze分析上述两个SQL语句，可知第一条SQL需要的内存不到10MB,第二条SQL只需要几百KB。

但觉得这种方案还是不够完美，因为如果数据量特别多的时候，第一条SQL还是存在超出内存限制的可能，如有更好的办法，欢迎留言评论告诉我，谢谢~