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

MYSQL八、MYSQL的SQL优化

一、SQL优化

  • sql优化是指:通过对sql语句和数据库结构的调整,来提高数据库查询、插入、更新和删除等操作的性能和效率。

1、插入数据优化

  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. 主键顺序插入,性能要高于乱序插入。

1.4、大批量插入数据

  1. 如果一次性需要插入大批量数据(比如: 几百万的记录),使用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、数据的组织方式

  1. 在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表
    • 行数据都是存储在聚集索引的叶子节点上的。
      在这里插入图片描述
  2. 在InnoDB引擎中,数据行是记录在逻辑结构 page 页中的,而每一个页的大小是固定的,默认16K。
    • 那也就意味着一个页中所存储的行也是有限的,如果插入的数据行row在该页存储不小,将会存储到下一个页中,页与页之间会通过指针连接。

在这里插入图片描述

2.2、页分裂

  1. 页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据过大,会行溢出),根据主键排列。
  2. MySQL中的页分裂是指:当B+tree索引中的一个页已经满了,再插入新的记录时,该页会被分裂成两个页。其中,一些记录会被留在原来的页中,而其余记录则会被移到一个新的页中。
    • 页分裂是为了避免数据过度集中在一个页上而引起的性能问题。通过将记录分散到更多的页中,可以减少索引的深度,从而提高查询效率。
    • 在MySQL中,当一个页满了之后,会触发页分裂。具体来说,MySQL会将该页中的记录按照顺序依次遍历,找到一个位置将其分为两个部分,并将后面的记录移到一个新的页中。同时,为了保证索引有序性,MySQL还会将新页中的第一条记录的键值插入到父节点中,并调整父节点中的指针。如果父节点也满了,则递归进行分裂操作。
  3. 页分裂通常会发生在以下情况:
    • 顺序插入:当数据按索引顺序插入,且最后一页已满,会创建一个新的页并在其上继续插入。
    • 乱序插入:如果数据插入到中间的某个索引位置,导致中间的页溢出,那么会把一部分数据移动到新页中,以便为新数据腾出空间。(可能还需要重新设置链表指针)

2.3、页合并

  1. 当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
  2. 当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
    在这里插入图片描述
  • 参数MERGE_THRESHOLD:是合并页的阈值,可以自己设置,在创建表或者创建索引时指定。

2.4、主键的设计原则

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

3、order by优化

  1. MySQL的排序,有两种方式:

    • Using filesort : 通过表的索引或全表扫描,读取到满足条件的数据行,然后在排序缓冲区sort
      buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
    • Using index : 通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高。
    • Using index的性能,而Using filesort的性能
  2. order by优化原则:

    • A. 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
    • B. 尽量使用覆盖索引。
    • C. 多字段排序, 一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
    • D. 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

4、group by优化

  1. 在分组操作中通过以下两点进行优化,以提升性能:
    • A. 在分组操作时,可以通过索引来提高效率。(没有使用索引进行分组Extra下会出现Using temporate(使用临时表),这个性能很低的)
    • B. 分组操作时,索引的使用也是满足最左前缀法则的

5、limit优化

  1. 在数据量比较大时,如果进行limit分页查询,在查询时越往后的数据,分页查询效率越低。
    • 优化思路: 一般分页查询时,通过创建 覆盖索引 能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。

6、count优化

  1. 如果数据量很大,在执行count操作时,是非常耗时的。
    • MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高; 但是如果是带条件的count,MyISAM也慢。
    • InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数
      • 要大幅度提升InnoDB表的count效率,主要的优化思路:自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)

6.1、count函数的用法

  1. count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是NULL,累计值就加 1,否则不加,最后返回累计值。
    • 用法:count(*)、count(主键)、count(字段)、count(数字)
      在这里插入图片描述
  • 按照效率排序的话,count(字段) < count(主键 id) < count(1) ≈ count(*),所以尽量使用 count(*)。

