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 between
where id >= ? limit 10
就可以直接搞定了。
带查询条件 + 主键 id 不连续
这种就是我们最需要解决的情况。使用 limit 分页,有个查询耗时与起始记录的位置成正比的问题,所以不能直接使用。
可以这样根据主键进行关联查询
select * from table t1
join (select id from table where condition limit 10) t2
on t1.id = t2.id
order by t1.id asc
其中 condition 是包含索引的查询条件,使用 id 字段进行具体信息的关联回查。当然查询条件 condition 中索引是否生效对性能影响也很大。
索引没有生效的一些情况:
组合索引的「最左前缀」原则
or 的使用可能导致索引未生效,可使用 union all 替代
like 查询以 % 开头
对 null 值判断
使用 != 或 <> 操作符
索引列上使用计算、函数
4、其他解法
继续优化数据库配置
提升数据库服务器硬件性能
引入大数据组件
引入大型商业数据库或者非关系型数据库解决大表问题
PS:
MySQL 大表分页问题,一般效果比较好的是,使用记录页面最大最小 ID 或统计表优化 count 查询。
从面试回答问题的角度看,如果能结合索引的实现,比如 InnoDB 的索引使用 B+ 树,子查询中索引如何生效与失效,说清楚问题的本质是就是用空间去换取查询时间,把问题提高到计算机原理(I/O、CPU 之间的权衡)、数据结构与算法的层面去阐述,肯定会加分不少。