做一个招聘信息的网站 用什么做网站的软件,wordpress 投稿 图片大小,苏州做学校网站的,wordpress 文章点赞插件当我们对一张数据表中的记录进行统计的时候#xff0c;习惯都会使用 count 函数来统计#xff0c;但是 count 函数传入的参数有很多种#xff0c;比如 count(1)、count(*)、count(字段) 等。
到底哪种效率是最好的呢#xff1f;是不是 count(*) 效率最差#xff1f;
我曾…当我们对一张数据表中的记录进行统计的时候习惯都会使用 count 函数来统计但是 count 函数传入的参数有很多种比如 count(1)、count(*)、count(字段) 等。
到底哪种效率是最好的呢是不是 count(*) 效率最差
我曾经以为 count(*) 是效率最差的因为认知上 selete * from t 会读取所有表中的字段所以凡事带有 * 字符的就觉得会读取表中所有的字段当时网上有很多博客也这么说。
但是当我深入 count 函数的原理后被啪啪啪的打脸了
不多说 发车 哪种 count 性能最好 哪种 count 性能最好 我先直接说结论 要弄明白这个我们得要深入 count 的原理以下内容基于常用的 innodb 存储引擎来说明。 count() 是什么 count() 是一个聚合函数函数的参数不仅可以是字段名也可以是其他任意表达式该函数作用是统计符合查询条件的记录中函数指定的参数不为 NULL 的记录有多少个。
假设 count() 函数的参数是字段名如下
select count(name) from t_order;这条语句是统计「 t_order 表中name 字段不为 NULL 的记录」有多少个。也就是说如果某一条记录中的 name 字段的值为 NULL则就不会被统计进去。
再来假设 count() 函数的参数是数字 1 这个表达式如下
select count(1) from t_order;这条语句是统计「 t_order 表中1 这个表达式不为 NULL 的记录」有多少个。
1 这个表达式就是单纯数字它永远都不是 NULL所以上面这条语句其实是在统计 t_order 表中有多少个记录。 count(主键字段) 执行过程是怎样的 在通过 count 函数统计有多少个记录时MySQL 的 server 层会维护一个名叫 count 的变量。
server 层会循环向 InnoDB 读取一条记录如果 count 函数指定的参数不为 NULL那么就会将变量 count 加 1直到符合查询的全部记录被读完就退出循环。最后将 count 变量的值发送给客户端。
InnoDB 是通过 B 树来保持记录的根据索引的类型又分为聚簇索引和二级索引它们区别在于聚簇索引的叶子节点存放的是实际数据而二级索引的叶子节点存放的是主键值而不是实际数据。
用下面这条语句作为例子
//id 为主键值
select count(id) from t_order;如果表里只有主键索引没有二级索引时那么InnoDB 循环遍历聚簇索引将读取到的记录返回给 server 层然后读取记录中的 id 值就会 id 值判断是否为 NULL如果不为 NULL就将 count 变量加 1。 但是如果表里有二级索引时InnoDB 循环遍历的对象就不是聚簇索引而是二级索引。 这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间所以二级索引树比聚簇索引树小这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小因此「优化器」优先选择的是二级索引。 count(1) 执行过程是怎样的 用下面这条语句作为例子
select count(1) from t_order;如果表里只有主键索引没有二级索引时。 那么InnoDB 循环遍历聚簇索引主键索引将读取到的记录返回给 server 层但是不会读取记录中的任何字段的值因为 count 函数的参数是 1不是字段所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL因此 server 层每从 InnoDB 读取到一条记录就将 count 变量加 1。
可以看到count(1) 相比 count(主键字段) 少一个步骤就是不需要读取记录中的字段值所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。
但是如果表里有二级索引时InnoDB 循环遍历的对象就二级索引了。 count(*) 执行过程是怎样的 看到 * 这个字符的时候是不是大家觉得是读取记录中的所有字段值
对于 selete * 这条语句来说是这个意思但是在 count(*) 中并不是这个意思。
count(\*) 其实等于 count(0)也就是说当你使用 count(*) 时MySQL 会将 * 参数转化为参数 0 来处理。 所以count(*) 执行过程跟 count(1) 执行过程基本一样的性能没有什么差异。
在 MySQL 5.7 的官方手册中有这么一句话
InnoDB handles SELECT COUNT(\*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
翻译InnoDB以相同的方式处理SELECT COUNT\*和SELECT COUNT1操作没有性能差异。
而且 MySQL 会对 count(*) 和 count(1) 有个优化如果有多个二级索引的时候优化器会使用key_len 最小的二级索引进行扫描。
只有当没有二级索引的时候才会采用主键索引来进行统计。 count(字段) 执行过程是怎样的 count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的。
用下面这条语句作为例子
//name不是索引普通字段
select count(name) from t_order;对于这个查询来说会采用全表扫描的方式来计数所以它的执行效率是比较差的。 小结 count(1)、 count(*)、 count(主键字段)在执行的时候如果表里存在二级索引优化器就会选择二级索引进行扫描。
所以如果要执行 count(1)、 count(*)、 count(主键字段) 时尽量在数据表上建立二级索引这样优化器会自动采用 key_len 最小的二级索引进行扫描相比于扫描主键索引效率会高一些。
再来就是不要使用 count(字段) 来统计记录个数因为它的效率是最差的会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数建议给这个字段建立一个二级索引。
为什么要通过遍历的方式来计数
你可以会好奇为什么 count 函数需要通过遍历的方式来统计记录个数
我前面将的案例都是基于 Innodb 存储引擎来说明的但是在 MyISAM 存储引擎里执行 count 函数的方式是不一样的通常在没有任何查询条件下的 count(*)MyISAM 的查询速度要明显快于 InnoDB。
使用 MyISAM 引擎时执行 count 函数只需要 O(1 )复杂度这是因为每张 MyISAM 的数据表都有一个 meta 信息有存储了row_count值由表级锁保证一致性所以直接读取 row_count 值就是 count 函数的执行结果。
而 InnoDB 存储引擎是支持事务的同一个时刻的多个查询由于多版本并发控制MVCC的原因InnoDB 表“应该返回多少行”也是不确定的所以无法像 MyISAM一样只维护一个 row_count 变量。
举个例子假设表 t_order 有 100 条记录现在有两个会话并行以下语句 在会话 A 和会话 B的最后一个时刻同时查表 t_order 的记录总个数可以发现显示的结果是不一样的。所以在使用 InnoDB 存储引擎时就需要扫描表来统计具体的记录。
而当带上 where 条件语句之后MyISAM 跟 InnoDB 就没有区别了它们都需要扫描表来进行记录个数的统计。
如何优化 count(*)
如果对一张大表经常用 count(*) 来做统计其实是很不好的。
比如下面我这个案例表 t_order 共有 1200 万条记录我也创建了二级索引但是执行一次 select count(*) from t_order 要花费差不多 5 秒 面对大表的记录统计我们有没有什么其他更好的办法呢
*第一种近似值*
如果你的业务对于统计个数不需要很精确比如搜索引擎在搜索关键词的时候给出的搜索结果条数是一个大概值。 这时我们就可以使用 show table status 或者 explain 命令来表进行估算。
执行 explain 命令效率是很高的因为它并不会真正的去查询下图中的 rows 字段值就是 explain 命令对表 t_order 记录的估算值。 第二种额外表保存计数值
如果是想精确的获取表的记录总数我们可以将这个计数值保存到单独的一张计数表中。
当我们在数据表插入一条记录的同时将计数表中的计数字段 1。也就是说在新增和删除操作时我们需要额外维护这个计数表。
