MySQL存储引擎、索引、索引失效

MySQL

Docker 安装 MySQL8.0，安装见docker-compose.yaml

操作类型

SQL 程序语言有四种类型，对数据库的基本操作都属于这四种类，分为 DDL、DML、DQL、DCL

  1. DDL(Dara Definition Language 数据定义语言)，是负责数据结构定义与数据对象定义的语言，由 create、alter、drop、truncate 四个语法组成

    • create table 创建表
    • alter table 修改表
    • drop table 删除表
    • truncate table 清空表

  2. DML(Data Manipulation Language 数据操纵语言)，主要是进行插入数据、修改数据、删除数据的操作，由 insert、update、delete 语法组成

  3. DQL(Data Query Language 数据查询语言)，用来进行数据库中的数据查询，最常用的就是 select 语句

  4. DCL(Data Control Language 数据控制语言)，用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视

存储过程

MySQL数据存储位置可以通过SHOW VARIABLES LIKE 'datadir';去获取，每一个数据库都会有一个文件，每一张表都会有一个*.ibd文件，这个文件存储着表数据、索引、UNDO日志等等…

表空间文件结构

表空间有文件头(File Header)、段(Segment)、区(Extent)、页(Page)

  1. 段(Segment)，段是表空间的逻辑分区，用于管理不同类型的数据，如表的数据段、索引段、溢出段等
  2. 区(Extent)，每个区由多个连续的页组成，默认大小为 1MB（即 64 个连续的 16KB 页）
  3. 页(Page)，页是 InnoDB 表空间文件的基本存储单元，每页存储不同的数据内容，如行数据、索引、回滚信息等
  4. 行(Row)，行是表中数据的基本逻辑单位，代表每一条记录；记录以特定格式存储在数据页中，并包含实际的列值、元信息（如事务 ID、回滚指针等）

请添加图片描述

数据页结构

数据库I/O操作的最小单位是页，与数据库相关的内容都会存储在页结构里。数据页包括七个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Infimum+supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）

请添加图片描述

请添加图片描述

行(Row)格式分类

MySQL 支持以下几种行格式，具体格式由表的 ROW_FORMAT 定义：Compact（紧凑格式）、Redundant（冗余格式，MySQL 早期版本的默认格式）、Dynamic（动态格式）、Compressed（压缩格式）

