详谈MySQL优化器如何使用索引快速统计(count函数)

项目中，遇到一个统计需求，从某张表中按照条件分别统计，使用count(distinct case when)解决此问题。

count()函数

数据统计中，count出现最频繁

最简单的用法

select count(*) from table where ....

select count(distinct column_name) from table where ...

count(1)、count(*)、count(字段)，它们有什么区别呢？

查看表所有索引SQL语句

SHOW INDEX FROM table_name;

• count(1) 和 count(*)

count(1)和count(*)差别不大，通过explain发现MySQL会帮你自动优化(仅有主键索引时使用主键索引、存在唯一索引和主键时使用唯一索引、存在普通索引和主键索引时选择普通索引，存在多个二级索引时选择索引占空间最少的一个)，指定到最快的字段或者索引树（组合索引）。

表存在主键和唯一索引

 表结构信息sql语句 (SHOW CREATE TABLE `t_product_events_params`)

CREATE TABLE `t_product_events_params` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '产品类型事件参数ID',
  `ref_id` bigint(20) NOT NULL COMMENT '事件ID',
  `identifier` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(64) NOT NULL COMMENT '参数名称',
  `description` varchar(256) DEFAULT NULL COMMENT '参数描述',
  `data_type` int(11) NOT NULL COMMENT '参数数据类型',
  `specs` longtext NOT NULL COMMENT '数据规格',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ref` int(11) NOT NULL,
  `custom` tinyint(2) DEFAULT NULL COMMENT '模板引入，0：引入',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `identifier_ref_id` (`ref_id`,`identifier`) USING BTREE COMMENT '同一功能定义下，identifier不允许重复'
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='产品类型事件输出数据规格表'

 存在普通索引和主键索引的表

 

仅有主键索引的表

表中索引如下图

 根据explain来看，count(1)和count(*)二者并无太多区别!!!

原理：

统计行数的操作，查询优化器的优化方向就是选择能够让IO次数最少的索引，也就是基于占用空间最小的字段所建的索引（每次IO读取的数据量是固定的，索引占用的空间越小所需的IO次数也就越少）。而Innodb的主键索引是聚簇索引（包含了主键，主键对应的整条记录信息，事务ID、MVCC和回滚指针）所以主键索引一定会比二级索引（包含KEY和对应的主键ID）大，也就是说在有二级索引的情况下，一般COUNT()都不会通过主键索引来统计行数，在有多个二级索引的情况下选择占用空间最小的！！！

• count(*) 和 count(字段)

count(*)会统计所有行数，count(字段)不会统计null值

count(case when)

条件统计，即对某个table分条件统计，比如表test_db，有一个字段user_id(可能重复), gender(man、women)，需要统计man和women的人数

可以使用where分别统计

select count(distinct user_id) as man_cnt from test_db where gender = 'man'

select count(distinct user_id) as women_cnt from test_db where gender = 'women'

也可以使用按条件统计

SELECT
	count( DISTINCT CASE gender = 'man' THEN user_id END ) AS man_cnt,  count( DISTINCT CASE gender = 'women' THEN user_id END ) AS woman_cnt 
FROM
	test_db

更多SQL复杂查询https://blog.csdn.net/Be_insighted/article/details/107683361?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522164217052716780271580287%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=164217052716780271580287&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-27-107683361.nonecase&utm_term=%E7%B4%A2%E5%BC%95&spm=1018.2226.3001.4450