MySql 索引 下

通俗点讲

聚簇索引（InnoDB）：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引（MyISAM）：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，MyISAM通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

场景选择

为什么MyISAM会比Innodb 的查询速度快

InnoDB 在做SELECT的时候，要维护的东西比MYISAM引擎多很多；
1）InnoDB 要缓存数据和索引，MyISAM只缓存索引块，这中间还有换进换出的减少
2）innodb寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比INNODB要快
3）InnoDB 还需要维护MVCC一致
InnoDB ：通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。让我们来看看当隔离级别是REPEATABLE READ时这种策略是如何应用到特定的操作的
SELECT InnoDB必须每行数据来保证它符合两个条件
1、InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。
2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。

MyISAM 和 InnoDB 的区别

MyISAM

1. 不支持事务，但是每次查询都是原子的；

2. 支持表级锁，即每次操作是对整个表加锁；

3. 存储表的总行数；

4. 一个 MYISAM 表有三个文件：索引文件、表结构文件、数据文件；

5. 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性；

6. MyISAM读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。

InnoDb

1. 支持 ACID 的事务，支持事务的四种隔离级别；

2. 支持行级锁及外键约束：因此可以支持写并发；

3. 不存储总行数：

4. 一个 InnoDb 引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为 2G），受操作系统文件大小的限制；

5. 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅助索引的数据域存储主键的值；因此从辅助索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；

6. InnoDB 读写阻塞与事务隔离级别相关。

MyISAM索引实现（非聚集）

MyISAM引擎使用B+Tree作为索引结构，索引文件和数据文件是分开的，叶节点的data域存放的是数据记录的地址,因此要回表。如图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。 一遍查询。
底层文件(/usr/local/mysql/data) .frm(表结构),.MYD(数据),**.MYI(索引)。

InnoDB索引实现（聚集）

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
底层文件 **.frm(数据表结构), **.ibd（数据+索引）

为啥innoDB 要主键，且推荐整型的自增主键？

为了构建索引，没有会自动添加一列。
整型比对比字符串效率高，并且整型占用空间小，自增是为了范围查找（对比hash索引）。

结合图再仔细点看

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引的优势

看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

1. 由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

2. 辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

3. 聚簇索引适合用在排序的场合，非聚簇索引不适合

4. 取出一定范围数据的时候，使用用聚簇索引

5. 二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据

6. 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势

1. 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
2. 表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，

所以建议使用int的auto_increment作为主键

主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满（二级索引页可能是不一样的）

3. 如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间。

索引使用经验(结合explain)

单表

1. 全值匹配我最爱，where后面有几列建复合索引。
2. 最佳左前缀法则，如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中间的列。
3. 不在索引列上做任何操作(计算、函数、（自动或手动）类型转换)，会导致索引失效。where abs(a) = 12;
4. 存储引擎不能使用索引中范围条件右边的列,范围查询列放最后建索引。
5. mysql 在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描。
6. is not null 也无法使用索引，但是is null 是可以使用索引的。
7. like 已通配符开头(%abc)索引会失效，全表扫描。
8. 字符串不加单引号索引失效。

索引->平衡二叉树

多表关联

1. 保证被驱动表的join字段已经被索引。
2. left join时，选择小表作为驱动表，大表作为被驱动表。
3. inner join 时，mysql 会自己帮你把小结果集的表作为驱动表。（在不影响结果的情况下优化器调整，order by 多列就不会调整）,如果优化后还是大表是驱动表，可以使用STRAIGHT_JOIN指明驱动表。
4. 子查询尽量不要放在被驱动表，有可能使用不到索引。
5. 能够直接多表关联的尽量直接关联，不用子查询,子查询会新增一趟独立查询。
   

子查询优化

尽量不要使用not in 或者not exists，用left join on xxx is null 替代。，一个表里有，一个关联查询，尽量关联查询。

order by&group by

1. 无过滤，不索引。没有过滤条件（where age = 10； limit 10等）索引用不上。
2. 顺序错，必排序。order by 后面顺序不符合建的复合索引的顺序，Using filesort:。
3. 方向反，必排序。order by 后面要么都升序，要么都降序。一升一降Using filesort:。

总结

在数据库开发中，了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助。例如，知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好做法，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

参考：
https://blog.csdn.net/qq_27607965/article/details/79925288（mysql中innodb和myisam对比及索引原理区别）
https://blog.csdn.net/alexdamiao/article/details/51934917(MYSQL索引：对聚簇索引和非聚簇索引的认识)
https://www.cnblogs.com/wuchanming/p/6886020.html(数据库索引原理及优化
)
https://www.cnblogs.com/leezhxing/p/4420988.html(硬盘的读写原理)
https://www.jianshu.com/p/fa8192853184 （聚簇索引与非聚簇索引（也叫二级索引））