MySQL索引

一、索引类型

MySQL索引是一种特殊的数据结构，InnoDB的索引是表空间的一部分。索引保存着涉及所有记录的引用指针。按索引字段特性角度分类：

1、普通索引（index）

2、唯一索引

    主键索引（primary key）

    唯一索引（unique）

3、联合索引

    联合主键索引：primary key（id, name, age）

    联合唯一索引：unique(id, name)

    联合索引：index（id，name，age）

三、B+Tree索引和Hash索引

按索引数据类型分类，可以分为B+Tree索引、Hash索引、full-texts索引。

 * Innodb从5.6开始支持全文索引。

1. B+Tree索引

B+Tree由二叉查找树、平衡二叉树、B树演变而来。

★二叉查找树

如图所示，二叉查找树的节点存的是键值（key，索引字段的实际值）和数据（data，数据行）。

二叉树的特点：左子树的值<当前节点的值<右子树的值。顶端节点为根节点，没有子节点的节点为叶子节点。

如果要查id（索引字段）为12的数据，步骤如下：

1. 根节点id与12比较，10<12，选择右子树为当前节点；

2. 13>12,选择左子树为当前节点；

3. 12=12，取出索引中的data。

时间复杂度：

第1次折半：还剩n/2个元素

第2次折半：还剩n/4个元素

第3次折半：还剩n/8个元素

……

第k次折半：还剩n/2^k个元素

最坏的情况下，查到最后还剩1个元素才查出来，即n/2^k = 1。得k=logn。所以二叉树的时间复杂度为O(logn)。

★平衡二叉树

二叉树是可能退化成链表的，如下图。

如果要查id为17的用户，需要遍历所有节点，等效于全表扫描。

造成二叉树退化的原因是，结构不平衡，二叉树高度不稳定，从而导致查找效率不稳定。为了保证二叉树保持平衡，就要用平衡二叉树。

平衡二叉树又称AVL树，要求左右节点高度差不能超过1。

当插入或者删除导致平衡二叉树不平衡的时候，它会调整结构来保持平衡。调整的方式为旋转节点位置。

平衡二叉树相比二叉树，查询效率更稳定，速度更快。但是保持平衡的旋转调整会消耗很多资源。平衡二叉树可以优化为红黑树。

★B树（Balance Tree）

MySql将数据和索引都存在磁盘中，相较于内存，从磁盘读取数据会慢成千上万倍。所以应当尽量减少磁盘的读取次数，另外，从磁盘读取数据时都是按照磁盘块来读取，并不是一条一条读取。

如果把尽可能多的数据放在一个磁盘块中，那一次IO就能读到更多的数据，查找数据消耗的时间也会大幅度降低。

如果用树这种数据结构存索引，那每查找一次数据就需要从磁盘读取一个节点，也就是一个磁盘块。但是二叉树一个节点只能存一个key和一条data，所以一个磁盘块只能存一条数据...

为了避免平衡二叉树一个节点存一条数据的弊端，需要寻找一种一个节点存储多个键值的数据结构。

BTree是一种多路自平衡搜索树，类似于二叉树，却可以拥有更多的子节点。

图中的节点为“页”，也就是磁盘块，MySql中读取数据的基本单位都是页。p节点为指向子节点的指针，二叉查找树和平衡二叉树也有，只是被省略了。

B树相较于二叉树，每个节点存储了更多的键值（key）和数据（data），并且拥有更多（>2）个子节点，子节点的个数称为“阶”，上图中B树为三阶B树，更多的子节点意味着树的高度会更低。

假如我们要查id=28的数据，步骤如下：

1. 查根节点页1，判断28在17和35之间，就根据p2指针找到页3；

2.将28和页3中的key比较，26<28<30，根据p2指针找到页8；

3. 将28与页8的key比较，发现有匹配的的key，找到对应的信息（28，bv）。

特点：

（1）键值分布在整个树的所有节点；

（2）任何关键字（索引key）出现且仅出现在一个节点中；

（3）搜索可能在非叶子节点结束；

（4）在关键字全集内做一次查找，性能逼近二分查找算法。

★B+树

B+树就是InnoDB索引的真正实现方式，准确说是聚集（聚簇）索引。

B+Tree为BTree的变体，也是一种多路平衡查找树。与BTree的区别：

（1）所有Key存储在叶子节点，非叶子节点不存储真正的数据；B树节点的数据页中不但存键值，也存数据。

