MYSQL八、MYSQL的SQL优化
一、SQL优化
- sql优化是指:通过对sql语句和数据库结构的调整,来提高数据库查询、插入、更新和删除等操作的性能和效率。
1、插入数据优化
- 要一次性往数据库表中插入多条记录:
insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');.....
1.1、优化方案一(批量
插入数据)
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
1.2、优化方案二(手动
控制事务)
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.3、优化方案三(主键顺序
插入)
主键顺序
插入,性能要高于乱序插入。
1.4、大批量插入数据
- 如果一次性需要插入大批量数据(比如: 几百万的记录),使用insert语句插入性能较低,此时可以使用
MySQL数据库提供的load指令
进行插入。
可以执行如下指令,将数据脚本文件中的数据加载到表结构中:
-- 客户端连接服务端时,加上参数 -–local-infilemysql –-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时,主键顺序插入性能高于乱序插入
2、主键优化
2.1、数据的组织方式
- 在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表。
- 行数据都是存储在聚集索引的叶子节点上的。
- 行数据都是存储在聚集索引的叶子节点上的。
- 在InnoDB引擎中,数据行是记录在逻辑结构 page 页中的,而每一个页的大小是固定的,默认16K。
- 那也就意味着一个页中所存储的行也是有限的,如果插入的数据行row在该页存储不小,将会存储到下一个页中,页与页之间会通过指针连接。
2.2、页分裂
- 页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据过大,会行溢出),根据主键排列。
- MySQL中的
页分裂
是指:当B+tree索引中的一个页已经满了,再插入新的记录时,该页会被分裂成两个页。其中,一些记录会被留在原来的页中,而其余记录则会被移到一个新的页中。- 页分裂是为了避免数据过度集中在一个页上而引起的性能问题。通过将记录分散到更多的页中,可以减少索引的深度,从而提高查询效率。
- 在MySQL中,当一个页满了之后,会触发页分裂。具体来说,MySQL会将该页中的记录按照顺序依次遍历,找到一个位置将其分为两个部分,并将后面的记录移到一个新的页中。同时,为了保证索引有序性,MySQL还会
将新页中的第一条记录的键值插入到父节点中
,并调整父节点中的指针。如果父节点也满了,则递归进行分裂操作。
- 页分裂通常会发生在以下情况:
- 顺序插入:当数据按索引顺序插入,且最后一页已满,会创建一个新的页并在其上继续插入。
- 乱序插入:如果数据插入到中间的某个索引位置,导致中间的页溢出,那么会把一部分数据移动到新页中,以便为新数据腾出空间。(可能还需要重新设置链表指针)
2.3、页合并
- 当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
- 当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
参数MERGE_THRESHOLD:是合并页的阈值,可以自己设置,在创建表或者创建索引时指定。
2.4、主键的设计原则
- 满足业务需求的情况下,尽量降低主键的长度。
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
- 业务操作时,避免对主键的修改。
3、order by优化
-
MySQL的排序,有两种方式:
- Using filesort : 通过表的索引或全表扫描,读取到满足条件的数据行,然后在排序缓冲区sort
buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
。 - Using index :
通过有序索引顺序扫描直接返回有序数据
,这种情况即为 using index,不需要额外排序,操作效率高。 - Using index的性能
高
,而Using filesort的性能低
- Using filesort : 通过表的索引或全表扫描,读取到满足条件的数据行,然后在排序缓冲区sort
-
order by优化原则:
- A. 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
- B. 尽量使用覆盖索引。
- C. 多字段排序, 一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
- D. 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
4、group by优化
- 在分组操作中通过以下两点进行优化,以提升性能:
- A. 在分组操作时,可以通过索引来提高效率。(没有使用索引进行分组Extra下会出现Using temporate(使用临时表),这个性能很低的)
- B. 分组操作时,索引的使用也是满足最左前缀法则的
5、limit优化
- 在数据量比较大时,如果进行limit分页查询,在查询时越往后的数据,分页查询效率越低。
- 优化思路: 一般分页查询时,通过创建 覆盖索引 能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。
6、count优化
- 如果数据量很大,在执行count操作时,是非常耗时的。
- MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高; 但是如果是带条件的count,MyISAM也慢。
- InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后
累积计数
。- 要大幅度提升InnoDB表的count效率,主要的优化思路:自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)
6.1、count函数的用法
- count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是NULL,累计值就加 1,否则不加,最后返回累计值。
- 用法:count(*)、count(主键)、count(字段)、count(数字)
- 用法:count(*)、count(主键)、count(字段)、count(数字)
按照效率排序的话,count(字段) < count(主键 id) < count(1) ≈ count(*),所以尽量使用 count(*)。
7、update优化
- 主要需要注意一下update语句执行时的注意事项:
- 在更新数据时,最好根据
索引字段
进行更新 (否则会出现行锁升级为表锁的问题,就是锁住整张表,导致并发性能降低)
- 在更新数据时,最好根据
-- id是主键索引
update course set name = 'javaEE' where id = 1 ;-- 此时执行上面的update语句只会出现行锁
InnoDB的行锁是针对索引加的锁,不是针对数据记录加的锁 ,并且该索引不能失效,否则会从行锁升级为表锁 。
相关文章:

