数据库——索引

一、索引（index）

1.什么是索引

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

2.索引的实现原理

注意：（1）在任何数据库当中主键上都会自动添加索引对象；（2）在 mysql 中一个字段上如果有 unique 约束的话，也会自动创建索引对象；（3）在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号；（4）在 mysql 中索引是一个单独的对象，不同的存储引擎以不同的形式存在，不管存储引擎在哪里，索引在 Mysql 中都是一个树的形式存在（自平衡二叉树）

3.什么条件下考虑给字段添加索引

（1）数据量庞大（需要测试获得）
（2）字段经常出现在where后面，以条件的形式存在，也就是说这个字段经常被扫描
（3）该字段很少的DML（insert delete update）操作，因为DML之后，索引需要重新排序

二、索引的创建和删除

1.创建索引

给emp表的ename字段添加索引，起名：emp_ename_index
create index emp_ename_index on emp(ename);

2.删除索引

将emp表上的emp_ename_inex索引对象删除
drop index emp_ename_index on emp;

三、查看一个SQL语句是否使用索引

explain select * from emp wher ename=‘jack’;

四、索引失效

1.模糊查询时以"%"开始

select * from emp where ename like ‘%t’;
尽量避免模糊查询以"%"开头，是一种优化策略

2.使用 or 的时候

如果使用 or 那么要求 or 两边的字段都要有索引，才会使用索引查；如果一个字段有索引另一个字段没有的话，索引会失效
select * from emp where ename=‘jack’ or ejob=‘doctor’;

3.使用复合索引没有使用左侧的列查找

（1）复合索引：两个字段或者更多的字段联合起来添加一个索引
create index emp_job_sal_index on emp(job,sal);
（2）查找时如果查找 job ，索引不会失效；但是如果查找 sal ，索引会失效

4.在where索引列参加了运算

select * from emp where sal+1=1000;

5.在where中索引列使用了函数

select * from emp where lower(ename)=‘jack’;

6.还有其他失效情况

五、索引分类

（1）单一索引：一个字段上添加索引
（2）复合索引：两个或以上字段上添加索引
（3）主键索引：主键上添加索引
（4）唯一性索引：具有unique约束的字段上添加索引
（5）注意：唯一性比较弱的字段上添加索引的用处不大，可能会存在数据重复