MySQL：索引，B树&B+树，存储引擎，日志文件

1，索引 

1.1，基本概念

 索引是一个单独的、存储在磁盘上的数据库结构，是按照用户任意指定字段对数据进行排序的一种数据结构。  使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。 

★索引优点★：

• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的查询速度，这也是创建索引的最主要的原因。
• 实现数据的参照完整性，可以加速表和表之间的连接查询。
• 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间，大大加快数据的检索速度。
• 索引将随机I/O变为顺序I/O，减少磁盘I/O的有效手段。
• 创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能。

★索引缺点★：

• 索引必须创建在表上，不能创建在视图上。
• 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快到达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

★索引优化★：

• 选择适当的列进行索引：选择经常用于查询条件的列进行索引，特别是在WHERE子句中经常使用的列。
• 避免创建过多的索引：过多的索引会增加写操作的开销，因为每次插入、更新或删除数据时都需要维护索引。
• 使用复合索引：复合索引包括多个列，适用于多列查询条件。确保查询中的列按照复合索引的顺序出现，以获得最佳性能。注意不要过度使用复合索引，只创建对查询有用的索引组合。
• 使用覆盖索引：覆盖索引是一个包含所有查询所需列的索引。这样的索引可以避免访问实际数据行，提高查询性能。
• 定期维护和优化索引：删除不再使用的索引，以减少维护成本。使用OPTIMIZE TABLE命令来重新组织表，以减小索引碎片。
• 使用索引提示：可以在查询中使用FORCE INDEX或USE INDEX提示，来指定使用特定的索引。这在某些情况下可以提高查询性能。
• 避免在索引列上使用函数：在查询条件中使用函数（如CONVERT、UPPER、LOWER等）可能会阻止MySQL使用索引。尽量避免在索引列上进行函数操作。
• 注意排序和分组操作：如果查询需要排序或分组，确保适当的索引覆盖了ORDER BY和GROUP BY子句中的列。
• 监控性能：使用MySQL的性能监控工具，如EXPLAIN语句和慢查询日志，来分析查询性能并发现潜在的索引问题。
• 考虑存储引擎差异：不同的MySQL存储引擎（如InnoDB和MyISAM）对索引的处理方式不同。根据存储引擎的特点来优化索引。

1.2，索引原则

索引的设计可以遵循一些已有的原则：

• 索引并非越多越好，一个表中如果有大量的索引，不仅占用大量的磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能。因为当表中数据更改的同时，索引也会进行调整和更新。
• 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
• 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
• 在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在学生表的【性别】字段上只有【男】与【女】两个不同值，因此就无需建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低更新速度。
• 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引能够确保定义的列的数据完整性，提高查询速度。
• 在频繁进行排序或分组（即进行GROUP BY或ORDER BY操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

【问题】为什么索引没有被使用？

• 若索引列出现了隐式类型转换，则MySQL不会使用索引。比如：where中字段类型为字符串，而其值为数值。
• 当like操作符的参数以"%"开头时，MySQL会认为这是一次范围查找，因此不会使用索引。
• 在使用cast函数时，需要保证字符集一样，否则MySQL不会使用索引。
• 如果where条件中含有or，如果or 条件里面有一个条件没有索引就不会使用索引。其次判断是否全表扫描。

//a,b分别建立索引，会出现全表扫码，并不会走索引。
where a = 1 or b = 1;

• 当 MySQL 表的数据量较小时，会根据查询的性质和优化器的决策来确定是否使用索引。如果索引的维护和搜索开销可能会超过直接扫描整个表的开销。那么MySQL将不使用索引。
• 在使用组合索引的时候，如果没有遵从“最左前缀”的原则进行搜索，则索引是不起作用的。（在使用复合索引（即包含多个字段的索引）进行查询时，索引会优先匹配查询条件中最左边的字段，然后再依次，满足后会停止）。

(a,b,c)建立了联合索引，相当于建立了三个索引：(a),(a,b),(a,b,c)
#查询用到(a,b,c)索引，mysql有优化器，可以自动优化索引排序
where a = 1 and b = 1 and c = 1;
where b = 1 and c = 1 and a = 1
#查询用到(a,b)索引
where a = 1 and b = 1
#查询用到(a,b)索引，排序用到 c 索引
where a = 1 and b = 1 order by c
#查询用到(a)索引，排序没有用到 c 索引，确实 b 字段的条件
where a = 1 order by c
#查找用到(a)索引，排序使用到 b,c 索引
where a = 1 order by b,c
#查找用到(a)索引，b,c 的排序不一致，排序无法使用索引
where a = 1 order by b asc, c desc
#查询用到 (a) 和部分 b，无法使用 c 索引
where a = 1 and b > 1 and c = 1
where b > 1 and a = 1 and c 1

