MySQL索引本质解析

一、什么是索引

• 索引是对数据库表中一列或多列的值进行排序的数据结构。
• 表面层次来讲，索引就像一本书的目录，可以快速访问数据库表中的特定信息。
• 深层次来看，索引是帮助MySQL高效获取数据的排好序的数据结构，这个数据结构可以是平衡二叉树、红黑树、Hash表、B-Tree、B+Tree

二、索引数据结构解析

演化：二叉树——红黑树——B-Tree——B+Tree

1、排序二叉树

假设有一表：t

有一sql语句：select * from t where t.col2=89
无索引时，进行全表遍历，直到符合条件，如图需要遍历6次。
以col2建立索引（假设该索引以二叉树去实现，实际大都以B树或B+树），则有：

那么根据排序二叉树“左小右大”性质，只需要两步就找到“89”的指针地址，再根据该指针到磁盘做一次IO，便可以找到想要的那一行数据。

2、红黑树

排序二叉树的弊端：当以col1这类递增列为索引时，树的结构变成链表结构

这样一来就与全文索引无区别。
优化——红黑树：当单边节点超过3个，会自动平衡，则上述结构变成：

红黑树是一种含有红黑节点并能自动平衡的二叉查找树。
性质：

• 根节点和叶子节点(NIL或NULL)都是黑色的。
• 每个红色节点的两个子节点一定都是黑色。
• 任意一节点到每个叶子节点的路径都包含数量相同的黑节点。

3、B-Tree

红黑树缺点，如上图，当持续有序递增加入节点数据时，会出现左空右满的情况，导致树的高度太高
优化： 限制高度，扩展宽度。每个节点存储多个数据。每个又可以分叉，形成多叉平衡树。eg：

特性：

• 叶节点具有相同的深度，叶节点指针为空。
• 所有索引元素不重复。
• 节点中的数据索引从左到右递增排序。
• 当查找某个节点时，将该节点放到内存RAM中快速查找。

为什么不全部放到一个节点，一起放到RAM查找？
因为内存是有限的。因此设置每个节点大小为16KB。

4、B+Tree（B-Tree的变种）：MySql的最终选择

特性：

• 非叶子节点不存储数据（data），只存储索引（冗余），可以放更多的索引（每个节点16KB）
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问性能
  
  叶子节点存放所有Data，并以链表形式呈现，体现两大特点：大数据存储、大规模查询（有序）
  扩展——聚集索引和非聚集索引的区别：
• 聚集索引（主键、聚簇索引）：叶子节点存放整条记录（索引字段+数据），不需要回表。
• 非聚集索引（非主键、普通、二级索引）：叶子节点存放索引值和磁盘地址指针，需要回表。
  详情见——存储引擎。

5、B树和B+树的区别

a、B树的每个节点都存储了key和data，B+树的data存储在叶子节点上。节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。

b、树的所有叶子节点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

三、MySQL常见存储引擎（形容对象：表）

1、MySQL的基本逻辑架构图

由图可知，逻辑架构包括Server层和存储引擎层。其中Server层包括连接器，分析器，优化器以及执行器；存储引擎包括多种支持的存储引擎。各个逻辑部件的作用如下：

• 连接器： 验证客户端权限，建立和断开MySQL链接。
• 分析器： 进行SQL语句的语法分析。
• 优化器： 选择索引，生成具体的SQL语句执行计划。
• 执行器： 操作存储引擎，执行SQL，返回执行结果。
• 存储引擎层： 各个不同的存储引擎都提供了一些读写接口来操作数据库。

在MySQL5.5版本之后，InnoDB已经成为了其默认的存储引擎，也是大部分公司的不二选择，毕竟谁家公司会不要求数据库支持事务呢？谁家公司又可以忍受表级锁导致的读写冲突呢？
除了InnoDB以及MyISAM存储引擎外，常见的考擦存储引擎还有Memory，使用Memory作为存储引擎的表也可以叫做内存表，将数据存储在了内存中，所以适合做临时表来使用，在索引结构上支持B+树索引和Hash索引。

2、聚簇索引和非聚簇索引

a. 聚簇索引也称为主键索引，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚簇索引。因为索引（目录）只能按照一种方法进行排序。

b、非聚簇索引（普通索引）的叶子节点内容是主键的值。在InnoDB里，非主键索引也称为二级索引。这里需要注意的是：InnoDB的索引储存是聚簇索引。InnoDB里面没有非聚簇索引，更加准确的叫法是辅助索引或二级索引。非聚簇索引针对MyISAM引擎而言。

3、MyISAM存储引擎索引实现

MyISAM索引文件、数据文件是分离的（非聚簇），eg：表test_myisam的文件图如下：

结构图如下：

MyISAM存储引擎索引实现称为“非聚集索引”，B+Tree的叶子结点只存放主键的值和其内存地址指针，需要根据该地址回到内存中（test_myisam.MYD中）找到对应的一行数据，该过程也称为“回表”。
附加（草稿图）：

