Home 成长之路 索引

索引

0 87

索引

什么是索引?

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说,索引就相当于目录。为了方便查找书中的内容,通过对内容建立索引形成目录。索引是一个文件,它是要占据物理空间的。

索引有哪些优缺点?

索引的优点

可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

索引的缺点

时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率;
空间方面:索引需要占物理空间。

深入理解Mysql索引之B+Tree(在此过程中了解其他索引概念,重要!!!)

1 索引是什么及工作机制?

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。其工作机制如下图:
在这里插入图片描述

上图中,如果现在有一条sql语句 select * from user where id = 40,如果没有索引的条件下,我们要找到这条记录,我们就需要在数据中进行全表扫描,匹配id = 40的数据。

如果有了索引,我们就可以通过索引进行快速查找,如上图中,可以先在索引中通过id = 40进行二分查找,再根据定位到的地址取出对应的行数据。

 

2. MySQL数据库为什么要使用B+TREE作为索引的数据结构?

2.1 二叉树为什么不可行

原因:存在退化成链表的情况,相当于全表搜索

分析:

对数据的加速检索,首先想到的就是二叉树,二叉树的查找时间复杂度可以达到O(log2(n))。下面看一下二叉树的存储结构:

二叉搜索树相当于一个二分查找;二叉查找能大大提升查询的效率,但是它有一个问题:二叉树以第一个插入的数据作为根节点,如上图中,如果只看右侧,就会发现,就是一个线性链表结构。如果我们现在的数据只包含1, 2, 3, 4,就会出现以下情况:

如果我们要查询的数据为4,则需要遍历所有的节点才能找到4,即,相当于全表扫描,就是由于存在这种问题,所以二叉查找树不适合用于作为索引的数据结构。

2.2 平衡二叉树为什么不可行

原因:在二叉树的基础上解决了退化(即全表扫描)的情况,但是

分析:

平衡二叉查找树定义为:节点的子节点高度差不能超过1,如上图中的节点20,左节点高度为1,右节点高度0,差为1,所以上图没有违反定义,它就是一个平衡二叉树。保证二叉树平衡的方式为左旋,右旋等操作,至于如何左旋右旋,可以自行去搜索相关的知识。

如果上图中平衡二叉树保存的是id索引,现在要查找id = 8的数据,过程如下:

1.把根节点加载进内存,用8和10进行比较,发现8比10小,继续加载10的左子树

2.把5加载进内存,用8和 5比较,同理,加载5节点的右子树

3.此时发现命中,则读取id为8的索引对应的数据

索引保存数据的方式一般有两种:

  • 数据区保存id 对应行数据的所有数据具体内容。(Innodb存储引擎)
  • 数据区保存的是真正保存数据的磁盘地址。

到这里,平衡二叉树解决了存在线性链表的问题,数据查询的效率好像也还可以,基本能达到O(log2(n)), 那为什么mysql不选择平衡二叉树作为索引存储结构,他又存在什么样的问题呢?

  1. 搜索效率不足。一般来说,在树结构中,数据所处的深度,决定了搜索时的IO次数(MySql中将每个节点大小设置为一页大小,一次IO读取一页 / 一个节点)。如上图中搜索id = 8的数据,需要进行3次IO。当数据量到达几百万的时候,树的高度就会很恐怖。
  2. 查询不不稳定。如果查询的数据落在根节点,只需要一次IO,如果是叶子节点或者是支节点,会需要多次IO才可以。
  3. 存储的数据内容太少。没有很好利用操作系统和磁盘数据交换特性,也没有利用好磁盘IO的预读能力。因为操作系统和磁盘之间一次数据交换是以页为单位的,一页大小为 4K,即每次IO操作系统会将4K数据加载进内存。但是,在二叉树每个节点的结构只保存一个关键字,一个数据区,两个子节点的引用,并不能够填满4K的内容。幸幸苦苦做了一次的IO操作,却只加载了一个关键字。在树的高度很高,恰好又搜索的关键字位于叶子节点或者支节点的时候,取一个关键字要做很多次的IO。

那有没有一种结构能够解决二叉树的这种问题呢?有,那就是多路平衡查找树。

 

2.3 多路平衡查找树(Balance Tree)

