MySQL学习[2] ——MySQL索引

Magic@

于 2024-08-11 16:40:55 发布

阅读量810

点赞数 14

文章标签： mysql 学习数据库 linux 服务器

本文链接：https://blog.csdn.net/shenfenxihuan/article/details/141107604

版权

二、MySQL索引

2.1 什么是索引？

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，索引是数据的目录。利用空间换取时间，能够极大加速数据库的查询效率。

索引和数据一样，位于MySQL的存储引擎层。

2.2 索引的分类

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

2.2.1 按数据结构分类

索引本身是通过一种数据结构来实现的，根据数据结构的不同，可以分为：B+树索引、Hash索引、Full-Text索引等。每种存储引擎支持的索引类型也不同，InnoDB中默认使用B+树作为索引的数据结构。

2.2.2 按物理存储分类

从物理存储的角度，索引分为**聚簇索引（主键索引）、二级索引（辅助索引）**，这两个的主要区别在于：

主键索引的B+树叶子节点中存放的是实际数据，所有的记录都存放在主键索引的B+树叶子节点中；
二级索引的B+树的叶子节点存放的是主键值，需要再通过主键去查找实际数据。

2.2.3 按字段特性分类

从字段特性分类，索引可以分为：主键索引、唯一索引、普通索引、前缀索引。

主键索引：建立在主键上的索引。一张表只能有一个主键索引（逐渐只能有一个），不能重复，不允许空值NULL
唯一索引：UNIQUE字段上建立的索引。一张表可以有多个唯一索引，允许存在空值
普通索引：在普通字段上建立的索引，对字段没什么要求
前缀索引：对字符类型（char、varchar、binary、varbinary）的字段的前几个字符建立的索引，而不是在整个字段上建立的索引

2.2.4 按字段个数分类

从字段个数上来看，索引分为单列索引、联合索引。

单列索引：建立在单个列上的索引，如主键索引；
联合索引：建立在多个列上的索引

2.3 为什么使用B+树作为索引？

2.3.1 B+Tree 索引的存储和查询的过程

B+树是一种**多叉搜索树，只有叶子节点才会存放实际数据，非叶子节点只会存放索引**，每个节点中数据是按照主键的顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在**叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表**。

数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作。B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以**B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。**

会先检二级索引中的 B+Tree 的索引值，找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

不过，当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到（说明查询的数据就是索引），这时就不用再查主键索引查，这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据。

2.3.2 InnoDB为什么使用B+树作为索引？

B+树相对于B树、二叉树、Hash表的优势：

B+树 vs B树

与B树相比，B+树只在叶子节点中存放数据，因此B+树单个节点可以存放更多的索引数据，可以使得树的层数更小。即能够在相同的I/O次数下，查询到更多的节点。

另外，B+树的叶子节点通过双链表连接，很适合MySQL中的范围查询，B树无法做到这一点。
B+树 vs 二叉树

二叉树的一个父节点只能有两个子节点，搜索复杂度为O(logN)，当节点很多时会导致二叉树的层数很高，检索到目标数据所经历的磁盘I/O次数更多。

对于B+树最大子节点个数为d个，实际应用中就保证了即使数据达到千万级，B+树的高度依然在3~4层左右。
B+树 vs Hash表

Hash表在做等值查询时效率非常快，搜索复杂度为O(1)。但是Hash表很难做范围查询。因此B+Tree 索引要比 Hash 表索引有着更广泛的适用场景。

2.4 联合索引是怎么发挥作用的？

2.4.1 什么是联合索引？

通过将多个字段组合成一个索引，这个索引就叫做联合索引。

联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。这是**最左匹配原则，按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效**，这样就无法利用到索引快速查询的特性了。

2.4.2 最左匹配原则

创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要，但在理论上需要用标准化写法：按照顺序。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

这是因为**利用索引的前提是索引里的 key 是有序的**，对于联合索引，在保证 a 字段相等时，才能使得b有序。

2.4.3 联合索引的范围查询

当联合索引中对某个字段使用了范围查询时，会导致联合索引到这个「范围查询」就会停止匹配。也就是范围查询的字段（及之前）可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。这也是由于**利用索引的前提是索引里的 key 是有序的**决定的。

联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配

2.4.4 联合索引的索引下推

由于无法继续联合索引的匹配，在MySQL早期版本，需要「回表」。通过联合索引中范围查询之前的字段快速定位到符合条件的索引条目对应的主键值（如果是覆盖索引可以直接得到数据）。使用获取到的主键值回到原始数据表中，进一步读取需要的其他列数据。这个步骤需要再次访问数据表，称为 “回表”。

显然，回表会带来一定的性能损失。因此在 MySQL 5.6 引入了**索引下推优化**，可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

2.4.5 联合索引的索引区分度

由于「最左匹配原则」，建立联合索引的顺序对索引效率有很大的影响。实际开发工作中，需要把区分度大的字段排在前面，可以很早地过滤掉大部分不符合的数据。区分度就是某个字段 column 不同值的个数「除以」表的总行数，计算公式如下：

2.5 什么时候需要/不需要索引？

索引主要目的是加快查询，但也不可避免地增加了内存占用和维护数据库表的难度，应有选择的使用。

2.5.1 索引的优缺点

优点：

可以大幅度加快数据的查询速度（大部分情况下比全表扫描快得多）；
唯一性索引可能保证该字段中数据的唯一性；

缺点：

带来了额外的空间消耗。一个索引就是一颗B+树，B+树的每一个节点就是一个16KB的页，占用内存大；
索引的创建和维护会耗费很多时间，对表进行增删改时对应需要修改索引中的内容，降低了效率。

