数据库索引深入刨析

为什么要学数据库索引

   相信谈到数据库，每一个职业程序员都会说自己会使用。但是会使用很多时候仅仅指的是会写增删改查的语句而已。如果把数据库比作一本书，那么程序员就是这本书总编辑。身为一个总编辑，仅仅会把记录的文字用订书机装订在一起可不够。至少要先明确这本书的内容，然后选择合适的文体(选择使用哪一种数据库)，还要会对这些内容进行排版(建立恰当的表)，添加合适的目录(建立数据库的索引)，还有选择舒服的纸张与背景色(选择合适的数据库引擎)。
  所以如果你只想当一个图书装订员(毕竟图书装订员也是有市场需求的)，那么你就可以不用学习数据库索引了，但是如果你想当一个图书总编辑，那么学习数据库索引就是一个必须要进行的步骤。
  好不容易走上程序员这条路，还是应该有一个成为大佬的梦想。且行且珍惜，来和我一起慢慢学习吧。

什么是数据库索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

  上一小节已经提到，数据库的索引就像是一本书的目录，它可以帮助我们更快的找到我们需要的数据。是的，对于索引的理解就是如此的简单。但是，数字世界和物理世界毕竟是有着天壤之别，因此数据库的索引不像书的目录那样，只是一张纸。它从某种意义上来讲，是一种被我们称为B+Tree(有的是B Tree)的树状数据结构，正是因为这种结构，让它具有了和目录一样的功能。

数据库索引的数据结构

数据库中的数据都是存在磁盘里的，索引的作用是为了快速定位到数据的磁盘存储位置，而谈到定位，有以下几种数据结构供我们使用:

• hash表(散列表)
• 有序数组
• 红黑树(平衡二叉树)
• B树
• B+树
• …（还有其它几种，上面我列举了最重要的几种，当你会分析上面的几种之后，后面其他的你也一定就会分析了）

上面已经提到数据库使用的数据结构多是B+树，但它为什么不使用其它几种结构呢，下面是详细的分析。

为什么不使用hash表(散列表)

用散列表进行查询的时间复杂度为O(1)，从速度上来说可以秒杀B+树。但是世上没有完美，查询速度虽然快，但是由于另一些方面的不足，数据库的底层结构并不使用它。那么究竟是哪些方面呢，严重的问题有两个方面。

• 如果只选一个数据，那么的确hash更快，但是数据库种经常会选择多条，这个时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多。
• 当数据量很大时，hash冲突的概率也会非常大。

为什么不使用有序数组

为什么不使用有序数组，这个问题大家可能随便一想，就能想到很多很多个援用，这里我随便列举几个问题:

• 数据库的索引一般是存在磁盘上，数据量大的情况下可能无法一次装入内存。
• 数据库很多时候都需要增删，当数据库的数据增删时，索引也要相应的发生改变，有序数组的增删耗时很严重。
• …

为什么不使用红黑树

红黑树是平衡树的一种，它的复杂的定义和规则，最后都是为了保证树的平衡性。它的查询效率也很高，那为什么不使用它来进行查询呢，原因如下:

• 每次增删都需要维护红黑树的平衡性，相较于维护B+树的结构，维护红黑树显得费时费力。
• 相较于红黑树，B+树拥有多路，进一步降低了树的高度，加快了查询的速度。

为什么大多不使用B树

B+树的结构原自于B树，它与B树有一些微小的区别，如下:

• B+树的所有非叶子节点都不带有任何数据信息，只有索引信息，所有数据信息全部存储在叶子节点里，这样，整个树的每个节点所占的内存空间就变小了，读到内存中的索引信息就会更多一些，相当于减少了磁盘IO次数。
• 又由B树的性质可以得到，所有叶子节点都会在同一层，B+树会以一个链表的形式将所有叶子节点的信息全部串联起来，这样，想遍历所有数据信息只需要顺序遍历叶子节点就可以了，方便又高效。这使得B+树的查询效率非常稳定，因为所有信息都存储在了叶子节点里面，从根节点到所有叶子节点的路径都是一样长的。

就是因为这些微小的区别导致数据库一般使用B+树作为索引的底层结构。

建索引的几大原则

• 最左前缀匹配原则：非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

• =和in可以乱序：比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

