每个开发人员都应该知道的Count()Count(1)和Count(id)的区别
1.网上一些文章的总结count(*) :它会获取所有行的数据,不做任何处理,行数+1。 count(1):它会获取所有行的数据,每行固定值1,也是行数+1。 count(id):id代表主键,它需要从所有行的数据中解析出id字段,其中id肯定都不为NULL,行数+1。 count(普通索引列):它需要从所有行的数据中解析出普通索引列,然后判断是否为NULL,如果不是NULL,则行数+1。 count(未加索引列):它会全表扫描获取所有数据,解析中未加索引列,然后判断是否为NULL,如果不是NULL,则行数+1。
由此,最后count的性能从高到低是:
count(*) count(1) > count(id) > count(普通索引列) > count(未加索引列)
以上结论错误至极,根本没有得到验证。
下面我将基于 MySQL 5.7 + InnoDB引擎进行总结分析。 2.分析
下面是一张数据量为100万的表,表中字段比较短,整体数据量不大。 CREATE TABLE `hospital_statistics_data` ( `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT, `id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL, `hospital_code` varchar(36) COLLATE utf8mb4_general_ci NOT NULL, `biz_type` tinyint NOT NULL, `item_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `item_name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL, `item_value` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL, `is_deleted` tinyint DEFAULT NULL, PRIMARY KEY (`pk_id`) ) DEFAULT CHARSET=utf8mb4;
下面我会通过不同的 索引情况来看 count(*) 的执行计划。 2.1 只有一个聚簇索引EXPLAIN select COUNT(*) from hospital_statistics_data; type: index key: PRIMARY key_len: 8
count(*) 会遍历索引,并使用 聚集索引。2.2 存在非聚集索引(二级索引)
hospital_code 向表中添加索引。 alter table hospital_statistics_data add index idx_hospital_code(hospital_code)
此时表中有2个索引 primary key , hospital_code 。 EXPLAIN select COUNT(*) from hospital_statistics_data; type: index key: idx_hospital_code key_len: 146
索引变为刚刚添加的 idx_hospital_code 。 2.3 有两个非聚集索引(二级索引)
再添加一个二级索引。alter table hospital_statistics_data add index idx_biz_type(biz_type);
此时表中有3个索引 primary key 、 hospital_code 、 biz_type 。 EXPLAIN select COUNT(*) from hospital_statistics_data; type: index key: idx_biz_type key_len: 1
现在索引变成是 biz_type ,是不是很神奇。 2.4 基于以上三个索引,分别来看 count(1), count(id), count(index), count(no index)
这四个的执行计划和 count(*) 有什么区别?
count(1) EXPLAIN select COUNT(1) from hospital_statistics_data; type: index key: idx_biz_type key_len: 1
count(pk_id) EXPLAIN select COUNT(pk_id) from hospital_statistics_data; type: index key: idx_biz_type key_len: 1
count(index) EXPLAIN select COUNT(biz_type) from hospital_statistics_data; type: index key: idx_biz_type key_len: 1
count (no index) EXPLAIN select COUNT(item_code) from hospital_statistics_data; type: ALL key: null key_len: null2.5 总结count(index) 将使用当前索引指定的索引。 count(no index) 是没有索引的全表扫描。 count(1), count(*), count(id) 也会选择 idx_biz_type 索引。 三、知识点
MySQL分为服务层和引擎层。
所有的SQL在执行前都会经过服务层的优化。优化有很多种,可以简单分为成本和规则优化。
执行计划反映了SQL优化后服务层可能的执行过程。在大多数情况下,执行计划是可信的(不绝对,以防有人说我只看执行计划过于片面)。
索引类型分为聚集索引和非聚集索引(二级索引)。其中,数据挂在聚簇索引上,非聚簇索引只是记录的主键id。
抛开数据内存不谈,只谈数据量是扯淡。什么500万是极限,什么超过2个表需要优化join,什么是null就不会去索引等等,都是错误的。
原因分析
原因很简单。如上所述,服务层将根据成本进行优化。而且,一般情况下,非聚簇索引占用的内存要比聚簇索引小很多。
问题讨论 3.1 如果你是 MySQL 开发者,你会在执行 count(*) 查询时使用哪个索引?
我相信普通人使用非聚集索引。 3.2 如果有2个或多个非聚集索引如何选择?
那么一定要选择占用内存最小的。同样是非聚集索引, idx_hospital_code 长度为146字节,而 idx_biz_type 长度仅为1。 3.3 那为什么count(*)取了index之后还是很慢?
这里要明确一点,索引只是提高效率的一种方式,并不能完全解决效率问题。 count(*) 有一个明显的缺陷,就是需要计算总数,也就是遍历所有符合条件的数据,相当于一个计数器。当数据量足够大时,即使使用非聚集索引,也不能优化太多。
官方文档:
InnoDB 以相同的方式处理 SELECT COUNT(*) 和 SELECT COUNT(1) 操作。没有性能差异。
简单的说,InnoDB下的 count(*) 相当于 count(1) 。 3.4 既然会自动取索引,那上面所谓的快速排序还觉得对吗?
count(*) 的性能与数据量有很大关系。此外,二级索引的字段长度越短越好。
另外,网上提到的索引故障大多是片面的,这里只说一点。量变可以导致质变。索引失效取决于你划定数据的范围。
如果筛选的数据量占整体数据量的比例过高,就会放弃使用索引,否则,就会优先使用索引。但是这个规则并不完美,有时候可能和你预想的不一样。也可以使用一些技巧强制使用索引,但这种方法很少用到。
例如:
通过上表 hospital_statistics_data ,做如下查询: select * from hospital_statistics_data where hospital_code is not null ;
这个SQL hospital_code 这个时候会用到索引吗?
如果 hospital_code 只有一小部分数据是null,那么不会使用索引,否则会使用索引。
就像买橘子一样。如果只买2斤,挑篮子里好的就可以了。但是如果你要买一筐,相信老板不会让你一个一个挑,而是一次给你一整筐。当然,大家也不傻,谁都知道篮子里肯定有几个坏果子。但这样对老板来说效率最高,成本也最低。