MySQL 常用引擎及优缺点

一、MySQL 存储引擎介绍

1、什么是存储引擎

在说存储引擎前，举个例子，我们平时下载电影是不是同一个电影有 mp4、rmvb、avi 等格式，这些不同的格式的同一个电影清晰度、占用磁盘的空间可能会不同，但是它们的内容都是一样的。

存储引擎和上述所说的类似，不同的存储引擎存入到数据中存储的形式不同，所以导致占用空间、性能等不同，但是给用户展现的数据都是相同的。

2、存储引擎架构

MySQL 引入插件是存储引擎架构，允许将不同的存入引擎加载到正在运行的 MySQL 服务器中。这也就是说 MySQL 同时支持多种存储引擎。

MySQL 插件式存储引擎的体系结构

可以看到 MySQL 中有很多可插拔式的存储引擎，MyISAM、InnoDB、Archive、Memory 等，不过常用的存储引擎是 MyISAM 和 InnoDB。

二、MyISAM 引擎介绍

1、什么是 MyISAM 引擎

MyISAM 引擎是 MySQL5.5.5 版本以前数据库的默认引擎，它基于更老的 ISAM 代码，但有很多有用的扩展。

每个 MyISAM 在磁盘上存储成三个文件。这三个文件第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm 文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

[root@hadoop-slave1 mysql]#ll

total 1016

-rw-rw----. 1 mysql mysql 8820 Aug 7 23:43columns_priv.frm-rw-rw----. 1 mysql mysql 0 Aug 7 23:43columns_priv.MYD-rw-rw----. 1 mysql mysql 4096 Aug 7 23:43columns_priv.MYI-rw-rw----. 1 mysql mysql 9582 Aug 7 23:43db.frm-rw-rw----. 1 mysql mysql 1320 Aug 16 11:54db.MYD-rw-rw----. 1 mysql mysql 5120 Aug 16 12:15db.MYI-rw-rw----. 1 mysql mysql 10223 Aug 7 23:43event.frm-rw-rw----. 1 mysql mysql 0 Aug 7 23:43event.MYD-rw-rw----. 1 mysql mysql 2048 Aug 7 23:43event.MYI

...

MySQL 系统表多数都使用了 MyISAM 引擎。

2、特点

不支持事务

表级锁定(更新时锁定整个表)

读写互相阻塞(写入时阻塞读入、读时阻塞写入；但是读不会互相阻塞)

只会缓存索引(通过 key_buffer_size 缓存索引，但是不会缓存数据)

读取速度快

不支持外键，但支持全文索引

3、生产业务场景

不需要支持事务的场景(像银行转账之类的不可行)

一般读数据的较多的业务

数据修改相对较少的业务

数据一致性要求不是很高的业务

4、MyISAM 引擎调优

设置合适索引

启用延迟写入，尽量一次大批量写入，而非频繁写入

尽量顺序 insert 数据，让数据写入到尾部，减少阻塞

降低并发数，高并发使用排队机制

MyISAM 的 count 只有全表扫描比较高效，带有其它条件都需要进行实际数据访问

三、InnoDB 引擎介绍

InnoDB 支持事务(ACID)以及外键支持，InnoDB 引擎的表在磁盘上保留了一个 frm 扩展名的文件：

[root@hadoop-slave1 crm_db]#ll

...-rw-rw----. 1 mysql mysql 8622 Aug 30 10:22userinfo.frm

...

2、特点

支持事务，支持 4 个事务隔离级别

行级锁定(更新时锁定当前行)

读写阻塞与事务隔离级别相关

既能缓存索引又能缓存数据

支持外键

InnoDB 更消耗资源，读取速度没有 MyISAM 快

3、生产业务场景

需要支持事务的场景(银行转账之类)

适合高并发，行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成的

数据修改较频繁的业务

4、MyISAM 引擎调优

主键尽可能小，否则会给 Secondary index 带来负担

避免全表扫描，这会造成表锁

尽可能缓存所有的索引和数据，减少 IO 操作

避免主键更新，这会造成大量的数据移动

五、引擎相关命令

1、查看 MySQL 引擎

mysql>show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| CSV | YES | CSV storage engine | NO | NO | NO |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

8 rows in set (0.00 sec)

2、更改引擎

#法一

mysql> ALTER TABLE userinfo ENGINE=MyISAM;#法二

[root@hadoop-slave1 ~]#mysql_convert_table_format --user=root --password=123456 \

--socket=/data/3306/mysql.sock --engine=MyISAM crm userinfo

3、配置项参数

#InnoDB 用来存储数据目录信息＆其它内部数据结构的内存池的大小。你应用程序里的表越多，你需要在这里分配越多的内存。如果 InnoDB 用光了这个池内的内存，

InnoDB 开始从操作系统分配内存，并且往 MySQL 错误日志写警告信息。默认值是 1MB。

innodb_additional_mem_pool_size =16M#InnoDB 用来缓存它的数据和索引的内存缓冲区的大小。你把这个值设得越高，访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上，

你可以设置这个参数达机器物理内存大小的 80%。尽管如此，还是不要把它设置得太大，因为对物理内存的竞争可能在操作系统上导致内存调度。

innodb_buffer_pool_size =2048M#到单独数据文件和它们尺寸的路径。通过把 innodb_data_home_dir 连接到这里指定的每个路径，到每个数据文件的完整目录路径可被获得。

innodb_data_file_path = /ibdata/ibdata1:2000M:autoextend#InnoDB 中文件 I/O 线程的数量。正常地，这个参数是用默认的，默认值是 4，但是大数值对 Windows 磁盘 I/O 有益。在 Unix 上，增加这个数没有效果，InnoDB 总是使用默认值。

innodb_file_io_threads = 4

#当 innodb_flush_log_at_trx_commit 被 设置为 0，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

当这个值为 1(默认值)之时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。当设置为 2 之时，在每个提交，日志缓冲被写到文件，

但不对日志文件做到磁盘操作的刷新。

innodb_flush_log_at_trx_commit = 2

#InnoDB 事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB 在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB 用 LOCK TABLES 语句注意到锁定设置。默认值是 50 秒。

innodb_lock_wait_timeout = 120

#InnoDB 用来往磁盘上的日志文件写操作的缓冲区的大小。明智的值是从 1MB 到 8MB。默认的是 1MB。一个大的日志缓冲允许大型事务运行而不需要在事务提交之前往磁盘写日志。

因此，如果你有大型事务，使日志缓冲区更大以节约磁盘 I/O。

innodb_log_buffer_size =16M#在日志组里每个日志文件的大小。在 32 位计算机上日志文件的合并大小必须少于 4GB。默认是 5MB。明智的值从 1MB 到 N 分之一缓冲池大小，

其中 N 是组里日志文件的数目。值越大，在缓冲池越少需要检查点刷新行为，以节约磁盘 I/O。但更大的日志文件也意味这在崩溃时恢复得更慢。

innodb_log_file_size =128M#在日志组里日志文件的数目。InnoDB 以循环方式写进文件。默认是 2(推荐)。

innodb_log_files_in_group = 3...

更多请参考 MySQL 安装包中 mysql-5.5.32/support-files/my-innodb-heavy-4G.cnf 文件。

————————————————

版权声明：本文为 CSDN 博主「伍治坚」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/weixin_29175469/article/details/113115955