mysql索引详解(十分钟时间搞定)

2023-12-30 14:20:29

一、简述索引发展过程

为什么数据库要有索引?
索引其实就是它的“目录”。
一本 500 页的书,如果你想快速找到其中的某一个知识点,在不借助目录的情况下,那我估计你可得找一会儿。就像你看到我这篇帖子一样,查找完全随缘。
一个数据库的索引到底应该怎么设计呢?

  1. 早期的索引底层设计其实是一个hash表结构,设计出一个hash函数,根据函数求出相应的值即为hash表对应的键值,但是根据数据结构查找章节的内容,我们知道,其实利用hash表来存储数据,对于hash函数的设计要求非常高,对于数据量比较大一不小心就会产生大量的冲突,再利用线性探测法、拉链法去解决冲突,耗时耗力,完全违背了我们简单查询的目的。
    在这里插入图片描述

  2. 后来,我们想到可以用二叉树来解决啊!当有新结点的产生,我们就把他插入到树中去。但是二叉树又没有顺序,我们就选择设计一个二叉排序树去解决,数据左小右大,暂时解决!
    请添加图片描述

  3. 但是后来我们又发现,当数据量过大,二叉排序树一直插入节点,会出现其中某些子树的深度变得特别的深,导致在搜索的时候出现查询最深节点的数据时,要经过整个树的高度。有点小烦!于是我们又引入了平衡二叉树,来降低树的高度,但是又引发了新问题,平衡二叉树节点只能插入一条数据,会不断的进行平衡旋转,还是有点烦!请添加图片描述

  4. 之后,我们就引入B树来解决上面问题,虽然B树解决了节点存储,不断调整平衡的问题,但还是有点小瑕疵,比如范围查找,当我找到一个值时,不能最大效率的找到他前后的N个值。
    请添加图片描述

  5. 最后引入了B+树来解决这种问题,让搜索的效率变得更高效。
    请添加图片描述

二、索引分类

  • 从功能逻辑上说,索引可以分为:普通索引、唯一索引、主键索引、全文索引。
  • 按照物理实现方式,索引可以分为:聚簇索引非聚簇索引
  • 按照作用字段个数进行划分,分成单列索引联合索引

三、索引介绍

1. 普通索引和主键索引

(1)简述
- 其中主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引。
- 非主键索引也即普通索引,它的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引。
- 普通索引和主键索引两个要放在一起进行介绍,因为他俩涉及到回表问题,下面会说到。
(2)创建过程
一般一张表设计出来的时候他的主键就已经是确定的,因此主键索引也是默认创建好的,如下:

create table iam_group
(
    id                  varchar(255) not null
        primary key,
    name                varchar(255) null,
    parent_group_id     varchar(255) null,
);

普通索引:

create index iam_group_index
    on iam_group (parent_group_id);

其中iam_group_index为索引名,iam_group为表名,parent_group_id为要在表的这个列上加索引

2. 单列索引和联合索引

  • 上面普通索引的举例就是单列索引,只在单列上加了索引
  • 联合索引是在多列上加了索引,举例:
create index IDX_AM_ACCESS_LOGIN_NAME_UNION
    on iam_user (login_name, type, group_id);

表示对iam_user这张表login_nametypegroup_id加了一个联合索引

四、不得不提的索引高阶

首先我们先创建一张表,进行举例:

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');

说明:上面新建了一张表,主键是ID,列有k和s,k上加了普通索引,然后又插入了一批数据。
这个时候创建出来的索引是这样的结构
在这里插入图片描述

1.回表

首先和大家探讨下回表的问题,在表 T 中,如果我执行 select * from T where k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?
我们一起来看看这条 SQL 查询语句的执行流程:

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

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

2. 覆盖索引

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

3. 最左前缀原则

假如为每一种查询都设计一个索引,索引是不是太多了,因此就需要设计联合索引,但是在建立联合索引的时候,如何安排索引内的字段顺序呢?
这里我们的评估标准是,索引的复用能力。就出现了最左前缀原则,如果查询时使用了联合索引中的一部分列,那么这些列必须按照联合索引中的顺序出现,从左到右进行匹配。

  • like aa%后模糊查询索引有效,like %aa模糊查询索引无效。
  • mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。范围列可以用到索引,但是范围列后面的列无法用到索引。即,索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引
  • InnoDB会把主键字段放到索引定义字段后面, 当然同时也会去重。 所以,当主键是(a,b)的时候, 定义为c的索引,实际上是(c,a,b); 定义为(c,a)的索引,实际上是(c,a,b) 你看着加是相同的 ps 定义为(c,b)的索引,实际上是(c,b,a)

4. 索引下推

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

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

说明:建立了tuser表,在表中建立了name和age的联合索引
执行下面这条查询语句时

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

无索引下推的情况:
在这里插入图片描述
有索引下推的情况:在这里插入图片描述
可以看到,有索引下推的时候,过滤掉了两条age不等于10的回表情况,减少了两次回表,提高了效率!
最后想要对一个查询语句进行分析,可以使用explain关键字进行分析,该文就不一一举例了

文章来源:https://blog.csdn.net/weixin_45404884/article/details/135239415
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。