• 在where子句中，如果索引列对应的值的第一个字符由通配符开始，索引将不被采用，然而当通配符出现在字符串其他位置时，优化器就能利用索引。
• 如果对索引字段进行函数、算术运算或其他表达式操作，那么MySQL也不使用索引。

1.3，Explain

EXPLAIN是MySQL中用于查询优化的关键字。通过执行EXPLAIN语句，可以查看MySQL查询优化器在执行查询时的查询执行计划，从而帮助我们分析查询性能问题，优化查询性能。

EXPLAIN SELECT column1, column2, ... FROM table_name WHERE condition;

• table：指示查询语句需要访问的表名或者导出表，如果是一个导出表，则该字段的值为“<derivedN>”，其中N表示此导出表的唯一编号。
• type：指示MySQL在查询执行中使用的访问策略，即MySQL在访问表时所使用的方式，例如全表扫描、按索引次序对表进行扫描等。不同的表访问方式对应不同的取值，常见的有"ALL", "index", "range"等。
• possible_keys：指示MySQL在表中寻找可用索引的过程中所考虑的所有潜在索引。如果查询涉及到多个表，则该字段会显示逗号分隔的所有潜在索引。
• key：指示MySQL实际上选择使用的索引。如果没有使用索引，则该字段的值为NULL。
• rows：表示MySQL在执行查询时估计需要扫描的记录数，该值越小越好。
• Extra： 提供了MySQL在执行查询时所采取的一些额外信息，如使用的索引类型、是否使用临时表等。

【问题】如何判断数据库的索引有没有生效？可以使用EXPLAIN语句查看索引是否正在使用。假设已经创建了book表，并已经在其year_publication字段上建立了普通索引。

EXPLAIN SELECT * FROM book WHERE year_publication=1990;

EXPLAIN语句将为我们输出详细的SQL执行信息，其中：possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。key行是MySQL实际选用的索引。 如果possible_keys行和key行都包含year_publication字段，则说明在查询时使用了该索引。

1.4，索引分类

【聚簇索引】又称聚集索引或主键索引，是指按照每张表的主键建立的索引。

• 表的数据按照主键的顺序排列，也就是说，每个表只能有一个聚簇索引；
• 主键索引的叶子节点存储的是整个数据行的记录，可以直接通过主键进行数据查询和排序；
• 聚簇索引是高度优化的索引，可以提高单条记录的查询速度。

【非聚簇索引】也称为次要索引或辅助索引，是指在表的非主键列上建立的索引。

• 叶子节点存储的是索引列的值及指向对应数据行的指针；
• 可以在多个非主键列上建立非聚簇索引；
• 非聚簇索引可以提高查询效率，但查询时需要进行两次查找，一次查找索引，一次查找数据行。

在InnoDB中，聚簇索引并不是按照主键建立的，而是按照一个被称为聚簇索引的隐藏字段建立的，这个字段的值与主键相关，但不是主键本身。因此，即使没有主键，InnoDB也可以有聚簇索引。

 【唯一索引】 唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 主键索引是一种特殊的唯一索引，不允许有空值。

【普通索引】普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

【问题】唯一索引和普通索引有什么区别，该如何选择？

【答案】唯一索引和普通索引的不同点就在于，普通索引查找到满足条件的第一个记录后，还会继续去查找下一个记录，直到碰到第一个不满足该条件的记录；而对于唯一索引来说，一旦找到一个满足条件的记录后，就会立即停止继续检索。不过这一点性能差距几乎是微乎其微，因为 InnoDB 存储引擎是按页进行读写的，所以说，当它找到符合某个条件的记录的时候，这条记录所在的数据页就已经都在内存里了。对于普通索引来说，无非就是再移动一次指针罢了。

真正能够区分唯一索引和普通索引差距的：在于 Insert Buffer / Change Buffer 的存在，因为它们只适用于非唯一的辅助索引。以 Insert Buffer 为例，当要插入的索引页不在缓冲池的时候，存储引擎并不会每插入一个新数据就去离散地访问一次磁盘页，而是先将这个操作存储到 Insert Buffer 中，在下次查询需要访问这个数据的时候，存储引擎才会将其合并（Merge）到真正的辅助索引中。这时，就相当于将多个叶子节点插入操作合并到一个操作中，这就大大提高了对于辅助索引的插入性能。

