当前位置：首页 > news >正文

mysql笔记-索引

news 2024/11/4 23:36:16

索引

1、什么是索引

索引是对数据库中数据的一种结构化表示。它像一本书的目录，能够快速定位信息，而无需逐行扫描所有数据。
索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

2、索引的常见模型

2.1.哈希表

用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。我们只要输入待查找的key，就可以找到其对应的值即 Value。

举例：当我们要根据用户身份证号查用户名时，哈希表索引就是通过哈希函数算出身份证号对应的数组位置，然后得到用户名的。

适用于只有等值查询的场景
做区间查询的速度是很慢的

2.2.有序数组

适合范围查询（如上图，该有序数组是根据身份证号大小递增存储的，如果我们范围搜索身份证号则速度很快）
更新数据麻烦，因为数组插入一个元素需要移动后面所有的元素。

2.3.搜索树

2.3.1.二叉搜索树

二叉树是搜索效率最高的
因为二叉树较高，读磁盘的次数多，导致查询速度慢

2.3.2.多叉搜索树

读磁盘的次数少，因此查询速度快

3、InnoDB 的索引模型

在 MySQL 中，索引是在存储引擎层实现的，因此不同的存储引擎实现的索引底层的索引模型不同。在这里我们介绍InnoDB实现的索引底层的索引模型是怎样的。

InnoDB 使用了 B+ 树索引模型实现索引。每一个索引在 InnoDB 里面对应一棵 B+ 树。

B+树相比于B树的优点是什么？

B树每个结点不仅存储了索引，还存储了数据，而B+树只有叶子结点才存储数据。因此B+树一个结点中存储的索引会更多，使得结点总数会更少，自然而然读磁盘的次数就更少。

4、索引类型

索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。
非主键索引的叶子节点内容是主键的值。

基于主键索引和非主键索引的查询有什么区别？

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

5、索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。在维护的过程中可能出现页分裂和页合并，会影响性能。

为了避免出现页分裂和页合并，我们希望尽可能使用自增主键。

5.1.什么是自增主键？

主键：主键是一个表中的字段，它的值能够唯一标识表中的每一行数据，
自增：每当新记录被插入到表中时，自增主键会自动加一，生成新的唯一值。

5.2.使用自增主键的好处？

不会触发叶子节点的分裂。每次插入一条新记录，都是追加操作，不涉及到挪动其他记录。
**占用存储空间相对较小。**每个非主键索引的叶子节点上都是主键的值。主键占用的空间越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

5.3.什么场景适合用业务字段直接做主键？

只有一个索引：由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。
该索引必须是唯一索引：因为主键是唯一标识每行数据的，因此需要是唯一索引。

6、索引使用

6.1.覆盖索引

当数据库执行查询时，如果可以从覆盖索引中获取所需的所有数据，就不需要回表。意味着查询可以直接使用索引中的数据来返回结果，从而提高查询效率。

举例：select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。这里的索引k就是覆盖索引。

6.2.联合索引

多个字段组合成一个索引，该索引就被称为联合索引。

举例：现在有一个联合索引（name，age），这个联合索引的B+树结构是先给name进行排序，如果name相同，则按照age排序。也就是说age是全局无序，局部无序的。

使用到联合索引全部字段的情况：

有一个联合索引（a，b），查询语句的条件是where a = 2 and b = 7。

当在联合索引上查询到a=2的记录时，接着在a=2的基础上继续查询b是否=7，两个字段都给用到了联合索引。

在这里插入图片描述

使用到联合索引部分字段的情况：

有一个联合索引（a，b），查询语句是select * from t_table where a > 1 and b = 2。

定位到符合 a > 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a > 1 条件位置。但是在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的。因此，我们不能根据查询条件 b = 2 来进一步减少需要扫描的记录数量。按理来说，当查出符合 a > 1 的所有记录后，就会回表查询，然后执行 b = 2 的过滤。但是因为索引下推，会在联合索引表上检查满足 b = 2 的记录，最后再回表。

这里补充：sql性能分析中用到的explain命令

