MySQL存储引擎

MySQL存储引擎

MySQL有很多的存储引擎，每个存储引擎都有自己的特点，且存储引擎是基于表的，所以可以根据不同的应用来建立不同存储引擎表，虽然有很多存储引擎，但是使用最多的还是Innodb、MyISAM和Memory这三种存储引擎

在mysql的my.cnf文件中使用default-storage-engine=INNODB来指定默认存储引擎

所以先看一下这三种搜索引擎的区别

对比	MyISAM	Innodb	Memory
外键	不支持	支持	不支持
事务	不支持	支持	不支持
行表锁	表锁，不适合高并发	表锁、行锁，适合高并发	表锁
缓存	只缓存索引，不缓存数据	既缓存索引，又缓存数据，对内存要求较高
表空间	小	大	小
关注点	性能	事务
内存使用	低	高	高
插入速度	慢	快	快
存储文件	.frm表定义文件 .myd数据文件 .myi索引文件	.frm表定义文件 .ibd数据文件
count	有专门存储表count的地方	扫表

可以使用SHOW ENGINES来查看数据库支持的存储引擎

使用alter table <表名> engine = <存储引擎> 来修改表的存储引擎

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

Innodb存储引擎

InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。

InnoDB存储引擎总支持AUTO_INCREMENT。自动增长列的值不能为空，并且值必须唯一。MySQL中规定自增列必须为主键。在插入值的时候，如果自动增长列不输入值，则插入的值为自动增长后的值；如果输入的值为0或空（NULL），则插入的值也是自动增长后的值；如果插入某个确定的值，且该值在前面没有出现过，就可以直接插入

InnoDB还支持外键（FOREIGN KEY）。外键所在的表叫做子表，外键所依赖（REFERENCES）的表叫做父表。父表中被子表外键关联的字段必须为主键。当删除、更新父表中的某条信息时，子表也必须有相应的改变，这是数据库的参照完整性规则

InnoDB中，创建的表的表结构存储在.frm文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中

默认情况下，InnoDB的后台线程有7个，包括4个IO 线程, 1个master 线程, 1个lock monitor 线程, 一个error monitor 线程

内存

InnoDB的内存主要有以下几个部分组成：缓冲池 (buffer pool)、重做日志缓冲池(redo log buffer）以及额外的内存池（additional memory pool）

其中缓冲池占最大块内存，用来缓存各自数据，数据文件按页（每页16K）读取到缓冲池，按最近最少使用算法（LRU）保留缓存数据。

缓冲池缓冲的数据类型有：数据页、索引页、插入缓冲、自适应哈希索引、锁信息、数据字典信息等，其中数据页和索引页占了绝大部分内存。

MyISAM存储引擎

MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为frm、MYD、MYI。其中，frm文件存储表的结构；MYD文件存储数据，是MYData的缩写；MYI文件存储索引，是MYIndex的缩写

基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要用到myisampack工具，占用的磁盘空间较小

MEMORY存储引擎

MEMORY是将数据存储在内存中，每个基于MEMORY存储引擎的表实际上对应了一个磁盘文件，文件名称与表名相同，类型为frm，文件中只存储表结构，数据文件都存储在内存中，其默认使用的是hash索引，但是如果数据库重启或者崩溃，数据将消失，一般内存数据库也不会使用MySQL，而是使用redis居多

NDB存储引擎

NDB存储引擎是一种集群存储引擎，其结构是share nothing的集群结构，数据全部放在内存中(可以将非索引数据放在磁盘上)，使用主键查找的速度极快，并且通过添加NDB数据存储节点可以线性的提高数据库性能

Archive存储引擎

Archive存储引擎只支持insert和select语句，使用zlib算法将数据行进行压缩后存储，存储比一般可达1：10，适合存储归档数据，如日志信息或者数据采集

Federated存储引擎

Federated存储引擎不存放数据，只是指向一台远程MySQL数据库服务器上的表

BlackHole存储引擎

BlackHole没有实现任何存储机制，会丢弃所有插入的数据，但是服务器会记录BlackHole表的日志，可以用于复制数据到备库

https://zhhll.icu/2021/数据库/关系型数据库/MySQL/进阶/2.MySQL存储引擎/

本文由 mdnice 多平台发布