所以，在平常使用中，对于写多读少的业务，因为页面在写完以后马上被访问到的概率比较小，那么 Merge 操作就不会被频繁的执行，所以这个时候 使用非唯一索引的性能就优于唯一索引。

【单列索引】单列索引即一个索引只包含单个列，一个表可以有多个单列索引。

【组合索引】组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段 时，索引才会被使用。使用组合索引时遵循最左前缀集合。

【全文索引】使用fulltext参数可以设置索引为全文索引。全文索引只能创建在char、varchar或text类型的字段上。在查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。在默认情况下，全文索引的搜索执行方式不区分大小写。但是，当索引的列使用二进制排序后，可以执行区分大小写的全文索引。

【空间索引】使用SPATIDX参数可以设置索引为空间索引，这个索引可以被用作地理数据支持。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括geometry和point、linestring和polygon等。目前只有MyISAM存储引擎支持空间检索（InnoDB5.7.5开始支持），而且索引的字段不能为空值。 

1，创建表时，创建UNIQUE约束或者PRIMARY KEY约束时，自动创建聚集索引
2，create unique clustered/noclustered index 索引名称 on 表名(表属性 desc/asc)
clustered/noclustered：聚集索引/非聚集索引
unique：可选，是否唯一索引
desc/asc：倒序/正序

alter oldIndex rename to newIndex

drop index indexName

★SQL中的索引是非显式索引，也就是在索引创建以后，用户在索引撤消前不会再用到该索引键的名字，但是索引在用户查询时会自动起作用。

★索引一经建立，就由系统使用和维护，不需用户的干预。不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持数据的物理独立性。

★应该在使用频率高的、经常用于连接的列上建索引。

★一个表上可根据查询需要建立多个索引。索引可提高查询效率，但索引过多则耗费空间，且降低了插入、删除、更新的效率，因此不要建立过多的索引。 

1.5，MySQL中的索引分类

MySQL的所有列类型都可以被索引。MyISAM和InnoDB类型的表默认创建的都是BTREE（B树）索引；MEMORY类型的表默认使用hash索引，但是也支持B树索引；空间列类型的索引使用R-tree（空间索引）。

MySQL中的索引是在存储引擎层中实现的，而不是在服务器实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MySQL目前有如下索引：

• B-tree索引：最常见的索引类型，大部分引擎都支持B-tree索引，例如MyISASM、InnoDB、MEMORY等。
• Hash索引：只有MEMORY和NDB引擎支持，适用于简单场景。在>，<等非等值查询效率缓慢。
• R-tree索引（间隙树）：空间索引是MyISAM的一个特殊索引类型。R树是一种用于地理空间和多维数据的索引结构，它允许高效地执行范围查询和空间连接操作，比如查找包含某个点或范围内的所有对象。

R树的基本思想是将空间中的对象以层次结构组织起来，每个节点代表一个包含其子节点的矩形（或多维区域）。
这个树的叶子节点包含实际的数据对象，而内部节点包含子树的信息。
这种组织方式使得可以通过在树中向下移动来快速定位到包含查询范围的矩形，从而加速范围查询。

• Full-text：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本开始提供对全文索引的支持。

1.6，回表操作

MySQL中的"回表操作"通常指的是在使用索引的情况下，当需要从表中检索额外的数据行时，数据库引擎需要进一步访问主表来获取这些数据行。回表操作通常会影响查询的性能，因为它需要额外的I/O操作和数据访问。

CREATE INDEX idx_customer_id ON orders (customer_id);
SELECT * FROM orders WHERE customer_id = 123;

在这种情况下，如果使用了idx_customer_id索引，MySQL可以使用索引快速找到匹配的行，但如果您的查询需要检索订单的详细信息（例如，订单金额、产品列表等），MySQL需要执行回表操作。

减少回表操作：

• 覆盖索引：尽量让索引包含所有查询所需的字段，这样可以避免回表操作。在上述示例中，如果将订单详细信息也包含在idx_customer_id索引中，那么查询就可以直接从索引中获取所有信息。

• 合适的索引设计：合理设计索引，以尽量减少回表操作。根据查询需求创建合适的复合索引，以减少额外的数据访问。

