MySQL索引

如果没有索引，那么表当中的数据就会是以比较零散的状态分布在磁盘当中，查找时候一定要线性地遍历表，才可以查询到对应的数据。如果数据量大的话，会严重影响查找的效率，把磁盘的IO次数提高了很多。这就好像，如果数据库是使用链表来存储数据的话，那么想查询第100W条数据，就需要IO 100W次。因为，每一条数据，都相当于一个节点，每遍历一个节点，就需要IO一次。

此外，索引还会占用一定的内存，也就是说，构造索引，需要额外的硬盘空间来保存。虽然占用了一定的空间，但是可以提升查找效率，也是值得的。

Attention2:索引虽然方便了查询的操作，但是却增加了增删改的复杂程度。因为，增删改操作需要进行索引的重构，但是，由于增删改操作的频率远远低于查询的频率，因此仍然需要采用索引

索引一旦创建好之后，在查询时候，是由数据库的执行引擎进行查询的。执行引擎会自动评估哪种方案是速度最低，查询最快的，不需要在查询的时候手动操作索引。

Q2:索引有哪些类型？：

一、按照数据结构进行分类：

①B+树索引：这种数据结构的索引，其更适合做索引的存储结构。非叶子节点上仅仅存储键的值，不存储数据；叶子节点保存所有的键的值以及每一行的数据，且叶子节点全部首尾相连。

可以有效避免B树有可能数值大小相近但是数据不相邻的情况。

②Hash索引：此索引是基于哈希算法实现的，具体的实现，应当参考HashMap的源码。如果基于哈希表存储，其存储的效率是比较高的，但是哈希索引不适合用于大量的比较操作，并且有可能由于存储数据过多而造成扩容，造成空间浪费。

③全文索引：对于文本类型的大对象，需要设置为全文索引。如果需要匹配好几个单词，那么就需要使用好多个 like%...%这样的方式来存储。响应时间会大大增加。因此，如果使用了全文索引，那么他会有自己独特的查询语法。

二、按照物理存储分类：

①聚簇索引：按照每张表的主键构成一棵B+树，非叶子节点存放键的值，叶子节点存放对应的一整行的数据。如果没有主键，表的第一个非空的唯一索引将被设定为聚簇索引。如果二者都没有，那么InnoDB会将自动生成一个隐士的自增id列，并在此列上面建立聚簇索引。

②非聚集索引（二级索引、辅助索引）

其数据结构也是B+树的构成。区别在于，区别在于其叶子节点存放的不是行数据而是数据的主键或者聚簇索引。因此，如果使用聚簇索引的话，有可能造成回表的操作，后面会解释。

三、按照字段特征分类：

①主键索引：一个表的主键构成的索引，不可以存在空值

②唯一索引：可以为空但是此列不允许有重复的元素

③普通索引：建立在普通的字段上面的索引；

④全文索引：在前面提到过。

Q3:索引在哪些条件下不适合使用？

首先引入一个场景，就是，当一个字典只有几页的时候，这个时候，直接翻页查找，可能比建立一个目录，然后在目录当中一页一页等级每个重要信息的页码要来的迅速一些。

①那么在MySql当中，如果当一个表存储的数据比较少的时候，就可以不使用索引，因为索引的创建也是需要很大的内存开销的。

②如果这一列在where条件条件查询，或者(group by)分组查询当中出现的比较少，那么也不适合建立为索引。

③如果字段不是趋近于唯一的字段，是重复性比较高的字段，也不适合作为索引列；例如学生的性别；反之，如果是一些区分度比较大，重复率比较低的字段，就可以考虑建立索引。例如学生的学号，学生的姓名。

②查看，创建，删除索引

一、查看索引：

show index from student

如果查询一个带有主键的表：

如果表当中有主键，主键这一列就会自动创建索引。同时也可以得到主键一个非常大的作用，那就是提升查询的效率

此外，foriegn key,unique两种约束也可以产生索引。

二、创建索引：

如果此时想给某个表新增一列索引,应当使用如下语句：

create + index+索引名称+on+表名称(列名称);

create index index_name on student(name)

这一句SQL的含义就是，给student这个表的name这一列设置为索引，这样，如果想根据name来查询student表当中的行的时候，就变成了根据索引来查询，提升了查询的效率。因此，索引最好设置为重复次数比较少的列的。同时，一个表当中可以有多个索引。

一个表的索引最好是在创建表的时候就把索引创建好，如果当表中存放了很多的数据的时候，再新建索引，这样会增加很大的开销，就好像为一本已经编写好但是没有目录的书新建目录一样。

三、删除索引：

drop + index+索引名称+ on+表名称。

drop index index_name on  student

