B+树

              （1）与B-tree区别

                       1>有n个key的节点中有n个指针，而不是b-tree中的n+1个

                       2>所有叶子节点中包含了全部的关键字信息，以及指向这些关键字记录的指针，且叶子节点本身的关键字是从大到小顺序连接的

                       3>所有的非叶子节点实际上就是到达叶子节点的索引

              

                       4>有些b+树还增加了顺序访问指针，指向第一个叶子节点

               

                              这么做是为了提高区间查询的速度。如插叙key从18到49的数据，当找到18后，只要顺着节点顺序遍历就可以一次性访问到所有数据节点，极大提高了区间查询效率

              （2）B+树查找

                         由于和记录相关的地址信息存放在叶节点中，所以就算在上层已经找到待查的关键码，并不停止，而是继续沿着指针向下一直找到叶节点层的关键码。

                         ——>不管查找是否成功，每次查找都会走一条从根节点到叶子节点的路径。

                         ——>正因为这样，指针指向的孩子节点才会仍然包含父节点中的关键字

              （3）B+树与B树作为索引比较

                         B-tree的高度 ：h<=log[ceil(m/2)] (N+1)/2 （[]中为底数），显然其中ceil（m/2）越大，高度越低，那么查询性能越好。

                         一般用一个节点占用一页，所以，其中能存储的key越多，那么就可以有更多指针指向子节点，即出度越大。

                         B树种除了要存放key，还要存放记录其他字段，而b+树只用存储key，所以显然b+树的出度更大，所以就会使得树高越低，io操作越少，查询性能越高

              （4）MySql的MyISAM引擎索引实现

                        MyISAM引擎使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址

                       

                            此处我们使用col1作为主键，上图就是一个主索引。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引在结构上没有区别，只是主索引要求key是唯一的，辅助索引的key可以重复。在col2上简历一个辅助索引，结果如下图：

                      同样也是一棵B+树，data域保存数据记录的地址。一你穿MyISAM的检索算法为：先按照B+树的搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值作为地址，读取相应的记录

                     MyISAM的索引方式也成为”非聚集“的

              （5）MySql的InnoDB引擎索引实现

                        虽然InnoDB也使用B+树作为索引结构，但是具体实现方式和MyISAM完全不同。

                        1.InnoDB的索引文件就是数据文件

                           从上文可以知道，MyISAM的索引文件和数据文件时分离的，索引文件仅仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按照B+树组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引key是数据表的主键，因此InnoDB表数据文件本身就是索引文件

                              

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

                        2.InnoDB辅助索引data域存储相应记录的主键值而不是地址

                           换句话说，就是InnoDB的所有辅助索引都引用主键作为data域。

                         

                    聚集索引这种实现方式使得按主键搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，再用主键到主索引中检索获得的记录。

                        1.InnoDB中，不推荐使用过长的字段作为主键：因为所有的辅助索引都引用主索引，过长的主键会使得辅助索引变得过大

                        2.InnoDB中，不推荐使用非单调字段作为主键：因为InnoDB数据文件本身是一棵B+树，非单调的主键会造成在插入新记录时数据文件为了维持B+树的特性而频繁的分裂调整，而使用自增字段作为主键则是一个很好的选择。

              （5）从索引结构谈索引优化

                      高效使用索引的首要条件是知道什么样的查询会使用到索引，答案就是最左前缀原理。

                      以如下关系数据库为例，便于说明问题：

                      1）联合索引

                           在上面都是假设索引只引用了单个的列，实际上，MySql中的索引可以以一定顺序引用多个列，这种索引叫做联合索引

                           一个联合索引是有一个有序元组<a1,a2...an>,其中各个元素均为数据表的一列。实际上单列索引可以看成联合索引元素数为1的特例。

                      2）检查表索引

                           以titles表为例，先查看上面都有哪些索引

     

                         从结果中可以看到titles表的主索引为<emp_no,title,from_date>，还有一个辅助索引<emp_no>。为了避免多个索引使得事情变复杂，我们这里讲辅助索引drop掉：

                      3）情况一：全列匹配

                         很明显，当按照索引中所有列进行精确匹配（这里精确匹配是指=或in）时，索引可以全部被用到。这里有一点需要注意，理论上 索引对顺序是敏感的，但是由于MySql的查询优化器会自动调整where中条件的顺序以使用合适的索引，例如我们将where中的条件顺序调到：

                       效果是一样的。

                      4）情况二：最左前缀匹配

                         当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>或<emp_no,title>，索引可以被使用到，但是只能使用到一部分，即条件所组成的最左前缀。上面的分析结果可以看到用了Primary索引，但是key_len=4，说明只用到了索引的第一列前缀。

                      5）情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

                        此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀匹配，因此需要对结果进行扫描过滤from_date（因为这里的emp_no唯一，所以就不存在扫描）。

                       如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no，from_date>，此时上面的查询会使用这个索引。

                       除此之外，还可以使用一种称为“隔离列”的优化方法，将emp_no和from_date之间的坑填上。

                       我们先看一下title有多个个值：

                       只有7种。在这种称为坑的列值比较少的情况下，可以使用In来填补这个坑，从而形成最左前缀

                    这次key_len=59，说明索引全部被用了，但是从type和rows可以看出In实际上执行了一个range查询，这里检查了7个key。看下这两种查询的性能比较：

                   填坑后性能提升了一点。如果经过emp_no筛选后余下很多数据，则后者的性能优势会更加明显。当然如果title值很多，用填坑就不合适了，必须建立辅助索引。

                      6）情况四：查询条件没有指定索引第一列

                        由于不是最左前缀匹配，所以这样的查询显然不能用到索引。

                      7）情况五：匹配某列的前缀字符串

                        如果通配符%不出现在开头，则可以用到索引，但是如果不是开头就不行

                      8）情况六：范围查询

                         如果是最左前缀匹配，那么可以用到索引，但是范围列后面的列无法用到索引，同时，索引最多用于一个范围列，因此，如果查询条件中有2个范围列，则无法全部用到索引。

                        可以看到索引对第二个范围索引无能为力。这里特别要说明MySql中的一个特点：仅用explain可能无法区分是范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了between并不意味着就是范围查询，如下面的查询：

                      表面看起来是用了2个范围查询，但实际上作用于emp_no的between相当于in，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部3个列。因此在MySql中要谨慎区分多值匹配和范围匹配，否则会对MySql的行为产生困惑

                      9）情况七：查询条件中含有函数或表达式

                           如果查询条件中含有函数或表达式，则MySql不会为这列使用索引。

                         虽然这个查询和情况五中功能相同，但是由于使用了函数left，则无法为title列使用索引，而情况五中用like就可以。再如：

                        显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySql无法对其使用索引。因此，在写查询语句时应尽量避免表达式出现在查询中，而是应该先手工计算，转换为无表达式的查询语句。

              （6）索引选择与前缀索引

                      既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建立索引？

                      答案是否定的。因为索引虽然加快了查询速度，但是索引也是有代价的：索引文件本身要消耗存储空间；同时索引会增加插入、删除、修改时的负担（因为修改后同时也要修改索引文件）。另外，MySql在运行时也要消耗资源维护索引，因此索引并不是越多越好。

                     一般有以下2种情况不建议使用索引：

                     1）表记录比较少

                          例如一两千条数据的表，没有必要建立索引，让查询做全表扫描就好了。至于有多少条记录才算，这个有不同看法，个人经验以2000作为分界线，不超过2000可以不适用索引，超过2000可以酌情考虑索引。

                     2）索引的选择性较低

                          所谓选择性，其实也就是有多少个不重复的值/表记录总数，显然选择性的取值范围为（0，1]，现则性越高的索引价值越大，这是由B+树的性质决定的。

                          例如，如果title字段经常被单独查询，是否需要建立索引，我们看一下他的选择性：

                       title的选择性不足0.0001（精确值为0.00001579），说明值特别少，所以没有必要建立索引

                       还有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

                       现在以employees表为例，说明前缀索引的作用：

                       employees表中只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：

                     如果频繁按名字搜索员工，这样效率显然很低，因此我们考虑建立索引。有两种选择，建立<first_name>或<first_name,last_name>，看下两个索引选择性：

                  显然<first_name>选择性太低<first_name,last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑使用first_name和last_name的前几个字符建立索引，例如<first_name,left（last_name，3）>，看看其选择性

                 选择性还可以，大师离0.9313还有点远，那么把last_name前缀增加到4：

                这样选择性已经很理想了，而这个索引的长度只有不到<first_name,last_name>的一般，我们把这个前缀索引建上：

               此时再执行一遍按名字查询，比较与建索引前的结果：

             性能明显提升，查询速度提高了120多倍

             前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于order by和group by操作，也不能用于covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）

              （7）InnoDB的主键选择与插入优化

                      在使用InnoDB作为存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

                      InnoDB使用聚集索引，即数据记录本身被保存在主索引的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘块）的各条数据是按主键顺序存放的，因此每当有一条新记录插入时，MySql会根据其主键将其插入适当的节点和位置，如果页面达到装载银子（InnoDB默认为15/16），则开辟一个新的页（节点）

                     如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

            

                     这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

                     如果使用非自增主键（如身份照等），由于每次插入主键的值近似于随机，因此每次新记录都要被插入到现有索引页的中间某个位置：

                                                       

                     此时MySql不得不为了将新纪录插到合适位置而移动数据，甚至目标页面可能已经被会写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

                      因此，只要可以，请尽量在InnoDB上采用自增字段作为主键