• 尽量选择区分度高的列作为索引：区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

• 索引列不能参与计算：保持列“干净”，比如where from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成where create_time = unix_timestamp(’2014-05-29’);

• 尽量的扩展索引：不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

索引的使用

  索引的使用，这里主要介绍 创建、删除、查看三种方式。

创建索引的三种方式

第一种方式,在执行CREATE TABLE时创建索引:

CREATE TABLE `black_list` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`black_user_id` BIGINT(20) NULL DEFAULT NULL,
	`user_id` BIGINT(20) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
    INDEX indexName (black_user_id(length))
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

使用ALTER TABLE命令去增加索引：

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

//标准语句：
ALTER TABLE table_name ADD INDEX index_name (column_list)//添加普通索引，索引值可出现多次。 
ALTER TABLE table_name ADD UNIQUE (column_list)//这条语句创建的索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。 
ALTER TABLE table_name ADD PRIMARY KEY (column_list)//该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE table_name ADD FULLTEXT index_name(olumu_name);该语句指定了索引为FULLTEXT，用于全文索引。

//针对上述数据库，增加商品分类的索引
ALTER table commodity_list ADD INDEX classify_index  (Classify_Description)

table_name为增加索引的表名，column_list为对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX可对表增加普通索引或UNIQUE索引。

//标准语句：
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
//针对上述数据库：
CREATE INDEX classify_index  ON commodity_list (Classify_Description)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。
PS：不能用CREATE INDEX语句创建PRIMARY KEY索引。

删除索引

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

DROP INDEX [indexName] ON [table_name];
alter table [table_name] drop index [index_name] ;
alter table [table_name] drop primary key ;
//针对上述数据库
drop index classify_index on commodity_list ;

其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

查看索引

SHOW INDEX FROM [table_name];
show keys from [table_name];

上图中:

• Table：表的名称。
• Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1。
• Key_name：索引的名称。
• Seq_in_index：索引中的列序列号，从1开始。
• Column_name：列名称。
• Collation：列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
• Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
• Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
• Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。
• Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。
• Index_type：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
• Comment：注释

索引的分类(根据使用方式分类)

1）普通索引

普通索引就很普通，它没有任何的限制。

创建方式:

//标准语句：
ALTER TABLE table_name ADD INDEX index_name (column_list)
CREATE INDEX index_name ON table_name (column_list); 
//还有建表的时候创建亦可
CREATE TABLE table_name ( 
ID INT NOT NULL, 
column_listVARCHAR(16) NOT NULL,
INDEX [index_name ] 
(column_list(length)) 
);  

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

例子：假如length为10，也就是索引这个字段的记录的前10个字符。

2）唯一索引

与前面的普通索引类似，不同的就是：MySQL数据库索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建方式：

ALTER TABLE table_name ADD UNIQUE (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
//还有建表时创建
CREATE TABLE table_name (
 ID INT NOT NULL, 
 column_list VARCHAR(16) NOT NULL, 
 UNIQUE [index_name ]  
 (column_list(length)) 
 );  

3）主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

创建方式：

CREATE TABLE table_name ( 
ID INT NOT NULL,
 [column] VARCHAR(16) NOT NULL,
 PRIMARY KEY(ID)  
 );  

全文索引（FULLTEXT）

定义

全文检索是对大数据文本进行索引，在建立的索引中对要查找的单词进行进行搜索，定位哪些文本数据包括要搜索的单词。因此，全文检索的全部工作就是建立索引和在索引中搜索定位，所有的工作都是围绕这两个来进行的。

建立全文索引中有两项非常重要，一个是如何对文本进行分词，一是建立索引的数据结构。分词的方法基本上是二元分词法、最大匹配法和统计方法。索引的数据结构基本上采用倒排索引的结构。分词的好坏关系到查询的准确程度和生成的索引的大小。

应用：

FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

但是要注意：对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。因为！！插入修改删除表的同时也要针对索引做一系列的处理。

创建方法：

//针对content做了全文索引：
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);

SQL使用全文索引的方法：

首先必须是MyISAM的数据库引擎的数据表，如果是其他数据引擎，则全文索引不会生效。

SELECT * FROM article WHERE MATCH( content) AGAINST('想查询的字符串')

此外，MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

注意：
目前，使用MySQL自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。

如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

