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 优化
插入数据
普通插入:
-
采用批量插入(一次插入的数据不建议超过1000条)
-
手动提交事务
-
主键顺序插入
大批量插入: 如果一次性需要插入大批量数据,使用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优化|开发手册
系列专栏:MySQL数据库进阶 前言 在看此篇前,建议先阅读MySQL索引,对索引有个基本了解:MySQL数据库进阶-索引-CSDN博客 在进行SQL优化前,我们必须先了解SQL查询的性能分析,为什么这条SQL慢,慢在…...

一文了解Git(所有命令)附带图片
我是南城余!阿里云开发者平台专家博士证书获得者! 欢迎关注我的博客!一同成长! 一名从事运维开发的worker,记录分享学习。 专注于AI,运维开发,windows Linux 系统领域的分享! 其他…...

Hex2Bin转换软件、Bootloader 、OTA加密升级 、STM32程序加密、其他MCU同样适用
说明:这个工具可以将 Hex 文件 转换为 Bin 格式文件,软件是按自己开发 STM32 OAT 功能需求开发的一款辅助 上位机软件。 文中的介绍时 bootloader boot 文档在补充完善中... 有兴趣的朋友可留言探讨。 1. 软件功能: 1.生成 bin&#x…...

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

Vue开发中,在实现单页面应用(SPA)前端路由时的hash模式和history模式的区别及详细介绍
文章目录 一、前言二、hash模式hashchange 事件: 三、history模式方法:1、history.go():2、history.back():3、history.forward():4、History.replaceState()5、History.pushState()popState 事件 四、nginx配置五、原…...

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

在Vue中使用Web Worker详细教程
1.什么是Web Worker? Web Worker 是2008年h5提供的新功能,每一个新功能都是为了解决原有技术的的痛点,那么这个痛点是什么呢? 1.1 JavaScript的单线程 JavaScript 为什么要设计成单线程? 这与js的工作内容有关:js只…...

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

贪心算法的“左最优“与“右最优“及其对应的堆处理和预处理方法
1 答疑 1.1 什么是贪心算法的"左最优"与"右最优" "左最优"和"右最优"是贪心算法中的两种策略: 左最优 (Leftmost Greedy): 在每一步选择中,总是选择最左边(最早出现的)可行的选项。 右…...

【Docker】容器的相关命令
上一篇:创建,查看,进入容器 https://blog.csdn.net/m0_67930426/article/details/135430093?spm1001.2014.3001.5502 目录 1. 关闭容器 2.启动容器 3.删除容器 4.查看容器的信息 查看容器 1. 关闭容器 从图上来看,容器 aa…...

Android BUG 之 Error: Activity class {} does not exist
项目场景: 更换包名,运行报错 问题描述 原因分析: 在替换包名的时候要确认,配置文件跟build中的保持一致,在更换后还要将旧包的缓存数据清理掉 解决方案: 1 替换后删除 app 下的build 文件夹 2 Rebuild Pr…...

听劝,年度规划有它真的很必要!
2024年的时间进度条已走过一周,完成全年的1/52。 新年的flag悄然立下:愿逆风如解意,税后八个亿。 在不确定的世界中,发财暴富终归是确定的目标。 相比2023年的卷,年底的即兴生活正在悄悄上演,上一秒还在…...

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

秒变办公达人,只因用了这5款在线协同文档app!
在日常工作中,我们不可避免地需要处理各种文档,有时你可能会为如何高效地管理这些文档而感到烦恼,或是不知道如何挑选合适的在线文档工具? 不用担心!在这篇文章中,我们将介绍5个好用的在线文档工具App&…...

镜头选型和计算
3.5 补充知识 一、单像元分辨率(单像素精度) 单像素精度是表示视觉系统综合精度的指标,表示一个像元对应检测目标的实际物理尺寸,是客户重点关注的 视觉系统参数; 计算公式1:单像素精度视野范围FOV/相机分辨…...

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

可狱可囚的爬虫系列课程 09:通过 API 接口抓取数据
前面已经讲解过 Requests 结合 BeautifulSoup4 库抓取数据,这种方式在抓取数据时还是比较方便快捷的,但是这并不意味着所有的网站都适合这种方式,并且这也不是抓取数据的最快方式,今天我们来讲一种更快速的获取数据的方式…...

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

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

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

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

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

Transformers 2023年度回顾 :从BERT到GPT4
人工智能已成为近年来最受关注的话题之一,由于神经网络的发展,曾经被认为纯粹是科幻小说中的服务现在正在成为现实。从对话代理到媒体内容生成,人工智能正在改变我们与技术互动的方式。特别是机器学习 (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接口取电芯片介绍和应用场景
随着科技的发展,USB PDTYPE-C已经成为越来越多设备的充电接口。而在这一领域中,LDR6328Q PD取电芯片作为设备端协议IC芯片,扮演着至关重要的角色。本文将详细介绍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 决定利用假期外出旅行,他们将想去的城市从 1 1 1 到 n n n 编号,且编号较小的城市在编号较大的城市的西边,已知各个城市的海拔高度互不相同…...

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

【OCR】 - Tesseract OCR在mac系统中安装
Tesseract OCR 在Mac环境下安装Tesseract OCR(Optical Character Recognition)通常可以通过Homebrew包管理器进行。以下是安装步骤: 安装Homebrew 如果你还没有安装Homebrew,请访问 https://brew.sh/ 并按照页面上的说明安装。…...

了解不同方式导入导出的速度之快
目录 一、用工具导出导入 Navicat(速度慢) 1.1、导入: 共耗时: 1.2、导出表 共耗时: 二、用命令语句导出导入 2.1、mysqldump速度快 导出表数据和表结构 共耗时: 只导出表结构 导入 共耗时&…...