4、InnoDB存储引擎索引实现

(1)、InnoDB索引实现（聚集）

• 表结构文件本身就是按B+Tree组织的一个索引结构文件。
• 叶子节点包含了完整的数据记录。
• 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键?（可以叶子链表快速查找数据）。
• 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）。

(2)、结构
eg：表test_innodb的文件图：

表的结构图：

• 当你的InnoDB表没有设置主键，它会自动帮你选一个能够唯一标识字段（无重复）作为默认字段，如果找不到该字段，则自动生成一列作为标识性字段来维护（建立）一棵B+Tree。
• 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键?
  （可以叶子链表快速查找数据）因为我们B+Tree结构每个节点从左到右是依次递增的。意味着我们存放该索引值时要比较大小，整型数据易于比较，如果是字符串“ABCD”一类的，要逐个字母去从头比较到尾。而且整型占内存小（2个字节）。另外，如果该字段已经告诉计算机为“自增”就不必再比较排序大小了。

附加（草稿图）：

5、Hash索引

相当于每个索引做一次Hash结果（加密），就可以快速找到对应数据在磁盘的位置（地址）。
缺点：像select * from t where t.col = 6 单个索引值来说速度最快（所有索引数据结构中），但不支持连续查询，eg：“coll>6”。而B+Tree叶子结点是递增且都由指针链接，如找“t>20”，只需找叶子节点20再顺着指针向后找即可。非常方便千万级数据规模查询。

6、索引最左前缀原理

四、MySQL常见面试题

1、B树和B+树的区别？

• B树的每个节点都存储了key和data，B+树的data存储在叶子节点上。节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。
• 树的所有叶子节点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

2、为什么底层数据结构使用B+树，而不是B树？

• B+树是B树的变种，B+树的非叶子节点只用来保存索引，不存储数据，所有的数据都保存在叶子节点；而B树的非叶子节点也会保存数据。这样就使得B+树的查询效率更加稳定，均为从根节点到叶子节点的路径。
• B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，同样空间可以读入更多的节点，所以B+树的磁盘读写代价更低。

3、MySQL常见的存储引擎有哪些？

MySQL中最常见的存储引擎有InnoDB和MyISAM，它们主要区别如下：

• MyISAM不支持事务；InnoDB是事务类型的存储引擎。
• MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁。
• MyISAM引擎不支持外键；InnoDB支持外键。
• 对于count(*)查询来说MyISAM更有优势，因为其保存了行数。
• InnoDB是为处理巨大数据量时的最大性能设计的存储引擎。
• MyISAM支持全文索引（FULLTEXT）；InnoDB不支持。

总结：最主要的区别就是MyISAM表不支持事务、不支持行级锁、不支持外键。InnoDB表支持事务、支持行级锁、支持外键。

4、MySQL事务有哪些特性？

事务是单个逻辑工作单元执行的一系列操作，是一个不可分割的工作单位。满足如下的四大特性：

• 原子性（Atomicity）： 事务作为一个整体被执行，要么全部执行，要么全部不执行。
• 一致性（Consistency）： 保证数据库状态从一个一致状态转变为另一个一致状态。
• 隔离性（Isolation）： 多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）： 一个事务一旦提交，对数据库的修改应该永久保存。

5、MySQL中的锁机制？

MySQL数据库的锁分为表级锁和行级锁。从数据库的角度看，行级锁又可以分为独占锁和共享锁。

• a、独占锁（排它锁），也称X锁（Exclusive Lock）：
  独占锁锁定的资源只允许进行锁定操作的程序使用，其他任何对它的操作均不会被接受。执行数据更新命令，即INSERT、UPDATE或DELETE命令时，MySQL会自动使用独占锁。但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放。
  在select命令中使用独占锁的SQL语句为：select …for uodate。
• b、共享锁，也叫S锁（Shared Lock）：
  共享锁顾名思义，那就是其锁定的资源可以被其它用户读取，但其它用户不能修改。如果在select查询语句中要手动加入共享锁，那么对应的SQL语句为：select …lock in share mode。
  这里需要注意：
  一个事务在一行数据上加入了独占锁，那么其余事务不可以在该数据行上加入任何锁。也就是说加过排它锁的数据行在其他事务中是不能修改数据的，也不能通过for uodate和lock in share mode锁的方式查询数据，但可以直接通过select…from…查询数据，因为普通查询没有任何锁机制。

6、MySQL建表的约束条件有哪些？

约束条件是我们建表的时候对数据库表做的一个限制条件。MySQL建表时候一般有如下的五个约束条件：

• 主键约束（Primay Key Coustraint）： 唯一性，非空性。
• 唯一约束（Unique Counttraint）： 唯一性，可以空，但只能有一个。
• 检查约束（Check Counstraint）： 对该列数据的范围、格式的限制。
• 默认约束（Default Counstraint）： 该数据的默认值。
• 外键约束（Foreign Key Counstraint）： 需要建立两表间的关系并引用主表的列。