2.5.2 什么时候需要创建索引

表很大，数据很多，全表扫描性能差；
频繁用于 WHERE 查询条件的字段；
频繁使用排序（order by）或分组（group by）的字段；
需要保证唯一性的字段。

2.5.3 什么时候不需要创建索引

表数据比较小，全局扫描也不会有太大延迟；
很少用于 WHERE 查询条件、排序（order by）或分组（group by）的字段；
经常需要更新的字段最好不要创建索引，带来维护的性能瓶颈；
存在大量重复数据的字段（区分度不大，如性别等）。

2.6 优化索引的方法？

前缀索引优化：对于字符串，可以通过字符串的前几个字符建立索引而不是整个，可以减小索引字段的大小；
覆盖索引优化：尽量让查询的字段都在索引中，这样可以避免回表，减少大量的I/O操作；
主键索引最好是自增的：每次插入一条新记录，都是追加操作，不需要移动数据，不会产生页分裂；
索引最好是NOT NULL：索引列存在NULL值时会导致优化器更加难以优化查询的执行，且NULL值会占用物理空间
防止索引失效：避免写出索引失效的查询语句

常见扫描类型的执行效率从低到高的顺序为：

All（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（结果只有一条的主键或唯一索引扫描）。

2.6 索引失效有哪些？

查询条件用上了索引列，查询过程不一定都用上索引，接下来再一起看看哪些情况会导致索引失效，而发生全表扫描。

2.6.1 对索引使用左模糊或左右模糊匹配

使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。**因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。**使用带有左模糊的匹配时，无法确定前缀，则无法根据顺序查找，只能全局扫描。

2.6.2 对索引使用函数或表达式计算

在查询条件中对索引进行表达式计算，也是无法走索引的。例如：

select * from user where length(name)=6;  # 函数
selecr * from user where id + 1 = 10;	  # 表达式，改成 where id = 10 - 1，这样就不是在索引字段进行表达式计算了，于是就可以走索引查询了。

原因是建立的**索引是对字段的原始值的**，而不是对函数值或表达式计算后的值（id + 1），后面得到的值当然无法使用索引匹配。

2.6.3 对索引隐式类型转换

例如，在表中phone的类型是varchar，但是查询语句如下：

select * from user where phone = 1300000001;

这样就**导致字段phone发生了隐式类型转换，则无法通过索引匹配**，而是全局扫描。

特例：从字符串转换成整型不会。由于MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。所以其作用对象不是字段，而是整型。

2.6.4 使用联合索引但不满足最左匹配

创建了联合索引，在使用时需要满足最左匹配原则，否则索引会失效，进行全局扫描。

2.6.5 WHERE字句中的OR

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

这是因为 OR 的含义就是**两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的**，只要有条件列不是索引列，就会进行全表扫描。

2.7 MySQL 单表不要超过 2000W 行，靠谱吗？

非叶子节点内指向其他页的数量为 x
叶子节点内能容纳的数据行数为 y
B+ 数的层数为 z

MySQL的表数据是以页（记录是行）的形式存放的，页在磁盘中不一定连续。页的空间是 16K ,并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等，大概需要1K左右的空间，剩下的15K用来存放数据。

索引页中主要记录的是主键与页号，主键我们假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte。

所以 x=15*1024/12≈1280 行。

叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k。但是叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如，字段的类型，字段的数量。每行数据占用空间越大，页中所放的行数量就会越少。

暂时按一条行数据 1k 来算，那一页就能存下 15 条，y = 15*1024/1000 ≈15。

Total =x^(z-1) *y，已知 x=1280，y=15：

假设 B+ 树是两层，那就是 z = 2， Total = （1280 ^1 ）*15 = 19200
假设 B+ 树是三层，那就是 z = 3， Total = （1280 ^2） *15 = 24576000 （约 2.45kw）

一般 B+ 数的层级最多也就是 3 层，能够存储两千多万行的数据。如果数据过多，导致三层的B+树无法完全存储，就会导致**B+树的层数增加，则在查询时会导致多一次的I/O操作，造成性能下降**。

2.8 count(*) 和 count(1) 有什么区别？哪个性能最好？

2.8.1 count()聚合函数比较

count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是**统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个**。

count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。
count(主键字段)需要遍历索引读取主键值，根据主键值是否为NULL来增加计数；而count(*) 和 count(1) 虽然也遍历索引，不需要读取任何字段的值，所以速度更快。
count(普通字段) 由于该字段不是索引，所以必须通过全局扫描的方式，性能最差。

2.8.2 为什么需要遍历来计数？

MyISAM 引擎时，执行 count 函数**只需要 O(1 )复杂度**，这是因为每张 MyISAM 的数据表都有一个 meta 信息有存储了row_count值，由表级锁保证一致性，所以直接读取 row_count 值就是 count 函数的执行结果。

InnoDB存储引擎是支持事务的，同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表**“应该返回多少行”也是不确定的（不同版本结果不同）**，所以无法像 MyISAM一样，只维护一个 row_count 变量。

2.8.2 如何优化count(*)？

近似值：使用 show table status 或者 explain 命令来表进行估算
额外维护一个表，来统计计数；

资料参考

内容大多参考自：图解MySQL介绍 | 小林coding (xiaolincoding.com)

Magic@

关注

14
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习[2] ——MySQL索引

通过将多个字段组合成一个索引，这个索引就叫做联合索引。联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。这是**最左匹配原则，按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效**，这样就无法利用到索引快速查询的特性了。
复制链接

扫一扫