前言

咱们好，我是田螺。

金三银四很快就要来啦，预备了索引的15连问，相信咱们看完必定会有协助的。

MySQL索引15连问，抗住！

  • 公众号：捡田螺的小男孩
  • github地址，感谢每颗star：github

1. 索引是什么？

MySQL索引15连问，抗住！

  • 索引是一种能进步数据库查询功率的数据结构。它能够比作一本字典的目录，能够帮你快速找到对应的记载。
  • 索引一般存储在磁盘的文件中，它是占用物理空间的。
  • 正所谓水能载舟，也能覆舟。适当的索引能进步查询功率，过多的索引会影响数据库表的刺进和更新功用。

2. MySQL索引有哪些类型

MySQL索引15连问，抗住！

数据结构维度

  • B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，合适范围查询。
  • 哈希索引: 合适等值查询，检索功率高，一次到位。
  • 全文索引：MyISAM和InnoDB中都支撑运用全文索引，一般在文本类型char,text,varchar类型上创立。
  • R-Tree索引: 用来对GIS数据类型创立SPATIAL索引

物理存储维度

  • 集合索引：集合索引便是以主键创立的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
  • 非集合索引：非集合索引便是以非主键创立的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）

逻辑维度

  • 主键索引：一种特别的仅有索引，不答应有空值。
  • 一般索引：MySQL中根本索引类型，答应空值和重复值。
  • 联合索引：多个字段创立的索引，运用时遵循最左前缀准则。
  • 仅有索引：索引列中的值必须是仅有的，可是答应为空值。
  • 空间索引：MySQL5.7之后支撑空间索引，在空间索引这方面遵循OpenGIS几何数据模型规矩。

3. 索引什么时分会失效？

  • 查询条件包括or，或许导致索引失效
  • 假如字段类型是字符串，where时必定用引号括起来，不然索引失效
  • like通配符或许导致索引失效。
  • 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
  • 在索引列上运用 mysql 的内置函数，索引失效。
  • 对索引列运算（如，+、-、*、/），索引失效。
  • 索引字段上运用（！= 或许 < >，not in）时，或许会导致索引失效。
  • 索引字段上运用is null， is not null，或许导致索引失效。
  • 左连接查询或许右连接查询查询相关的字段编码格局不一样，或许导致索引失效。
  • mysql 估计运用全表扫描要比运用索引快,则不运用索引。

4. 哪些场景不合适树立索引？

  • 数据量少的表，不合适加索引
  • 更新比较频繁的也不合适加索引
  • 区分度低的字段不合适加索引（如性别）
  • where、group by、order by等后面没有运用到的字段，不需求树立索引
  • 已经有冗余的索引的情况（比方已经有a,b的联合索引，不需求再独自树立a索引）

5. 为什么要用 B+树，为什么不用二叉树？

能够从几个维度去看这个问题，查询是否够快，功率是否安稳，存储数据多少， 以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是 B 树，而偏偏是 B+树呢？

为什么不是一般二叉树？

假如二叉树特别化为一个链表，相当于全表扫描。平衡二叉树比较于二叉查找 树来说，查找功率更安稳，整体的查找速度也更快。

为什么不是平衡二叉树呢？

咱们知道，在内存比在磁盘的数据，查询功率快得多。假如树这种数据结构作 为索引，那咱们每查找一次数据就需求从磁盘中读取一个节点，也便是咱们说 的一个磁盘块，可是平衡二叉树可是每个节点只存储一个键值和数据的，假如 是 B 树，能够存储更多的节点数据，树的高度也会下降，因而读取磁盘的次数 就降下来啦，查询功率就快啦。

那为什么不是 B 树而是 B+树呢？

  • B+树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储 键值，也会存储数据。innodb 中页的默认大小是 16KB，假如不存储数据，那 么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就 会更矮更胖，如此一来咱们查找数据进行磁盘的 IO 次数有会再次削减，数据查 询的功率也会更快。
  • B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链 表连着的。那么 B+树使得范围查找，排序查找，分组查找以及去重查找变得 异常简略。

