开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第16天，点击查看活动详情

3.引擎

3.1.引擎操作

MySQL 提供了多种引擎， 在不同的版本中支持的引擎也不同，引擎的相关操作如下：

查看当前版本支持的引擎及其功能信息：

show engines;
查看当前数据库的默认引擎：

SHOW VARIABLES LIKE 'default_storage_engine'
查看某表的引擎：

show table status where name = 'TABLE_NAME';  -- 查看表信息
show create table TABLE_NAME;  -- 查看表信息
修改表引擎：

alter table TABLE_NAME engine=innodb;
创建表的时候指定引擎：

create table TABLE_NAME(
	. . .
)engine=innodb charset=utf8;

3.2.常见引擎

MySQL 最为常用的引擎一般只有两种，MyISAM 和 InnoDB，这里给出几种常见引擎的对比：

功能/引擎 InnoDB MyISAM Memory Archive 存储限制 有 None 有 None 事务 √ 全文索引 √ B树索引 √ √ √ 哈希索引 √ √ 集群索引 √ 数据缓存 √ √ 索引缓存 √ √ √ 数据压缩 √ √ 外键 √ √ 空间占用 高 低 N/A 非常低 内存占用 高 低 中 低 批量插入速度 低 高 高 非常高 锁 行锁、表锁 表锁 表锁 行锁 InnoDB：InnoDB 是为处理巨大数据量的最大性能设计，提供了事务的存储引擎，在 SQL 查询中，可以自由地将 InnoDB 类型的表和其他 MySQL 的表类型混合起来，甚至在同一个查询中也可以混合，InnoDB 被用在众多需要高性能的大型数据库站点上。

InnoDB 不保存表的记录总数，查询某张表的记录总数会全表扫描。

InnoDB 的表锁是通过索引实现的，如果没有命中索引，则依然会使用表锁。

MyISAM：MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事物。

每个 MyISAM 类型的表都有一个 AUTO_INCREMENT 的内部列，当 INSERT 和 UPDATE 操作的时候该列被更新，同时 AUTO_INCREMENT 列将被刷新。所以说，MyISAM 类型表的 AUTO_INCREMENT 列更新比 InnoDB 类型的 AUTO_INCREMENT 更快。MyISAM 表最大索引数是 64，这可以通过重新编译来改变。每个索引最大的列数是 16。

MyISAM 表存储了数据记录总数，查询记录总数时速度非常快。

NULL 被允许在索引的列中，这个值占每个键的0~1个字节。

Memory：Memory 存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。Memory 表由客户端之间共享。

Memory 引擎每个表可以有多达 32 个索引，每个索引 16 列，以及 500 字节的最大键长度，支持 AUTO_INCREMENT 列，对可包含 NULL 值的列的索引。

当不再需要 Memory 表的内容时，要释放被 Memory 表使用的内存，应该执行 DELETE FROM 或 TRUNCATE TABLE，或者删除整个表 (使用 DROP TABLE)。

Archive：Archive 存储引擎非常适合存储大量独立的、作为历史记录的数据。区别于 InnoDB 和 MyISAM 这两种引擎，ARCHIVE 提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些。

Archive 引擎提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供 MySQL 服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变 MySQL 服务器的监控周期，例如 (CYCLE、MICROSECOND) 。

该引擎的表占用空间极小，且拥有极高的批量插入速度，因此非常适合用于存储日志类信息。

3.3.聚簇索引与非聚簇索引

聚簇索引与非聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，具体的细节依赖于实现方式。

3.3.1.聚簇索引

聚簇索引的顺序就是数据存放的顺序 (物理顺序) ，只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。一张数据表只能有一个聚簇索引。

以 InnoDB 引擎为例，InnoDB 的主键索引存放方式为聚簇索引，非主键索引存放方式为非聚簇索引，对于主键索引的 B+ 树的叶子节点包含了完整的数据信息，而对于非主键索引，叶子节点存放的是主键的 id。

