当前位置: 首页 > news >正文

MySQL数据库——SQL优化(1)-介绍、插入数据、主键优化

目录

介绍

插入数据

Insert

大批量插入数据

主键优化

数据组织方式

页分裂

页合并

索引设计原则


介绍

SQL优化将分为下面几个部分进行学习:

  • 插入数据
  • 主键优化
  • order by优化
  • group by优化
  • limit优化
  • count优化
  • update优化

首先就先来看第一方面,

插入数据

Insert

如果我们需要一次性往数据库表中插入多条记录,可以从以下三个方面进行优化。

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');

优化方案一

批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

优化方案二

手动控制事务

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

优化方案三

主键顺序插入,性能要高于乱序插入。

1   主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
2   主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录),使用insert语句插入性能较低,此时可以使
用MySQL数据库提供的load指令进行插入。操作如下:

可以按照我们指定的格式 ,批量插入数据。

-- 客户端连接服务端时,加上参数 -–local-infile
mysql –-local-infile -u root -p-- 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;-- 执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.log'   -- 本地文件路径
into table tb_user fields terminated by ','  -- 字段间分隔符
lines terminated by '\n' ;                   -- 行间分隔符

在load时,主键顺序插入性能高于乱序插入。

主键优化

在上面提到,主键顺序插入的性能是要高于乱序插入的。 下面就来介绍一下具体的原因,然后再分析一下主键又该如何设计。

数据组织方式

在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表
(index organized table IOT)


行数据,都是存储在聚集索引的叶子节点上的。

我们之前也看过InnoDB的逻辑结构图:

在InnoDB引擎中,

数据行是记录在逻辑结构 page 页中的,而每一个页的大小是固定的,默认16K。
那也就意味着, 一个页中所存储的行也是有限的,如果插入的数据行row在该页存储不小,将会存储到下一个页中,页与页之间会通过指针连接。

页分裂

页可以为空,可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据过大,会行溢出),根据主键排列。

主键顺序插入效果

 

主键按乱序插入效果

假如1#,2#页都已经写满了,存放了如图所示的数据

此时再插入id为50的记录,不会再开启一个新的页然后插入

因为索引结构的叶子节点是有顺序的。按照顺序,应该存储在47之后。 

但是47所在的1#页已经写满了,存储不了50对应的数据了。

那么此时会开辟一个新的页 3#,但是并不会直接将50存入3#页,而是会将1#页后一半的数据,移动到3#页,然后在3#页,插入50。

移动数据,并插入id为50的数据之后,那么此时,这三个页之间的数据顺序是有问题的。

1#的下一个页,应该是3#, 3#的下一个页是2#。 所以,此时,需要重新设置链表指针。

 上述的这种现象,就称之为 "页分裂",是比较耗费性能的操作。

页合并

目前表中已有数据的索引结构(叶子节点)如下:

当我们对已有数据进行删除时,具体的效果如下:
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。 

当我们删除2#的数据记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

删除数据,并将页合并之后,若再次插入新的数据20,则直接插入3#页 

这个里面所发生的合并页的这个现象,就称之为 "页合并"

MERGE_THRESHOLD:合并页的阈值,可以自己设置,在创建表或者创建索引时指定。 

索引设计原则

  • 满足业务需求的情况下,尽量降低主键的长度。
  • 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。
  • 尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
  • 业务操作时,避免对主键的修改。

UUID是国际标准化组织(ISO)提出的一个概念。用来识别属性类型,在所有空间和时间上被视为唯一的标识。

UUID是基于当前时间、计数器(counter)和硬件标识(通常为无限网卡的MAC地址)等数据计算生成的。

可以保证这个值是真正唯一的任何地方产生的任意一个UUID都不会有相同的值。


END 


学习自:黑马程序员——MySQL数据库课程

相关文章:

MySQL数据库——SQL优化(1)-介绍、插入数据、主键优化

目录 介绍 插入数据 Insert 大批量插入数据 主键优化 数据组织方式 页分裂 页合并 索引设计原则 介绍 SQL优化将分为下面几个部分进行学习: 插入数据主键优化order by优化group by优化limit优化count优化update优化 首先就先来看第一方面, 插…...

Flink---10、处理函数(基本处理函数、按键分区处理函数、窗口处理函数、应用案例TopN、侧输出流)

星光下的赶路人star的个人主页 我的敌手就是我自己,我要他美好到能使我满意的程度 文章目录 1、处理函数1.1 基本处理函数(ProcessFunction)1.1.1 处理函数的功能和使用1.1.2 ProcessFunction解析1.1.3 处理函数的分类 1.2 按键分区处理函数&…...

多种方案教你彻底解决mac npm install -g后仍然不行怎么办sudo: xxx: command not found

问题概述 某些时候我们成功执行了npm install -g xxx,但是执行完成以后,使用我们全局新安装的包依然不行,如何解决呢? 解决方案1: step1: 查看npm 全局文件安装地址 XXXCN_CXXXMD6M ~ % npm list -g …...