6. 一次B+树索引树查找进程

假设有以下表结构，并且初始化了这几条数据

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

履行这条查询SQL，需求履行几回的树查找操作？能够画下对应的索引树结构图~

select * from Temployee where age=32;

其实这个，这个咱们能够先画出idx_age一般索引的索引结构图，大概如下：

MySQL索引15连问，抗住！

再画出id主键索引，咱们先画出聚族索引结构图，如下：

MySQL索引15连问，抗住！

这条 SQL 查询句子履行大概流程是这样的：

  • 查找idx_age 索引树，将磁盘块1加载到内存，由于32<43,查找左路分支，到磁盘寻址磁盘块2。
  • 将磁盘块2加载到内存中，由于32<36,查找左路分支，到磁盘寻址磁盘块4。
  • 将磁盘块4加载到内存中，在内存继续遍历，找到age=32的记载，取得id = 400.
  • 拿到id=400后，回到id主键索引树。
  • 查找id主键索引树，将磁盘块1加载到内存，由于300<400<500,所以在选择中心分支，到磁盘寻址磁盘块3。
  • 虽然在磁盘块3，找到了id=400，可是它不是叶子节点，所以会继续往下找。 到磁盘寻址磁盘块8。
  • 将磁盘块8加载内存，在内存遍历，找到id=400的记载，拿到R4这一行的数据，好的，大功告成。

7. 什么是回表？如何削减回表？

当查询的数据在索引树中，找不到的时分，需求回到主键索引树中去获取，这个进程叫做回表。

比方在第6末节中，运用的查询SQL

select * from employee where age=32;

需求查询所有列的数据，idx_age一般索引不能满意，需求拿到主键id的值后，再回到id主键索引查找获取，这个进程便是回表。

8. 什么是掩盖索引？

假如咱们查询SQL的select * 修改为 select id, age的话，其实是不需求回表的。由于id和age的值，都在idx_age索引树的叶子节点上，这就涉及到掩盖索引的仅仅点了。

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

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

索引的最左前缀准则，可所以联合索引的最左N个字段。比方你树立一个组合索引（a,b,c），其实能够相当于建了（a），（a,b）,(a,b,c)三个索引，大大进步了索引复用能力。

当然，最左前缀也可所以字符串索引的最左M个字符。。 比方，你的一般索引树是酱紫：

MySQL索引15连问，抗住！

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

MySQL索引15连问，抗住！

10. 索引下推了解过吗？什么事索引下推

给你这个SQL：

select * from employee where name like '小%' and age=28 and sex='0';

其中，name和age为联合索引（idx_name_age）。

假如是Mysql5.6之前，在idx_name_age索引树，找出所有姓名第一个字是“小”的人，拿到它们的主键id，然后回表找出数据行，再去比照年纪和性别等其他字段。如图：

MySQL索引15连问，抗住！

有些朋友或许觉得古怪，idx_name_age（name,age)不是联合索引嘛？为什么选出包括“小”字后，不再趁便看下年纪age再回表呢，不是更高效嘛？所以呀，MySQL 5.6就引入了索引下推优化，能够在索引遍历进程中，对索引中包括的字段先做判别，直接过滤掉不满意条件的记载，削减回表次数。

因而，MySQL5.6版本之后，选出包括“小”字后，顺表过滤age=28

MySQL索引15连问，抗住！

11. 大表如何增加索引

假如一张表数据量级是千万等级以上的，那么，如何给这张表增加索引？

咱们需求知道一点，给表增加索引的时分，是会对表加锁的。假如不谨慎操作，有或许呈现生产事故的。能够参阅以下办法：

  1. 先创立一张跟原表A数据结构相同的新表B。
  2. 在新表B增加需求加上的新索引。
  3. 把原表A数据导到新表B
  4. rename新表B为原表的表名A，原表A换别的表名；

12. 如何知道句子是否走索引查询？

explain检查SQL的履行计划，这样就知道是否射中索引了。

当explain与SQL一同运用时，MySQL将显示来自优化器的有关句子履行计划的信息。

MySQL索引15连问，抗住！