MYSQL八、MYSQL的SQL优化
一、SQL优化 sql优化是指:通过对sql语句和数据库结构的调整,来提高数据库查询、插入、更新和删除等操作的性能和效率。 1、插入数据优化 要一次性往数据库表中插入多条记录: insert into tb_test values(1,tom); insert into tb_tes…...

鸿蒙轻内核M核源码分析系列二一 02 文件系统LittleFS
1、LFS文件系统结构体介绍 会分2部分来介绍结构体部分,先介绍LittleFS文件系统的结构体,然后介绍LiteOS-M内核中提供的和LittleFS相关的一些结构体。 1.1 LittleFS的枚举结构体 在openharmony/third_party/littlefs/lfs.h头文件中定义LittleFS的枚举、…...

【ARMv8/ARMv9 硬件加速系列 3 -- SVE 指令语法及编译参数详细介绍】
文章目录 SVE 汇编语法SVE 单通道谓词SVE 测试代码 SVE 软件和库支持SVE 编译参数配置-marcharmv8-alseprofilememtagsve2-aessve2-bitpermcryptosve2sve2-sha3sve2-sm4 SVE 汇编语法 在介绍 SVE 汇编指令语法之前,先介绍下如何判断自己所使用的芯片是否实现了SVE功…...

Java版+ SaaS应用+接口技术RESTful API 技术开发的智慧医院HIS系统源码 专注医院管理系统研发 支持二开
Java版 SaaS应用接口技术RESTful API WebSocket WebService技术开发的智慧医院HIS系统源码 专注医院管理系统研发 支持二开 医院住院管理系统(Hospital Information System简称HIS)是一门医学、信息、管理、计算机等多种学科为一体的边缘科学ÿ…...

工业机器人远程运维,增强智慧工厂运营管理
1、需求背景 随着工业自动化技术的普及和工业机器人应用的增加,制造业对于生产线稳定性和效率的要求不断提高。然而,传统的现场监控方式存在着地理位置限制、实时监控难度大以及诊断能力有限等问题,迫切需要一种更具灵活性和效率的监控方式。…...

