MyISAM存储引擎的索引和InnoDB存储引擎的索引
MyISAM存储引擎的索引和InnoDB存储引擎的索引
MyISAM和InnoDB这两个存储引擎都使用B+树作为索引的结构，但是这两种存储引擎对索引的具体实现方式方面是不同的。下面来具体介绍一下这两种存储引擎的索引具体是如何实现的。
MyISAM存储引擎
MyISAM引擎中，B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data域的值，然后以data域的值为地址读取相应的数据记录。所以MyISAM存储引擎中索引的实现特征是数据和索引分离，这被称为“非聚簇索引”。
如图所示，以Col1列作为主键建立的主索引：
非聚簇索引是一种索引结构和数据分开存放的索引，该索引中索引的逻辑顺序与数据库表行中数据的物理顺序不同。
在MyISAM存储引擎中，只要索引值不重复的索引都被称为主索引。 所以MyISAM存储引擎中可以从在多个主索引。
在MyISAM存储引擎中，索引值重复的索引都被称为辅助索引（又称二级索引）。如下图所示，为Col2列建立的辅助索引的结构示意图：
在MyISAM存储引擎中，无论是主索引还是辅索引，他们的叶子节点都保存的是数据的地址，因此多个索引之间可以保持同步的关系。
当我们在MyISAM存储引擎中创建一个表时，这个表会相应生成三个文件
1、.frm文件，这是表定义文件。
2、.myi文件，这个表存储了数据的索引。
3、.md文件，这个表存储的是数据。
InnoDB存储引擎
InnoDB引擎中，其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶子节点data域保存了完整的数据记录（在Mysql中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据）。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，即数据表的主键列使用的就是主索引。
如图所示为一个数据库表的主键索引的结构示意图：
主键索引和辅助索引
数据表的主键列使用的索引就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。许多情况下我们在建好表之后，并没有主动的建立索引，此时索引是系统帮助我们创建的。其创建的过程如下：
在我们创建好表之后，系统会查看表中有没有主键，如果我们设置了主键，那么系统就会根据这个主键来建立一个主键索引，接着将这个主键索引当为主索引（主索引不允许索引值重复，也不允许值为null）。
如果表中没有设置主键，那么系统就会查看表中有没有唯一索引的字段，如果有，那么系统就会将这个唯一索引的字段当为主索引（主索引不允许索引值重复）。
如果表中没有唯一索引的字段，此时系统就会在表中添加一个隐含字段，这个字段的大小为6字节。接着系统就会根据这个隐藏字段来建立索引，此时这个索引就变成了主索引了。所以InnoDB 存储引擎中的主索引只有一个，其余的索引都作为辅助索引。
在根据主索引搜索时，直接找到key所在的叶子节点即可取出数据；辅助索引是一种非聚集索引，其在叶子节点中存储的数据是索引列所在的表中对应的主键值。所以在根据辅助索引（二级索引）查找时，则需要先搜索辅助索引取出主键的值，然后依据取出的主键查询主索引，通过主键值找到数据，一共查询了两次。
如图所示，以某个数据表的name建立的辅助索引的结构图：
聚簇索引
聚簇索引是一种索引结构和数据存放在一起的索引，数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以对应的聚集索引只能有一个。如果某索引不是聚集索引，则表中的行物理顺序与索引顺序不匹配，与非聚集索引相比，聚集索引有着更快的检索速度。
覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作。
覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。所以对于非聚簇索引来说，并不是必须要回表查询的，只要查询的字段正好是索引的字段，那么就可以直接返回查询的结果，无需再根据主键值到主键索引中再次查询了。
索引优化
1、对于InnoDB存储引擎数据表来说，尽量使用自增主键。
InnoDB使用聚集索引，数据记录本身存放在主索引（B+树）的叶子结点上，这就要求同一个叶子结点（大小为一个内存页或磁盘页）的数据记录按主键顺序存放，每当一条新的记录插入时，mysql会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。 如果使用自增主键，那么每次插入新的记录，记录就会顺序插入到当前节点的下一个位置。这样就会形成一个紧凑的索引结构，每次插入不需要移动已有数据，因此效率很高。
如果使用非自增主键（例如身份证号或学号这种无序字符串），每次插入主键近似随机，每次记录都要插入到现有索引页的中间的某个位置，这时不得不移动元素来完成插入，增加了开销。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
2、最左匹配原则
联合索引：mysql可以将多个列按照顺序作为一个索引，这种索引叫做联合索引。
索引的最左匹配原则是：假如索引列分别为A，B，C，顺序也是A，B，C，那么：
- 查询的时候，如果查询【A】，【A，B】，【A，B，C】，可以使用索引查询。
- 如果查询的时候，查询【A，C】，由于中间缺失了B，那么C这个索引是用不到的，只能用到A索引。
- 如果查询的时候，查询【B】，【B，C】或【C】，由于缺失了最左前缀A，那么是用不到这个联合索引的，除非有其他索引。
- 如果查询的时候使用范围查询，并且是最左前缀，那么可以用到索引，但是范围后面的字段无法用到索引。
这个原则可以结合索引的原理来理解：Mysql索引是B+树这种复合结构，当索引是联合索引，比如【name,age,sex】时，B+树是按照从左到右的顺序建立索引树的。当（张三，20，M）这样的数据来检索时，B+树会优先根据name来确定下一步的搜索方向，如果name相同再比较name和sex，最后得到检索的数据。但当（20，M）这样的数据来的时候，mysql就不知道该查哪个节点，因为建立索引的时候，name就是第一个比较因子，必须先根据name去确定下一步去哪里搜索。当（张三，M）这样的数据来时，可以根据name是“张三”，来确定下一步的搜索，然后再去匹配性别是“M”的数据，因此只能用到联合索引中name这个索引。
3、其他原则
1.选择合适的字段创建索引：
- 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
- 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
- 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
- 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
- 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
2.被频繁更新的字段应该慎重建立索引。
虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。
3.尽可能的考虑建立联合索引而不是单列索引。
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
4.注意避免冗余索引 。
冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
5.考虑在字符串类型的字段上使用前缀索引代替普通索引。
前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。
6.索引列在sql语句中不能参与运算，否则会导致索引失效。
例如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。应该改成create_time = unix_timestamp(’2014-05-29’);
参考：
MySQL | 索引 | MyISAM存储引擎的索引 和 InnoDB存储引擎的索引（图文详解）_ThinPikachu的博客-CSDN博客