一般来说，咱们需求要点重视type、rows、filtered、extra、key。

1.2.1 type

type表明连接类型，检查索引履行情况的一个重要指标。以下性能从好到坏顺次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system：这种类型要求数据库表中只要一条数据，是const类型的一个特例，一般情况下是不会呈现的。
  • const：通过一次索引就能找到数据，一般用于主键或仅有索引作为条件，这类扫描功率极高，，速度非常快。
  • eq_ref：常用于主键或仅有索引扫描，一般指运用主键的相关查询
  • ref : 常用于非主键和仅有索引扫描。
  • ref_or_null：这种连接类型类似于ref，差异在于MySQL会额外查找包括NULL值的行
  • index_merge：运用了索引兼并优化办法，查询运用了两个以上的索引。
  • unique_subquery：类似于eq_ref，条件用了in子查询
  • index_subquery：差异于unique_subquery，用于非仅有索引，能够回来重复值。
  • range：常用于范围查询，比方：between … and 或 In 等操作
  • index：全索引扫描
  • ALL：全表扫描

1.2.2 rows

该列表明MySQL预算要找到咱们所需的记载，需求读取的行数。对于InnoDB表，此数字是估计值，并非必定是个精确值。

1.2.3 filtered

该列是一个百分比的值，表里符合条件的记载数的百分比。简略点说，这个字段表明存储引擎回来的数据在经过过滤后，剩下满意条件的记载数量的份额。

1.2.4 extra

该字段包括有关MySQL如何解析查询的其他信息，它一般会呈现这几个值：

  • Using filesort：表明按文件排序，一般是在指定的排序和索引排序不一致的情况才会呈现。一般见于order by句子
  • Using index ：表明是否用了掩盖索引。
  • Using temporary: 表明是否运用了临时表,性能特别差，需求要点优化。一般多见于group by句子，或许union句子。
  • Using where : 表明运用了where条件过滤.
  • Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，运用索引现有的数据削减回表的数据。

1.2.5 key

该列表明实践用到的索引。一般合作possible_keys列一同看。

13.Hash 索引和 B+树差异是什么？你在设计索引是怎么选择的？

  • B+树能够进行范围查询，Hash 索引不能。
  • B+树支撑联合索引的最左边准则，Hash 索引不支撑。
  • B+树支撑 order by 排序，Hash 索引不支撑。
  • Hash 索引在等值查询上比 B+树功率更高。（可是索引列的重复值很多的话，Hash冲突，功率下降）。
  • B+树运用 like 进行含糊查询的时分，like 后面（比方%最初）的话能够起到优化的效果，Hash 索引根本无法进行含糊查询。

14. 索引有哪些优缺陷？

优点：

  • 索引能够加速数据查询速度，削减查询时刻
  • 仅有索引能够确保数据库表中每一行的数据的仅有性

缺陷：

  • 创立索引和维护索引要耗费时刻
  • 索引需求占物理空间，除了数据表占用数据空间之外，每一个索引还要占用必定的物理空间
  • 以表中的数据进行增、删、改的时分，索引也要动态的维护。

15. 聚簇索引与非聚簇索引的差异

聚簇索引并不是一种独自的索引类型，而是一种数据存储方法。它表明索引结构和数据一同寄存的索引。非集合索引是索引结构和数据分隔寄存的索引。

接下来，咱们分不同存存储引擎去聊哈~

在MySQL的InnoDB存储引擎中， 聚簇索引与非聚簇索引最大的差异，在于叶节点是否寄存一整行记载。聚簇索引叶子节点存储了一整行记载，而非聚簇索引叶子节点存储的是主键信息，因而，一般非聚簇索引还需求回表查询。

  • 一个表中只能具有一个集合索引（由于一般聚簇索引便是主键索引），而非集合索引一个表则能够存在多个。
  • 一般来说，相对于非聚簇索引，聚簇索引查询功率更高，由于不用回表。

而在MyISM存储引擎中，它的主键索引，一般索引都是非聚簇索引，由于数据和索引是分隔的，叶子节点都运用一个地址指向真正的表数据。