MySQL读书笔记－存储引擎

前言

关于存储引擎的细节，请参考官方文档。如果使用InnoDB引擎，建议阅读官方手册中的InnoDB事务模型和锁一节。

数据库和表

• 在文件系统中，MySQL将每个数据库（schema）保存为数据目录下的一个子目录。
• 创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件，用于保存表的定义。
• 因为MySQL使用文件系统的目录保存数据库和表的定义，大小写敏感性和具体的平台相关。在Windows中，大小写是不敏感的，而在类UNIX中则是敏感的。
• 不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理的。可以使用SHOW TABLE STATUS命令显示表的相关信息。

通过Engine字段可以看出mysql.user表，是一个MyISAM表，表中一些字段含义：

• Name
  表名。

• Engine
  表的存储引擎类型。

• Row_format
  行的格式。对于MyISAM表，可选的值为Dynamic/Fixed/Compressed。其中：

  1. Dynamic的行长度是可变的，一般包含可变长度的字段，如，VARCHAR或BLOB。
  2. Fixed的行长度则是固定的，只包含固定长度的列，如，CHAR和INTEGER。
  3. Compressed的行则只在压缩表中存在。

• Rows
  表中的行数。对于MyISAM和其他一些存储引擎，该值是精确的，但对于InnoDB该值是估计值。

• Avg_row_length
  平均每行包含的字节数。

• Data_length
  表数据的大小，以字节为单位。

• Max_data_length
  表数据的最大容量，该值和存储引擎有关。

• Index_length
  索引的大小，以字节为单位。

• Data_free
  对于MyISAM表，表示已分配但目前没有使用的空间。这部分空间包括之前删除的行，以及后续可以被INSERT利用到的空间。

• Auto_increment
  下一个AUTO_INCREMENT的值。

• Create_time
  表的创建时间。

• Update_time
  表数据的最后修改时间。

• Check_time
  使用CHECK TABLE命令或者myisamchk工具最后一次检查表的时间。

• Collation
  表的默认字符集和字符列排序规则。

• Checksum
  如果启用，保存的是整个表的实时校验和。

• Create_options
  创建表时指定的其他选项。

• Comment
  该列包含罗一些其他的额外信息。

  1. 对于MyISAM表，保存的是表在创建时带的注释。
  2. 对于InnoDB表，保存的是表空间剩余空间的信息。
  3. 如果是一个视图，包含VIEW的文本字样。

存储引擎

InnoDB

• InnoDB是MySQL的默认事务引擎。
• 被设计用来处理大量的短期（short-lived）事务，短期事务大部分情况是正常提交的，很少会被回滚。
• InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。
• InnoDB是一个很重要的存储引擎，很多个人和公司都对其贡献代码，而不仅仅是Oracle公司的开发团队。一些重要的贡献者包括，Google，Percona，Facebook等，他们的一些改进被直接移植到官方版本，也有一些由InnoDB团队重新实现。
• InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一些列的数据文件组成。在MySQL4.1以后的版本中，InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB可以使用裸设备作为表空间的存储介质，但现在的文件系统使得裸设备不再是必要的选择。
• InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认隔离级别是REPEATABLE READ，并且通过间隙锁（next-key locking）策略，防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
• InnoDB表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能，不过它的二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。
• InnoDB的存储格式是平台独立的，也就是可以将数据和索引文件从Intel平台复制到PowerPC，或者Sun SPARC平台。
• InnoDB内部做了很多优化，包括从磁盘读取数据时，采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引（adaptive hash index），以及能够加速插入操作的插入缓冲区（insert buffer）等。
• 作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份。例如，Percona提供的开源的XtraBackup。MySQL的其他存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入（Q：需要停服？）。

MyISAM

• 在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。
• MyISAM提供了大量的特性，包括，全文索引，压缩，空间函数等，但MyISAM不支持事务和行级锁，在崩溃后无法安全恢复。
• MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。
• MyISAM表可以包含动态或静态（长度固定）的行，MySQL会根据表的定义来决定采用何种行格式。MyISAM表可以存储的行纪录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。
• 在MySQL 5.0中，MyISAM表如果是变长行，则默认配置只能处理256TB数据，因为指向数据记录的指针长度是6个字节（2^(6*8)=256TB，Q：需要这么大吗？）。

• MyISAM的一些特性：

  1. 加锁与并发
     MyISAM对整张表加锁，而不是针对行。即：
     读取时，会对需要读到的所有表加共享锁；
     写入时，会对需要写入的所有表加排他锁；
     并发插入，在表有读取查询的同时，也可以往表中插入新的记录。

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

  3. 索引特性
     对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引。

  4. 延迟更新索引键（Delayed Key Write）
     创建MyISAM表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区（in-memory key buffer），只有在清理键缓冲区，或者关闭表的时候，才会将对应的索引块写入到磁盘。这种方式可以极大地提升写入性能，但是在数据库崩溃时会造成索引损坏，需要执行修复操作。延迟更新索引键的特性，可以在全局设置，也可以为单个表设置。

• MyISAM压缩表
  如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。压缩表可以极大地减少磁盘空间占用，因此可以减少磁盘I/O，从而提升查询性能。
  压缩表也支持索引，但索引也是只读的。
  如果要修改压缩表，除非先将表解压缩，修改数据，然后再次压缩。
  压缩时表中的记录是独立压缩的，所以读取单行的时候不需要去解压整个表。

• MyISAM的性能
  MyISAM最典型的性能问题还是表锁的问题。如果发现所有的查询都长期处于Locked状态，那么毫无疑问表锁就是罪魁祸首。

第三方存储引擎

MySQL从2007年开始提供了插件式的存储引擎API，从此涌出了一系列为不同目的而设计的存储引擎。

• OLTP类引擎

Percona的XtraDB存储引擎是基于InnoDB引擎的一个改进版本，已经包含在Percona Server和MariaDB中，它的改进点主要集中在性能，可测量性和操作灵活性方面。XtraDB可以作为InnoDB的一个完全的替代产品，甚至可以兼容地读写InnoDB的数据文件。

面向列的存储引擎

MySQL默认是面向行的，每一行的数据是一起存储的，服务器的查询也是以行为单位处理的。而在大数据量处理时，面向列的方式可能效率更高。如果不需要整行的数据，面向列的方式可以传输更少的数据。如果每一列都单独存储，那么压缩的效率也会更高。

选择合适的存储引擎

• 除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎。
• 除非万不得已，否则建议不要混合使用多种存储引擎，否则可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。

转换表的引擎

有很多种方法可以将表的存储引擎转换成另外一种引擎。

• ALTER TABLE

ALTER TABLE mytable ENGINE = InnoDB;

问题：需要执行很长时间。因为MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的I/O能力，同时原表上会加上读锁。

• mysqldump
  为了更好地控制转换过程，可以使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，注意同时修改表名，因为同一个数据库中不能存在相同的表名，即使它们使用的是不同的存储引擎。

• CREATE和SELECT
  先创建一个新的存储引擎的表，然后利用INSERT…SELECT语法来导数据。

CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;

如果数据量很大，则可以考虑分批处理，针对每一段数据执行事务提交操作，以避免大事务产生过多的undo。

START TRANSACTION;
INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
COMMIT;