Mysql索引入门 —— 一篇标题普通的文章

最近公司培训Mysql索引相关知识，遇到许多疑问。然后毕竟我是VIP，所以看了诸葛老师Mysql索引优化，我觉得我又行了。首先写不写完先感谢诸葛老师，一下图片都是盗得诸葛老师的图片。

基础知识篇章

索引

Mysql的索引其实就是帮Mysql更加高效且便捷的获取数据。大学老师是这样讲的，如果把Mysql存储的数据比作一本字典中的字，那么索引就是字典前几页的根据部首拼音快速查询汉字的部分。

索引的数据结构

Hash
对索引的key进行hash计算，然后定位出数据存储的位置。

当你们看到这个图，是不是觉得像HashMap。没错确实差不多，都是hash表数据结构。

利用hash数据结构建立索引非常少，最起码我们公司是真没有。hash虽然查询效率很高，但是局限性太大。利用hash数据结构建列的索引，只能支持 = 或 IN，而不支持范围查询。

举个例子：就按照上图我们按照名字建立一个hash索引。
如果搜索 where name = ‘Alice’，能直接确定位置2，然后取出Alice的数据。
如果搜索 where name > ‘Alice’ ,hash是无法计算出对应的位置取出数据筛选，只能进行全盘扫描。

B-Tree
B-tree 称为 多路平衡查找树。之前树都是一个节点放一个值，然后分叉多个子节点。这个B树不一般了，它一个节点放多个值，这样一个节点可以存放多个值。有什么用呢？
很显然，当数据量相同时候B-Tree数据结构能够利用更少的层数能存储全部数据。层数越少那么我们需要进行磁盘IO次数就能越少。

聪明的你有没有这样的想法：节点存储越多值就能更多减少磁盘IO次数。直接全存进去一个节点行不行？
对于这样的想法，前半段完全正确。所以有了B+Tree，但是后半段中由于计算机内部存储的结构，无法实现的（就记住一个节点大小是有限的，存储不了所有数据）。

B+Tree
B-Tree的进化版本，既然节点存储越多值就能更多减少磁盘IO次数，那么我们就朝着这个方向优化，进化后的B+Tree只有叶子结点才会存储data。那么非叶子结点就能存储更多的值。不仅如此，B+Tree还在叶子节点加上一个双向指针。

一般数据库都利用B+Tree，每个节点只存储对应索引列的值，能存储许多值，所以百万级别的数据量级别B+Tree只需要三层就能存储完成。

Mysql的引擎

ps：其实两个引擎区别还有很多，这里不过多叙述，可以自行了解。

MyISAM
一个基本已经淘汰了的引擎，经常面试拿出来和InnoDB做比较。有很多不同，就不叙述了。说一点就是 MyISAM存储索引树和数据是分开存储的。也就是 一个文件是索引(.myi 文件存储表索引) ，另一个文件是数据 (.myd 文件存储表索引)。
而他们是如何关联的，如何通过索引找到数据的呢？就是索引树的叶子结点保存着数据的物理地址。

上面索引树存储在一个文件中，而叶子结点存储的并不是行数据，是行数据的物理地址。这种形式通过索引查询到具体行的叶子结点时候，Mysql还需要拿着这个地址再进行一次磁盘的IO查询出数据。那如果查询出来的数据量非常多，那么Mysql需要再次进行多次IO查询查询出来所有的行数据。

InnoDB
如果用Mysql数据库，应该都是用它。它对于索引的存储是 索引和数据存放在一起，就是一个文件。怎么存储呢？来吧上图。

你不会没有疑问吧？一个表一个主键索引这样建没问题，那么有非主键索引呢？而且还有多个非主键索引树呢？继续上图

当我们建立非主键索引时候，我们的根节点没有存储是数据，而是存储的是主键。也就是说我们利用非主键索引查询时候，其实是查询出来对应的主键然后再回表进行主键索引查询才能查询出来结果。

经典的面试题

这有两个面试题，能让我们更好的理解上述所学知识。

• 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

  选择整型：节点存储越多值就能更多减少磁盘IO次数，整型占用的磁盘空间小啊，妙啊。
  自增原因：B+Tree索引是需要将插入的主键进行排序维护的，如果不是自增插入，维护成本会很高。

  如果自增的插入那么维护B+Tree树时间效率会很低，具体可以去详细了解一下B+Tree数据结构。

• 为什么非主键索引结构叶子节点存储的是主键值？
  
  还是这张图，所以为什么选择叶子结点存储主键，而不是数据，还要多一步回表操作呢？
  存储空间：如果每个索引树的叶子结点都保存数据，那有多个索引就会相当于copy重复几份数据。
  一致性：当我们更新一个数据，要同时维护多个索引树的数据，使其保持数据保持一致。

联合索引

由多个列（两个或者两个列以上）建立的索引称为联合索引（也叫复合索引）。提到联合索引肯定都要知道 最左前缀原则，那为什么会出现最左前缀原则的问题呢？以及Mysql联合索引都有哪些失效的情况呢？
再来个图：

首先我们先了解一下联合索引是怎么进行构建索引树的。
如上图利用name，age，position三个字段建立联合索引树，Mysql首先按照 name进行排序，如果name重复那么就会利用age进行排序，同理如果age重复才会利用position进行排序。（好好理解这段话，这也是最左前缀原则产生的原因）

最左前缀原理

先了解一下最左前缀，就拿我们上图来说，我们查询条件必须有联合索引建立时候 最左侧的字段 ‘name = xxx’ 的条件才能走该索引，如果没有该字段使用 ‘age = xxx’ ，'position = xxx’都不会使用到联合索引。

例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .

原因
我们如果直接使用age进行查询，联合索引中 单单看age是无序的。
因为上面也提到了先按照name进行排序，name相同才会利用age排序。试想一下 如果直接查询age = 28，那么我们如何去筛选到出结果，是不是必须全盘扫描不走索引。
而当我们 输入条件 name = Bill ，age = 30 的时候，我们通过name走索引查询到有三个，然后按照顺序筛选age=30的，最后查询出数据集。

索引失效

看这样一张图，我们慢慢分析关于索引失效的问题。

前四个都应该没什么问题，最左前缀原则最基本的。

• 第五个：where a = 3 and c = 5
  a = 3 会走联合索引的a字段，但是c字段却不会走联合索引。中间b不存在导致根据a筛选完成之后，c并不是顺序的所以c无法使用索引查询。
• 第六个：where a = 3 and b > 4 and c = 5
  如果使用范围查询，那么范围查询的那个字段可以走索引，但是之后的字段无法走索引。
  a = 3 可以走索引的a字段，筛选出有序的b字段。然后b>4也可走索引的b字段，因为通 过a = 3 筛选之后b是有序的。但是当b>4筛选之后，再使用c = 5 字段是无法走索引的c字段的。因为b是范围查询，查询出来c是无序的，所以是无法使用索引。
• 下方：like kk% 相当于 = 常量，%kk 和%kk%相当于范围

那就先这样吧，下篇讲解索引优化相关知识吧。