B Tree 是一个绝对平衡树,所有的叶子节点在同一高度,如下图所示:在这里插入图片描述

上图为一个2-3树(每个节点存储2个关键字,有3路),多路平衡查找树也就是多叉的意思,从上图中可以看出,每个节点保存的关键字的个数和路数关系为:关键字个数 = 路数 – 1。

假设要从上图中查找id = X的数据,B TREE 搜索过程如下:

1.取出根磁盘块,加载40和60两个关键字。
2.如果X等于40,则命中;如果X小于40走P1;如果40 < X < 60走P2;如果X = 60,则命中;如果X > 60走P3。
3.根据以上规则命中后,接下来加载对应的数据, 数据区中存储的是具体的数据或者是指向数据的指针。

为什么说这种结构能够解决平衡二叉树存在的问题呢?

B Tree 能够很好的利用操作系统和磁盘的交互特性, MySQL为了很好的利用磁盘的预读能力,将页大小设置为16K,即将一个节点(磁盘块)的大小设置为16K,一次IO将一个节点(16K)内容加载进内存。这里,假设关键字类型为 int,即4字节,若每个关键字对应的数据区也为4字节,不考虑子节点引用的情况下,则上图中的每个节点大约能够存储(16 * 1000)/ 8 = 2000个关键字,共2001个路数。对于二叉树,三层高度,最多可以保存7个关键字,而对于这种有2001路的B树,三层高度能够搜索的关键字个数远远的大于二叉树。

这里顺便说一下:在B Tree保证树的平衡的过程中,每次关键字的变化,都会导致结构发生很大的变化,这个过程是特别浪费时间的,所以创建索引一定要创建合适的索引,而不是把所有的字段都创建索引,创建冗余索引只会在对数据进行新增,删除,修改时增加性能消耗。

B树确实已经很好的解决了问题,我先这里先继续看一下B+Tree结构,再来讨论BTree和B+Tree的区别。

先看看B+Tree是怎样的,B+Tree是B Tree的一个变种,在B+Tree中,B树的路数和关键字的个数的关系不再成立了,数据检索规则采用的是左闭合区间,路数和关键个数关系为1比1,具体如下图所示:

如果上图中是用ID做的索引,如果是搜索X = 1的数据,搜索规则如下:

取出根磁盘块,加载1,28,66三个关键字。
X <= 1 走P1,取出磁盘块,加载1,10,20三个关键字。
X <= 1 走P1,取出磁盘块,加载1,8,9三个关键字。
已经到达叶子节点,命中1,接下来加载对应的数据,图中数据区中存储的是具体的数据。

 

2.4 B TREE和B+TREE区别是什么?

B+Tree 关键字的搜索采用的是左闭合区间,之所以采用左闭合区间是因为他要最好的去支持自增id,这也是mysql的设计初衷。即,如果id = 1命中,会继续往下查找,直到找到叶子节点中的1。

B+Tree 根节点和支节点没有数据区,关键字对应的数据只保存在叶子节点中。即只有叶子节点中的关键字数据区才会保存真正的数据内容或者是内容的地址。而在B树种,如果根节点命中,则会直接返回数据。

在B+Tree中,叶子节点不会去保存子节点的引用。

B+Tree叶子节点是顺序排列的,并且相邻的节点具有顺序引用的关系,如上图中叶子节点之间有指针相连接。

 

2.5 MySQL为什么最终要去选择B+Tree?

B+Tree是B TREE的变种,B TREE能解决的问题,B+TREE也能够解决(降低树的高度,增大节点存储数据量)

B+Tree扫库和扫表能力更强。如果我们要根据索引去进行数据表的扫描,对B TREE进行扫描,需要把整棵树遍历一遍,而B+TREE只需要遍历他的所有叶子节点即可(叶子节点之间有引用)。

B+TREE磁盘读写能力更强。他的根节点和支节点不保存数据区,所以根节点和支节点同样大小的情况下,保存的关键字要比B TREE要多。而叶子节点不保存子节点引用,能用于保存更多的关键字和数据。所以,B+TREE读写一次磁盘加载的关键字比B TREE更多。

B+Tree排序能力更强。上面的图中可以看出,B+Tree天然具有排序功能。

