【图文动画详解原理系列】1.MySQL 索引原理详解

2023-09-24 19:52:01 浏览数 (1)

【图文动画详解原理系列】1.MySQL 索引原理详解

MySQL简介

MySQL是一个开放源代码的关系数据库管理系统。原开发者为瑞典的MySQL AB公司，最早是在2001年MySQL3.23进入到管理员的视野并在之后获得广泛的应用。 2008年MySQL公司被Sun公司收购并发布了首个收购之后的版本MySQL5.1，该版本引入分区、基于行复制以及plugin API。移除了原有的BerkeyDB引擎，同时，Oracle收购InnoDB Oy发布了InnoDB plugin，这后来发展成为著名的InnoDB引擎。2010年Oracle收购Sun公司，这也使得MySQL归入Oracle门下，之后Oracle发布了收购以后的首个版本5.5，该版本主要改善集中在性能、扩展性、复制、分区以及对windows的支持。目前版本已发展到5.7。

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

MySQL运行原理与基础架构

MySQL逻辑架构

MySQL运行原理与基础架构

客户端和连接服务

1.最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

核心服务

2.第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

存储引擎层

3.存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

数据存储层

4.数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

并发控制和锁的概念

当数据库中有多个操作需要修改同一数据时，不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力，这一切在MySQL中都是由服务器和存储引擎来实现的。

解决并发问题最有效的方案是引入了锁的机制，锁在功能上分为共享锁(shared lock)和排它锁(exclusive lock)，即通常说的读锁和写锁。

当一个select语句在执行时可以施加读锁，这样就可以允许其它的select操作进行，因为在这个过程中数据信息是不会被改变的这样就能够提高数据库的运行效率。当需要对数据更新时，就需要施加写锁了，不在允许其它的操作进行，以免产生数据的脏读和幻读。锁同样有粒度大小，有表级锁(table lock)和行级锁(row lock)，分别在数据操作的过程中完成行的锁定和表的锁定。这些根据不同的存储引擎所具有的特性也是不一样的。

MySQL大多数事务型的存储引擎都不是简单的行级锁，基于性能的考虑，他们一般都同时实现了多版本并发控制(MVCC)。这一方案也被Oracle等主流的关系数据库采用。它是通过保存数据中某个时间点的快照来实现的，这样就保证了每个事务看到的数据都是一致的。详细的实现原理可以参考《高性能MySQL》第三版。

MySQL 执行原理

MySQL运行原理图：

SQL语句执行的过程详细说明

如上图所示，当向MySQL发送一个请求的时候，MySQL到底做了什么：

a, 客户端发送一条查询给服务器。 b, 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。 c, 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。 d, MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询。 e, 将结果返回给客户端。

查询缓存说明

MySQL查询缓存保存查询返回的完整结构。当查询命中该缓存时，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。

查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生了变化，那么和这个表相关的所有缓存数据都将失效。

MySQL将缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了以下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能影响返回结果的信息。

当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。 当查询语句中有一些不确定的数据时，则不会被缓存。例如包含函数NOW()或者CURRENT_DATE()的查询不会缓存。包含任何用户自定义函数，存储函数，用户变量，临时表，mysql数据库中的系统表或者包含任何列级别权限的表，都不会被缓存。 有一点需要注意，MySQL并不是会因为查询中包含一个不确定的函数而不检查查询缓存，因为检查查询缓存之前，MySQL不会解析查询语句，所以也无法知道语句中是否有不确定的函数。 事实则是，如果查询语句中包含任何的不确定的函数，那么其查询结果不会被缓存，因为查询缓存中也无法找到对应的缓存结果。

解析和预处理说明：

解析器通过关键字将SQL语句进行解析，并生成对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。 预处理器则根据一些MySQL规则进行进一步检查解析书是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义

查询优化器说明：

查询优化器会将解析树转化成执行计划。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是找到这其中最好的执行计划。 生成执行计划的过程会消耗较多的时间，特别是存在许多可选的执行计划时。如果在一条SQL语句执行的过程中将该语句对应的最终执行计划进行缓存，当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过SQL语句生成执行计划的整个过程，进而可以提高语句的执行速度。

MySQL使用基于成本的查询优化器(Cost-Based Optimizer，CBO)。它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最少的一个。

