ch08 - MySQL存储引擎

什么是存储引擎

在经清楚什么是存储引擎之前，我们先来个比喻，我们都知道录制一个视频文件，可以转换成不同的格式如mp4 avi wmv等，而存在我们电脑的磁盘上也会存在于不同类型的文件系统中如windows里常见的ntfs fat32，存在于linux常见的ext3 ext4 xfs，但是，给我们或者用户看到实际视频内容都是一样的。直观区别是，占用系统的空间大小与清晰程度可能不一样。

那么数据库表里的数据存储在数据库里及磁盘上和上述的视频格式及存储磁盘文件系统格式特征类似，也有很多中存储方式。

但是，对于用户和应用程序来说同样一张表的数据，无论用什么引擎来存储，用户看到的数据都是一样的。不同的引擎存储，引擎功能，占用的空间大小，读取性能等可能有区别。

MySQL最常用的存储引擎为：MyISAM和InnoDB。全文索引：目前MySQL5.5版本，myisam和inondb都已经支持。

MySQL存储引擎的架构

MySQL的存储引擎是MySQL数据库的重要组成部分，MySQL常用的表的引擎为MyISAM和InnoDB两种。MySQL的每种存储引擎在MySQL里是通过插件的方式使用的，MySQL可以同时支持多种存储引擎。下面是MySQL存储引擎体系结构简图：

MyISAM引擎

MyISAM引擎是MySQL关系数据库管理系统的默认存储引擎（MySQL 5.5.5以前）。这种MySQL表存储结构从旧的ISAM代码扩展出许多有用的功能。在新版本MySQL中，InnoDB引擎由于其对事务参照完整性，以及更高的并发性等优点开始逐步的取代MyISAM引擎，

“InnoDB is the default storage engine as of MySQL 5.5.5。MyISAM: The MySQL storage engine that is used the most in Web,data warehousing,and other application environments.MyISAM is supported in all MySQL configurations,an is the default storage engine prior to MySQL 5.5.5。”

查看MySQL5.1数据库默认引擎

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `name` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

提示：MySQL 5.1数据库的默认存储引擎为MyISAM。

每一个MyISAM的表都对应于硬盘上的三个文件。这三个文件有一样的文件名，但是有不同的扩展名指示其类型用途：

---

.frm文件保存表的定义，这个文件并不是MyISAM引擎的一部分，而是服务器的一部分

.MYD保存表的数据

.MYI是表的索引文件。

# MYD和MYI是MyISAM的关键点

---

范例

$ ll /var/lib/mysql/mysql/
user.frm	# 表的定义
user.MYD	# data
user.MYI	# index

MySQL系统的表多数属于MyISAM引擎

$ ls /var/lib/mysql/mysql/
columns_priv.frm   help_keyword.frm      proc.frm                   time_zone_leap_second.MYI
columns_priv.MYD   help_keyword.MYD      proc.MYD                   time_zone.MYD
columns_priv.MYI   help_keyword.MYI      proc.MYI                   time_zone.MYI

“为什么MySQL5.5.5以前默认的是MyISAM引擎，而MySQL5.5.5以后默认是innodb”

答：和互联网发展有关，互联网诞生之初。基本上已读为主，那时机器硬件性能低。设计数据库时需要占用资源少的数据库。5.5之后选择了innodb

“为什么mysql库里内部表默认是myisam”

web2.0时代：以用户为中心的时代多数平台都是用户上传其他用户读）在这种时代myisam引擎就胜任不了了，顾mysql5。

MyISAM引擎特点

至少掌握5点

1. 不支持事务 事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败

2. 表级锁定 数据更新时锁定整个表：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时降低了其开发性能

   ---

   举例子：上厕所，还有很多小便坑，锁上外面的们，一个人上厕所，谁也去不了厕所了。

   表级锁定并发处理降低，但是提升了效率 举例：商场有小偷，不知道在哪，锁住一楼大门，然后逐一摸排

   缺点是，别人想出出不去了。但是对于保安来说是最有效的方法。

   ---

   