7、update优化

  1. 主要需要注意一下update语句执行时的注意事项:
    • 在更新数据时,最好根据索引字段进行更新 (否则会出现行锁升级为表锁的问题,就是锁住整张表,导致并发性能降低)
-- id是主键索引
update  course  set  name = 'javaEE'  where  id  =  1 ;-- 此时执行上面的update语句只会出现行锁
  • InnoDB的行锁是针对索引加的锁,不是针对数据记录加的锁 ,并且该索引不能失效,否则会从行锁升级为表锁 。

相关文章:

MYSQL八、MYSQL的SQL优化

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

鸿蒙轻内核M核源码分析系列二一 02 文件系统LittleFS

1、LFS文件系统结构体介绍 会分2部分来介绍结构体部分&#xff0c;先介绍LittleFS文件系统的结构体&#xff0c;然后介绍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 汇编指令语法之前&#xff0c;先介绍下如何判断自己所使用的芯片是否实现了SVE功…...

Java版+ SaaS应用+接口技术RESTful API 技术开发的智慧医院HIS系统源码 专注医院管理系统研发 支持二开

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

工业机器人远程运维,增强智慧工厂运营管理

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

理解Python的元类

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

web前端黑马下载:探索学习资源的海洋

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

最新版jd-gui下载

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

(051)FPGA时钟--->(001)时钟介绍

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

Java程序员英语单词通关:

Java程序员英语单词通关&#xff1a; abstract - 抽象的 boolean - 布尔值 break - 打断 byte - 字节 case - 情况&#xff0c;实例 catch - 捕获 char - 字符 class - 类 continue - 继续 default - 默认&#xff0c;通常 do - 做&#xff0c;运行 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种物品和一个背包&#xff0c;物品i的重量是Wi​&#xff0c;其价值为Vi​&#xff0c;问如何选择装入背包的物品&#xff0c;使得装入背包的物品的总价值最大&#xff1f; 在选择装入背包的物品时&#xff0c;对每种物品i只能有两种选择&#xff0c;装入或者不装入…...

elasticsearch快照生成与恢复

Elasticsearch快照生成与恢复的场景主要涉及到数据的备份与恢复需求。当需要对Elasticsearch集群中的数据进行备份&#xff0c;或者在数据丢失、损坏等情况下需要恢复数据时&#xff0c;就可以使用快照功能。 快照生成的方法通常包括以下步骤&#xff1a; 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 地址隔离时&#xff0c;我们实际上是在讨论一种网络安全策略&#xff0c;旨在通过技术手段将网络划分为不同的区域或子网&#xff0c;每个区域或子网都有自己独特的 IP 地址范围。这种划分使网络管理员可以更精细地控制哪些设备或用…...

【C++】stack、queue和deque的使用

&#x1f497;个人主页&#x1f497; ⭐个人专栏——C学习⭐ &#x1f4ab;点击关注&#x1f929;一起学习C语言&#x1f4af;&#x1f4ab; 目录 导读 一、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. 在每个树行中找最大值 首先是遍历每层的节点&#xff0c;将每一层最大值的节点的值保留下来&#xff0c;最后将所有层的最大值的表返回&#xff1b;具体的遍历每层节点的过程如上一篇故事&#xff1b; 综上所述&#xff0c;代码如下&#xff1a; /*** Definition …...

记录一次CTF图片拼图安装工具montage+gaps成功步骤以及踩坑全过程

安装图片拼接工具montage&#xff1a; 1.安装 使用pip install montage无法安装montage工具的师傅可以尝试下面的方法 #Debian apt-get install graphicsmagick-imagemagick-compat#Ubuntu apt-get install graphicsmagick-imagemagick-compat#Alpine apk add imagemagick6#…...

深入剖析人才管理的关键要素:“选、用、育、留”四大核心要素

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

网络六边形受到攻击

