索引

在数据之外,数据库系统还维护着特定结构,这些数据结构以某种方式引用指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构,就是索引.
索引在MySQL中也叫做键,是存储引擎用于快速找到记录的一种数据结构，尤其是当表中的数据量越来越大时,索引对性能的影响越来越大。索引优化是对查询性能优化最有效的手段了,索引能够轻易将查询性能提高好几个数量级.

1.索引基础

在MySQL 中，先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。索引可以包含一个或则多列的值，如果包含多个列，则列的顺序也很重要，Mysql 只能高效的使用索引的最左前缀列。索引有很多类型,可以在不同的场景提供更好的性能,在MySQL中,索引是存储引擎层而不是在服务层实现的,不同存储引擎的索引的工作方式也不一样,也不是所有的存储引擎都支持所有类型的索引。
①从数据存储结构角度：B Tree索引（B-Tree或B+Tree索引）、hash索引（memory引擎支持）、R-Tree索引（空间数据索引，MyISAM支持，用作地理数据存储）、FULLTEXT（全文）索引（MyISAM、InnoDB支持）

②从物理存储角度：聚簇索引(也称聚集索引)、非聚簇索引（也称非聚集索引）
聚簇索引：在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同，一个表只能包含一个聚集索引，比如SQL SERVER、MySQL默认是在主键上建立聚集索引的。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。

非聚簇索引：（也叫二级索引）二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据。

二级索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的，排序很快。非聚簇索引则没有按序存放，需要额外消耗资源来排序。

③从应用层次来分：普通索引、唯一索引、主键索引、空间索引（只有MyISAM支持且支持的不好）
唯一索引 唯一索引是不允许其中任何两行具有相同索引值的索引。比如学号，身份证号
当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

主键索引数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

2.聚簇索引与非聚簇索引

innodb索引分类

• 聚簇索引(clustered index)
  1) 有主键时，根据主键创建聚簇索引
  2) 没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引
  3) 如果以上两个都不满足那innodb自己创建一个虚拟的聚集索引
• 辅助索引(secondary index)
  非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引

MySQL InnoDB一定会建立聚簇索引，把实际数据行和相关的键值保存在一块，这也决定了一个表只能有一个聚簇索引，即MySQL不会一次把数据行保存在二个地方。

1. InnoDB通常根据主键值(primary key)进行聚簇
2. 如果没有创建主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引
3. 上面二个条件都不满足，InnoDB会自己创建一个虚拟的聚集索引

非聚簇索引更直白的说，叶子节点只是存着数据的地址，而不是数据本身。索引文件和数据文件两个文件是分开的，如MyISAM就是采用非聚簇索引。

上图是非聚簇索引的一级索引，下图是非聚簇索引的二级索引，叶子节点中存放数据地址，没有太大的区别。

---

聚簇索引的叶子节点中保存着数据，InnoDB就是用聚簇索引的。
其主索引如下：

而在聚簇索引的二级索引中，叶子节点保存着主键，所以在二级索引中查找时，先找到二级索引中的主键，在根据这个主键，从聚簇索引中找到该主键的数据，所以需要两次查找

3. 联合索引与覆盖索引

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

mysql创建复合索引的规则是首先会对复合索引的最左边的字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段进行排序。其实就相当于实现了类似 order by field1, field2这样一种排序规则。

联合索引实现：每个节点含有多个关键字，排序时按照多个关键字的顺序进行排序。而这个顺序就是你创建索引时候的顺序。

如果你经常要用到多个字段的多条件查询，可以考虑建立联合索引，建立了一个联合索引就相当于建立了多个索引.,比如联合索引sql会先过滤出last_name符合条件的记录，在其基础上再过滤first_name符合条件的记录。那如果我们分别在last_name和first_name上创建两个列索引，mysql的处理方式就不一样了，它会选择一个最严格的索引来进行检索，可以理解为检索能力最强的那个索引来检索，另外一个利用不上了，这样效果就不如多列索引了。

