mysql 存储引擎深入了解

基础语句

查看表语句
mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) CHARACTER SET utf8mb4 NOT NULL,
  `sex` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '性别1男2女 未知0',
  `age` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

查看表信息
mysql> show table status like 'user'\G;
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 22
    Create_time: 2019-08-02 14:49:29
    Update_time: 2019-08-06 09:37:37
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

InnoDB 存储引擎

InnoDB 是mysql 默认事务型引擎，也是最重要、使用最广泛的存储引擎。InnoDB 的性能和自动崩溃恢复的特性，使得他在非事务性存储的需求也很流行

• InnoDB的数据存储在表空间，表空间是由inno管理的一个黑盒子，由一系列数据文件组成。
• InnoDB·采用MVCC来支持高并发，并实现了四个标准的隔离级别。默认级别是REPEATEBLE READ （可重复读），并通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入。
• InnoDB表是基于聚簇索引建立的，InnoDB的索引结构和mysql其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能。不过他的二级索引（费主键所以）中必须包含主键列，所以如果逐渐很大的话，其他的所有索引都会很大，因此表索引比较多的话主键应当尽量小。
• InnoDB内部做了很多优化，

1. 从磁盘读取数据时采用可预测性预读，
2. 能自动在内存中创建hash索引以加速读操作的自适应哈希
3. 加速插入操作的插入缓冲区 等

• InnoDB通过一些机制和工具支持真正的热备份，mysql其他存储引擎不支持热备份

MyISAM 存储引擎

在mysql5.1及之前的版本， MyISAM 是默认的存储引擎，MyISAM提供了大量的特性，包括 全文索引、压缩、空间函数GIS等， 但不支持事务和行级锁，并且崩溃后无法安全恢复

存储
MyISAM会将表存储在两个文件：数据文件.MYD和索引文件.MYI，
MyISAM特性
-加锁和并发
MyISAM对整张表加锁，而不是针对行，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁，
但是在表读取查询的同时，也可以往表里插入新记录**（这被称为并发插入 CONCURRENT INSERT）**

• 修复
  MyISAM表可以手动或自动执行检查和修复操作，执行表的修复可能导致一些数据丢失，而且修复操作非常慢。

    查询表
    mysql> check table user_mi;
   +---------------+-------+----------+----------+
   | Table         | Op    | Msg_type | Msg_text |
   +---------------+-------+----------+----------+
   | study.user_mi | check | status   | OK       |
   +---------------+-------+----------+----------+
   修复表
   mysql> repair table user_mi;
   +---------------+--------+----------+----------+
   | Table         | Op     | Msg_type | Msg_text |
   +---------------+--------+----------+----------+
   | study.user_mi | repair | status   | OK       |
   +---------------+--------+----------+----------+

• 索引特性
  对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引。
  MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂查询
• MyISAM压缩表
  如果表不会在进行修改操作 那么这样的表适合MyISAM压缩表可以使用 myisampack

	xiaochai@xiaochaideMacBook-Air mysql$ myisampack  study/user_mi
	study/user_mi is too small to compress

• MyISAM性能
  MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下性能很好。但是最典型的性能问题还是表锁问题，如果你发现所有的查询都长期处于 "Locked"状态，那么毫无疑问表锁导致的。

Archive引擎

Archive 只支持 INSERT 和SELECT操作 在5.1之前也不支持索引。

• Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，比Myisam表的磁盘I/O更少。但是每次 SELECTE查询都要全表扫描。所以适合做日志和数据采集类应用，或者在一些更需要快速INSERT操作场合下使用。
• Archive引擎支持行级锁和专用的缓冲区，所以可以实现高并发插入。在一个查询开始直到返回表中存在的所有行数之前，Archive会足赤其他的SELECT执行，以实现一致性读。另外也实现了批量插入完成前对读操作不可见。

Blackhole引擎

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但是服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或只是简单的记录到日志。

CSV引擎

CSV引擎可以将普通的CSV文件（逗号分隔值的文件）作为mysql表处理，但不支持索引。可以作为一种数据交换的机制。

Federated引擎

Federated引擎是访问其他mysql服务器的一个代理，他会创建一个到远程mysql服务器的客户端连接，并将查询传输到远程服务期执行，然后提取或发送需要的数据。

Memory引擎

如果需要快速的访问数据，并且这些数据不会被修改，重启后丢失也没关系，可以使用Memory。
所有的数据存储在内存中，无需磁盘I/O,Memory表的结构重启后会保留但数据会丢失

• 用于查找lookup或映射mapping表，例如将邮编和州名映射的表
• 用于缓存周期性聚合数据的结果
• 用于保存数据分析中产生的中间数据。
  Memory支持hash索引，因此查找速度非常快。Memory是表级锁，因此并发写入性能低。
  不支持BLOB或TEXT类型的列，并且每行长度固定，即使制定了varchar也会在实际存储的时候转换成char，可能会导致内存浪费。
  如果mysql执行查询的过程中需要使用临时表保存中间结果，内部使用的临时表就是Memory表。如果中间结果太大，超过了Memory限制或包含Blob或Text字段，则会使用myisam。

Merge引擎

merge引擎是myisam的一个变种。引入分区功能后已废弃。

NDB集群引擎

集群存储引擎

第三方存储引擎 略

---

选择适合的引擎

大部分情况InnoDB都是正确的选择，Mysql5.5 开始讲InnoDB作为默认存储引擎。一句话：除非需要某些InnoDB不具备的特性，并且没有其他办法替代，否则都应选用InnoDB引擎
例如

• 如果用到全文索引，建议优先考虑InnoDB加上Sphinx组合，而不是选择myisam
• 除非万不得已，不建议混用多种存储引擎，会带来一系列复杂的问题和潜在的bug和边界问题。