MySQL索引

1.索引是什么

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。

2.索引的分类

• 普通索引(INDEX)：最基本的索引，没有任何限制
• 唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
• 主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。
• 全文索引(FULLTEXT )：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。
• 组合索引：为了更多的提高 mysql 效率可建立组合索引，遵循”最左前缀“原则。

不管什么类型的索引，索引的本质还是 B+树这种数据结构。另外 mysql 中还有另一种数据结构而索引：hash 索引。
Hash 索引：采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似 B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
B+树的索引：B+索引可分为聚集索引和辅助索引

3.mysql聚簇索引和非聚簇索引的区别

都是B+树的结构

3.1 聚集索引

• 将数据存储和索引放在了一块，并且是按照一定顺序排列的，找到了索引也就找到了数据。

优势：

• 因为数据存储和索引放在了一块，因此，查询聚簇索引可以直接获取数据，相比较非聚簇索引需要二次查询，效率要高
• 因为聚簇索引是按照一定的顺序排列的，因此，它更适合用于范围内的查找。
• 因为聚簇索引是按照一定的顺序排列的，因此，它更适合用于排序的场合。

3.2 非聚簇索引

• 非聚簇索引的叶子节点不存放数据，存储的是数据行地址，也就是说根据索引找到该数据的行地址，再去根据行地址去磁盘里查数据。

mysql 中的两种存储引擎 innodb 和 myisam 都采用的是 B+树这种数据结构作为索引。他们主要的区别在于：
InnoDB（索引组织表）使用的聚簇索引、索引就是数据，顺序存储，因此能缓存索引，也能缓存数据
MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引

4.mysql 为什么使用 B+索引

4.1 B+树索引的本质

B+树索引的本质就是B+树在数据库中的实现，B+索引在数据库中有一个特点：高扇出性，也就是说在数据库中，B+树的高度一般在2~4层，也就是说查找某一键值的行记录时，最多只需要2-4次I/O

4.2 B 树相对于红黑树的区别

在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘 IO 读写过于频繁，进而导致效率低下的情况。
为什么会出现这样的情况？
树的深度过大会造成磁盘 IO 频繁读写。所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B 树可以有多个子女，从几十到上千，可以降低树的高度。

4.3 B 树和 B+树的区别

1.B 树所有叶子结点都出现在同一层，它的关键字集合分布在整棵树中，也就是说叶子节点和非叶子节点都存放有数据。
2.B+树所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的分支结点可以看成是索引部分，结点中仅含有其子树中的最大（或最小）关键字。

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

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

5.建立索引时机

5.1 哪些情况需要创建索引

1).主键自动建立唯一索引
2).频繁作为查询条件的字段应该创建索引
3).查询中与其它表关联的字段，外键关系建立索引
4).查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
5).查询中统计或者分组字段
6).单键/组合索引的选择问题(在高并发下倾向创建组合索引)

5.2 哪些情况不要创建索引

表：记录太少、经常增删
表字段：经常更新、数据重复且分布均匀、where条件中用不到

1).表记录太少
2).经常增删改的表(因为不仅要保存数据，还要保存一下索引文件)
3).数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
4).频繁更新的字段不适合创建索引
5).where 条件里用不到的字段不创建索引
注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

5.3 索引失效

复合索引不使用前列；like以通配符开头；
查询条件中有or、字符串类型要将数据用引号引起来
1.对于复合索引，如果不使用前列，后续列也将无法使用，类电话簿。因为他们要遵守，最左前缀法则，即的是查询从索引的最左前列开始并且不跳过索引中的列。
2.like 以通配符开头(‘%abc…’)mysql 索引失效会变成全表扫描的操作。
3.如果条件中有or，即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)，例子中user_id无索引 。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
4.存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引