删除索引，也需要谨慎使用。一般情况下面，表的索引创建/删除都是在还没有往表当中填入数据时候。

③索引底层的数据结构：B+树

在聊B+树的时候，先看一下比较熟悉的二叉搜索树：二叉搜索树，搜索的效率为O(n)，当元素增加的比较多的时候，树的高度就比较高

树的高度，决定了查询的时候元素的比较次数。但是由于每次比较都需要读取硬盘的，当数量上升到一定的高度的时候，比较的次数也就会非常多，因此不采用二叉树。

因此，就引入了N叉树，随着分支的增多，查找的高度就会降低，因此有了一种数据结构：B树。

简单认识一下B树~

如图所示：根节点上面有30，40，50，60一共4个元素，分别在<30,30~40之间，40~50之间，50~60之间，以及>60的5个区间，划分出来这四个区域，每个区域再次延申出一条跟，连接一个新的节点，里面也有若干个元素，但是个数不确定。如果再往下层走，就是在<15,15~20，20~25，25~32等等的区间内延申节点。

这样一棵树，可以看到，存储相同多的节点，高度比二叉树搜索树少了许多。由于每个节点都是存储在硬盘当中的，由于节点个数减少了，也就意味着读写硬盘的次数减少了许多（并不一定是减少了比较的次数）。

认识B+树：（为索引量身定制的数据结构）

如图所示，根节点为存放数据8，15 的节点，在小于8的地方存放至少一个元素8，同时每个节点不连接>本节点最大数值的树杈，这一点与B树不太一样。

B+树的特点：

①父元素的key会在子元素当中出现，并且是以最大值的姿态出现的，这样的重复出现，导致叶子节点就包含了全部的数据：

②叶子节点以链表的方式首尾相连

(id为主键）

如果此时想查询一条sql语句：select*from table where id>3&&id<10;

那么执行的顺序就是这样的：首先获取到根节点，由于3<8,因此来到<8的区间，发现最左边为3，因此继续往左下走，走到叶子节点（包含元素2，3）处，找到了3，由于所有的叶子节点以链表的方式相连，因此找到3~10之间的元素，就可以了。需要注意的是，所有的读取一整行的数据，都是落在叶子节点上面的

B树和B+树当中，为什么采用的是B+树，而不是B树？

1）B树的每个结点都存储了key和data，B+树的data存储在叶子节点上。

节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。

2）树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录

由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻。

3)B+树的查询更加均匀，由于所有的数据都是分布在叶子节点上面的，因此查询的效率永远是O(logN)。类比B树，如果一个数据离根节点更加靠近，那么查询的效率越高。反之，越低。

数据库当中节点存储的模型:

（1）非叶子节点，仅仅存放id，不存放其他数据;

（2）在刚才的叶子节点当中，往下画的那些框，就是对应存储的某一列的全部数据;

(3)带有主键索引的表，存储数据的时候，就是按照主键索引构成的B+树来存储的。那会引入一个新的问题：有的表，如果既有主键索引构成的列，又有非主键索引构成的列，那么这样又是怎样查询的呢?

回表操作：

这个情况，会构造出来另外一棵B+树，新的B+树当中的非叶子节点，里面存放的都是这一列里面的非主键索引。

这种情况，需要先查询一遍索引列的B+树，（再查一遍主键列的B+树）

其中，括号内的

部分，操作称为“回表”

下面，将重点说明一下回表的操作：

如图所示，下面是一张普通的表：

他的结构为：staffId为主键，name为普通的索引，age为一个普通的没有任何约束的键，depart_Id为外键。

如果此时执行sql语句：

select*from  staff where staffId=3

那么会根据查询的条件staffId=3来查询到对应的某一行数据，这个查询，是通过索引来确定的，因为staffId就是主键。

但是，如果我改成根据name来查询呢？

select*from  staff where name='小李';

这个时候，由于name是索引列，那么首先会根据name构成的B+树定位到主键ID为3的成员，再根据主键构成的B+树，来完成查找，最后返回在主键索引构成的B+树的叶子节点上面的一整行数据。因为单纯根据name查询无法查询到一整行的数据，只能查询到name所对应的主键索引。所以需要用主键索引定位到主键索引所在的那一棵B+树，去他的叶子节点上面查询。

以上黑体字的部分操作，称之为回表。

④聚簇索引，二级索引

--转载自CSDN博主「c&0xff00」

聚簇索引和二级索引
聚簇索引：
常叫主键索引，聚簇索引的叶子节点对应的就是实际的一行数据，由于数据在物理上是一份，所以聚簇索引只能有一个。聚簇索引的索引键就是数据表的主键，如果没有主键会选择一个没有null值的唯一列。
二级索引：
就是常说的普通索引、非聚簇索引，它是与聚簇索引配合使用的，它的B+树的叶子节点存放的是对应数据的主键或者其他联合索引的索引列。查找的时候先获取数据的主键，再根据主键获取实际的数据（这个过程就是回表）。