5）单列索引与多列索引

单列索引

顾名思义，它的意思就是索引只有一列，我们平常建的索引就是这一种。

PS:多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格（获得结果集记录数最少）的索引。

多列索引

顾名思义，就是索引可以同时用多个列组合在一起，MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。（在CHAR和VARCHAR列上，你也可以使用列的前缀作为一个索引的部分)。

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引（多列索引）。

多列索引创建方式

//假设只使用单列索引名字
 ALTER TABLE people ADD INDEX name (name);
 //使用多列索引：
  ALTER TABLE people ADD INDEX height_name_age (height,name,age);
  //相当于创建了(height)单列索引，(height,name)组合索引以及(height,name,age)组合索引

为什么使用多列索引

以联合索引(a,b,c)为例：

• 建立这样的索引相当于建立了索引a、ab、abc三个索引。
• 覆盖(动词)索引。同样的有联合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=xxx and b = xxx。那么MySQL可以直接通过遍历索引取得数据，而无需读表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
• 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知。

多列索引使用范围

• 全字段匹配
• 匹配部分最左前缀
• 匹配第一列
• 匹配第一列范围查询(可用用like a%,但不能使用like %b)
• 精确匹配某一列和和范围匹配另外一列

PS:在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

数据库索引的优缺点

优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

索引的分类(根据结构分类)

1）聚集索引：

数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

打个比方，一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面，现在用一个简单的示意图来大概说明一下在数据库中的样子：

PS：第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。

结合上面的表格就可以理解这句话了吧：数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

聚集索引优点

索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询，后面会详细讲。因此在查询方面，聚集索引的速度往往会更占优势。

创建聚集索引

如果不创建索引，系统会自动创建一个隐含列作为表的聚集索引。

1.创建表的时候指定主键（注意：SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引）

create table t1(
    id int primary key,
    name nvarchar(255)
)

2.创建表后添加聚集索引

• SQL Server

create clustered index clustered_index on table_name(colum_name)

• MySQL

alter table table_name add primary key(colum_name)

值得注意的是，最好还是在创建表的时候添加聚集索引，由于聚集索引的物理顺序上的特殊性，因此如果再在上面创建索引的时候会根据索引列的排序移动全部数据行上面的顺序，会非常地耗费时间以及性能。

2）非聚集索引：

该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

非聚集索引的二次查询问题

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，因此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

以及聚集索引clustered index(id), 非聚集索引index(username)。

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id, username from t1 where username = '小明'
select username from t1 where username = '小明'

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：

select username, score from t1 where username = '小明'

在SQL Server里面查询效率如下所示，Index Seek就是索引所花费的时间，Key Lookup就是二次查询所花费的时间。可以看的出二次查询所花费的查询开销占比很大，达到50%。

在SQL Server里面会对查询自动优化，选择适合的索引，因此如果在数据量不大的情况下，SQL Server很有可能不会使用非聚集索引进行查询，而是使用聚集索引进行查询，即便需要扫描整个聚集索引，效率也比使用非聚集索引效率要高。

还有一点要注意的是非聚集索引其实叶子节点除了会存储索引覆盖列的数据，也会存放聚集索引所覆盖的列数据（即主键）。

如何解决非聚集索引的二次查询问题（建立复合索引）

建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句:

select col1, col2 from t1 where col1 = '213';

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

在SQL Server中还有include的用法，可以把非聚集索引里包含的列包含进来，而不一定需要建立复合索引。

总结与使用心得

• 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
• 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
• 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在区分度高的列上面性能会更好。

数据库引擎

数据库引擎在数据库中的重要性，就相当于发动机在机动车中的地位。所以要深入学习数据库，必须要了解数据库引擎。首先我们要知道数据库引擎是什么，然后要学会合理选择数据库引擎。

什么是数据库引擎

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

为什么要合理选择数据库存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

存储引擎作用

• 设计并创建数据库以保存系统所需的关系或XML文档。
• 实现系统以访问和更改数据库中存储的数据。包括实现网站或使用数据的应用程序，还包括生成使用SQL Server工具和实用工具以使用数据的过程。
• 为单位或客户部署实现的系统。
• 提供日常管理支持以优化数据库的性能。

修改数据库引擎

修改方式一：