优化器会根据优化规则对关系表达式进行转换，这里的转换是说一个关系表达式经过优化规则后会生成另外一个关系表达式，同时原有表达式也会保留，经过一系列转换后会生成多个执行计划，然后CBO会根据统计信息和代价模型(Cost Model)计算每个执行计划的Cost，从中挑选Cost最小的执行计划。由上可知，CBO中有两个依赖：统计信息和代价模型。统计信息的准确与否、代价模型的合理与否都会影响CBO选择最优计划 查询执行引擎说明：

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和其他的关系型数据库那样生成对应的字节码

返回结果给客户端说明：

如果查询可以被缓存，那么MySQL在这个阶段页会将结果存放到查询缓存中。 MySQL将结果集返回给客户端是一个增量、逐步返回的过程。在查询生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了

什么是索引？

索引是在存储引擎层实现的，且在 MySQL 不同存储引擎中的实现也不同。

聚簇索引

InnoDB 引擎中使用的是聚簇索引，其主索引的实现树中的叶子结点存储的是完整的数据记录。

辅助索引

而辅助索引中存储的则只是辅助键和主键的值。

这样在用辅助索引进行查询时，会先查出主键的值，然后再去主索引中根据主键的值查询目标值。(这个过程叫“回表”)。

回表

举例说明：

比如，假想一个表如下图存储了 4 行数据。其中 Id 作为主索引，Name 作为辅助索引。

Id

Name

Company

5

Gates

Microsoft

7

Bezos

Amazon

11

Jobs

Apple

14

Ellison

Oracle

对于聚簇索引，若使用主键索引进行查询，select * from tab where id = 14 这样的条件查找主键，则按照 B 树的检索算法即可查找到对应的叶节点，之后获得行数据。

若使用辅助索引进行查询，对 Name 列进行条件搜索，则需要两个步骤：

1、第一步在辅助索引 B 树中检索 Name，到达其叶子节点获取对应的主键值。 2、第二步根据主键值在主索引 B 树中再执行一次 B 树检索操作，最终到达叶子节点即可获取整行数据。

上面这个过程称为回表。

回表

回表：在数据中，当查询数据的时候，在索引中查找索引后，获得该行的 rowid，根据 rowid 再查询表中数据，就是回表。

显然，使用辅助索引出现了回表操作，这势必会影响查询性能，那有什么办法能够减少回表吗？下面将会介绍。

MySQL InnoDB 索引原理: B 树

BST -> B Tree ( AVL ) -> B Tree

二叉查找树（Binary Search Tree）

基于二分查找思想的二叉查找树 二叉查找树（Binary Search Tree）即BST树是这样的一种数据结构,如下图：

在二叉搜索树中：

1). 若任意结点的左子树不空，则左子树上所有结点的值均不大于它的根结点的值。

2). 若任意结点的右子树不空，则右子树上所有结点的值均不小于它的根结点的值。

3). 任意结点的左、右子树也分别为二叉搜索树。

这样的结构非常适合用二分查找的思维查找元素。

比如我们需要查找键值为8的记录：

先从根找起，找到 6； 显然 8>6，所以接着找到 6 的右子树，找到 7； 显然 8>7, 所以找 7 的右子树，找到了 8，查找结束。 这样一棵子树高度差不大于 1 的二叉查找树的查找效率接近与 O(log n) ;

B树

B树是一种这样的数据结构：

根结点至少有两个子结点;

每个中间节点都包含 k-1 个元素和k个子结点，其中 m/2 <= k <= m;

每一个叶子结点都包含 k-1 个元素，其中 m/2 <= k <= m;

所有的叶子结点都位于同一层;

每个结点中关键字从小到大排列，并且当该结点的孩子是非叶子结点时，该 k-1 个元素正好是 k 个子结点包含的元素的值域的分划。

可以看到，B树在保留二叉树预划分范围从而提升查询效率的思想的前提下，做了以下优化：

二叉树变成 m 叉树，这个 m 的大小可以根据单个页的大小做对应调整，从而使得一个页可以存储更多的数据，从磁盘中读取一个页可以读到的数据就更多，随机 IO 次数变少，大大提升效率。

但是我们看到，我们只能通过中序遍历查询全表，当进行范围查询时，可能会需要中序回溯。

B 树

B 树在B树的基础上加了以下优化：

1.叶子结点增加了指针进行连接，即叶子结点间形成了链表；

2.非叶子结点只存关键字 key，不再存储数据，只在叶子结点存储数据；

说明：叶子之间用双向链表连接比单向链表连接多出的好处是通过链表中任一结点都可以通过往前或者往后遍历找到链表中指定的其他结点。

这样做的好处是：

范围查询时可以通过访问叶子节点的链表进行有序遍历，而不再需要中序回溯访问结点。