• 使用覆盖索引查询：如果只需要部分字段，而不是整行数据，可以使用覆盖索引查询，以减少数据访问。

2，Hash，B树&B+树

2.1，Hash索引

哈希索引：哈希索引建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引的哈希码（Hash Code），它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中的每一行的指针。如果多个值有相同的哈希码，那么索引就会把行指针以链表的方式保存在哈希表的同一条记录中。

只有MEMORY和NDB两种引擎支持哈希索引，MEMORY引擎默认支持哈希索引，如果多个HASH值相同，出现哈希碰撞，那么索引以链表方式存储。Hash索引检索效果非常高，索引的检索可以一次到位，不像B-tree索引需要从根节点到枝节点，最后才能访问到叶节点这样多次的I/O访问，所以hash索引的查询效率要远高于B-tree索引。

【问题】那么既然Hash索引的效率要比B-tree高很多，为什么不用Hash索引？

• hash索引仅仅能满足=，in，<=>查询，不能使用范围查询。
• 优化器不能使用hash索引来加速order by操作，即hash索引无法被用来避免数据的排序操作。
• MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为hash索引的MEMORY表，那么会影响一些查询的执行效率。
• 只能使用整个关键字来搜索一行，即hash索引不能利用部分索引键查询。
• hash索引在任何时候都不能避免表扫描。
• hash索引遇到大量hash值相等的情况后性能并不一定会比B-tree索引高。

【问题】为什么不使用红黑树？

• B+树的每个节点可以存储更多的数据，因为B+树的非叶子节点只存储索引信息，而不存储数据。这样，每个节点可以存储更多的关键字，减少了磁盘I/O次数。
• B+树的叶子节点形成了一个有序链表，可以方便地进行范围查询和顺序遍历。而红黑树则需要进行中序遍历才能得到有序数据，这在磁盘存储上是不利的。
• B+树的深度相对于红黑树来说更浅，因为B+树的每个节点可以存储更多的数据。这意味着在查询时，需要访问的磁盘块数量更少，减少了磁盘I/O的次数。

【问题】为什么使用B+树？

• 索引为了提高数据检索速度，通常使用B+树结构。B+树是一种高效的数据结构，与二叉查找树和平衡二叉树相比，B+树更适合在磁盘等外存储器上进行操作。
• 与B树相比，B+树的非叶子节点只存储索引信息，不存储数据信息，所有数据都存放在叶子节点上。这样可以使得在进行范围查询的时候，只需要遍历叶子节点即可。而在B树中，由于非叶子节点和叶子节点都存储数据信息，因此进行范围查询时需要遍历整棵树，效率较低。
• 另外，B+树叶子节点使用链表连接，可以便于范围查询和排序操作。同时，B+树采用了内部节点分裂技术，保证了插入、删除操作时的平衡性，也减少了树的高度，提升了访问速度。

【问题】Mysql中的一条数据在哪里存储的？在 InnoDB 存储引擎中，一行记录的数据是存储在 B+ 树的叶子节点中的。

【问题】Mysql的B+树一般多少层？一般B+树高大约为1~3层（通过主键索引查询，通常磁盘io数为1~3次），可容纳记录数约2000w条。一个非叶节点可容纳约1170个指针，这里假设一行记录数据大小为 1k，那么底层叶节点一页16k就能存16条记录。叶节点数 * 一个叶节点能存放的记录数 = 1170 * 16 = 18720条记录数。高度为3的B+树能存的记录数为：1170*1170*16=21902400，2190w条记录，约2000w条记录。

【问题】B+树如何范围查询？在B+树进行范围查询时，首先需要找到包含目标区间左端点的叶子结点，然后从这个叶子结点开始，依次遍历其后继指针，直到找到最后一个在目标区间内的叶子结点或者找到第一个不在目标区间内的叶子结点。在遍历的过程中，将遇到的所有在目标区间内的结点的关键字值返回即为答案。由于B+树叶子结点是按照关键字顺序排列的，因此可以方便地对结果进行排序，并且不需要去重。因为 B+ 树的非叶子节点不存储数据，所以在遍历之前可以通过查找非叶子节点中包含的关键字信息来缩短遍历路径，提高查找效率。

【问题】B+树的退化？B+树退化通常指在插入或删除元素后，为保持平衡，需要向上递归更新祖先结点的情况。如果这个递归过程一直进行下去，那么B+树将会退化成一个链表，其查询和插入、删除操作的效率都将大大降低。

2.2，B树&B+树

