MySQL数据库进阶第三篇(MySQL性能优化)

文章目录

  • 一、插入数据优化
  • 二、主键优化
  • 三、order by优化
  • 四、group by优化
  • 五、limit优化
  • 六、count优化
  • 七、update优化(避免行锁升级为表锁)

这篇博客详细探讨了MySQL数据库的多项优化技巧。包括如何进行数据插入优化,采用批量插入和MySQL的load指令进行大量数据插入等;阐述了主键优化,包括InnoDB存储引擎的数据组织方式和主键设计原则;探讨了如何进行order by,group by,limit,count等多方面的优化,让数据库操作更加高效;最后讨论了避免行锁升级为表锁的update优化方法。各种方法都配有详尽的代码示例和插图解释,使读者能更为直观、深入地理解和应用这些优化技巧,对于深化对MySQL性能优化的理解有很大帮助。

一、插入数据优化

普通插入:

采用批量插入(一次插入的数据不建议超过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优化

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

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

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

create index idx_user_age_phone_ad on tb_user(age asc, phone desc);

,此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引

四、group by优化

在这里插入图片描述
1.在分组操作时,可以通过索引来提高效率
2.分组操作时,索引的使用也是满足最左前缀法则的
如索引为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
在这里插入图片描述

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

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

如以下两条语句:

update student set no = '123' where id = 1;,这句由于id有主键索引,所以只会锁这一行;
update student set no = '123' where name = 'test';,这句由于name没有索引,所以会把整张表都锁住进行数据更新,

解决方法是给name字段添加索引

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/399872.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

四非保研之旅

大家好,我是工藤学编程,虽有万分感概,但是话不多说,先直接进入正题,抒情环节最后再说,哈哈哈 写在开头 我的分享是来给大家涨信心的,网上的大佬们都太强了,大家拿我涨涨信心&#…

在linux环境如何使用Anaconda安装指定的python版本

首先我们可以查看一下服务器现有的环境 conda info --envs 发现没有我需要的版本,那么可以用如下命令 conda create --name py36 python3.6 我这里安装了python 3.6的版本 再次输入 conda info --envs 可以通过以下命令激活刚刚创建的环境 conda activate py36…

Docker中如何删除某个镜像

1. 停止使用镜像的容器 首先,您需要停止所有正在使用该镜像的容器。您可以使用 docker stop 命令来停止容器: docker stop 11184993a106如果有多个容器使用该镜像,您需要对每个容器都执行停止命令。您可以通过 docker ps -a | grep core-ba…

C语言------------指针笔试题目深度剖析

1. #include <stdio.h> int main() { int a[5] { 1, 2, 3, 4, 5 }; int *ptr (int *)(&a 1); printf( "%d,%d", *(a 1), *(ptr - 1)); return 0; } 首先要明白这个强制类型转换&#xff0c;即int(*)[5]类型转换成int(*)类型&#xff1b; *&#xff…

联发科将展示6G环境运算和次世代卫星宽带 | 百能云芯

联发科技术有限公司&#xff08;MediaTek&#xff09;近日宣布&#xff0c;将在2024年世界移动通信大会&#xff08;MWC&#xff09;上展示其在移动通信技术领域的最新成就&#xff0c;包括6G环境运算、Pre-6G卫星宽带以及智能手机生成式人工智能&#xff08;AI&#xff09;应用…

相机图像质量研究(40)常见问题总结:显示器对成像的影响--画面泛白

系列文章目录 相机图像质量研究(1)Camera成像流程介绍 相机图像质量研究(2)ISP专用平台调优介绍 相机图像质量研究(3)图像质量测试介绍 相机图像质量研究(4)常见问题总结&#xff1a;光学结构对成像的影响--焦距 相机图像质量研究(5)常见问题总结&#xff1a;光学结构对成…

C++学习Day08之类模板中的成员函数分文件编写问题及解决

目录 一、程序及输出1.1 .h文件cpp1.2 包含hpp 二、分析与总结 一、程序及输出 1.1 .h文件cpp person.h #pragma once #define _CRT_SECURE_NO_WARNINGS #include<iostream> using namespace std;template<class T1, class T2> class Person { public:Person(T1…

判断一个dll/exe是32位还是64位

通过记事本判断&#xff08;可判断C或者C#&#xff09; 64位、将dll用记事本打开&#xff0c;可以看到一堆乱码&#xff0c;但是找到乱码行的第一个PE&#xff0c;如果后面是d?则为64位 32位、将dll用记事本打开&#xff0c;可以看到一堆乱码&#xff0c;但是找到乱码行的第…

三防加固平板在房地产行业的应用|亿道三防onerugged

近期&#xff0c;有一款引人注目的解决方案——亿道三防onerugged平板电脑&#xff0c;它以其出色的性能和多功能的设计&#xff0c;为房地产行业带来了全新的应用体验。 首先&#xff0c;亿道三防onerugged平板电脑的NFC功能在小区业主身份验证中发挥着重要作用。传统的身份验…

Excel SUMPRODUCT函数用法(乘积求和,分组排序)

SUMPRODUCT函数是Excel中功能比较强大的一个函数&#xff0c;可以实现sum,count等函数的功能&#xff0c;也可以实现一些基础函数无法直接实现的功能&#xff0c;常用来进行分类汇总&#xff0c;分组排序等 SUMPRODUCT 函数基础 SUMPRODUCT函数先计算多个数组的元素之间的乘积…

【RL】Policy Gradient Methods(策略梯度方法)

Lecture 9: Policy Gradient Methods Basic idea of policy gradient 之前&#xff0c;policy是用表格表示的&#xff1a; 所有state的action概率都存储在表 π ( a ∣ s ) \pi(a|s) π(a∣s)中。 表的每个条目都由state和action索引。 因此可以直接访问或更改表中的值。 …

药物检测设备行业分析:市场年均复合增长速度为14.04%

在制药行业中&#xff0c;质量检验检测过程尤为重要。因为药品质量关系到人们的身体健康&#xff0c;如何控制好药品的质量安全&#xff0c;做好药品生产管理过程中的质量风险管理工作&#xff0c;是药品生产企业面临的重要问题。 为保证做好药品质量、安全方面的控制&#xff…

☀️将大华摄像头画面接入Unity 【1】配置硬件和初始化摄像头

一、硬件准备 目前的设想是后期采用网口供电的形式把画面传出来&#xff0c;所以这边我除了大华摄像头还准备了POE供电交换机&#xff0c;为了方便索性都用大华的了&#xff0c;然后全都连接电脑主机即可。 二、软件准备 这边初始化摄像头需要用到大华的Configtool软件&#…

ipad作为扩展屏的最简单方式(无需数据线)

ipad和win都下载安装toDesk&#xff0c;并且都处于同一局域网下 连接ipad&#xff0c;在ipad中输入win设备的设备密码和临时密码&#xff0c;连接上后可以看到ipad会是win屏幕的镜像&#xff0c;此时退出连接&#xff0c;准备以扩展模式再次连接。 注意&#xff0c;如果直接从…

#gStore-weekly | gMaster功能详解之数据库管理

gMaster提供了数据库管理功能。该功能可以对集群中的数据库进行集中管理&#xff0c;可以查看各个数据库详细信息。能够方便的对数据库进行新建、构建、导出、备份、还原、删除操作。 登录gMaster&#xff0c;点击左侧菜单【数据库】下的【数据库管理】&#xff0c;进入数据库…

数据脱敏(六)脱敏算法-加密算法

脱敏算法篇使用阿里云数据脱敏算法为模板,使用算子平台快速搭建流程来展示数据 "加密脱敏"是一种数据处理技术&#xff0c;主要用于保护个人隐私和数据安全。它通过将敏感信息&#xff08;如姓名、身份证号、电话号码等&#xff09;进行加密处理&#xff0c;使其无法…

linux 系统的目录结构

为什么某些执行程序位于/bin、/sbin、/usr/bin或/usr/sbin目录下&#xff1f;例如&#xff0c;less命令位于/usr/bin目录下。为什么不是/bin、/sbin或/usr/sbin&#xff1f;这些目录之间有什么区别呢&#xff1f; 在这篇文章中&#xff0c;让我们主要讲述一下Linux文件系统结构…

【前端】前端三要素之JavsScript基础

写在前面&#xff1a;本文仅包含JavaScript内容&#xff0c;DOM知识传送门在这里&#xff0c;BOM传送门在这里。 本文内容是假期中刷的黑马Pink老师视频&#xff08;十分感谢Pink老师&#xff09;&#xff0c;原文保存在个人的GitLab中&#xff0c;如果需要写的网页内容信息等可…

【Java EE初阶二十二】https的简单理解

1. 初识https 当前网络上,主要都是 HTTPS 了,很少能见到 HTTP.实际上 HTTPS 也是基于 HTTP.只不过 HTTPS 在 HTTP 的基础之上, 引入了"加密"机制&#xff1b;引入 HTTPS 防止你的数据被黑客篡改 &#xff1b; HTTPS 就是一个重要的保护措施.之所以能够安全, 最关键的…

AI人工智能写作,这5款AI软件帮你解决写作难题

在如今这个信息爆炸的时代&#xff0c;写作已经成为我们生活和工作中必不可少的一部分。但是&#xff0c;对于很多人来说&#xff0c;写作可能是一件挺费劲的事情&#xff0c;需要花费很多时间和精力。不过&#xff0c;幸运的是&#xff0c;随着人工智能技术的不断进步&#xf…