之所以这么做，是因为数据库中页的大小是固定的，InnoDB中页的默认大小是16kb。如果不存数据只存key，就能存更多的key，树的阶数（子节点个数）就会更大，树就更矮更胖，这样查找数据的磁盘IO就会再次减少，效率就更高。

另外，B+树的阶数是等于键值的数量的，如果一个B+树节点可以存储1000个键值，那么三层B+树就可以存1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以我们查找10亿数据只需要两次IO。

（2）因为B+树所有数据都存在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找、排序查找、分组、去重查找异常简单。而B树数据散落在各个节点，实现很不容易。

（3）为所有叶子节点增加了一个链指针。所以基于该索引的顺序扫描时，可以利用双向指针快速移动，效率非常高。

注：B+树中的各个数据页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

我们通过数据页之间的双向链表，以及叶子节点中数据之间通过单向链表连接的方式可以找到表中的所有数据。

★B+Tree索引以B+树为存储结构实现，但是MySQL文档中将其写为BTree索引。

★B+Tree索引在MyISAM和InnoDB中有很大区别。MyISAM中，B+树的叶子节点并不存储数据，而是存储数据的文件地址。

2. 哈希索引

哈希索引就是采用Hash算法，把键值换算成新的哈希值，检索时不需要类似B+Tree那样从根节点逐级查找，只需一次Hash即可立刻定位到相应的位置。

MySQL只有Heap和Memory引擎才支持哈希索引（NDB也支持，但是不常用），InnoDB的自适应哈希索引（Adaptive Hash Idex）不在此列，因为这不是创建索引时可以指定的。

Hash索引与BTree索引的区别：

1.等值查询与范围查询

如果是等值查询，Hash索引有绝对优势，因为通过一次Hash就可以立马找到值（如果键值不唯一，则需先找到键所在位置，然后根据链表往后扫描，知道找到相应数据）；

如果是范围查询（like，>, between），Hash索引无效（原有连续键值通过Hash算法计算后就不连续了，同理，Hash索引也不能利用索引排序）。

2. 哈希索引不支持联合索引的最左匹配规则；

3. 效率

B+Tree检索效率稳定，哈希索引波动巨大。因为哈希索引受重复键值的数量影响巨大，重复键值越多，效率越低，因为存在哈希碰撞问题。

二、聚集索引和非聚集索引

MySQL索引根据存储方式不同，B+树索引分聚集索引和非聚集索引（也叫聚簇索引）。

1. 聚集索引

聚集索引决定在物理磁盘上的物理排序，一个表肯定有，也只能有一个聚集索引。因为，InnoDB是把数据存在B+树里的，B+树的键，即聚集索引的键，有主键就是主键，没有主键按下面的规则定义：

如果定义了主键，InnoDB会通过主键来聚集数据；如果没有主键，InnoDB会选择一个唯一的非空索引代替；如果都没有，InnoDB会隐式地定义一个主键作为聚集索引。

聚集索引可以很大提高访问速度，因为它将索引和行数据保存在同一个B-Tree中，所以找到索引也就找到了相应的数据。但在使用聚集索引时应避免随机的聚集索引（一般指主键不连续，且分布不均匀），如使用uuid作为聚集索引，性能会很差，因为uuid值的不连续会导致增加很多的索引碎片和随机I/O，最终导致性能急剧下降。

2. 非聚集索引

与聚集索引不同点：

（1）非聚集索引不决定数据在磁盘上的物理排序

（2）并且非聚集索引中只保存索引，不保存数据行，只保存数据行的指针（主键）。如果走非聚集索引查询，先在索引中找到数据行的主键，在根据主键去聚集索引中查找数据行。这个过程称为回表。

三、根据聚集索引、非聚集索引查找数据

根据聚集索引查询

select * from user where id>=18 and id <40，查询过程如下：

1. 一般根节点是常驻内存的，也就说页1已近在内存了，不需要产生磁盘IO。先在内存中取到页1，要查到18到40范围的值，得先找到id=18的键值。找到键值18后，根据指针p2，定位到页3。

2. 根据指针找到页3，从磁盘读取出页3，放入内存。查找找到键值18，然后再拿到页3中的指针p1，定位到页8；

3. 页8也不在内存中，得从磁盘读出页8的数据到内存。

因为页中的数据是链表连接的，而且键值是顺序存放的，此时可以根据二分查找法定位到键值18。