B树和B+树是常用的数据结构，用于在存储介质上高效地存储和查找数据。B＋树的查找和Ｂ树一样，类似于二叉查找树。起始于根节点，自顶向下遍历树，选择其分离值在要查找值的任意一边的子指针。在节点内部典型的使用是二分查找来确定这个位置。

【B树】是一种多叉树，它可以有多个子节点，每个节点可以存储多个关键字和对应的指针。B树的节点分为内部节点和叶子节点，内部节点存储关键字，用于查找子节点，而叶子节点存储数据。B树的每个节点都可以存储大量的关键字，因此它可以减少I/O操作次数，提高查找效率。

【B+树】B+树与B树的不同之处在于，B+树的叶子节点只包含关键字和对应的指针，而数据则全部存储在叶子节点的同一层中。因此，B+树的叶子节点可以形成一个有序链表，从而使范围查询和顺序遍历更加高效。另外，B+树的内部节点也只存储关键字，而不存储指针，这使得B+树更加紧凑，可以存储更多的关键字。

综上所述，B树适合于随机读写，而B+树适合于顺序读写和范围查询。因此，B+树在数据库和文件系统中的应用更加广泛。

2.3，B+树对比B树的好处

B+树的优势在于查找效率上，具体说明：

• 不同的是，Ｂ＋树中间节点没有卫星数据（索引元素所指向的数据记录），只有索引，而Ｂ树每个结 点中的每个关键字都有卫星数据；这就意味着同样的大小的磁盘页可以容纳更多节点元素，在相同的数据量下，Ｂ＋树更加“矮胖”，ＩＯ操作更少。 
• 其次，因为卫星数据的不同，导致查询过程也不同；Ｂ树的查找只需找到匹配元素即可，最好情况下查找到根节点，最坏情况下查找到叶子结点，所说性能很不稳定，而Ｂ＋树每次必须查找到叶子结点，性能稳定。
• 在范围查询方面，B+树的优势更加明显B树的范围查找需要不断依赖中序遍历。首先二分查找到范围下限，在不断通过中序遍历，知道查找到范围的上限即可。整个过程比较耗时。 而B+树的范围查找则简单了许多。首先通过二分查找，找到范围下限，然后同过叶子结点的链表顺序遍历，直至找到上限即可，整个过程简单许多，效率也比较高。

2.4，插入多条数据B+树的变化

MySQL插入多个数据的过程可以概括为以下步骤：

• 将新数据插入到内存中的数据缓存（Buffer Cache）中。

• 如果数据缓存已满，MySQL会将缓存中的一些数据写回磁盘中的表文件，以腾出空间存储新数据。

• MySQL会按照表中的索引规则，将新数据插入到相应的B+树中，使得B+树保持平衡。

B+树的变化过程：

• 如果插入新数据后，插入点所在的叶子节点未满，则直接插入新数据，无需分裂节点。

• 如果插入新数据后，插入点所在的叶子节点已满，则需要将该节点分裂为两个节点，并将中间值上移到父节点中，使得B+树保持平衡。

• 如果分裂父节点后，父节点也满了，则需要继续向上递归分裂父节点，直到根节点。

• 在B+树的节点分裂过程中，需要重新分配索引键的值和指针的位置，以保证新节点的有序性。

总之，B+树在插入新数据时，会动态地调整树的结构，以保持树的平衡性和有序性。B+树的结构使得插入、查找和删除操作的时间复杂度为O(logN)，适用于处理大量数据的数据库系统。

2.5，B+树在高并发场景下的缺点

B+树是一种广泛用于数据库索引的数据结构，它具有许多优点，例如支持高效的查找、插入和删除操作等。然而，在高并发场景下，B+树也存在一些缺点，主要包括以下几点：

• 锁竞争：在高并发场景下，多个线程或进程可能同时访问同一个B+树，这时候就会出现锁竞争的情况。如果锁的粒度太细，会导致频繁的锁竞争，降低系统的性能。如果锁的粒度太粗，会导致不必要的等待时间，也会降低系统的性能。

• 磁盘IO瓶颈：B+树的数据通常存储在磁盘上，而在高并发场景下，频繁的读写操作会导致磁盘IO瓶颈，影响系统的性能。为了解决这个问题，可以采用缓存机制，将热点数据缓存在内存中，减少对磁盘的读写次数。

