MySQL 索引实现

见不见
275 阅读6分钟

接上一节的内容

innoDB索引模型

innoDB中，表都是根据主键顺序以索引的方式存放的 被称为索引组织表 innoDB使用B+树的索引模型 所以数据存放到B+树中

该组织表构建顺序
        1.存在主键 使用主键来构建索引
        2.如果不存在主键 就使用表中唯一性非空索引 存在多个的话 就选创建的第一个
        3.都没有的话 innoDB引擎会创建一个6字节隐藏列 来构建索引

主键索引（聚簇索引）的叶子节点存整行数据

    此处‘整行数据’ 不仅包含索引的键值 还包含记录所在其他列的值 其中的索引的记录是根据键值的顺序排列的 但是逻辑顺序 不是物理的顺序

非主键索引（二级索引）的叶子节点内容存主键的值

接下里用一个实例来说明

CREATE TABLE `t2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  index(a),
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT INTO t2 VALUES(100,'1',jjj'),(200,'2','jkj'),(300,'3','jaj'),(400,'4','jcj'),(500,'5','bbb')

讨论一个问题------->基于主键查询和普通查询的区别

  1. select * from t2 where id = `500`;
  2. select * from t2 where a = `5`

语句1 主键查询 只需要搜索ID树即可

语句2 普通索引查询 则是先索索k树 得到ID值 再去搜索ID树 也叫回表操作

索引维护

B+树为保证索引有序性 对新插入值维护 上图中

如果ID = 700 则需要在后面插一条记录

如果ID = 400 需要逻辑上挪动后面数据 空出位置 如果当前数据页已经满了 这时会申请一个新页 挪动部分数据 这个过程也叫页分裂 这种情况下 性能受到影响 同时还会影响数据页的利用率 整体空间的利用率下降50%

数据页也会合并 当相邻的数据页由于删除数据 利用率很低的话 会将数据页合并(也 叫分裂的逆过程)

接下来讨论下自增主键的问题:

建表的时 我们指定自增主键 这种情况下的插入数据模式 是上面提到的递增插入 追加操作 不涉及数据页的挪动 分页等操作

同时由于非主键索引的叶子节点存储的都是主键的值 所以主键长度越小 普通索引的叶子节点就越小 占用的空间也越小

从性能和空间上考虑 自增主键都是最好的选择 当然也有特列：使用业主主键做索引的情况 同时有条件的限制------》

  1. 只有一个索引
  2. 该索引是唯一索引
 
    没有其他索引的话 不用考虑叶子节点的大小 同时将唯一索引设置成主键 避免扫描两棵树

回表

上面我们提到了回表 先来看下什么是回表？

还是上面的那个图哈

select * from t2 where a between 3 and 5;  需要执行几次树搜索操作 扫描多少行？？

    1.在 a 索引树上找到 a=3 的记录，取得 ID = 300...
    2.再到 ID 索引树查到 ID=300 对应的值
    3.在 a 索引树上找到 a=5 的记录，取得 ID = 500...
    4.。。。。同上
    5.在 a 索引树上找到 a=6 的记录，不满足条件 break;.

    在上面过程中 回到主键索引树搜索的过程 我们称为回表

那么怎样可以避免回表操作 或者减少回表的操作呢？

覆盖索引

    select ID from t2 where k between 3 and 5;  
    
        注： 此处索引其实拿到了3个值  但对于MySQL服务层来说 就是拿到两条符合记录 返回值为2  关于扫描行数的问题 后续再说

如果上面的语句改成这样的 只需要查询ID值 而ID值已经在索引树上 因此可以直接提供结果 不需要回表 也就说在索引k已经覆盖了我们的查询需求 即覆盖索引

覆盖索引可以减少树的搜索次数 提高查询性能 所以使用覆盖索引是常见的优化方式

**联合索引 **

简单理解就是多列索引 索引项是按照索引定义里面的字段排序的 B+树结构可以利用索引的“最左前缀”,来定位记录。也就说我们常说的 ’最左匹配原则‘

   假设上面表结构中 a b字段建立联合索引  对于(a,b)索引
   
   1.select *  from t2 where a = xxx and b = xxx;
   2.select *  from t2 where a = xxx;
   3.select *  from t2 where b = xxx;
   4.select *  from t2 where a >= xxx and b = xxx;
   5.select id from t2 where b = xxx;
   6.select * from t2 where a <= xxx and b = xxx

(a,b)索引在树上结构大概是这样的

SQL1 可以用到联合索引
SQL2 可以用到联合索引
SQL3 不可以使用联合索引
SQL4 可以用到联合索引(type为rang 注意如果扫描行数过多则不会走索引)

SQL5 可以用到覆盖索引(type为index)

SQL6 可以用到联合索引(type为rang 注意如果扫描行数过多则不会走索引)

只要满足最左索引,就可以索引来加速检索 这个前缀可以是联合索引的最左N个字段 也可以是字符串的最左M个字符

索引下推

索引下推(index condition pushdown) 简称MySQL ICP优化 mysql5.6 之后新增的特性 默认是开启

索引下推减少了二级索引过滤where条件的回表次数和减少mysql server层和引擎层的交互

    select * from t2 where a <= 100 and b = 'jjdj';

explain 分析发现Extra 中Using index condition 出现这个说明使用了索引下推 二级索引内部判断了 b = 'jjdj' 是否成立 减少回表次数

ICP优化的使用条件

  1. 只适用于二级索引
  2. explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
  3. ICP可以支持MyISAM和InnnoDB存储引擎

以上皆为个人理解，如果有错的地方，欢迎指出

avatar
文章
阅读
粉丝