Mysql系列-联合索引

程序员fly

前言

对于联合索引的考察点,面试中常见的问题大概有这几个,但是重点肯定扯一些最左匹配原则,问一下自己是否能够答上关于联合索引相关的嘛。

  • 什么是联合索引
  • 联合索引的查找过程
  • 什么是最左前缀法则
  • 建立联合索引的时候为什么有的时候索引会失效
  • 索引下推过程描述

联合索引是什么

​ 基于多个字段创建的索引我们称为联合索引,比如我们创建索引create index idx on table(A,B,C) 我们称在字段A,B,C上创建了一个联合索引

存储结构

​ 在上篇文章中,我们知道,索引存储底层是B+树,在InnoDB存储引擎下,主键索引叶子节点存储的是数据,非主键索引上存储的是主键id,在联合索引下,这个B+树是如何组织的呢,我们通过一个具体的例子来看一下,首先我们先建立一个表,向表里添加一些数据。

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id自增',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
    `money` int(11) NULL DEFAULT NULL COMMENT '账户余额 ,真正开发时候,余额不能用整数哈',
    `ismale` int(11) NULL DEFAULT NULL comment '性别 0男1女',
    `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL comment '名称',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_bcd`(`age`, `money`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

联合索引底层存储结构

​ 联合索引和单个索引对比来讲,联合索引的所有索引项都会出现在索引上,存储引擎会先根据第一个索引项排序,如果第一个索引项相同的话才会去看第二个,所有我们在查询的时候,如果头索引不带的话,联合索引就会失效,因为在根节点他就不知道怎么往下走。比如我们现在select * from USER us where us.age=20 and us.money=30这个sql去查的,首先在根节点上age>1并且<60,那么读下一个节点,依次类推读到叶子节点上取出主键id回表查询所有的字段值。

最左前缀法则

​ 如果索引了多个列,要遵循最左前缀法则,查询从索引的最左前列开始,并且不能跳过索引中的列,啥意思,组合索引(age,money,name)你select * from user where money=3 AND name='程序员fly'跳过了age这一些,索引失效,这点面试官还挺爱问的,我们一起来做几个例子

用到组合索引

select *  from  user   where age>5
select *  from  user   where age=5 and money>3
select *  from  user   where age=5 and money=3 and name='程序员fly'

索引失效

select *  from  user   where money>3   //跳过开头的
select *  from  user   where  money>3 and name='程序员fly'  //跳过开头的索引失效

使用部分索引

select *  from  user   where age>5 AND money=3 //范围查询仅仅能用到第一个age,(Mysql在5.6之后好像优化器会把sql语句顺序调整为select *  from  user   where money=3 AND age>5 这里需要抽空研究一下)

索引下推

​ 上面我们大概了解到联合索引相关知识,现在我们可能有一个这样的需求,查询姓李的年龄20周岁男性用户的相关信息,我们可能接到这个需求的时候想着这样建立这样的联合索引(name,age),因为性别库中只要0,1两个值(因为枚举只要男和女,enen,其实这个社会上还是真实存在的其他这个枚举值的 ),性别字段区因为区分度不大,所以性别字段不适合建立索引,我们看下这个例子

SELECT * from user where  name like '李%' and age=20 and ismale=0;

MySQL5.6之前没有索引下推的概念

无索引下推需要四次

Mysql 5.6新增了索引下推的优化,譬如ame like '李%' and age=20 检索,MySQL5.6版本之前,会对匹配的数据进行回表查询。5.6版本后,会先过滤掉age!=20的数据,再进行回表查询,减少回表率,提升检索速度

回表一次

闲谈

本人学历是双非本科,自己本人能力有限,文章列的把自己所知道的已经网上看的总结下,自己还在持续学习中,开始写文章的原因是发现自己学完一个东西,有的时候老是发现自己学的模模糊糊的,没有自己的总结,所有选择写下来,能帮助各位更好,公众号有自己总结的一系列文章,需要的小伙伴还请关注下个人公众号点个赞,这将对我是很大的鼓励~

巨人肩膀

https://www.cnblogs.com/ibigb...

https://time.geekbang.org/col...

阅读 131
1 声望
0 粉丝
0 条评论
你知道吗?

1 声望
0 粉丝
文章目录
宣传栏