54、数据库索引

索引的优缺点

  优点：
  1、大大加快数据的检索速度；
  2、创建唯一性索引，保证数据库表中每一行数据的唯一性；
  3、加速表和表之间的连接；
  4、在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
  缺点：
  1、索引需要占物理空间；
  2、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低数据的维护速度。

索引的分类

  1、唯一索引： 是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中不可以重复。

  2、非唯一索引：是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中可以重复，不要求唯一。

  3、主键索引（主索引）： 是唯一索引的特定类型。表中创建主键时自动创建的索引，一个表只能建立一个主索引。

  4、聚集索引（聚簇索引、Innodb）：表中记录的物理顺序与键值的索引顺序相同。 因为真实数据的物理顺序只有一种，所以一个表只能有一个聚集索引。叶子节点（B+树）存储真实的数据行，不再有另外单独的数据页。

  5、非聚集索引（Mylsam）：表中记录的物理顺序与键值的索引顺序不同。这也是非聚集索引与聚集索引的根本区别。叶子节点并非数据节点，而是每一个指向真正数据行的指针。
  聚集索引与非聚集索引的区别：
  1）、聚集索引的优缺点：优点是查询速度快，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理的紧跟其后。缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引顺序的一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效。
  建议使用聚集索引的场合为：A.某列包含了小数目的不同值。B.排序和范围查找。
  2）、聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点。
  3）、非聚集索引添加记录时，不会引起数据顺序的重组。
  看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次 B+树查找，这不是多此一举吗？聚簇索引的优势在哪？
  1)、由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键 Id 来组织数据，获得数据更快。
  2)、辅助索引使用主键作为"指针"，而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时，辅助索引的维护工作，InnoDB 在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置会随着数据库里数据的修改而发生变化，使用聚簇键索引就可以保证不管这个主键 B+ 树的节点如何变化，辅助索引树都不受影响。

  6、组合索引： 基于多个字段而创建的索引就称为组合索引，组合索引的使用要遵从最左前缀。在最左前缀原则中，范围查询会导致组合索引半生效，where子句有or出现还是会遍历全表。

Mysql怎么增加一个索引

  创建索引：create index idx1 on table(col1, col2, col3);
  添加索引：alter table tablename add index indexname(col1, col2);

55、数据库索引的实现

  目前大部分数据库系统及文件系统都采用B-Tree（B树）或其变种B+Tree（B+树）作为索引结构。B+Tree 是数据库系统实现索引的首选数据结构。
  在 MySQL 中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

MyISAM 索引实现（非聚集索引）

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

  图8是一个MyISAM表的主索引（Primary Key）示意，可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示：

  同样也是一颗 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法会首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其data 域的值，然后以 data 域的值为地址，读取相应数据记录。

InnoDB 索引实现（聚集索引）

  MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点data 域保存了完整的数据记录（第一个重大区别）。 这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

  上图是 InnoDB 主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集。
  1、InnoDB 要求表必须有主键（MyISAM 可以没有）， 如果没有显式指定，则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，类型为长整形。
  2、尽量在 InnoDB 上采用自增字段做表的主键。 因为 InnoDB 数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。
  这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
  第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。 换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。
  聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
  引申：为什么不建议使用过长的字段作为主键？因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

总结

  InnoDB使用的是聚簇索引，将主键组织到一棵 B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照 B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对 Name 列进行条件搜索，则需要两个步骤：第一步在辅助索引 B+树中检索 Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中再执行一次 B+树检索操作，最终到达叶子节点即可获取整行数据。
  MyISAM 使用的是非聚簇索引，非聚簇索引的两棵 B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引 B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗 B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。  

56、为什么使用B+树作为索引

B/B+ 树性能分析

  1、n 个节点的平衡二叉树的高度为 H(即 logn)，而 n 个节点的 B/B+树的高度为logt((n+1)/2)+1；
  2、若要作为内存中的查找表，B 树却不一定比平衡二叉树好，尤其当 m 较大时更是如此。因为查找操作 CPU 的时间在 B-树上是 O(mlogtn)=O(lgn(m/lgt))，而 m/lgt>1；所以 m较大时O(mlogtn)比平衡二叉树的操作时间大得多。因此在内存中使用B树必须取较小的m。（通常取最小值 m=3，此时 B-树中每个内部结点可以有 2 或 3 个孩子，这种 3 阶的 B-树称为 2-3 树）。

为什么说 B+tree比 B 树更适合实际应用中操作系统的文件索引和数据索引。

  B+tree 的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对 B 树更小， 如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对 IO 读写次数就降低了。
  由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
  也有人认为数据库索引采用 B+树的主要原因是：B 树在提高了 IO 性能的同时并没有解决元素遍历的效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B树不支持这样的操作（或者说效率太低，需要中序遍历）。