文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL性能优化1-MySQL底层索引结构

2023-08-23 23:22

关注

❤️ 个人主页:程序员句号
🚀 支持水滴:点赞👍 + 收藏⭐ + 留言💬+关注
🌸 订阅专栏:MySQL性能调优

MySQL性能优化专栏
1.MySQL性能优化1-MySQL底层索引结构
2.MySQL2-Explain详解
3.MySQL3-索引最佳实战
4.MySQL4-MySQL内部组件结构
5.MySQL5-事务隔离级别和锁机制
6.MySQL6-深入理解MVCC和BufferPool缓存机制

深入理解MySQL底层索引结构与算法

什么是索引?

一句话解释:索引是帮助MySQL高效获取数据的排好序的数据结构
我们都知道MySQL底层使用的是B+Tree树,那为什么要使用B+Tree树勒?有哪些数据结构可以使用?

索引数据结构的选择

不了解数据结构也没关系,记住就行,后面去理解下各种数据结构就好。
这四种我们都没有使用,为什么不使用?

为什么MySQL选择B+Tree,不选择B-Tree?

主要因为两个原因:

  1. 同样的数据量B+Tree树的高度小于B-Tree的高度,因为B-Tree非叶子节点也会存储数据,而MySQL设置的一个高度数据大小是16KB。所以每个节点的数据大小越大,每个高度能存储的节点就越少,那么能存储的节点数量也就越少
  2. 加载到内存中,费内存

什么是聚集(聚簇)索引,什么是非聚集索引?

我们常用的InnoDB存储结构就是使用的聚集索引,而非聚集索引就是MyISAM。
聚集索引:叶子节点包含了完整的数据记录(也就是数据和索引没有分开)。
非聚集索引:MyISAM索引文件和数据就是分离的。

在window下安装mysql,然后建一个test表,找到mysql安装目录下的data目录,再找到对应的数据库目录,打开后可以看到下方的文件。

InnoDB存储结构

  1. frm:表结构
  2. .ibd:索引加数据
    在这里插入图片描述

MyISAM存储结构

  1. 【frm后缀】:表结构
  2. .【MYD后缀】:数据
  3. 【MYI后缀】:索引文件

在这里插入图片描述

什么是主键索引和非主键索引?

主键索引:每张表都有主键,你设置的主键会默认生成一个索引,该索引就叫主键索引。
非主键索引:除主键索引外,设置的其他索引如单一索引,联合索引都是非主键索引。

两者区别:主键索引的叶子节点是存储的完整数据,而非主键索引叶子节点是存储的主键id,所以就有个回表操作。SQL命中了非主键索引,非主键索引还需要进行一次回表操作

什么是覆盖索引?

 覆盖索引算不上真正的索引,它意思是查询的结构集在我们的索引中,就不需要进行回表操作。MySQL执行计划explain结果里的key有使用索引,如果select后面查询的字段都可以从这个索引的树中获取,这种情况一般可以说用到了覆盖索引,extra里一般都有using index;覆盖索引一般针对的是辅助索引,整个查询结构只通过辅助索引就能拿到结构,不需要通过辅助所以树找到主键,再通过主键去主键索引树里获取其他字段值。

为什么建议InnoDB表必须建主键?并且推荐使用整型的自增主键?

 因为和Innodb的存储结构相关的,Innodb的索引和数据是在一起的,所以它肯定会由一列组成B+Tree,如果你没有建索引,首先MySQL会先从表里找一列全是数字不重复的列作为索引,如果没找到,会创建一个虚拟的索引rowId,所以这就是有啥建议有主键的原因。

第二个问题:为什么推荐使用整型的?
  我们观察索引查找的过程,会发现每次都在做比较把,那什么样的数据比较最快勒,那肯定是整型咯,如果是uuid,还得转成Aisll码去一个一个的比较。

第三个问题:为什么推荐使用自增主键?
  因为如果是有序的,那么就会减少B+Tree树的分裂和平衡。
  如下图这时候插入7,可能会造成节点的分裂和平衡。因为我们知道叶子叶子的排列是排好序的,7在6和8之间,而刚好节点数量又达到了阈值,所以会分裂和平衡。
(这里是使用了数据结构可视化网址
在这里插入图片描述

为什么非主键索引结构叶子存储的是主键值?(InnoDB)

  因为一致性和节省存储空间,如果每开一个索引就要维护一份数据,那它浪费空间了。并且你做更新删除等操作时也需要同时删除几个索引下的数据。会产生回表操作(也就是查聚集索引的那个数据结构)

联合索引的底层数据结构是怎么样的?

分为联合主键和联合索引。
也是一颗B+Tree,但是它会排序,先比较name,再比较age,再比较position来决定先后顺序。
在这里插入图片描述

最左前缀原理是什么?为什么有这个最左前缀?

前提你创建了联合索引,你想要使用到索引,必须先使用最左边的那个字段,否则你是使用不了索引的。

EXPLAIN SELECT * FROM test_innodb WHERE name = 'Bill' and age = 31; 走索引EXPLAIN SELECT * FROM test_innodb WHERE age = 30 AND position = 'dev'; 不走EXPLAIN SELECT* FROM test_innodb WHERE position = 'manager'; 不走EXPLAIN SELECT* FROM test_innodb WHERE name = 'Bill' and position = 'manager'; 走索引EXPLAIN SELECT* FROM test_innodb WHERE name = 'Bill' and position < 'manager' and position > 'dev';EXPLAIN SELECT * FROM test_innodb WHERE age = 30 AND name = 'dev';  走索引(因为Mysql的自动优化)

第二个问题:因为它的底层数据结构就是先根据字段前后去排序组成的二叉树,如果第一个没得,那么后面的字段你去查找会发现不是顺序的,那么你就只能去全表扫描了。
例如上图:如果不用name字段,那么age单独是没得顺序的,你根据age去查找肯定是得全表扫描的。想要age字段有序,之前的索引字段name必须是相等的

来源地址:https://blog.csdn.net/2301_76921448/article/details/129632825

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