MySQL索引简析

本文有参照其他博客的地方，若有侵权请告知本人删除

索引概述

• 什么是索引？为什么需要
  • 帮助MySQL高效获取数据的排好序的数据结构
  • 没有索引的时候查找数据是会根据表进行每行顺序查找，而数据是放在磁盘上的而且位置不一定连续，也就是需要进行多次的I/O交互【一行一次】，需要消耗大量时间
• 索引数据结构
  • 二叉搜索树
  • 红黑树
  • Hash表
  • B-Tree
  • B+Tree
• 索引分类
  • 聚集(聚簇)索引：索引文件和数据文件在一个文件，索引文件的直接存有数据【叶节点包含了完整的数据记录】（稠密索引）
  • 非聚集(非聚簇)索引：索引文件和数据文件分离，索引文件的存有数据磁盘存储的地址（在存储文件找数据的操作称为回表）【根据不同引擎有不同索引存储结构】（稀疏索引）

索引可选数据结构

二叉搜索树

• 每个节点对应存储key(索引值，即数据库存储的值)-value(磁盘地址),通过这样的形式去更快的查到磁盘地址获取对应的数据行
• 由于二叉树本身的局限性，在某些情况下树会成链表，这时候查询会变成链式复杂度

红黑树

• 是在二叉平衡树上进行优化的二叉搜索树，通过着色和旋转控制树的相邻深度差为2
• 由于树的高度还是随着数据量增加而递增（其实已经很快了，最深log n再大一些，但仍有更优的数据结构），导致仍有大量的I/O交互[访问磁盘]，所以也存在较大的局限性

B-Tree

• 通过扩大每个节点可以存储的元素个数，达到控制高度的作用【相邻的存储空间可以有效的减少I/O交互】，不过还有更优的索引结构

• B树具有以下特点

  • 叶节点具有相同的深度，叶节点的指针为空
  • 所有元素不重复
  • 节点中的数据索引从左到右递增排列
  • 在索引存储中，每个节点都会存储对应的磁盘data，也就是关联外部数据，所以每次更换节点都会进行I/O交互【优化点：每次节点的变动都需要I/O交互，B+Tree就优化这一点，让I/O交互只有一次，内存操作比起I/O交互的耗时可以忽略不计】

• 大致是通过节点数和高度维持去计算每个节点能放下元素个数，称之为度，通过插入排序以及二分搜索去找到每个元素该插入哪里，每个元素前面会有一个指针，用于存储比当前元素都小(大)的元素形成的节点位置，每个节点的最后一个元素的指针在右，则指向关系相反的节点数组，当节点元素等于度时，取中间值作为父元素，将元素数组一分为二，若是已有父元素，则将中间值插入父元素数组中合适的位置，最后效果就是前序遍历是顺序的且每个节点数组也是顺序的。

• 普通结构示例图

• 索引结构示例图

B+Tree

• B树的变种

  • 叶节点有双向指针维护，范围查找的时候效率更好
  • 放入一些冗余索引，B树没有，因为冗余索引了，存储的索引占用空间小，对于B树而言有data，所以空间占有很大，这样相比B+Tree所能需要的树深度就深了很多
  • 综上MySQL选用了B+Tree，更符合数据库的结构特点，效率更高

• 具有以下特点

  • 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
  • 叶子节点包含所有索引字段
  • 叶子节点用指针连接，提高区间访问性能【示例：由于有序性，查找区间的起点找到之后，终点只需要通过双向指针就可以确认，或者找的终点直接找到起点】

• 插入和搜索的过程跟B树相似，但是(比较高版本的MySQL)它除叶节点data之外的索引值都在内存内，也就是最后找到对应叶节点后才需要进行一次I/O操作

• 示例图

Hash

• 对索引的key进行一次Hash计算就可以定位出数据存储的位置
  • 数组加链表实现，以及MySQL自己的散列值计算算法
• 很多时候Hash索引要比B+树索引效率更高
• 缺点：只能满足"=“和"IN” ，不支持范围查询
• hash冲突问题

MySQL对B+Tree的应用

• 每个叶节点默认分配空间为16kb[InnoDB一页是16KB]，其中元素值占8kb，指针占6kb，以bigint类型8bit为例，可以放约1170个索引元素，叶子结点需要存储data，可能大点，估计为16个，除了估计一个三层级的B+树可以存放进2千万的数据，一般1千万都已经进行分库分表，所以这个索引结构支持的容量是够用的

