[MySQL] 分页优化

时间:2024-09-28 19:07:02

  在传统的分页思路影响下,很多人都形成了对于分页的固定理解,也就是给出select语句,先用count()函数计算出总的条目,除与每个页面大小pagesize,然后用ceil取整,得出总的页数,用limit去查询每一页的结果。

这种做法的确带来了很多好处,它可以让前端马上知道查询的总记录数,并且马上可以计算出页码和总页数。事实上这样的做法在表的记录较少的情况下尚可,一旦行数超过一定的数量,查询性能会下降得非常厉害,原因在于count()和limit的查询方式。

  count()的作用是统计表的记录数,理想情况下的count()并不慢,例如select count(*) from `table`这样的查询是非常快的,因为没有任何查询条件的count() mysql引擎会直接调用表明细的总记录值,无需直接地对表进行查询,但是一旦带上条件,例如select count(*) from `table` where `id` <> 1,哪怕是提供很小的条件,也会改变mysql对count()的查询机制,这时引擎无法确定哪些记录是小于1,哪些记录是1,只能进行全表扫描,这是一个很耗时的过程,即使语句触发了索引,在百万级别的表上都会耗时超过0.2秒,最糟糕的情况是如果没有触发索引,将会达到2-3秒的查询耗时。

  优化count(),

  第一种,自然是最简便的尽量在设计数据库的时候避免带条件的count()语句。

  第二种,根据主键id去减少全表扫描的行,例如某表有100000条记录,select count(*) from `table` where `id` > 90000 and `type` = 2这样的语句只会从第90000条记录开始扫描后面剩下的10000条记录,速度会快上很多,同理,select count(*) from `table` where `id` < 10000 and `type` = 2语句只会扫描前面10000的记录.

  优化limit,

  第一种,用between and 代替limit

  第二种,取消offset值,像select count(*) from `table` limit 12000,10这样的语句可以用select count(*) from `table` where `id`>12000 limit 10去代替。