• 页分裂：在B+树中，当一个节点已经满了时，需要进行页分裂操作，将节点分裂成两个节点。在高并发场景下，频繁的页分裂会导致锁竞争和磁盘IO瓶颈，影响系统的性能。为了解决这个问题，可以采用预分配技术，提前为B+树分配足够的空间，减少页分裂的次数。

• 数据不均衡：在高并发场景下，B+树的数据可能会出现不均衡的情况，导致某些节点的负载过重，影响系统的性能。为了解决这个问题，可以采用动态负载均衡技术，将数据分配到多个B+树中，使得负载更加均衡。

3，InnoDB

3.1，MyISAM和InnoDB 

索引是在存储引擎中实现的。因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一 定支持所有索引类型。MySQL中索引的存储类型有两种，即B树和hash，具体和表的存储引擎相关。MyISAM和InnoDB存储引擎只支持B/B+树；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

show engines

MySQL数据库通常支持如下两种存储机制：

• MyISAM：这是MySQL早期（5.5之前）默认的存储机制，速度快，对事务支持不太好。
• InnoDB：InnoDB提供事务安全的存储机制。InnoDB通过建立行级锁来保证事务完整性，并以Oracle风格的共享锁来处理Select语句语句。系统默认启动InnoDB存储机制，如果不想再使用InnoDB表，则可以使用skip-innodb选项。

对比两种存储机制：

• 事务支持&外键约束： MyISAM 不支持事务处理&外键约束，它提供高速存储和检索，适合大量的select查询操作；而 InnoDB 支持，具有安全性高和完整性，适合大量insert和update操作。

• 锁机制： MyISAM 只支持表级锁（table-level locking），InnoDB 支持行级锁（row-level locking）和表级锁，并发性相对较高。行级锁可以最大程度地支持多个用户并发修改同一数据，而表级锁在更新时会锁住整张表，因此可能会导致其他进程阻塞。

• 并发支持：InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发。MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。

• 索引结构（数据缓存）：InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，即缓存索引，也缓存记录，因此在使用缓存时 InnoDB 性能更优。MyISAM使用非聚集索引（非聚簇索引），索引和记录分开。

• 全文索引：MyISAM 支持全文索引，而 InnoDB 在 MySQL 5.6.4 版本后才开始支持全文索引。

• 存储文件：InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB；MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从MySQL5.0开始默认限制256TB。

【问题】MyISAM速度快的原因：

• 不支持事务处理： MyISAM 不支持事务处理，因此在数据读写时不需要进行事务管理，使其在高并发的读取操作场景下具备更优的性能表现。

• 表级锁定： MyISAM 是基于表级锁定（table-level locking）实现的，当表中有多个并发操作时，MyISAM 只会对整张表加锁，不会对单独的行或列加锁，在一些大规模的批量导入或删除操作中，相对 InnoDB 等存储引擎，MyISAM 应该可以保持较快的写入速度。

• 缓冲机制： MyISAM 只缓存索引文件，而不缓存实际数据文件，这样可以避免缓存过多数据而占用过多内存，从而在一些内存资源不充足的情况下，MyISAM 可以保持高性能的读取操作。而 InnoDB 存储引擎则需要缓存数据和索引文件，也就需要更多的内存空间来维护。

如果需要在建表时显式指定存储机制，则可在标准表语法的后面添加下面任意一句。

• ENGINE=MyISAM：强制使用MyISAM存储机制。
• ENGINE=InnoDB：强制使用InnoDB存储机制。

MyISAM和InnoDB区别：

	MyISAM	InnoDB
