MySQL索引的学习总结

花了一周的时间研究了一下MySQL的索引机制，做一点简单的总结和信息梳理吧。由于刚刚开始学习，如果哪里我理解的有误，还请大家提出宝贵意见。

1、关于索引的基本认识：

     1.1、MySQL中索引被称为“键(key)”。

     1.2、由存储引擎层来负责实现。

              注意：不是服务层实现。服务层通过API和存储引擎层进行通信。MySQL服务器逻辑架构图从上到下为：

                         服务层1(连接\线程处理)                                  －－－[非MySQL独有]

                                ｜

                         服务层2(查询缓存  or 解析器  or  优化器)       －－－[MySQL核心服务功能]

                                ｜

                         存储引擎层                                                      －－－[数据的存储和提取]

2、索引能给我们带来的三个好处：

      2.1、大大减少服务器扫描的信息量。

              因为有效的索引可以在存储引擎层过滤掉大部分不符合当前查询的数据。

      2.2、帮助服务器避免排序和临时表。

               关于排序：因为我们知道常见的B-Tree索引是按照顺序在磁盘存储数据的，所以我们在提取连续数据的时候数据本身就是有序的。我们都知道group   by 比order  by 仅仅多了后面一步分组操作，也就是两者均会排序。所以如果建立的索引能够满足排序请求，则该索引就能避免本次查询中的排序。

              关于临时表，此处的临时表应该是指heap临时表，这种表是MySQL自动创建并用来存储某些中间数据的，这一操作发生在优化或者执行阶段。这种临时表的数据存储在内存当中。如果可以通过索引直接获得group by 结果，则可以不用使用临时表来group by了。

      2.3、将随机I/O变为顺序I/O。

               如果select语句的数据分散在磁盘的不同页中，那么最坏的情况下每条记录都会导致一次磁盘I/O。但是如果select语句查询的列为索引中的列，因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O少得多。这也是覆盖索引的优势之一。

3、MySQL常用的B-Tree索引机制介绍：

       说明：

              a、关于MySQL的引擎我们主要讨论InnoDB，关于索引我们主要讨论B-Tree。

              b、MySQL 中建表时，通常默认其存储引擎为InnoDB，这是由InnoDB自身强大的优势决定的。引擎可以在建表时根据engine设定，也可以后期通过alter table 修改。具体写语法请自己学习。

              c、当人们在谈论索引的时候，如果没有指明特定类型，那么多半说的是B-Tree索引，它是用B-Tree数据结构来存储数据。B-Tree实质为B+Tree，即为每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。对于B+Tree更详细的细节可以参考计算机科学方面的书籍。大多数MySQL存储引擎都支持这种索引，虽然各个引擎实现B-Tree索引的方式并不相同，性能各不相同，各有优劣；甚至有的存储引擎内部实际上使用了T-Tree结构存储实现，虽然其名字依然为B-Tree。但这些变种都不在我们本次讨论的范围之内。

       3.1、B-Tree加快数据访问速度原因：

               存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根结点开始进行搜索。

       3.2、B-Tree查找过程：

               通过比较节点项的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么是记录不存在。叶子节点比较特殊，叶子节点存储的内容为指针，它们的指针指向的是被索引的数据而不是其它的节点页(不同引擎的“指针”类型不同)。

       3.3、适用于B-Tree的查询类型：

               说明：查询类型的确定源于B-Tree对索引列在磁盘是顺序存储组织的，所以非常适合查找范围数据。B-Tree适用于全键值、键值范围或最左前缀的三类查找。6个具体类型如下：

                3.3.1、全值匹配：查找条件为索引列的所有列，此处列的顺序可以和索引中的顺序不同。

                3.3.2、匹配最左前缀：查找条件为索引列的第一列。

                3.3.3、匹配列前缀：查找条件为索引列中第一列的值的开头部分，left(str,len)来实现。

                3.3.4、匹配范围值：查找条件为索引列的第一列，且查找条件为该列的范围值，比如查找姓氏在A和B之间的值。

                3.3.5、精确匹配某一列并范围匹配另外一列：查找条件为索引中的第一个和第二个列，第一列为col1＝‘xxx’ ，第二列为范围匹配。

                3.3.6、只访问索引的查询：查询列为索引中的列，即为查询只需要访问索引即可，无需访问数据行。

        3.4、B-Tree的一些限制：

                3.4.1、如果不是按照索引的最左列开始查找，则无法使用索引。                

                3.4.2、不能跳过索引中的列。例如：索引 idx_fk_retalinfo(col1,col2,col3)包含了三个索引，如果查找条件为col1='xxx' and col3='xxx' ，则MySQL仅能使用索引的第一列。

               3.4.3、如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找。

                          注意：范围列还是可以使用索引的，只是范围列右边的列不能使用索引列而已。