修改配置文件my.ini
将mysql.ini另存为my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重启服务，数据库默认的引擎修改为InnoDB。

修改方式二：

在建表的时候指定

create table mytbl(   
    id int primary key,   
    name varchar(50)   
)type=MyISAM;

修改方式三：

建表后更改

alter table table_name type = InnoDB;

怎么查看修改成功？

• show table status from table_name;
• show create table table_name
• 使用数据库管理工具

MySQL各大存储引擎

使用show engines;可以查看目前安装的MySQL支持什么数据库引擎。

存储引擎主要有： 1. MyIsam 、2. InnoDB 、3. Memory 、4. Blackhole 、 5. CSV 、6. Performance_Schema 、 7. Archive 、8. Federated 、 9 Mrg_Myisam

一般情况下我们主要使用MyIsam 与 InnoDB,所以接下来我们对这两个引擎进行研究，掌握了这两种，其它的需要时自行百度即可。

InnoDB

Mysql 默认的存储引擎为InnoDB。

大概介绍

InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。

使用时机

需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

//这个就是select锁表的一种，不明确主键。增删改查都可能会导致锁全表，在以后我们会详细列出。
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

应用场景

• 经常更新的表，适合处理多重并发的更新请求。
• 支持事务。
• 可以从灾难中恢复（通过bin-log日志等）。
• 外键约束。只有他支持外键。
• 支持自动增加列属性auto_increment。

MyIsam

MyIASM是老版本MySQL默认的引擎。

大概介绍

它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

独立于操作系统

MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。

这意味着：引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.MYD文件用于存储表的数据，另一个是.MYI文件，存储的是索引。操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理。MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。

使用场景

• 不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
• 不支持外键的表设计。
• 查询速度很快，查询多时很始用。
• 对表进行加锁的场景。
• MyISAM极度强调快速读取操作。
• MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。
• 如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

Memory（也叫HEAP）堆内存

使用存在内存中的内容来创建表。

大概介绍

每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。

但是一旦服务关闭，表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。

适用场景：

• 那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。
• 目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。
• 数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
• 存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。

注意： Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。

特性要求：

• 要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用（长度不固定的）。
• 要记住，在用完表格之后就删除表格。

剩余的引擎各种各样，各有各的应用场景，需要时可以自己再去学习，这里就不再一一叙述了，反正都很方便，当你有特殊的需求时，不要想着自己直接写一个service层的控制，应该先看看Mysql支持的各种引擎能不能满足你的各种需求。

InnoDB与MyIsam应用对比

必须使用 InnoDB的情况：

1）可靠性高或者要求事务处理，则使用InnoDB。这个是必须的。
2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。

适合使用MyISAM的场景：

1）做很多count的计算的。如一些日志，调查的业务表。
2）插入修改不频繁，查询非常频繁的。

PS:MySQL能够允许你在表这一层应用数据库引擎，所以你可以只对需要事务处理的表格来进行性能优化，而把不需要事务处理的表格交给更加轻便的MyISAM引擎。对于 MySQL而言，灵活性才是关键。

InnoDB与MyIsam效果对比总述：

1）事务。MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持，提供事务支持已经外部键等高级数据库功能。

2）性能主题。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。

3）行数保存。InnoDB 中不保存表的具体行数，也就是说，执行select count() fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的。

4）索引存储。对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

5）服务器数据备份。InnoDB必须导出SQL来备份，LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

• MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
• InnoDB是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

6）锁的支持。MyISAM只支持表锁。InnoDB支持表锁、行锁。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

InnoDB和MyIsam引擎原理

MyIASM引擎的索引结构：

MyISAM索引结构: MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。
B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

因此，查询过程为： MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，根据data域的值去读取相应数据记录。

InnoDB引擎的索引结构

其索引结构也是B+Treee索引结构。Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。
InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。

建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

而且，与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

InnoDB搜索：

• 主键搜索 : 若使用where id = 13这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
• 非主键搜索 : 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。
  
  
  
  
  
  

参考链接：

数据库索引到底是什么，是怎样工作的
数据库索引实现原理
数据库索引一二事（三）–索引的底层结构
聚簇索引 与 非聚簇索引
数据库引擎浅谈
MySQL优化系列（三）–索引的使用、原理和设计优化
聚集索引与非聚集索引的总结