构成	表在磁盘上存储成3个： 文件扩展名为.frm的文件存储了表定义； 文件扩展名为.MYD的文件存储了表数据； 文件扩展名为.MYI的文件存储了索引。 数据文件和索引文件可以放置在不同的目录，平均分布I/O，获得更快的速度。	表在磁盘上存储成2个文件：.frm和idb文件。 .frm文件存储了表定义。 ibd文件分为数据区和索引区，有较好的读写并发能力。
事务	MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持。	InnoDB提供事物支持、外键等高级数据库功能。InnoDB存储引擎提供了具有提交、回滚和崩溃回复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且占用更多的磁盘空间依保留数据和索引。
场景	不需要事务支持（不支持） 并发相对较低（锁定机制问题） 数据修改相对较少， 以读为主数据一致性要求不高	需要事务支持（具有较好的事务特性） 行级锁定对高并发有很好的适应能力 数据更新较为频繁的场景 数据一致性要求较高 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO
清空表	MyISAM会重建表	InnoDB是一行一行地删除，效率非常慢。
自增列操作	MyISAM为insert和update操作自动更新这一列。Auto_increment值可用alter table来重置。 对于Auto_increment类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。	如果为一个表指定Auto_increment列，那么在数据字典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在该列赋新值，自动增长计数器仅被存储在主内存中，而不是存在磁盘上。InnoDB中必须包含只有该字段的索引。
表的行数	当执行SQL语句 select count(*) from table时，MyISAM只是简单地读出保存好的行数， 需要注意的是，当count(*)语句包含where条件时，MyISAM和InnoDB的操作是一样的。	InnoDB中不保存表的具体行数，也就是说，当执行select count(*) from table时，InnoDB要扫描一遍整个表来计算行数，所以，InnoDB在做count运算时相当消耗CPU。
锁	表级锁定（更新时锁定整个表）：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小，但是也同时大大降低了其并发性能。不支持行级锁，只支持并发插入的表锁，主要用于高负载的select。	提供行锁（locking on row level），提供与Oracle类型一致的不加锁读取（non-locking read），另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，那么InnoDB表同样会锁全表，例如update table test set num=1 where name like "%ysy"。

3.2，InnoDB内存结构

InnoDB引擎架构图，主要分为内存结构和磁盘结构两部分。5.7版本。

【Buffer Pool】缓冲池，简称BP。BP以page页为单位，默认大小16k，BP底层采用链表数据结构管理page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率。

【Page管理机制】Page根据状态可以分为三种类型：

• free page：空闲page，未被使用
• clean page：被使用page，数据没有被修改过
• dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致

针对上述三种page类型，InnoDB通过三种链表结构来维护和管理：

• free list：表示空闲缓冲区，管理free page
• flush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作。
• lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少数据，占37%。

改进型LRU算法：

• 普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间从末尾淘汰
• 改进LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。
• 每当有新的page数据读取到buffer pool时，innodb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。

【Change Buffer】写缓冲区：在进行DML操作时，如果BP没有其相应的page数据，并不会立即将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。Change Buffer占用Buffer Pool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。参数innodb_change_buffer_max_size;

• 当更新更新一条记录时，该记录如果在Buffer Pool存在，直接在BufferPool修改，一次内存操作。如果在BufferPool中不存在（没有命中），会直接再ChangeBuffer进行一次内存操作，不会在去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进行磁盘读取，然后再从ChangeBuffer中读取信息合并，最终载入BufferPool中。
• 写缓冲区，仅适用于非唯一普通索引页，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufferPool中，然后再缓冲池修改，不会在ChangeBuffer操作。

【Adaptive Hash Index】自适应哈希索引，用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问频率和模式来为某些页建立哈希索引。

【Log Buffer】日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲器满时会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘IO。

• Log Buffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。
• LogBuffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size（默认为1）参数调大，减少磁盘IO频率。
  • 0：每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer-->OS Cache，刷盘 OS Cache -->磁盘文件），最多丢失1秒数据。
  • 1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作。
  • 2：事务提交，立刻写日志文件，每隔1秒进行刷盘操作（建议）。

【System Tablespace】系统表空间：包含InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区域。系统表空间页默认默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间，因为它是被多个表共享的。该空间的数据文件通过参数innodb_data_file_path控制，默认值是ibdata1:12M:autoextend(文件名为ibdata1、12M、自动扩展)。

• 数据字典：InnoDB数据字典由内部系统组成，这些表包含用于查找表、索引和表字典等对象的元数据。元数据物理上位于InnoDB系统表空间中。由于历史原因，数据字段元数据在一定程度上与InnoDB表元数据文件（.frm）中存储的信息重叠。
• Doublewrite Buffer（双写缓冲区）：位于系统表空间，是一个存储区域，在BufferPage的page页刷新到磁盘真正的位置前，会先将数据存在Doublewirte缓冲区。如果在page页写入过程中出现操作系统、存储子系统或mysqld进程崩溃，InnoDB可以在崩溃恢复期间从Doublewrite缓冲区中找到page的一个好备份。在大多数情况下，默认情况下启用双写缓冲区，要禁用Doublewrite缓冲区，可以将innodb_doublewrite设置为0。使用Doublewrite缓冲区时建议将innodb_flush_method设置为O_DIRECT。
• Redo Log（重做日志）：重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。MySQL以循环方式写入重做日志文件，记录InnoDB中所有对Buffer Pool修改的日志。当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数据更新到数据文件。读写事务在执行过程中，会不断产生redo log。默认情况下，重做日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件物理表示。
• Undo Logs（撤销日志）：撤销日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤销日志属于逻辑日志，根据每行记录进行记录。撤销日志存在于系统表空间、撤销表空间和临时表空间中。