CREATE TABLE example (id INT,name VARCHAR(255)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

以Compact为例

字段内容说明
行头信息INFO_BITS, HEAP_NO 等用于管理行的元信息
隐藏列TRX_ID, ROLL_PTR 等用于支持事务和回滚
用户数据id=1定长数据直接存储
用户数据name=‘Alice’包括长度前缀和实际数据
用户数据age=30定长数据直接存储
用户数据bio 指向溢出页如果数据过大，存储在溢出页

存储引擎

可以通过SELECT * FROM INFORMATION_SCHEMA.ENGINES;查询数据库支持存储引擎，常见的存储引擎有InnoDB、MyISAM

请添加图片描述

InnoDB 存储引擎

InnoDB是现在默认的存储引擎，具体参考官方文档

  1. 事物支持

    • 支持事物，遵循ACID特性

  2. 行级锁

    • 采用行级锁，支持高并发
    • 结合多版本并发控制(MVCC)，减少锁争用

  3. 外键约束

    • 支持外键约束，确保数据一致性和完整性

  4. 崩溃恢复

    • 使用 Redo Log 和 Undo Log 来确保数据在系统崩溃后可以恢复

  5. 索引

    • 聚簇索引（Clustered Index）存储数据，主键索引和行数据一起存储
    • 辅助索引，辅助索引存储索引键和指向主键的引用，回表

MyISAM 存储引擎

MyISAM存储引擎是基于较旧的ISAM存储引擎的扩展，具体参考官方文档

  1. 无事物支持
  2. 表级锁
  3. 高效读操作
  4. 非聚簇索引，数据和索引分开存储
  5. 压缩表
  6. 不支持外键

选择存储引擎

  1. 如果系统需要 事务支持、高并发写入、数据一致性（如银行、订单系统）
    选择 InnoDB

  2. 如果系统以 读操作为主、不需要事务支持（如报表系统、数据统计）
    选择 MyISAM

索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构，索引的作用就相当于书的目录

索引分类

  1. 按照存储方式划分

    • 聚簇索引：索引结构和数据存一起存放的索引（InnoDB中的主键索引）
    • 非聚簇索引：索引结构和数据分开存放的索引，如二级索引，MyISAM引擎下的索引

  2. 按照应用维度划分

    • 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个
    • 普通索引：仅加速查询
    • 唯一索引：加速查询 + 列值唯一（可以有 NULL）
    • 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值
    • 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
    • 全文索引：对文本的内容进行分词，进行搜索

  3. 按照数据结构划分

    • BTree 索引：最常用的索引类型，叶子节点存储value
    • 哈希索引：类似键值对的形式，一次即可定位
    • 全文索引：对文本的内容进行分词，进行搜索

BTree

B-Tree（Balanced Tree，平衡树）是一种自我平衡的树数据结构，保持数据有序，时间复杂度为 O ( log ⁡ n ) O(\log n) O(logn)

比较项BTreeB+Tree
数据存储位置数据存储在叶子节点和非叶子节点数据仅存储在叶子节点
索引节点存储内容键和值仅存储键
范围查询效率较低，需遍历多个节点高效，叶子节点形成链表
顺序遍历需要中序遍历整棵树通过叶子节点链表直接遍历
树高度较高（非叶子节点存储更多数据）较低（非叶子节点存储更少数据）
适用场景一般的搜索和存储场景数据库索引、文件系统的最佳选择

数据库使用B+Tree的优势

  • 更高效的磁盘 IO：非叶子节点占用更少的存储空间，能减少磁盘读取次数，提高性能。
  • 更快的范围查询：叶子节点形成链表，适合处理范围查询和排序查询。
  • 易于维护：插入和删除操作的复杂度较低，树的平衡性易维护。
  • 良好的扩展性：能适应大规模数据和高并发场景。

索引失效

  1. 字段类型隐式转换

    -- 索引失效
SELECT * FROM user WHERE id = '1';
-- 索引生效
SELECT * FROM user WHERE id = 1;

  2. 索引列参与运算

    -- 索引失效
SELECT * FROM user WHERE id + 1 = 10;
-- 索引生效
SELECT * FROM user WHERE id = 10;

  3. 索引列使用函数

    -- 索引失效
SELECT * FROM users WHERE SUBSTR(name, 1, 3) = 'Tom'
-- 索引生效
SELECT * FROM users WHERE name = 'Tom'

  4. 索引列使用like，且%在前面

    -- 索引失效
SELECT * FROM users WHERE name LIKE '%Tom%'
-- 索引生效
SELECT * FROM users WHERE name = 'Tom'

  5. 数据量比较少，优化器不走索引

  6. 索引列使用!=、<>、!<、!>、NOT IN、NOT LIKE、NOT BETWEEN、IS NOT NULL

    -- 索引失效
SELECT * FROM users WHERE id != 1;
-- 索引生效
SELECT * FROM users WHERE id = 1;

  7. 容易误解的地方是 <、> 走不走索引，在MySQL8.0中，<、> 针对数字类型走索引会走索引

    • 当匹配的数据量超过表总数据量的20%-30%时，优化器倾向于选择全表扫描，索引也会失效

  8. 索引列使用OR

  9. 索引列使用复合索引， 违反最左前缀原则

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/326.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

如何看待Akamai 退出中国市场进行转型？

如何看待Akamai 退出中国市场进行转型？

Akamai宣布退出中国市场并进行战略转型&#xff0c;这一举措引发了广泛的关注和讨论。从多个角度来看&#xff0c;这一决策既反映了Akamai自身的业务调整需求&#xff0c;也与中国市场环境的变化密切相关。 Akamai的退出是其全球战略调整的一部分。Akamai近年来一直在推进业务…
阅读更多...
【Linux】网络层

【Linux】网络层

目录 IP协议 协议头格式 网段划分 2中网段划分的方式 为什么要进行网段划分 特殊的IP地址 IP地址的数量限制 私有IP地址和公有IP地址 路由 IP协议 在通信时&#xff0c;主机B要把数据要给主机C&#xff0c;一定要经过一条路径选择&#xff0c;为什么经过路由器G后&…
阅读更多...
多线程面试相关

多线程面试相关

线程基础知识 线程与进程的区别 并行和并发的区别 创建线程的方式 Runnable和Callable有什么区别 run()方法和start()方法的区别 小结 线程包含哪些状态&#xff0c;各个状态之间如何变化 线程按顺序执行 notify()和notifyAll()的区别 Java中的wait方法和sleep方法的不同 如何…
阅读更多...
Unity + Firebase + GoogleSignIn 导入问题

Unity + Firebase + GoogleSignIn 导入问题

我目前使用 Unity版本&#xff1a;2021.3.33f1 JDK版本为&#xff1a;1.8 Gradle 版本为&#xff1a;6.1.1 Firebase 版本: 9.6.0 Google Sign In 版本为&#xff1a; 1.0.1 问题1 &#xff1a;手机点击登录报错 apk转化成zip&#xff0c;解压&#xff0c;看到/lib/armeabi-v…
阅读更多...
【Rust自学】11.10. 集成测试

【Rust自学】11.10. 集成测试

喜欢的话别忘了点赞、收藏加关注哦&#xff08;加关注即可阅读全文&#xff09;&#xff0c;对接下来的教程有兴趣的可以关注专栏。谢谢喵&#xff01;(&#xff65;ω&#xff65;) 11.10.1. 什么是集成测试 在Rust里&#xff0c;集成测试完全位于被测试库的外部。集成测试…
阅读更多...
【Word_笔记】Word的修订模式内容改为颜色标记

【Word_笔记】Word的修订模式内容改为颜色标记

需求如下&#xff1a;请把修改后的部分直接在原文标出来&#xff0c;不要采用修订模式 步骤1&#xff1a;打开需要转换的word后&#xff0c;同时按住alt和F11 进入&#xff08;Microsoft Visual Basic for Appliations&#xff09; 步骤2&#xff1a;插入 ---- 模块 步骤3&…
阅读更多...
深入Android架构(从线程到AIDL)_21 IPC的Proxy-Stub设计模式03

深入Android架构(从线程到AIDL)_21 IPC的Proxy-Stub设计模式03

目录 3、包裝IBinder接口 -- 使用Proxy-Stub设计模式 EIT造型的双层组合 4、 谁来写Proxy及Stub类呢? -- 地头蛇(App开发者)自己写 范例 定义一个新接口&#xff1a; IPlayer 撰写一个Stub类&#xff1a; PlayerStub 撰写mp3Binder类 撰写mp3RemoteService类 3、…
阅读更多...
数据在内存的存储

数据在内存的存储

数据类型介绍 前面我们已经学习了基本的内置类型&#xff1a; char //字符数据类型 1字节 打印%c short //短整型 2字节 打印%hd int //整形 4字节 打印%d long long int //长整型 4/8字节 打印%ld l…
阅读更多...
springboot 默认的 mysql 驱动版本

springboot 默认的 mysql 驱动版本

本案例以 springboot 3.1.12 版本为例 <parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>3.1.12</version><relativePath/> </parent> 点击 spring-…
阅读更多...
直流无刷电机控制（FOC）：电流模式

直流无刷电机控制（FOC）：电流模式

目录 概述 1 系统框架结构 1.1 硬件模块介绍 1.2 硬件实物图 1.3 引脚接口定义 2 代码实现 2.1 软件架构 2.2 电流检测函数 3 电流环功能实现 3.1 代码实现 3.2 测试代码实现 4 测试 概述 本文主要介绍基于DengFOC的库函数&#xff0c;实现直流无刷电机控制&#x…
阅读更多...
在 Flownex 网络中创建传热元件

在 Flownex 网络中创建传热元件

本文探讨了在 Flownex 仿真中集成新传热元件的步骤&#xff0c;以增强热系统管理能力。 了解 Flownex 中的传热元件 Flownex 中的传热元件复制传导、对流和辐射&#xff0c;从而深入了解系统的热流体行为。准确建模复杂系统需要了解这些单元的特性和功能。Flownex 的高级功能…
阅读更多...
《OpenCV计算机视觉实战项目》——银行卡号识别

《OpenCV计算机视觉实战项目》——银行卡号识别

文章目录 项目任务及要求项目实现思路项目实现及代码导入模块设置参数对模版图像中数字的定位处理银行卡的图像处理读取输入图像&#xff0c;预处理找到数字边框使用模版匹配&#xff0c;计算匹配得分 画出并打印结果 项目任务及要求 任务书&#xff1a; 要为某家银行设计一套…
阅读更多...
PyCharm文档管理

PyCharm文档管理

背景&#xff1a;使用PyCharmgit做文档管理 需求&#xff1a;需要PyCharm自动识别docx/xslx/vsdx等文件类型&#xff0c;并在PyCharm内点击文档时唤起系统内关联应用(如word、excel、visio) 设置步骤&#xff1a; 1、file -》 settings -》file types 2、在Files opened i…
阅读更多...
景联文科技提供高质量多模态数据处理服务，驱动AI新时代

景联文科技提供高质量多模态数据处理服务，驱动AI新时代

在当今快速发展的AI时代&#xff0c;多模态数据标注成为推动人工智能技术进步的关键环节。景联文科技作为行业领先的AI数据服务提供商&#xff0c;专注于为客户提供高质量、高精度的多模态数据标注服务&#xff0c;涵盖图像、语音、文本、视频及3D点云等多种类型的数据。通过专…
阅读更多...
django基于Python的智能停车管理系统

django基于Python的智能停车管理系统

1.系统概述 1.定义&#xff1a;Django 基于 Python 的智能停车管理系统是一个利用 Django 框架构建的软件系统&#xff0c;用于高效地管理停车场的各种事务&#xff0c;包括车辆进出记录、车位预订、收费管理等诸多功能。 2.目的&#xff1a;它的主要目的是提高停车场的运营效…
阅读更多...
【Rust】切片类型

【Rust】切片类型

目录 思维导图 1. 切片类型概述 2. 函数示例&#xff1a;获取字符串中的第一个单词 2.1 问题描述 2.2 初步实现 2.3 代码实现 3. 切片的引入 3.1 切片的定义 3.2 切片的优势 3.3 改进后的函数 4. 函数参数的通用性 4.1 改进函数签名 4.2 示例代码 5. 其他切片类型…
阅读更多...
微信小程序-Docker+Nginx环境配置业务域名验证文件

微信小程序-Docker+Nginx环境配置业务域名验证文件

在实际开发或运维工作中&#xff0c;我们时常需要在 Nginx 部署的服务器上提供一个特定的静态文件&#xff0c;用于域名验证或第三方平台验证。若此时使用 Docker 容器部署了 Nginx&#xff0c;就需要将该验证文件正确地映射&#xff08;挂载&#xff09;到容器中&#xff0c;并…
阅读更多...
鸿蒙UI（ArkUI-方舟UI框架）

鸿蒙UI（ArkUI-方舟UI框架）

参考&#xff1a;https://developer.huawei.com/consumer/cn/doc/harmonyos-guides-V13/arkts-layout-development-overview-V13 ArkUI简介 ArkUI&#xff08;方舟UI框架&#xff09;为应用的UI开发提供了完整的基础设施&#xff0c;包括简洁的UI语法、丰富的UI功能&#xff…
阅读更多...
图片和短信验证码（头条项目-06）

图片和短信验证码（头条项目-06）

1 图形验证码接口设计 将后端⽣成的图⽚验证码存储在redis数据库2号库。 结构&#xff1a; {img_uuid:0594} 1.1 创建验证码⼦应⽤ $ cd apps $ python ../../manage.py startapp verifications # 注册新应⽤ INSTALLED_APPS [django.contrib.admin,django.contrib.auth,…
阅读更多...
SpringCloud系列教程：微服务的未来（十二）OpenFeign连接池、最佳实践、日志、微服务拆分

SpringCloud系列教程：微服务的未来（十二）OpenFeign连接池、最佳实践、日志、微服务拆分

本篇博客将讨论如何优化 OpenFeign 的连接池配置&#xff0c;如何使用最佳实践提升服务间通信的效率和可维护性&#xff0c;并探讨如何通过拆分服务来提升微服务架构的灵活性和可扩展性&#xff0c;具体涵盖了用户服务和交易服务的拆分。 目录 前言 OpenFeign 连接池 最佳实…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023