MySQL高级篇—存储引擎

java小豪
152 阅读27分钟

这是我参与「掘金日新计划 · 2 月更文挑战」的第 三十 天，点击查看活动详情

存储引擎

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL SERVER的功能，把真实的存取数据的功能划分为存储引擎的功能。所以在MySQL SERVER完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

MySQL中提到了存储引擎的概念。简而言之，存储引擎就是指表的类型。其实就是存储引擎以前叫做表处理器，后来改名为存储引擎

它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作

1、查看存储引擎

  • 查看MySQL提供的存储引擎
mysql> show engines;

image-20221022173959659

查询结果显示，MySQL 8 支持9种存储引擎，分别为FEDERATED、MEMORY、InnoDB、PERFORMANCE_SCHEMA、MyISAM、MRG_MYISAM、BLACKHOLE、CSV、ARCHIVE

  • Engine参数：表示存储引擎的名称
  • Support参数：表示MySQL数据库管理系统是否支持该存储引擎：YES表示支持，NO表示不支持
  • DEFAULT参数：表示系统默认支持的存储引擎。
  • Comment参数：表示对存储引擎的评论
  • Transactions参数：表示存储引擎是否支持事物：YES表示支持，NO表示不支持
  • XA参数：表示存储引擎是否支持事物处理的保存点：YES表示支持，NO表示不支持。也就是说该存储引擎是否支持部分事物回滚
mysql> show engines \G;

显示如下：

*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 4. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

2、查看系统默认的存储引擎

  • 查看默认的存储引擎
mysql> show variables like '%storage_engine%';
# 或
mysql> SELECT @@default_storage_engine;

image-20221022175336734

  • 修改默认的存储引擎

如果在创建表的语句种没有显示指定的存储引擎的话，那就会默认使用InnoDB作为表的存储引擎。。如果我们想改变表默认的存储引擎的话，可以写启动服务器的命令行：

SET DEFAULT_STORAGE_ENGINE=MyIASM;

或者修改my.cnf文件

default-storage-engine=MyIASM;
# 或者
systemctl restart mysqld.service;

3、设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为 不同的表设置不同的存储引擎 ，也就是 说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

3.1、创建表时指定存储引擎

我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎 InnoDB 。如果我们想显 式的指定一下表的存储引擎，那可以这么写：

CREATE TABLE 表名(
        建表语句;
)ENGINE = 存储引擎名称;

3.2、修改表的存储引擎

如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎：

ALTER TABLE 表名 ENGINE = 存储引擎名称;

比如我们修改一下 emp1 表的存储引擎：

mysql> ALTER TABLE emp1 ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

image-20221022181438648

这时我们再查看一下 emp1 的表结构：

mysql> show create table emp1 \G
*************************** 1. row ***************************
       Table: emp1
Create Table: CREATE TABLE `emp1` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

4、引擎介绍

4.1、InnoDB 引擎：具备外键支持功能的事务存储引擎

  • MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。

  • InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务 的完整提交(Commit)和回滚(Rollback)。

  • 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。

  • 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

  • 数据文件结构：（在《第02章_MySQL数据目录》章节已讲）

    • 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
    • 表名.ibd 存储数据和索引

  • InnoDB是 为处理巨大数据量的最大性能设计 。

    • 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除 了。比如： .frm ， .par ， .trn ， .isl ， .db.opt 等都在MySQL8.0中不存在了。

  • 对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和 索引。

  • MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响。

4.2、 MyISAM 引擎：主要的非事务处理存储引擎

  • MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级 锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。

  • 5.5之前默认的存储引擎

  • 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用

  • 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高

  • 数据文件结构：（在《第02章_MySQL数据目录》章节已讲）

    • 表名.frm 存储表结构
    • 表名.MYD 存储数据 (MYData)
    • 表名.MYI 存储索引 (MYIndex)

  • 应用场景：只读应用或者以读为主的业务

4.3、Archive 引擎：用于数据存档（了解）

  • 下表展示了存储引擎功能

image-20221022183504503

4.4、Blackhole 引擎：丢弃写操作，读操作会返回空内容（了解）

  • Blackhole引擎没有实现任何存储机制，它会丢弃所插入的数据，不做任何保存
  • 但服务器会记录Blackhole表的日志，所以可以用于复制到数据到备库，或简单记录日志

4.5、CSV引擎：存储数据时，以逗号分隔各个数据项（了解）

  • CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
  • CSV引擎可以作为一种数据交换机制，非常有用。
  • CSV存储的数据直接可以在操作系统里用文本编辑器，或者excel读取。