B+Tree查询性能稳定。B+Tree数据只保存在叶子节点,每次查询数据,查询IO次数一定是稳定的。当然这个每个人的理解都不同,因为在B TREE如果根节点命中直接返回,确实效率更高。

 

3 MySQL B+Tree具体落地形式

这里主要讲解的是MySQL根据B+Tree索引结构不同的两种存储引擎(MYISAM 和 INNODB)的实现。

首先找到MySQL保存数据的文件夹,看看MySQL是如何保存数据的:

mysql> show variables like ‘%datadir%’;
+—————+————————+
| Variable_name | Value |
+—————+————————+
| datadir | /usr/local/mysql/data/ |
+—————+————————+

进入到这个目录下,这个目录下保存的是所有数据库,再进入到具体的一个数据库目录下。就能够看到MySQL存储数据和索引的文件了。

这里我创建了两张表,user_innod和user_myisam,分别指定索引为innodb和myisam。对于每张表,MySQL会创建相应的文件保存数据和索引,具体如下:

-rw-rw—-. 1 mysql mysql 8652 May 3 21:11 user_innodb.frm
-rw-rw—-. 1 mysql mysql 109051904 May 7 21:26 user_innodb.ibd
-rw-rw—-. 1 mysql mysql 8682 May 16 18:27 user_myisam.frm
-rw-rw—-. 1 mysql mysql 0 May 16 18:27 user_myisam.MYD
-rw-rw—-. 1 mysql mysql 1024 May 16 18:27 user_myisam.MYI

从图中可以看出:

MYISAM存储引擎存储数据库数据,一共有三个文件:
Frm:表的定义文件。
MYD:数据文件,所有的数据保存在这个文件中。
MYI:索引文件。

Innodb存储引擎存储数据库数据,一共有两个文件(没有专门保存数据的文件):
Frm文件: 表的定义文件。
Ibd文件:数据和索引存储文件。数据以主键进行聚集存储,把真正的数据保存在叶子节点中。

 

3.1 MyISAM存储引擎

说明:为了画图简便,下面部分图使用在线数据结构工具进行组织数据,组织的B+Tree为右闭合区间,但不影响理解存储引擎数据存储结构。

在MYISAM存储引擎中,数据和索引的关系如下:在这里插入图片描述

 

如何查找数据的呢?
如果要查询id = 40的数据:先根据MyISAM索引文件(如上图左)去找id = 40的节点,通过这个节点的数据区拿到真正保存数据的磁盘地址,再通过这个地址从MYD数据文件(如上图右)中加载对应的记录。

如果有多个索引,表现形式如下:在这里插入图片描述

所以在MYISAM存储引擎中,主键索引和辅助索引是同级别的,没有主次之分。

 

3.2 Innodb存储引擎

Innodb主键索引为聚集索引,首先简单理解一下聚集索引的概念:数据库表行中数据的物理顺序和键值的逻辑顺序相同。

Innodb以主键索引来聚集组织数据的存储,下面看看Innodb是如何组织数据的。在这里插入图片描述

 

如上图中,叶子节点的数据区保存的就是真实的数据,在通过索引进行检索的时候,命中叶子节点,就可以直接从叶子节点中取出行数据。mysql5.5版本之前默认采用的是MyISAM引擎,5.5之后默认采用的是innodb引擎。

在innodb中,辅助索引的格式如下图所示?在这里插入图片描述

 

如上图,主键索引的叶子节点保存的是真正的数据。而辅助索引叶子节点的数据区保存的是主键索引关键字的值。

假如要查询name = C 的数据,其搜索过程如下:

先在辅助索引中通过C查询最后找到主键id = 9.
在主键索引中搜索id为9的数据,最终在主键索引的叶子节点中获取到真正的数据。
所以通过辅助索引进行检索,需要检索两次索引。

之所以这样设计,一个原因就是:如果和MyISAM一样在主键索引和辅助索引的叶子节点中都存放数据行指针,一旦数据发生迁移,则需要去重新组织维护所有的索引。

把Innodb 和 MYISAM区别放在一张图中看,就如下所示:在这里插入图片描述

4 创建索引的几大原则

4.1 列的离散型

离散型的计算公式:count(distinct column_name):count(*),就是用去重后的列值个数比个数。值在 (0,1] 范围内。离散型越高,选择型越好。

