chapter01_MySQL架构_5_MySQL存储引擎

最新推荐文章于 2024-07-04 21:43:30 发布

captxb

最新推荐文章于 2024-07-04 21:43:30 发布

阅读量154

点赞数

CC 4.0 BY-SA版权

分类专栏：《高性能MySQL》学习笔记

本文链接：https://blog.youkuaiyun.com/captxb/article/details/88966546

《高性能MySQL》学习笔记专栏收录该内容

12 篇文章

订阅专栏

本文详细对比了MySQL中的MyISAM和InnoDB存储引擎的特点及适用场景。MyISAM适合高并发读取操作，而InnoDB则支持事务处理，适合读写混合负载。文章还介绍了如何根据需求选择合适的存储引擎。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当创建一个表时，MySQL会在和表名同名的、以.frm为后缀的文件中存储__表的定义__

（例如在C:\Program Files\MySQL\MySQL Server 5.6\data\texas_holdem目录下存放着8个frm文件，代表8个表的定义）

这个目录下还能看到其他文件，有的代表数据记录，有的代表索引文件，这些文件的格式、后缀名是和存储引擎相关的，但是__.frm__是所有存储引擎都共有的
使用
```
  SHOW TABLE STATUS LIKE 'my_table'
```
命令可以看到表的很多信息，例如

存储引擎、行数、下一个AUTO_INCREMENT的值、数据量、索引所占空间等
MyISAM引擎

(1) MyISAM不支持事务

(2) MyISAM不支持行级锁，只支持表级锁。但是MyISAM支持__并发插入__：在SELECT查询的同时可以在同一张表内插入新行

(3) 用户可以使用
```
  CHECK TABLE my_table 
```
检查表的错误；

用
```
  REPAIR TABLE my_table
```
修复表的错误

(4) MyISAM支持__延迟更新索引__：在创建表时添加选项
```
  DELAY_KEY_WRITE
```
可以达到的效果是：索引改变时不写回磁盘而是保存在内存缓冲区，等到关闭表时再一起写回。这样做的好处是对于数据经常改变、使用频繁的表可以提高表的性能

(5) MyISAM将每个表__分成两个文件__：

1° .MYD — 数据文件

2° .MYI — 索引文件

并且.MYD和.MYI是平台通用的，可以直接从不同操作系统复制粘贴

(6) 如果某些表的数据一旦创建和填写后__永不改变__（只读不写），那么可以将MyISAM表转为__压缩的MyISAM表__，具体做法是使用myisampack工具

(7) MyISAM存储引擎顺便提供了一些小工具，例如myisamchk（检查表）、myisampack（压缩表）
InnoDB引擎

(1) InnoDB支持事务，并且特别适合处理大量短期事务（大概率正常完成不需要回滚的事务），高并发处理能力强

(2) InnoDB支持崩溃后自动恢复

(3) InnoDB将数据存储在.ibd文件中

(4) InnoDB实现4个隔离级别，默认REPEATABLE READ

并且使用__间隙锁(???)__防止幻读

(5) InnoDB基于__聚簇索引__建立（给主键自动添加聚簇索引），因此辅助索引叶结点存储的不是位置指针，而是主键值 —> 如果表上要添加很多索引，那么最好主键索引不要特别大

(6) InnoDB的另一个特征是__支持外键约束__，而外键约束这是MySQL服务器都不支持的

(7) 对于频繁查询的列，InnoDB会在内存中建立__哈希索引__

(8) MySQL默认存储引擎
还有一堆存储引擎，例如Memory、Archive、Falcon等等……，但是没细看
选取存储引擎类型要考虑的因素

(1) 事务

需要事务处理时，InnoDB是最稳定的；

不需要事务处理、主要操作是SELECT、INSERT时，MyISAM是很好的选择

(2) 并发

如果只是需要并发的SELECT和INSERT，那么MyISAM是一个合适的选择（虽然它只支持表级锁）；如果需要__混合性的并发操作__，可以考虑支持行级锁的存储引擎（例如InnoDB）

(3) 备份

(4) 崩溃后恢复

MyISAM比InnoDB表更容易出错，恢复所需的时间也更长。所以如果有大量的数据，并且系统容易崩溃，要考虑用InnoDB，即使不需要事务

(5) 特有特性

1° 应用程序依赖聚集索引优化 --> 使用了聚集索引的存储引擎(InnoDB、solidDB)

2° 支持全文检索 --> MyISAM

全文检索示例
```
  CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title,body)
  ) ENGINE=MyISAM;
```
建立表时要指定存储引擎类型为MyISAM，并且为title和body列添加全文检索FULLTEXT；

接下来插入测试数据
```
  INSERT INTO articles (title,body) VALUES 
  ('MySQL Tutorial','DBMS stands for DataBase ...'),
  ('How To Use MySQL Well','After you went through a ...'),
  ('Optimizing MySQL','In this tutorial we will show ...'),
  ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
  ('MySQL vs. YourSQL','In the following database comparison ...'),
  ('MySQL Security','When configured properly, MySQL ...');
```
然后进行一下测试
```
  SELECT * FROM articles
  WHERE MATCH (title,body) AGAINST ('database');
```
使用全文检索时必须在MATCH函数中和FULLTEXT中的列相同，AGAINST函数中添加的是检索字符
总之，MyISAM的系统消耗比较小，适合只进行SELECT、INSERT的高负载操作，不适合读写并重的场合；

InnoDB最显著的特点就是支持事务，由于支持行级锁所以适合读写并重的场景；

如果担心崩溃代理的代价过大，那么无论如何也要用InnoDB；

对于需要外键约束、聚簇索引优化、全文检索等具有特色的应用场景，根据情况选择InnoDB还是MyISAM
表存储引擎的转换

(1) 方式一：直接ALTER TABLE
```
  ALTER TABLE my_table ENGINE = FALCON;
```
这种方式适用于所有的存储引擎，但是有2个问题：

1° 实际操作的过程是：旧表到新表的逐行复制，所以大表操作很耗时

2° 原始存储引擎的特性会丢失

例如原来是InnoDB，并且表上有外键，那么经历
```
  ALTER TABLE my_table ENGINE = MYISAM;
  ALTER TABLE my_table ENGINE = INNODB;
```
的操作以后，外键就没了

(2) 方式二：转储(dump)和导入(import)

1° 使用mysqldump工具将表转储为文本文件

2° 编辑转储文本文件，修改CREATE TABLE部分的存储引擎类型

3° 导入修改后的转储文本文件

(3) 方式三：创建新表，然后DROP旧表
```
  CREATE TABLE innodb_table LIKE myisam_table;
  ALTER TABLE innodb_table ENGINE = INNODB;
  INSERT INTO innodb_table SELECT * FROM myisam_table;
  DROP TABLE myisam_table;
```