MySQL-索引

我是鸹貔

已于 2024-05-19 00:05:51 修改

阅读量775

点赞数 22

分类专栏： mysql 文章标签： mysql 数据库

于 2024-05-19 00:00:43 首次发布

本文链接：https://blog.csdn.net/m0_62740234/article/details/139033678

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

![[Pasted image 20240518231731.png]]

索引是怎么提高查询效率的？可以为了提高查询效率增加索引么？

索引能提升数据库查询效率，根本原因就在于索引减少了查询过程中的IO次数。
为了提高查询效率而过多的增加索引是不可取的，在MySQL中除了主键索引，多加一个索引，就会多生成一颗非聚簇索引树。因此，索引不能随意增加。在做写库操作的时候，需要同时维护这几颗树的变化，导致效率降低！

mysql索引系统的数据结构为什么要使用B+树？B树、B+树、B*树的区别?

![[Pasted image 20240518141816.png]]

![[Pasted image 20240518141832.png]]

从图中我们就可以看出，B树与B+树的区别：

B树：树内的每个节点都存储数据；叶子节点之间无指针连接。
B+树：数据只出现在叶子节点；所有叶子节点增加了一个链指针。

为什么选择B+树？：

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，尽量减少磁盘IO操作。访问二叉树的每个节点就会发生一次I/O，如果想要减少磁盘IO操作，就需要尽量降低树的高度。首先我们知道在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，即有限的，如果data数据较大我们采用B树存储时就回导致每一个页存储的key的数量减小，那么保存同样多的key就会增加树的高度，相应的就会增加I/O的次数。而B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+树的高度。
B+树的叶子节点上有指针进行相连，因此在做数据遍历的时候，只需要对叶子节点进行遍历即可，这个特性使得B+树非常适合做范围查询。

聚集索引相对于非聚集索引的区别？

聚簇索引：数据库表行中数据的物理地址与键值的逻辑地址（索引）顺序相同，即数据和索引存储在在一起。
非聚簇索引：索引的结构和数据的存储是分开的。索引结构中只存储了键值和一个指向对应数据行的指针。

值得注意的是在InnoDB中：

在Innodb中，聚簇索引默认就是主键索引。
如果表中没有定义主键，那么该表的第一个唯一非空索引被作为聚集索引。
如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

什么是回表？

在数据库中，如果使用了非聚簇索引进行查询，那么在查找数据时，首先会在非聚簇索引中找到对应的键值及其指向聚簇索引的指针，然后再根据这个指针到聚簇索引中去寻找真正的数据。这个过程就称为"回表"，因为它需要再次回到聚簇索引表中去查找数据。简单点来说就是根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

什么是索引覆盖？

索引覆盖（Covering Index）是数据库中一种查询优化策略。当一个查询的所有列都包含在索引中，且该索引可以满足查询需求而无需回表去查原数据时，我们就称这个索引为覆盖索引。
假设我们有一个用户表，其有"username",“password”,"email"三个字段，而我们为"username"和"password"创建了一个复合索引。此时，如果我们执行查询"SELECT username, password FROM users WHERE username = “123”;那么这个查询就是一个覆盖索引的查询，因为在该查询中我们只需要password和usernam信息，而这两个信息都包含在我们的索引中，无需再去实际的表中查找数据。

什么是最左匹配原（左前缀匹配）？

首先我们需要知道什么是联合索引，联合索引就是建立在多个字段上的索引。联合索引依然是B+树，但联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树，这就是最左匹配原则。
假如在table表的a,b,c三个列上建立联合索引，索引key在排序上a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。就相当于我拥有了(a)、(ab)、(abc)这三个索引。
当进行等值查询时，a,ab,abc,ba,bac,cba,cab都是走索引的（a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮助优化），而ac则由于c排序依赖于b，所以会先通过a列的索引筛选出a=1的记录，再在这些记录中遍历筛选c=3的值，是一种不完全使用索引的情况。
值得注意的是：mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

索引失效场景可能有哪些，如何避免？

不满足最左前缀原则。
在索引列上使用函数：例如select * from student where upper(name)=‘ZHANGSAN’;会导致索引失效，而select * from student where name=upper(‘zhangsan’);是会使用索引的。
在索引列上计算：例如select * from student where age-1=17;
进行类型转换时：where code = “123” 错写成了 where code = 123会导致索引失效。
查询条件使用LIKE+左百分号：使用后置通配符会走索引，例如WHERE name LIKE ‘z%’，而前置通配符会导致索引失效而进行全表扫描WHERE name LIKE ‘%b’。
错误使用or：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效。
not in、not like可能无法充分利用索引。