创建CSV表时，服务器会创建一个纯文本数据文件，其名称以表开头带有.CSV扩展名。当你将数据存储到表中时，存储引擎将其以逗号分隔值格式保存到数据文件中

使用案例如下：

mysql> create table test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.01 sec)
​
mysql> INSERT INTO test VALUES(1, "read one"), (2, "read two");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
​
mysql> SELECT * FROM test;
+---+----------+
| i | c        |
+---+----------+
| 1 | read one |
| 2 | read two |
+---+----------+
2 rows in set (0.00 sec)

4.6、Memory 引擎：置于内存的表

概述：

Memory采用的逻辑介质是 内存 ， 响应速度很快 ，但是当mysqld守护进程崩溃的时候 数据会丢失 。另 外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

主要特征：
  • Memory同时 支持哈希（HASH）索引 和 B+树索引 。
  • Memory表至少比MyISAM表要 快一个数量级 。
  • MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默 认为16MB，可以按需要进行扩大。
  • 数据文件与索引文件分开存储。
  • 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。
使用Memory存储引擎的场景：
  1. 目标数据比较小 ，而且非常 频繁的进行访问 ，在内存中存放数据，如果太大的数据会造成 内存溢 出 。可以通过参数 max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大 小。
  2. 如果 数据是临时的 ，而且 必须立即可用 得到，那么就可以放在内存中。
  3. 存储在Memory表中的数据如果突然间 丢失的话也没有太大的关系 。

4.7 Federated 引擎（了解）

  • 访问远程表 Federated引擎是访问其他MySQL服务器的一个 代理 ，尽管该引擎看起来提供了一种很好的 跨服务 器的灵活性 ，但也经常带来问题，因此 默认是禁用的 。

4.8 Merge引擎：管理多个MyISAM表构成的表集合 （了解）

4.9 NDB引擎：MySQL集群专用存储引擎 （了解）

也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集 群。

第06_索引的数据结构

1、为什么使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教科书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MySQL中也是一样的道理，进行数据查找时，首先查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描,即需要一条一条地查找记录，直到找到条件符合的记录。

image-20221022202958476

image-20221022203318072

2、索引及其优缺点

2.1、索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构。你可以理解为排好序的快速查找数据结构，满足特定的查找算法。这些数据结构以某种方式指向数据，这样可以在这些数据结构的基础上实现高级查找算法。

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时存储引擎可以定义每个表的最大索引数和最大索引长度。所以存储引擎支持每个表至少16个索引，总索引长度至少为256字节。

2.2、优点

  1. 类似大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主 要的原因。
  2. 通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。
  3. 在实现数据的 参考完整性方面，可以 加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时， 可以提高查询速度。
  4. 在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时 间 ，降低了CPU的消耗。

2.3、缺点

增加索引也有许多不利的方面，主要表现在如下几个方面：

  1. 创建索引和维护索引要 耗费时间 ，并 且随着数据量的增加，所耗费的时间也会增加。
  2. 索引需要占 磁盘空间 ，除了数据表占数据空间之 外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文 件更快达到最大文件尺寸。
  3. 虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表 中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
  4. 因此，选择使用索引时，需要综合考虑索引的优点和缺点。

提示：

索引可以提高查询的速度，但是会影响到插入记录的速度。这种情况下，最好的办法就是先删除表中的索引，然后插入数据，插入完成后再创建索引。

3、InnoDB中的索引推演

3.1、索引之前的查找

先来看一个精确匹配的例子

SELECT [列表名] FROM 表名 WHERE 列名 = xxxx;
1、在一页中查找

假设目前表中的记录比较少，所有的记录都可以被存放到一页中，在查找记录的时候可以根据搜索条件的不同

分两种情况：

  • 以主键为搜索条件

    可以在页目录中使用二分法快速定位到对应的槽，在遍历该槽对应分组中的记录即可快速找到指定的记录。

  • 以其他列作为搜索条件

    因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分算法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。

2、在很多页中查找

在很多页查找记录的话分为两个步骤：

  1. 定位到记录所在的页
  2. 从所在页内中查找相应的记录

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录 所在的页，所以只能 从第一个页 沿着 双向链表 一直往下找，在每一个页中根据我们上面的查找方式去查 找指定的记录。因为要遍历所有的数据页，所以这种方式显然是 超级耗时 的。如果一个表有一亿条记录 呢？此时 索引 应运而生。

