mysql 存储引擎

Write by lyc at 2018-07-26

一、存储引擎介绍

1.关于mysql存储引擎

• mysql存储引擎相当于linux的文件系统，数据是放在文件系统上存储的，而mysql里面存数据是放在引擎上存储的。
• 一份数据只能存在一个文件系统上面，而mysql一份数据只能存在一个引擎上面，但是不同的表和库可以使用不同的引擎。
• 不同的引擎有不同的特性。mysql5.5.5以前常用的引擎是MyISAM，mysql5.5.5以后默认的引擎是InnoDB
• MyISAM适合于读多写少，而InnoDB适合当今的高并发。

mysql > show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| 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         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

2.修改引擎

方法1：sql语句修改

mysql > ALTER TABLE db_name.table_name ENGINE = MyISAM;
mysql > ALTER TABLE db_name.table_name = InnoDB;

方法2：sed批量修改引擎

这种方式不适用与线上业务，只适合测试环境数据库导入导出。除非业务能够停下来，否则会影响数据一致性。

# 单表备份
$ mysqldump -uroot -p -S /data/3306/mysql.sock db_name table_name > /data/backup/test.sql

# 对备份的sql使用sed替换
$ sed -i 's#MyISAM#InnoDB#g' /data/backup/test.sql

# 导入数据
$ mysql -uroot -p -S /data/3306/mysql.sock db_name < /data/backup/test.sql

# 验证
mysql > show create table table_name;

二、MyISAM

1.MyISAM 引擎介绍

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

1. MySQL 5.5版本以前默认的存储引擎是MyISAM。
2. 每一个MyISAM表都对应磁盘上的三个文件：.frm,.MYD,.MYI

$ ls -l|grep "user*"
-rw-rw----. 1 mysql mysql  11K 2017-06-22 03:44 user.frm		# 文件保存表的定义，这个文件并不是MyISAM引擎的一部分，而是服务器的一部分。
-rw-rw----. 1 mysql mysql  440 2017-06-22 03:44 user.MYD		# 保存表的数据
-rw-rw----. 1 mysql mysql 2.0K 2017-06-22 03:44 user.MYI		# 表的索引文件

2.MyISAM 引擎特点

1. 不支持事务：（事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败。）
2. 表级锁定：数据更新时锁定整个表：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发的性能。
3. 读写互相阻塞：不仅会在写入的时候阻塞了读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。
4. 只会缓存索引：MyISAM可以通过key_buffer_size=1024M设置索引缓冲区大小，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。
5. 读取速度较快，占用资源相对少。
6. 不支持外键约束，但支持全文索引。
7. MyISAM引擎是MySQL5.5.5前缺省的存储引擎。

3.MyISAM 引擎生产场景

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

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

4.MyISAM 调优

1. 设置合适的索引（缓存机制）
2. 调整读写优先级，根据实际需求确保重要操作更优先执行。
3. 启动延迟插入改善大批量写入性能（降低写入频率，尽可能多条数据一次写入）
4. 尽量顺序操作让insert数据都写入到尾部，减少阻塞。
5. 分解大的时间长的SQL操作，降低单个操作的阻塞时间。
6. 降低并发数（减少对MySQL访问），某些高并发场景通过应用排队队列机制。
7. 对于相对静态（更改不频繁）的数据库数据，充分利用Query Cache或者memcached缓存服务可以极大的提高访问效率，网站动态内容静态化，减少数据库的访问。

三、Innodb

1.InnDB引擎架构

2.InnoDB引擎特点

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

3.InnoDB调优

1. 主键尽可能小，避免给Secondary index带来过大的空间负担。
2. 建立有效索引避免全表扫描，因为会使用表锁。
3. 尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO消耗。
4. 在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交。
5. 合理设置innodb_flush_log_at_trx_commit参数值，1-安全，2-高性能不要过度追求安全性。如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。
6. 避免主键更新，因为这会带来大量的数据移动。