数据库基础知识

数据库进阶知识

1. 存储引擎

1.1. 概念

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除的数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使不同的存储引擎还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎.

存储引擎主要有：1.MyIsam , 2.InnoDB , 3.Memory , 4.Archive , 5.Federated

MySQL 5.5的默认存储引擎是InnoDB

1.2. InnoDB (B+树）

InnoDB底层存储结构为B+树，B+树的每个节点对应InnoDB的一个page，page大小是固定的，一般设为16k。其中非页子节点只有键值，叶子节点包含全部数据。

适用场景：

• 经常更新的表，适合处理多重并发的更新请求。
• DML操作遵循ACID模型，支持事务。
• 可以从灾难中恢复（通过 bin-log 日志等）。
• 外键约束。只有他支持外键。
• 支持自动增加列属性auto_increment。

特点：

• DML操作遵循ACID模型，支持事务；
• 行级锁，提高并发访问性能；
• 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

文件：

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结

构（frm-早期的 、sdi-新版的）、数据和索引。

参数：innodb_file_per_table

show variables like 'innodb_file_per_table';

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。 我们直接打开MySQL的

数据存放目录： C:\ProgramData\MySQL\MySQL Server 5.7\Data ， 这个目录下有很多文件

夹，不同的文件夹代表不同的数据库，我们直接打开itcast文件夹。

可以看到里面有很多的ibd文件，每一个ibd文件就对应一张表，比如：我们有一张表 account，就

有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的

索引信息。 而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一

个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表

show variables like 'innodb_file_per_table’的表结构。

逻辑存储结构：

• 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。

• 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。

• 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。

• 页 : 页是组成区的最小单元，页也是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

• 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段

1.3. MyIASM

MyIASM是mysql5.5之前默认的引擎，它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时需要锁定整个表，效率会低一些。MyIASM执行读取操作的速度很快，而且不占用大量的内存和存储资源。

缺点：不支持事务

特点：

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

1.4. Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为

临时表或缓存使用。

特点：

• 内存存放
• hash索引（默认）

1.5. Innodb、MyISAM、Memory的区别及特点

特点	Innodb	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	—	—
锁机制	行锁	表锁	表锁
B+树索引	支持	支持	支持
Hash索引	—	—	支持
全文索引	支持（5.6版本后）	支持	—
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	—	—

常见的面试题：

InnoDB和MyISAM引擎的区别是什么？

• InnoDB引擎支持事务和外键，而MyISAM不支持
• InnoDB引擎，支持行锁和表锁，而MyISAM只支持表锁，不支持行锁
• InnoDB引擎，支持外键，而MyISAM是不支持的。

1.6.存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

2. 索引

2.1. 索引概述

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2.2. 索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

索引结构	概述
B+树索引	最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引)	空间索引是MyIASM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr,Es

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况。

索引	InnoDB	MyISAM	Memory
B+树索引	支持	支持	支持
Hash索引	不支持	不支持	不支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

2.2.1. 二叉树

假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下：

上面这种二叉树是理想的情况，一旦主键顺序插入的，则会形成一个单向链表：

如果选择二叉树作为索引结构，会存在以下缺点：

• 顺序插入时，会形成一个链表，查询性能大大降低。
• 大数据量情况下，层级较深，检索速度慢。

此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树。

2.2.2. 红黑树

红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态

特点：

• 每个节点非红即黑
• 根节点总是黑色的
• 每个叶子节点都是黑色的空节点（NIL 节点）
• 如果节点是红色的，则它的子节点必须是黑色的（反之不一定）
• 从任意节点到它的叶子节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）

但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点：

• 大数据量情况下，层级较深，检索速度慢。

红黑树并不追求严格的平衡，而是大致的平衡。正因如此，红黑树的查询效率稍有下降，因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因

红黑树的应用还是比较广泛的，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异的。

所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree。

2.2.3. B-树

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：

特点：

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。
• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
• 在B树中，非叶子节点和叶子节点都会存放数据。

2.2.4 B+树

B+Tree是B-Tree的变种，B 树和 B+树中的 B 是 Balanced （平衡）的意思。们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图：

它分成两部分：

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

B 树& B+树两者有何异同呢？

• B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
• 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

2.3. 索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

2.3.1. 聚簇索引(聚集索引)&二级索引

在InnoDB存储引擎中，根据索引存储形式，分为两种：

分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚簇索引选取规则：

• 如果存在主键，主键就是聚簇索引。
• 如果不存在主键，将使用第一个唯一索引作为聚簇索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowId作为隐藏的聚簇索引。

聚簇索引和二级索引的具体结构如下：

• 聚簇索引的叶子节点下挂的是这一行的数据。
• 二级索引的叶子节点下挂的是该字段对应的主键值。

使用二级索引执行SQL语句时，具体的查找郭过程：

查询过程：

• 因为是根据name字段进行查询，所以先根据name = ‘Arm’ 到name字段的二级索引中进行匹配查找。但是二级索引中只能查到Arm对应的主键值10，
• 因为我们要查到的数据是*（一整行数据），所以还需要根据主键值，到聚簇索引中查找10对应的记录，最终找到行记录。
• 这种返回到聚簇索引查询的过程就叫做（回表查询）

回表查询： 这种先到二级索引中查找到数据，找到主键值，然后再到聚簇索引中根据主键值，获取数据的方式，就称为回表查询。

聚簇索引的优缺点：

优点：

• 查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
• 对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

• 依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

2.4. 索引语法

（1）：创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

（2）：查看索引

SHOW INDEX FROM table_name ;

（3）：删除索引

DROP INDEX index_name ON table_name ;