面向面试知识--MySQL数据库与索引
面向面试知识–MySQL数据库与索引
优化难点与面试点
什么是MySQL索引?
索引的MySQL官方定义:索引是帮助MySQL快速获取数据的数据结构。
动力节点原文:
MysQL官方对于索引的定义:索引是帮助MySQL高效获取数据的数据结构。
MysQL在存储数据之外,数据库系统中还维护着满足特定查找算法的数据结构,这些数据结构以某种引用(指向)表中的数据,这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。
简单理解为:“排好序的,帮助我们快速查找数据的数据结构”
索引的分类
逻辑分类:
按照功能划分
- 主键索引:一张表只能有一个主键索引,不允许重复、不允许为null;
- 唯一索引:数据列不允许重复,允许为 NULL 值,一张表可有多个唯一索引,索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
- 普通索引:一张表可以创建多个普通索引,一个普通索引可以包含多个字段,允许数据重复,允许 NULL 值插入;(阿里开发者手册:高并发场景的表,索引数量控制在五个以内)
- 全文索引:它查找的是文本中的关键词,主要用于全文检索。(篇幅较长,下文有独立主题说明)
按照列数划分
- 单列索引:一个索引只包含一个列,一个表可以有多个单例索引。
- 组合索引:一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则,即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。(
索引失效的坑
)
物理分类:
聚簇索引
聚簇是为了提高某个属性(或属性组)的查询速度,把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。
聚簇索引(clustered index)不是单独的一种索引类型,而是一种数据存储方式。这种存储方式是依靠B+树来实现的,根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时,方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块,找到索引也就找到了数据。
优点
(查询和更新快)
- 数据访问速度快,因为聚簇索引将索引和数据都放在同一棵B+树,因此聚簇索引中获取数据比飞聚簇索引更快;
- 聚簇索引对于主键的排序查找和范围查找速度非常快。
缺点
(删除和插入慢) - 插入速度严重依赖于插入顺序;插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键(主键列不要选没有意义的自增列,选经常查询的条件列才好,不然无法体现其主键索引性能);
- 更新主键代价很高(不推荐更改主键);更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。
- 二级索引需要两次索引查找(回表);二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
补充:MySQL中,key、primary key、unique key、与index的区别。
详见文章一文搞懂MySQL索引(清晰明了)
非聚簇索引
数据和索引是分开的,B+树叶子节点存放的不是数据表的行记录。
虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引,但是只有InnoDB的主键索引才是聚簇索引,InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。
InnoDB和MyISAM索引实现,索引的数据结构
InnoDB索引实现
InnoDB使用B+树存储数据,除了主键索引为聚簇索引,其他索引均为非聚簇索引。
一个表中只能存在一个聚簇索引(主键索引),但是可以存在多个非聚簇索引。
InnoDB表和索引的数据是在一起的,表数据和索引的文件都放在.ibd
文件中。
聚簇索引(主键索引)
B+树叶子节点包含数据表中行记录就是聚簇索引(索引和数据是存放在一块的)
可以看到叶子节点包含了完整的数据记录,这就是聚簇索引。因为InnoDB的数据文件(.idb)按主键聚集,所以InnoDB必须有主键(MyISAM可以没有),如果没有显示指定主键,则选取首个为唯一且非空的列作为主键索引,如果还没具备,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
;
主键索引结构分析:
数据保存以key-value的形式保存。
B+树:所有的数据都存放在叶子结点,非叶子结点只保存键值方便查找;
B树:所有的结点都保存数据;
对于MySQL中的InnoDB数据库引擎的主索引,索引即数据,数据即索引。方便范围查找以及顺序查找;同时存在普通索引,帮助查找某些被查找频率较高的字段。如果需要较多索引,推荐使用联合索引,以提高索引的利用效率。
索引的优缺点
参考文章:一文搞懂MySQL索引(清晰明了)
MySQL体系结构:
自上而下分为四层:
① 网络接入层
② 服务层
③ 存储引擎层
④ 文件系统层
网络接入层
提供了应用程序进入MySQL服务的接口。客户端与服务器建立连接,客户端发送SQL到服务端。
服务层
管理工具和服务
系统管理和控制工具,例如备份恢复、MySQL复制、集群等。
连接池
主要负责连接管理、授权认证、安全等。
主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池,避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时,服务器对其进行认证。可以通过用户名与密码认证,也可以通过SSL证书进行认证。登录认证后,服务器还会验证客户端是否有执行某个查询的操作权限。
SQL接口
查询解析器
查询优化器
缓存(8.0以前支持查询缓存,8.0之后就不支持了)
存储引擎层
索引是占用物理空间的,在不同的的存储引擎中,索引存在的文件也不同。存储引擎是给予表的,以下是分别使用MyISAM和InnoDB分别建立的两张表。
当存储引擎是MyISAM时:
- *.frm:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等
- *.MYD:MyISAM DATA,用于存储MyISAM表的数据
- *.MYI:MyISAM INDEX,用于存储MyISAM表的索引信息
当是InnoDB时,
文件系统层
*.frm:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等
- *.ibd:InnoDB DATA,表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引,叶子节点存储索引和索引对应的数据
参考文章:10分钟了解MySQL体系构架、存储引擎和索引结构
相关文章:

面向面试知识--MySQL数据库与索引
面向面试知识–MySQL数据库与索引 优化难点与面试点 什么是MySQL索引? 索引的MySQL官方定义:索引是帮助MySQL快速获取数据的数据结构。 动力节点原文: MysQL官方对于索引的定义:索引是帮助MySQL高效获取数据的数据结构。 MysQL在存储数据之…...
portainer + portainer/agent
参考链接 https://docs.portainer.io/ portainer 免费版 portainer-ce 免费版 portainer-ee 企业版 portainer-agent docker本机代理 agent 下载地址 https://download.csdn.net/download/a309450028a/87451332 portainer 下载地址 https://download.csdn…...
C# 截取字符串
在 C# 中,可以使用 Substring 方法来截取字符串的一部分。该方法有两个参数:起始索引和要截取的字符数。 以下是使用 Substring 方法截取字符串的示例: string str "Hello World"; string result str.Substring(6); // 从索引为…...

FOXBORO FBM233 P0926GX控制脉冲模块
FOXBORO FBM233 P0926GX 是一种控制脉冲模块,通常用于工业自动化和控制系统中。这个模块的主要功能是生成和控制脉冲信号,以用于执行特定的操作或控制过程。以下是可能适用于 FOXBORO FBM233 P0926GX 控制脉冲模块的一些常见特点: 脉冲生成&a…...

MySQL性能优化——MYSQL执行流程
MySQL 执行流程1-5如下图。 MySQL 的架构共分为两层:Server 层和存储引擎层, Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现,主要包括连接器,查询缓存、解析器、预处理器、优化器、执行器等。…...

