什么是索引
索引的目的在于提高查询效率。
一 般的应用系统，读写比例在10:1 左右 ，而且插入操作的一般的更新操作很少出现性能问题，在生产环境中，我们遇到的更多的，也是最容易出问题的，还是一些复杂的查询操作。而查询中，索引则显示得特别重要。
索引在 MySQL 中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其当表中的数据特别大时，索引以俞发重要。
磁盘 IO 与预读
磁盘 IO是非常高昂的操作，计算机操作系统做了一些优化，当一次 IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内。局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。
每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
索引的数据结构
数据结构要求，每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。 B+ 树应运而生。
- 浅蓝色的块称之为一个磁盘块，每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）。
- 如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
- 真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
B+树的查找过程
查找数据项29：
首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
B+ 树性质
- 索引字段要尽量的小
- 索引的最左匹配特性（即从左往右匹配）
MySQL 索引分类
- 普通索引 index: 加速查找
- 唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique： 加速查找+约束 （唯一） - 联合索引
primary key(id,name):联合主键索引
unique(id,name):联合唯一索引
index(id,name):联合普通索引 - 全文索引fulltext : 用于搜索很长一篇文章的时候，效果最好。
- 空间索引spatial : 几乎不用
创建/删除索引
key 列
PRI 主键索引
MUL 辅助索引
UNI 唯一索引
在创建表时就创建（需要注意的几点）
create table s1(
id int ,
# 可以在这加primary key
# id int index不可以这样加索引，因为index只是索引，没有约束一说，不能像主键，还有唯一约束一样，在定义字段的时候加索引
name char(20),
age int,
email varchar(30)
index(id) #可以这样加
#primary key(id) #也可以在这加
);
在创建表后在创建
create index name on s1(name); #添加普通索引
create unique age on s1(age);#添加唯一索引
create index name on s1(id,name); #添加普通联合索引
alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
ALTER TABLE 表名 ADD INDEX idx_索引名(索引列); #建立指定列的单列索引
ALTER TABLE 表名 ADD INDEX idx_索引名(索引列(n)); #建立指定列的前缀索引(n为前缀长度)
ALTER TABLE 表名 ADD INDEX idx_索引名(索引列1,索引列2,...); #建立多列的联合索引
删除索引
drop index id on s1;
drop index name on s1; #删除普通索引
drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)
索引使用
- 覆盖索引
select * from s1 where id=123;
sql命中了索引，但未覆盖索引
利用id=123到索引的数据结构中定位到该id在硬盘中的位置，或者说再数据表中的位置。
但是我们select的字段为*，除了id以外还需要其他字段，这就意味着，我们通过索引结构取到id还不够，
还需要利用该id再去找到该id所在行的其他字段值.
网友评论