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

MySQL数据库进阶|SQL优化|开发手册

系列专栏:MySQL数据库进阶

前言

在看此篇前,建议先阅读MySQL索引,对索引有个基本了解:MySQL数据库进阶-索引-CSDN博客

在进行SQL优化前,我们必须先了解SQL查询的性能分析,为什么这条SQL慢,慢在哪里?这样我们才能更好的进行SQL优化。

性能分析

查看执行频次

查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次:

SHOW GLOBAL STATUS LIKE 'Com_______';

或者 SHOW SESSION STATUS LIKE 'Com_______';

例:show global status like 'Com_______'

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息: # 开启慢查询日志开关 slow_query_log=1

# 设置慢查询日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志 long_query_time=2 更改后记得重启MySQL服务,日志文件位置:/var/lib/mysql/localhost-slow.log

查看慢查询日志开关状态:

show variables like 'slow_query_log';

profile

show profile 能在做SQL优化时帮我们了解时间都耗费在哪里,通过 have_profiling 参数,能看到当前 MySQL 是否支持 profile 操作:

SELECT @@have_profiling;

profiling 默认关闭,可以通过set语句在session/global级别开启 profiling:

SET profiling = 1;

查看所有语句的耗时:

show profiles;

查看指定query_id的SQL语句各个阶段的耗时:

show profile for query query_id;

查看指定query_id的SQL语句CPU的使用情况

show profile cpu for query query_id;

explain

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 语法: # 直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;

EXPLAIN 各字段含义:

  • id:select 查询的序列号,表示查询中执行 select 子句或者操作表的顺序(id相同,执行顺序从上到下;id不同,值越大越先执行)

  • select_type:表示 SELECT 的类型,常见取值有 SIMPLE(简单表,即不适用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等

  • type:表示连接类型,性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all

  • possible_key:可能应用在这张表上的索引,一个或多个

  • Key:实际使用的索引,如果为 NULL,则没有使用索引

  • Key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好

  • rows:MySQL认为必须要执行的行数,在InnoDB引擎的表中,是一个估计值,可能并不总是准确的

  • filtered:表示返回结果的行数占需读取行数的百分比,filtered的值越大越好

SQL 优化

插入数据

普通插入:

  1. 采用批量插入(一次插入的数据不建议超过1000条)

  2. 手动提交事务

  3. 主键顺序插入

大批量插入: 如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令插入。

# 客户端连接服务端时,加上参数 --local-infile(这一行在bash/cmd界面输入)
mysql --local-infile -u root -p
# 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
select @@local_infile;
# 执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主键优化

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

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

页合并:当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前后)看看是否可以将这两个页合并以优化空间使用。

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

主键设计原则:

  • 满足业务需求的情况下,尽量降低主键的长度

  • 插入数据时,尽量选择顺序插入,选择使用 AUTO_INCREMENT 自增主键

  • 尽量不要使用 UUID 做主键或者是其他的自然主键,如身份证号

  • 业务操作时,避免对主键的修改

order by优化

        Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区 sort buffer 中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序

        Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高

如果order by字段全部使用升序排序或者降序排序,则都会走索引,但是如果一个字段升序排序,另一个字段降序排序,则不会走索引,explain的extra信息显示的是Using index, Using filesort,如果要优化掉Using filesort,则需要另外再创建一个索引,如:

此时使用如下sql会全部走索引

select id, age, phone from tb_user order by age asc, phone desc;
总结
  • 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则

  • 尽量使用覆盖索引

  • 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)

  • 如果不可避免出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)

group by优化

  • 在分组操作时,可以通过索引来提高效率

  • 分组操作时,索引的使用也是满足最左前缀法则的

如索引为idx_user_pro_age_stat,则句式可以是select ... where profession order by age,这样也符合最左前缀法则

limit优化

常见的问题如limit 2000000, 10,此时需要 MySQL 排序前2000000条记录,但仅仅返回2000000 - 2000010的记录,其他记录丢弃,查询排序的代价非常大。 优化方案:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化

例如:

-- 此语句耗时很长
select * from tb_sku limit 9000000, 10;
-- 通过覆盖索引加快速度,直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;
-- 下面的语句是错误的,因为 MySQL 不支持 in 里面使用 limit
-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
-- 通过连表查询即可实现第一句的效果,并且能达到第二句的速度
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

count优化

MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高(前提是不适用where); InnoDB 在执行 count(*) 时,需要把数据一行一行地从引擎里面读出来,然后累计计数。 优化方案:自己计数,如创建key-value表存储在内存或硬盘,或者是用redis