3. 读写互相阻塞 不仅会在写入的时候阻塞读取，myisam还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读

4. 只会缓存索引 MyISAM可以通过key_buffer_size（只是myisam）缓存索引，以大大提高访问性能较少磁盘IO，但这个缓存区只会缓存索引，而不会缓存数据

5. 读取速度较快。占用资源相对少。

6. 不支持外键约束，但支持全文索引。

7. MyISAM引擎是MySQL5.5.5 前默认的存储引擎 ( “is the default storage engine prior to MySQL 5.5.5” )。

MyISAM引擎适用的生产业务场景

1. 不需要事务支持的业务（例如转账就不行）。
2. 一般为读数据比较多的应用，读写都频繁场景不适合，读多或者写多的都适合。
3. 读写并发相对较低的业务（纯读纯写高并发也可以）（锁机制问题）。
4. 数据修改相对少的业务（阻塞问题）。
5. 已读为主的业务，例如：读数据库系统表、www blog 图片信息数据库 用户数据库 商品库等业务。
6. 对数据一致性要求不是非常高的业务（MySQL 5.6前不支持事务）。
7. 硬件资源比较差的机器都可以用myisam（占用资源少）。
8. 使用读写分离的MySQL从库可以使用myisam（5年前提到较多）

小结：单一对数据库的操作都可以使用myisam，所谓单一就是尽量纯读，或纯写（insert update delete）等

InnoDB引擎

Innodb引擎是MySQL数据库的另一个重要的存储引擎，正式成为MySQLAB所发行新版的标准，被包含在所有二进制安装包里。和其他的存储引擎相比，InnoDB引擎的优势是支持兼容ACID的事务（类似于PostgreSQL），以及参数完整性（即对外键的支持）。Oracle公司2005年手抽了Innobase。innobase采用双认证授权。它使用GNU发型，也允许其他想将InnoDB结合到商业软件的团体获得授权。

更多参考 reman-5.5-en.html-chapter/storage-engines.html

---

提示：只有test1.frm没有MyISAM对应的数据文件和索引文件了。

---

$ ll test/
ibdata1
ib_logfile0 #
ib_logfile1	# 这里是存放Innodb数据文件
ib_logfile2 #
test1.frm	
test2.frm

InnoDB引擎特点

1. 支持事务：支持4个事务隔离级别，支持多版本读。
2. 行级锁定（更新时一般是锁定当前行）：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
3. 读写阻塞与事务隔离级别相关。
4. 具有非常搞笑的缓存特性：能缓存索引，也能缓存数据。
5. 整个表和主键以Cluster方式存储，组成一颗平衡树。
6. 所有Secondary Index都会保存主键信息。
7. 支持分区，表空间，类似oracle数据库
8. 支持外键约束，5.5前不支持全文索引
9. 和MyISAM引擎比，InnoDB对硬件资源要求更高

面试必问

innodb特点：面试必答项：

1. row-level locking
2. full-text search indexs
3. data caches
4. index caches
5. transactions
6. 占用资源多
7. 读写阻塞与事务隔离级别相关
8. 外键

innodb引擎使用的生产业务场景

1. 需要事务支持的业务（具有较好的事务特性）。
2. 行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成。
3. 数据读写及更新都较为频繁的场景，如:BBS SNS 微博，微信等。
4. 数据一致性要求较高的业务，例如：充值转账，银行卡转账。
5. 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO

innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0

共享表空间对应物理数据文件

$ ll test/
ibdata1
ib_logfile0

独立表空间对应物理数据文件

innodb_file_per_table
innodb_data_home_dir=xxx

innodb引擎调优精要

1. 主键尽可能小，避免给Secondary index带来过大的空间负担

2. 建立有效索引避免全表扫描，因为会使用表锁。

