MySQL 单表上亿，怎么优化分页查询？

我 Java 开发三年多经验的时候，不知咋地，有次被猎头安排到一家大数据公司去面试了。

面试官问了我一道题：MySQL 单表上亿，怎么优化分页查询？

当时回答这题，我仅仅说了分表分库，其他方面说的都是支支吾吾。现在感觉，至今也无法答好这题，但多少有了点想法。希望我说完答案，你可以帮我指出错误，谈谈你的想法。

1、表容量的问题

首先，MySQL 不管怎么优化也是很难支持单表一亿数据量带查询条件的分页查询，需要提前考虑分表分库。单表设计以 200-500 万为宜；优化的好，单表数据到一两千万，性能也还行。

2、总页数的问题

页面不需要显示总页数，仅显示附近的页码，这样可以避免单表总行数的查询。

需要显示总页数，这种情况就比较难处理一些。首先 MySQL 的 MyISAM 引擎把一个表的总行数记录在磁盘中，查询 count(*) 可以直接返回；InnoDB 引擎是一行行读出来累加计数，大数据量时性能堪忧，大几秒甚至几十秒都有可能（我相信你一定遇到过）。所以 MyISAM 的总行数查询速度是比 InnoDB 快的，但这个快也仅限于不带 where 条件的。MyISAM 还有一个硬伤，不支持事务。

如何既支持事务又快速的查出总数呢？

使用 InnoDB 引擎，新建一张表记录业务表的总数，新增、删除各自在同一事务中增减总行数然后查询，保证事务的一致性和隔离性。当然，这里更新总行数要借助分布式锁或 CAS 方式更新记录总数的表。

3、具体的 SQL 优化

新增表记录业务表的总数，也是无法彻底解决带查询条件的总行数查询慢的问题。这里只能借助具体的 SQL 优化。

不带条件 + 自增 id 字段连续

这种理想情况就不讨论了，通过 pageNo 和 pageSize 算出 id 的起始与结束值

where id >= ? and id < ?where id betweenwhere id >= ? limit 10

就可以直接搞定了。

主键 id + 带查询条件

这种就是我们最需要解决的情况。使用 limit 分页，有个查询耗时与起始记录的位置成正比的问题，所以不能直接使用。

可以这样根据主键进行关联查询

select * from table t1join (select id from table where condition limit 10) t2on t1.id = t2.id order by t1.id asc

其中 condition 是包含索引的查询条件，使用 id 字段进行具体信息的关联回查。当然查询条件 condition 中索引是否生效对性能影响也很大。

索引没有生效的一些情况：

组合索引的「最左前缀」原则
or 的使用可能导致索引未生效，可使用 union all 替代
like 查询以 % 开头
对 null 值判断
使用 != 或 <> 操作符
索引列上使用计算、函数

4、其他解法

继续优化数据库配置
提升数据库服务器硬件性能
引入大数据组件
引入大型商业数据库或者非关系型数据库解决大表问题

更多相关文章

随机推荐