理解Python的元类
1.type()函数 type 函数是一个内置函数,用来获取一个对象的类型。它可以接受一个参数,返回这个参数的数据类型。type也可以用来创建类,type就是元类 x333 list["ab"] tuple (1, "a", True, 3.14) dict {name: Alice,…...

web前端黑马下载:探索学习资源的海洋
web前端黑马下载:探索学习资源的海洋 在数字化时代,Web前端技术日益成为互联网行业的核心驱动力。为了跟上这一趋势,众多学习者纷纷投身于Web前端的学习之中。而在这个过程中,“黑马”作为一个备受瞩目的品牌,其Web前…...

最新版jd-gui下载
对于java开发的工程师来说,jd-gui应该是经常会用到的工具了 官网的jd-gui目前只支持到JAVA13,更新版本JAVA编译出来的JAR包就反编译不出来了 此版本支持到了JAVA23 如果需要win以外的其他版本,可以查看我的其他上传 如果不想花积分&#…...

(051)FPGA时钟--->(001)时钟介绍
(001)时钟介绍 1 目录 (a)FPGA简介 (b)Verilog简介 (c)时钟简介 (d)时钟介绍 (e)结束 1 FPGA简介 (a)FPGA(Field Programmable Gate Array)是在PAL (可编程阵列逻辑)、GAL(通用阵列逻辑)等可编程器件的基础上进一步发展的产物。它是作为专用集成电…...

Java程序员英语单词通关:
Java程序员英语单词通关: abstract - 抽象的 boolean - 布尔值 break - 打断 byte - 字节 case - 情况,实例 catch - 捕获 char - 字符 class - 类 continue - 继续 default - 默认,通常 do - 做,运行 double - 双精度…...

数据库开发-Mysql03
目录 1. 多表查询 1.1 概述 1.1.1 数据准备 1.1.2 介绍 1.1.3 分类 1.2 内连接 1.3 外连接 1.4 子查询 1.4.1 介绍 1.4.2 标量子查询 1.4.3 列子查询 1.4.4 行子查询 1.4.5 表子查询 1.5 案例 2. 事务 2.1 介绍 2.2 操作 2.3 四大特性 3. 索引 3.1 介绍 3…...

0-1 背包问题(动态规划 查询背包元素)
描述 给定n种物品和一个背包,物品i的重量是Wi,其价值为Vi,问如何选择装入背包的物品,使得装入背包的物品的总价值最大? 在选择装入背包的物品时,对每种物品i只能有两种选择,装入或者不装入…...

elasticsearch快照生成与恢复
Elasticsearch快照生成与恢复的场景主要涉及到数据的备份与恢复需求。当需要对Elasticsearch集群中的数据进行备份,或者在数据丢失、损坏等情况下需要恢复数据时,就可以使用快照功能。 快照生成的方法通常包括以下步骤: 1、创建一个快照仓库…...

178.二叉树:最大二叉树(力扣)
代码解决 /*** Definition for a binary tree node.* struct TreeNode {* int val;* TreeNode *left;* TreeNode *right;* TreeNode() : val(0), left(nullptr), right(nullptr) {}* TreeNode(int x) : val(x), left(nullptr), right(nullptr) {}* Tre…...

跨境电商中的IP隔离是什么?怎么做?
一、IP地址隔离的概念和原理 当我们谈论 IP 地址隔离时,我们实际上是在讨论一种网络安全策略,旨在通过技术手段将网络划分为不同的区域或子网,每个区域或子网都有自己独特的 IP 地址范围。这种划分使网络管理员可以更精细地控制哪些设备或用…...

【C++】stack、queue和deque的使用
💗个人主页💗 ⭐个人专栏——C学习⭐ 💫点击关注🤩一起学习C语言💯💫 目录 导读 一、stack 1. stack介绍 2. stack使用 二、queue 1. queue介绍 2. queue使用 三、deque 1. deque介绍 2. deque的…...

通过SSH远程登录华为设备
01 进入系统编辑视图 system-view Enter system view, return user view with return command. 02 创建本地RSA密钥对 [HUAWEI]rsa local-key-pair creat The key name will be:HUAWEI_Host The range of public key size is (2048 ~ 2048). NOTE: Key pair generation will ta…...

算法day27
第一题 515. 在每个树行中找最大值 首先是遍历每层的节点,将每一层最大值的节点的值保留下来,最后将所有层的最大值的表返回;具体的遍历每层节点的过程如上一篇故事; 综上所述,代码如下: /*** Definition …...

记录一次CTF图片拼图安装工具montage+gaps成功步骤以及踩坑全过程
安装图片拼接工具montage: 1.安装 使用pip install montage无法安装montage工具的师傅可以尝试下面的方法 #Debian apt-get install graphicsmagick-imagemagick-compat#Ubuntu apt-get install graphicsmagick-imagemagick-compat#Alpine apk add imagemagick6#…...

深入剖析人才管理的关键要素:“选、用、育、留”四大核心要素
在当今这个日新月异的商业时代,企业的成功不再仅仅取决于资金、技术或市场策略,而更多地依赖于企业所拥有的人才资源。有效的人才管理策略,尤其是“选、用、育、留”四大核心要素,已成为推动企业持续发展的关键。 一、选ÿ…...

【C++】类的默认成员函数
类的默认成员函数 类的六个默认成员函数构造函数构造函数的概念构造函数的特性 析构函数析构函数的概念析构函数的特性 构造函数与析构函数的调用顺序拷贝构造拷贝构造的概念拷贝构造的特性赋值运算符重载运算符重载赋值运算符重载前置与后置重载输入输出流重载 const修饰成员实…...

归并排序!
归并排序 https://articles.zsxq.com/id_g23e5o3lg87e.html 目录 归并排序算法思想命名由来算法描述sortList函数mergeSort函数 源代码 算法思想 通过将当前乱序的数组分成两个部分,分别进行「递归调用」,利用两个指针将数据元素以此比较,选…...

深入探讨:Spring与MyBatis中的连接池与缓存机制
深入探讨:Spring与MyBatis中的连接池与缓存机制 引言 在现代应用程序开发中,性能优化是一个永恒的话题。而在企业级Java应用开发中,Spring和MyBatis是两种非常流行的框架,它们的连接池和缓存机制对应用程序的性能有着至关重要的…...

[C#]使用C#部署yolov10的目标检测tensorrt模型
【测试通过环境】 win10 x64vs2019 cuda11.7cudnn8.8.0 TensorRT-8.6.1.6 opencvsharp4.9.0 .NET Framework4.7.2 NVIDIA GeForce RTX 2070 Super cuda和tensorrt版本和上述环境版本不一样的需要重新编译TensorRtExtern.dll,TensorRtExtern源码地址:T…...

Linux CFS 调度器 (1):概述
文章目录 1. 前言2. CFS 调度器2.1 概述2.2 一些实现细节2.3 运行队列:红黑树2.4 一些特征2.5 调度策略2.6 调度器类别2.7 扩展:组调度 3. 参考资料 1. 前言 限于作者能力水平,本文可能存在谬误,因此而给读者带来的损失ÿ…...

HBase中Master初始化错误~
ERROR:org.apache.hadoop.hbase.PleaseHoldException:Master is initializing 1、停止HBase运行 2、启动zookeeper中的zkCli.sh服务 ./zookeeper/bin/zkCli.sh 3、执行完毕显示以下结果,删除habse文件夹 4、重新启动HBase即可。...

Hive on Spark版本兼容性
Hive on Spark仅在特定版本的Spark上进行测试,因此给定版本的Hive只能保证与特定版本的Spark一起工作。其他版本的Spark可能与给定版本的Hive一起工作,但不能保证。以下是Hive版本及其对应的Spark版本列表: 详情参考官方文档:http…...

grep命令知多少
引言 1. grep命令的重要性 在Linux系统中,grep是一个不可或缺的文本处理工具,它允许用户快速搜索文件中的文本模式。这个命令的名称来源于Global Regular Expression Print,即全局正则表达式打印,它源自UNIX早期的ed文本编辑器。…...

[java]windows和linux下jdk1.8安装包所有版本系列下载地址汇总
【windows jdk1.9系列下载地址】 序号java版本下载地址1java-jdk9-jdk-9.0.1-windows-x64-bin.zip点我下载 【windows jdk1.8系列下载地址】 序号java版本下载地址1java-jdk1.8-jdk-8u202-windows-x64.zip点我下载2java-jdk1.8-jdk-8u201-windows-x64.zip点我下载3java-jdk1…...

Electron+Vue开源软件:洛雪音乐助手V2.8畅享海量免费歌曲
洛雪音乐助手是一款功能全面且完全免费的开源音乐软件,支持在Windows、Android和iOS平台上使用。 平台支持: 桌面版:采用Electron Vue技术栈开发,支持Windows 7及以上版本、Mac OS和Linux,具有广泛的用户群体覆盖。 …...