SQL索引

遥远的将不再遥远，平凡的已不再平凡。

索引 index

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

分类

单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
复合索引：一个索引同时包括多列

创建索引

• 查看索引，主键会自动创建索引

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不仅要保存数据,也要保存一下索引文件
• 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引