非叶子结点只存储关键字key，一方面这种结构相当于划分出了更多的范围，加快了查询速度，另一方面相当于单个索引值大小变小，同一个页可以存储更多的关键字，读取单个页就可以得到更多的关键字，可检索的范围变大了，相对 IO 读写次数就降低了。

一些总结 B 树和 B 树的区别？ B 树非叶子结点和叶子结点都存储数据,因此查询数据时，时间复杂度最好为 O(1),最坏为 O(log n)。 B 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。

B 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B树只能通过中序遍历。 为什么 B 树比 B 树更适合应用于数据库索引？ B 树更加适应磁盘的特性，相比 B 树减少了 I/O 读写的次数。由于索引文件很大因此索引文件存储在磁盘上，B 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。

B 树的查询效率相比B树更加稳定，由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)。

B 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B 树的效率更高。

(https://leetcode-cn.com/circle/discuss/F7bKlM/)

以这张表为例：

代码语言：javascript复制
# 创建一个主键为 id 的表，表中有字段 k，并且在 k 上有索引。
create table T(
  `id` int(11) AUTO_INCREMENT, 
  `k` int(11) NOT NULL, 
  `name` varchar(16),
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY (`k`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB;

# 插入多条数据
insert into T values (100, 'Bob'),(200, 'Peter'),(300,'Mary');

索引的类型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，也就是数据放在主键索引上，其他索引上保存的是主键 id，这种存储方式的表称为索引组织表。

InnoDB 使用了 B 树 索引模型，所以数据都是存储在 B 树 中的。每一个索引在 InnoDB 里面对应一棵 B 树。

主键索引和二级索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引、辅助索引。

image

主键索引和非主键索引的区别

image

主键查询方式：只需要搜索 ID 这棵 B 树； 普通索引查询方式：先搜索普通索引的 B 树，得到主键索引 ID 的值，再到 ID 索引树上搜索，这个过程称为回表。

主键索引的选取规则

从空间的角度出发：主键列长度尽可能短，每个二级索引的叶子节点是主键，主键过长会导致二级索引占用空间更大。 从性能的角度出发：推荐使用自增索引，非自增主键在插入和删除的操作中，会导致页分裂和页合并。

非主键索引的优化：覆盖索引

先看下面这个 sql：

代码语言：javascript复制
select * from T where k = 100;

这个 sql 语句会在 k 索引树上找到 k=100 的记录，取得 ID=15； 再到 ID 索引树查到 ID=15 对应的记录，发生了回表，如果将 sql 语句改为

代码语言：javascript复制
select id from T where k = 100;

因为 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。

如果一个索引包含（或覆盖）所有需要查询的字段的值，称为覆盖索引，即只需扫描索引而无须回表。

业务字段做主键的条件

如果不使用自增 ID 做主键，用业务字段直接做主键，则需要满足：只有一个索引，且该索引为唯一索引。

由于没有其他索引，所以不用考虑其他索引的叶子节点大小的问题，把这个索引设置为主键，避免每次查询需要搜索两棵树。

索引的重建
  • 主键索引的重建
代码语言：javascript复制
# 正确做法
alter table T engine=InnoDB

# 错误做法
alter table T drop primary key;
alter table T add primary key(id);

直接删掉主键索引会使得所有的二级索引都失效，并且会用 ROWID 来作主键索引。

  • 非主键索引的重建
代码语言：javascript复制
alter table T drop index k;
alter table T add index(k);

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，达到省空间的目的。

索引字段数量：联合索引和单列索引

在上面的建表语句中，可以看到有两个索引，一个是 k 索引，一个是name_age 索引，不难看出，前者是单列索引，而后者就是联合索引了。

为什么会有联合索引呢？当查询条件为2个及以上时，比如当经常要用 name 和 age 去查询数据时：

代码语言：javascript复制
select * from T where name = 'Job' and age = 28;

创建一个 (name,age) 的联合索引，相当于创建了 name 和 name、age 这两个组合的索引，可以加速检索。

最左前缀原则

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。

联合索引的示例图如下：

image

索引项是按照索引定义里的字段顺序来排序的，因此在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

当创建 (a,b,c) 联合索引时，相当于创建了 (a) 单列索引、(a,b) 联合索引以及 (a,b,c) 联合索引。 想要索引生效的话，只能使用 a 和 a,b 和 a,b,c 三种组合；a,c 组合也可以，但实际上只用到了 a 的索引，并没有用到 c。

索引下推

MySQL 5.6 引入索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

比如根据（name,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据。

注意：innodb 引擎的表，索引下推只能用于二级索引。

唯一约束：唯一索引和普通索引

普通索引允许被索引的数据列包含重复的值，创建唯一索引的目的一般不是为了提高访问速度，而只是为了避免数据重复。

change buffer机制

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

唯一索引和普通索引的选择

不推荐使用唯一索引，这是因为：

从查询的角度出发：

  • 如果查询结果全在内存上：唯一索引在数据页中查找满足查询条件的第一条记录即可返回；普通索引需要再获取下一条记录，由于索引项是有序的且内存操作，多一次判断的时间损耗可忽略不计；
  • 如果查询结果不在内存上：先把数据页加载到内存中，再按照查询结果全在内存的流程处理。

从更新的角度出发：

  • 如果需要更新的记录全在内存上，直接更新内存记录并返回；
  • 如果需要更新的记录不在内存上以及部分在内存上：唯一索引需要先将需要更新的记录从磁盘中加载到内存，更新内存记录并写 redolog；普通索引将更新操作写入 change buffer，通知执行器更新完成；在下次读相关记录的时候，先把原记录读取到内存，再将 change buffer 上的操作在内存记录上回放，并写 redolog；
  • 普通索引在更新时，节省了更新时从磁盘读取记录的时间，而唯一索引在更新时，若记录不在内存，需要从磁盘读取记录到内存。

结论：change buffer 只适用于普通索引，而不适用于唯一索引。

事务

1.事务简介

简单的说事务就是一组原子性的SQL语句。可以将这组语句理解成一个工作单元，要么全部执行要么都不执行。在MySQL中可以使用如下命令操作事务：

代码语言：javascript复制
start transaction;
select ...
update ...
insert ...
commit;

注意：默认MySQL中自动提交是开启的：

MySQL运行原理与基础架构

2.事务具有ACID的特性：

原子性(atomicity):事务中的所有操作要么全部提交成功，要么全部失败回滚。

一致性(consistency):数据库总是从一个一致性状态转换到另一个一致性状态。

隔离性(isolation):一个事务所做的修改在提交之前对其它事务是不可见的。

持久性(durability):一旦事务提交，其所做的修改便会永久保存在数据库中。

3.事务的隔离级别：

**在SQL标准中定义了四种隔离级别：

READ UNCOMMITTED(读未提交)：事务中的修改即使未提交也是对其它事务可见

READ COMMITTED(读提交)：事务提交后所做的修改才会被另一个事务看见，可能产生一个事务中两次查询的结果不同。

REPEATABLE READ(可重读)：只有当前事务提交才能看见另一个事务的修改结果。解决了一个事务中两次查询的结果不同的问题。

SERIALIZABLE(串行化)：只有一个事务提交之后才会执行另一个事务。

MySQL中可以利用如下语句查询并临时修改隔离级别:

MySQL运行原理与基础架构

4.死锁

两个或多个事务在同一资源上相互占用并请求锁定对方占用的资源，从而导致恶性循环的现象。MySQL的部分存储引擎能够检测到死锁的循环依赖并产生相应的错误。InnoDB引擎解决死锁的方案是将持有最少排它锁的事务进行回滚。

插件式的存储引擎架构

MySQL采用插件式的存储引擎架构，可以根据不同的需求为不同的表设置不同的存储引擎。可以通过如下命令显示数据库中表的状态信息，以user表为例，显示如下：

MySQL运行原理与基础架构

其中，

代码语言：javascript复制
Name：显示的是表名

Engine：显示存储引擎，该表存储引擎为MyISAM

Row_format：显示行格式，对于MyISAM有Dynamic、Fixed和Compressed三种。非别表示表中有可变的数据类型，表中数据类型为固定的，以及表是压缩表的环境。

Rows：显示表中行数

Avg_row_length：平均行长度（字节）

Data_length：数据长度（字节）

Max_data_length：最大存储数据长度（字节）

Data_free：已分配但未使用的空间，包括删除数据空余出来的空间

Auto_increment：下一个插入行自动增长字段的值

Create_time：表的创建时间

Update_time：表数据的最后修改时间

Collation：表的默认字符集及排序规则

Checksum：如果启用，表示整个表的实时校验和

Create_options：创建表示的一些其它选项

Comment：额外的一些注释信息，根据存储引擎的不同表示的内容也不胫相同。

MySQL 存储引擎介绍

InnoDB引擎：

1.将数据存储在表空间中，表空间由一系列的数据文件组成，由InnoDB管理；

2.支持每个表的数据和索引存放在单独文件中(innodb_file_per_table)；

3.支持事务，采用MVCC来控制并发，并实现标准的4个事务隔离级别，支持外键；

4.索引基于聚簇索引建立，对于主键查询有较高性能；

5.数据文件的平台无关性，支持数据在不同的架构平台移植；

6.能够通过一些工具支持真正的热备。如XtraBackup等；

7.内部进行自身优化如采取可预测性预读，能够自动在内存中创建hash索引等。

MyISAM引擎：

1.MySQL5.1中默认，不支持事务和行级锁；

2.提供大量特性如全文索引、空间函数、压缩、延迟更新等；

3.数据库故障后，安全恢复性差；

4.对于只读数据可以忍受故障恢复，MyISAM依然非常适用；

5.日志服务器的场景也比较适用，只需插入和数据读取操作；

6.不支持单表一个文件，会将所有的数据和索引内容分别存在两个文件中；

7.MyISAM对整张表加锁而不是对行，所以不适用写操作比较多的场景；

8.支持索引缓存不支持数据缓存。

Archive引擎：

1.只支持insert和select操作；

2.缓存所有的写数据并进行压缩存储，支持行级锁但不支持事务；

3.适合高速插入和数据压缩，减少IO操作，适用于日志记录和归档服务器。

Blackhole引擎：

1.没有实现任何存储机制，会将插入的数据进行丢弃，但会存储二进制日志；

2.会在一些特殊需要的复制架构的环境中使用。

CSV引擎：

1.可以打开CSV文件存储的数据，可以将存储的数据导出，并利用excel打开；

2.可以作为一种数据交换的机制，同样经常使用。

Memory引擎：

1.将数据在内存中缓存，不消耗IO；

2.存储数据速度较快但不会被保留，一般作为临时表的存储被使用。

Federated引擎：

能够访问远程服务器上的数据的存储引擎。能够建立一个连接连到远程服务器。

Mrg_MyISAM引擎：

将多个MYISAM表合并为一个。本身并不存储数据，数据存在MyISAM表中间。

NDB集群引擎：

MySQL Cluster专用。

第三方存储引擎：

1.OLTP类：

XtraDB：InnoDB的改进版本。

PBXT：类似InnoDB，但提供引擎级别的复制和外键约束，适当支持SSD存储。

TokuDB(开源)：支持分形树索引结构，支持海量数据的分析。

2.列式存储引擎：MySQL默认是面向行的存储

Infobright: 支持数十TB的数据量，为数据分析和数据仓库设计的。数据高度压缩。

InfiniDB:可以在一组集群间做分布式查询，有商业版但没有典型应用案例。

3.社区存储引擎：

Aria：解决MyISAM崩溃安全恢复问题，并能够进行数据缓存。

Groona: 全文索引引擎。

QQGraph: 由Open query研发支持图操作，比如查找两点间最短距离。

SphinxSE: 该引擎为Sphinx全文索引搜索服务器提供SQL接口。

Spider: 支持sharding并能够基于分片实现并列查询。

VPForMySQL: 支持垂直分区。

存储引擎选取参考因素

1.是否有事务需求

如果需要事务支持最好选择InnoDB或者XtraDB，如果主要是select和insert操作MyISAM比较合适，一般使用日志型的应用。

2.备份操作需求

如果能够关闭服务器进行备份，那么该因素可以忽略，如果需要在线进行热备份，则InnoDB引擎是一个不错的选择。

3.故障恢复需求

在对恢复要求比较好的场景中推荐使用InnoDB，因为MyISAM数据损坏概率比较大而且恢复速度比较慢。

4.性能上的需求

有些业务需求只有某些特定的存储引擎才能够满足，如地理空间索引也只有MyISAM引擎支持。所以在应用架构需求环境中也需要管理员折衷考虑，当然从各方面比较而言，InnoDB引擎还是默认应该被推荐使用的。

5.表引擎转换方法

1.直接修改

MySQL运行原理与基础架构

2.备份修改

利用mysqldump备份工具将数据导出，修改create table语句中的存储引擎选项。注意修改的同时修改表名。

3.创建插入

MySQL运行原理与基础架构

参考资料

https://blog.csdn.net/u012006689/article/details/73195837 http://lihx8.lofter.com/post/1cc9bc99_7da03fe http://www.linuxidc.com/Linux/2014-04/99721.htm https://hoxis.github.io/mysql-zhuanlan-05-high-performance-index.html

全文检索 缓存 文件存储 存储 数据结构

0 人点赞