如下表中各个字段,明显能看出Id的选择性比gender更高。

mysql> select * from user;
+—-+————–+——+——–+
| id | name | age | gender |
+—-+————–+——+——–+
| 20 | 君莫笑 | 15 | 1 |
| 40 | 苏沐橙 | 12 | 0 |
| 50 | 张楚岚 | 25 | 1 |
| 60 | 诸葛青 | 27 | 1 |
| 61 | 若有人兮 | 38 | 0 |
| 64 | 冯宝宝 | 18 | 0 |
+—-+————–+——+——–+

为什么说离散型越高,选择型越好?
因为离散度越高,通过索引最终确定的范围越小,最终扫面的行数也就越少。

4.2 最左匹配原则

对于索引中的关键字进行对比的时候,一定是从左往右以此对比,且不可跳过。之前讲解的id都为int型数据,如果id为字符串的时候,如下图:
在这里插入图片描述当进行匹配的时候,会把字符串转换成ascll码,如abc变成97 98 99,然后从左往右一个字符一个字符进行对比。所以在sql查询中使用like %a 时候索引会失效,因为%表示全匹配,如果已经全匹配就不需要索引,还不如直接全表扫描。

4.3 最少空间原则

前面已经说过,当关键字占用的空间越小,则每个节点保存的关键字个数就越多,每次加载进内存的关键字个数就越多,检索效率就越高。创建索引的关键字要尽可能占用空间小。

补充:

创建索引的原则(重中之重)
索引虽好,但也不是无限制的使用,最好符合一下几个原则

1) 最左前缀匹配原则,组合索引非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

2)较频繁作为查询条件的字段才去创建索引

3)更新频繁字段不适合创建索引

4)若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)

5)尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

6)定义有外键的数据列一定要建立索引。

7)对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

8)对于定义为text、image和bit的数据类型的列不要建立索引.

 

5 联合索引

单列索引:节点中的关键字[name]
联合索引:节点中的关键字[name, age]

可以把单列索引看成特殊的联合索引,联合索引的比较也是根据最左匹配原则。

 

5.1 联合索引列的选择原则

经常用的列优先(最左匹配原则)
离散度高的列优先(离散度高原则)
宽度小的列优先(最少空间原则)
5.2 实例分析
下面简单举例平时经常会遇到的问题:
如,平时经常使用的查询sql如下:
select * from users where name = ?
select * from users where name = ? and age = ?

为了加快检索速度,为上面的查询sql创建索引如下:
create index idx_name on users(name)
create index idx_name_age on users(name, age)

在上面解决方案中,根据最左匹配原则,idx_name为冗余索引, where name = ?同样可以利用索引idx_name_age进行检索。冗余索引会增加维护B+TREE平衡时的性能消耗,并且占用磁盘空间。

 

6. 覆盖索引

如果查询的列,通过索引项的信息可直接返回,则该索引称之为查询SQL的覆盖索引。覆盖索引可以提高查询的效率。在这里插入图片描述

 

如上图,如果通过name进行数据检索:
select * from users where name = ?
需要需要在name索引中找到name对应的Id,然后通过获取的Id在主键索引中查到对应的行。整个过程需要扫描两次索引,一次name,一次id。

如果我们查询只想查询id的值,就可以改写SQL为:
select id from users where name = ?
因为只需要id的值,通过name查询的时候,扫描完name索引,我们就能够获得id的值了,所以就不需要再去扫面id索引,就会直接返回。

当然,如果你同时需要获取age的值:
select id,age from users where name = ?
这样就无法使用到覆盖索引了。

知道了覆盖索引,就知道了为什么sql中要求尽量不要使用select *,要写明具体要查询的字段。其中一个原因就是在使用到覆盖索引的情况下,不需要进入到数据区,数据就能直接返回,提升了查询效率。在用不到覆盖索引的情况下,也尽可能的不要使用select *,如果行数据量特别多的情况下,可以减少数据的网络传输量。当然,这都视具体情况而定,通过select返回所有的字段,通用性会更强,一切有利必有弊。

 

创建索引时需要注意什么?

非空字段:应该指定列为NOT NULL,除非你想存储NULL。在mysql中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值;
取值离散大的字段:(变量各个取值之间的差异程度)的列放到联合索引的前面,可以通过count()函数查看字段的差异值,返回值越大说明字段的唯一值越多字段的离散程度高;
索引字段越小越好:数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

 