MyISAM存储引擎实现

• MyISAM索引文件和数据文件是分离的（非聚集）

• 在对应的data目录下可以看到每一个库都有frm(表结构)、MYI(索引存储)和MYD(数据存储)三个文件后缀的文件

• 在查询的时候首先会先判断改字段是否有索引（后缀frm的文件获取表结构），若是有则会先MYI索引定位磁盘地址，然后在MYD文件里面查找数据

• 示例：先判断col1字段是否有索引（后缀frm的文件获取表结构），有则在MYI文件中定位地址，然后MYD文件中获取对应数据

InnoDB存储引擎实现(聚集)

• 表数据文件本身就是按B+Tree组织的一个索引文件结构【对应的data目录下，只有两个后缀文件frm和ibd(数据和索引放在一块，也就是不放磁盘存储地址，直接放对应的数据)】，以主键索引为例，示例图如下：

• 聚集索引 - 叶节点包含了完整的数据记录

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

  • 由于InnoDB是以索引建立存储文件，若没有建主键，InnoDB会在表中寻找一个每个数据唯一的列创建索引存储文件(若有唯一索引就选唯一索引那一列)，若是没有找到，InnoDB会自己建立一个影藏的自增列【所以没有建立索引会导致MySQL消耗多余的资源去处理】
  • 若是采用UUID，索引建立的时候需要比较，而字符串相比整形的比较要慢，而且是随机的，而自增整形直接放在最后插入【减少树的分裂次数】，相比较就看出效率差了，且UUID存储空间更大。【在的采用好的磁盘，比如SSD固态硬盘，这时候空间的节省就显得尤为宝贵】

• 为什么非主键索引结构叶节点存储的是主键值？（一致性和节省存储空间示例图如下）

  

  • 保持一致性：当数据库表进行DML(一般指数据操纵语言)操作时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。
  • 节省存储空间：InnoDB数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。

页结构

• InnoDB的逻辑单位
• 一次从内存取出16KB[默认]
• 这里页可以看做树节点
• 每一次数据的插入都会根据主键进行排序，以链式结构存储每行数据
• 页与页之间有由页内存储的专门指针的连接前后
• 每一行的大小都有限制，当超过限制之后会额外找一个空间用指针连接起来
• 当页数多了，会建立一个额外的索引页，将其他页进行目录化，用于快速的二分查找定位
• 用页目录加快链表遍历的速度[空间换时间]
  • 将链表按照一定的规则进行划分组，页目录里会有一个指针指向页目录的开头
  • 页目录还包含每组开头的行的主键
  • 页目录的内容也是有序的【可以用二分查找】
• 大致结构图如下：
  

联合索引的底层

• 联合主键索引的示例图

• 使用B+Tree存储，比较原则是按索引建立的规则从左向右依次比较，得出比较结果
  • 因为联合索引比较是从左至右走，所以在写查询的时候要跟索引字段从左到右设置查询条件限制

使用索引注意事项

单列索引

• 多个独立索引在同一where下，只有第一个有索引的字段的索引会生效

  • 例子：WHERE class_id = 1 AND value > 100【class_id 和 value都有索引，只有第一个字段索引生效】
  • 原因：形成的索引B+Tree中只有e或者f的，而没有e，f一起的，所以选择e生效后f无法得到相关的索引匹配，而且MySQL底层实现的时候会判断直接走全表快还是索引快，若是全表更快就不走索引了，所以当走完第一个索引之后剩下的那个直接查更快就选择了直接查

联合索引

• 联合索引中，要使索引生效必须满足最左前缀原则

  • 根据索引建立之后的，按照顺序挨个进行匹配，若是遇到中断或者范围查找，之后的字段便不会走索引了

  • 例子：(正常使用的时候MySQL会对查询进行优化，在不影响语义的情况下，改变条件顺序是的走尽可能快的索引，有些无法体现)

    -- 组合索引(a,b,c)
    WHERE a = 1 AND b = 2 AND c = 3  -- 使用的索引(a,b,c)
    WHERE a = 1 AND c = 2 AND b = 3  -- 使用的索引(a)
    WHERE c = 1 AND a = 2 AND b = 3  -- 使用的索引(a,b)，但由于mysql自动优化成a = 1 AND b = 2 AND c = 3，所以最终会走abc
    WHERE a = 1 AND b > 2 AND c = 3  -- 使用的索引(a,b)
    

