08 索引
一、什么是索引
- 索引就相当于一本书的目录,通过目录快速查找对应资源。
- 在数据库方面,查询一张表的时候有两种检索方式:
- 第一种是:全表扫描
- 第二种是:根据索引检索(效率很高)
- 索引提高检索效率的本质:缩小了扫描范围
- 索引虽然可以提高检索效率,但是不能随意添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护。是有维护成本的。比如,表中的数据经常被修改这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护。
- 添加索引指的是给某一字段或者某些字段添加索引。
二、如何创建索引对象和删除索引对象
-
创建索引
使用命令:create index [索引名称] on [表名]([字段名]); /*示例*/ create index t_stu_sname on t_stu(sname);
-
删除索引
使用命令drop index [索引名称] on [表名]; /*示例*/ drop index t_stu_sname on t_stu;
三、什么时候考虑给字段添加索引(满足条件)
- 数据量庞大(根据客户需求和线上环境判断)
- 该字段很少的DML操作(因为字段进行修改操作,索引也需要维护)
- 该字段经常出现在
where
语句中(经常根据某个字段查询) - 注意:
主键
和具有unique
约束的字段会自动添加索引
四、查看sql语句执行计划
使用命令:explain [sql语句];
explain select * from t_stu where sname='zs2';
执行结果:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | t_stu | ref | t_stu_sname | t_stu_sname | 195 | const | 2 | Using where |
五、索引的底层原理
- 采用的数据结构:B + Tree 结构
- 实现原理
通过B + Tree 缩小扫描范围,底层索引进行排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据后,获得到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。select sname from t_stu where sname='zs1'; /*通过索引转换为:*/ select sname from t_stu where 物理地址=0x2;
六、索引的分类
- 单一索引:给单个字段添加索引
- 复合索引:给多个字段联合起来添加一个索引
- 主键索引:主键上为主动添加索引
- 唯一索引:有unique约束的字段上会自动添加索引
七、索引什么时候失效
select sname from t_stu where sname like '%z%';
什么时候失效
select sname from t_stu where sname like '%z%';
模糊查询的时候,第一个通配符用%,这时索引会失效。