InnoDB 的聚簇索引实际上在同一个结构中保存了 B+Tree 索引和数据行。当表中有聚簇索引时，它的数据实际上存储在索引的叶子页中 (叶子页中包含了行的全部数据) 。而没有聚簇索引时 B+Tree 叶子页存放的是指向数据的指针 (页是 MySQL 存储引擎最小的存储单元，InnoDB 每个页默认大小为16k )可以理解为有聚簇索引时，数据和对应的叶子页在同一页中，没有聚簇索引时，叶子页和对应的数据不在同一页中。

InnoDB 存储引擎通过主键聚集数据 (聚簇索引) ，如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有唯一索引且非空的索引， InnoDB 会自动生成一个不可见的名为 ROW_ID 的列名为 GEN_CLUST_INDEX 的聚簇索引，该列是一个6字节的自增数值，随着插入而自增。该引擎数据文件和索引绑在一起。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

InnoDB 的文件只有一个表结构文件和数据文件：

TABLE_NAME.ibd：数据和索引的文件

TABLE_NAME.frm：表定义的文件

聚簇索引的优点：

数据存放的顺序和索引顺序一致，可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。 数据访问更快，聚簇索引将索引和数据保存在同一个B-Tree中，因此从举措索引中获取数据通常比非聚簇索引查找更快。 使用覆盖索引扫描的查询可以直接使用页节点中的主键值(二级索引(非聚簇索引) 的叶子节点保存的不是指向行的物理位置的指针，而是行的主键值)。 聚簇索引的缺点：

插入速度严重依赖插入顺序。按主键的顺序插入是速度最快的。但如果不是按照主键顺序加载数据，则需在加载完成后最好使用 optimize table 整理表空间； 更新聚簇索引列的代价很高。因为会强制将每个被更新的行移动到新的位置； 基于聚簇索引的表在插入新行，或主键被更新导致需要移动行的时候，可能面临页分裂的问题。页分裂会导致表占用更多的磁盘空间； 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或由于页分裂导致数据存储不连续的时候； 聚簇索引列的选择原则：

聚簇索引尽量选择有序的列 (如AUTO_INCREMENT自增列)，这样可以保证数据行是顺序写入，对于根据主键做关联操作的性能也会更好。 最好避免随机的 (不连续且值的分布范围非常大) 聚簇索引，特别是对于 I/O 密集型的应用，从性能角度考虑，使用 UUID 来做聚簇索引会很糟糕，它使得聚簇索引的插入变得完全随机，这是最坏的情况，是的数据没有任何聚集的特性。 总结使用 UUID 做聚簇索引列的缺点： UUID字段长，索引占用的空间更大； 写入是乱序的，InnoDB 不得不频繁的做页分裂操作，以便新的行分配空间，页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页； 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者还没有被加载到缓存中，InnoDB 在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机 IO； 频繁的页分裂，页会变的稀疏并被不规则的填充，会产生空间碎片。

3.3.2.非聚簇索引

非聚簇索引也叫辅助索引、普通索引，它的叶子节点只包含一个主键值。非聚簇索引通过叶子节点指针找到数据页中的数据，所以非聚簇索引是逻辑顺序。

非聚簇索引的索引文件和数据文件分开存放，以 MyISAM 引擎为例，有三个文件组成：

• TABLE_NAME.MYD：数据文件
• TABLE_NAME.MYI：索引文件
• TABLE_NAME.frm：表定义的文件 索引文件中存放的是对应数据的文件指针，查询时会按指针去 MYD 文件中去找对应指针的数据。MyISAM 引擎的索引是非聚簇索引。

非聚集索引比想象的更大，因为二级索引的叶子节点包含了引用行的主键列

非聚集索引访问需要两次索引查找 (非聚集索引中叶子节点保存的行指针指向的是行的主键值)，对于 InnoDB 自适应哈希索引可以减少这样的重复工作

注释：

回表：通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。 覆盖索引：MySQL 可以使用索引来直接获取列的数据，这样就不需要查到索引后，通过叶子节点的指针回表读取数据记录。如果索引的叶子节点中已经包含了或者覆盖所有需要查询的字段的值，那么就没有必要再回表查询了，这种方式称之为 "覆盖索引" 。