oracle中的count(*)与count(name)效率

1.如果COUNT(列）中列有索引时且列为空时

COUNT(列）比COUNT(*)要快，因为count(*)不可以使用索引

2.如果COUNT(列）中列有索引时且列不为空时

count(列)和count(*)其实一样快，如果索引列是非空的，count(*)可用到索引

但是当count(列)中的列在表中的位置越靠后速度越慢

 

 

 

 

select u.* from t_user u where u.empe_code in ('0000126991','1111');--ok
select u.* from t_user u where u.dept_code='0003130122'; -- 2 no

select u.* from t_user u where u.job_code='P100021404003'; --3 no

select u.* from t_user u where u.empe_code='0000126991' and u.job_code='P100021404003';-- 13 ok
select u.* from t_user u where u.job_code='P100021404003' and u.empe_code='0000126991'; --31 ok
select u.* from t_user u where u.job_code='P100021404003' and u.dept_code='0000126991'; --32 no
select u.* from t_user u where u.dept_code='0000126991' and u.job_code='P100021404003'; --23 no

select u.* from t_user u where u.empe_code='0000126991' and u.dept_code='0000126991' and u.job_code='P100021404003'; --123 ok
select u.* from t_user u where u.empe_code='0000126991' and u.job_code='P100021404003' and u.dept_code='0000126991';--132 ok
select u.* from t_user u where u.job_code='P100021404003' and u.dept_code='0000126991' and u.empe_code='0000126991';--321 ok
create index user_empe_dept_job on t_user (empe_code,dept_code,job_code);

创建多列索引后，只要索引的首个字段出现就会被使用，与出现顺序无关

 

转载于:https://www.cnblogs.com/atwanli/articles/8097805.html