3.2、设计索引

建一个表

mysql> CREATE TABLE index_demo(
    -> c1 INT,
    -> c2 INT,
    -> c3 CHAR(1),
    -> PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;

这个新建的 index_demo 表中有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键， 这个表使用 Compact 行格式来实际存储记录的。这里我们简化了index_demo表的行格式示意图：

image-20221023091457850

我们只在示意图里展示记录的这几个部分：

  • record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、 2 表示最小记 录、 3 表示最大记录、 1 暂时还没用过，下面讲。
  • next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用 箭头来表明下一条记录是谁。
  • 各个列的值 ：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。
  • 其他信息 ：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果就是这样：

image-20221023091643338

把一些记录放到页里的示意图就是：

image-20221023091709480

1、一个简单的索引设计方案

假设：每个数据页最多能存放3条记录（实际上一个数据页非常大），现在向表index_demo中插入3条记录：

mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

这些记录已经按照主键值的大小串联成一个单向链表了

image-20221023092401168

从图中可以看出来，index_demo表中的3条记录都被插入到编号为10 的数据页中。此时我们再插入一条记录：

mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
Query OK, 1 row affected (0.01 sec)

因为页10最多只能放3条记录，所以我们不得不分配一个新页：（工作中的数据页不会这么小）

image-20221023092918258

注意新分配的数据页编号可能是不连续的。它们只通过维护着上一个页和下一个页的编号而建立了链表关系。另外，页10中用户的最大记录的最大主键值是5，而页28中有一条记录主键值是4，因为5 > 4,不符合下一个数据页中用户记录主键值必须大于上一页中用户记录的主键值要求，所以在插入主键值为4 的记录的时候会伴随一次记录移动

image-20221023093526185

这个过程表明了在对页中记录进行增删改操作的过程中，我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立：==下一个数据页中的用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程我们称为页分裂。

我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规 律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果 我们 想快速的定位到需要查找的记录在哪些数据页 中该咋办？我们可以为快速定位记录所在的数据页而 建 立一个目录 ，建这个目录必须完成下边这些事：

  • 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
  • 给所有的页建立一个目录项。

所以我们为上边几个页做好的目录就像这样子：

image-20221023091913708

以 页28 为例，它对应 目录项2 ，这个目录项中包含着该页的页号 28 以及该页中用户记录的最小主 键值 5 。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键 值快速查找某条记录的功能了。比如：查找主键值为 20 的记录，具体查找过程分两步：

  1. 先从目录项中根据 二分法 快速确定出主键值为 20 的记录在 目录项3 中（因为 12 < 20 < 209 ），它对应的页是 页9 。
  2. 再根据前边说的在页中查找记录的方式去 页9 中定位具体的记录。
2、InnoDB中的索引方案

① 迭代1次：目录项纪录的页

经过上节的迭代，我们需要一种可以灵活管理所有目录项的方式。目录项中只有两项，为了和用户记录做以下区分，我们把表示目录项的记录称为目录项记录,使用记录头信息里的record_type属性，他们各个取值代表的意思如下：

  • 0：普通的用户记录
  • 1：目录项的记录
  • 2：最下记录
  • 3：最大记录

我们把前边使用到的目录项放到数据页中的样子就是这样：

image-20221023095303634

从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调 目录项记录 和普通的 用户记录 的不同点： -

  • 目录项记录 的 record_type 值是1，而 普通用户记录 的 record_type 值是0。
  • 目录项记录只有 主键值和页的编号 两个列，而普通的用户记录的列是用户自己定义的，可能包含 很 多列 ，另外还有InnoDB自己添加的隐藏列。
  • 了解：记录头信息里还有一个叫 min_rec_mask 的属性，只有在存储 目录项记录 的页中的主键值 最小的 目录项记录 的 min_rec_mask 值为 1 ，其他别的记录的 min_rec_mask 值都是 0 。

相同点：两者用的是一样的数据页，都会为主键值生成 Page Directory （页目录），从而在按照主键 值进行查找时可以使用 二分法 来加快查询速度。

现在以查找主键为 20 的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：

  1. 先到存储 目录项记录 的页，也就是页30中通过 二分法 快速定位到对应目录项，因为 12 < 20 < 209 ，所以定位到对应的记录所在的页就是页9。
  2. 再到存储用户记录的页9中根据 二分法 快速定位到主键值为 20 的用户记录。

② 迭代2次：多个目录项纪录的页

image-20221023100412615

从图中可以看出，我们插入了一条主键值为320的用户记录之后需要两个新的数据页：

  • 为存储该用户记录而新生成了 页31 。

  • 因为原先存储目录项记录的 页30的容量已满 （我们前边假设只能存储4条目录项记录），所以不得 不需要一个新的 页32 来存放 页31 对应的目录项。

  • 现在因为存储目录项记录的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步 骤，以查找主键值为 20 的记录为例：

    1. 确定 目录项记录页

      我们现在的存储目录项记录的页有两个，即 页30 和 页32 ，又因为页30表示的目录项的主键值的 范围是 [1, 320) ，页32表示的目录项的主键值不小于 320 ，所以主键值为 20 的记录对应的目 录项记录在 页30 中。

    2. 通过目录项记录页 确定用户记录真实所在的页 。

      在一个存储 目录项记录 的页中通过主键值定位一条目录项记录的方式说过了。

    3. 在真实存储用户记录的页中定位到具体的记录

③ 迭代3次：目录项记录页的目录页

image-20221023100646925

④ B+Tree

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第 0 层， 之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页 最多存放3条记录 ，存放目录项 记录的页 最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录 的叶子节点代表的数据页可以存放 100条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存 放 1000条目录项记录 ，那么：

  • 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
  • 如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
  • 如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
  • 如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记 录！！！

所以一般情况下，我们 用到的B+树都不会超过4层 ，那我们 通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又 因为在每个页面内有所谓的 Page Directory （页目录），所以在页面内也可以通过 二分法 实现快速 定位记录。

提示：

树的层次低，加载的IO次数越少。

3.3、常见索引概念

索引按照物理实现方式，索引可以分为2种：聚簇（聚集）和非聚簇（非聚集）索引。非聚簇索引也称为二级索引或辅助索引

1、聚簇索引

聚簇索引并不是单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引

术语“聚簇”表示数据行和相邻的键值聚簇的存储在一起

特点：

  1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

    • 页内 的记录是按照主键的大小顺序排成一个 单向链表 。
    • 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
    • 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键 大小顺序排成一个 双向链表 。

  2. B+树的 叶子节点 存储的是完整的用户记录。

    所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点：

  • 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非 聚簇索引更快
  • 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多 个数据块中提取数据，所以 节省了大量的io操作 。

缺点：

  • 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影 响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
  • 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为 不可更新
  • 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据
2、二级索引（辅助索引、非聚簇索引）

image-20221023103106621

概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根 据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就 是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！

小结：聚簇索引与非聚簇索引的原理不同

  1. 聚簇索引的叶子节点存储的也就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序
  2. 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索
  3. 使用聚簇索引的时候数据查询效率高，但如果对数据进行插入、删除、更新等操作，效率会比非聚簇索引低。
3、联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按 照 c2和c3列 的大小进行排序，这个包含两层含义：

  • 先把各个记录和页按照c2列进行排序。
  • 在记录的c2列相同的情况下，采用c3列进行排序

注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意 思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

  • 建立 联合索引 只会建立如上图一样的1棵B+树。
  • 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

3.4、InnoDB的B+树索引的注意事项

1、根页面位置万年不动

实际上 B+ 树的行成过程是这样的：

  • 每当为某个表创建一个 B+ 树索引，都会为这个索引创建一个根节点页面。最开始表里没数据，所以根节点中既没有用户记录，也没有目录项记录。
  • 当往表里插入用户记录时，先把用户记录存储到这个根节点上。
  • 当根节点页空间用完，继续插入记录，此时会将根节点中所有记录复制到一个新页（比如页 a），然后对这个新页进行页分裂，得到另一个新页（页 b）。这时候新插入的记录就根据键值大小分配到页 a 和 页 b 中。于是，根节点页就升级成了存储目录项记录的页，就需要把页a 和 页b 对应的目录项记录插入到根节点中。

另外，当一个B+树索引的根节点创建后，它的页号就不会再变。

所以只要我们对某个表建立一个索引，那么它的根节点的页号就会被记录到某个地方，后续只要 innodb引擎需要用这个索引，就会从那个固定的地方取出根节点的页号，从而访问这个索引。

2、内节点中目录项记录的唯一性

内节点：除了叶子节点外的节点。

上面我们对内节点内容的表示都是 索引列+页号，其实是不严谨的。

假设有索引所处的状态是这样的：

image-20221023105800682

此时我们如果向插入一条新的纪录，其中c1，c2，c3的值为：9，1，‘c’，那么在修改这棵B+树时就遇到了问题：此时页3中的目录项记录是由c2+页号的值构成的，而有两条记录的c2值都是一样的，那么新插入的数据应该放到页4还是页5呢？

因此，我们需要保证在B+树的同一层节点的目录项记录除页号外的字段是唯一的。 所以对于二级索引的内节点的目录项纪录的内容实际上是由三个部分组成的：

  • 索引列的值
  • 主键值
  • 页号

这样就能够确保各目录项记录除页号外是唯一的。

image-20221023105922859

3、 一个页面最少存储2条记录

image-20221023110420123

4、MyISAM中的索引方案

B树索引适用存储引擎如表所示：

索引/存储引擎MyISAMInnoDBMemory
B-Tree索引支持支持支持

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。InnoDB和MyISAM默认的索 引是Btree索引；而Memory默认的索引是Hash索引。 MyISAM引擎使用 B+Tree作为索引结构，叶子节点的data域存放的是 数据记录的地址 。

4.2、MyISAM索引的原理

下图是MyISAM索引的原理图。

MyISAM的索引方案虽然也是树形结构，但却是将索引和数据分开存储：

  • 将表中的记录按照记录插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少条记录就都在这个文件内。在插入数据时并没有可以按照主键大小排序所以我们并不能在这些数据上使用二分法进行查找。
  • 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中，MyISAM会单独为表的主键创建一个索引，只不过存储的不是完整的记录，而是主键值 + 数据记录地址的组合。

image-20221023111044289

如果我们在Col2上建立一个二级索引，则此索引的结构如下图所示：

image-20221023111114230

4.3、MyISAM与InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区 别：

① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数 据记录的地址。

③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说， InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通 过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐 含字段作为主键，这个字段长度为6个字节，类型为长整型。

5、索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

  • 空间上的代价

    每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会 占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

  • 时间上的代价

    每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每 层节点都是按照索引列的值 从小到大的顺序排序 而组成了 双向链表 。不论是叶子节点中的记录，还 是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序 而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需 要额外的时间进行一些 记录移位 ， 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果 我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

一个表上索引键的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们需要知道这些索引在那些条件下起作用的

6、MySQL数据结构选择的合理性

查找都是索引操作，一般来说索引非常大，尤其时关系型数据库，当数据量比较大的时候，索引的大小有可能几个G甚至更多，为了减少索引在内存的占用，数据库索引时存储在外部磁盘上的。当我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载，那么MySQL衡量查询效率的标准就是磁盘IO次数

６.２、Hash结构

加速查找的数据结构，常见的有两类

  • 树， 例如平衡二叉树，查询/插入/修改/删除的平均时间复杂度都是O(log2N);
  • 哈希， 例如HashMap， 查询/插入/修改/删除的平均时间复杂度都是O(1);

image-20221023152645230

image-20221023153105826

上图中哈希函数h有可能将两个不同的关键字映射到相同的位置，这叫做 碰撞 ，在数据库中一般采用 链 接法 来解决。在链接法中，将散列到同一槽位的元素放在一个链表中，如下图所示：

image-20221023153130434

实验：体会数组和hash表的查找方面的效率区别

// 算法复杂度为 O(n)
@Test
public void test1(){
    int[] arr = new int[100000];
    for(int i = 0;i < arr.length;i++){
        arr[i] = i + 1;
    }
    long start = System.currentTimeMillis();
    for(int j = 1; j<=100000;j++){
        int temp = j;
        for(int i = 0;i < arr.length;i++){
            if(temp == arr[i]){
                break;
            }
        }
    }
    long end = System.currentTimeMillis();
    System.out.println("time： " + (end - start)); //time： 823
}

//算法复杂度为 O(1)
@Test
public void test2(){
    HashSet<Integer> set = new HashSet<>(100000);
    for(int i = 0;i < 100000;i++){
        set.add(i + 1);
    }
    long start = System.currentTimeMillis();
    for(int j = 1; j<=100000;j++) {
        int temp = j;
        boolean contains = set.contains(temp);
    }
    long end = System.currentTimeMillis();
    System.out.println("time： " + (end - start)); //time： 5
}
​

Hash结构效率高，为什么索引结构要设计成树型呢？

原因1: Hash索引仅能满足(=) (<>) 和IN查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为O(n); 而树型的有序特性，依然能够保持O(log2N)的高效率。

原因2: Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序

原因3: 对于联合索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。

原因4: 对于等值查询来说，通常Hash索引的效率更高

avatar
文章
阅读
粉丝