MySQL：count(1)与count(*)有什么区别，深分页问题

前言在MySQL中使用统计count函数时count(1)与count()有说明区别那个性能更好我下意识的认为count() 效率最差应为他要进行全表扫描。事实却时相反这究竟是为什么count底层函数原理是什么呢?今天这篇文章来帮助大家答疑解惑那种count性能最好结论count是什么count是一个聚合函数函数的参数可以是字段名其他表达式该函数的作用是统计符合查询条件的记录中函数指定的参数不为NULL的记录是多少个假设 count() 函数的参数是字段名如下selectcount(name)fromt_order;这条语句是统计「 t_order 表中name 字段不为 NULL 的记录」有多少个。也就是说如果某一条记录中的 name 字段的值为 NULL则就不会被统计进去。再来假设 count() 函数的参数是数字 1 这个表达式如下selectcount(1)fromt_order;这条语句是统计「 t_order 表中1 这个表达式不为 NULL 的记录」有多少个。1 这个表达式就是单纯数字它永远都不是 NULL所以上面这条语句其实是在统计 t_order 表中有多少个记录count主键字段执行过程是怎么样的在经过count函数之前MySQL的service层会维护一个名叫count的变量。service层会循环像innode读取一条记录如果count函数指定的参数部位null于是将count变量1知道符合查询的全部记录被读完就退出循环。最终将count变量的值发给客户端innodb是通过b树来保存记录的根据索引可以分类成聚簇索引和二级索引。用下面这条语句作为例子//id 为主键值selectcount(id)fromt_order;如果表中没有二级索引只有主键索引。那么innodb循环遍历主键索引将读取到的记录返回给service层让后读取记录中的id值就会用id值来判断是否为null如果不为null将count变量1。但是当表中有二级索引时innodb循环遍历的对象就不是主键索引而是二级索引。因为相同数量的二级索引记录可以比主键索引记录占用更少的存储空间所以二级索引树比主键索引树小于是遍历二级索引的I/O成本小所以优化器选择二级索引。count(1) 执行过程用下面这条语句作为例子selectcount(1)fromt_order;如果表里只有主键索引没有二级索引时。那么InnoDB 循环遍历聚簇索引主键索引将读取到的记录返回给 server 层但是不会读取记录中的任何字段的值因为 count 函数的参数是 1不是字段所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL因此 server 层每从 InnoDB 读取到一条记录就将 count 变量加 1。可以看到count(1) 相比 count(主键字段) 少一个步骤就是不需要读取记录中的字段值所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。不过需要注意上面这个结论只适用于「表里没有任何二级索引、只能扫聚簇索引」这种场景。如果表上存在二级索引优化器会把 count(主键字段) 也改写成扫描 key_len 最小的二级索引参见后文此时 count(1) 和 count(主键字段) 的执行过程完全一致性能上没有差异。但是如果表里有二级索引时InnoDB 循环遍历的对象就是二级索引了。count(*) 执行过程看到 * 这个字符的时候是不是大家觉得是读取记录中的所有字段值对于 select * 这条语句来说是这个意思但是在 count() 中并不是这个意思。count(*) 其实等于 count(0)也就是说当你使用 count() 时MySQL 会将 * 参数转化为参数 0 来处理。所以count(*) 执行过程跟 count(1) 执行过程基本一样的性能没有什么差异。count(字段) 执行过程count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的。用下面这条语句作为例子// name不是索引普通字段selectcount(name)fromt_order;对于这个查询来说会采用全表扫描的方式来计数所以它的执行效率是比较差的。小结count(1)、 count()、 count(主键字段)在执行的时候如果表里存在二级索引优化器就会选择二级索引进行扫描。所以如果要执行 count(1)、 count()、 count(主键字段) 时尽量在数据表上建立二级索引这样优化器会自动采用 key_len 最小的二级索引进行扫描相比于扫描主键索引效率会高一些。再来就是不要使用 count(字段) 来统计记录个数因为它的效率是最差的会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数建议给这个字段建立一个二级索引。如何优化count(*)近似值如果业务对于统计个数不需要很精确比如搜索引擎搜索干建瓷的时候给出的搜索结果条数是一个大概值。我们可以使用show table status或者explain命令来表示估算。explain命令效率很高因为他并不胡正真的去查询。额外表保存计数值如果像获得精确的获取表的记录总数我们可以将计数值保存到单独的一张计数表中。当在数据表插入一条记录的同时将计数表的计数字段1.需要额外维护这张计数表。MySQL分页查询两种limit执行过程基于主键索引的limit执行过程select*frompageorderbyidlimit0,10;service层调用innodb的接口在innodb里的主键索引中获取第0-10条完整行数据一次返回给service层并放到service层的结果集中返回给客户端。select*frompageorderbyidlimit6000000,10;offset改为了6000000会在innodb里的主键索引中获取到第0-600000010条整行数据返回给service层后根据offset值挨个抛弃只留下最后main的size条10条数据。可以看出当offset非0时server层会从引擎层获取到很多无用的数据而获取的这些无用数据都是要耗时的。mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出100010条数据并抛弃前1000条这部分耗时更大。因为前面的offset条数据最后都是不要的所以我们可以将sql语句修改成下面这样select*frompagewhereid(selectidfrompageorderbyidlimit6000000,1)orderbyidlimit10;先执行查询语句select id from page order by id limit 60000001这个操作将在innodb中的主键索引中获取到60000001条数据然后server层会抛弃前6000000条只保留最后一条数据的id。但不同的地方在于在返回server层的过程中只会拷贝数据行内的id这一列而不会拷贝数据行的所有列当数据量较大时这部分的耗时还是比较明显的。基于非主键索引的limit执行过程select*frompageorderbyuser_namelimit0,10;server层会调用innodb的接口在innodb里的非主键索引中获取到第0条数据对应的主键id后回表到主键索引中找到对应的完整行数据然后返回给server层server层将其放到结果集中返回给客户端。当offset变得非常大时MySQL会使用全表扫描。这是因为server层的优化器会在执行器执行sql语句前判断下哪种执行计划的代价更小。很明显优化器在看到非主键索引的600w次回表之后摇了摇头还不如全表一条条记录去判断算了于是选择了全表扫描。因此当limit offset过大时非主键索引查询非常容易变成全表扫描。这种情况也能通过一些方式去优化。比如select*frompage t1,(selectidfrompageorderbyuser_namelimit6000000,100)t2WHEREt1.idt2.id;通过select id from page order by user_name limit 6000000, 100。先走innodb层的user_name非主键索引取出id因为只拿主键id不需要回表所以这块性能会稍微快点在返回server层之后同样抛弃前600w条数据保留最后的100个id。然后再用这100个id去跟t1表做id匹配此时走的是主键索引将匹配到的100条行数据返回。这样就绕开了之前的600w条数据的回表。如何解决深分页问题子查询优化把原本的查询分为两步先用子查询在二级索引上快速定位其起始id再用这个id去主键索引取数据-- 原始写法慢SELECT*FROMmianshiyaWHEREnameyupiORDERBYidLIMIT99999990,10;-- 优化写法SELECT*FROMmianshiyaWHEREnameyupiANDid(SELECTidFROMmianshiyaWHEREnameyupiORDERBYidLIMIT99999990,1)ORDERBYidLIMIT10;name字段有索引的情况下子查询只扫描name的二级索引二级索引只存了name和id数据量比主键索引小很多。拿到其实id后再去主键索引取10条完整记录速度很快。游标分页每次查询都返回当前页的最大id下次查询时带上这个id作为起点-- 第一页SELECT*FROMmianshiyaWHEREnameyupiORDERBYidLIMIT10;-- 假设最大 id 是 100-- 第二页SELECT*FROMmianshiyaWHEREnameyupiANDid100ORDERBYidLIMIT10;利用idmaxId直接过滤MySQL可以从索引定位到起始位置不用扫描前面的数据。缺点是只能连续翻页没法跳到10000页。搜索引擎把数据同步到elasticsearch用search_after做深度分页。