③. in和exsits优化

• ①. in后面跟的是小表,exists后面跟的是大表

• ②. 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
    }

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

将主查询A的数据,放到子查询B中做条件验证,根据验证结果(true或false)来决定主查询的数据是否保留

elect * from A where exists (select 1 from B where B.id = A.id)
#等价于:
    for(select * from A){
      select * from B where B.id = A.id
    }
#A表与B表的ID字段应建立索引

④. count(*)查询优化

• ①. 坏境准备

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

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

②. 注意:以上4条sql只有根据某个字段count不会统计字段为null值的数据行

③. 四个sql的执行计划一样,说明这四个sql执行效率应该差不多

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

字段无索引:count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引,count(主键 id)还可以走主键索引,所以count(主键 id)>count(字段)

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

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

为什么对于count(id),mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少,检索性能应该更高,mysql内部做了点优化(应该是在5.7版本才优化)。