MySQL学习笔记（一）：存储引擎与事务

一、如何阅读官方文档

官方中文文档传送门：MySQL5.1中文文档
重点关注：

• 第七章 优化
• 第十一章 列类型
• 第十三章 SQL语句语法
• 第十五章 存储引擎和表类型

更好地阅读文档：注意方括号 [] 意味着该字段可以省略，也就是可选字段；当要用到圆括号字段修饰的字段的时候，圆括号()不可以省略；大括号{}意味着在该字段的给定值中进行选择，| 这个符号就意味着选择呗；啥符号都没带的就是必填字段呗。
例如下面由MySQL官方文档给出的select语法：
像[all | distinct | distinctrow] 这个字段本身是可选字段，写select语句的时候可加可不加，但是如果加上该字段的时候，就要在all，distinct，distinctrow中选择一个字段加上。
select_expr就是必选字段，必须要在select语句中写这个字段。
像[LIMIT {[offset,] row_count | row_count OFFSET offset}]这个，也就是说，这一整个字段是可选字段，但是如果加上该字段，就要在{}中给出选项中选择一个，offser加上了[]，说明它可有可无。

# 以MySQL官方文档的SELECT语法说明为例
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FROM table_references
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

---

二、存储引擎

存储引擎简单总结

以下内容基本来源于官方文档以及书籍《深入浅出MySQL》

常用存储引擎的对比

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持

其中常用的几种就是MyISAM（默认存储引擎）、InnoDB、MEMORY和MERGE；

• MyISAM不支持事务，也不支持外键，优势是访问的速度快，对事务性没有要求或者以SELECT和INSERT为主的应用可以使用这个引擎来创建表；
• InnoDB提供了具有提交、回滚和崩溃恢复能力的事务安全，相较于MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引，并且在MySQL中支持外键的存储引擎只有InnoDB，因此需要使用外键约束的时候就只能够选择InnoDB存储引擎；
• MEMORY使用存在内存中的内容来创建表，因此它的表访问起来非常快，并且默认使用HASH索引（创建索引的时候也可以指定使用HASH索引还是BTREE索引，例如create index mem_hash USING HASH on tab_memory (city_id)），但是这只是临时表，服务关闭之后，数据就会丢失；
• MERGE是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，而MERGE表本身没有数据，对MERGE类型的表可以进行查询、更新、删除的操作，但是其实际上就是对内部的MyISAM表进行的；

# 创建MERGE表示例，代码来源于《深入浅出MySQL》
# 三个关键点：
# 1. payment_2006与payment_2007都是使用MyISAM存储引擎的表，并且该MERGE表的
# 定义和payment_2006，payment_2007都一样；
# 2. INSERT_METHOD=LAST指定了当对该MERGE表进行插入时应当作用在最后一个表上
# 也可以指定为FIRST，如果创建MERGE表时不对该子句指定或者指定为NO，就不允许对
# 该MERGE表进行插入操作；
# 3. 对MERGE表进行DROP删除只会删除MERGE表的定义，对实际MyISAM表没有影响；
mysql> CREATE TABLE payment_all(
	-> country_id smallint,
	-> payment_date datetime,
	-> amount DECIMAL(15,2),
	-> INDEX(country_id)
	-> )engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;

关于InnoDB

• InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。如果你想要所有（非系统）表都被创建成InnoDB表，你可以简单地把default-table-type=innodb行添加到my.cnf或my.ini文件的[mysqld]节里。
• 每个连接到MySQL服务器的客户端开始之时是允许自动提交模式的，这个模式自动提交你运行的每个SQL语句。要使用多语句事务，你可以用SQL语句SET AUTOCOMMIT = 0禁止自动提交，并且用COMMIT和ROLLBACK来提交或回滚你的事务。
• 改变一个表为InnoDB型最快的办法就是直接插入进一个InnoDB表。即，使用ALTER TABLE ... ENGINE=INNODB，或用相同的定义创建一个空InnoDB表，并且用INSERT INTO ... SELECT * FROM ...插入行。如果你对第二个键有UNIQUE约束，你可以在导入阶段设置：SET UNIQUE_CHECKS=0，以临时关掉唯一性检查好加速表的导入。对于大表，这节省了大量的磁盘I/O，因为InnoDB随后可以使用它的插入缓冲区来第二个索引记录作为一批来写入。

三、事务控制与锁定

• MySQL 通过SET AUTOCOMMIT、START TRANSACTION、COMMIT和ROLLBACK等语句支持本地事务。默认情况下，MySQL 是自动提交（Autocommit）的，如果需要通过明确的Commit和Rollback来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和Oracle的事务管理明显不同的地方。
• 如果只是对某些语句需要进行事务控制，则使用START TRANSACTION语句开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果希望所有的事务都不是自动提交的，那么通过修改AUTOCOMMIT为0来控制事务比较方便，这样不用在每个事务开始的时候再执行START TRANSACTION语句。
• 在同一个事务中，最好不使用不同存储引擎的表，否则ROLLBACK时需要对非事务类型的表进行特别的处理，因为COMMIT、ROLLBACK只能对事务类型的表进行提交和回滚。