此时已经到叶子节点（数据页）了，我们已经可以拿到一些满足条件的数据了，即键值18对应是数据。

因为是范围查找，且所有的数据都在叶子节点，并且是有序排列的，那么我们就可以对页8的键值依次遍历查询并匹配满足条件的数据。我们找到键值为22的数据，此时页8没有数据了，但还没有查完，我们就要拿着页8的叶子节点指针，水平去找页9.

4. 把页9加载到内存，通过和页8一样的方法遍历数据，知道发现41大于40，此时不满足条件，查询终止。

满足条件的数据

(18,kl),(19,kl),(22,hj),(24,io),(25,vg),(29,jk),(31,jk),(33,rt),(34,ty),(35,yu),(37,rt),(39,rt)。

流程如下图所示：·

根据非聚集索引查询

非聚集索引的叶子节点不存数据，存的是key和主键。

图中叶子节点上的x-y，比如5-3，5是索引的键值（key），3是主键值。

select * from user where luckNum=33，查找流程和聚集索引一样，最终会找到主键值47，找到主键后需要在到聚集索引里找具体对应的数据行，就又回到聚集索引的查找流程。

四、B-Tree索引规则

1. 全值匹配

指查询条件与索引列全部匹配。如where name=XX and age=XX and sex=XX匹配（name,age,sex）的索引。

* 这里查询条件的顺序不会影响索引检索，因为MySQL查询优化器会对查询条件进行优化。

2. 匹配列前缀

指匹配列值的开头部分。如 select * from user where name like 'li%';  可以走name的索引，但是如果%在值前面就不走索引了。

3. 匹配最左前缀

指优先最左侧的索引列，直到出现范围搜索（<,>,between..and..），范围搜索不能命中索引。如（name，sex，age）的联合索引可以满足如下的查询：

name=XX；

name=XX and sex=XX；

name=XX and sex=XX and age=XX.

如果查询条件为name=XX and age=XX，则只走name的索引。

* 与查询条件顺序无关。

五、高效索引的策略及索引建立的注意事项

1. 模糊查询条件以%开头不能使用索引（ where name like '%_li'）

2. 范围查询不能使用索引。如索引为（name，age，sex），where name=XX and age>10 and sex=XX只能命中name的索引。

3. 索引列不能是表达式的一部分，也不能是函数参数，否则无法使用索引。如

        select * from user where name=concat( real_name, '_li');

4. 选择合适的索引列顺序

        建联合索引时，应当以业务需要为主，如果业务需要不能区分主次，则将选择性高的列放前面。选择性比例计算方式如下：

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name;  -- index_column代表要添加前缀索引的列

5. 索引列尽量避免null值，因为空值索引列会使索引、索引的统计信息以及比较运算更为复杂。所以建表时应当指定索引列not null，设默认值。

6. 根据实际情况选择前缀索引

当需要建立索引的列很长时，会增加索引的存储空间，降低索引效率。一种策略是使用hash索引，一种是使用前缀索引，选择列的前n个字符作为索引，可以大大节省索引空间，提高效率。

ALTER TABLE table_name ADD INDEX index_name (index_column(length));

创建前缀索引，需要足够的长度保证高选择性，否则建索引意义不大；同时不能太长。可以通过如下方式选择索引长度：

（1）

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name;  -- index_column代表要添加前缀索引的列

我们通常使用该方法来计算索引的选择性比值，比值越高索引的效率越高。

（2）

SELECT

COUNT(DISTINCT LEFT(index_column,1))/COUNT(*),

COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),

COUNT(DISTINCT LEFT(index_column,3))/COUNT(*)

...

FROM table_name;

与（1）同理，测试结果越接近（1）的结果越好，最终结合实际情况选择索引长度。

★ MySQL无法使用前缀索引做order by和group by。

六、导致InnoDB索引失效的情况

1. 不满足复合索引最左前缀原则；

2. 在索引列上做计算、函数；

3. 查询条件为“不等于”索引列时，如“!=”，“<>”等；

4. like查询以%开头；

5. 查询条件有or；

★ is null 和 is not null不会使索引失效！！

？有人说字符串列查询不加引号会导致索引失效，我试了不会，不知道是不是被Navicat给优化了。

？有人说如果全表扫描如果比走索引快，索引会失效。只知道oracle有执行计划优化器，mysql需要后续研究下。