Django:四、Djiango如何连接使用MySQL数据库
一、安装数据库第三方插件 安装下载mysql第三方插件 pip install mysqlclient 二、创建MySQL数据库 ORM可以帮助我们做两件事: 创建、修改、删除数据库中的表(不用写SQL语句),但无法创建数据库操作表中的数据(不用…...

LeetCode 热题 100(八):贪心。121. 买卖股票的最佳时机、45. 跳跃游戏 II
题目一: 121. 买卖股票的最佳时机https://leetcode.cn/problems/best-time-to-buy-and-sell-stock/ 思路:因为时间复杂度O(n),所以使用贪心来做。类似双指针,一个指针记录到当前循环时最小的股票价格&…...

第N个数字
给你一个整数 n ,请你在无限的整数序列 [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, …] 中找出并返回第 n 位上的数字。 我觉得这题是哪以理解的 看这个题解 func findNthDigit(n int) int {digit : 1start : 1count : 9for n > count {n - countdigitstart start …...

【适用于电力系统和音频系统】计算信号的总谐波失真 (THD)(Matlab代码实现)
💥💥💞💞欢迎来到本博客❤️❤️💥💥 🏆博主优势:🌞🌞🌞博客内容尽量做到思维缜密,逻辑清晰,为了方便读者。 ⛳️座右铭&a…...
kubernetes(k8s)PVC
概念 PVC 的全称是:PersistentVolumeClaim(持久化卷声明),PVC 是用户存储的一种声明,PVC 和 Pod 比较类似,Pod 消耗的是节点,PVC 消耗的是 PV 资源,Pod 可以请求 CPU 和内存&#x…...

Android ANR问题触发机制
1 Anr类型 Anr一般有四种类型。 1.1 input dispatching timeout 主要时按键或触摸屏事件在5s内没有响应。这个时间在ActivityManagerService中定义。 C:\Users\wangjie\AppData\Local\Android\Sdk\sources\android-32\com\android\server\am\ActivityManagerService.…...

解决jupyter找不到虚拟环境的问题
解决jupyter找不到虚拟环境的问题 使用jupyter只能使用base环境,不能找到自己创建的虚拟环境。如下图,显示的默认的虚拟环境base的地址。 如何解决这个问题?需要两个步骤即可 1 . 在base环境中安装nb_conda_kernels这个库 activate base c…...

Unity丨移动相机朝向目标并确定目标在摄像机可视范围内丨摄像机注释模型丨摄像机移动丨不同尺寸模型优化丨
文章目录 问题描述功能展示技术细节小结 问题描述 本文提供的功能是摄像机朝向目标移动,并确定整个目标出现在摄像机视角内,针对不同尺寸的模型优化。 功能展示 提示:这里可以添加技术名词解释 技术细节 直接上代码 using UnityEngine;…...

排序算法:归并排序(递归和非递归)
朋友们、伙计们,我们又见面了,本期来给大家解读一下有关排序算法的相关知识点,如果看完之后对你有一定的启发,那么请留下你的三连,祝大家心想事成! C 语 言 专 栏:C语言:从入门到精通…...

数据可视化
一、Flask介绍 #通过访问路径,获取用户的字符串参数 app.route(/user/<name>) def welcome(name):return "你好,%s"%nameapp.route(/user/<int:id>) def welcome2(id):return "你好,%d号的会员"%id能够自动…...

Go并发可视化解释 – select语句
上周,我发布了一篇关于如何直观解释Golang中通道(Channel)的文章。如果你对通道仍然感到困惑,请先查看那篇文章。 Go并发可视化解释 — Channel 作为一个快速复习:Partier、Candier和Stringer经营着一家咖啡店。Partie…...

http的网站进行访问时候自动跳转至https
通常情况下我们是用的都是http的路径,对于https的使用也很少,但是随着https的普及越来越多的域名访问需要用到https的,这个我们就演示怎么设置在我们对一个http的网站进行访问时候自动跳转至https下。 用到的工具及软件: 系统:wi…...

realloc
目录 前提须知: 函数介绍: 函数原型: 使用realloc: realloc在调整内存空间的是存在两种情况/使用realloc为扩大空间的两种情况 1.是剩下的没有被分配的空间足够 2 .剩下没有被分配的空间不够了 注意事项: rea…...

Windows AD域使用Linux Samba
Windows AD域使用Linux Samba 1. 初始化配置 1.1 初始化配置 配置服务器名 hostnamectl set-hostname samba.sh.pana.cnhosts文件配置,确保正常解析到本机和域控 [rootcentos7 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.loc…...

Scrapy+Selenium自动化获取个人CSDN文章质量分
前言 本文将介绍如何使用Scrapy和Selenium这两个强大的Python工具来自动获取个人CSDN文章的质量分数。我们将详细讨论Scrapy爬虫框架的使用,以及如何结合Selenium浏览器自动化工具来实现这一目标。无需手动浏览每篇文章,我们可以轻松地获取并记录文章的…...

19c补丁后oracle属主变化,导致不能识别磁盘组
补丁后服务器重启,数据库再次无法启动 ORA01017: invalid username/password; logon denied Oracle 19c 在打上 19.23 或以上补丁版本后,存在与用户组权限相关的问题。具体表现为,Oracle 实例的运行用户(oracle)和集…...

安宝特方案丨XRSOP人员作业标准化管理平台:AR智慧点检验收套件
在选煤厂、化工厂、钢铁厂等过程生产型企业,其生产设备的运行效率和非计划停机对工业制造效益有较大影响。 随着企业自动化和智能化建设的推进,需提前预防假检、错检、漏检,推动智慧生产运维系统数据的流动和现场赋能应用。同时,…...
MVC 数据库
MVC 数据库 引言 在软件开发领域,Model-View-Controller(MVC)是一种流行的软件架构模式,它将应用程序分为三个核心组件:模型(Model)、视图(View)和控制器(Controller)。这种模式有助于提高代码的可维护性和可扩展性。本文将深入探讨MVC架构与数据库之间的关系,以…...

Keil 中设置 STM32 Flash 和 RAM 地址详解
文章目录 Keil 中设置 STM32 Flash 和 RAM 地址详解一、Flash 和 RAM 配置界面(Target 选项卡)1. IROM1(用于配置 Flash)2. IRAM1(用于配置 RAM)二、链接器设置界面(Linker 选项卡)1. 勾选“Use Memory Layout from Target Dialog”2. 查看链接器参数(如果没有勾选上面…...

自然语言处理——循环神经网络
自然语言处理——循环神经网络 循环神经网络应用到基于机器学习的自然语言处理任务序列到类别同步的序列到序列模式异步的序列到序列模式 参数学习和长程依赖问题基于门控的循环神经网络门控循环单元(GRU)长短期记忆神经网络(LSTM)…...
【学习笔记】深入理解Java虚拟机学习笔记——第4章 虚拟机性能监控,故障处理工具
第2章 虚拟机性能监控,故障处理工具 4.1 概述 略 4.2 基础故障处理工具 4.2.1 jps:虚拟机进程状况工具 命令:jps [options] [hostid] 功能:本地虚拟机进程显示进程ID(与ps相同),可同时显示主类&#x…...
聊一聊接口测试的意义有哪些?
目录 一、隔离性 & 早期测试 二、保障系统集成质量 三、验证业务逻辑的核心层 四、提升测试效率与覆盖度 五、系统稳定性的守护者 六、驱动团队协作与契约管理 七、性能与扩展性的前置评估 八、持续交付的核心支撑 接口测试的意义可以从四个维度展开,首…...

蓝桥杯3498 01串的熵
问题描述 对于一个长度为 23333333的 01 串, 如果其信息熵为 11625907.5798, 且 0 出现次数比 1 少, 那么这个 01 串中 0 出现了多少次? #include<iostream> #include<cmath> using namespace std;int n 23333333;int main() {//枚举 0 出现的次数//因…...

Reasoning over Uncertain Text by Generative Large Language Models
https://ojs.aaai.org/index.php/AAAI/article/view/34674/36829https://ojs.aaai.org/index.php/AAAI/article/view/34674/36829 1. 概述 文本中的不确定性在许多语境中传达,从日常对话到特定领域的文档(例如医学文档)(Heritage 2013;Landmark、Gulbrandsen 和 Svenevei…...
Android第十三次面试总结(四大 组件基础)
Activity生命周期和四大启动模式详解 一、Activity 生命周期 Activity 的生命周期由一系列回调方法组成,用于管理其创建、可见性、焦点和销毁过程。以下是核心方法及其调用时机: onCreate() 调用时机:Activity 首次创建时调用。…...