从磁盘I/O角度聊聊Mysql索引

从磁盘I/O角度聊聊Mysql索引

首先思考下这个问题，为什么添加了索引就能提高mysql的查询效率呢？MySQL查询中，性能瓶颈究竟在哪里？也就是今天所说的：磁盘I/O上

为什么添加了索引就能提高查询效率呢?

不同与Redis、Memcached等内存储存数据库，MySQL数据是以存储引擎规定的特定数据格式存储在物理磁盘上的。计算机存储体系如下图：

image-20200426205957608.png

所以，mysql查询中，最耗时的阶段就是磁盘I/O阶段，那如何减少mysql查询中磁盘的I/O次数呢？

假设有一张表，里面储存了N条数据，查询其中符合条件的数据，时间复杂度为O(N)，需要对整个表进行顺序遍历。那么添加了索引为什么就能提交查询的效率呢，我们可以先看看索引的数据结构，mysql最主要的索引数据结构采用了BTree也就是B+树。我们定义一张数据表，其中id为主键索引，age是一个索引字段

image-20200426210513462.png

，然后往数据表中加入如下数据：

image-20200426210625973.png

与之对应age建立的B+树结构如下：

这是一个平衡有序N叉树（相对于平衡二叉树而言，B+树的非叶子节点可以有N个分支，相对于B树来说，B+树的非叶子节点是不存储实际数据，仅用于B+树的检索，叶子节点则用于存储真正的数据）第一层为非叶子节点，只存储了age信息，二层为叶子节点，存储了age信息和主键id的信息。

B+树的优势：非叶子节点不存储真正的数据，可以保证存储更多的检索分叉，可以降低整棵树的高度，从而降低磁盘I/O，叶子结点之间是相互连接的，构成有序的双向链表，方便叶子结点双向的横向遍历，有利于数据的范围查找

image-20200426210727484.png

在进行此条sql查询的时候，mysql会用age字段的索引进行查找，从根节点开始，然后按照B+树的结构一层一层的往下遍历，找到符合条件的数据后会根据索引上对应的主键id再去主键索引上查找具体的数据，如下图

image-20200426211702014.png

整个过程的时间复杂度为O(logN)，从而达到提高查询效率的目的，当然使用了索引也不是就万事大吉，关机开黑，在索引上仍有功夫可下

如何正确用到索引

从减少磁盘I/O的角度上优化索引

1. InnoDB索引，主键尽量避免使用很长的字段

和 myisam储存引擎不同，myisam索引文件和数据文件是分开存储的，myisam索引上叶子结点数据记录的指向是物理地址,根据物理地址去数据文件查找对应的数据,如图

image-20200426212856534.png

，而InnoDB叶子结点上存储的是索引值和数据记录的指向（主键），如果主键字段过长，二级节点存储的主键就会越少，会使整个二级节点索引数更大，检索会需要更多的I/O次数

image-20200426212958889.png

2. 在保障索引区分度的情况下，被索引的字段尽量不要太长

   在 MySQL 的InnoDB储存引擎中，BTree索引的每个节点都是一个磁盘页面,称为page，大小一般默认16k

  show variables like 'innodb_page_size';

在BTree的检索中，每读取一个节点就会进行一次磁盘I/O，一次磁盘I/O就获得更多的索引建，整个B+树就会降低

索引的区分度（选择性）：不重复的索引值（基数）/ 索引总数，这个值会在(0,1]之间，越趋近1则表示这个索引区分度越高越有价值

    select count(distince(name)) / count(1) as selectivity from t_student;

假设使用了更短的索引建

    select count(distince(left(name,1))) / count(1) as selectivity from t_student;

3. 查询中可以利用索引覆盖，从而避免不必要的回表

   回表：通过二级索引无法查到整条数据的记录，需要根据二级索引查找主键再根据主键索引去查找整条数据的过程

   如果查询所需要的的信息恰巧在二级索引中能够得到，那就可以避免不必要的回表，减少磁盘的I/O次数，不需要回表的现象称为 索引覆盖 。进而引申除了联合索引，从而达到索引覆盖的目的

4. 不要建立太多的索引

   数据表中的任何一条数据的写操作都会影响到索引树，随着数据量的增大，索引树也会变得越来越大，数据的写操作会耗费更多的磁盘I/O次数，在索引区分度非常低的字段上建立索引，反而达不到提升查询效率的目的