一个人至少拥有一个梦想,有一个理由去坚强

心若没有栖息的地方,到哪里都是在流浪

MySQL实站45讲学习笔记—深入浅出索引(下)

上一篇介绍了InnoDB索引的数据结构模型,这篇作者主要讲的是MySQL索引有关的概念。

本来想写自己的笔记总结,但是感觉晓斌作者写的更加浅显易懂,实例也一看就明白,因此为了以后巩固,有些例子还是原文。

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

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

上面新建一张表,其中k是索引

Select * from T where k between 3 and 5;

现在执行上面这条语句,需要执行几次树的索引操作,会扫描多少行呢?

《MySQL实站45讲学习笔记—深入浅出索引(下)》

图1,InnoDB的索引组织结构

这条SQL语句的执行流程为:

  1. 在K索引树上找到k=3的记录,获取ID=300.
  2. 再到ID索引树找到ID=300对应的R3.
  3. 在k索引树找到k=5的记录,获取ID=500.
  4. 再到ID索引树找到Id=500的对应的R4.
  5. 再到k索引树取下一个值k=6,不满足条件则退出循环,结束搜索过程.

如上,回到主键搜索树搜索的过程,称为回表。

为什么需要回表?因为查询结果所需要的数据只在主键索引上,所以不得不回表。

有什么办法可避免呢?那就是索引覆盖。

索引覆盖

所谓覆盖索引是指,一条查询语句所需要的查询结果在普通索引中就能查询到,不需要再去主键索引中查询,因此不需回表。

优点:减少树的查询次数,显著提升查询性能,是常用的数据库性能优化手段。

Select ID from T where k between 3 and 5;

例如上面这条查询语句,就可以不用回表查询。因为ID在k索引树中就能查询到。

最左前缀原则

联合索引:MySQL中的索引可以以一定顺序引用多个列,这种索引叫做联合索引,一般的,一个联合索引是一个有序元组,其中各个元素均为数据表的一列。

单列索引可以看成联合索引元素数为1的特例。

例如下面表的定义:

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

例如上面市民表建表语句,其中name和age就是联合索引。因为会频繁一起查询name和age,所以建立了联合索引,那若偶尔需要查询身份证号和地址,是否需要单独建立身份证号和地址的联合索引呢?

 

B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。

这篇文章对最左前缀原则说明的简单易懂

问题:在建立联合索引的时候,如何安排索引内的字段顺序?

第一原则:如果通过调整顺序,可以少维护一个索引,那么这个顺序则优先考虑使用。

第二原则:空间。

索引下推

《MySQL实站45讲学习笔记—深入浅出索引(下)》

图2,(name,age)联合索引示意图

还是上例的联合索引(name, age)

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

这条搜索语句在搜索索引树的时候,只能用到索引最左前缀原则查询”张”,找到第一个满足条件的记录ID3,然后再判断其他条件是否满足。相比全表扫描要快。

在MySQL5.6之前,只能从ID3一个个回表。在主键索引上找出数据行,再对比字段值。

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

《MySQL实站45讲学习笔记—深入浅出索引(下)》

图3,无索引下推执行流程

《MySQL实站45讲学习笔记—深入浅出索引(下)》

图4,有索引下推执行流程

每一个虚线箭头表示回表一次,从图四可看出,有索引下推,则减少了回表次数,从而提高了查询效率。

 

 

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注