覆盖索引一定是这个联合索引覆盖了你所有select中需要的数据！就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

4. Ｂtree 索引 与 Hash 索引

mysql默认存储引擎innodb只显式支持B 树索引，对于频繁访问的表，innodb会透明建立自适应hash索引， 即在B树索引基础上建立hash索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。

B Tree索引能够加快访问数据的速度,因为存储引擎不需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索,根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际定义了子节点页中值的上限和下限,叶子节点比较特殊,他们的指针指向的是被索引的数据,而不是其他的节点页。

• B tree 索引相关特性

1. 树中每个节点至多包含m棵子树
2. 若根节点不是叶子节点，则至少包含两颗子树
3. 除根以外的所有非终点节点至少有 (m/2)向上取整棵子树
4. 支持范围查询，前缀匹配查询，等值查询，可以避免排序，例如order by index相关的列，排序会非常快，因为该列本身就是有序存储的
5. 查找时间复杂度 log m N(m为底,N的对数，N为总记录数)

create table Student(
    last_name varchar(50) not null, 
    first_name varchar(50) not null, 
    birthday date not null, 
    gender int(2) not null, 
    key(last_name, first_name, birthday)
);

以上述例子作为分析

- 全值匹配
和索引中所有的列进行匹配，例如查找姓名为Cuba Allen，生日为1960-01-01的人。
- 匹配最左前缀
查找姓为Allen的人，即只用索引的第一列。
- 匹配列前缀
匹配某一列的值的开头部分，例如查找所有以J开头的姓的人。
- 匹配范围值
查找姓在Allen和Barrymore之间的人。
- 精确匹配某一列并范围匹配另外一列
查找姓为Allen，名字是字母K开头的人。即第一列last_name全匹配，第二列first_name范围匹配。
- 只访问索引的查询
查询只需要访问索引，无需访问数据行。这种索引叫做覆盖索引。

• B Tree 索引的限制

如果不是按照索引的最左列开始查找,则无法使用索引
不能跳过索引中的列,也就是姓 ,名,生日的三行索引,这种索引不能找姓为XX 生日为XX的人,因为没有指定名.
如果查询中有某个列的范围查询，则右边所有的列都无法使用索引优化查找 例如查询WHERE last_name=’Smith’ AND first_name LIKE ‘J%’ AND birthday=‘1996-05-19’，这个查询只能使用索引的前两列。

• 哈希索引的相关特性

哈希索引是基于哈希表实现的，只有精准匹配索引所有列的查询才有效，对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，不同键值行计算出来的哈希码也不一样，哈希索引将所有哈希码存储在索引中，同时在哈希表中保存只想每个数据行的指针。

• 哈希索引的限制

 - 哈希索引只包含哈希值和行指针，而不存储字段值
 - 哈希索引不是按照索引值顺序来存储，所以无法进行排序**
 - 哈希索引也不支持部分索引列匹配查找
 - 哈希索引只支持等值比较查询

性别字段为什么不适合加索引？从 B+树原理解释。
尽量选择区分度高的字段作为索引,区分度的公式是 count(distinct col)/count(*)，表示 字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、 性别字段可能在大数据面前区分度就是 0。在性别字段上增加索引，并不能明显加快检索速度。
普通索引索引值可以不唯一

5. 数据库索引失效的几种情况

1.对于组合索引， 不是使用的第一部分， 则不会使用索引 。
2.or 语句前后没有同时使用索引。 要想使用 or， 又想让索引生效， 只能将 or 条件中的每个列都加上索引。
3.如果列类型是字符串， 那一定要在条件中使用引号引起来， 否则不会使用索引 。
4.如果 mysql 估计使用全表描述比使用索引快， 则不使用索引。
5.在索引列上做运算或者使用函数。
6.以“%”开头的 LIKE 查询， 模糊匹配。