MySQL索引15连问，抗住！

1. 索引是什么&＃xff1f;

• 索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录&＃xff0c;可以帮你快速找到对应的记录。

• 索引一般存储在磁盘的文件中&＃xff0c;它是占用物理空间的。

• 正所谓水能载舟&＃xff0c;也能覆舟。适当的索引能提高查询效率&＃xff0c;过多的索引会影响数据库表的插入和更新功能。

2. MySQL索引有哪些类型

数据结构维度

• B&＃43;树索引&＃xff1a;所有数据存储在叶子节点&＃xff0c;复杂度为O(logn)&＃xff0c;适合范围查询。

• 哈希索引: 适合等值查询&＃xff0c;检索效率高&＃xff0c;一次到位。

• 全文索引&＃xff1a;MyISAM和InnoDB中都支持使用全文索引&＃xff0c;一般在文本类型char,text,varchar类型上创建。

• R-Tree索引: 用来对GIS数据类型创建SPATIAL索引

物理存储维度

• 聚集索引&＃xff1a;聚集索引就是以主键创建的索引&＃xff0c;在叶子节点存储的是表中的数据。&＃xff08;Innodb存储引擎&＃xff09;

• 非聚集索引&＃xff1a;非聚集索引就是以非主键创建的索引&＃xff0c;在叶子节点存储的是主键和索引列。&＃xff08;Innodb存储引擎&＃xff09;

逻辑维度

• 主键索引&＃xff1a;一种特殊的唯一索引&＃xff0c;不允许有空值。

• 普通索引&＃xff1a;MySQL中基本索引类型&＃xff0c;允许空值和重复值。

• 联合索引&＃xff1a;多个字段创建的索引&＃xff0c;使用时遵循最左前缀原则。

• 唯一索引&＃xff1a;索引列中的值必须是唯一的&＃xff0c;但是允许为空值。

• 空间索引&＃xff1a;MySQL5.7之后支持空间索引&＃xff0c;在空间索引这方面遵循OpenGIS几何数据模型规则。

3. 索引什么时候会失效&＃xff1f;

• 查询条件包含or&＃xff0c;可能导致索引失效

• 如果字段类型是字符串&＃xff0c;where时一定用引号括起来&＃xff0c;否则索引失效

• like通配符可能导致索引失效。

• 联合索引&＃xff0c;查询时的条件列不是联合索引中的第一个列&＃xff0c;索引失效。

• 在索引列上使用 mysql 的内置函数&＃xff0c;索引失效。

• 对索引列运算&＃xff08;如&＃xff0c;&＃43;、-、*、/&＃xff09;&＃xff0c;索引失效。

• 索引字段上使用&＃xff08;&＃xff01;&＃61; 或者 <>&＃xff0c;not in&＃xff09;时&＃xff0c;可能会导致索引失效。

• 索引字段上使用is null&＃xff0c; is not null&＃xff0c;可能导致索引失效。

• 左连接查询或者右连接查询查询关联的字段编码格式不一样&＃xff0c;可能导致索引失效。

• mysql 估计使用全表扫描要比使用索引快,则不使用索引。

4. 哪些场景不适合建立索引&＃xff1f;

• 数据量少的表&＃xff0c;不适合加索引

• 更新比较频繁的也不适合加索引

• 区分度低的字段不适合加索引&＃xff08;如性别&＃xff09;

• where、group by、order by等后面没有使用到的字段&＃xff0c;不需要建立索引

• 已经有冗余的索引的情况&＃xff08;比如已经有a,b的联合索引&＃xff0c;不需要再单独建立a索引&＃xff09;

5. 为什么要用 B&＃43;树&＃xff0c;为什么不用二叉树&＃xff1f;

可以从几个维度去看这个问题&＃xff0c;查询是否够快&＃xff0c;效率是否稳定&＃xff0c;存储数据多少&＃xff0c;以及查找磁盘次数&＃xff0c;为什么不是二叉树&＃xff0c;为什么不是平衡二叉树&＃xff0c;为什么不是B 树&＃xff0c;而偏偏是 B&＃43;树呢&＃xff1f;

为什么不是一般二叉树&＃xff1f;

如果二叉树特殊化为一个链表&＃xff0c;相当于全表扫描。平衡二叉树相比于二叉查找 树来说&＃xff0c;查找效率更稳定&＃xff0c;总体的查找速度也更快。

为什么不是平衡二叉树呢&＃xff1f;

