MySQL索引

转载链接：https://blog.csdn.net/Senye_ing/article/details/108951745

参考链接：https://www.cnblogs.com/boothsun/p/8970952.html

 

一、什么是索引

MySQL官方对索引的定义为：索引（Index）是帮助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个，会自动平衡，则上述结构变成：

红黑树是一种含有红黑结点并能自动平衡的二叉查找树。性质：
（1）、根结点和叶子结点都是黑色的
（2）、每个红色结点的两个结节点一定都是黑色
（3）、任意一结点到每个叶子结点的路径都包含数量相同的黑结点。

3、B-Tree

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

特性：
（1）、叶结点具有相同的深度，叶结点指针为空
（2）、所有索引元素不重复
（3）、结点中的数据索引从左到右递增排序
当查找某个结点时，将该结点放到内存RAM中快速查找。
为什么不全部放到一个结点，一起放到RAM查找，因为内存是有限的。因此设置每个结点大小为16KB(可以自行设置大小)。

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

特性：
（1）、非叶子结点不存储数据（data），只存储索引（冗余），可以放更多的索引（每个结点16KB）
（2）、叶子结点包含所有索引字段
（3）、叶子结点用指针连接，提高区间访问性能

叶子结点存放所有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索引实现（聚集）：
a、表结构文件本身就是按B+Tree组织的一个索引结构文件。
b、叶子节点包含了完整的数据记录。
c、为什么InnoDB表必须有主键，并且推荐使用整型的自增主键?（可以叶子链表快速查找数据）。
d、为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）。
(2)、结构
eg：表test_innodb的文件图：

表的结构图：

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

附加（草稿图）：

5、Hash索引

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

6、索引最左前缀原理

7、MySQL常见面试题

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

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

MySQL常见的存储引擎有哪些？
MySQL中最常见的存储引擎有InnoDB和MyISAM，它们主要区别如下：
a、MyISAM不支持事务；InnoDB是事务类型的存储引擎。
b、MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁。
c、MyISAM引擎不支持外键；InnoDB支持外键。
d、对于count(*)查询来说MyISAM更有优势，因为其保存了行数。
e、InnoDB是为处理巨大数据量时的最大性能设计的存储引擎。
f、MyISAM支持全文索引（FULLTEXT）；InnoDB不支持。

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

MySQL事务有哪些特性？
事务是单个逻辑工作单元执行的一系列操作，是一个不可分割的工作单位。满足如下的四大特性：
原子性（Atomicity）： 事务作为一个整体被执行，要么全部执行，要么全部不执行。
一致性（Consistency）： 保证数据库状态从一个一致状态转变为另一个一致状态。
隔离性（Isolation）： 多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）： 一个事务一旦提交，对数据库的修改应该永久保存。

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…查询数据，因为普通查询没有任何锁机制。

MySQL建表的约束条件有哪些？
约束条件是我们建表的时候对数据库表做的一个限制条件。MySQL建表时候一般有如下的五个约束条件：
a、主键约束（Primay Key Coustraint）：唯一性，非空性。
b、唯一约束（Unique Counttraint）：唯一性，可以空，但只能有一个。
c、检查约束（Check Counstraint）：对该列数据的范围、格式的限制。
d、默认约束（Default Counstraint）：该数据的默认值。
e、外键约束（Foreign Key Counstraint）：需要建立两表间的关系并引用主表的列。
 

 

8、B-/+Tree索引的性能分析

到这里终于可以分析B-/+Tree索引的性能了。

上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。（h表示树的高度 & 出度d表示的是树的度，即树中各个节点的度的最大值）

综上所述，用B-Tree作为索引结构效率是非常高的。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

上文还说过，B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：

 

dmax=floor(pagesize/(keysize+datasize+pointsize))dmax=floor(pagesize/(keysize+datasize+pointsize))

 

floor表示向下取整。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一棵B+树，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，上图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。