物理结构
最直观的不同就是底层的插件式存储引擎，可以配置不同的存储引擎，支持不同的业务特性：
| 存储引擎 | 特性 |
| innodb存储引擎 | 面向oltp(online transaction processing)、行锁、支持外键、非锁定读、默认采用repeaable级别（可重复读）通过next-keylocking策略避免幻读、插入缓冲、二次写、自适应哈希索引、预读 |
| myisam存储引擎 | 不支持事务、表锁、全文索引、适合olap（在线分析处理），其中myd:放数据文件，myi:放索引文件 |
| memory存储引擎 | 数据存放在内存中，表锁，并发性能差，默认使用哈希索引 |
| archive存储引擎 | 只支持insert和select zlib算法压缩1：10，适合存储归档数据如日志等、行锁 |
| maria存储引擎 | 目的取代myisam、缓存数据和索引、行锁、mvcc |
innodb
后台线程：4个io thread(insert buffer、log、read、write),1个master thread(优先级最高),1个锁(lock)监控线程，1个错误监控线程。
loop主线程主要做是： 1s-日志缓冲刷新到奥磁盘，合并插入缓冲，刷新脏页到磁盘，10s-增加删除无用undo页，产生检查点等
插入缓冲是物理页的一个组成部分，用来提高性能，由于b+树算法，插入数据时候主键索引是顺序的，如果非聚集索引（辅助索引），叶子节点无序，索引innodb引入插入缓冲，判断非聚集索引页是否在缓冲池中，如果在则直接插入，不在则先放入缓冲区，然后loop会以一定频率将插入缓冲合并。
存储引擎： 缓冲池(buffer pool)、重做日志缓冲池(redo log buffer)以及额外的内存池(additional memory pool) 组成。
缓冲池：占最大块内存，用来存放各种数据的缓存包括有索引页、数据页、undo页、插入缓冲、自适应哈希索引、innodb存储的锁信息、数据字典信息等。工作方式总是将数据库文件按页(每页16k)读取到缓冲池，然后按最近最少使用
(lru)的算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改，总是首先修改在缓存池中的页(发生修改后即为脏页)，然后再按照一定的频率将缓冲池的脏页刷新到文件。
日志缓冲： 将重做日志信息先放倒这个缓冲区中，然后按照一定频率将其刷新到重做日志文件。
两次写：如果写失败，可以通过重做日志恢复，重做日志中记录的是对页的物理操作，如果页本身损坏，则没用。提升可靠性，在应用重做日志之前，需要一个页的副本，当写入失效发生时候，先通过页的副本来还原该页，再进行重做。
恢复数据=页副本+重做日志
自适应哈希索引：对于等值搜索，innodb存储引擎会监控表上索引的查找，如果观察到建立哈希索引会带来性能提升，则建立哈希索引，innodb_adaptive_hash_index来启用或者禁用
对应的查看命令：
show engine innodb status show variables like 'innodb_io_thread%' show variables like 'innodb_buffer_pool_size' show variables like 'innodb_log_buffer_size' show variables like 'innodb_additional_mem_pool_size'
表存储
表空间（由数据段、索引段、回滚段等组成）-> 段-> 区（由64个连续页组成） -> 页（16k，由数据页、Undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页） -> 行
索引
b+树http://blog.csdn.net/v_JULY_v/article/details/6530142，高度一般2～3层，查询某一键值的行记录，通过b+树一般2～3次io
聚集索引，聚集索引是按照主键组织，每张表只能有一个聚集索引，每个数据页都能通过双向链表进行连接，叶子节点存放一整行的信息，聚集索引对于存储是逻辑上连续的，所以对主键的排序查找和范围查找速度很快
辅助索引，非聚集，叶子节点不存出全部数据，主要存储键值及一个只想剧组索引的键，来告诉innodb再哪里可以找到与索引对应的行数据，如果一个高度为3的辅助索引和高度为3的聚集索引，则根据辅助索引来查询记录需要6次io。辅助索引可以多个
高选择列，每次取出少量数据的。
联合索引，多列的，最左前缀，where子句中最频繁且过滤性最好的一列放在最左边。
mvcc
一致性非锁定读，innodb存储引擎通过多版本控制的方式读取当前执行时间数据库中行的记录，如果此时该行正在执行delete/update操作，这是读取操作不会因此而等待释放锁，相反，会读取一个行的数据快照，快照数据是指该行之前版本的数据，该实现是通过undo段来实现，而undo用来事物中回滚数据，因此快照本身是没有额外的开销，此外，快照数据是不需要上锁，因此没有必要对历史数据进行修改，一个行有多个数据快照。
隔离级别的实现，Read uncommitted只读取行主数据，Read committed快照数据，读取被锁定行的最新一份快照数据，Repeatable，对于快照数据，读取事物开始时的版本
锁
行锁：
共享锁s - 允许一个事物区读一行，阻止其他事物获得相同的数据集的排他锁 lock in share mode/排他锁x - 允许获得排他锁的事物更新数据，但是阻止其他事物获得相同数据集的共享锁和排他锁 for update
Record Lock:单行记录上的锁， 防止别的事物修改或删除
Gap Lock:间隙锁，第一个记录前的间隙或最后一条记录后的间隙加锁，锁定一个范围，但不包含记录本身，防止别的事物新增
Next-Key Lock:Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。解决了rr级别在写数据时幻读的问题
当innodb扫描索引记录的时候，会首先对选中的索引记录加上行锁，再对索引记录两边的间隙加上间隙锁，如果一个间隙被事物t1加了锁，其他事物是不能再这个间隙插入记录的。
当我们用范围条件而不是相等条件键锁数据，并要求共享或排他锁时，innodb会给符合条件的已有数据的索引项枷锁，对于键值在条件范围内单不存在记录，叫做间隙，innodb会对这个间隙枷锁，这种锁机制不是所谓的间隙锁
一共101条数据，select * from emp where id > 100 for update，系统会为ID为101的记录加锁，也会为ID大于101的间隙加锁，可以防止幻读以满足隔离级别的要求，防止其他事物插入了大于100的任何记录，那么本事物如果再次执行上述语句，就会发生幻读
在使用范围条件键锁并锁定记录时，innodb这种加锁机制会组赛符合条件范围内值的并发插入
表锁：
意向共享锁is - 表示事物准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先获得is锁/意向排他锁ix - 表示事物准备给数据行加入排他锁，说明事物在一个数据行加排他锁前必须先取的该表的ix锁
意向锁是innodb自动加的，不需要用户干预。对于insert/update/delete, innodb会自动给涉及的数据加排他锁；对于一般的select语句不会加任何锁
SET AUTOCOMMIT=0; LOCAK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and here]; COMMIT; UNLOCK TABLES;
myisam表锁是不会死锁的，因为他是一次性获取所需的全部锁，但innodb锁是逐步获取的，所以他可能发生死锁
innodb死锁后，会自动检测，并使一个事物是放锁并退回，另一个事物获得锁。同时可设置超时来防止死锁。
如何防止死锁：
1. 应用程序中，尽量约定以相同的顺序访问表。
2. 批处理数据时，尽量对数据排序，保证每个线程按照固定的顺序来处理记录
3. 在事物中，如果要更新记录，直接申请足够级别的锁-排他锁