count的几种用法:

  • 如果count函数的参数(count里面写的那个字段)不是NULL(字段值不为NULL),累计值就加一,最后返回累计值

  • 用法:count(*)、count(主键)、count(字段)、count(1)

  • count(主键)跟count(*)一样,因为主键不能为空;

  • count(字段)只计算字段值不为NULL的行;count(1)引擎会为每行添加一个1,然后就count这个1,返回结果也跟count(*)一样;count(null)返回0

各种用法的性能:

  • count(主键):InnoDB引擎会遍历整张表,把每行的主键id值都取出来,返回给服务层,服务层拿到主键后,直接按行进行累加(主键不可能为空)

  • count(字段):没有not null约束的话,InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加;有not null约束的话,InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加

  • count(1):InnoDB 引擎遍历整张表,但不取值。服务层对于返回的每一层,放一个数字 1 进去,直接按行进行累加

  • count(*):InnoDB 引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加

按效率排序:count(字段) < count(主键) < count(1) < count(*),所以尽量使用 count(*)

update优化(避免行锁升级为表锁)

前面讲的都是基于查询的,基于update最常见的问题就是锁升级,也是update最重要的点。

InnoDB 的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。

如以下两条语句: update student set no = '123' where id = 1;,这句由于id有主键索引,所

以只会锁这一行;

update student set no = '123' where name = 'test';这句由于name没有索引,所以会把整张表都锁住进行数据更新,解决方法是给name字段添加索引。

如果本文对你有帮助,记得点赞关注,你的支持是我最大的动力!

如果您有任何疑问或建议,欢迎在评论区留言,一起探讨交流!

相关文章:

MySQL数据库进阶|SQL优化|开发手册

系列专栏&#xff1a;MySQL数据库进阶 前言 在看此篇前&#xff0c;建议先阅读MySQL索引&#xff0c;对索引有个基本了解&#xff1a;MySQL数据库进阶-索引-CSDN博客 在进行SQL优化前&#xff0c;我们必须先了解SQL查询的性能分析&#xff0c;为什么这条SQL慢&#xff0c;慢在…...

一文了解Git(所有命令)附带图片

我是南城余&#xff01;阿里云开发者平台专家博士证书获得者&#xff01; 欢迎关注我的博客&#xff01;一同成长&#xff01; 一名从事运维开发的worker&#xff0c;记录分享学习。 专注于AI&#xff0c;运维开发&#xff0c;windows Linux 系统领域的分享&#xff01; 其他…...

Hex2Bin转换软件、Bootloader 、OTA加密升级 、STM32程序加密、其他MCU同样适用

说明&#xff1a;这个工具可以将 Hex 文件 转换为 Bin 格式文件&#xff0c;软件是按自己开发 STM32 OAT 功能需求开发的一款辅助 上位机软件。 文中的介绍时 bootloader boot 文档在补充完善中... 有兴趣的朋友可留言探讨。 1. 软件功能&#xff1a; 1.生成 bin&#x…...

Hadoop之mapreduce参数大全-6

126.指定 Map 任务运行的节点标签表达式 mapreduce.map.node-label-expression 是 Hadoop MapReduce 框架中的一个配置属性&#xff0c;用于指定 Map 任务运行的节点标签表达式。节点标签是在 Hadoop 集群中为节点分配的用户定义的标签&#xff0c;可用于将 Map 任务限制在特定…...

Vue开发中,在实现单页面应用(SPA)前端路由时的hash模式和history模式的区别及详细介绍

文章目录 一、前言二、hash模式hashchange 事件&#xff1a; 三、history模式方法&#xff1a;1、history.go()&#xff1a;2、history.back()&#xff1a;3、history.forward()&#xff1a;4、History.replaceState()5、History.pushState()popState 事件 四、nginx配置五、原…...

功能强大的免费SSL证书

一、数据加密的重要性 免费SSL证书的核心作用在于对网站的数据传输进行加密处理。当一个网站部署了SSL证书后&#xff0c;它能够将HTTP协议升级至HTTPS&#xff0c;这意味着所有在客户端&#xff08;如浏览器&#xff09;与服务器之间传输的信息都将被高强度的加密算法所保护。…...

在Vue中使用Web Worker详细教程

1.什么是Web Worker? Web Worker 是2008年h5提供的新功能&#xff0c;每一个新功能都是为了解决原有技术的的痛点&#xff0c;那么这个痛点是什么呢&#xff1f; 1.1 JavaScript的单线程 JavaScript 为什么要设计成单线程&#xff1f; 这与js的工作内容有关&#xff1a;js只…...

四、C#高级特性(动态类型与Expando类)

