create index idx_emp on emp(ename,deptno);
where ename=‘SCOTT’;
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0)| 00:00:01 |
mysql查询方式–ref
where deptno=‘20’;
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 145 | 2 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_EMP | 5 | | 1 (0)| 00:00:01 |
mysql查询方式–ALL
where ename=‘SCOTT’ and deptno=‘20’;
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0)| 00:00:01 |
mysql查询方式–ref
where deptno=‘20’ and ename=‘SCOTT’;
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0)| 00:00:01 |
mysql查询方式–ref
where ename=‘SCOTT’ and job=‘ANALYST’;
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0)| 00:00:01 |
mysql查询方式–ref
where job=‘ANALYST’ and ename=‘SCOTT’ ;
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0)| 00:00:01 |
mysql查询方式–ref
job=‘ANALYST’ and deptno=‘20’ ;
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_EMP | 5 | | 1 (0)| 00:00:01 |
mysql查询方式–ALL
deptno=‘20’ and job=‘ANALYST’ ;
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_EMP | 5 | | 1 (0)| 00:00:01 |
mysql查询方式–ALL
where job=‘ANALYST’;
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 58 | 4 (0)| 00:00:01 |
mysql查询方式–ALL
mysql
在 MySQL 中如果创建了符合索引，例如创建复合索引（name，salary，dept），就相当于创建了（name，salary，dept）、
（name，salary）和 （name） 三个索引，这被称为复合索引前导列特性，因此在创建复合索引时应该将从常用作为查询条件的列放在最左边，依次递减。
create index idx_emp on emp(empno,ename,job,mgr);
where empno=‘7788’;
mysql查询方式–ref
where empno=‘7788’ and ename=‘SCOTT’;
mysql查询方式–ref
where empno=‘7788’ and ename=‘SCOTT’ and job=‘ANALYST’;
mysql查询方式–ref
where empno=7788 and ename=‘SCOTT’ and job=‘ANALYST’ and mgr=7566;
mysql查询方式–ref
where ename=‘SCOTT’ and job=‘ANALYST’ and mgr=7566;
mysql查询方式–ALL
where job=‘ANALYST’ and mgr=7566;
mysql查询方式–ALL
where empno=7788 and job=‘ANALYST’ and mgr=7566;
mysql查询方式–ref
where empno=7788 and mgr=7566;
mysql查询方式–ref
where mgr=7566 and empno=7788 ;
mysql查询方式–ref
