数据库索引原理

先看个例子

在下面这个表T中,如果我执行 select* from t where k between3and5,需要执行几次树的搜索操作,会扫描多少行?

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'),(208,2,'bb'),(300,3,'cc'),(509,5,'ee'),(600,6,'ff'),(788,7,'gg')

表结构如下所示:

image

现在,我们一起来看看这条SQL查询语句的执行流程:

  1. 在k索引树上找到k=3的记录,取得ID=300
  2. 再到ID索引树查到ID=300对应的R3
  3. 在k索引树取下一个值k=5,取得ID=500
  4. 再回到|D索引树查到|D=500对应的R4
  5. 在k索引树取下一个值k=6,不满足条件,循环结束

在这个过程中,回到主键索引树搜索的过程,我们称为回表。可以看到,这个查询过程读了k索引树的3条记录(步骤1、3和5),回表了两次(步骤2和4)。

如何进行索引优化,避免回表?

什么是覆盖索引?

如果执行的语句是select ID from T where k between 3 and 5, 这时只需要查ID的值, 而ID的值已经在k索引树上了, 因此可以直接提供查询结果, 不需要回表。也就是说,在这个查询里面,索引k已经“覆盖了”我们的查询需求, 我们称为覆盖索引

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名称',
  `age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `I_name` (`name`)
) ENGINE=InnoDB;

INSERT INTO student (name, age) VALUES("小赵", 10),("小王", 11),("小李", 12),("小陈", 13);

主键索引和普通索引的结构如下图:

如何使用覆盖索引

创建联合索引,可以使用上覆盖索引。

ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);

联合索引结构如下图所示:

SELECT age FROM student WHERE name = '小李';
  1. 在name,age联合索引树上找到名称为小李的节点
  2. 此时节点索引里包含信息age 直接返回 12

image

可以看到Extra中Using index表明我们成功使用了覆盖索引。

索引原则

最左前缀原则

B+树这种索引结构, 可以利用索引的“最左前缀”, 来定位记录。为了直观地说明这个概念, 我们用(name, age) 这个联合索引来分析

image

当你的逻辑需求是查到所有名字是“张三”的人时, 可以快速定位到ID4, 然后向后遍历得到所有 需要的结果。

如果你要查的是所有名字第一个字是“张”的人, 你的SQL语句的条件是"where name like ‘张%’"。这时, 你也能够用上这个索引, 查找到第一个符合条件的记录是ID3, 然后向后遍历, 直到不满足条件为止。

只要满足最左前缀, 就可以利用索引来加速检索。在建立联合索引的时候, 如何安排索引内的字段顺序。

索引复用能力

这里我们的评估标准是, 索引的复用能力。因为可以支持最左前缀, 所以当已经有了(a,b)这个联合索引后, 一般就不需要单独在a上建立索引了。因此, 第一原则是, 如果通过调整顺序, 可以少维护一个索引, 那么这个顺序往往就是需要优先考虑采用的。

索引空间占用

如果既有联合查询, 又有基于a、 b各自的查询呢?查询条件里面只有b的语句, 是无法使 用(a,b)这个联合索引的, 这时候你不得不维护另外一个索引, 也就是说你需要同时维护(a,b)、 (b) 这两个索引,我们要考虑的原则就是空间了。name字段是比age字段大的 , 那我就建议你创建一个(name,age)的联合索引和一个(age)的单字段索引。(name)字段索引需要的空间会大一些,所以推进创建一个(name,age)+(age)。

索引下堆

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

现在假设有这样一个表:

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

现在要进行如下查询:

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

如果没索引下堆优化的情况是怎么执行的呢?

只能从ID3开始一个个回表。 到主键索引上找出数据行, 再对比字段值。

image

有索引下堆优化的情况

把 age !=10 的先过滤掉,然后再回表查询。

索引下堆优化

本文分享自微信公众号 - 程序员开发者社区(gh_016ffe40d550),作者:猿星人

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MongoDB 索引

    当往一个集合中插入多个文档后,每个文档经过存储殷引擎后,有一个位置信息,通过这个位置信息。就能从存储引擎中读出该文档。在 mmapv1 引擎下,位置信息是【文件...

    王小明_HIT
  • 数据库索引有哪些?

    如果数据量比较少,是否使用索引对结果的影响并不大,比如数据不超过 1000 行,那么可以不建索引。

    王小明_HIT
  • 怎么给字符串加索引

    如果 email 不建索引,那么就只能全表扫描,如果 email 这个字段是哪个没有索引,那么这个语句只能做全表扫描。

    王小明_HIT
  • 技术分享 | 常见索引问题处理

    数据库技术爱好者,爱可生 DBA 团队成员,负责 MySQL 日常问题处理以及数据库运维平台的问题排查,擅长 MySQL 主从复制及优化,喜欢钻研技术问题,还有...

    爱可生开源社区
  • MySQL索引的原理及使用

      上篇文章中学习了MySQL库的架构以及存储引擎,了解了基本索引(普通索引,唯一索引,主键索引),着重介绍了innerDB的存储方式以及内存模型,本篇文章和大...

    会说话的丶猫
  • 聊一聊 InnoDB 引擎中的这些索引策略

    这一篇我们学习 InnoDB 的索引,聊一聊索引策略,更好的利用好索引,提升数据库的性能,主要聊一聊覆盖索引、最左前缀原则、索引下推。

    黄泽杰
  • MongoDB 索引

    当往一个集合中插入多个文档后,每个文档经过存储殷引擎后,有一个位置信息,通过这个位置信息。就能从存储引擎中读出该文档。在 mmapv1 引擎下,位置信息是【文件...

    王小明_HIT
  • 【Oracle】-【索引】先查数据再建索引,还是先建索引再插数据?

    1、新建一个表结构,创建索引,将百万或千万级的数据使用insert导入该表。 2、新建一个表结构,将百万或千万级的数据使用isnert导入该表,再创建索引。 ...

    bisal
  • Mongodb分页查询优化上

    最近遇到mongo集群性能问题,主要体现在查询性能或者聚合性能慢(查询类似关系型数据库中select * from xx where a='xx',另外聚合类...

    徐靖
  • 类索引和父类索引

    类索引(this_class)和父类索引(super_class)都是一个u2类型的数据,类索引用于确定这个类的全限定名,父类索引用于确定这个类的父类全限定名。...

    不会飞的小鸟

扫码关注云+社区

领取腾讯云代金券