4、MySQL的聚簇索引机制介绍：

        说明：并不是所有的存储引擎都支持聚簇索引，接下来我们要研究的是InnoDB引擎下的聚簇引擎，但其中讨论的原理对其它任何支持聚簇索引的存储引擎都是适用的。

        4.1、InnoDB聚簇索引本质

                聚簇索引本质是一种存储结构，而不是一种索引类型。在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页上。术语“聚簇”表示：数据行和相邻的键值紧凑的存储在一起(虽然这并非总是成立的)。InnoDB通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。

        4.2、与B-Tree索引区分

                它比B-Tree索引多的就是与键值对应的数据行而已。其节点页只是包含了索引列，叶子页包含了行的全部数据。全部数据是指：键值固定时，该行中其它列的值。

        4.3、聚簇索引的优点：

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

                 4.3.2、数据访问更快。因为聚簇索引将索引和数据保存在一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找快。

                 4.3.3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值。因为聚簇索引的节点中保存了主键值，所以如果在覆盖索引中查找的列刚刚好有主键列，那么其主键值可以直接在聚簇索引中直接获取。

        4.4、聚簇索引的缺点：

                4.4.1、聚簇索引的优势就是极大提高了I/O密集型应用的性能。但是在一中场景中其优势无法体现：如果数据本身比较小且已经都存在内存当中了，那么根据内存本身由DRAM实现可知，可以随机访问内存当中的任意单元的内容，则访问顺序就没那么重要了。

               4.4.2、插入速度严重依赖于插入顺序。按照主键的插入顺序是加载数据到InnoDB表中速度最快的方式。因为聚簇索引本身是按照主键存储数据行的，这样在插入数据时，则可以根据B-Tree本身就有序的情况找到合适位置直接插入数据即可。

               4.4.3、更新聚簇索引代价很高。以为这会造成数据行的移动，这代价就很高了。

               4.4.4、基于聚簇索引的表插入新行，或者主键被更新时，可能面临“页分裂”问题。页分裂会导致表占用更多的磁盘空间。比如，原来为页1，页2......，当我们需要在页1插入一条新记录时，则存储引擎会将页1分裂成两个页面来容纳该行，这会导致页1存储数据不饱和，新页2存储数据为原来一半。

               4.4.4、聚簇索引可能导致全表扫描变慢，尤其行比较稀疏，或者由于页分裂导致数据存储不连续时。

               4.4.5、二级索引(MySQL每个表都有一个聚簇索引，除此之外的表上的非聚簇索引都是二级索引)可能比想象中要大，因为二级索引包含来引用行的主键列。   而主键列的类型和数据库的编码方式决定了其占用字节数目。

               4.4.6、二级索引至少需要两次索引查找。第一次为查找二级索引；第二次根据在二级索引中找到的主键值去聚簇索引中查找对应的数据行。

        4.5、InnoDB的数据分布[物理分布]

               说明：因为InnoDB支持聚簇索引，所以其存储数据的方式和不支持聚簇索引的引擎方式时很不一样的。

                4.5.1、聚簇索引结构中的存储内容：

                            内部节点：主键值 

                            叶子节点：主键值，事务ID，用于事务ID和MVCC的回滚指针以及所有的剩余列。

                 4.5.2、二级索引结构中的存储内容：

                            内部节点：索引列值

                            叶子节点：索引列值、主键值

5、高效使用索引的一些规则：

      5.1、使用覆盖索引

              5.1.1、定义：如果一个索引包含(or 说覆盖)所有需要查询的字段的值，我们就称之为“覆盖索引”。

              5.1.2、优势：

                          5.1.2.1、索引条目通常远小于数据行大小。因为只需要读取索引就可以了，索引明显比数据更小。

                          5.1.2.2、因为索引是按照列值顺序存储的(至少在单页中是如此)，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少很多。

                          5.1.2.3、一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能导致严重的性能问题，尤其那些系统调用占了数据访问中的最大开销的场景。

          5.1.2.4、聚簇索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键的二次查询。

              5.1.3注意：不是所有类型的索引都可以成为覆盖索引，因为索引必须要存储索引列的值。比如哈希索引、空间索引等不存储索引列的值，所以无法作为聚簇索引。MySQL中仅有B-Tree索引可以作为覆盖索引。

       5.2、使用索引扫描来排序

              5.2.1、可用索引来排序的条件：

                         5.2.1.1、索引的列顺序和order by顺序一致，并且各个列的排序方式(asc ,desc)是一致的。

                         5.2.1.2、如果查询关联多张表，则只有当order by 子句引用的字段全部为第一张表中的时，才能使用索引做排序。

                         5.2.1.3、order by 子句和查找型查询的限制是一样的：需满足索引的最左前缀的要求；否则，无法使用索引排序。

                         5.2.1.4、有一种情况下order  by 子句可以不满足索引的最左前缀的要求，即为前导列为常量。这可以作为一种技巧而使用某个索引。

       5.3、最好的索引：既满足排序，又满足查找行。对此，提出一个三星系统的概念：

               如何评价一个索引是否适合某个查询，提出了“三星系统”(three-star system)：

               a、索引将相关的记录放到一起，则获得一星。

               b、如果索引中的数据顺序和查找中的排列顺序一致则获得二星。

               c、如果索引中的列包含了查询中需要的全部列则获得三星。

      5.4、尽可能将范围查询的列放在索引的后面，以便优化器可以使用尽可能多的索引列。

6、其它和索引相关的一些概念性问题：

      6.1、压缩存储：MyISAM使用前缀压缩来减少索引的大小，从而可以让更多的索引可以缓存到内存中，这在某些情况下可以极大提高性能。默认只压缩字符串，但通过参数设置可以对整数做压缩。MyISAM压缩每个索引块的方法是：先完全保存索引块的第一个值，然后将其它值和第一个值比较得到相同前缀和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块第一个值为“perform”，第二个值为“performance”，那么第二个值压缩后存储的类似为“7，ance”这样的形式。MyISAM行指针也采用类似的前缀压缩格式。

     6.2、冗余索引：如果创建了索引(A,B)，再创建索引(A)，这就是冗余索引。冗余索引通常发生在：为表添加新索引的时候。

     6.3、重复索引：指在相同的列上，按照相同的顺序，创建相同的类型的索引。

     6.4、过滤数据时间：key在存储引擎层过滤数据，where在服务层过滤数据。所以索引可以让查询锁定更少的行，从而减少锁的数量。

     6.5、关于InnoDB、索引、锁一些很少有人知道的细节：InnoDB在二级索引上使用共享锁，但读取主键索引需要排他锁。

     6.7、如果想了解某个查询的索引相关的信息，可以explain一下，explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。