数据库索引问题

B+Tree

  B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。B+Tree与B-Tree的结构很像，但是也有几个自己的特性：

1、所有的非叶子节点只存储关键字信息。
2、所有卫星数据（具体数据）都存在叶子结点中。
3、所有的叶子结点中包含了全部元素的信息。
4、所有叶子节点之间都有一个链指针。

innodb引擎数据存储

  在InnoDB存储引擎中，也有页的概念，默认每个页的大小为16K，也就是每次读取数据时都是读取4*4k的大小！假设我们现在有一个用户表，我们往里面写数据

  这里需要注意的一点是，在某个页内插入新行时，为了不减少数据的移动，通常是插入到当前行的后面或者是已删除行留下来的空间，所以在某一个页内的数据并不是完全有序的（后面页结构部分有细讲），但是为了为了数据访问顺序性，在每个记录中都有一个指向下一条记录的指针，以此构成了一条单向有序链表，不过在这里为了方便演示我是按顺序排列的！

  由于数据还比较少，一个页就能容下，所以只有一个根结点，主键和数据也都是保存在根结点（左边的数字代表主键，右边名字、性别代表具体的数据）。假设我们写入10条数据之后，Page1满了，再写入新的数据会怎么存放呢？我们继续看下图

  有个叫“秦寿生”的朋友来了，但是Page1已经放不下数据了，这时候就需要进行页分裂，产生一个新的Page。在innodb中的流程是怎么样的呢？

1、产生新的Page2，然后将Page1的内容复制到Page2。
2、产生新的Page3，“秦寿生”的数据放入Page3。
3、原来的Page1依然作为根结点，但是变成了一个不存放数据只存放索引的页，并且有两个子结点Page2、Page3。

  这里有两个问题需要注意的是
  1、为什么要复制Page1为Page2而不是创建一个新的页作为根结点，这样就少了一步复制的开销了？
  如果是重新创建根结点，那根结点存储的物理地址可能经常会变，不利于查找。并且在innodb中根结点是会预读到内存中的，所以结点的物理地址固定会比较好！

  2、原来Page1有10条数据，在插入第11条数据的时候进行裂变，根据前面对B-Tree、B+Tree特性的了解，那这至少是一颗11阶的树，裂变之后每个结点的元素至少为11/2=5个，那是不是应该页裂变之后主键1-5的数据还是在原来的页，主键6-11的数据会放到新的页，根结点存放主键6？
  如果是这样的话新的页空间利用率只有50%，并且会导致更为频繁的页分裂。所以innodb对这一点做了优化，新的数据放入新创建的页，不移动原有页面的任何记录。

  随着数据的不断写入，这棵树也逐渐枝繁叶茂，如下图

  每次新增数据，都是将一个页写满，然后新创建一个页继续写，这里其实是有个隐含条件的，那就是主键自增！主键自增写入时新插入的数据不会影响到原有页，插入效率高！且页的利用率高！但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率！降低页的利用率！这也是为什么在innodb中建议设置主键自增的原因！

  这棵树的非叶子结点上存的都是主键，那如果一个表没有主键会怎么样？在innodb中，如果一个表没有主键，那默认会找建了唯一索引的列，如果也没有，则会生成一个隐形的字段作为主键！

  有数据插入那就有删除，如果这个用户表频繁的插入和删除，那会导致数据页产生碎片，页的空间利用率低，还会导致树变的“虚高”，降低查询效率！这可以通过索引重建来消除碎片提高查询效率！

innodb引擎数据查找

  数据插入了怎么查找呢？

