mysql索引原理总结

分类：

单列索引

一个索引只包含单个列，一张表可以有多个单列索引

组合索引

一个索引包含多个列

优缺点

查询速度快

更新速度慢，不仅要更新数据，还要更新索引文件

占用磁盘空间

索引

hash、b+树

b+树是对数据进行排序，
再分组记录，可以进行范围查询，支持排序，采用的是b+树，时间是log

hash是直接计算hash值，
因此hash不支持范围查询，不支持排序
因为hash地址会冲突，需要再比较链表的数据内容

当hash冲突少时，hash查找比较快，时间是o(1)+o(logt)

b+：innodb、myisam

myisam表数据文件与索引文件时分离的，索引文件只记录记录再磁盘中的存储地址，可以不需要主键

innodb有表数据的ibd文件，表数据文件就是主索引，叶节点的data域保存了完整的数据信息，按照主键聚集，所以要有主键，如果没有定义的话，会采用隐藏列中的行id来作为主键

因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），
如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，
如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

b+树查找过程

1、从磁盘中加载一页数据，
2、在内存中通过二分查找，
确定数据的范围，锁定第二页的指针，
3、把第二页加载到内存，再二分查找，确定范围，锁定指针，
4、直到找到数据。

1. 字段要尽量小，当树高度越高，就需要多次载入数据，搜索效率比较低，所以字段要尽量小，存的数据会比较多，减少占的页数
2. 最左匹配特性，b+树是采用从左到右的顺序来建树的，查找是也是通过第一个比较因子来确定搜索范围，如果没有第一个比较因子，就没办法确定从哪开始搜索。如果有3个比较条件，只给了头尾，可以第一个采用索引确定下一个要搜索的节点，在对这个范围的数据利用第3个比较条件进行匹配。

问题整理

索引

索引分成2类：

• hash类型
• b+树类型

hash类型

• 方法：

根据hash值进行定位，因为会有hash冲突，索引最后要把查找的元素跟链表中的元素做一个匹配，得到结果，

• 效率

当hash冲突比较少时，时间复杂度是o(1)

b+树类型

• 方法：

将数据进行排序，分组，分组是存在页中，每页中还要页目录，再用页存储页号跟每一页的主键的最小值，同一层的页之间是双向链表，数据页之间也是双向链表

本质：
搜索是通过减少搜索范围以及二分查找可以提高查找速度

• 效率跟特征：

因为cpu从磁盘每次都读取一页，当树的高度比较高时，读取的次数就会比较多，降低查找速度，所以可以减少字段的大小，使一页中存放更多的数据，减少树的高度

• 最左匹配

从左到右进行建树，查找时需要通过第一个比较条件确定下一步搜索范围，如果没有第一个比较条件，就无法确定搜索范围，如果有第一个跟最后一个，会先确定搜索范围，然后对这个范围的所有数据根据最后一个比较条件进行匹配（这个就没有索引了）

引擎

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

聚集索引

采用主键对每张表建立b+树，就是用主键对数据进行排序

同一层的页之间按照主键从左到右是双向链表
数据页之间按照主键从左到右是双向链表
数据页内的记录按照主键从左到右是单向链表
叶子节点存储每一行的记录

不需要用index语句去创建，innodb默认的数据存储方式就是这种方式

优点

查找最后几个时，双向链表可以倒序

查找范围时，叶节点的上层节点可与得到页范围

辅助索引

1. 表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引）
2. 用我们指定的索引列来排序
3. 叶节点存储的是主键跟索引列，
4. 目录页存储的不是主键跟页号，是索引列跟主键、页号
5. 需要再通过主键到聚集索引查找到记录（回表）

目录页的记录的唯一的
占的空间小，可以减少页，所以计较快

联合索引

采用多个列来作为排序的规则,也是辅助索引

先按照前面的列进行排序，相同的话再考虑后面的列

命令

普通索引

index：加速查找

唯一索引：
主键索引primary key(不为空、不重复）：加速查找+约束
唯一索引unique：（不重复）：加速查找+约束

联合索引：
联合主键primary key（id+……）
联合唯一unique（id+……）
联合普通index（id+……）

创建表时创建
unique key id_id(id)
index id_name(name)

在已存在的表上创建
create index id_id on gyy5(message(20));
create index id_id on gyy5(name);

修改表结构时创建
alter table gyy5 add index age int;

删除
alter table gyy5 drop index ix_t;
drop index ix_m on gyy5;

查看表结构
show columns from gyy5