在C#中&#xff0c;动态类型和ExpandoObject类是两个与运行时类型系统相关的特性&#xff0c;它们提供了更灵活的数据处理能力。 动态类型 动态类型是一种特殊的类型&#xff0c;允许你在运行时解析和操作对象的成员&#xff0c;而不需要在编译时知道这些成员的细节。使用动态…...

贪心算法的“左最优“与“右最优“及其对应的堆处理和预处理方法

1 答疑 1.1 什么是贪心算法的"左最优"与"右最优" "左最优"和"右最优"是贪心算法中的两种策略&#xff1a; 左最优 (Leftmost Greedy): 在每一步选择中&#xff0c;总是选择最左边&#xff08;最早出现的&#xff09;可行的选项。 右…...

【Docker】容器的相关命令

上一篇&#xff1a;创建&#xff0c;查看&#xff0c;进入容器 https://blog.csdn.net/m0_67930426/article/details/135430093?spm1001.2014.3001.5502 目录 1. 关闭容器 2.启动容器 3.删除容器 4.查看容器的信息 查看容器 1. 关闭容器 从图上来看&#xff0c;容器 aa…...

Android BUG 之 Error: Activity class {} does not exist

项目场景&#xff1a; 更换包名&#xff0c;运行报错 问题描述 原因分析&#xff1a; 在替换包名的时候要确认&#xff0c;配置文件跟build中的保持一致&#xff0c;在更换后还要将旧包的缓存数据清理掉 解决方案&#xff1a; 1 替换后删除 app 下的build 文件夹 2 Rebuild Pr…...

听劝,年度规划有它真的很必要!

2024年的时间进度条已走过一周&#xff0c;完成全年的1/52。 新年的flag悄然立下&#xff1a;愿逆风如解意&#xff0c;税后八个亿。 在不确定的世界中&#xff0c;发财暴富终归是确定的目标。 相比2023年的卷&#xff0c;年底的即兴生活正在悄悄上演&#xff0c;上一秒还在…...

leetcode滑动窗口问题总结 Python

目录 一、理论 二、例题 1. 最长无重复字符串 2. 长度最小的子数组 3. 字符串的排列 4. 最小覆盖子串 5. 滑动窗口最大值 一、理论 滑动窗口是一类比较重要的解题思路&#xff0c;一般来说我们面对的都是非定长窗口&#xff0c;所以一般需要定义两个指针 left 和 right&…...

秒变办公达人,只因用了这5款在线协同文档app!

在日常工作中&#xff0c;我们不可避免地需要处理各种文档&#xff0c;有时你可能会为如何高效地管理这些文档而感到烦恼&#xff0c;或是不知道如何挑选合适的在线文档工具&#xff1f; 不用担心&#xff01;在这篇文章中&#xff0c;我们将介绍5个好用的在线文档工具App&…...

镜头选型和计算

3.5 补充知识 一、单像元分辨率&#xff08;单像素精度&#xff09; 单像素精度是表示视觉系统综合精度的指标&#xff0c;表示一个像元对应检测目标的实际物理尺寸&#xff0c;是客户重点关注的 视觉系统参数&#xff1b; 计算公式1&#xff1a;单像素精度视野范围FOV/相机分辨…...

2024--Django平台开发-Django知识点(四)

1.知识回顾 创建项目&#xff1a;新项目、别人项目、新版版、老版本 项目目录&#xff08;v1.0版本&#xff09; 路由系统 常见路由编写加粗样式 /index/ 函数 /index/<str:v1> 函数 re_path(ryy/(\d{4})-(\d{2})-(\d{2})/, views.yy), re_path(ryy/(?…...

可狱可囚的爬虫系列课程 09:通过 API 接口抓取数据

前面已经讲解过 Requests 结合 BeautifulSoup4 库抓取数据&#xff0c;这种方式在抓取数据时还是比较方便快捷的&#xff0c;但是这并不意味着所有的网站都适合这种方式&#xff0c;并且这也不是抓取数据的最快方式&#xff0c;今天我们来讲一种更快速的获取数据的方式&#xf…...

2. Spring Boot 自动配置 Mybatis 流程

1. Spring Boot 自动配置 Mybatis 自动配置过程中做了3个主要bean的创建及很重要的一些事情。 sqlSessionFactory、sqlSessionTemplate、MapperScannerConfigurer 等配置bean的创建。sqlSessionFactory&#xff1a;解析 xml配置文件&#xff0c;并将MappedStatement放入到Has…...

Nginx配置反向代理实例一

Mac 安装Nginx教程 提醒一下&#xff1a;下面实例讲解是在Mac系统演示的&#xff1b; 反向代理实例一实现的效果 在浏览器地址栏输入www.testproxy.com, 跳转到系统Tomcat主页面。 第一步&#xff1a;在系统的 hosts 文件进行ip和域名对应关系的配置。 Mac 系统修改Hosts文…...

