索引

    Mysql的索引是由存储引擎实现的，整个数据库查询优化的基石就是索引机制。正确创建合适的索引是提升数据库查询性能的基础。

索引定义与概念

   索引是什么？

    索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。基于硬盘的数据结构。

磁盘的地址代表数据存储的位置。

为什么要用索引？

• 索引能够极大的减少 存储引擎需要扫描的数据量。
• 索引能够把随机IO变成顺序IO
• 索引能够帮助我们在进行分组(group by)、排序(order by)、等操作时避免使用临时表

索引的结构为什么选择B+Tree

二叉查找树：

二叉树的检索效率最高。可能出现数据规划的不太好，形成线性检索的情况，查询效率就不高了。

平衡二叉查找树：

     平衡二叉树 在插入和删除的时候维护了树的平衡，保证树的相对平衡。不会形成线性检索、一个节点就会保存在磁盘块里。数据区通常保存的是数据的地址，磁盘内容，达到快速检索的目的。P1指向的是磁盘块2 的地址（此处以节点10为例）。P1,p2是左右子节点的指针地址。

平衡二叉树作为索引的缺点

1. 太深了：数据处的（高）深度决定着他的IO 操作次数，IO耗时大
2. 每一个磁盘块（节点/页）保存数据量太小了

    （太深是IO 次数太多，太小了浪费IO效率）

     3、 没有很好的利用操作磁盘的IO数据交换特性（操作系统与磁盘交换数据大小，每次做一页是4kb），也没有利用好磁盘IO的预读能力（空间局部性原理，去加载更多的数据），从而带来频繁的IO 操作

       预读能力如果将头部的4k装载进来，操作系统会认为 后面的空间马上就会用到，它会加载数据的时候不是单纯的只加载一页数据的大小，在Mysql里定义的一页数据是16k,通过mysql去进行磁盘交互的话，一次性交互就是16k,一次读操作系统的4页，相当于mysql的一页

多路平衡查找树，B-Tree

       多路平衡查找数B-tree,多路平衡二叉树存储的数据节点比较多，结构上比较矮胖。通过少量IO的次数就能查询出个节点的数据。（比如磁盘块1，将它作为16k，比如以int型id作为索引列，4byte,16k 的byte就是16*1024，一个关键字加上冗余一些内存，将他定义成8 byte,就可以保存2048个关键字。两千多个关键字就意味着就有2049路。所以在定义索引列的时候要尽可能的保证数据合适的长度，能短则短。）

          关键字的个数一定是 度-1 即（路 -1）;  索引不宜建多，要建合适的，否则会影响B树的维护与更新。当数据量大时如果索引不合适，在insert,update,delete的时候，索引的维护会非常的慢。会拖垮性能。绝对平衡：是指他的子节点全部在一个高度。

加强版的多路平衡查找树B+树

       加强版的多路平衡查找树B+树 ，左闭合索引，设计原因id列总是作为主键索引通常id是一个自增，往右边进行插入。 B+树的实现方式，比如查找的是p1他就算命中了1,也会继续往下走，直到找到叶子节点，支节点不保存任何数据，支节点只保存关键字和引用，不保存数据区。所以在支节点上保存的关键字比B 树要更多。数据全部放到叶子节点。

特点：1、B+节点关键字搜索采用闭合区间

  2、B+非叶子节点不保存数据相关信息，只保存关键字和子节点的引用

  3、B+关键字对应用的数据保存在叶子节点中

  4、B+叶子节点是顺序排列的，并且相邻节点有顺序引用的关系（保证数据是有序的）。

为什么选用B+Tree?

      B+树是B-树的变种（Plus）版多路绝对平衡查找树，他拥有B-树的优势

      B+树扫库、表能力更强（只需要扫描页子节点）

      B+树对磁盘读写能力更强（支节点不保存数据区，磁盘区存储的关键个数就越多，所以读写能力就更强）  

      B+树的排序能力更强（叶子节点存储的数据具有顺序性，会将随机IO 优化为顺序IO）

      B+树的查询效率更加稳定（每次都去叶子节点查询数据，所以每次查询用时基本相同，就会更加稳定）

Mysql 中的 myIsam引擎  和 InnDB 引擎都使用了B+ 树来作为的索引数据的存储结构

Mysql B+Tree索引体现形式

Myisam

     Myisam引擎中索引保存在  .MYI文件中 数据保存在.MYD文件 中。在myisam 里面索引B+数据区保存的是数据的物理地址，索引走完之后去拿到地址去加载数据内容，根据地址把数据内容取出来。

 

     Mysiam 引擎实现索引的方式id（主键）索引与name（附加）索引是平级的，各找各的数据区的数据地址，根据数据地址找到数据。

Innodb

     InnoDb只有一个 IDB 文件。InnoDB 是以主键为索引来组织数据的存储。若没有显示的去创建主键索引，Innodb会以唯一键帮你建一个隐式的主键索引，若唯一键也没有就会以一个6位的Int型去创建一个主键索引.InnoDB 实现的主键索引在叶子节点上保存了所有该数据的所有信息，而不是数据的物理地址。

         InnoDB 实现的辅助索引，在叶子节点上保存了主键索引的值，查找方式是基于辅助索引找到主键的值，在基于主键索引找到相关的数据。InnoDb 认为 主键查询时最常用的查询方式，InnoDB 中一旦数据发生迁移，只要主键不变，并不需要维护辅助索引

InnoDB 与 MyIsam 对比

     InnerDB 索引与数据是在一起的，索引中叶子节点存放的是数据的内容

     MyIsam是索引与数据是分开，索引中叶子节点存放的是数据的地址。

索引知识补充

列的离散型：count(distinct col )  : count(col)

     列的离散性取决于：count(distinct col )  与 count(col)  的 比例，比例越大离散性越高。 离散性越高，选择性就越好。对于某个离散性很差的列，不一定会用到索引，若列的离散性很差，走索引的效率低mysql就会进行优化 不用索引.

  查询会不会用到索引就是，mysql进行索引列的数据采样，如果离散性大于15%才会走索引

最左匹配原则：

最左匹配原则不仅仅是联合索引中才有,如：name 索引

取决于创建数据库时采用什么样的排序规则，

联合索引

索引的冗余

       单列索引是联合索引的一个特殊。Name 索引他一定是 从做到右 一个字母一个字母比对的，只是name和phone联合索引的话他会有一个列的段，他会先比较一个列的段的概念. 所以name索引一定是冗余的。

覆盖索引

如：索引有name和phone  :  查询语句 select name phone from users where name =?

若此时查询的列覆盖到了索引的B+树中，就立马返回， 不再做数据库的IO 操作了，减少数据库的IO ,

随机IO变为顺序IO的体现

1. 索引运用B+树扫描叶子节点的过程，叶子节点是有顺序的

    2.体现在覆盖索引上

注：尽量不要再索引列上允许某个字段为空，空字段会加重索引的计算。需要额外的空间来保存这个空了

索引的创建于使用规则

• 索引列的数据长度能少则少。 
• 索引一定不是越多越好，越全越好，一定是建合适的。
• 匹配列前缀可用到索引 like 9999%，like %9999%、like %9999用不到索引; 
• Where 条件中 not in 和 <>操作无法使用索引;
• 匹配范围值，order by 也可用到索引;
• 多用指定列查询，只返回自己想到的数据列，少用select *; 
• 联合索引中如果不是按照索引最左列开始查找，无法使用索引;
• 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引; 
• 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引;