mysql索引及索引结构深入理解

1.为什么索引使用B+树而不使用其他的一些数据结构？如：二叉树，红黑色。hash算法在什么场景下适用索引。

       1.二叉树：数据结构在数据递增时深度会逐渐增加，在一些情况下二叉树会变成链表。

       2.红黑树：红黑色虽然是平衡二叉树。但在数据量太大时，会出现深度太深的情况。

       mysql索引查询时，是按照每一层一次IO读取的方式进行的，当树的深度太深时，假如需要查询的数据在最后一行那么IO读取的次数会很大，性能会非常的低。

      我们可以简单计算一下，假设数据库表的数据量为2千万条深度多少 ，我们假设深度为H

            根据计算公式：2^0+2^1+2^2+.........+2^n=2千万

                                     2千万=2^(H+1)-2

            粗略计算一下大概   H=21。假设查询数据在叶子节点要IO 查询21次，所以性能非常差

        3.所用 hash算法：hash算法做索引，会对每一条hash对应数据地址，查询是非常快的。但是使用hash做范围查找和联合索引的情况下索引就会完全失效，性能下降非常快。

         4.使用B+树，B+树数据结构特点：

                  注：为什么不使用B树，因为B树结构特点（节点不重复，不冗余），每个节点都存放数据，导致节点中索引存放少，因为每个节点设置是16K大小。

                   有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

                 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

                   所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素

            5.索引使用B+树时的元素存放：

              mysql默认设置的每个节点为16K,所以每次IO读盘为16K,当然可以自己设置。

                      计算每个节点存放元素个数，按照每个元素大概8B,指针大小大概4B。每个元素14B大小

                      每个节  点存放元素数：16k/14B =1170个。

                      叶子节点存放数据或者数据地址，大小为1k,叶子节点存放元素为16k/1k=16个

                      假设存放3层树，计算存放数据：1170（第一层节点）*1170（第二层节点）*16=2100万数据。

                B+树三层即可存放这么多数据，按照Mysql数据库设计原则，当单表数据超过2千万即可分库分表了

                所以查询一次数据只需要2次IO操作即可，第一层根数据可以常驻内存。性能是非常高的。

             6：常用引擎myisam和InnoDB对比：

                   myisam ：首先，myisam不支持事物操作

                   存在磁盘库中表内容：

                        .frm文件：存放表结构。myd文件:存放数据。myi文件存放索引。

                        B+树索引叶子节点存放数据的地址指针。

                  InoDB ：首选，支持事物操作

                   存在磁盘库中表内容：

                       .frm文件:存放表结构。 .idb文件存放索引+数据。

                       B+树叶子节点存放行数据。

           7.索引的创建：

                注：必须建主键，否则的话mysql会自己建一个rowid。

                  因为B+树叶子节点是按照顺序从左到右顺序排列，所以主键最好用整型自增，这样B+索引就不会再进行重新排序了，节约性能。

                   在使用索引查找时，会进行比较大小，整型数据大小比较比String类型要快的多，String要转换asci码，逐位比较，

                    UUID占用空间比Inter，id大。 

                索引每个表都使用主键索引，能更有效查询数据。

          8.关于回表：

                 注：非主键索引叶子节点存放的是主键

               个人通俗理解，当定义一个非主键索引，根据这个索引去查询数据时，当查询字段有不包含索引的字段，那么查询会根据索引查到主键后再此根据主键索引去查询数据。

                 比如：

                      一张表中存在索引 name

                      select name  from table name="xxxxx"  没有回表，因为查询字段就在此索引上

                      select name,age from table name="xxxxx" 存在回表

             9.关于复合索引

                     复合索引结构会复合最左原则：

                         按照索引最左表一个字段进行B+树排序，因此如果条件查询时第一个条件不是最左边索引（现在查询分析器的优化后可以不在条件最前面，但是需要有），索引将会失效。

                         也同样适用按照左边原则进行索引搜索，否则部分失效

                         比如索引：(a,b,c)

                         如果按照条件查询 a=？and b=? and c=? 索引会完全生效  首选会根据a到B+树中找到相应节点，然后根据b找到相应节点，在找c

                        如果使用 b=? and c=? 会完全失效，因为刚开始就不知道怎么找，B+树是按照a排序。

                        如果适应 a=? and c=?会导致部分失效，只有a起作用

                           可参考https://blog.csdn.net/BThinker/article/details/89149660