最近在重构自己工作上写的代码，重构完后也开始考虑要做一些数据库优化。其中一项工作就是重新审视之前设置的索引是否合理。于是这周都在反复看《极客时间》上面的Mysql实战的专栏，先恶补下索引方面的基础知识，本文在此做一些记录。
0、背景
本文以Mysql中的InnoDB的使用为例
1、索引解决的问题
索引，可以理解为一本书的目录，它要解决的是提高查询效率的问题。当我们要查找一本书的某个内容时，如果没有目录，就得一页一页去找，效率非常低。但当我们有一个目录时，直接根据要查找的关键词，就可以在目录上搜寻主题，从而更快地搜寻到关心的内容
2、索引的数据结构
既然是为了方便查找数据，那么我们就会根据数据的特性，找到出能提高其读写效率的数据模型，这里我们简单说三种：
- 有序数组
该模型的特点是 静态、顺序 存储，不适合需要经常插入的场景 - 哈希表
适合等值查询的场景，经常见的就是key-value；由于插入的数据都是无序的，哈希表在进行范围查询或有序查询时，就不太适用； - 二叉树
出现的原因主要是让查询尽可能少的访问磁盘；其特点是leftChild < parent< rightChild，所以树也是一个有序的结构；
3、索引的类型
索引类型分两种，根据其索引树叶子节点的内容，区分成两类，一个是普通索引，一个是主键索引；普通索引又称为二级索引，为啥称为二级索引呢？大家可以看下图
dcda101051f28502bd5c4402b292e38d.png
图片来自极客时间，侵删
例如我对表A建立了一个普通索引k，Mysql中便会建立两颗索引树，一个是主键索引树，一个是普通索引树，如上图。
那么大家可能会问，基于主键索引 和 普通索引的查询有什么区别？
我们用两条Mysql语句进行说明。
如果是基于主键索引，select * from T where id = 500, 它只用搜索主键索引树；
如果是基于普通索引，select * from T where k = 3，它的搜索过程如下：
- 先到K索引树上找到k = 3的值
- 找到后，取出叶子节点的值，得知主键为300，则再去主索引树搜寻ID = 300的节点，再取出数据；
从此可以得出，普通索引要比主键索引的查询多查一次索引树，这个过程我们称为 回表，我们一般都建议，用主键查询，就是为了减少回表的过程；
4、建表时最好用自增Id主键，为什么？
这个问题，主要是从性能和存储的方面来说的。首先我们先看下索引的维护过程
4.1 索引维护过程
我们知道，当插入新数据时，索引也是要相应维护，如果我们新插一条数据，而且是用自增ID的话，基本上数据是在最新ID的后面，整颗树还是保持有序的，不需要变动，但如果现插入一条数据，例如在id = 500前插入一个id = 400的值，这会就需要调整树结构，把值插进去。。就很有可能会出现数据页满了的情况，导致进行分页，这样Mysql就会把一部分数据挪过去，导致性能受到影响。同样的，如果我们删除了一些数据，数据库也会做合并。
4.2 场景
比如现在有一个用户表，用户表中有一个唯一标志，身份证。
我们假设用身份证做主键，会出现两个问题：
- 一个主键就会占用至少二十个字节，后边新建普通索引的时候，该普通索引上的每一个叶子节点，至少有二十个字节，整体算下来会占较大空间。
- 用身份证做主键并不能保证有序插入，插入时可能产生数据的挪动，从而降低查询性能
当然也有些场景是适合用业务字段作为主键的，什么情况呢？
当该字段是表中的唯一索引 ，典型场景就是 KV场景
既然表中没有其他索引，也不用考虑叶子节点的大小了，直接将其设成主键，这样也不用回表查询
5、索引的其他概念
在使用索引的时候，我们还经常听到这样一些概念,例如 覆盖索引，前缀索引，索引下推，下面我们再分别介绍一些这些概念是什么
5.1 覆盖索引
覆盖索引的定义就是 定义的索引已经覆盖了查询需求，该索引查询出的结果已经满足需求
还是第三节的这个图，k是普通索引，ID是主键，我们看看下列sql语句
select ID from T where k between a and b
这里，通过这句sql得出的查询结果已经能满足查询到ID的需求了，不需要再回表（回到主索引树）查询一次，索引k 就 被称为 覆盖索引
由上得知，覆盖索引主要是为了减少索引树的搜索次数；如果有些字段查询非常高频，可以考虑建立一些索引，覆盖其查询需求；
5.2 前缀索引
前缀索引在百度上的定义多为 用联合索引的最左N个字段或者字符串的最左M个字符进行查询的索引。在这里大家可能会一直冒问号了，为什么是最左？为什么最左N个字段就可以加速查询？
首先，要知道联合索引的存储结构，索引项按照索引定义中出现的字段项进行排序，比如我现在给用户表里面建立了(name，age）的联合索引，建立后的数据存储结构可看下图：
22222dcd6370.jpg
图片来源极客时间，侵删
可以看到，索引项是按照我们的定义（姓名，年龄）排好序的，当我们去查询‘张三’的时候，他会迅速定位到ID4，然后向后遍历；
当然，我们也可以查姓张的人，这样我们就直接定位到了ID3，于是这里我们可以看到，不只是索引的全部定义，只要用到索引的最左N个字段/前缀，就可以加速查询
5.3 索引下推
索引下推，其实在索引遍历过程中，对索引包含的字段做判断，过滤不符合条件的记录，减少回表次数。
比较好玩的是，Mysql在5.6版本才开始支持该功能，下推的过程可以看下图
333333333333c4238c7b65acfe1b.jpg
图片源自极客时间，侵删
六、建立索引的原则
根据上面的概念介绍，我们其实可以总结出，建立索引时要遵循的一些原则：
- 最左前缀（最高频的放到最左边）
- 索引的复用能力（若调整字段顺序，能够少一个索引，则优先使用该顺序）
- 考虑空间和性能
网友评论