1、找到数据所在的页。这个查找过程就跟前面说到的B+Tree的搜索过程是一样的，从根结点开始查找一直到叶子结点。
2、在页内找具体的数据。读取第1步找到的叶子结点数据到内存中，然后通过分块查找的方法找到具体的数据。

  这跟我们在新华字典中找某个汉字是一样的，先通过字典的索引定位到该汉字拼音所在的页，然后到指定的页找到具体的汉字。innodb中定位到页后用了哪种策略快速查找某个主键呢？这我们就需要从页结构开始了解。

  左边蓝色区域称为Page Directory，这块区域由多个slot组成，是一个稀疏索引结构，即一个槽中可能属于多个记录，最少属于4条记录，最多属于8条记录。槽内的数据是有序存放的，所以当我们寻找一条数据的时候可以先在槽中通过二分法查找到一个大致的位置。

  右边区域为数据区域，每一个数据页中都包含多条行数据。注意看图中最上面和最下面的两条特殊的行记录Infimum和Supremum，这是两个虚拟的行记录。在没有其他用户数据的时候Infimum的下一条记录的指针指向Supremum，当有用户数据的时候，Infimum的下一条记录的指针指向当前页中最小的用户记录，当前页中最大的用户记录的下一条记录的指针指向Supremum，至此整个页内的所有行记录形成一个单向链表。

  行记录被Page Directory逻辑的分成了多个块，块与块之间是有序的，也就是说“4”这个槽指向的数据块内最大的行记录的主键都要比“8”这个槽指向的数据块内最小的行记录的主键要小。但是块内部的行记录不一定有序。

  每个行记录的都有一个n_owned的区域（图中粉红色区域），n_owned标识这个这个块有多少条数据，伪记录Infimum的n_owned值总是1，记录Supremum的n_owned的取值范围为[1,8]，其他用户记录n_owned的取值范围[4,8]，并且只有每个块中最大的那条记录的n_owned才会有值，其他的用户记录的n_owned为0。

  所以当我们要找主键为6的记录时，先通过二分法在稀疏索引中找到对应的槽，也就是Page Directory中“8”这个槽，“8”这个槽指向的是该数据块中最大的记录，而数据是单向链表结构所以无法逆向查找，所以需要找到上一个槽即“4”这个槽，然后通过“4”这个槽中最大的用户记录的指针沿着链表顺序查找到目标记录。

聚集索引&非聚集索引

  前面关于数据存储的都是演示的聚集索引的实现，如果上面的用户表需要以“用户名字”建立一个非聚集索引，是怎么实现的呢？我们看下图：

  非聚集索引的存储结构与前面是一样的，不同的是在叶子结点的数据部分存的不再是具体的数据，而数据的聚集索引的key。所以通过非聚集索引查找的过程是先找到该索引key对应的聚集索引的key，然后再拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表！

  图中的这些名字均来源于网络，希望没有误伤正在看这篇文章的你~^_^

innodb与MyISAM两种存储引擎对比

  上面包括存储和搜索都是拿的innodb引擎为例，那MyISAM与innodb在存储上有啥不同呢？憋缩话，看图：

  上图为MyISAM主键索引的存储结构，我们能看到的不同是

1、主键索引树的叶子结点的数据区域没有存放实际的数据，存放的是数据记录的地址。
2、数据的存储不是按主键顺序存放的，按写入的顺序存放。

  也就是说innodb引擎数据在物理上是按主键顺序存放，而MyISAM引擎数据在物理上按插入的顺序存放。并且MyISAM的叶子结点不存放数据，所以非聚集索引的存储结构与聚集索引类似，在使用非聚集索引查找数据的时候通过非聚集索引树就能直接找到数据的地址了，不需要回表，这比innodb的搜索效率会更高呢！

索引优化建议？

  大家经常会在很多的文章或书中能看到一些索引的使用建议，比如说

1、like的模糊查询以%开头，会导致索引失效。
2、一个表建的索引尽量不要超过5个。
3、尽量使用覆盖索引。
4、尽量不要在重复数据多的列上建索引。
5、。。。。。。。。。。。
6、。。。。。。。。。。。

 命名规则：表名_字段名
1、需要加索引的字段，要在where条件中
2、数据量少的字段不需要加索引
3、如果where条件中是OR关系，加索引不起作用
4、符合最左原则

联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

两个或更多个列上的索引被称作复合索引。
利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。
复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。
如果您知 道姓，电话簿将非常有用；
如果您知道姓和名，电话簿则更为有用，
但如果您只知道名不姓，电话簿将没有用处。

什么是数据库索引

大家平时在开发过程中都避免不了使用数据库索引，那么你了解数据库索引么，接下来呢，我就简单讲一下什么是数据库索引。

