MySQL-索引篇

csdn推荐

文章目录

什么是索引?

索引是一种用于快速查询和检索数据的数据结构,其本质可以看成是一种排序好的数据结构

索引的优缺点

优点:

缺点:

问:索引一定能提高查询性能吗?

索引底层数据结构选型 Hash表

问:为什么MySQL不使用Hash表作为索引的结构?

Hash索引不支持顺序和范围查询,并且每次IO只能取一个

二叉查找树

二叉查找树的性能非常依赖于其平衡程度,也不适合作为MySQL的索引底层数据结构

AVL树 红黑树 B树&B+树 索引类型总结

按不同的角度有不同的划分

主键索引 数据库表主键列使用的索引就是主键索引一个表只能有一个主键,主键不能为null,不能重复InnoDB中,如果没有指定主键,会自动检查是否有唯一索引并且不允许存在null值的字段,如果有设为主键,否则,自动创建一个6byte的自增主键 二级索引 二级索引的叶子节点所存储数据是主键的值唯一索引,普通索引,前缀索引,全文索引都属于二级索引

聚集索引与非聚集索引 聚集索引

索引结构和数据存放到一起的索引,InnoDB中的主键索引就是聚集索引,B+树每个非叶子结点存储索引,叶子节点存储索引和索引对应的数据

优点:

查询速度非常快对排序查找和范围查找优化

缺点:

依赖于有序的数据更新代价大 非聚集索引

非聚集索引就是索引结构和数据分开存放的索引,二级索引就是非聚集索引

优点:

更新代价较小,因为叶子节点不存放数据

缺点:

依赖于有序的数据可能会二次查询(回表)

问:什么是回表?

在使用非主键索引查询时,会先找到主键,再根据主键索引查询完整的数据,这个过程被称为回表

覆盖索引与关联索引 覆盖索引

一个索引包含所有需要查询的字段的值,需要查询的字段刚好是索引的字段,直接根据该索引,就可以拿到所需的数据了,不需要回表查询

联合查询

使用表中多个字段建立索引,就是联合索引

最左前缀匹配原则

在使用联合索引时,MySQL根据索引的字段顺序,从左到右依次匹配查询条件中的字段,如果查询条件与索引中的最左侧字段相匹配,就会根据索引来查

#联合索引 idx(name,class)
# 可以命中索引
SELECT * FROM student WHERE name = 'Anne Henry';
EXPLAIN SELECT * FROM student WHERE name = 'Anne Henry' AND class = 'lIrm08RYVk';
# 无法命中索引
SELECT * FROM student WHERE class = 'lIrm08RYVk';

索引下推

是一项索引优化功能,允许存储引擎在索引遍历过程中,执行部分where字句的判断条件,直接过滤掉不满足条件的记录,从而减少回表次数,提高查询效率

如何正确使用索引 选择合适的字段创建索引 不为Null的字段被频繁查询的字段被作为条件查询的字段频繁需要排序的字段经常需要连接的字段 被频繁更新的字段应该慎重创建索引 限制每张表上的索引数量

建议单张表不超过5个,MySQL优化器在选择如何优化查询时,会对每一个可以用到的索引进行评估,以生成一个最好的执行计划,如果有多个索引可以选择,会增长生成执行计划的时间,也会降低查询效率

尽可能考虑建立联合索引而不是单列索引

索引需要占用磁盘空间,如果是联合索引,多个字段在一个索引上,会节省很大的磁盘空间,且修改数据的效率也会提升

注意避免冗余索引

大多数情况下应扩展已有的索引,而不是创建新索引

字符串类型的字段使用前缀索引而不是普通索引

前缀索引仅限于字符串类型,且相对于普通索引会占用更小的空间

避免索引失效

常见索引失效场景:

创建了联合索引,但查询条件未遵循最左前缀法则在索引列上计算,函数,类型转换等操作以%开头的LIKE查询比如LIKE ‘%abc’;查询条件中使用了or,or左右有一个列没有索引,涉及索引都会失效in的取值范围较大时会导致索引失效,走全表扫描发生隐式转换() 删除长期未使用的索引

不用的索引的存在会造成不必要的索引的损耗

文章来源:https://blog.csdn.net/m0_74870396/article/details/138708570

© 版权声明
THE END
喜欢就支持一下吧
点赞6 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    暂无评论内容