3. 尽可能缓存所有的索引和数据，提高相应速度，减少磁盘IO消耗

4. 在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交。有开关可以控制提交方式；

5. 合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。

   如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事务时候不做任何操作。

6. 避免主键更新。（字段的起名长短都会影响性能）。

MySQL引擎特别说明

Feature	MyISAM	Memory	InnoDB	Archive	NDB
Storage limits	256TB	RAM	64TB	None	384EB
Transactions	No	No	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	Yes[a]	No	No
B-tree indexes	Yes	Yes	Yes	No	No
T-tree indexes	No	No	No	No	Yes
Hash indexes	No	Yes	No[b]	No	Yes
Full-text search indexes	Yes	No	Yes[c]	No	No
Clustered indexes	No	No	Yes	No	No
Data caches	No	N/A	Yes	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Compressed data	Yes[d]	No	Yes[e]	Yes	No
Encrypted data[f]	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Replication support[g]	Yes	Yes	Yes	Yes	Yes
Foreign key support	No	No	Yes	No	No
Backup / point-in-time recovery[h]	Yes	Yes	Yes	Yes	Yes
Query cache support	Yes	Yes	Yes	Yes	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

• [a] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher.
• [b] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.
• [c] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.
• [d] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.
• [e] Compressed InnoDB tables require the InnoDB Barracuda file format.
• [f] Implemented in the server (via encryption functions). Data-at-rest tablespace encryption is available in MySQL 5.7 and higher.
• [g] Implemented in the server, rather than in the storage engine.
• [h] Implemented in the server, rather than in the storage engine.

参考手册：https://dev.mysql.com/doc/refman/5.5/en/storage-engines.html

以上是myisam innodb和NDBCluster三个存储引擎是目前互联网公司应用比较多的存储引擎，特别是前两者，其他如 memory merge csv archive等存储引擎的使用场景都相对较少，初学的同学可以暂时忽略。更多可参考MySQL官方文档。

如何确定MySQL服务器有那些引擎可用？

在MySQL中使用显示引擎的命令得到一个可用引擎的列表

show engines\G
...
...
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
...
...
10 rows in set (0.01 sec)

生产场景中批量更改MySQL引擎

一般来说这样的需求不多见，但偶尔也会有，在这里我们推荐使用sed对备份内容进行引擎转换的方式，当然，不要忘记修改my.cnf使之支持并能高效的使用对应的引擎

方法1：MySQL命令语句修改

alter table test engine=innodb;

方法2：使用sed对备份内容进行引擎转换

# 此方法折腾数据不推荐使用
# 主从复制丛库换引擎，正式数据换不要用
sed -e 's#MyISAM#InnoDB#g' b.sql>b1.sql

方法3：mysql_convert_table_format命令修改

mysql_convert_table_format --host=$HOST --user=$USER --passsword=$PASS --socket=$SOCKET --type=$Engine $DB $TN

建表语句加上指定引擎：

create table test(
id int not null
) engine=innodb default charset=utf8;

混合引擎和单独innodb引擎配置差别？

有关MySQL引擎常见企业面试题

1. MySQL有那些存储引擎，各自有什么特点和区别？
2. 生产环境中如何选用MySQL的引擎

​ 在一般的既有读又有写的业务中，建议使用innodb引擎，一句话尽可能多的使用innodb引擎。

​ 纯读 纯写可用myisam。例如系统的MySQL库。

3. 不同引擎如何备份？混合引擎如何备份。

   # myisam
   mysqldump -uroot -p111 -S/data/3306/mysql.sock \
   	-A \
   	-x \
   	-B \
   	-F \
   	--master-data=2|gzip >back.sql
   
   # innodb
   mysqldump -uroot -p111 -S/data/3306/mysql.sock \
   	-A \
   	-x \
   	-B \
   	-F \
   	--master-data=2 \
   	--single-transaction|gzip >back.sql