MySQL分页中的那些事

1.  优化count()查询

count()是一个特殊的函数，有两种用法。其一：count(*)用来统计结果集的行数。其二：count(column)用来统计column列或column表达式的非NULL值的结果数。很多博客都误解count(*)和count(column)的性能，在这里负责任的说一下，count(*)的性能比count(column)的性能好。在MySQL优化器对查询语句进行优化时，如果cloumn列或column表达式不存在非NULL值，那么count(column)将被优化为count(*)，因为它性能更好。

关于count(*)的性能，在InnoDB引擎和MyISAM引擎中表现是不同的。MyISAM引擎会为每个表维护一个总行数的精确值，因此在执行count(*)时实际上是直接从引擎中获取这个值。而InnoDB引擎只维护了总行数的近似值，因此在执行count(*)时需要做全表扫描。尽管做全表扫描，优化器也会找一个性能好的索引进行扫描，因此和在InnoDB引擎上执行count(column)相比，在InnoDB引擎上执行count(*)的性能还是好的。

通常情况下，count(*)都需要扫描大量的行才能获得精确的值，所以优化是很难的。因此，通常在具体的场景下，我们或者使用近似值（explain select count(*)结果中的rows就是近似值），或者建立一个计数器表（之前的博客有说明）。

在做分页的时候，count(*)通常被用来返回记录的总数，但是这真的慢（使用InnoDB引擎）。这个时候可以考虑为数据库表使用一个自增的主键，这样就可以使用max(primary key column)来代替count(*)。当然也可以如上面所说，建立一个计数器表。

2.  优化limit查询

在做分页的时候，不可避免的使用limit查询。但是当偏移量非常大的时候，比如limit 10000,20 ，为了获取20条数据数据库需要扫描10020条记录。这样的代价非常高。要优化这个问题，可以从业务和技术两个角度入手：

对于业务上来说，可以限制用户分页的数量，比如每个用户只能看50页，这样每页10条记录的话也才500条记录。而大多数情况下这是可以接受的，基本上没有用户会去看50页以后的记录。当然，也可以不允许用户进行跳转，而只能一页一页的看，这样就可以利用局部性原理，在后台加一个缓存。

从技术上来说，可以建立一个自增的主键，并且使用主键的where范围查询来代替limit。 比如使用where id >= 10000 and id < 10020来代替limit10000,20。

总之，关于分页过程中的性能问题十分常见，此文中提出的方案也是给大家提供一个思路，有但不只有这些方法。如果有新方案的话，欢迎在评论区讨论。

笔者开设了一个知乎live，详细的介绍的JAVA从入门到精通该如何学，学什么？

提供给想深入学习和提高JAVA能力的同学，欢迎收听https://www.zhihu.com/lives/932192204248682496