训练自己的GPT2

训练自己的GPT2 1.预训练与微调2.准备工作2.在自己的数据上进行微调 1.预训练与微调 所谓的预训练&#xff0c;就是在海量的通用数据上训练大模型。比如&#xff0c;我把全世界所有的网页上的文本内容都整理出来&#xff0c;把全人类所有的书籍、论文都整理出来&#xff0c;然…...

etcd储存安装

目录 etcd介绍: etcd工作原理 选举 复制日志 安全性 etcd工作场景 服务发现 etcd基本术语 etcd安装(centos) 设置&#xff1a;etcd后台运行 etcd 是云原生架构中重要的基础组件&#xff0c;由 CNCF 孵化托管。etcd 在微服务和 Kubernates 集群中不仅可以作为服务注册…...

如何彻底卸载Microsoft Edge浏览器

一、引语 随着微软推出全新的Edge浏览器&#xff0c;许多用户可能想要尝试或完全切换到其他浏览器。在这篇文章中&#xff0c;我们将向您介绍如何彻底卸载Microsoft Edge浏览器&#xff0c;以确保您的系统干净整洁。 二、通过系统设置卸载 1、首先&#xff0c;右键单击桌面上…...

Transformers 2023年度回顾 :从BERT到GPT4

人工智能已成为近年来最受关注的话题之一&#xff0c;由于神经网络的发展&#xff0c;曾经被认为纯粹是科幻小说中的服务现在正在成为现实。从对话代理到媒体内容生成&#xff0c;人工智能正在改变我们与技术互动的方式。特别是机器学习 (ML) 模型在自然语言处理 (NLP) 领域取得…...

判断两个对象某些字段的值是否相同

1、借助mybatis plus的方法 import com.baomidou.mybatisplus.core.toolkit.LambdaUtils; import com.baomidou.mybatisplus.core.toolkit.support.SFunction; import com.baomidou.mybatisplus.core.toolkit.support.SerializedLambda; import lombok.SneakyThrows; import o…...

TYPE-C接口取电芯片介绍和应用场景

随着科技的发展&#xff0c;USB PDTYPE-C已经成为越来越多设备的充电接口。而在这一领域中&#xff0c;LDR6328Q PD取电芯片作为设备端协议IC芯片&#xff0c;扮演着至关重要的角色。本文将详细介绍LDR6328Q PD取电芯片的工作原理、应用场景以及选型要点。 一、工作原理 LDR63…...

基于TI TPSXX系列 Buck电路应用计算-外围器件详细计算过程

TPS54202 Buck电路应用计算 1、电气特性2、内部框图3、典型应用电路4、设计需求5、计算EN引脚电阻6、FB引脚电阻估算7、查看反馈电压电压基准8、输入电容计算10、FB引脚反馈电阻计算11、功率电感计算12、输出电容计算13、前馈电容计算15、Layout布局TPS54202-中文版 1、电气特…...

NOIP2012提高组day1-T3:开车旅行

题目链接 [NOIP2012 提高组] 开车旅行 题目描述 小 A \text{A} A 和小 B \text{B} B 决定利用假期外出旅行&#xff0c;他们将想去的城市从 1 1 1 到 n n n 编号&#xff0c;且编号较小的城市在编号较大的城市的西边&#xff0c;已知各个城市的海拔高度互不相同&#xf…...

Golang Web框架性能对比

Golang Web框架性能对比 github star排名依次: Gin Beego Iris Echo Revel Buffalo 性能上gin、iris、echo网上是给的数据都是五星&#xff0c;beego三星&#xff0c;revel两星 beego是国产&#xff0c;有中文文档,文档齐全 根据star数&#xff0c;性能&#xff0c;易用程度…...

【OCR】 - Tesseract OCR在mac系统中安装

Tesseract OCR 在Mac环境下安装Tesseract OCR&#xff08;Optical Character Recognition&#xff09;通常可以通过Homebrew包管理器进行。以下是安装步骤&#xff1a; 安装Homebrew 如果你还没有安装Homebrew&#xff0c;请访问 https://brew.sh/ 并按照页面上的说明安装。…...

了解不同方式导入导出的速度之快

目录 一、用工具导出导入 Navicat&#xff08;速度慢&#xff09; 1.1、导入&#xff1a; 共耗时&#xff1a; 1.2、导出表 共耗时&#xff1a; 二、用命令语句导出导入 2.1、mysqldump速度快 导出表数据和表结构 共耗时&#xff1a; 只导出表结构 导入 共耗时&…...