一篇就搞懂Mysql存储引擎和索引的文章
mysql常见存储引擎概念
存储引擎，即表类型(table_type)
用户可以根据应用的需求选择如何来存储数据、索引、是否使用事务等。选择合适的存储引擎往往能够有效的提高数据库的性能和数据的访问效率，另外一个数据库中的多个表可以使用不同引擎的组合以满足各种性能和实际需求。MySQL支持很多存储引擎，包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE等，其中InnoDB和BDB支持事务安全。它还支持一些第三方的存储引擎，例如TokuDB（高写性能高压缩存储引擎）、Infobright（列式存储引擎）。
1.MyISAM
它是MySQL5.5之前的默认存储引擎
优势：访问速度快
适用场景：对事务的完整性没有要求，或以select、insert为主的应用基本都可以选用MYISAM。在Web、数据仓库中应用广泛。
特点：
1、不支持事务、外键
2、每个myisam在磁盘上存储为3个文件，文件名和表名相同，扩展名分别是
.frm -------存储表定义
.MYD --------MYData，存储数据
.MYI --------MYIndex，存储索引
数据文件和索引文件可以放在不同的目录，平均分布IO，加快访问速度，在创建表的时候通过 data directory和index directory来指定存储路径.
myisam表还支持三种不同的存储格式
a 静态表（fixed）
默认的存储格式
静态表中的字段都是非变长字段，每个记录都是固定的长度，当表不包含变量长度列(VARCHAR, BLOB, 或TEXT)时,使用这个格式。
优点：存储迅速，出现故障容易恢复
缺点：占用空间比动态表大，静态表在进行数据存储时会按照事先定义的列宽度补足空格，但在访问的时候会去掉这些空格
注意：如果数据本身带有空格，在返回的时候会去掉数据本身自带的末尾的空格，前面的会保留
b 动态表（dynamic）
包含变长字段，例如varchar、、text、blob，如果一个MyISAM表包含任何可变长度的字段（varchar、blob、text），或者该表创建时用row_format=dynamic指定，则该表使用动态格式存储
优点：占用空间小
缺点：频繁的更新和删除操作会产生碎片，需要定期用optimize table语句或myisamchk -r命令来改善性能，并且在出现故障后较难恢复
c 压缩表
由myisampack工具创建，占据非常小的磁盘空间，因为每个记录都是被单独压缩的
2.InnoDB
MySQL5.5之后的默认存储引擎
应用场景：如果应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作中包含读、插入、删除、更新，那InnoDB是最好的选择。在计费系统、财务系统等对数据的准确性要求较高的系统中被广泛应用。
优点：提供了具有提交（Commit）、回滚（Rollback）、崩溃恢复能力的事务安全，支持外键。
缺点：相比较于MyISAM，写的处理效率差一点，并且会占用更多的磁盘空间来存储数据和索引
特点：
1、自动增长列
innoDB表的自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列
MyISAM表的自动增长列可以是组合索引的其他列
设置自动增长列：create表时，在字段后加auto_increment
可以通过alter table emp auto_increment=n 来强制设置自动增长列的初始值，默认是1，但是该强制指定的值是保存在内存中的，所以在数据库重启后会失效，需要重新设置
2、外键约束
MySQL的存储引擎中只有innoDB支持外键约束
注意：当某个表被其它表创建了外键参照，那么该表对应的索引和主键禁止被删除
当导入多个表的数据时，如果要忽略表之前导入顺序，或者当执行load data和alter table操作，为了提高处理速度的时候，可以暂时关闭外键约束，命令是
mysql> set foreign_key_checks=0;
执行完之后，再使其为1 ，开启外键。
查看外键信息
show create table 或show table status
3、存储方式
innoDB存储数据和索引有共享表空间存储和独占表空间存储两种方式，通过参数innodb_file_per_table控制，0表示共享空间，也是默认的，1表示独占空间
两种方式的表结构（描述）都保存在.frm文件中
共享表空间：
每一个数据库的所有表的数据、索引都保存在一个文件中，默认在data目录下，名为ibdata1，大小为10M的文件，可以通过参数innodn_data_file_path=/data/ibdata1:2000M来指定存储路径。
优点：
（1）、可以将表空间分为多个文件放在不同的磁盘上，分布IO，提高性能。innodn_data_file_path=/data/ibdata1:2000M；/db/ibdata2:2000M:autoextend
autoextend表示如果指定的2000M空间用满后，该文件自动增长。
也就是说采用共享空间存储，存储空间的大小不受文件系统下文件大小的限制了，而取决于自身的限制，官方文档显示，表空间的最大限制是64TB。
（2）、表数据和表结构放在一起，方便管理
缺点：由于所有的数据和索引都是在一个文件中混合存储，这样的话对一个表做了大量的删除操作后，表空间中会产生大量的空隙
独占表空间存储：
每一张表都有自己独立的表空间，表的结构依然在.frm文件中，还有一个后缀为.ibd的文件，保存了这张表的数据和索引。
优点：
每张表都有自己独立的表空间，可实现单表在不同数据库中移动
空间可回收。drop table会自动回收；删除数据后，通过alter table emp engine=innodb也可回收不用的表空间
效率和性能会好一些
缺点：由于每个表的数据都是以一个单独的文件来存放，所以会受到文件系统的大小限制
3.MEMORY
MEMORY存储引擎是用保存在内存中的数据来创建表，每个memory表对应一个磁盘文件。格式是.frm
特点：由于他的数据是存放在内存中的，并且默认使用HASH索引，所以它的访问速度特别快，同时也造成了他的缺点，就是数据库服务一旦关闭，数据就会丢失，另外对表的大小有限制
每个memary表中可存储数据量的大小，受到max_heap_table_size变量的约束，他的初始值是16MB，可以在定义Memary表的时候通过max_rows指定表的最大行数
适用场景：内容变化不频繁的代码表，作为统计操作的中间结果表，便于利用它速率快的优势高效的对中间结果进分析。
4.MERGE
Merge表是一组MyISAM表的组合，这些myisam表的结构必须完全相同，MERGE表本身并没有数据，对它的操作实际上是对内部MYISAM表的操作。
MERGE表在磁盘上保留两个文件，.frm文件存储表的定义，.mrg文件存储组合表的信息
应用场景：用于将一系列MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们
优点：突破对单个MyISAM表的大小限制，通过将不同的表分布在多个磁盘上，提高访问效率
例：
mysql>create table emp1(
->id int,
->name varchar(11),
->salary decimal(8,2)
->)engine=myisam;
mysql>create table emp2(
->id int,
->name varchar(11),
->salary decimal(8,2)
->)engine=myisam;
mysql>create table emp-all(
->id int,
->name varchar(11),
->salary decimal(8,2)
->)engine=merge union=(emp1,emp2) insert_method=last;
insert_method=first/last/no 表示对MERGE表插入操作时，作用对象是谁。first表示作用于第一张myisam表，last作用于最后一张myisam表，no或者不指定表示不能对该MERGE表进行插入操作。
5.Archive
从archive单词的解释我们大概可以明白这个存储引擎的用途，这个存储引擎基本上用于数据归档；它的压缩比非常的高，存储空间大概是innodb的10-15分之一所以它用来存储历史数据非常的适合，由于它不支持索引同时也不能缓存索引和数据，所以它不适合作为并发访问表的存储引擎。Archivec存储引擎使用行锁来实现高并发插入操作，但是它不支持事务，其设计目标只是提供高速的插入和压缩功能。
每个archive表在磁盘上存在两个文件
.frm(存储表定义)
.arz(存储数据)
1.archive存储引擎支持insert、replace和select操作，但是不支持update和delete。
2.archive存储引擎支持blob、text等大字段类型。支持auto_increment自增列同时自增列可以不是唯一索引。
3.archive支持auto_increment列，但是不支持往auto_increment列插入一个小于当前最大的值的值。
4.archive不支持索引所以无法在archive表上创建主键、唯一索引、和一般的索引。
往archive表插入的数据会经过压缩，archive使用zlib进行数据压缩，archive支持optimize table、 check table操作。
一个insert语句仅仅往压缩缓存中插入数据，插入的数据在压缩缓存中被锁定，当select操作时会触发压缩缓存中的数据进行刷新。insert delay除外。
对于一个bulk insert操作只有当它完全执行完才能看到记录，除非在同一时刻还有其它的inserts操作，在这种情况下可以看到部分记录，select从不刷新bulk insert除非在它加载时存在一般的Insert操作。
对于检索请求返回的行不会压缩，且不会进行数据缓存；一个select查询会执行完整的表扫描；当一个select查询发生时它查找当前表所有有效的行，select执行一致性读操作，注意，过多的select查询语句会导致压缩插入性能变的恶化，除非使用bulk insert或delay insert，可以使用OPTIMIZE TABLE 或REPAIR TABLE来获取更好的压缩，可以使用SHOW TABLES STATUS查看ARCHIVE表的记录行。
Archive存储引擎支持分区。
由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。
MyISAM和InnoDB对索引和数据的存储在磁盘上是如何体现的
先来看下面创建的两张表信息，role表使用的存储引擎是MyISAM，而user使用的是InnoDB：
再来看下两张表在磁盘中的索引文件和数据文件：
1. role表有三个文件，对应如下：
role.frm：表结构文件
role.MYD：数据文件（MyISAM Data）
role.MYI：索引文件（MyISAM Index）
2. user表有两个文件，对应如下：
user.frm：表结构文件
user.ibd：索引和数据文件（InnoDB Data）
索引概念
众所周知，索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引用的是B+树索引。
SQL Sever索引类型有：唯一索引，主键索引，聚集索引，非聚集索引。
MySQL 索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引。
聚集索引
聚集（clustered）索引，也叫聚簇索引。
定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
单单从定义来看是不是显得有点抽象，打个比方，一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面，现在用一个简单的示意图来大概说明一下在数据库中的样子：
注：第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。
结合上面的表格就可以理解这句话了吧：数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。
非聚集索引
非聚集（unclustered）索引。
定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。
MyISAM主键索引与辅助索引的结构
我们先列举一部分数据出来分析，如下：
上面已经说明了MyISAM引擎的索引文件和数据文件是分离的，我们接着看一下下面两种索引结构异同。
1. 主键索引：
上一篇文章已经介绍过数据库索引是采用B+Tree存储，并且只在叶子节点存储数据，在MyISAM引擎中叶子结点存储的数据其实是索引和数据的文件指针两类。
如下图中我们以Col1列作为主键建立索引，对应的叶子结点储存形式可以看一下表格。
过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。
2. 辅助（非主键）索引：
以Col2列建立索引，得到的辅助索引结构跟上面的主键索引的结构是相同的。
InnoDB主键索引与辅助索引的结构
1. 主键索引：
我们已经知道InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的Col1、Col2、Col3数据项。
2. 辅助（非主键）索引：
这次我们以数据表中的Col3列的字符串数据建立辅助索引，它的索引结构跟主键索引的结构有很大差别，我们来看下面的图：
在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。
InnoDB索引结构需要注意的点
1. 数据文件本身就是索引文件
2. 表数据文件本身就是按B+Tree组织的一个索引结构文件
3. 聚集索引中叶节点包含了完整的数据记录
4. InnoDB表必须要有主键，并且推荐使用整型自增主键
正如我们上面介绍InnoDB存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没有显式指定索引列的话，MySQL会从表中选择数据不重复的列建立索引，如果没有符合的列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为整型。
那为什么推荐使用整型自增主键而不是选择UUID？
UUID是字符串，比整型消耗更多的存储空间；
在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速；
自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。
在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。
为什么非主键索引结构叶子节点存储的是主键值？
保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息（真是名称、手机号、收货地址···）修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。
