数据库基础笔记（MySQL）5 —— 基础索引

索引

索引是帮助 MySQL 高效获取数据的数据结构，可以在字段上添加，用于提高查询效率（ 可以给一个字段添加，也可以给多个字段联合添加 ）

创建索引，就相当于为这个字段中的数据创建一个 B+ 树，如果没有索引我们便在该字段上一个个找对比值，如果有索引，我们则在 B+ 树上找
例：select * from table_1 where id = 0 ;
（ B+ 树是 B 树的一个升级版，相对于 B 树来说 B+ 树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找 ）

一般给唯一性强的字段添加索引，效率更高

我们经常在 多查询 ， 少修改 ， 数据量大 的情况使用，因为 B+ 树能大幅优化查询效率，但如果添加修改数据，我们将需要调整树结构来维护数据，如果添加修改次数过多，反而降低性能

在不同存储引擎中（ 后面会讲 ），索引存放的位置不同

查看查询语句是否使用了索引 ： explain select … from … where …

常用索引类型

1. 普通索引 （ 单列索引 ）
2. 复合索引 （ 组合索引 ）
3. 唯一索引
4. 主键索引

普通索引 （ 单列索引 ）

就是以一个字段为索引

1. 创建索引（ 直接添加 ） ： create index 索引名 on 表名 ( 字段名 ) ;
2. 创建索引（ 修改表结构形式 ） ： alter table 表名 add index 索引名 ( 字段名 ) ;
3. 删除索引（直接删除 ） ： drop index 索引名 on 表名 ;
4. 删除索引 （修改表结构形式 ） ： alter table 表名 drop index 索引名 ;

复合索引 （ 组合索引 ）

以多个字段联合起来做索引

1. 创建索引（ 直接添加 ） ： create index 索引名 on 表名 ( 字段1 , 字段2 … ) ;
2. 创建索引（ 修改表结构形式 ） ： alter table 表名 add index 索引名 ( 字段1 , 字段2 … ) ;
3. 删除索引（ 直接删除 ） ： drop index 索引名 on 表名 ;
4. 删除索引 （ 修改表结构形式 ）：alter table 表名 drop index 索引名 ;

唯一索引

唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值），如果添加索引的列的值存在两个或者两个以上的空值，则不能创建唯一性索引会失败，如果添加索引的列的值存在两个或者两个以上的null值，还是可以创建唯一性索引，只是后面创建的数据不能再插入null值 ，并且严格意义上此列并不是唯一的，因为存在多个null值

1. 创建索引 ：可创建 唯一普通索引 或 唯一复合索引 ，只要在 index 前加上 unique 关键字即可，其他和上面的一样
2. 删除索引 ：和上面两种一样

对于多个字段创建唯一索引规定列值的组合必须唯一

主键索引

在数据库当中主键上都会自动添加索引，另外在 MySQL 中，如果字段上有 unique 约束，则也会自动创建索引

常见索引失效情况

索引很多情况会失效，大致有 效率方面问题 ， 查询方面问题

效率方面问题

如果MySQL认为 全盘扫描 速度比 扫描索引 速度要快则不走索引，比如

1. 要查询的是该表的绝大部分
2. 列 与 列 之间对比
3. …

查询方面问题

一般就是用索引查不到的情况，比如

1. 索引字段查询存在类型转换
2. 索引字段使用了函数
3. 索引字段参加了数学运算
4. 索引字段用了模糊查询，且以 ‘ % ’ 开头，例 ‘ %23 ’ ，就不知道该从何查起
5. 索引字段查询用的是 != , <>
6. 对于复合索引，只有使用了最左边的索引字段才会使用索引，可以类比模糊查询（ 第四条 ）
7. …