高性能Mysql（第一章MySQL架构与历史）

MySQL也可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。

也可以只改变当前会话的隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;

不要在事务中混合使用存储引擎，例如InnoDB和MyISAM，在正常提交的情况下不会有什么问题。但如果该事务需要回滚，非事务型的表上的变更就无法撤销。

InnoDB采用的是两阶段锁定协议，锁只有在COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。

另外，InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于SQL规范。

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

MySQL也支持LOCK TABLES和UNLOCK TABLES语句，这是在服务层实现的与存储引擎无关，他们有自己的用途，但并不能代替事务处理。

多版本并发控制（MVCC）

MVCC的实现，是通过保存在某个时间点的快照来实现的，也就是说不管需要执行多长时间，每个事务看到的数据都是一致的。

典型的有乐观锁（optimistic）和悲观锁（pessimistic）。

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存行的创建时间，一个保存行的过期时间。当然存储的并不是实际的时间，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

• SELECT
  • InnoDB会根据一下两个条件检查每行记录：
    • 只查找版本早于当前事务版本的数据后（也就是，行的版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
    • 行的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。
• INSERT
  • InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
• DELETE
  • InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
• UPDATE
  • InnoDB会插入一行新纪录，保存当然系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

保存这两个额外系统版本号，使大多数操作都可以不用加锁。这样操作使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC只在REPEATABLE READ和READ COMMITED两个隔离级别下工作。其它两个隔离级别都和MVCC不兼容，因为READ UNCOMMITED总是读取最新的数据行，而不是符合当然事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

MySQL的存储引擎

 在文件系统中，MySQL将每个数据库（也可以称之为schema），保存为数据目录下的一个子目录。创建表时，会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。

可以使用SHOW TABLE STATUS命令显示表的相关信息。

 MyISAM存储引擎

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。

MyISAM特性

• 加锁与并发：MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入是则对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录（这被称为并发插入，CONCURRENT INSERT）。
• 修复：执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过CHECK TABLE mytable检查表的错误。如有错误，可以通过执行REPAIR TABLE mytable进行修复。另外如果MySQL服务器已关闭，也可以通过myisamchk命令行工具进行检查和修复操作。
• 索引特性：对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引。MyISAM也支持全文索引。
• 延迟更新索引键（Delayed Key Write）：创建MyISAM表的时候，如果指定了DELAY_KEY_WRITE，在每次修改完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的缓冲区，只有在清理缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大的提升写入性能，但是在数据库崩溃时会造成索引损坏，需要执行修复操作。这个特性可以在全局设置，也可以为单个表设置。

MyISAM压缩表

如果表在创建并导入数据后，不会再进行修改，这样的表适合采用MyISAM压缩表。

可以使用myisampack进行压缩，压缩表是不能修改的，可以极大的减少磁盘空间占用，因此也可以减少磁盘I/O，提升查询性能。支持索引，但索引是只读的。

MySQL内建的其它存储引擎

• Archive：适合高速插入，快速INSERT。
• Blackhole：没有任何的存储机制，他会丢弃所有插入的数据，但是服务器会记录表日志，所以可以用于复制数据到备库，或者只是简单的记录到日志。
• CSV：可以将普通CSV文件作为MySQL的表来处理，这种表不支持索引。CSV引擎可以在数据库运行时拷入或者拷出文件。
• Federated：访问其它MySQL服务器的一个代理。
• Memory：如果需要快速访问数据且不会被修改，重启以后丢失也没问题，可以使用Memory表。Memory表是表级锁，因此并发写入性能较低，他不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR列，实际存储时也会转成CHAR，这可能导致部分内存的浪费。
• Merge：MyISAM的一个变种，是由多个MySAM合并而来的虚拟表。引入分区功能后，该引擎已被放弃。
• NDB集群：2003年，MySQL AB公司从索尼爱立信公司收购了NDB数据库，然后开发了NDB集群存储引擎，作为SQL和NDB原生协议之间的接口。

第三方存储引擎

 面向列的存储引擎

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

infobright是最有名的面向列的存储引擎。在非常大的数据量（数10TB）时，该引擎工作良好。

转换表的引擎

ALTER TABLE mytable ENGINE = InnoDB;

上述语法可以适用任何存储引擎，但是需要执行很长时间，MySQL会按行将数据从原表复制到一张新的表中，在复制期间会消耗系统所有的I/O能力，同时原表会加上读锁。所以在繁忙的表上执行此操作要特别小心。替代方案是导出与导入，手工进行表复制。

可以使用mysqldump将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项。

还有一种不需要导出整个表的数据，而是先创建一个新的存储引擎的表，然后利用INSERT...SELECT语法来导数据。

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

数据量不大，这样做工作得很好。如果数据量很大，则考虑做分批处理，针对每一段数据执行事务提交操作。

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

如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。

Percona Toolkit提供了一个pt-online-schema-change的工具，可以比较简单、方便的执行上述过程。

高性能Mysql（第一章MySQL架构与历史）

标签：字符   单位   lin   原子性   inno   实时   show   功能   面向