Extra：Using index condition 【表示用到了索引下推】

Using filesort 【表示用到了文件排序】

Using index：【所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错】

key_len：【表示索引长度】

type：【表示索引使用情况】

All【全表扫描】；

index【全索引扫描】；

range【索引范围扫描】；

ref【非唯一索引扫描】；

eq_ref【唯一索引扫描】；

const【结果只有一条的主键或唯一索引扫描】。

6.3.最左前缀原则

最左前缀原则主要用于确定在使用联合索引时，查询条件中必须包含索引的最左侧列（即联合索引的第一列），才能有效地利用该索引。

举例：现在有一个联合索引（name，age），查询条件是"where name like ‘张 %’"。满足最左前缀原则，能有效利用这个联合索引。

建立联合索引的时候，如何安排索引内的字段顺序？

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
第二考虑的原则就是空间。如果既有联合查询，又有基于 a、b 各自的查询，那么我们可以建立（a，b）、（b）索引或（b，a）、（a）索引。如果b的空间占用更大，那么则考虑建立（b，a）、（a）索引。
第三是要把区分度大的字段排在前面。比如说性别这个字段，区分度很小，要么是男，要么是女，如果给性别建立索引，查询完之后得到一半的数据，用索引有啥意义？一个字段的区分度怎么看？就是看这个字段在全部记录中重复程度。【公式就是：distinct(这个字段)/总记录数】

6.4.索引下推

索引下推指在索引查找过程中，尽可能多地将过滤条件（如 WHERE 子句中的条件）直接应用于索引，而不是在检索数据后再进行过滤。

举例：现在有联合索引（name, age），执行查询select * from tuser where name like ‘张%’ and age=10;

因为查询条件name like '张%'满足最左前缀原则，因此在联合索引（name，age）上查询。

无索引下推执行流程：

1、在联合索引（name，age）上找到满足查询条件name like '张%'的所有行

2、依次回表查询获得完整数据

3、执行age=10的过滤。

索引下推执行流程：

1、在联合索引（name，age）上找到满足查询条件name like '张%'的所有行，并在这个过程中也检查 age=10 的条件。

2、依次回表查询获得完整数据

可以看出索引下推减少了回表的次数，提高了查询的速度。

6.5.前缀索引

前缀索引：定义字符串的一部分作为索引。

举例：查询语句select f1, f2 from SUser where email=‘zhangssxyz@xxx.com’;现在要给email字段建立前缀索引。我们选择给email的前7个字节建立前缀索引：alter table SUser add index index2(email(7));

1、前缀索引的优势在于，索引在一个结点中所占用的空间减少，一个结点中可以存储更多索引值，使得查询速度更快。但是前提是前缀索引的字段值区分度要高，如果不高的话，反而会增加查询次数。

比如说，你给email的前6个字节建立前缀索引。如下图：

在这里插入图片描述

有四条记录的email字段值都是一样的，这区分度显然不太行，现在让你查询email='zhangssxyz@xxx.com’的记录，你就需要回表四次判断email是否等于zhangssxyz@xxx.com，这查询次数比直接给email建立索引的次数还多。还不如直接给email字段建立索引。

2、用了前缀索引可能就会影响覆盖索引。

以上基本上把索引相关知识记录清楚了，现在我们来根据上面的知识总结【索引优化思路】：

前缀索引优化；
覆盖索引优化；
主键索引最好是自增的；
防止索引失效；
- like %xx 或者 like %xx%这两种方式都会造成索引失效；
- 查询条件中对索引列做了计算、函数、类型转换操作会造成索引失效；
- 联合索引需要遵循最左匹配原则，否则就会导致索引失效。
- 在where子句后，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，索引失效。
前缀索引优化；
覆盖索引优化；
主键索引最好是自增的；
防止索引失效；
- like %xx 或者 like %xx%这两种方式都会造成索引失效；
- 查询条件中对索引列做了计算、函数、类型转换操作会造成索引失效；
- 联合索引需要遵循最左匹配原则，否则就会导致索引失效。
- 在where子句后，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，索引失效。