我们知道&＃xff0c;在内存比在磁盘的数据&＃xff0c;查询效率快得多。如果树这种数据结构作为索引&＃xff0c;那我们每查找一次数据就需要从磁盘中读取一个节点&＃xff0c;也就是我们说的一个磁盘块&＃xff0c;但是平衡二叉树可是每个节点只存储一个键值和数据的&＃xff0c;如果是 B 树&＃xff0c;可以存储更多的节点数据&＃xff0c;树的高度也会降低&＃xff0c;因此读取磁盘的次数就降下来啦&＃xff0c;查询效率就快啦。

那为什么不是 B 树而是 B&＃43;树呢&＃xff1f;

• B&＃43;树非叶子节点上是不存储数据的&＃xff0c;仅存储键值&＃xff0c;而 B 树节点中不仅存储键值&＃xff0c;也会存储数据。innodb 中页的默认大小是 16KB&＃xff0c;如果不存储数据&＃xff0c;那 么就会存储更多的键值&＃xff0c;相应的树的阶数&＃xff08;节点的子节点树&＃xff09;就会更大&＃xff0c;树就 会更矮更胖&＃xff0c;如此一来我们查找数据进行磁盘的 IO 次数有会再次减少&＃xff0c;数据查询的效率也会更快。

• B&＃43;树索引的所有数据均存储在叶子节点&＃xff0c;而且数据是按照顺序排列的&＃xff0c;链表连着的。那么 B&＃43;树使得范围查找&＃xff0c;排序查找&＃xff0c;分组查找以及去重查找变得 异常简单。

6. 一次B&＃43;树索引树查找过程

假设有以下表结构&＃xff0c;并且初始化了这几条数据

CREATETABLE &＃96;employee&＃96; (&＃96;id&＃96; int(11) NOTNULL,&＃96;name&＃96; varchar(255) DEFAULT NULL,&＃96;age&＃96; int(11) DEFAULT NULL,&＃96;date&＃96; datetime DEFAULT NULL,&＃96;sex&＃96; int(1) DEFAULT NULL,PRIMARY KEY (&＃96;id&＃96;),KEY &＃96;idx_age&＃96; (&＃96;age&＃96;) USING BTREE
) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8;insert into employee values(100,&＃39;小伦&＃39;,43,&＃39;2021-01-20&＃39;,&＃39;0&＃39;);
insert into employee values(200,&＃39;俊杰&＃39;,48,&＃39;2021-01-21&＃39;,&＃39;0&＃39;);
insert into employee values(300,&＃39;紫琪&＃39;,36,&＃39;2020-01-21&＃39;,&＃39;1&＃39;);
insert into employee values(400,&＃39;立红&＃39;,32,&＃39;2020-01-21&＃39;,&＃39;0&＃39;);
insert into employee values(500,&＃39;易迅&＃39;,37,&＃39;2020-01-21&＃39;,&＃39;1&＃39;);
insert into employee values(600,&＃39;小军&＃39;,49,&＃39;2021-01-21&＃39;,&＃39;0&＃39;);
insert into employee values(700,&＃39;小燕&＃39;,28,&＃39;2021-01-21&＃39;,&＃39;1&＃39;);

执行这条查询SQL&＃xff0c;需要执行几次的树搜索操作&＃xff1f;可以画下对应的索引树结构图~

select * from Temployee where age&＃61;32;


其实这个&＃xff0c;这个大家可以先画出idx_age普通索引的索引结构图&＃xff0c;大概如下&＃xff1a;

再画出id主键索引&＃xff0c;我们先画出聚族索引结构图&＃xff0c;如下&＃xff1a;

这条 SQL 查询语句执行大概流程是这样的&＃xff1a;

• 搜索idx_age 索引树&＃xff0c;将磁盘块1加载到内存&＃xff0c;由于32<43,搜索左路分支&＃xff0c;到磁盘寻址磁盘块2。

• 将磁盘块2加载到内存中&＃xff0c;由于32<36,搜索左路分支&＃xff0c;到磁盘寻址磁盘块4。

• 将磁盘块4加载到内存中&＃xff0c;在内存继续遍历&＃xff0c;找到age&＃61;32的记录&＃xff0c;取得id &＃61; 400.

• 拿到id&＃61;400后&＃xff0c;回到id主键索引树。

• 搜索id主键索引树&＃xff0c;将磁盘块1加载到内存&＃xff0c;因为300<400<500,所以在选择中间分支&＃xff0c;到磁盘寻址磁盘块3。

• 虽然在磁盘块3&＃xff0c;找到了id&＃61;400&＃xff0c;但是它不是叶子节点&＃xff0c;所以会继续往下找。 到磁盘寻址磁盘块8。

• 将磁盘块8加载内存&＃xff0c;在内存遍历&＃xff0c;找到id&＃61;400的记录&＃xff0c;拿到R4这一行的数据&＃xff0c;好的&＃xff0c;大功告成。

