张治峰的博客

in、exist 与 count(*)优化

2021-09-24

in 与 exist

原则: 小表驱动大表 , 即小的数据集驱动大的数据集(A.id 索引字段)

in

当B表的数据集小于A表的数据集时,in优于exists

语句分析

select * from A where id in (select id from B)
等价于
for(select id from B){
select * from A where A.id = B.id
}

exist

当A表的数据集小于B表的数据集时,exists优于in。(B.id 索引字段)

语句分析

select * from A where exists (select 1 from B where B.id = A.id)
等价于
for(select id from A){
select * from B where B.id = A.id
}

  1. EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会 忽略SELECT清单,因此没有区别
  2. EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
  3. EXISTS子查询往往也可以用JOIN来代替,何种最优需要具体问题具体分析

count(*) (Innodb)

count统计总数平时我们都用的很多,那么count(*)、count(主键ID)、count(字段)、count(1)那个效率高更高呢?

先上结论: 统计时不用花里胡哨,直接count(*) 就完事了.

语句分析

照旧我们看执行计划,上语句
示例表见 Order by与Group by优化

# 临时关闭mysql查询缓存,为了查看sql多次执行的真实时间
set global query_cache_size=0;
set global query_cache_type=0;

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

⚠️ 以上4条sql只有根据某个字段count不会统计字段为null值的数据行

四个sql的执行计划一样如上图,说明这四个sql执行效率应该差不多。

不同count的用法

1. count(主键ID)

innodb引擎会遍历整张表,把每一行的ID值都那出来,然后返回给server层,server层拿到ID后,判断不可能为空,进行累加。

2. count(1)

count(1)跟count(字段)执行过程类似,不过count(1)不需要取出字段统计,就用常量1做统计,count(字段)还需要取出 字段,所以理论上count(1)比count(字段)会快一点。

3. count(*)

count(@) 是例外,mysql并不会把全部字段取出来,而是专门做了优化,不取值,按行累加,效率很高,所以不需要用 count(列名)或count(常量)来替代 count(@)。

4. count(字段)

分为两种情况,字段定义为not null和null

  • not null:逐行从记录里面读出这个字段,判断不能为null,累加
  • null:执行时,判断到有可能是null,还要把值取出来再判断一下,不是null才累加。

查询效率结论

1.字段有索引:count(*)≈count(1)>count(字段)>count(主键 id)

字段有索引,count(字段)统计走二级索引,二级索引存储数据比主键索引少,所以count(字段)>count(主键id)

2.字段无索引:count(*)≈count(1)>count(主键 d)>count(字段)

字段没有索引count(字段)统计走不了索引, count(主键 id)还可以走主键索引,所以count(主键 id)>count(字段)

为什么对于案例 count(id),mysql最终选择辅助索引而不是主键聚集索引?

因为二级索引相对主键索引存储数据更少,检索 性能应该更高,mysql内部做了点优化(应该是在5.7版本才优化)

mysql对count(*) 优化

MySQL对于count(*)这样的操作,不管遍历那个索引树得到的结果在逻辑上都一样。主键索引叶子节点挂载了整行数据,所以普通索引的比主键索引小很多
因此,优化器会找到最小的那棵树来遍历,所以也走的索引树,在保证正确的逻辑前提下,尽量减少扫描数据量,是数据库系统设计的通用法则之一。

count()优化手段

  1. 查询mysql自己维护的总行数

    对于myisam存储引擎的表做不带where条件的count查询性能是很高的,因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上,查询不需要计算
    对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制,不同事务中查询的总量不一致,后面会讲),查询count需要实时计算

  2. show table status

    如果只需要知道表总行数的估计值可以用如下sql查询,性能很高。

    show table status like 'employees';

  3. 将总数维护到Redis里

    插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令),但是这种方式可能不准,很难保证表操作和redis操作的事务一致性

  4. 增加数据库计数表

    插入或删除表数据行的时候同时维护计数表,让他们在同一个事务里操作

Tags: mysql
使用支付宝打赏
使用微信打赏

若你觉得我的文章对你有帮助,欢迎点击上方按钮对我打赏

扫描二维码,分享此文章