当前位置: 首页 > 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时速度…...

2021-03-15 iview一些问题

1.iview 在使用tree组件时,发现没有set类的方法,只有get,那么要改变tree值,只能遍历treeData,递归修改treeData的checked,发现无法更改,原因在于check模式下,子元素的勾选状态跟父节…...

Java面试专项一-准备篇

一、企业简历筛选规则 一般企业的简历筛选流程:首先由HR先筛选一部分简历后,在将简历给到对应的项目负责人后再进行下一步的操作。 HR如何筛选简历 例如:Boss直聘(招聘方平台) 直接按照条件进行筛选 例如&#xff1a…...

Angular微前端架构:Module Federation + ngx-build-plus (Webpack)

以下是一个完整的 Angular 微前端示例,其中使用的是 Module Federation 和 npx-build-plus 实现了主应用(Shell)与子应用(Remote)的集成。 🛠️ 项目结构 angular-mf/ ├── shell-app/ # 主应用&…...

【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 软件&#…...

关于uniapp展示PDF的解决方案

在 UniApp 的 H5 环境中使用 pdf-vue3 组件可以实现完整的 PDF 预览功能。以下是详细实现步骤和注意事项&#xff1a; 一、安装依赖 安装 pdf-vue3 和 PDF.js 核心库&#xff1a; npm install pdf-vue3 pdfjs-dist二、基本使用示例 <template><view class"con…...

Leetcode33( 搜索旋转排序数组)

题目表述 整数数组 nums 按升序排列&#xff0c;数组中的值 互不相同 。 在传递给函数之前&#xff0c;nums 在预先未知的某个下标 k&#xff08;0 < k < nums.length&#xff09;上进行了 旋转&#xff0c;使数组变为 [nums[k], nums[k1], …, nums[n-1], nums[0], nu…...

轻量级Docker管理工具Docker Switchboard

简介 什么是 Docker Switchboard &#xff1f; Docker Switchboard 是一个轻量级的 Web 应用程序&#xff0c;用于管理 Docker 容器。它提供了一个干净、用户友好的界面来启动、停止和监控主机上运行的容器&#xff0c;使其成为本地开发、家庭实验室或小型服务器设置的理想选择…...

k8s从入门到放弃之Pod的容器探针检测

k8s从入门到放弃之Pod的容器探针检测 在Kubernetes&#xff08;简称K8s&#xff09;中&#xff0c;容器探测是指kubelet对容器执行定期诊断的过程&#xff0c;以确保容器中的应用程序处于预期的状态。这些探测是保障应用健康和高可用性的重要机制。Kubernetes提供了两种种类型…...

【HTML】HTML 与 CSS 基础教程

作为 Java 工程师&#xff0c;掌握 HTML 和 CSS 也是需要的&#xff0c;它能让你高效与前端团队协作、调试页面元素&#xff0c;甚至独立完成简单页面开发。本文将用最简洁的方式带你掌握核心概念。 一、HTML&#xff0c;网页骨架搭建 核心概念&#xff1a;HTML通过标签定义内…...