mysql 索引组织表 实现 mysql 索引 结构

MySQL

四、索引

4.1索引简介

• MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构
• 索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。
• 可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例（MyISAM）。

• 索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上
• 平常说的索引，没有特别指明的话，就是B+树（多路搜索树，不一定是二叉树）结构组织的索引。

优势：

• 减少扫描数量，提高数据检索效率
• 将随机IO变成顺序IO，降低数据库IO成本
• 降低数据排序的成本，降低CPU的消耗

劣势：

• 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

4.2基础语法

创建：

CREATE [UNIQUE] INDEX indexName ON mytable(username(length));

-- 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

删除

DROP INDEX [indexName] ON mytable;

查看

SHOW INDEX FROM table_name\G --可以通过添加 \G 来格式化输出信息。

修改

ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)

-- 示例

-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) 

-- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)。

-- 添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD INDEX index_name (column_list) 。

-- 该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

4.3索引分类

数据结构角度

• B+树索引
• Hash索引(底层是哈希表，哈希索引做区间查询的速度是很慢,要扫描链表，适合等值查询)
• Full-Text全文索引
• R-Tree索引

从物理存储角度

• 聚集索引（clustered index），索引和数据存储在一起
• 非聚集索引（non-clustered index），也叫辅助索引（secondary index）
  聚集索引和非聚集索引都是B+树结构

从逻辑角度

• 主键索引：主键索引是一种特殊的唯一索引，不允许有空值
• 普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引
• 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
• 唯一索引或者非唯一索引，唯一索引列值可为null，且可有多个null
• 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

这里补充一点覆盖索引的内容，覆盖索引（Covering Index），或者叫索引覆盖， 也就是平时所说的不需要回表操作，准确的来说，他并不是指某一种具体的索引，而是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

是否是覆盖索引可通过使用explain，根据输出的extra列来判断，如果使用了覆盖索引显示为using index。

前缀索引因为不能判断索引列的值对应的完整数据是否符合要求，所以一定会回表，也就不能享受覆盖索引带来的好处。

4.4索引结构

首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

4.4.1B-Tree 和 B+Tree 的区别

4.4.1.1B-Tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，在 MySQL 中可通过如下命令查看页的大小：show variables like 'innodb_page_size';。

而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述 B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个 3 阶的 B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

4.4.1.2B+Tree

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

从前面的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息；
2. 所有叶子节点之间都有一个链指针；
3. 数据记录都存放在叶子节点中

由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点（图中未显示），而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10³）,也就是说一个深度为3的B+Tree索引可以维护
10 ³ x 10³ x10³ = 10亿条记录（实际上叶子节点内的数据要大一些，这里只是估算）。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

一个叶子节点的总体结构如下：

File Header：是对 InnoDB Page 的一个总体描述，包含了前驱指针、后继指针以及当前 Page 的类型等等，InnoDB的叶子节点是双向链表结构，所以在范围查询时会更快，只需要通过前驱或后驱指针查找即可，不需要再回到根节点重新查找。User Records：InnoDB规定User Records中至少有2条记录，防止B+树退化成链表，其中的数据是以单向链表的形式保存的。

compact行记录格式：

Page Directory:官方文档对这部分的描述是

The Page Directory part of a page has a variable number of record pointers. Sometimes the record pointers are called “slots” or “directory slots”

意思是Page Directory里面有一些可变数量的记录指针，有时把这些指针称为槽（slot）。

大致示意图如上，根据slot进行二分查找，官方文档关于这部分描述是：

Since the Page Directory does not have a slot for every record, binary search can only give a rough position and then InnoDB must follow the “next” record pointers. InnoDB’s “sparse slots” policy also accounts for the n_owned field in the Extra Bytes part of a record: n_owned indicates how many more records must be gone through because they don’t have their own slots

也就是说并不是每一个记录都是对应的slot，二分查找的时候只是先查到一个大致的范围，在根据指针上的next指针进行顺序遍历。行记录中的Extra Bytes里有一列是n_owned，表示该记录（slot）下有多少个其他record，从这个slot出发经过多少个record到下一个slot。

所以，InnoDB查询数据的大致流程为：

1. 首先从 B+Tree 的根节点开始，逐层检索，每一层的检索都需要一次逻辑 I/O，然后找到对应的叶子节点，也就是对应的数据页，将其载入内存。
2. 根据 Page Directory 中的 slot 进行粗略的二分搜索，找到数据所在的记录分组，然后通过链表遍历的方式找到具体的那一条记录。

4.4.2MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

在MyISAM中，索引（含叶子节点）存放在单独的.myi文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）。

主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。

4.4.3InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为“聚簇索引”，一个表只能有一个聚簇索引。

主键索引：

InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的id、stu_id、name数据项。

在Innodb中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。Innodb的数据文件可以按照表来切分，切分后存放在xxx.ibd中，不切分，存放在xxx.ibdata中。切分由innodb_file_per_table)控制，SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table'可以查看当前设置值。

辅助（非主键）索引：

它的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

查询索引树上不存在的列数据时，需要两个步骤：

① 在辅助索引上检索name，到达其叶子节点获取对应的主键；

② 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询”

4.4.4Hash索引

主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。目前使用Hash索引的数据库并不多，主要有Memory等。

MySQL目前有Memory引擎和NDB引擎支持Hash索引

4.4.5full-text全文索引

全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。

它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

同样使用B-Tree存放索引数据，但使用的是特定的算法**，将字段数据分割后再进行索引**（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

4.4.6R-Tree空间索引

空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型

4.5需要创建索引场景

• 主键自动建立唯一索引
• 频繁作为查询条件的字段
• 查询中与其他表关联的字段，外键关系建立索引（第二个表关联列上要有索引）
• 单键/组合索引的选择问题，高并发下倾向创建组合索引
• 查询中排序的字段，排序字段通过索引访问大幅提高排序速度
• 查询中统计或分组字段

4.6不要创建索引场景

• 表记录太少（维护索引成本高于全表扫描）
• 经常增删改的表
• 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
• 频繁更新的字段不适合创建索引（会加重IO负担）
• where条件里用不到的字段不创建索引