回到上面的操作当中，

select*from  staff where name='小李';

这种情况下面，由于name这一列构成的索引存放的是主键的叶子节点，因此，执行的顺序为:

第一步:根据name构成的B+树查询到name为‘小李'这一列的主键，此时主键为staffId=3。

第二步:因为我需要查询一整行的数据，但是查询的索引列当中并没有一整行的数据，接下来，就会根据主键构成的B+树去查询到对应的叶子节点上面的一整行的数据，这也就触发的"回表"。

避免回表

①联合索引

把一列或者多列组建起来，组合成为一个新的索引，那么这个新的索引就被称为联合索引。

下面是建联合索引的SQL语句：

create unique index uniqueIndex on staff(name,age)

②索引覆盖

select 的数据列只用从索引列当中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于 InnoDB 表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

此时，如果执行下面的sql语句：

select staffId,name,age from staff where name='小李'

此时，就不会触发回表操作了。非聚集索引（这里的组合索引）存放的就是索引列+主键索引。

虽然主键索引不在索引列。

③联合索引的结构是什么样的

假设一张表有如下的结构(id:主键索引),(name,age,postion)联合索引，hobby(非索引列)

id（主键)	(name	age	position)	hobbby

那么，在根据name,age,postion三个字段建立索引的时候：

第一步：首先根据name这一列排序，然后建立索引(B+树);

第二步：如果name这一列的值一样，那么就根据age这一列排序。

第三步：如果age一样，那么根据position字段进行排序

所以，可以从上图得到：

联合索引也是属于非聚簇索引，叶子节点存放的是组合索引列的值和每一行对应的簇簇索引

⑤explain关键字有哪些关键字段(6个)

字段	说明
possible_keys	可能用到的索引
key	字段实际用到的索引
key_len	索引的长度
rows	扫描数据的行数
type	数据扫描类型（下面会提到

对于type,这是一个比较关键的字段。以下，将按照效率从低到高的顺序排序

对于一张表，假如存在一组联合索引(a,b,c)，一个普通字段name，还有一个聚簇索引字段id

一张表:

id(聚簇索引)

c联合索引)

name(非索引字段)

对于这个表的一些查询

序号	type这一列的值	说明
1	All	情况一: 没用到索引，全表扫描 selectfrom table where b=3; 情况二：用到了索引，但是触发了回表操作:* select a,b,c,name from table where a>6; (没有命中最左边的索引，同时又需要查询一整行的数据，因此就是全表扫描)
2	index	全索引扫描： select b,c from car where b>2;/select b from car where b>2;select b,c from car where b=2; 这三种情况，都没有遵循最左匹配原则，但是都仅仅只是查询自己的索引字段，因此就是全索引扫描
3	range	索引范围扫描注意:range和index的区别在于： range是在索引的范围内进行遍历，确实是使用了索引，并且没有触发回表操作例如:select*from table where id>3；//根据id查询字段一般情况下，在走索引的时候，使用诸如>、<、in 、between ，这种都属于范围查询。
4	ref	非唯一索引扫描
5	eq_ref	唯一索引扫描(联表查询时候扫描带有主键/唯一索引的表)
6	const	结果只有一条的主键或唯一索引扫描：id为主键，并且这一行记录存在 select *from user where userId=1

⑤最左前缀原则

(1)最左前缀原则的定义：

最左前缀原则，一定是基于组合索引的。顾名思义，最左优先。如果查询命中了最左边的一列索引，查询才会走组合索引。否则，将会导致全表扫描。

举个例子：

此时在一张table表中，存在组合索引列(a,b,c);

分别有以下三种条件查询：

①select a,b,c from table where a='...';//会根据组合索引查询


②select a,b,c from table where b='...' and c='...';//不会，因为没有命中最左边的字段


③select a,b,c from table where a='...' and c='...';//不会，因为这属于索引截断，必须要
//从a到b再到c这样的顺序

在①，因为查询的列数都位于组合索引(a,b,c)当中，因此，这个种场景就称为索引覆盖。

(2)联合索引停止匹配的情况:

从最左边的索引列开始，当遇到范围查询，例如(>,<,between,like)的时候，就会停止匹配接下来的条件，放弃索引，这种情况也称为范围查询右失效。

举个例子:

还是组合索引列(a,b,c)，有如下的查询语句：

①select a,b,c from table where a=2 and b>3;
//因为命中了索引，因此继续往右走，当遇到>3
//的时候，仍然会进行匹配；


