【MySQL】MySQL的存储引擎和索引详解（聚集索引和非聚集索引）
目录
一、MySQL存储引擎
注意，MySQL的存储引擎是表级的，同一个数据库的不同表可以使用不同的引擎
1.1 Innodb引擎
Innodb引擎现在是MySQL的默认引擎。Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。
1.2 MyISAM引擎
在MySQL5.1之前，MyISAM是MySQL默认的引擎，它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyISAM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。
1.3 InNoDB与MyISAM异同
- InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text （InNoDB从1.2.X版本开始支持全文搜索的技术）等索引，不支持 Hash 索引，但是给了又有一个特殊的解释：InnoDB存储引擎 是支持hash索引的，不过，我们必须启用，hash索引的创建由InnoDB存储引擎引擎自动优化创建，是数据库自身创建并使用，DBA（数据库管理员）无法干预；
- MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
- Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
- MyISAM引擎不支持外键，InnoDB支持外键
- MyISAM引擎的表在大量高并发的读写下会经常出现表损坏的情况
- 对于count()查询来说MyISAM更有优势，MyISAM直接通过计数器获取，MyISAM会有一个空间转专门又来存储行数。InnoDB需要通过扫描全部数据，虽然InNoDB存储引擎是支持行级别锁，InNoDB是行级别锁，是where对他主键是有效，非主键的都会锁全表的
- MyISAM引擎的表的查询、更新、插入的效率要比InnoDB高，如果你的数据量是百万级别的，并且没有任何的事务处理，那么用MyISAM是性能最好的选择。并且MyISAM可以节省很多内存，因为MyISAM索引文件是与数据文件分开放置，并且索引是有压缩，内存使用率提高不少
- 平台承载的大部分项目是读多写少的项目，MyISAM读性能比InNoDB强很多
MyISAM和innoDB的区别总结如下：
| MyISAM和innoDB引擎对比 | MyISAM | innoDB |
| 索引类型 | 非聚簇 | 聚簇 |
| 支持事务 | 是 | 否 |
| 支持表锁 | 是 | 是 |
| 支持行锁 | 否 | 是（默认） |
| 支持外键 | 否 | 是 |
| 支持全文索引 | 是 | 是（5.6以后支持） |
| 适用操作类型 | 大量select下使用 | 大量insert、delete和update下使用 |
1.4 两种引擎的选择
- 大尺寸的数据集趋向于选择InnoDB引擎，因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。
- 主键查询在InnoDB引擎下也会相当快，不过需要注意的是如果主键太长也会导致性能问题，因为在检索索引树的时候不管是主键索引还是辅助键索引最终都是会通过比较主键来进行检索进而取得行数据的，如果逐渐太长，那么比较主键的操作也会变复杂。
- 大批的INSERT语句(在每个INSERT语句中写入多行，批量插入)在MyISAM下会快一些
- 但是UPDATE语句在InnoDB下则会更快一些，尤其是在并发量大的时候。
二、索引(Index)
索引（Index）是帮助MySQL高效获取数据的排好序的数据结构。MyISAM和Innodb都使用了B+树这种数据结构做为索引。每建一个索引就会将索引数据按照B+树的数据结构创建，将相关数据冗余的存储一份，但是这样能加快搜索速度，很明显的一个用空间换时间的例子。
数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引分为聚簇索引和非聚簇索引两种，在一个表中只能有一个聚集索引，在InnoDB引擎中以主键作为聚集索引，而非聚集索引可以有多个，除了聚集索引其他都是非聚集索引。
可以说数据库必须有索引，没有索引则检索过程变成了顺序查找，O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引，只要将关键字存储到树的节点即可。当数据库一条记录里包含多个字段时，一棵B+树就只能存储主键（指结点中存储存储主键，使用主键来进行检索），如果检索的是非主键字段，则主键索引失去作用，又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引（使这一课B+树的结点中存储辅助键，使用辅助键来进行检索），这个就是二级索引或者叫辅助键索引，除了主键索引之外其他的所有索引都是二级索引。 这个索引由独立的B+树来组织。有两种常见的方法可以解决多个B+树访问同一套表数据的问题，一种叫做聚簇索引（clustered index ），一种叫做非聚簇索引（secondary index）。这两个名字虽然都叫做索引，但这并不是一种单独的索引类型，而是一种数据存储方式。
不同的存储引擎对聚集索引和非聚集索引的实现方式是不同的：
- 对于InnoDB来说，使用聚集索引的主键索引行数据和主键B+树存储在一起，使用非聚集索引的辅助键B+树只存储辅助键和主键，主键和非主键B+树几乎是两种类型的树。辅助键索引也称为非主键索引或二级索引或次级索引
- 对于MyISAM来说，它不支持聚集索引，所以主键索引和辅助键索引都是非聚集索引。主键B+树和辅助键B+树在叶子节点存储指向真正数据行的指针，通过主键索引树或者辅助键索引树都可以直接找到相应数据行的全部数据。
通过上面的对聚集索引和非聚集索引的简单介绍我们就可以发现聚集索引的主键索引和辅助键索引叶子结点树存储内容是有区别的。而非聚集索引的主键索引和辅助键索引的存储结构其实是没有区别，叶子节点中数据区存储的都是指向数据行数据的指针，唯一的区别就是索引树节点中的索引区存储的索引字段不同了。
2.1 InnoDB存储引擎索引的实现
InnoDB支持是聚簇索引，InnoDB存储引擎将主键索引用聚集索引来管理，二级索引用非聚集索引来管理。将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键（使用主键索引），则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索（使用二级索引），则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作（回表），最终到达叶子节点即可获取整行数据。
<figcaption> 以上数字id是主键，姓名是辅助键 </figcaption> 以下为InnoDB存储引擎的表文件：
- .frm：表的定义，就是描述表结构的文件
- .ibd：表的数据文件和索引文件 i表示的是InnoDB和Index d表示的是Data
由表文件结构就可以看出，InnoDB是支持聚集索引的，它的表数据文件和索引文件放在一起。
- 为什么InnoDB非聚集索引的辅助键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
因为如果InnoDB的辅助键也将行数据全部存到叶子节点，虽然能避免使用辅助键索引进行检索的时候需要检索两次才找到行数据，但是这样会造成冗余的存储数据（因为同一份数据在物理存储器中只能存放在同一个位置，如果像上面这样在非聚集索引中也将表数据存在叶子节点，就只能在存储器中冗余的存两份相同的表数据），同一份数据在硬盘中存储两次，就会造成数据浪费，还有一点就是同一份数据存在两个索引上，一旦对数据库的数据进行修改，还需要保证两颗索引树的数据一致性，这又是一个很麻烦的事情，所以辅助键索引树的叶子节点存储主键值，可以节约空间和保证数据一致性，修改行数据只需要修改主键索引中的行数据就可以了，不需要再对辅助索引进行修改。
- 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？不用UUID
由上面的辅助键索引的检索过程可以看出，辅助键索引必须依赖于主键索引才能查找到完整的行数据，表数据就存在主键索引树中，所以InnoDB引擎必须要有主键索引，如果自己不设置主键索引InnoDB也会自己选一列作为索引，如果自己建的表中没有合适的列作为索引，InnoDB也会自动创建一个隐藏列来作为主键。使用整型自增的主键是为了方便检索，很显然整型数据的比较效率要高于随机ASCII码字符串的比较。
如果InnoDB引擎的表自己不主动定义主键，那么MySQL会自己想办法创建主键，过程如下：
- 如果没有为表定义PRIMARY KEY，MySQL将找到第一个UNIQUE索引，其中所有键列都是NOT NULL，而InnoDB将它用作聚集索引。
- 如果表没有PRIMARY KEY或合适的UNIQUE索引，InnoDB会在包含行ID值的合成列内部生成名为GEN_CLUST_INDEX的隐藏聚集索引 。这些行按InnoDB分配给此类表中的行的ID排序。行ID是一个6字节的字段，在插入新行时会单调增加。因此，由行ID排序的行在物理上处于插入顺序。
- 之前一直有一个误区，认为主键索引就是聚集索引
纠正一下这个误区，因为MySQL的默认存储引擎是InnoDB,所以创建的主键索引就是聚集索引。其实主键索引和聚集索引并没有必然联系，非聚集索引也有主键索引。聚集索引只是一种数据存储的方式，不同的存储引擎有不同的实现。只是因为MySQL默认是InnoDB引擎，所以创建的主键也就默认是聚集索引。即主键是聚集索引还是非聚集索引取决于这个表的存储引擎是InnoDB还是MyISAM。
2.2 MyISAM索引实现(非聚集索引)
MyISAM使用的是非聚簇索引，不支持聚集索引。非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据（指向的就是对应的这一行全部的字段数据），对于表数据来说，主键索引树和辅助键索引树没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
<figcaption> 以上Col1列数字id是主键，Col2列的分数是辅助键 </figcaption>
以下为MyISAM存储引擎下的表文件：
- .frm:表的定义，就是描述表结构的文件
- .MYD:数据存储文件 D表示的是Data MY表示的是MyISAM
- .MYI:索引存储文件 I表示的是Index
由表文件组成可以看出，MyISAM存储引擎是使用的非聚集索引，它的数据文件和索引文件是分开的。
2.3 InnoDB的索引与InnoDB的索引的区别
为了更形象说明这两种存储引擎中的索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
我们重点关注聚簇索引，看上去InnoDB的效率明显要低于MyISAM，因为每次使用辅助索引检索都要经过两次B+树查找，而MyISAM的非聚集索引使用辅助键查询只需要一次就能找到一整行的元组数据。这不是多此一举吗？聚簇索引的优势在哪？
1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，而不用再通过存储的地址再去硬盘中查询一次数据行，如果按照主键Id来组织数据，获得数据更快。
2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，详细可以看计算机操作系统分页管理相关章节）会随着数据库里数据的修改而发生变化（B+树节点分裂以及Page的分裂），使用InnoDB就可以保证不管这个主键B+树（聚集索引）的节点如何变化，辅助索引树（非聚集索引）都不受影响。
3 聚集索引的数据都是按顺序存放的，所以如果查询条件是主键，使用主键索引，那么聚集索引会非常快，因为相同范围段的数据都是连续存放在一起的。即聚集索引表记录的物理排列顺序与索引的逻辑排列顺序一致，优点是查询速度快，一旦符合条件的第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。聚集索引的主键索引的叶子节点中直接存储行数据，又因为B+树的叶子节点之间都会用过指针相连，所以直接就能很快将这个范围内的数据全部获取。但是非聚集索引的主键索引虽然在逻辑上相同范围的叶子节点是顺序存储在一起的，但是真实的行数据是在硬盘中散列存储的，要想获取数据还需要将存储在叶子节点中的地址取出，根据地址再去硬盘中获取数据，效率就慢了很多。这个是聚集索引的主键索引的优势，也是第一条优势的具体体现。根据局部性原理，这也会提高检索效率。
局部性原理是指CPU访问存储器时，无论是存取指令还是存取数据，所访问的存储单元都趋于聚集在一个较小的连续区域中。
聚集索引的劣势有哪些？
1 聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。插入数据时速度要慢(时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入)。而非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式（这个指针可能是真实的物理地址，也可能是对应的主键值，这根据不同的存储引擎对它实现是不同的）。非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。
总的来说，聚集索引查询数据速度快，插入数据速度慢;非聚集索引反之。他们各自优缺点就是相反的。所以非聚集索引的优缺点看上面聚集索引的优缺点就够了。
2.4 下面用一组实例来比较聚集索引和非聚集索引的根本区别
2.4.1 根本区别
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。
2.4.2 聚集索引
聚集索引表记录的排列顺序和索引的排列顺序一致（以InnoDB聚集索引的主键索引来说，叶子节点中存储的就是行数据，行数据在物理储器中的真实地址就是按照主键索引树形成的顺序进行排列的），所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序（因为在真实物理存储器的存储顺序只能有一种，而插入新数据必然会导致主键索引树的变化，主键索引树的顺序发生了改变，叶子节点中存储的行数据也要随之进行改变，就会发生大量的数据移动操作，所以效率会慢）。因为在物理内存中的顺序只能有一种，所以聚集索引在一个表中只能有一个。
2.4.3 非聚集索引
非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致（在逻辑上数据是按顺序排存放的，但是物理上在真实的存储器中是散列存放的），两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。所以如果表的读操作远远多于写操作，那么就可以使用非聚集索引。
2.4.4 例子对比两种索引
聚集索引就类似新华字典中的拼音排序索引，都是按顺序进行，例如找到字典中的“爱”，就里面顺序执行找到“癌”。而非聚集索引则类似于笔画排序，索引顺序和物理顺序并不是按顺序存放的。总的来说，聚集索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块
索引创建Demo
create database IndexDemo
go
use IndexDemo
go
create table ABC
(
A int not null,
B char(10),
C varchar(10)
)
go
insert into ABC select 1,'B','C'
union select 5,'B','C'
union select 7,'B','C'
union select 9,'B','C'
go select * from abc 这个时候查看表记录，如图一显示
这个时候插入一条数据，
insert into abc values('6','B','C') 此时的查询记录为图二展示
添加聚集索引，再查询数据显示为图三，此时发现表的顺序发生了变化，此时的排序按A字段的递增排序。这就说明了使用聚集索引如果插入新数据会进行重新排序
create clustered index CLU_ABC on abc(A) 
删除聚集索引，会发现表的顺序不会发生改变。
create nonclustered index NONCLU_ABC on abc(A)
聚集索引和非聚集索引的区别总结：
- 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
- 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
- 聚集索引：物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序
- 非聚集索引：物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序.
- 索引是通过B+树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
三、如何选择聚集索引和非聚集索引
| 动作描述 | 使用聚集索引 | 使用非聚集索引 |
| 列经常被分组排序 | 是 | 是 |
| 返回某范围内的数据 | 是 | 否 |
| 一个或极少不同值 | 否 | 否 |
| 小数目的不同值 | 是 | 否 |
| 大数目的不同值 | 否 | 是 |
| 频繁更新的列 | 否 | 是 |
| 外键列 | 是 | 是 |
| 主键列 | 是 | 是 |
| 频繁修改索引列 | 否 | 是 |
四、总结
我们需要搞清楚以下几个问题：
第一：聚集索引的约束是唯一性，是否要求字段也是唯一的呢？ 不要求唯一！
分析：如果认为是的朋友，可能是受系统默认设置的影响，一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。
结论：聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。
第二：为什么聚集索引可以创建在任何一列上，如果此表没有主键约束，即有可能存在重复行数据呢？
粗一看，这还真是和聚集索引的约束相背，但实际情况真可以创建聚集索引。
分析其原因是：如果未使用 UNIQUE 属性创建聚集索引，数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎 将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。
第三：是不是聚集索引就一定要比非聚集索引性能优呢?
如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？
答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引，此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。就是说我们用学分去建立非聚集索引，那么搜索出来之后结点中的索引数据区只存有学分的数据，还需要根据叶子节点中数据区中的地址去查询，但是如果直接将要查询的学分字段和姓名字段创建一个联合索引（也是非聚集索引），这样在索引树中查找到数据之后直接就能在节点的索引数据区取得两个索引值，就不用再通过叶子节点中数据区里面的地址再去查询一次了。
第四：在MySQL数据库中通过什么描述聚集索引与非聚集索引的？
索引是通过B+树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：InnoDB中的聚集索引的叶节点就是最终的数据节点，InnoDB中的非聚集索引叶子节点指向的是相应的主键值。而MyISAM中非聚集索引的主键索引树和二级索引树的叶节仍然是索引节点，但它有一个指向最终数据的指针。
第五：在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？
聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。插入数据时速度要慢(时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入)。非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。
其他相关文章：【MySQL】InnoDB存储引擎，MyISAM存储引擎，聚集索引，非聚集索引，主键索引，二级索引他们之间的关系梳理
【MySQL】InnoDB行格式、数据页结构以及索引底层原理分析
【MySQL】主从复制实现原理详解
【MySQL】MySQL分库分表详解
【MySQL】MySQL的锁与事务隔离级别详解
参考资料：高性能MySQL(第3版)
华为成长空间 480人发布