斐波那契数列 JS

问题: 给出一个数字,找出它是斐波那契数列中的第几个数 斐波那契数列 [1, 1, 2, 3, 5, 8, 13, ...],后一个数字是前两个数字之和 输入的数字大于等于 2 如果输入数字不存于斐波那契数列中,返回 -1 function demo(num) {//初始数据…...

IP 地址的分类

IP地址是用于标识计算机或设备在互联网上的位置的一种地址。IP地址通常根据其范围和用途分为不同的分类,主要包括以下几种: IPv4地址(Internet Protocol version 4): IPv4地址是32位二进制数,通常以点分十…...

CDN网络基础入门:CDN原理及架构

背景 互联网业务的繁荣让各类门户网站、短视频、剧集观看、在线教育等内容生态快速发展,互联网流量呈现爆发式增长,自然也面临着海量内容分发效率上的挑战,那么作为终端用户,我们获取资源的体验是否有提升呢? 答案是…...

李沐深度学习记录2:10多层感知机

一.简要知识记录 x.numel():看向量或矩阵里元素个数 A.sum():向量或矩阵求和,axis参数可对某维度求和,keepdims参数设置是否保持维度不变 A.cumsum:axis参数设置沿某一维度计算矩阵累计和x*y:向量的按元素乘法 torch.…...

Python标准库中内置装饰器@staticmethod@classmethod

装饰器是Python中强大而灵活的功能,用于修改或增强函数或方法的行为。装饰器本质上是一个函数,它接受另一个函数作为参数,并返回一个新的函数,通常用于在不修改原始函数代码的情况下添加额外的功能或行为。这种技术称为元编程&…...

MySQL8 间隙锁在11种情况下的锁持有情况分析

测试环境及相关必要知识 测试环境为mysql 8 版本 间隙锁(Gap Lock):用于锁定索引范围之间的间隙,防止其他事务在此间隙中插入新记录。间隙锁主要用于防止幻读问题。 在可重复读的隔离级别下默认打开该锁机制,解决幻…...

C# 图片按比例进行压缩

1、对图片进行压缩,保存在本地 对于一个200k的png文件按0.6的缩放比例进行压缩,压缩后的大小为20k左右 对于一个80k的jpg文件按0.6的缩放比例压缩,压缩后为13k左右 public void imageZoom(string name, Double zoomScale){Bitmap btImage …...

猜猜 JavaScript 输出:(! + [] + [] + ![]).length

一起猜 最近看到一个很有意思的题,直接来看,下面这段代码的打印结果是什么? console.log((! [] [] ![]).length) 猜猜看,你的答案是什么,打在评论区。 我的答案是 undefined,正如我的英文名 为什么呢&a…...

MTK Android12静默安装接口

该文档就是在android12系统上提供一个广播接收器,app端发送一个广播,并且带入apk的地址就可以实现安装 1、广播注册 frameworks\base\services\core\java\com\android\server\policy\PhoneWindowManager.java 首先要导入的依赖 import android.app.P…...

基于电容电流前馈与电网电压全前馈的三相LCL并网逆变器谐波抑制Simulink仿真

💥💥💞💞欢迎来到本博客❤️❤️💥💥 🏆博主优势:🌞🌞🌞博客内容尽量做到思维缜密,逻辑清晰,为了方便读者。 ⛳️座右铭&a…...

Python数据攻略-Pandas与统计数据分析

统计学在数据分析中到底有多重要?在数据分析的世界里,统计学扮演着一角色。想象一下你是《三国志》游戏的数据分析师,任务是找出哪个武将最受玩家欢迎,哪些战役最具挑战性等。 你怎么做呢?这就需要统计学的力量了。 文章目录 基础统计方法描述性统计方差和标准差相关性和…...

【gcc】RtpTransportControllerSend学习笔记 1

本文是大神 https://www.cnblogs.com/ishen 的文章的学习笔记。主要是大神文章: webrtc源码分析(8)-拥塞控制(上)-码率预估 的学习笔记。大神的webrtc源码分析(8)-拥塞控制(上)-码率预估 详尽而具体,堪称神作。因为直接看大神的文章,自己啥也没记住,所以同时跟着看代码。跟…...

若依分离版-前端使用

1 执行 npm install --registryhttps://registry.npm.taobao.org,报错信息如下 npm ERR! code ERESOLVE npm ERR! ERESOLVE unable to resolve dependency tree npm ERR! npm ERR! While resolving: ktg-mes-ui3.8.2 npm ERR! Found: vue2.6.12 npm ERR! node_modu…...

微信小程序-2

微信开发文档 https://developers.weixin.qq.com/miniprogram/dev/framework/ 一、app.js中的生命周期函数与globalData(全局变量) 指南 - - - 小程序框架 - - - 注册小程序 删除app.js里的东西,输入App回车,调用生命周期 选项 - - - 重新打开此项目…...

卷积神经网络的发展历史-ResNet

ResNet的产生 2015 年,Kaiming He 提出了ResNet(拿到了 2016 年 CVPR Best Paper Award),不仅解决了神经网络中的退化问题还在同年的ILSVRC和COCO 竞赛横扫竞争对手,分别拿下分类、定位、检测、分割任务的第一名。 R…...

基于瞬时无功功率ip-iq的谐波信号检测Simulink仿真

💥💥💞💞欢迎来到本博客❤️❤️💥💥 🏆博主优势:🌞🌞🌞博客内容尽量做到思维缜密,逻辑清晰,为了方便读者。 ⛳️座右铭&a…...

node安装,nvm管理器

一、下载nvm,nvm-setup.exe https://github.com/coreybutler/nvm-windows/releases 二、配置NodeJS下载代理镜像(可选) 可以在NVM安装根目录下的setting.txt文件中,配置NodeJS下载代理镜像,解决在线安装NodeJS时速度…...

观成科技:隐蔽隧道工具Ligolo-ng加密流量分析

1.工具介绍 Ligolo-ng是一款由go编写的高效隧道工具,该工具基于TUN接口实现其功能,利用反向TCP/TLS连接建立一条隐蔽的通信信道,支持使用Let’s Encrypt自动生成证书。Ligolo-ng的通信隐蔽性体现在其支持多种连接方式,适应复杂网…...

(二)TensorRT-LLM | 模型导出(v0.20.0rc3)

0. 概述 上一节 对安装和使用有个基本介绍。根据这个 issue 的描述,后续 TensorRT-LLM 团队可能更专注于更新和维护 pytorch backend。但 tensorrt backend 作为先前一直开发的工作,其中包含了大量可以学习的地方。本文主要看看它导出模型的部分&#x…...

Linux相关概念和易错知识点(42)(TCP的连接管理、可靠性、面临复杂网络的处理)

目录 1.TCP的连接管理机制(1)三次握手①握手过程②对握手过程的理解 (2)四次挥手(3)握手和挥手的触发(4)状态切换①挥手过程中状态的切换②握手过程中状态的切换 2.TCP的可靠性&…...

【android bluetooth 框架分析 04】【bt-framework 层详解 1】【BluetoothProperties介绍】

1. BluetoothProperties介绍 libsysprop/srcs/android/sysprop/BluetoothProperties.sysprop BluetoothProperties.sysprop 是 Android AOSP 中的一种 系统属性定义文件(System Property Definition File),用于声明和管理 Bluetooth 模块相…...

分布式增量爬虫实现方案

之前我们在讨论的是分布式爬虫如何实现增量爬取。增量爬虫的目标是只爬取新产生或发生变化的页面,避免重复抓取,以节省资源和时间。 在分布式环境下,增量爬虫的实现需要考虑多个爬虫节点之间的协调和去重。 另一种思路:将增量判…...

HashMap中的put方法执行流程(流程图)

1 put操作整体流程 HashMap 的 put 操作是其最核心的功能之一。在 JDK 1.8 及以后版本中,其主要逻辑封装在 putVal 这个内部方法中。整个过程大致如下: 初始判断与哈希计算: 首先,putVal 方法会检查当前的 table(也就…...

【SSH疑难排查】轻松解决新版OpenSSH连接旧服务器的“no matching...“系列算法协商失败问题

【SSH疑难排查】轻松解决新版OpenSSH连接旧服务器的"no matching..."系列算法协商失败问题 摘要: 近期,在使用较新版本的OpenSSH客户端连接老旧SSH服务器时,会遇到 "no matching key exchange method found"​, "n…...

计算机基础知识解析:从应用到架构的全面拆解

目录 前言 1、 计算机的应用领域:无处不在的数字助手 2、 计算机的进化史:从算盘到量子计算 3、计算机的分类:不止 “台式机和笔记本” 4、计算机的组件:硬件与软件的协同 4.1 硬件:五大核心部件 4.2 软件&#…...

从“安全密码”到测试体系:Gitee Test 赋能关键领域软件质量保障

关键领域软件测试的"安全密码":Gitee Test如何破解行业痛点 在数字化浪潮席卷全球的今天,软件系统已成为国家关键领域的"神经中枢"。从国防军工到能源电力,从金融交易到交通管控,这些关乎国计民生的关键领域…...

6️⃣Go 语言中的哈希、加密与序列化:通往区块链世界的钥匙

Go 语言中的哈希、加密与序列化:通往区块链世界的钥匙 一、前言:离区块链还有多远? 区块链听起来可能遥不可及,似乎是只有密码学专家和资深工程师才能涉足的领域。但事实上,构建一个区块链的核心并不复杂,尤其当你已经掌握了一门系统编程语言,比如 Go。 要真正理解区…...