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

测试微信模版消息推送

进入“开发接口管理”--“公众平台测试账号”&#xff0c;无需申请公众账号、可在测试账号中体验并测试微信公众平台所有高级接口。 获取access_token: 自定义模版消息&#xff1a; 关注测试号&#xff1a;扫二维码关注测试号。 发送模版消息&#xff1a; import requests da…...

深入剖析AI大模型:大模型时代的 Prompt 工程全解析

今天聊的内容&#xff0c;我认为是AI开发里面非常重要的内容。它在AI开发里无处不在&#xff0c;当你对 AI 助手说 "用李白的风格写一首关于人工智能的诗"&#xff0c;或者让翻译模型 "将这段合同翻译成商务日语" 时&#xff0c;输入的这句话就是 Prompt。…...

使用VSCode开发Django指南

使用VSCode开发Django指南 一、概述 Django 是一个高级 Python 框架&#xff0c;专为快速、安全和可扩展的 Web 开发而设计。Django 包含对 URL 路由、页面模板和数据处理的丰富支持。 本文将创建一个简单的 Django 应用&#xff0c;其中包含三个使用通用基本模板的页面。在此…...

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

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

安宝特方案丨XRSOP人员作业标准化管理平台:AR智慧点检验收套件

在选煤厂、化工厂、钢铁厂等过程生产型企业&#xff0c;其生产设备的运行效率和非计划停机对工业制造效益有较大影响。 随着企业自动化和智能化建设的推进&#xff0c;需提前预防假检、错检、漏检&#xff0c;推动智慧生产运维系统数据的流动和现场赋能应用。同时&#xff0c;…...

2021-03-15 iview一些问题

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

土地利用/土地覆盖遥感解译与基于CLUE模型未来变化情景预测;从基础到高级,涵盖ArcGIS数据处理、ENVI遥感解译与CLUE模型情景模拟等

&#x1f50d; 土地利用/土地覆盖数据是生态、环境和气象等诸多领域模型的关键输入参数。通过遥感影像解译技术&#xff0c;可以精准获取历史或当前任何一个区域的土地利用/土地覆盖情况。这些数据不仅能够用于评估区域生态环境的变化趋势&#xff0c;还能有效评价重大生态工程…...

九天毕昇深度学习平台 | 如何安装库?

pip install 库名 -i https://pypi.tuna.tsinghua.edu.cn/simple --user 举个例子&#xff1a; 报错 ModuleNotFoundError: No module named torch 那么我需要安装 torch pip install torch -i https://pypi.tuna.tsinghua.edu.cn/simple --user pip install 库名&#x…...

腾讯云V3签名

想要接入腾讯云的Api&#xff0c;必然先按其文档计算出所要求的签名。 之前也调用过腾讯云的接口&#xff0c;但总是卡在签名这一步&#xff0c;最后放弃选择SDK&#xff0c;这次终于自己代码实现。 可能腾讯云翻新了接口文档&#xff0c;现在阅读起来&#xff0c;清晰了很多&…...

并发编程 - go版

1.并发编程基础概念 进程和线程 A. 进程是程序在操作系统中的一次执行过程&#xff0c;系统进行资源分配和调度的一个独立单位。B. 线程是进程的一个执行实体,是CPU调度和分派的基本单位,它是比进程更小的能独立运行的基本单位。C.一个进程可以创建和撤销多个线程;同一个进程中…...