7. 什么是回表&＃xff1f;如何减少回表&＃xff1f;

当查询的数据在索引树中&＃xff0c;找不到的时候&＃xff0c;需要回到主键索引树中去获取&＃xff0c;这个过程叫做回表。

比如在第6小节中&＃xff0c;使用的查询SQL

select * from employee where age&＃61;32;

需要查询所有列的数据&＃xff0c;idx_age普通索引不能满足&＃xff0c;需要拿到主键id的值后&＃xff0c;再回到id主键索引查找获取&＃xff0c;这个过程就是回表。

8. 什么是覆盖索引&＃xff1f;

如果我们查询SQL的select * 修改为 select id, age的话&＃xff0c;其实是不需要回表的。因为id和age的值&＃xff0c;都在idx_age索引树的叶子节点上&＃xff0c;这就涉及到覆盖索引的只是点了。

覆盖索引是select的数据列只用从索引中就能够取得&＃xff0c;不必回表&＃xff0c;换句话说&＃xff0c;查询列要被所建的索引覆盖。

9. 聊聊索引的最左前缀原则

索引的最左前缀原则&＃xff0c;可以是联合索引的最左N个字段。比如你建立一个组合索引&＃xff08;a,b,c&＃xff09;&＃xff0c;其实可以相当于建了&＃xff08;a&＃xff09;&＃xff0c;&＃xff08;a,b&＃xff09;,(a,b,c)三个索引&＃xff0c;大大提高了索引复用能力。

当然&＃xff0c;最左前缀也可以是字符串索引的最左M个字符。。 比如&＃xff0c;你的普通索引树是酱紫&＃xff1a;

这个SQL: select * from employee where name like &＃39;小%&＃39; order by age desc; 也是命中索引的。

10. 索引下推了解过吗&＃xff1f;什么事索引下推

给你这个SQL&＃xff1a;

select*from employee where name like&＃39;小%&＃39;and age&＃61;28and sex&＃61;&＃39;0&＃39;;

其中&＃xff0c;name和age为联合索引&＃xff08;idx_name_age&＃xff09;。

如果是Mysql5.6之前&＃xff0c;在idx_name_age索引树&＃xff0c;找出所有名字第一个字是“小”的人&＃xff0c;拿到它们的主键id&＃xff0c;然后回表找出数据行&＃xff0c;再去对比年龄和性别等其他字段。如图&＃xff1a;

有些朋友可能觉得奇怪&＃xff0c;idx_name_age&＃xff08;name,age)不是联合索引嘛&＃xff1f;为什么选出包含“小”字后&＃xff0c;不再顺便看下年龄age再回表呢&＃xff0c;不是更高效嘛&＃xff1f;所以呀&＃xff0c;MySQL 5.6就引入了索引下推优化&＃xff0c;可以在索引遍历过程中&＃xff0c;对索引中包含的字段先做判断&＃xff0c;直接过滤掉不满足条件的记录&＃xff0c;减少回表次数。

因此&＃xff0c;MySQL5.6版本之后&＃xff0c;选出包含“小”字后&＃xff0c;顺表过滤age&＃61;28

11. 大表如何添加索引

如果一张表数据量级是千万级别以上的&＃xff0c;那么&＃xff0c;如何给这张表添加索引&＃xff1f;

我们需要知道一点&＃xff0c;给表添加索引的时候&＃xff0c;是会对表加锁的。如果不谨慎操作&＃xff0c;有可能出现生产事故的。可以参考以下方法&＃xff1a;

1. 先创建一张跟原表A数据结构相同的新表B。

2. 在新表B添加需要加上的新索引。

3. 把原表A数据导到新表B

4. rename新表B为原表的表名A&＃xff0c;原表A换别的表名&＃xff1b;

12. 如何知道语句是否走索引查询&＃xff1f;

explain查看SQL的执行计划&＃xff0c;这样就知道是否命中索引了。

当explain与SQL一起使用时&＃xff0c;MySQL将显示来自优化器的有关语句执行计划的信息。

一般来说&＃xff0c;我们需要重点关注type、rows、filtered、extra、key。

1.2.1 type

type表示连接类型&＃xff0c;查看索引执行情况的一个重要指标。以下性能从好到坏依次&＃xff1a;system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system&＃xff1a;这种类型要求数据库表中只有一条数据&＃xff0c;是const类型的一个特例&＃xff0c;一般情况下是不会出现的。

• const&＃xff1a;通过一次索引就能找到数据&＃xff0c;一般用于主键或唯一索引作为条件&＃xff0c;这类扫描效率极高&＃xff0c;&＃xff0c;速度非常快。

