从 MySQL 处理大量数据谈起

当需要从数据库查询的表有上万条记录的时候，将会导致查询和插入耗时太长，特别是随着数据量的增加特别明显，这时需要对于数据库查询，有很多种方法和优化的点。

依然是面试官系列，如何在 MySQL 中处理十万条以上数据？本来打算作为数据库笔记的附加题，查资料后发现可以延伸到很多方面。

分页

如果使用的ORM框架是mybatis的话，有开源的分页插件可以使用，如：Mybatis-PageHelper。如果不使用分页插件，那么就需要手动分页了，由于不同的数据库实现分页的SQL语句并不一致，如Mysql使用的是limit关键字，而Oracle使用的是rownum。首先讲讲分页操作必须满足的几个要求：一个是有序性，一个是不重复。

普通分页

数据分页在网页中十分多见，分页一般都是limit start,offset,然后根据页码page计算start。即，limit后面的第一个参数表示下标，也就是从第10000行记录开始取，第二个参数表示总共取10行记录。

1

select * from user limit 1,20

这种分页在几十万的时候分页效率就会比较低了，MySQL需要从头开始一直往后计算，这样大大影响效率。

1
2
3


SELECT * from user  limit 100001,20;  -- time 0.151s

explain SELECT * from user  limit 100001,20;

我们可以用explain分析下语句，没有用到任何索引，MySQL执行的行数是16W+。

优化分页

使用主键索引来优化数据分页

1

select * from user where id>(select id from user where id>=100000 limit 1) limit 20;  -- time 0.003s

比如我们要取的是从第10000行开始的10行记录，那么我们可以先把大于或等于10000行的数据查出来并排序，然后再取出前10行记录，这样也可以完成分页。使用这种方式，当在相邻的两页查询之间插入数据时，分页查询结果不会出现重复。

1

explain select * from user where id>(select id from user where id>=100000 limit 1) limit 20;

在数据量比较大的时候，我们尽量去利用索引来优化语句。上面的优化方法如果id不是主键索引，查询效率比第一种还要低点。我们可以先使用explain来分析语句，查看语句的执行顺序和执行性能。

假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：

1

select * from orders where type=2 and id between 1000000 and 1000100 limit 100;

一般情况下，在数据库中建立表的时候，强制为每一张表添加 id 递增字段，这样方便查询。如果像是订单库等数据量非常庞大，一般会进行分库分表。先使用范围查询定位 id （或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。

分页方案比较

MySQL分页的2种常见的方案：

第一种是基于limit的分页方案，如：

1

SELECT * FROM `user` ORDER BY id ASC LIMIT 100, 10;

第二种是基于where的分页方案，如：

1

SELECT * FROM `user` WHERE id > 100 ORDER BY id ASC LIMIT 10;

如果表记录数比较多，不建议使用基于limit的分页方案，而要使用基于where的分页方案。

使用后一种方案，当在相邻的两页查询之间插入数据时，分页查询结果不会出现重复。因为虽然插入记录后，分页的结构变了，但是由于我们现在的分页查询是从固定的id开始查的，所以插入新的数据对后面的分页结果没有影响。当然，这种分页查询也是有限制的，只适用于用来排序的列具有唯一性的情况。

同时，我们还要确保MySQL没有开启查询缓存，否则对于同一个SQL的多次查询有可能会命中缓存，这样一来实验就没有意义了。要确认MySQL有没有开启查询缓存，只需要查询下query_cache_type参数就行了。

分页offset过大带来的问题

1

SELECT b FROM t WHERE c<1000 LIMIT 2000000, 500

可以看到这个一个分页查询，从位置2000000处开始，取500条数据。mysql分页查询会并不是直接跳过前2000000再取出500条数据，而是把前2000000条和后面的500条都取出来，再把前2000000条抛弃，这样的话，上面的慢查询相当于从表中取2000500条数据，这么大的数据量必然会慢。

解决方案：

1

SELECT b FROM (SELECT a FROM t WHERE c<1000 LIMIT 2000000, 500) ta INNER JOIN t tb ON ta.a = tb.a

这种方式先用一个子查询表的主键（还是和原来一样带有过大分页），结果做为一个临时表，再和原来的t表JOIN，查出需要的字段。

分表分库

分表就是将一张大表数据通过某种路由算法将数据尽可能的均匀分配到 N 张小表中。

首先讨论下什么样的情况下适合分表？根据大佬的经验，当某张表的数据量已经达到千万甚至上亿，同时日增数据量在 2% 以上。当然这些数字并不是绝对的，最重要的还是对这张表的写入和查询都已经影响到正常业务执行，比如查询速度明显下降，数据库整体 IO 居高不下等。

范围

首先第一种是按照范围划分，比如我们可以将某张表的创建时间按照日期划分存为月表；也可以将某张表的主键按照范围划分，比如【1~10000】在一张表，【10001~20000】在一张表，以此类推。

这样的分表适合需要对数据做归档处理，比如系统默认只提供近三个月历史数据的查询功能，这样也方便操作；只需要把三月之前的数据单独移走备份保存即可）。

Hash

按照日期这样的范围分表固然简单，但适用范围还是比较窄；毕竟我们大部分的数据查询都不想带上时间。比如某个用户想查询他产生的所有订单信息，这是很常见的需求。于是我们分表的维度就得改改，分表算法可以采用主流的 hash+mod 的组合。

分表规则确定后，更麻烦的是，如何做到对业务影响最小的数据迁移。同时分表之后还需要兼容其他业务；比如原有的报表业务、分页查询等。分表完成后可以解决单表的压力，但数据库本身的压力却没有下降。将这类数据量巨大但对业务不太影响的表单独迁到一个库后，数据库的整体 IO 下降明显，业务也恢复正常。

参考文章

「前端进阶」高性能渲染十万条数据(时间分片) （强烈推荐）

前端如何处理十万级别的大量数据（web worker）

一次难得的分库分表实践

如何优雅地实现分页查询

MySQL分页offset过大性能问题与优化

MySQL快速插入大量数据