②select a,b,c from table where a=2 and b>3 and c=4;
//这个时候，查询a,b字段的时候会走索引，但是由于b是范围
//查询，因此接下来的字段无法命中索引，导致查询的字段c无法通过索引查询
//因此这种情况会触发全表扫描

(3)来几道经典的面试题~~~~

题目一:

如果一个sql语句为：

select a,b form table where a>1 and b=2;

那么这个时候如何建立索引?

答案是根据(b,a)建立组合索引；因为如果根据(a,b)建立组合索引的话，那么组合索引遇到范围查询就会停止匹配，这样导致b字段无法命中索引，从而失效，导致全表扫描。

如果根据(b,a)建立索引，哪怕a在前,b在后，sql执行的时候也会把执行的顺序优化为先匹配b再匹配a。

题目二：

如果一个sql语句为:

select a,b,c from table where a>1 and b=2 and c>1;

那么此时应该如何建立索引?

因为出现了相等的条件判断，为了命中这个条件，因此可以建立(b,a)或者(b,c)为联合索引。但是如果建立了(b,a,c），这样就会导致匹配遇到a>1的时候，停止匹配。

题目三:

如果一个sql语句为:

select a,b,c from table where a=1 and b>2 order by c

那么，这个时候如何建立索引呢？当出现order by的时候，这是涉及排序的情况。排序是比较耗时间与空间的，因此，要让排序的范围最小。所以，此时应该按照(a,b)建立索引列，让(a,b)建立联合索引，此时范围已经缩小到a=1且b>2的情况了。这个时候，再根据c来排序，会使得待排序的列数达到最小。并且又有组合索引(a,b)来提升查询的效率，因此，按照(a,b)来建立组合索引，查询的效率是最高的。

(4)索引下推

回到在(1)当中的第三个例子当中:(a,b,c)为组合索引

此时一条查询语句为:

select*from table where a=1 and b>2 and c>3

此时where子句当中的字段为a,b,c。此时，可以看到a字段命中了索引，根据最左匹配原则，b字段也可以根据索引来查询，但是c字段无法根据索引查询。

A.mysql5.5以及之前

当二级索引根据a,b字段查询到对应的结果集之后，就触发了回表

B.mysql 5.6往后

可以在联合索引遍历过程中，对联合索引中包含的所有字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，也就是检索完a,b,c字段，再去回表

因此，索引下推就是为了减少回表次数的一种操作。

当使用explain查看的时候，会再extra那一栏看到Using index condition；

⑥索引的失效场景

(1)对索引使用左或者左右模糊匹配

比如对某一列建立的索引，这一列是"name"，查询时候，使用了select name from table where name like '%小明';或者select name from table where name like '%小明%'；这样的查询，会导致根据name 字段查询的索引失效;会导致全表扫描。

但是，如果查询语句为select name from table where name like '小明%'，或者select name from table where name like '小%明'就不会全表扫描。

总结：只要模糊查询的'%'不出现在字符串首，那么就可以根据索引查询。

(2)对索引使用函数

比如select name form table where length(name)=6;此时针对索引列name 使用了函数；

因为在MYSQL8之前，仅仅只会根据索引列建立B+树，不会根据索引列对应的函数构建B+树；在MYSQL8之后，新增了一个特性，就是可以针对索引列通过函数计算出的值再建立一个索引，这样，即使针对索引列使用了函数，也仍然会走索引。

(3)对索引列使用表达式计算

例如id字段为索引列，执行查询语句select*from table where id+1=10;这个时候会走全表扫描；

原因：B+树的节点上面仅仅保存的是id的值，并不是id+1之后的值，因此，这样会无法命中索引。

如果真想用上表达式，不妨将上面的sql语句修改为:select *from table where id=10-1;

(4)联合索引非左匹配

这一点在上面提到过

(5)where子句当中使用了or，但是对应的or之前或者之后的字段不在索引列当中

select * from user where id = 1 or age = 18;

此时，假设id为索引列，但是age是非索引列；这个时候，由于查询的or后面的字段不在索引列当中，因此此时会走全表扫描；如果想要走索引，必须要把id和age设为一个组合索引(id,age);

(6)SQL优化的层面：

如果在sql语句被编译器认定为，全表扫描可能比走索引查询效率更高，那么就会在查询阶段不使用索引查询，走全表扫描。例如把一些重复率比较高的列设置为索引列，然后再把这些列作为where子句当中的条件查询的时候，就有可能会导致索引失效。

以上文章部分来源于《小林coding》

革凡成圣–wjx

关注

3
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
MySQL索引

MySql，数据库，索引，B+树
复制链接

扫一扫