一、数据索引是干什么用的呢？

数据库索引其实就是为了使查询数据效率快。

二、数据库索引有哪些呢？

1. 聚集索引（主键索引）：在数据库里面，所有行数都会按照主键索引进行排序。
2. 非聚集索引：就是给普通字段加上索引。
3. 联合索引：就是好几个字段组成的索引，称为联合索引。

1	key 'idx_age_name_sex' ('age','name','sex')

 联合索引遵从最左前缀原则，什么意思呢，就比如说一张学生表里面的联合索引如上面所示，那么下面A,B,C,D,E,F哪个会走索引呢？

1 2 3 4

A:select * from student where age = 16 and name = '小张' B:select * from student where name = '小张' and sex = '男' C:select * from student where name = '小张' and sex = '男' and age = 18 D:select * from student where age > 20 and name = '小张'<br>E:select * from student where age != 15 and name = '小张'<br>F:select * from student where age = 15 and name != '小张'

 A遵从最左匹配原则，age是在最左边，所以A走索引；

 B直接从name开始，没有遵从最左匹配原则，所以不走索引；

 C虽然从name开始，但是有索引最左边的age，mysql内部会自动转成where age = '18' and name = '小张'  and sex = '男' 这种，所以还是遵从最左匹配原则；

 D这个是因为age>20是范围，范围字段会结束索引对范围后面索引字段的使用，所以只有走了age这个索引；

 E这个虽然遵循最左匹配原则，但是不走索引，因为!= 不走索引；

 F这个只走age索引，不走name索引，原因如上；

三、有哪些列子不走索引呢？

 表student中两个字段age,name加了索引

1 2	key 'idx_age' ('age'), key 'idx_name' ('name')

   1.Like这种就是%在前面的不走索引，在后面的走索引

1 2	A:select * from student where 'name' like '王%' B:select * from student where 'name' like '%小'

  A走索引，B不走索引

  2.用索引列进行计算的，不走索引

1 2	A:select * from student where age = 10+8 B:select * from student where age + 8 = 18

 A走索引，B不走索引

 3.对索引列用函数了，不走索引

1 2	A:select * from student where  concat('name','哈') ='王哈哈'; B:select * from student where name = concat('王哈','哈');

 A不走索引，B走索引

 4. 索引列用了!= 不走索引,如下：

1	select * from student where age != 18

 四、为什么索引用B+树？

  这个可以参考什么是B+树

五、索引在磁盘上的存储？

 聚集索引和非聚集索引存储的不相同，那么来说下都是怎么存储的？

 有一张学生表

create table `student` (
`id` int(11) not null auto_increment comment '主键id',
`name` varchar(50) not null default '' comment '学生姓名',
`age` int(11) not null default 0 comment '学生年龄',
primary key (`id`),
key `idx_age` (`age`),
key `idx_name` (`name`)
) ENGINE=InnoDB default charset=utf8 comment ='学生信息';

  表中内容如下

    

id 为主键索引，name和age为非聚集索引

1.聚集索引在磁盘中的存储

 聚集索引叶子结点存储是表里面的所有行数据；

 每个数据页在不同的磁盘上面；

如果要查找id=5的数据，那么先把磁盘0读入内存，然后用二分法查找id=5的数在3和6之间，然后通过指针p1查找到磁盘2的地址，然后将磁盘2读入内存中，用二分查找方式查找到id=5的数据。

2.非聚集索引在磁盘中的存储

叶子结点存储的是聚集索引键，而不存储表里面所有的行数据，所以在查找的时候，只能查找到聚集索引键，再通过聚集索引去表里面查找到数据。

如果要查找到name = 小徐，首先将磁盘0加载到内存中，然后用二分查找的方法查到在指针p1所指的地址上，然后通过指针p1所指的地址可知道在磁盘2上面，然后通过二分查找法得知小徐id=4；

然后在根据id=4将磁盘0加载到内存中，然后通过二分查找的方法查到在指针p1所指的地址上，然后通过指针p1所指的地址可知道在磁盘2上面，然后通过id=4查找出郑正行数据，就查找出name=小徐的数据了。