一次数据库拆表的经验教训

2019-11-21 本文已影响0人 zhglance

一、业务场景

由于线上业务已经单表数据有1亿+的数据量，每天查询量在30万左右，给线上访问超时比较多，因此采取分表操作，分成100张表。

1.JVM内存溢出问题

单表查询代码如下：

statement = conn.prepareStatement(sql);
statement.setFetchSize(1000);
rs = statement.executeQuery();
while (rs.next()) {
  //  数据处理
}

结果出现：java.lang.OutOfMemoryError: Java heap space
原因，MySQL不支持游标功能，setFetchSize(1000)是不生效的。

生效方法：

statement = conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(Integer.MIN_VALUE);
rs = statement.executeQuery();
while (rs.next()) {
  //  数据处理
}

2.MySQL的8小时连接空闲被Server关闭的问题

如果连接空闲操作8小时，那么连接会被Server端断开。

3.插入数据库一定要控制写入量

1.评估数据库的硬盘是SSD硬盘，还是普通硬盘；
普通硬盘：延迟5~20 ms，带宽：50M~200MB/s
SSD硬盘：延迟10 us~1 ms，带宽：50M~2GB/s
2.如果业务允许，可以降低或者禁止其他业务的数据写入。
笔者使用了20个线程导入数据，结果导致数据库服务器IO飙升，导致读取连接长时间没有相应而TimeOut。

4.刷数的表一定要创建主键

4.1.没有主键的表，其数据是无序的保存在磁盘上的；
4.2.有主键的表，整个表的数据变成了一个索引平衡树，其叶子节点是数据库的整行数据，即作为的聚集索引；
4.3.非主键索引（也即是非聚集索引），是创建一个独立的索引树，独立于聚集索引树之外，叶子节点是主键的ID；
4.4.通过主键查询，从聚集索引的root节点，根据树分支到叶子节点，取到整行数据；5
4.5.通过非聚集索引查询，先根据普通索引树查询到主键的ID，然后根据主键的ID检索聚集索引树，知道叶子节点，即整行数据；
4.6.如果在非聚集索引查询的select的所有字段都是该索引覆盖的字段，那么不需要二次查询聚集索引。

5.分表之后，记得给分表创建索引

分拆后的表一定要创建符合业务的索引，这点容易忘记。