MySQL索引底层数据结构

索引简介

索引是一个排好序的数据结构，包含着对数据表里所有记录的引用指针，如下图所示。索引文件和数据文件一样都存储在磁盘中，数据库索引的目的是在检索数据库时，减少磁盘读取次数。

常见的索引数据结构包括二叉树、红黑树、Hash表、B树，可以通过https://www.cs.usfca.edu/~galles/visualization/Algorithms.html可视化学习这些数据结构。比如建立一个二叉树：

MySQL中使用的索引结构

Mysql索引主要有两种结构：B+Tree索引和Hash索引。

在MySQL中，只有Memory存储引擎支持Hash索引，Hash索引是Memory表的默认索引类型。Memory存储引擎下，数据存储在内存中，Hash索引则把数据以hash形式组织起来，因此通过hash值查找某一条数据时，检索速度是非常快。但又因为hash结构中每个键只对应一个值，而且数据分布散列，所以它不支持数据范围查找和排序等功能。

• B-Tree（B树）
  • 叶节点具有相同的深度，叶节点的指针为空
  • 所有索引元素不重复
  • 节点中的数据索引从左到右递增排列
    
• B+Tree（B+树）
  • 非叶子节点不存储数据，只存储索引，索引数据冗余
  • 叶子节点包含所有索引字段
  • 叶子节点用指针连接形成双向链表，提高区间查找的效率
    
    B+Tree索引是mysql使用最频繁的一个索引数据结构，在Inodb和Myisam存储引擎模式中支持BTree索引。相对Hash索引，B+Tree在查找单条记录的速度比不上Hash索引，但B+Tree索引支持范围查找等功能，实际用途更广。

从B+Tree索引结构图可以看到，非叶子结点只存储索引，叶子结点中既存储索引又存储数据，并且叶子结点之间形成双向链表。

比如在查找id=8时的数据

聚簇（聚集）索引和非聚簇（非聚集）索引

聚簇索引：数据和索引都存储在一个文件中
非聚簇索引：数据和索引存储在不同文件中，即在检索数据时，需要先读取索引文件，再根据索引文件中标记的磁盘地址去查找数据文件。

InnoDB 存储引擎

InnoDB 存储引擎中索引就是聚簇索引，数据和索引都存储在一个idb文件中，索引结构采用的是B+Tree，叶子节点中存储的键值为索引和索引列的数据值。

为什么建议InnoDB表必须建自增主键？
我们知道InnoDB存储引擎中，采用B+Tree作为索引和数据的存储结构，这样必然需要一个列作为key，key 是不重复的值且可以比较确保有序，而主键特性不可重复、不为空，正符合这样的条件。在聚簇索引中，默认key就是主键。

我们知道索引是一种有序的结构，如果主键不是自增的会怎么样？

如果没有指定主键，则Mysql会自动找到一个合适的唯一索引（不包含有NULL值的唯一索引）作为主键，若找不到符合条件唯一索引条件的字段时，会选择内置6字节长的ROW_ID作为隐含的聚集索引充当该InnoDB表的主键，此时写入顺序和ROW_ID增长顺序一致。

而如果使用自增列(INT/BIGINT类型)做主键，这时候数据写入顺序是自增的，这和B+数叶子节点分裂顺序一致，在数据插入和检索时效率高。

推荐采用自增主键正是因为数据写入顺序能和B+树索引的叶子节点顺序一致时，数据的存取效率是最高的。

MyISAM存储引擎

MyISAM存储引擎的数据文存储在myd文件中，索引存在myi文件中，两者是分开存储的。索引结构同样采用的是B+Tree索引，叶子节点中存储的键值为索引和索引所在行的磁盘地址，数据文件需要根据索引所在行的磁盘地址进行查找。

MySQL常见索引类型

MySQL常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

1. INDEX（普通索引）：ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col’)
   最基本的索引，没有任何限制
2. UNIQUE（唯一索引）：ALTER TABLE ‘table_name’ ADD UNIQUE(‘col’)
   与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。
3. PRIMARY KEY（主键索引）：ALTER TABLE ‘table_name’ ADD PRIMARY KEY(‘col’)
   是一种特殊的唯一索引，不允许有空值。
4. FULLTEXT（全文索引）：ALTER TABLE ‘table_name’ ADD FULLTEXT(‘col’)
   仅可用于MyISAM和InoDB，针对较大的数据，生成全文索引很耗时耗空间
5. 组合索引：ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,‘col3’)

为了更多的提高mysql效率可建立组合索引，遵循“最左前缀”原则。创建复合索引应该将最常用（频率）做限制条件的列放在最左边，一次递减。组合索引最左字段用in是可以用到索引的。相当于建立了col1,col1col2,col1col2col3三个索引