大家读完觉得有帮助记得关注和点赞&#xff01;&#xff01;&#xff01; 抽象 现代智能交通系统 &#xff08;ITS&#xff09; 的一个关键要求是能够以安全、可靠和匿名的方式从互联车辆和移动设备收集地理参考数据。Nexagon 协议建立在 IETF 定位器/ID 分离协议 &#xff08;…...

在软件开发中正确使用MySQL日期时间类型的深度解析

在日常软件开发场景中&#xff0c;时间信息的存储是底层且核心的需求。从金融交易的精确记账时间、用户操作的行为日志&#xff0c;到供应链系统的物流节点时间戳&#xff0c;时间数据的准确性直接决定业务逻辑的可靠性。MySQL作为主流关系型数据库&#xff0c;其日期时间类型的…...

Java 语言特性(面试系列2)

一、SQL 基础 1. 复杂查询 &#xff08;1&#xff09;连接查询&#xff08;JOIN&#xff09; 内连接&#xff08;INNER JOIN&#xff09;&#xff1a;返回两表匹配的记录。 SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id d.dept_id; 左…...

基于ASP.NET+ SQL Server实现(Web)医院信息管理系统

医院信息管理系统 1. 课程设计内容 在 visual studio 2017 平台上&#xff0c;开发一个“医院信息管理系统”Web 程序。 2. 课程设计目的 综合运用 c#.net 知识&#xff0c;在 vs 2017 平台上&#xff0c;进行 ASP.NET 应用程序和简易网站的开发&#xff1b;初步熟悉开发一…...

深入浅出:JavaScript 中的 `window.crypto.getRandomValues()` 方法

深入浅出&#xff1a;JavaScript 中的 window.crypto.getRandomValues() 方法 在现代 Web 开发中&#xff0c;随机数的生成看似简单&#xff0c;却隐藏着许多玄机。无论是生成密码、加密密钥&#xff0c;还是创建安全令牌&#xff0c;随机数的质量直接关系到系统的安全性。Jav…...

令牌桶 滑动窗口->限流 分布式信号量->限并发的原理 lua脚本分析介绍

文章目录 前言限流限制并发的实际理解限流令牌桶代码实现结果分析令牌桶lua的模拟实现原理总结&#xff1a; 滑动窗口代码实现结果分析lua脚本原理解析 限并发分布式信号量代码实现结果分析lua脚本实现原理 双注解去实现限流 并发结果分析&#xff1a; 实际业务去理解体会统一注…...

Axios请求超时重发机制

Axios 超时重新请求实现方案 在 Axios 中实现超时重新请求可以通过以下几种方式&#xff1a; 1. 使用拦截器实现自动重试 import axios from axios;// 创建axios实例 const instance axios.create();// 设置超时时间 instance.defaults.timeout 5000;// 最大重试次数 cons…...

前端开发面试题总结-JavaScript篇(一)

文章目录 JavaScript高频问答一、作用域与闭包1.什么是闭包&#xff08;Closure&#xff09;&#xff1f;闭包有什么应用场景和潜在问题&#xff1f;2.解释 JavaScript 的作用域链&#xff08;Scope Chain&#xff09; 二、原型与继承3.原型链是什么&#xff1f;如何实现继承&a…...

css3笔记 (1) 自用

outline: none 用于移除元素获得焦点时默认的轮廓线 broder:0 用于移除边框 font-size&#xff1a;0 用于设置字体不显示 list-style: none 消除<li> 标签默认样式 margin: xx auto 版心居中 width:100% 通栏 vertical-align 作用于行内元素 / 表格单元格&#xff…...

GC1808高性能24位立体声音频ADC芯片解析

1. 芯片概述 GC1808是一款24位立体声音频模数转换器&#xff08;ADC&#xff09;&#xff0c;支持8kHz~96kHz采样率&#xff0c;集成Δ-Σ调制器、数字抗混叠滤波器和高通滤波器&#xff0c;适用于高保真音频采集场景。 2. 核心特性 高精度&#xff1a;24位分辨率&#xff0c…...