MySQL总结(二)

MySQL总结（二）——索引(Engine)

索引的分类

索引是什么以及为什么使用索引，我们在这就不说了，去图书馆找书，你是看你要找的书在哪个书架还是把整个图书馆找一遍？

数据结构 B+tree，B-Tree，Hash，

物理存储 聚簇索引，二级索引（辅助索引）

字段个数 单例索引，联合索引

字段特性 主键索引，唯一索引，前缀索引

各个索引的简单介绍

聚簇索引就是主键索引，二级索引就是联合索引或者非主键索引

前缀索引就是varchar类型或者char等类型的数据根据前几个字符就可以区别开，不需要全部比较

hash的插入操作和查询操作是非常快的，为什么不用hash呢？

因为hash不能进行范围查询和ORDER BY

InnoDB默认的索引是B+Tree

所以我们后边主要介绍B+Tree

B+Tree

这是一条记录的行格式（后边会介绍，理解为在磁盘上一条记录就是这样储存的）

record_type有4个值

0代表目录页

1代表数据页

2代表最小记录

3代表最大记录

主键索引

这个是主键索引的B+Tree

pCfxr59.png

目录页的是主键和页号，而且记录时所指向页的最小记录

要查找数据时，在目录页二分查找，MySQL 是以页为最小单位进行IO操作的，所以上边的记录需要3次IO操作；

二级索引

这个是二级索引的图

为什么要有二级索引

一张表只能有一个主键，所以就只有一个主键索引，但是查询的话还可能会用到其他的字段，这时候可以给这个字段也加上索引，这个索引就是二级索引(辅助索引)

pCfxRKK.png

这个图的非叶子节点放的是非主键的值和页号, 叶子节点放的是主键和构建索引的非主键的值

所以想要找到全部的数据，就需要根据主键的值，回到主键的B+Tree中去找，这一步操作叫回表

那为什么不在叶子节点放上数据？

没有必要，浪费空间

联合索引

我们也可以用2个及以上的字段一起来建立联合索引

先按照第一个字段排序，在按照第二个字段排序

pCfxr59.png

目录页是两个字段和页号

叶子节点是连个字段加上主键

注意InnoDB的B+Tree

• 根页面位置万年不动

  也就是说，刚开始的时候根页面存的是数据，后来数据太多需要目录页的时候，根页面自动页分裂成为目录页

• 二级目录的内节点唯一

  其实为了保证内节点不重复，二级目录的内节点的目录页存储的是字段+主键

  引中目录项记录的内容只是 索引列 + 页号 的搭配的话，那么为 c2 列简历索引后的B+树应该长这样：

pCfx5UH.png

索引列+主键+页号的B+Tree是下边的图

pCfx78I.png

• 一个数据页最少存两条记录

  不然查起来太慢

不同存储引擎的B+Tree

MyISAM的B+Tree叶子节点存的是数据的地址，InnoDB存的是数据

这与底层存储有关，一个表MyISAM

• 表名.frm 存储表结构
• 表名.MYD 存储数据 (MYData)
• 表名.MYI 存储索引 (MYIndex)

InnoDB是这样的

• 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
• 表名.ibd 存储数据和索引

所以InnoDB中数据即索引，而且从上边可以看出MyISAM就算是主键索引也要回表，严格来说它的索引都是二级索引

索引的代价

索引虽然可以极大的提高查询速度，但会影响插入和修改速度。而且创建一个索引就需要一个B+Tree，这是空间消耗。

本文由 mdnice 多平台发布