这篇索引文章一定要配合上一篇文章B+树和B树的区别 来一起阅读
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。
MyISAM索引实现
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：
这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：
图9
同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
点评：
1. MyISAM的索引是使用B+树数据结构的，所以上面的非叶子节点都是保存了key（索引值），而没有任何的数据，而叶子节点则是有data域存放的是数据记录的地址，注意，不是真正的数据，而是数据记录的地址，找到地址后需要磁盘IO去找对应的数据；
2. MyISAM的主键索引和辅助索引的结构都是一样的：一样是B+树结构，一样是叶子节点的data域保存的具体数据的磁盘地址
3. MyISAM的索引是非聚簇索引的，MyISAM索引文件和数据文件是分离的，因为它的数据不是跟索引聚集在一起，而Innodb是聚簇索引
InnoDB索引实现
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
图10
图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：
图11
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
点评：
1. Innodb的索引也是B+树，主键索引非叶子节点同样只是保存key（索引值），而没有保存任何data域，而叶子节点的data域保存的是完整的数据记录 ，因此InnoDB表数据文件本身就是主键索引
2. Innodb的主键索引是聚簇索引，因为它的主键索引就是数据本身，说明索引跟数据是聚集在一块的，所以它是聚簇索引。按照主键索引搜索数据是十分高效，因为直接在索引上搜索完就能得到数据，而不用再通过IO查找数据
3. Innodb的辅助索引也是B+树，但是它的叶子节点的data域存储的是主键的值，而不是数据的地址，所以根据辅助索引搜索的时候需要两个搜索过程：第一个搜索辅助索引找到主键的值，第二部是根据搜索主键索引的值找到数据。所以效率相对不高
4. 由于Innodb的聚簇索引性质，所以必须要求定义一个主键索引，哪怕你没定义，它也会使用一个隐含字段作为主键。所以我们使用Innodb的时候，最好使用自增字段作为主键，否则不是自增的话，则每次增删改数据的时候，都会导致innodb的主键索引分裂重组，影响效率
索引使用策略及优化
最左前缀原理与相关优化
一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列。 最左前缀索引匹配注意一下几点：
- 从左到右使用上索引，如果中间空了，则后面的索引也用不上
- 如果where a1>1 and a2 = 3 and a3 >3 ，a1使用上了索引，但是由于a1是范围的，a2, a3都用不上索引，而且一个复合索引最多能使用一个范围查询
- 如果where a1='10001' AND a2 LIKE 'Senior%' 这样a1 , a2都用上索引，like的时候，只要%不是在最前面也可以使用上索引，这个也是跟最左匹配索引的性质比较类似
- 如果where a1 BETWEEN '10001' AND '10010' and a2=2 and a3 > 2 这样是3个索引都使用上了，因为a1的between在mysql看来只是跟in一样，不是范围查询，而是多值精确查询
- 表达式中如果使用了任何mysql的函数都不能使用上索引
索引选择性与前缀索引
既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。
第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。
另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T
显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。
比如性别字段，只有男和女两个值，在它身上建立索引是没有意义的，因为它的区分不够高，要选区分度高的，也就是选择性高的作为索引才有价值。
有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
比如有个查询是查 first_name，last_name，
由于<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 4)>，
ADD INDEX first_name_last_name4 (first_name, last_name(4))
这样既能达到很高的选择性，又能把索引空间减低一半。
但这样的前缀索引也有它的缺点：
前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。
因为这样的前缀索引只包含了这个字段的部分数据，因此不能覆盖索引。
InnoDB的主键选择与插入优化
在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。
经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。
上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：
图13
这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：
图14
此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
因此，只要可以，请尽量在InnoDB上采用自增字段做主键。
总结
MyISAM和InnoDB都是使用B+树作为索引的数据结构，也就是说他们的非叶子节点都是存储key（索引值），只有叶子节点才会存储data数据
-
MyISAM跟InnoDB的区别：
- MyISAM的索引和数据是分开的，它的主键索引和辅助索引结构是一样的，都是B+树，而叶子节点的data存储的是指向具体数据存储的位置地址，而不是具体数据，所以它的查询是先查了索引，然后回行IO读取数据。
- InnoDB的主键索引和数据是放在一起的，因此它的主键索引是聚簇索引，它的数据就是一个主键索引。主键索引的叶子节点data存的就是数据本身，因此如果能只根据主键查询，就能索引覆盖，不需要额外的回行IO。 而InnoDB的辅助索引的data存的是主键的值，因此每次根据辅助索引查询，都得先查辅助索引，拿到主键值，再去查主键索引。
- InnoDB的主键必须使用自增的字段，根据B+树的性质，如果字段自增，那么索引值是顺序添加的，就可以形成一个顺序的紧凑的索引结构，如果不是自增而是随机的，那么索引值增加的时候需要移动和分页的插入数据，造成不必要的开销。 而MyISAM没有这个限制，当然自增是最好的。
-
索引优化总结
- 遵循最左前缀匹配原理
- 过长的索引可以使用前缀索引
- 选择字段作为索引时，需要考虑字段的区分度，尽量选择选择性高的字段作为索引
- InnoDB一定要让自增字段作为主键索引
