MySQL

简介: MySQL中InnoDB引擎使用B+树作为索引结构,因其非叶子节点不存数据,可存储更多键值,降低树高(通常2-3层),提升查询效率。聚簇索引将数据与主键索引存储在一起,每表仅一个;二级索引则分离存储,叶子节点保存主键值,查询时需回表。为优化性能,应优先使用主键查询、创建覆盖索引或利用索引下推(ICP)减少回表次数。

在MySQL默认的InnoDB存储引擎中,有两类索引,分别是:聚簇索引和二级索引。 聚簇索引,他的特点呢就是数据与索引存放在一块儿,B+tree索引结构的叶子节点保存了整行数据,而且在一张表中,聚簇索引有且仅有一个,默认主键索引就是聚簇索引。二级索引,指的是数据和索引分开存储,B+tree的叶子节点保存对应的主键,二级索引在一张表中可以有多个。
所谓回表查询,就指的是,在执行这条SQL语句的时候,先根据二级索引去检索出对应的主键值;然后再根据主键值,到聚簇索引中查询出对应的数据,这个过程就叫回表查询。 所以回表查询,是需要扫描两次索引的,性能相对来说会差一些。
●
进阶内容:
所以,在项目开发中,我们进行SQL优化的时候,如果需求允许的情况下,尽量避免回表查询,主要从以下几个方面来做:
1). 业务允许的情况下,尽可能根据主键查询,使用聚集索引-避免回表查询。
2). 为表中的字段,根据业务需求创建合适的联合索引,查询时使用索引覆盖-避免回表查询。
3). 使用索引下推,减少回表查询的次数。【索引下推,是mysql5.6之后提供的功能】
●
可能继续发问的问题:
你刚才提到索引下推,简单聊聊什么是索引下推?
索引下推(Index Condition Pushdown),是MySQL5.6后提供的功能,指的是在多条件查询SQL执行时,提前判断对应的搜索条件是否满足,满足了再去回表(就是将本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断,这样能有效减少回表),通过减少回表次数进而提高查询效率。
2.4 为什么MySQL索引结构是B+tree ?
●
必答内容:
其实这个问题,我们可以做一个假设啊。
●
假设索引结构是二叉搜索树、平衡二叉树 或 红黑树等,其实本质都是二叉树,一个节点下最多只能有两个子节点,如果这张表要存储的数据量比较大,二叉树的层级将会非常深,检索效率会很低。
●
而如果索引结构是Btree,在B树中,非叶子节点和叶子节点既要要存储key和指针,还要存放数据,而InnoDB的物理存储结构中,一页(Page)的大小是固定的,就是16KB。 那这一页中能够存储的key的数量并不多,就会造成大数据量情况下,树的层级较深,检索速度慢。 还有一个问题,就是由于 非叶子节点和叶子节点既要要存储key,还要存放数据,查找效率并不稳定。 (有些数据,只需要一次查找,有些数据,可能需要五六次,有些...)
所以,在MySQL数据库中才使用了B+tree作为索引的数据结构。 主要有以下优势:
●
在B+tree中,非叶子节点并不存放数据,只存放key和指针,所以一页(Page)中能够容纳的key将更多,相同数据量的情况下,树的层级要浅的多,检索效率高。
●
所有的数据都存储在B+tree的叶子节点中,也就意味着无论什么数据,都需要找到叶子节点才能查询到对应的数据,检索效率更加稳定。
●
第三是B+树数据都存储在B+tree的叶子节点,并形成了一个双向链表,便于区间范围查询。
●
可能继续发问的问题:
那MySQL的B+tree的索引结构,树的高度一般是多高呢?
嗯,这个高度其实是可以计算出来的,一般高度在2-3层,如果高度为3,基本上就可以容纳一两千万的数据了。如何计算呢?
●
我们的索引是在页(Page)中存储的,而一个页的大小模式为(16KB)。
●
对于非叶子节点来说,页中存储的除了具体的key之外,还有一个就是指针 。(假设主键为bigint占8个字节,指针占6个字节)
●
那么我们就可以大概计算出一页中可以存储的key数量为:16 1024 / 14 = 1170 。也就意味着一个页(Page)中约可以存1170个key
●
假设一行数据的大小为1KB,一页可以存16条数据。那两层的B+tree可以容纳:1170
16=18720条数据。
●
那三层的B+tree可以容纳:1170 1170 16 = 21902400 条数据。

相关文章
|
算法 安全 Java
z3-solver求解器
一个非常高级的工具,SMT求解器。应用领域非常广,解各类方程,解各类编程问题(例如解数独),解逻辑题等都不在话下。
|
消息中间件 Kafka 程序员
kafka的Docker镜像使用说明(wurstmeister/kafka)
wurstmeister/kafka是镜像市场上很受欢迎的一个kafka镜像,今天来聊聊它的多种用法
7405 0
kafka的Docker镜像使用说明(wurstmeister/kafka)
|
6月前
|
SQL 算法 关系型数据库
【MySQL】MVCC多版本并发控制:核心原理、Read View、undo log版本链、RC/RR隔离级别的差异控制(附《高频面试题》+流程图)
MySQL MVCC是InnoDB实现高并发的核心机制,通过undo log版本链与Read View可见性判断,使读不加锁、读写互不阻塞。它支撑RC(每次查询新建Read View)和RR(事务首次查询创建并复用Read View)隔离级别,在解决不可重复读与幻读的同时,兼顾性能与一致性。
|
10月前
|
数据采集 缓存 开发框架
RFC规范解释、URL 与 Body 、GET/POST 的核心区别详解
本文深入解析RFC规范下GET与POST的本质区别:GET语义为“只读”,安全且幂等,适用于获取资源;POST为“写操作”,不安全也不幂等,用于提交数据。详解URL与Body用法误区,并揭示安全、幂等属性对开发的影响,助你避开常见坑,写出更规范的接口。
1062 3
|
算法 API 数据安全/隐私保护
电商 API 双平台实战:淘宝 item.get + 京东 item_detail 对接指南(附可复用代码 + 问题排查)
本文详细解析了淘宝和京东双平台API对接的核心流程,涵盖资质申请、凭证获取、签名生成、高频接口调用及常见问题解决方案,助力开发者高效实现商品数据同步与管理。
|
7月前
|
人工智能 弹性计算 机器人
阿里云轻量应用服务器部署OpenClaw应用镜像,以及OpenClaw集成飞书图文教程
本文介绍了在阿里云轻量应用服务器上部署OpenClaw的流程,包括计费模式如Coding Plan AI编码套餐和按Token用量计费两种方式、购买服务器步骤、配置OpenClaw的方法,以及集成飞书的具体操作。此外,还解答了重启OpenClaw网关、查看端口号、更改模型、与飞书配对、配置定时任务、开启/关闭WebUI公网访问等常见问题。
|
存储 运维 关系型数据库
深入理解MySQL的MVCC(多版本并发控制)实现原理
总结起来,MVVC技术使得MySQL能够有效地支持高并发环境中复杂交互要求; 然而合理配置及运维管理仍然关键确保系统长期稳健运转.
924 16
|
11月前
|
SQL 安全 Java
了解 SQL 注入:原理、危害与防御
SQL注入是Web安全头号威胁,攻击者通过恶意SQL代码窃取、篡改或删除数据。本文详解其原理、危害及防御方案,强调预编译、输入校验、最小权限等核心防护措施,助你筑牢数据库安全防线。