MySQL存储引擎&索引数据结构

MySQL体系结构

连接层：用于与一些客户端建立连接服务，主要完成类似于连接处理、授权认证及相关安全方案。同时会为每个连接数据库的用户进行操作权限验证

服务层：完成核心的服务功能，如sql接口、缓存查询、sql分析优化等

引擎层：存储引擎层，存储引擎负责MySQL数据的存储和提取，索引就在本层实现。不同的存储引擎拥有的索引结构不同。

存储层：将数据存储在文件系统中，完成与存储引擎的交互

存储引擎

存储引擎主要是用于控制数据的存储与提取、建立索引的实现方式。索引就是在引擎层实现，所以不同的存储引擎对应的索引结构不同。存储引擎是基于数据表的而不是基于数据库的，所以也被称为表类型

我们可以通过sql：show engines;来查看当前数据库支持的全部引擎：

MySQL从5.5以后的默认存储引擎就是InnoDB兼顾了可靠性和高性能，即在创建表时没有显式的指定存储引擎那么就会使用默认的InnoDB。
InnoDB存储引擎特点：支持事务、行级锁、支持外键。在项目开发时，如果对事务完整性要求较高（比如转账）,需要实现并发控制，那么InnoDB是最好的选择。

并且InnoDB对于提供了对外键的支持。如果在创建表时没有显示的指定主键，那么InnoDB会为每一行数据生成一个6字节的ROWID，并以此作为主键
默认情况下每一个InnoDB表都有一个对应的.ibd表空间文件，该文件主要用于存放表结构、表数据、索引。
我们可以通过sql指令：show variables like 'innodb_file_per_table';来查看当前开关是否打开，打开情况下则表示每一个innodb表会对应自己的表空间文件，关闭时表示库中所有的innodb表对应同一个表空间文件

InnoDB逻辑存储结构：

从上至下：TableSpace表空间->Segment段->Extent区->Page页->Row行
而上面提到的.ibd文件就对应了TableSpace：

MyISAM是MySQL早期的默认存储引擎，它不支持事务、不支持外键、不支持行级锁、只支持表级锁，同时具有较好的插入和查询速度。它的表空间是分离存放的，这与InnoDB不同，MyISAM会有三个表空间文件：.sdi表结构文件、.MYD表数据、.MYI索引文件。每个MyISAM表最大索引数为64，每个索引最大列数为16

Memory存储引擎：memory表中的数据存放在内存当中，所以访问速度较高，但是无法保证数据持久性，如果出现断电就会导致内存数据被清空。所以Memory表通常用作存储临时数据或者作为缓存，memory默认采用的是hash索引

索引

索引是一种数据结构，主要用于高效获取数据、通过索引可以实现一些高效的查找算法尽量减少出现全表扫描的情况

假设要查询一张表的字段remark=0的数据，那么在无索引状态下只能从第一条数据依次向下遍历，并且要遍历整张表数据（可能不止存在一条remark=0的数据），这种情况就被称之为全表扫描，效率非常低下

索引的优势：提高数据检索效率，减少IO次数
通过索引可以列可以对数据进行排序，降低排序成本降低CPU消耗

劣势：索引列也需要有自己的存储空间，所以会占用掉一些资源
索引提高了数据的检索效率，但是会降低表的更新效率，如进行增删改等操作时效率变低

索引的数据结构

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层基于哈希表实现，不支持范围查询，用于精准检索
R-Tree(空间索引)	MySIAM引擎的特殊索引类型，很少用
Full-text(全文索引)	通过建立倒排索引的快速匹配文档方式

二叉树：小的往左大的往右，每个根节点有且仅有两个子节点。那么当数据量较大时就会造成树的层级加深，同样会降低数据的检索效率
并且如果在使用二叉树时进行顺序插入，则会形成一个链表，降低查询性能而且不便于维护，图画的一般，将就看吧

B-Tree：多路平衡查找树->B树：采用多阶B树可以缓解二叉树层级深检索慢的问题：

B树中有一个属性叫做度数，表示每一个节点的子节点个数(最多存储的指针数)。假设当前有一个五阶B树，那就表示每个子节点上可以存放四个key和五个指针
key：我们假设key为10、20、30、40
那么五个指针分别代表了五个区间，并且指向下一级的子节点，<10为一个指针、10~20为一个指针… …>40为一个指针，如下图所示：

也就是n个key有n+1个指针，在指针指向的子结点处可以再次进行划分

B+Tree：B+树所有的元素都会出现在叶子节点、叶子节点会形成单向链表
根节点只是起到了索引作用，而不存储任何真实数据，通过B+树可以快速找到数据的对应范围

上面是经典的B+树结构，而MySQL在索引结构的实现上基于B+树并且额外添加了一个指针，将原来的叶子节点单向链表改为了双向链表，提高区间的访问性能、提高排序性能

