MySQL中有很多存储引擎，MyISAM、InnoDB、BDB(支持事务)、MEMORY(存储在内存中)、MERGE、NDB Cluster、ARCHIEVE、CSV、BLACKHOLE、FEDERATED等。
常用的主要是InnoDB和MyISAM，下面主要比较两种之间的区别。
下面分别分别以InnoDB和MyISAM两种存储引擎创建两张表，并插入一些数据
create table t1(a int not null primary key ,b int not null,c int not null,d varchar(32))engine=InnoDB;
create table t2(a int not null primary key ,b int not null,c int not null,d varchar(32))engine=MyISAM;
insert into t1(a,b,c,d) values(1,1,1,'a');
insert into t1(a,b,c,d) values(3,2,2,'c');
insert into t1(a,b,c,d) values(2,2,2,'b');
insert into t1(a,b,c,d) values(4,1,1,'d');
insert into t1(a,b,c,d) values(6,1,2,'e');
insert into t1(a,b,c,d) values(7,2,3,'m');
insert into t1(a,b,c,d) values(9,2,3,'m');
insert into t1(a,b,c,d) values(11,9,3,'m');
insert into t1(a,b,c,d) values(5,2,3,'m');
insert into t2(a,b,c,d) values(1,1,1,'a');
insert into t2(a,b,c,d) values(3,2,2,'c');
insert into t2(a,b,c,d) values(2,2,2,'b');
insert into t2(a,b,c,d) values(4,1,1,'d');
insert into t2(a,b,c,d) values(6,1,2,'e');
insert into t2(a,b,c,d) values(7,2,3,'m');
insert into t2(a,b,c,d) values(9,2,3,'m');
insert into t2(a,b,c,d) values(11,9,3,'m');
查看插入后表结构
发现t1插入的数据会按照主键自动排序
然后分别进行一次查询
发现t1查询的数据会按照主键自动排序
两个表的区别在于使用的存储引擎不同，t1表采用的InnoDB，t2表采用MyISAM
查看两个表的data文件
t1表只有一个文件t1.ibd(索引和数据文件)
t2表有两个文件t2.MYD(数据文件)和t2,MYI(索引文件)
我们可以得知InnoDB的索引和数据在同一个文件中的，而MyISAM索引和数据是在不同的文件中的。
InnoDB
局部性原理
cpu访问存储器时，无论是存取指令还剩数据，所访问的存储单元都聚集在一个较小的连续区域。
比如操作系统在的页
在InnoDB查询数据时候，select * from t1 where a=1不只是查询一条记录，cpu会查询出一段连续的区域的数据。
InnoDB的存储的最小单位称为页。
页
页(Page)是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。
| InnoDB页结构 |
|---|
| File Header |
| Page Header |
| Infimum+Supermum Records |
| User Records |
| Free Space |
| Page Directory |
| File Trailer |
| 名称 | 中文 | 占用空间 | 简单描述 |
|---|---|---|---|
| FileHeader | 文件头部 | 38B | 页的一些通用信息 |
| Page Header | 页面头部 | 56B | 数据页专有的一些信息 |
| Infimum+Supermum Records | 最小记录和最大记录 | 26B | 两个虚拟页的行记录 |
| User Records | 用户记录 | 不确定 | 实际存储的行记录内容 |
| Free Space | 空闲空间 | 不确定 | 页中尚未使用的空间 |
| Page Directory | 页面目录 | 不确定 | 页中某些记录的相对位置 |
| File Trailer | 文件尾部 | 8B | 检验页是否完整 |
InnoDB中一页的大小为16KB
InnoDB行格式
InnoDB存储引擎和大多数数据库一样，记录是以行的形式存储的，这就意味着页中保存至一行行数据。一行记录可以以不同的格式存储在InnoDB中，行格式有Compact、Redundant、Dynamic，我们可以在创建或修改表语句中指定行格式，
create table 表名(列信息) row_format=行格式名称
alter table 表名 row_format=行格式名称
Compact行格式
Compact行记录是在MySQL 5.0时被引入的，其设计目标是能高效存放数据。简单来说，如果一个页中存放的行数据越多，其性能就越高。Compact行记录以如下方式进行存储：
| 变长字段列表 | NULL标志位 | 记录头信息 | 列1数据 | 列2数据 | … |
|---|
变长字段列表存储变长数据，NULL标志位存储可为NULL的数据
存储结构
下面我们分析插入数据的过程，
由于是按照行存储数据，存储在一页中，那么一页中可以存放多行数据
先插入1_11a(索引为1)，然后插入2_22b,3_22c,4_11d，这时候插入的数据以链表形式存储，由于链表的插入效率较高，查询效率较低，所有链表的长度过长会导致链表查询效率很低，这时候使用页目录存储索引。
这样查找3的时候，先查询页目录，在根据页目录查询相应的行，这样查询效率就会提高。
当一页放满了时候，理所当然就会开辟新的页，然后把数据插入新的页中，这时候有两个页，我们假设分别为页10和页20，这时候如何判断取哪个页找数据？这时候我们引入目录页，每个目录存储索引和页号。就形成如下结构
这时候，如果我们查询a=4的数据，首先根据索引(这里a是主键，索引为a)查找目录页，4>1,根据索引1找到页10，然后就找到第10页，4>3,然后就找到了4_11a这条记录。
以上就形成了B+树的数据结构。通过以上分析，得知InnoDB底层的数据结构采用B+树实现。叶子结点存储索引(主键)和数据，非叶子结点只存储索引(下一页指针)。
索引
聚集索引:按照每个表的主键构建1颗B+树，叶子结点存储整张表的数据。
辅助索引:和聚集索引相比，叶子节点中并不包含行记录的全部数据。叶子结点存储当前索引和书签(存放该条记录的主键)。
InnoDB组织表的结构的时候，会先选择主键建立索引，如果没有主键，会选择自增字段，如果没有自增字段，会设置以隐藏列row_id组织B+树。
为什么建表的时候建议设置一个int型，自增的列做主键?自增?因为InnoDB底层是采用B+树组织数据的，顺序插入可以使得B+树进行较小调整;int？每一页是固定的，如果采用uuid，索引占据的空间就大，该页中存储的其他数据就少，存储数据的页数就变多了，可能会导致树的高度变高，这样查询效率就低了。
以上为主键索引的情况，那么如果是为非主键字段创建索引呢?
其实都是一样的，多个字段的大小比较无非多个字段就是多个字段比较，比如为有三个字段c(int)，d(varchar)，e(varchar)，有两条记录l1(1,‘assgf’,‘afaf’) l2(1,‘asfsg’,‘ssss’)
为cde建立索引，先比较c，再比较d(字符串比较每个字符(asscii))，再比较d(字符串比较每个字符(asscii))，‘asfsg’<‘assgf’，所以l2索引<l1索引，这种非主键字段创建的索引(单列索引和多列索引)与主键索引的结构几乎相同，区别在于叶子节点不会直接存储所有数据，而是存储主键索引。这样要想得到真实数据需要再根据主键索引查找的这一行的所有数据，这一过程叫做回表。
下面我们为t1表的bcd建立索引
create index idx_bcd on t1(b,c,d);
假设现在由三条数据，l1(1,1,1,a) l2(3,2,2,b) l3（6,1,2,b)，假设现在
它们存储在一页中，叶子节点只存储主键索引
下列判断下列sql的查询是否通过索引查找了
select * from t1 where b>=1 and c=2 and d='c'//可以，idx_bcd
select * from t1 where c>2 and d='c'//不可以
select * from t1 where d>='c'//不可以
select * from t1 where b>=1 //可以，idx_bcd
select * from t1 where b>=1 and d>3//不可以
select * from t1 where b>=1 and d>3 and c=1//可以
最左前缀原则:要查询的列的前面的列(在索引中前面的列)必须给出，比如索引idx_bcd，从前到后列为b、c、d，如果要查询c，b必须给出，如果要查询d，b和c必须都给出。
待完善。。。。。。
事务
什么是事务?
事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。
为什么需要事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。事务为解决数据安全操作提出的，事务控制实际上就是控制数据的安全访问。比如说有小明向小红转账10元，过程是小明的账户余额-10元，小红的账户余额+10元。假设转账过程中，小明账户已经减了10元，但是小红由于网络问题，账户无法接收10元，这样就导致了转账失败。这个时候就必须做出控制，使转账撤销，10元退还给小明。将小明账户的钱减少和小红账户的钱增加和减少放到同一个事务里，要么全部执行成功，要么全部撤销，这样就保证了数据的安全性。
事务四个特性
原子性
一个事务的全部操作，要么全部操作成功，要么全部操作失败，不会停留在中间环节。小明向小红转账，要么转账成功，要么转账失败，不存在小明账户钱转出，小红账户钱未增加的情况。
一致性
事务开始之前和事务结束以后，数据库的完整性没有被破坏。(小明余额+小红余额不变)
隔离性
数据库允许多个并发事务同时对其数据进行读写和修改的能力。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。注意，隔离通常意味着要锁定数据库的表或行。
持久性
一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
脏读、不可重复读、幻读
脏读:当第一个事务正在访问数据，并且对数据进行修改，而该事务并没有提交，另一个事务读到了未提交的该数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
不可重复读:
不可重复:一个事务需要多次重复读取数据，当该事务读取了数据之后，另一个事务访问并修改了该数据，这时候如果第一个事务还没结束然后需要再读一次该数据，这时候读取的数据与第一次读取的数据不一致，因此成为不可重复读。
幻读:表示在同一事务中，使用相同的查询语句，第二次查询时，莫名的多出了一些之前不存在数据，或者莫名的不见了一些数据。
幻读和不可重复读的区别:幻读侧重于插入和删除，不可重复读侧重于更新。
事物的隔离级别详解
事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable)。
读未提交
当前同一时间开启了两个事务，第一个事务修改了数据但是未提交，第二个事务能读到第一个事务修改的数据。读未提交会出现脏读现象。
读已提交
当前同一时间开启了两个事务，第一个事务修改了数据但是未提交，第二个事务不能读到第一个事务修改/添加/删除 的数据，只有第一个事务提交了，第二个事务才能读到。读已提交出现不可重复读、幻读现象。
可重复读
当前同一时间开启了两个事务，第一个事务修改了数据，即使第一个事务提交，第二个事务也不能读到新修改的数据。可重复读解决了不可重复读的问题，但仍会出现幻读现象(但是mysql在可重复读级别解决了幻读现象)。
串行化
当前同一时间开启了多个事务，除了读读，其他的读写/写写 都会阻塞。
锁机制
读锁:共享锁、Shared Locks、S锁
写锁:排他锁、Exclusive Locks、X锁
select:不加锁，对于普通读操作，InnoDB不会加任何锁。
读锁和读锁不冲突(一行记录可以加多个读锁)，读锁和写锁(一行记录加了读锁不能加写锁，加了写锁不能加读锁)冲突。
行锁和表锁
LOCK_REC_NOT_GAP:单个行记录上的锁。
LOCK_GAP:间隙锁。锁定一个范围，但不包括记录本身。GAP锁的目的是防止同一事务两次当前读，出现幻读的情况。
LOCK_ORDINARY:锁定一个范围，并且锁定记录本身。对于行的查询都是采用此方法，主要目的是解决幻读问题。
IS锁、IX锁
IS锁:意向共享锁、Intention Shared Locks 、IS锁。在事务对某条记录加S锁时候，需要现在表级别加一个IS锁。
IX锁:意向排他锁、Intention Exclusive Locks 、IX锁。在事务对某条记录加X锁时候，需要现在表级别加一个IX锁。
IS是意向表级锁，它们的提出仅仅是为了在之后加表级别的S或X锁时，判断记录是否被上锁。
加锁操作
- 普通select不会加任何锁
- select … lock in share mode 加S锁
- select … for update 加X锁
- DELETE：删除一条记录时候，先对该条记录加X锁，在进行删除操作
INSERT：插入一条记录时，会加"隐式锁"来保护这条记录在本事务提交之前不被其他事务访问。
UPDATR
如果被修改的列，修改前后没有导致存储空间变化，那么先给这条记录加X锁，再直接对记录修改。
如果被修改的列，修改前后存储空间发生变化，那么先给这条记录加X锁，将这条记录删除，再Insert一条记录。
隐式锁:一个事务插入一条记录，还未提交，这条记录会保存本次事务id，而其他事务如果对这条记录加锁时发现事务id不对应，这时会产生X锁，所以相当于在插入一条记录时，隐式的给这条记录加了一把X锁。
行锁
READ COMMITTED级别下
1.查询使用的主键:
对事务一和事务二进行分别如下操作
设置隔离级别为读已提交，开启事务
set session transaction isolation level read committed;
begin;
对事务一执行以下语句
select * from t1 where a=1 for update;
对事务二执行以下语句
发现事务二阻塞了，如果查询其他记录
发现其他记录不会堵塞
因此READ COMMITTED下根据主键查询加写锁，只会给对应记录加行锁。
2.查询使用的普通索引
给e字段加索引
create index idx_e on table(e)
事务一:
事务二
查询发现使用普通索引也是把查出的行加了锁。
2.查询使用的普通字段
发现也是把查出的行加锁。
因此读已提交只是对查出的记录加锁。
然后看以下READ COMMITTED下一个事务加锁后，另一个事务插入数据
另一个事务中插入e=‘a’，发现插入成功。
因此这就是READ COMMITTED 隔离级别下为什么会出现幻读的原因。
REPEATABLE READ级别下
1.走索引的情况
一个事务查询出e='a’的记录，并加X锁
另一个事务执行同样操作
因此REPEATABLE READ下也会对查出的记录加锁。
如果另一个记录进行写操作
插入e=‘a’
插入e=’a1’
插入e=‘f’
插入e=’axxxx’
这里发现e=‘a’，e=‘a1’，e='axxxx’都插入不进去，可见不只是一条记录加锁，是以a开头的这个间隙之间加了锁。因此REPEATABLE READ隔离级别下，会在间隙间加一把锁，因此不会出现幻读现象。
2.全表扫描(不走索引)的情况
d为普通字段，走全表扫描，事务1加X锁
事务2进行相同操作，堵塞
如果查询其他字段，发现也会堵塞
REPEATABLE READ隔离级别下，对与普通字段，一个事务加了写锁，会锁住表所有行包括间隙，也就是表锁（防止其他事务修改该普通字段的值，防止出现幻读）。
表锁
待完善。。。
MyISAM
存储结构
MyISAM底层也是采用B+树实现，不同的是MyISAM中B+树的叶子结点存储的是索引和当前行所在磁盘文件地址。
MyISAM不支持事务
InnoDB和MyISAM对比:
MyISAM特点
- 不支持事务，也不支持外键，但每次查询都是原子的。
- 支持表级锁，即每次操作都是对整个表加锁，锁开销小。
- 一个MyISAM表由三个文件构成，表结构文件(.frm文件)、索引文件(.MYI)、数据文件(.MYD)
- 采用非聚集索引，索引的数据域存储指向当前行的指针(在磁盘文件地址)。辅索引与主索引基本一致，但是辅索引不用保证唯一性
InnoDB特点
- 支持ACID的事务，支持事务的四种隔离级别。
- 支持行锁，，锁开销大 ，支持外键:因此可以支持写并发。
- 一个InnoDB表有两个文件，表结构文件(.frm文件)、.ibd文件(InnoDB引擎开启了独立表空间(my.ini中配置innodb_file_per_table = 1)产生的存放该表的数据和索引的文件。)
- 主键索引采用聚集索引，索引的数据域存储当前行的数据，辅助索引的数据域存储主键，通过辅助索引查找数据先根据辅助索引找到主键，然后再根据主键到主键索引取找。最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。
- 数据存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；
MyISAM和InnoDB区别
- 存储结构:一个MyISAM表在磁盘上有三个文件，表结构文件(.frm文件)、索引文件(.MYI)、数据文件(.MYD)，一个InnoDB表在磁盘上有两个文件，表结构文件(.frm文件)、.ibd文件(存储索引和数据)
- 存储空间：MyISAM索引可被进行压缩，存储空间较小，支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
- 事务:InnoDB提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表功能。MyISAM强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。。
- 锁差异:MyISAM不支持事务，支持表锁。用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持行锁，行锁提高了多用户并发操作的性能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
- 默认存储引擎:在MySQL 5.1之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB。
- AUTO_INCREMENT:对于自增长的字段，在MyISAM表中可以和其他字段一起建立联合索引。在InnoDB中，可以创建只有该字段的索引，也可以建立联合索引，但自增键必须在最左侧。
- 全文索引: MyISAM支持FULLTEXT全文索引，MySQL5.6.4版本后InnoDB支持全文索引。
- 主键:MyISAM允许没有任何索引和主键的表存在，索引是保存行的地址。InnoDB如果没有设置主键或非空唯一索引，会自动生成一个6B的隐藏列作为主键(用户不可见)。数据是主索引的一部分，辅助索引保存的是主索引的值。
- 外键:InnoDB支持外键，MyISAM不支持外键。
- CRUD操作:如果执行大量的SELECT，MyISAM是更好的选择。如果数据需要执行大量INSERT和UPDATE，采用InnoDB更佳。DELETE操作在性能上更佳，但是InnoDB在DELETE FROM table时候，不会重新建立表，而是一行一行的删除，InnoDB如果要清空大量数据的表，最好使用truncate table语句。
- 表的具体行数。MyISAM保存有表的总行数，如果执行 select count(*) from table;会直接取出出该值。InnoDB没有保存表的总行数(和事务有关)，如果使用select count(*) from table；就会遍历整个表，消耗相当大。
- 崩溃恢复: 数据量比较大的时候，系统崩溃后如何快速地恢复是一个需要考虑的问题。相对而言，MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也慢。
- 可移植性:MyISAM具有良好的可移植性，数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。InnoDB的真实数据存储在共享表空间(ibdata1)，可移植性比较差，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
687
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
