SQL索引
SQL索引
遥远的将不再遥远，平凡的已不再平凡。
索引 index
索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用B+Tree树方式构建。
分类
单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
复合索引：一个索引同时包括多列
创建索引
- 查看索引，主键会自动创建索引
show index from user;
- 创建索引
create index 索引名字 on 表名(字段名); #创建索引
create index score_index on scores(score); #创建索引
- 修改表结构,添加普通索引
alter table user add index sex_index(sex)
- 创建唯一索引
alter table students add unique(id) #创建唯一索引,索引列的值必须唯一
- 创建复合索引
alter table user add index cp_index(name,age);
- 创建复合唯一索引
alter table user add unique cp_index(name,age);
- 删除索引
alter table uesr drop index cp_index;
索引扫描类型
type：
- ALL 全表扫描，没有优化，最慢的方式
- index 索引全扫描，其次慢的方式
- range 索引范围扫描，常用语<，<=，>=，between等操作
- ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
- eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
- const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
- null MySQL不访问任何表或索引，直接返回结果
使用索引 explain
explain
select * from user where id = 1;#使用了id的索引
最左特性
当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。
注意：查询k2字段或者k3字段或者k2和k3字段，索引就会失效
ALTER TABLE user ADD INDEX user_index(id,name,age); #这里添加复合索引
explain
select * from user where id = 1 and name = "阿柒"; #索引生效
select * from user where name = "阿柒"; #索引失效
select * from user where id = 1 and age = 21; #索引失效
索引查询
明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。
其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。
- 排序，tree结构，类似二分查找
- 索引表小
优点:
- 索引是数据库优化
- 表的主键会默认自动创建索引
- 每个字段都可以被索引
- 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
- 索引事先对数据进行了排序，大大提高了查询效率
缺点:
- 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
- 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
- 虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
- 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引