Hash索引：采用哈希算法，把键值换算为新的哈希值，再将该哈希值映射到对应的哈希表位置上。哈希索引只能用于等值进行精确匹配，不能够范围查询。并且无法利用索引进行排序。通常使用hash索引只需要检索一次即可，通过数据的哈希值定位到该数据的位置然后获取。查询效率要高于B+树索引
这与Java当中的HashMap类似。
在MySQL当中只有Memory存储引擎支持Hash索引。

为什么InnoDB存储引擎选择使用B+Tree索引结构？

1、数据量较大时，二叉树的层级过深，B+Tree层级少一些，检索效率更高
2、对于B树，无论是叶子节点还是非叶子节点都会存储数据，导致每一页存储的键值减少、指针减少，存储大量数据时同样由于树的深度过大而降低数据的检索效率
3、相比于hash索引，B+树支持范围匹配以及排序操作。而hash索引通常用于精准检索

索引分类

单列索引：一个索引只关联了一个字段
多列索引：一个索引关联了多个字段，也叫做联合索引

主键索引和唯一索引：

在创建数据表时，通常我们会显式的指定某个字段为主键（比如id字段），那么表创建完成时，就会基于主键字段来生成唯一性索引也就是唯一索引。
区分：主键是唯一索引，但是唯一索引可以有多个，而主键全表唯一

那什么是主键索引？
唯一索引的字段是可以为null值的，并且全表可以有多个。而主键索引就是在唯一索引的基础上加上了not null ，一张表中只能有一个主键索引。主键索引针对主键创建的索引，默认创建。

那么如果没有指定主键呢？
在上面存储引擎InnoDB时就已经提到，如果没有显式的指定主键，那么InnoDB将会默认创建一个六字节隐藏列的ROWID作为主键。

普通索引：普通索引，其实在创建表的时候指定的每个字段都可以作为普通索引。比如select * from student where name = ?; 这个时候就是利用name来作为索引，而这个索引就是普通索引，它可以有多个

全局索引，通常不怎么用。通常项目中有需要用到全局索引的搜索时会使用单独的搜索引擎来做。

在InnoDB存储引擎索引又分类为聚集索引和非聚集索引

聚集索引：必须有有且仅有一个聚集索引。默认是基于主键作为聚集索引。聚集索引将整行数据存储在叶子节点上，并且数据与索引放在一起。
聚集索引叶子节点上挂的是整行数据

1. 如果存在主键，那么主键索引就是聚集索引
2. 如果不存在主键，那么第一个唯一( UNIQUE )索引就是聚集索引
3. 如果都没有，请参考上述主键索引的情况，谢谢！

辅助索引：也有的叫做二级索引、非聚集索引。辅助索引将数据与索引分开存储，并且叶子节点上关联的是对应行的主键，表中可以存在多个辅助索引
辅助索引叶子上挂的是主键

我们假设有下面一个表：

CREATE TABLE `books`
(
  `bookID` int   NOT NULL AUTO_INCREMENT COMMENT '图书id',
  `bookName` varchar(100) NOT NULL COMMENT '书名',
  PRIMARY KEY (`bookID`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 33
  DEFAULT CHARSET = utf8mb3

就上述图书表而言，id作为主键，存储引擎为InnoDB，那么此时id就是对应的聚集索引。当我们执行sql

select * from books where id = ?;

时就会走聚集索引，此时我们要获取的是整行数据，直接找到对应的叶子节点即可

但是当我们执行

select * from books where name = ?;

假设name为非聚集索引，那么此时就会先检索name这个辅助索引对应的B+树，由于辅助索引叶子节点处存放的是主键而不是完整的数据行，那么此时就会通过该主键在检索聚集索引的B+树，找到对应的数据行。这个过程就被称之为回表查询

回表查询：先走辅助索引找到叶子节点上挂的主键值，拿到主键值后走聚集索引的B+树找到数据行

InnoDB主键索引的B+树高度是多高？

假设：一行数据大小为1k，存储十条数据就是10k大小。而InnoDB的指针需要占用6字节大小( 固定 )，如果主键使用int那就是4字节，bigInt就是8字节

首先我们要知道，在非叶子节点的**页(InnoDB的逻辑结构)**中存储的只有键值和指针，并且每一页的最大容量为16k，假设key数量为n，则指针数量为n+1，驾驶额键的类型为bigInt那么可以得出：n * 8 + ( n + 1 ) * 6 = 16 * 1024得出n=1170，则指针数为1171，子节点有1171个
假设树的高度为2，那么该树能存储的数据容量就是：1171 * 16
也就是1171个页吗，每页容量16k。

索引语法

创建索引：

create [UNIQUE|FULLTEXT] index index_name on table_name(index_col_name,...)

查看索引：

show index from table_name

删除索引：

drop index index_name on table_name