【File-Per-Table-Tablespaces】独立表空间：默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表空间中。否则，innodb将被创建于系统表空间。每隔表文件空间又一个.bd数据文件代表，该文件默认被创建于系统数据库目录中。表空间的表文件支持动态（dynamic）和压缩（commpressed）行格式。

【General Tablespaces】通用表空间：通用表空间通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录之外的其他表空间，其可以容纳多张表，且支持所有的行格式。

【Undo Tablespaces】撤销表空间：撤销表空间由一个或多个包含Undo日志文件组成。在MySQL5.7版本之前Undo占用的是System Tablespaces共享区，从5.7开始将Undo从System Tablespaces分离出来。InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制，默认为0。参数值0表示使用系统表空间ibdata1；大于0表示使用undo表空间undo_001、undo_002等。

【Undo Tablespaces】临时表空间：分为session temporary tablespaces和global temporary tablespace两种。session temporary tablespace是存储的是用户创建的临时表和磁盘内部的临时表。global temporary tablespaces 存储用户临时表的回滚段（rollback segments）。mysql服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。

InnoDB引擎架构图，主要分为内存结构和磁盘结构两部分。8.0版本。 

MySQL 5.7版本：

• 将Undo日志表空间从共享表空间ibdata文件中分离出来，可以在安装MySQL时由用户自行指定文件大小和数量。
• 增加了temporary临时表空间，里面存储着临时表空间或临时查询结果集的数据。
• Buffer Pool大小可以动态修改，无需重启数据库实例。

MySQL 8.0版本：

• 将InnoDB表的数据字典和Undo都从共享表空间ibdata中彻底分离出来，以前需要ibdata中数据字典与独立表空间ibd文件中数据字典一致才行，8.0版本就不需要了。
• temporary临时表空间也可以配置多个物理文件，而且均为InnoDB存储引擎并能创建索引，这样加快了处理的速度。
• 用户可以像oracle数据那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中。
• 将Dublewirte Buffer从共享表空间中分离出来。

3.3，其他存储引擎

• InnoDB：支持事务，具有提交，回滚和崩溃恢复能力，事务安全。
• MyISAM：不支持事务和外键，访问速度快。
• Memory：利用内存创建表，访问速度非常快，因为数据在内存，而且默认使用Hash索引，但是一旦关闭，数据就会丢失。
• Archive：归档类型引擎，仅支持insert和select语句。
• CSV：以csv文件进行数据存储，由于文件限制，所有列必须强制指定not null，另外csv引擎不支持索引和分区，适合做数据交换的中间表。
• BlackHole：黑洞，只进不出，进来消失，所有插入数据都不会保存。
• Federated：可以访问远端MySQL数据库中的表。一个本地表，不保存数据，访问远程表内容。
• MRG_MyISAM：一组MyISAM表的组合，这些MyISAM表必须结构相同，Merge表本身没有数据，对Merge操作可以对一组MyISAM表进行操作。

特点	MyISAM	InnoDB	MEMORY	ARCHIVE	NDB	BDB	MERGE
是否默认存储引擎	是（5.5.8之前）	是（5.5.8往后）	否	否	否	否	否
存储限制	256TB	64TB	RAM	无	无	无	无
事务安全		支持				支持
锁机制	表锁	行锁	表锁	行锁	行锁	页锁	行锁
多版本并发控制		支持
空间数据类型支持	支持	支持		支持	支持	支持
空间索引支持	支持	支持（5.7.5往后）
B树索引	支持	支持	支持			支持	支持
T树索引					支持
哈希索引			支持		支持
全文索引	支持	支持（5.6.4往后）
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持		支持
数据可压缩	支持	支持		支持
数据可加密	支持	支持	支持	支持	支持
复制支持	支持	支持	支持	支持	支持	支持	支持
查询缓存	支持	支持	支持	支持	支持	支持	支持
备份/实时恢复	支持	支持	支持	支持	支持	支持	支持
集群支持					支持
空间使用	低	高	N/A	非常低		低	低
内存使用	低	高	中等	低		低	低
批量插入的速度	高	低	高	非常高	高	高	低