MySQL查询---COUNT函数

上一篇谈到了我们日常开发中经常需要用到的分页，在业务数据量不多的情况下，我们直接用limit指定偏移量就可以满足我们业务需求了，但是数据量大的时候使用limit指定偏移量性能会很低，因为需要全表检索。所以上一篇主要提到了几种可以优化分页的方案，而且分页业务一般都伴随着需要count函数查询总条数，所以本篇文章主要讲讲count函数的一般优化方案。

为什么分页一般要伴随查询数量？举个简单的例子：我们实现一个博客首页，我们按照十篇/页渲染，这样我们服务端必须查询给客户端当前页面10篇文章的数据，并且同时得告诉客户端一共有多少页的数据，所以这个时候的业务需求就变成分页 + count函数查询条数的逻辑了，而是大部分分页往往伴随着需要查询总数量的业务。

首先创建一个表，只有id，name，sex三个字段，使用存储过程随机插入100万条数据：

首先，我们先贴下最基本的count函数语法：

select count(expr) from table;

可以看到count函数实际上需要传入expr，这个expr一般取值有以下三个：

• 列名：会检索对应列值不为NULL的条数。
• *：查询符合条件的行数，和列值是否为NULL无关，返回结果都会返回。
• 常量：查询符合条件的行数，和列值是否为NULL无关，返回结果都会返回。

count(*)，count(id)与count(1)效率对比

实际上对于count(*)和count(1)效率哪个更高，众说纷坛。我们可以先看看效率对比：

可以发现执行速度两条SQL语句是相差无几的，count(1)和count(*)都是查询全表数据行数，可能网上很多言论会说count(*)其实走的就是count(1)查询，所以使用count(1)查询可以节省转换时间，实际上无论count(*)还是count(1)完全一致，都是表示指定非空表达式，所以会查询所有符合条件的行数。为什么我会说这两个语句执行效果是一样的？因为Mysql官方文档写了这么一句话：

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. 
There is no performance difference.

所以说实际上count(*)与count(1)在执行效率上是没有区别的，那既然没有区别我究竟要使用哪一个？我个人建议使用count(*)，因为SQL92标准中制定count(*)为标准统计行数的语法，所以Mysql一直在不断地对count(*)做一定的优化。那有人说count(id)效率会不会更高？因为可以走主键索引，走索引查询效率不是应该更高？但是事实上count(id)查询效率会比count(*)更慢。我们可以测试下：

可能有人会纳闷我们添加索引列目的不就是为了提高查询效率？平时我们检索数据属于范围查询，查询指定的数据，所以走索引可以提高查询效率，但是count(id)选择索引基数大的主键索引肯定效率更低。因为主键索引和数据文件存放在一起，所以通过主键id取条数会检索数据文件，count(id)会检索整张表，然后遍历取到每一行数据的id，然后返回server层对每一行的id，不为空count就 + 1，而count(*)一样全表检索，但是不会取id值，因为在索引树就可以得到结果，所以count(id)需要取到数据再过滤id为null的数据效率方面肯定是慢上不少的。

count(*)优化

count(*)和count(1)没什么差别，但是执行时间都得1S多，而且数据量只是100万条，所以我们肯定需要进行适当的优化。因为count(*)实际上查询会使用最小字段的索引进行优化查询，但是因为目前我们表中只有一个主键索引，刚才也说过count(id)效率比count(*)低，所以默认不使用索引查询，我们可以使用explain测试下：

可以看到查询没有走任何索引，所以效率很低是必然的。而count(*)刚才说过会默认寻找最小字段的索引优化查询，所以我们给表增加一个status字段，弄成tinyint类型，并且添加二级索引，然后测试count(*)执行时间:

可以看到count(*)百万级数据成功被优化到0.32秒。完全符合我们的日常需求。所以说我们在需要取整表行数的时候就可以给表加一个非空的tinyint类型字段，并且添加二级索引，count(*)就会使用这个二级索引，优化查询速度。

count(col)

说完了count(*),我们知道了如果在需要返回全部行数时可以使用count(*),那接下来我们如果需要查询姓名不为空的总行数怎么做呢？我们可以加个where很快实现：

可以看到条数只有50万条，因为另外50万条name值为null所以被忽略了，执行时间1.38秒，这是我测试了多次取的最短时间。所以说百万级数据就需要秒级明显不符合需求，所以我们单字段筛选就可以使用count(字段名)，会自动将字段为空的行剔除掉，我们可以测试下：

可以看到1.1秒可以查询到总条数，但是好像还是不满足我们的需求，一样还是秒级，我们使用explain查看下运行参数：

可以看到因为没有使用到索引，所以导致全表检索会扫描数据文件，现在我们对name字段添加一个索引，然后再测试：

可以看到只需要0.33秒就执行完成，一样使用explain查看下：

很显然，本次查询走索引了，加速查询的原因是什么呢？因为我们在name字段创建了一个二级索引，在无二级索引时，count操作只能全表检索数据。当我们通过二级索引统计总条数，无需扫描数据文件，因为二级索引存储的数据就是name字段的值与主键id值。所以在count(col)时就可以在字段上添加一个二级索引加快检索速率。

count函数指定where条件

这里一样得分两种情况：count(*)和count(col)。

• count(*)：where条件添加索引，就可以使用索引优化查询。例如我们刚才添加的type列表示用户账号是否可用，我们现在需要查询可用账户的总数量就可以这样写：

select count(*) from order_info where type = 0;

可以explain查看下运行参数可以发现确实使用到索引优化查询了：

• count(col)：查询列不为空的总条数并且添加where条件，就需要col添加索引并且where使用col进行条件限制，我们可以先来看下name添加索引但是where使用sex限制条件的情况：

然后我们查询name并且使用name限制条件看看是否可以使用索引优化查询：

可以看到查询的列名和where条件的列明一致就可以走索引查询提高效率了。所以说在数据量大的情况下要查询总条数我们要合理去利用索引优化查询。