使用索引查询一定能提高查询的性能吗?为什么

通常,通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O。 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
基于非唯一性索引的检索

 

百万级别或以上的数据如何删除

关于索引:由于索引需要额外的维护成本,因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以,在我们删除数据库百万级别数据的时候,查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

所以我们想要删除百万数据的时候可以先删除索引(此时大概耗时三分多钟)
然后删除其中无用数据(此过程需要不到两分钟)
删除完成后重新创建索引(此时数据较少了)创建索引也非常快,约十分钟左右。
与之前的直接删除绝对是要快速很多,更别说万一删除中断,一切删除会回滚。那更是坑了。

使用B树的好处

B树可以在内部节点同时存储键和值,因此,把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

 

使用B+树的好处

由于B+树的内部节点只存放键,不存放值,因此,一次读取,可以在内存页中获取更多的键,有利于更快地缩小查找范围。 B+树的叶节点由一条链相连,因此,当需要进行一次全数据遍历的时候,B+树只需要使用O(logN)时间找到最小的一个节点,然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历,这会需要更多的内存置换次数,因此也就需要花费更多的时间

 

Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理:

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据。

那么可以看出他们有以下的不同:

hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询。
因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围。

hash索引不支持使用索引进行排序,原理同上。
hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询。
hash索引虽然在等值查询上较快,但是不稳定。性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差。而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低。
因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

 

数据库为什么使用B+树而不是B树

B树只适合随机检索,而B+树同时支持随机检索和顺序检索;
B+树空间利用率更高,可减少I/O次数,磁盘读写代价更低。一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针,只是作为索引使用,其内部结点比B树小,盘块能容纳的结点中关键字数量更多,一次性读入内存中可以查找的关键字也就越多,相对的,IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素;
B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束,越靠近根节点的记录查找时间越短,只要找到关键字即可确定记录的存在,其性能等价于在关键字全集内做一次二分查找。而在B+树中,顺序检索比较明显,随机检索时,任何关键字的查找都必须走一条从根节点到叶节点的路,所有关键字的查找路径长度相同,导致每一个关键字的查询效率相当。
B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起,只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而B树不支持这样的操作。
增删文件(节点)时,效率更高。因为B+树的叶子节点包含所有关键字,并以有序的链表结构存储,这样可很好提高增删效率。
B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据,
在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引。 在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引。如果没有唯一键,则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询。

 

什么是聚簇索引?何时使用聚簇索引与非聚簇索引

聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据
非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因
澄清一个概念:innodb中,在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值

 

何时使用聚簇索引与非聚簇索引

非聚簇索引一定会回表查询吗?

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询。

举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询。

 

联合索引是什么?为什么需要注意联合索引中的顺序?

MySQL可以使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。

具体原因为:

MySQL使用索引时需要索引有序,假设现在建立了”name,age,school”的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序。

当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推。因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

7 总结

索引列的数据长度满足业务的情况下能少则少。

表中的索引并不是越多越好,冗余或者无用索引会占用磁盘空间并且会影响增删改的效率。

Where 条件中,like 9%, like %9%, like%9,三种方式都用不到索引。后两种方式对于索引是无效的。第一种9%是不确定的,决定于列的离散型,结论上讲可以用到,如果发现离散情况特别差的情况下,查询优化器觉得走索引查询性能更差,还不如全表扫描。

Where条件中IN可以使用索引, NOT IN 无法使用索引。

多用指定查询,只返回自己想要的列,少用select *。

查询条件中使用函数,索引将会失效,这和列的离散性有关,一旦使用到函数,函数具有不确定性。

联合索引中,如果不是按照索引最左列开始查找,无法使用索引。

对联合索引精确匹配最左前列并范围匹配另一列,可以使用到索引。

联合索引中,如果查询有某个列的范围查询,其右边所有的列都无法使用索引。
————————————————

————————————————

版权声明:本文为CSDN博主「ThinkWon」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/ThinkWon/article/details/104778621

版权声明:本文为CSDN博主「是小白哇」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/b_x_p/article/details/86434387

转载

 

SIMILAR ARTICLES

发表评论

发表评论