索引的数据结构

一、索引及其优缺点

1、索引概述：索引是帮助MySQL高效获取数据的数据结构

2、优点：

• 提高数据检索的效率，降低数据库的IO成本
• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
• 在实现数据的参考完整性方面，可以加速表和表之间的连接
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间 ，降低了CPU的消耗

3、缺点：

• 创建索引和维护索引要耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加
• 索引需要占磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸
• 降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度

二、InnoDB中索引的简单数据结构

1、建一张表：

CREATE TABLE index_demo(
  c1 INT,
  c2 INT,
  c3 CHAR(1),
  PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

这个表有c1、c2、c3 三个字段，其中c1为主键，那么在InnoDB引擎中的索引数据结构可大致为下图所示：

说明：

第三行存储的是表三个字段的信息，第二行为目录项信息，第一行为第二行的目录项信息。

三、常见索引概念

索引按照物理实现方式，分为两种：聚簇索引和非聚簇索引。我们也把非聚簇索引称为二级索引或者辅助索引。

1、聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在叶子节点），也就是所谓的索引即数据，数据即索引

特点：

• 使用记录主键值的大小进行记录和页的排序
• B+树的叶子节点存储的是完整的用户记录

我们把具有这两种特点的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点上。这种聚簇索引并不需要我们在MySQL语句中显式的使用Index语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引

优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作

缺点：

• 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
• 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
• 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

限制：

• 对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MyISAM不支持聚簇索引
• 由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。
• 如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引
• 为了充分利用聚簇索引的聚簇特点，所以InnoDB表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如UUID，MD5，HASH，字符串列作为主键无法保证数据的顺序增长

2、非聚簇索引（二级索引或者辅助索引）

说明：

聚簇索引只能在搜索条件为主键值时才能发挥作用，如果搜索条件不是主键值就可以多建几颗B+树，不同的B+树中的数据采用不用的排序顺序。比如使用c2列的大小作为数据页，再建一颗B+树，如下图：

 这个B+树和聚簇索引中的B+树有以下区别：

• 使用记录c2列的大小进行记录和页的排序
• B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值
• 目录项记录中不再是主键+页号的搭配，而变成c2列+页号的搭配

回表：

这时候如果以c2为条件查找的话，就会先从这个B+树中查找出主键的值，再用主键的值从聚簇索引中查找。这个过程叫做回表

总结：

• 聚簇索引的叶子节点存储的是数据记录，非聚簇索引叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序
• 一个表只能有一个聚簇索引，因为只能有一种存储的方式，但是可以有多个非聚簇索引
• 使用聚簇索引的时候，数据的查询效率高，但对数据进行插入、删除、更新等操作时，效率会比非聚簇索引低

 3、联合索引（非聚簇索引的一种）

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照c2列进行排序
• 在记录的c2列相同的情况下，采用c3列进行排序

示意图如下：

 如图所示，需要注意一下几点：

• 每条目录项记录都是由c2、c3、页号这三部分组成的，给条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序
• B+树叶子节点处的用户记录由c2、c3和主键c1组成

四、MyISAM中的索引方案

MyISAM的索引方案也使用了树结构，但是将索引和数据分开存储

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称为数据文件。这个文件不划分若干个数据页，有多少记录就往这个文件中塞多少个记录。
• 把索引信息存储到另一个文件中，称为索引文件。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的数据，而是主键值+数据记录地址的组合

五、MyISAM 与 InnoDB对比

• 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。
• InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
• InnoDB的非聚簇索引data域存储相应记录主键的值 ，而MyISAM索引记录的是地址。换句话说， InnoDB的所有非聚簇索引都引用主键作为data域
• MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问
• InnoDB要求表必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型

六、索引的代价

1、空间上的代价：每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间

2、时间上的代价：每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了 双向链表 。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序 而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需 要额外的时间进行一些记录移位 ， 页面分裂 、 页面回收等操作来维护好节点和记录的排序。如果建了许多索引，每个索引对应的B+树都要进行相关的维护操作，性能变差