聊一聊 InnoDB 引擎中的这些索引策略

在上一篇中,我们简单的介绍了一下 InnoDB 引擎的索引类型,这一篇我们继续学习 InnoDB 的索引,聊一聊索引策略,更好的利用好索引,提升数据库的性能,主要聊一聊覆盖索引、最左前缀原则、索引下推。

覆盖索引

覆盖索引是指在普通索引树中可以得到查询的结果,不需要在回到主键索引树中再次搜索

建立如下这张表来演示覆盖索引:

mysql> create table T (
ID int primary key,
age int NOT NULL DEFAULT 0, 
name varchar(16) NOT NULL DEFAULT '',
index age(age))
engine=InnoDB;

我们执行select * from T where age between 13 and 25 语句,这条语句的执行流程大概为:

  • 1、在 age 索引树中查找到 age = 13 的记录,取得 ID 的值
  • 2、根据 id 的值在主键索引上查找所需要的所有信息
  • 3、在 age 树上往下取,重复 1、2 两步操作,直到 age 不符合条件为止。

如果我们将语句换为 select ID from T where age between 13 and 25,执行这条语句时,在 age 索引树上就可以查询到 ID 的值,省去了上面的回表操作,这样就减少了搜索次数,提升了查询效率。

这时候的 age 索引树已经可以满足我们的查询需求,age 索引就称为覆盖索引。

覆盖索引是常用的数据查询优化技术,可以极大的提升数据库性能,有以下几个原因:

  • 减少树的搜索次数,显著提升查询性能
  • 索引是按照值的顺序存储,所以对于 I/O 密集型的范围查询比随机从磁盘中读取每一行的 I/O 要少很多
  • 索引的条目远小于数据的条目,在索引树上读取会极大的减小数据库的访问量

最左前缀原则

最左前缀原则是建立在联合索引之上的,如果我们建立了联合索引,我们不需要使用索引的全部定义,只要用到了索引中的最左边的那个字段就可以使用这个索引,这就是 B-tree 索引支持最左前缀原则。

建立如下这张表来解释最左前缀原则:

mysql> create table T (
ID int primary key,
age int NOT NULL DEFAULT 0, 
name varchar(16) NOT NULL DEFAULT '',
ismale tinyint(1) DEFAULT NULL,
email varchar(64),
address varchar(255),
KEY `name_age` (`name`,`age`))
engine=InnoDB;

我们建立了联合索引 name_age,现在,假设我们有一下三种查询情景:

  • 1、查出用户名的第一个字是“张”开头的人的年龄。即查询条件子句为"where name like '张%'"
  • 2、查处用户名中含有“张”字的人的年龄。即查询条件子句为"where name like '%张%'"
  • 3、查出用户名以“张”字结尾的人的年龄。即查询条件子句为"where name like '%张'"

在这三种情况中,第一种情况可以利用到 name_age 这个联合索引,加速查询,可以看出,我们并没有
使用索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。

如果我们将索引的顺序调整为KEY name_age (age,name) ,那么上面三种情况都使用不到这个联合索引。

维护索引需要代价,所以有时候我们可以利用“最左前缀”原则减少索引数量

索引下推

索引下推优化是 MySQL 5.6 引入的, 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

建立如下这张表来解释索引下推:

mysql> create table T (
ID int primary key,
age int NOT NULL DEFAULT 0, 
name varchar(16) NOT NULL DEFAULT '',
ismale tinyint(1) DEFAULT NULL,
email varchar(64),
address varchar(255),
KEY `name_age` (`name`,`age`))
engine=InnoDB;

在表中建立了 name、age 的联合索引,我们执行 select * from T where name like '张%' and age=10 and ismale=1;语句,我们已经知道了B-tree 索引的最左前缀原则,所以将会用到 name_age 索引,因为索引下推优化,会在 name_age 索引树上判断 name 和 age 是否满足

根据我们上面的执行语句,会在 name_age 索引树上查找 name 以 '张' 开头的并且 age = 10 的数据,然后在回到主键索引树中查询所需要的信息,并不是所有 name_age 索引树上查找 name 以 '张' 开头的数据都回主键索引树中查询数据,这样就减少了一些不必要的查询。

假设我们的数据如下图所示:
索引下推

在 name_age 索引树中有四条符合 name 以 '张'开头的数据,如果没有索引下推,则需要回到主键索引树上判断 age 是否等于 10 ,这样就需要回表四次,而有了索引下推之后,在 name_age 索引树上就判断 age 是否等于 10 ,只需要回表两次,这样就减少了回表次数,提升了查询性能。

以上就是关于 InnoDB 引擎中的索引策略,感谢您的阅读,希望这篇文章对您的学习或者工作有所帮助。

最后

目前互联网上很多大佬都有 MySQL 相关文章,如有雷同,请多多包涵了。原创不易,码字不易,还希望大家多多支持。若文中有所错误之处,还望提出,谢谢。

欢迎扫码关注微信公众号:「平头哥的技术博文」,和平头哥一起学习,一起进步。

平头哥的技术博文

posted @ 2020-01-29 08:33  平头哥的技术博文  阅读(2701)  评论(1编辑  收藏  举报