分组、排序与索引

• 分组、排序与索引示例

  • 不走索引的情况下，分组或者排序会在where过滤完之后建立临时表，对临时表进行分组或排序

  • 在走索引的情况下，由于分组和排序的本质还是数据排序，而InnoDB索引树是有序的，也就说符合索引排好的序的时候就不需要建立临时表

  • 例子：

    -- 建立的索引为联合索引 (c1,c2,c3,c4,c5) 
    
    where c1=x and c2=x and c4=x order by c3 -- 这里会走c1,c2索引，由于这里的排序字段在c2之后排序字段，这里就不需要建立临时表，直接在B+Tree中取出c1,c2索引的所有行，再过滤c4条件即可
    where c1=x and c4= x group by c3,c2 -- 这里需要分组的字段与索引字段顺序不符合左前缀，所以走了c1字段索引之后需要建立临时表
    where c1=x and c5=x order by c2,c3 -- 这里需要分组的字段与索引字段顺序符合左前缀，所以走了c1字段索引之后不需要建立临时表，因为过滤出来的c2,c3已是有序
    where c1=x and c2=x and c5=? order by c2,c3 -- 这里需要分组的字段与索引字段顺序符合左前缀，所以走了c1，c2字段索引之后不需要建立临时表，因为过滤出来的c3已是有序，相当于【由于c2是常量】where c1='a' and c2='b' and c5='e' order by c3
    

多表关联

• 主表一定会被全表扫描的，而关联表的若是关联字段有索引，也会走索引（若没有索引，三个全表对应数据量累乘，体量很大）
• 有关联字段要建立索引
• 连接的时候尽可能少用内连接

其他注意

• 当引用索引的列参与计算或者使用函数的时候，索引不会生效
• LIKE关键字，当左前缀为模糊的时候，不走索引，eg: ‘%like%’
• 正则表达式不走索引
• 当字段为字符类型的时候，匹配条件为数字的时候，不走索引。eg: a = 1
• 如果使用or关键字连接的条件也不会走索引
• IS NULL 或者 IS NO NULL 也都不走索引
• 索引不一定是越多越好，它是占用内存的，所以建立合理适当的字段就够了，同时数据插入或者删除都会更新索引，这是消耗资源的
• 在大数据导入时，可以先删除索引，再批量插入数据，最后再添加索引

高性能索引策略

理想索引字段的条件

• 查询频繁
• 区分度高
• 长度小
• 尽可能覆盖常用查询字段

常用技巧

主键索引优化

• 对于InnoDB而言，主键最好使用自增整形，因为自增的特性可以减少索引B+Tree分裂次数(分裂操作比较慢)，且自增主键占用的内存较少
• 若使用无规律的UUID等会增加B+Tree的分裂的可能，且随机主键的占用内存大

索引长度优化

• 引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)
• 合理利用前缀索引【去某字段的前n个做索引】可以减少内存的浪费
• 当左前缀的字段基本相同的时候，可以在存储的时候选择将相同的部分放为后缀，或者配合代码可以对其不存储

索引使用优化

考虑列的查询频率和区分度

• 延迟关联

  • 当有多列的时候，使用一个内查询先查询出无法走索引的查询查出主键之后再用关联查询将这个内查询的结果的主键进行关联，会比直接查询要快点

  • -- | 0.00183800 | 
    select * from it_area where name like '%东山%'  
    
    -- | 20 | 0.00169300 | 
    select a.* from it_area as a inner join (select id from it_area where name like '%东山%') as t on a.id=t.id |
    

• 字符串转数值

  • 不想截取一半又有较高的相似度的列加索引的做法：

    可以将需要建立索引的字符串的列，用一个额外的列存储一个对应散列值，可以用crc32() 或者 md5()去实现唯一值映射，在全值匹配的时候可以减少索引的存储空间以及索引查询的速度

• 区分度

  • 当一个列的区分度太低的时候就没有必要增加索引
  • 一般系统区分度超过0.1就可以建立索引，不过有个索引的使用的黄金比是0.31
  • 计算区分度：select count(distinct left(列名,6))/count(*) from 表明;

• 模糊匹配

  • 在右模糊匹配是符合最左前缀原则的
  • 一般写sql的时候要习惯把模糊的匹配放最后