• eq_ref&＃xff1a;常用于主键或唯一索引扫描&＃xff0c;一般指使用主键的关联查询

• ref : 常用于非主键和唯一索引扫描。

• ref_or_null&＃xff1a;这种连接类型类似于ref&＃xff0c;区别在于MySQL会额外搜索包含NULL值的行

• index_merge&＃xff1a;使用了索引合并优化方法&＃xff0c;查询使用了两个以上的索引。

• unique_subquery&＃xff1a;类似于eq_ref&＃xff0c;条件用了in子查询

• index_subquery&＃xff1a;区别于unique_subquery&＃xff0c;用于非唯一索引&＃xff0c;可以返回重复值。

• range&＃xff1a;常用于范围查询&＃xff0c;比如&＃xff1a;between ... and 或 In 等操作

• index&＃xff1a;全索引扫描

• ALL&＃xff1a;全表扫描

1.2.2 rows

该列表示MySQL估算要找到我们所需的记录&＃xff0c;需要读取的行数。对于InnoDB表&＃xff0c;此数字是估计值&＃xff0c;并非一定是个准确值。

1.2.3 filtered

该列是一个百分比的值&＃xff0c;表里符合条件的记录数的百分比。简单点说&＃xff0c;这个字段表示存储引擎返回的数据在经过过滤后&＃xff0c;剩下满足条件的记录数量的比例。

1.2.4 extra

该字段包含有关MySQL如何解析查询的其他信息&＃xff0c;它一般会出现这几个值&＃xff1a;

• Using filesort&＃xff1a;表示按文件排序&＃xff0c;一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句

• Using index &＃xff1a;表示是否用了覆盖索引。

• Using temporary: 表示是否使用了临时表,性能特别差&＃xff0c;需要重点优化。一般多见于group by语句&＃xff0c;或者union语句。

• Using where : 表示使用了where条件过滤.

• Using index condition&＃xff1a;MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤&＃xff0c;而不是在服务层过滤&＃xff0c;利用索引现有的数据减少回表的数据。

1.2.5 key

该列表示实际用到的索引。一般配合possible_keys列一起看。

13.Hash 索引和 B&＃43;树区别是什么&＃xff1f;你在设计索引是怎么抉择的&＃xff1f;

• B&＃43;树可以进行范围查询&＃xff0c;Hash 索引不能。

• B&＃43;树支持联合索引的最左侧原则&＃xff0c;Hash 索引不支持。

• B&＃43;树支持 order by 排序&＃xff0c;Hash 索引不支持。

• Hash 索引在等值查询上比 B&＃43;树效率更高。&＃xff08;但是索引列的重复值很多的话&＃xff0c;Hash冲突&＃xff0c;效率降低&＃xff09;。

• B&＃43;树使用 like 进行模糊查询的时候&＃xff0c;like 后面&＃xff08;比如%开头&＃xff09;的话可以起到优化的作用&＃xff0c;Hash 索引根本无法进行模糊查询。

14. 索引有哪些优缺点&＃xff1f;

优点&＃xff1a;

• 索引可以加快数据查询速度&＃xff0c;减少查询时间

• 唯一索引可以保证数据库表中每一行的数据的唯一性

缺点&＃xff1a;

• 创建索引和维护索引要耗费时间

• 索引需要占物理空间&＃xff0c;除了数据表占用数据空间之外&＃xff0c;每一个索引还要占用一定的物理空间

• 以表中的数据进行增、删、改的时候&＃xff0c;索引也要动态的维护。

15. 聚簇索引与非聚簇索引的区别

聚簇索引并不是一种单独的索引类型&＃xff0c;而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引。

接下来&＃xff0c;我们分不同存存储引擎去聊哈~

在MySQL的InnoDB存储引擎中&＃xff0c; 聚簇索引与非聚簇索引最大的区别&＃xff0c;在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录&＃xff0c;而非聚簇索引叶子节点存储的是主键信息&＃xff0c;因此&＃xff0c;一般非聚簇索引还需要回表查询。

• 一个表中只能拥有一个聚集索引&＃xff08;因为一般聚簇索引就是主键索引&＃xff09;&＃xff0c;而非聚集索引一个表则可以存在多个。

• 一般来说&＃xff0c;相对于非聚簇索引&＃xff0c;聚簇索引查询效率更高&＃xff0c;因为不用回表。

而在MyISM存储引擎中&＃xff0c;它的主键索引&＃xff0c;普通索引都是非聚簇索引&＃xff0c;因为数据和索引是分开的&＃xff0c;叶子节点都使用一个地址指向真正的表数据。