MYSQL存储引擎梳理

前言

        MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)

常用存储引擎

查看当前数据库只存的存储引擎：

show engines;

下面主要针对MyISAM和InnoDB进行详细介绍；

MyISAM引擎

1、数据文件：

MyISAM数据表在磁盘存储成3个文件，其文件名都和表名相同，扩展名分别是：

        .frm：存储数据表结构定义。

        .MYD：存储表数据。

        .MYI：存储表索引。

数据文件和索引文件可以放置在不同的目录，平均分配IO，获取更快的速度。

2、存储格式：

1）静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多。

2）动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行optimize table或myisamchk -r命令来改善性能，而且出现故障的时候恢复比较困难。

3）压缩表：使用myisampack工具创建，占用非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

3、引擎特点：

1）不支持事务（事件集合,全部事件都执行成功则事物成功）

2）表级锁定（更新时锁整个表）

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

4）只缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘I/O，但是这个缓存区只会缓存索引，而不会缓存数据。

5）读取速度快。占用资源相少。

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

4、适合业务场景：

1）不需要事物支持的业务，一般为读数据较多的网站应用。（不支持事务问题）

2）并发相对较低的业务（锁定机制问题）

3）数据修改相对较少的业务。（阻塞问题）

4）以读为主的业务，例如：www,blog,图片的数据库等业务。

5）对数据一致性要求不是很高的业务。（事务问题）

InnoDB引擎

InnoDB是一个健壮的事务型存储引擎

1、数据文件

InnoDB的数据文件由表的存储方式决定。

（1）共享表空间文件：由参数innodb_data_home_dir和innodb_data_file_path定义，用于存放数据词典和日志等。

（2）.frm：存放表结构定义。

（3）.idb：使用多表空间存储方式时，用于存放表数据和索引，若使用共享表空间存储则无此文件。

2、特点：

1）支持事物，支持4个事物隔离级别，支持多版本读。
2）行级锁定：通过索引实现，全表扫描仍然会是表锁。
3）读写阻塞与事务隔离级别相关。
4）具有非常高效的缓存特性：能缓存索引，也能缓存数据。
5）整个表和主键以Cluster方式存储，组成一颗平衡树。
6）所有Secondary Index都会保存主键信息。
7）支持分区，表空间等。
8）支持外键约束，不支持全文索引。

3、适合业务场景：

1）需要事物支持的业务
2）行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成。
3）数据更新较为频繁的场景，如：BBS,SNS等。
4）数据一致性要求较高的业务。

5）硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO。

4、调优要点：

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

2）避免全表扫描，因为会使用表锁

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

4）在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交

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

6）避免主键更新，因为会带来大量打的数据移动

总结

	InnoDB	MyISAM
事务	支持	不支持
锁	表锁、行锁	表锁
外键	支持	不支持
全文索引	不支持	支持
缓存	索引+数据	索引
读取速度	一般	快