【MySQL进阶】SQL优化

最新推荐文章于 2024-03-28 14:10:43 发布
最新推荐文章于 2024-03-28 14:10:43 发布
阅读量218 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Chovy_pyc/article/details/125591687
版权

3、SQL优化

3.1、插入数据

3.1.1、insert

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

insert into tb_test values(1,'tom'); 
insert into tb_test values(2,'cat'); 
insert into tb_test values(3,'jerry'); 
......

1、优化方案一：批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

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;

3、优化方案三：主键顺序插入，性能要高于乱序插入。

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3 
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

一般都是设置主键自增或者使用 MyBatis-Plus 自带的雪花 Id 算法

3.1.2、大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

在这里插入图片描述
可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile 
mysql –-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' ;

示例：

1、创建表结构

CREATE TABLE `tb_user` ( 
`id` INT(11) NOT NULL AUTO_INCREMENT, 
`username` VARCHAR(50) NOT NULL, 
`password` VARCHAR(50) NOT NULL, 
`name` VARCHAR(20) NOT NULL, 
`birthday` DATE DEFAULT NULL, 
`sex` CHAR(1) DEFAULT NULL, 
PRIMARY KEY (`id`), 
UNIQUE KEY `unique_user_username` (`username`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

2、设置参数

-- 客户端连接服务端时，加上参数 -–local-infile 
mysql –-local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 
set global local_infile = 1;

3、load加载数据

load data local infile '/root/load_user_100w_sort.sql' into table tb_user
fields terminated by ',' lines terminated by '\n' ;

在这里插入图片描述
我们看到，插入100w的记录，17s就完成了，性能很好。

注意：在load时，主键顺序插入性能高于乱序插入

3.2、主键优化

在上一小节，我们提到，主键顺序插入的性能是要高于乱序插入的。 这一小节，就来介绍一下具体的原因，然后再分析一下主键又该如何设计。

3.2.1、数据组织方式

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

在这里插入图片描述
行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

在这里插入图片描述
在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不了，将会存储到下一个页中，页与页之间会通过指针连接。

3.2.2、页分裂

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

1、主键顺序插入效果

a、从磁盘中申请页，主键顺序插入

在这里插入图片描述
b、第一个页没有满，继续往第一页插入

在这里插入图片描述
c、当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接

在这里插入图片描述
2、主键乱序插入效果

1、加入1#, 2#页都已经写满了，存放了如图所示的数据

在这里插入图片描述
2、此时再插入id为50的记录

会再次开启一个页，写入新的页中吗？

在这里插入图片描述
不会。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。

在这里插入图片描述
但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。

在这里插入图片描述
但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。

在这里插入图片描述
移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。

在这里插入图片描述
上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。

3.2.3、页合并

目前表中已有数据的索引结构(叶子节点)如下：

在这里插入图片描述
当我们对已有数据进行删除时，具体的效果如下：

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

在这里插入图片描述
当我们继续删除2#的数据记录。

在这里插入图片描述
当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

在这里插入图片描述
删除数据，并将页合并之后，再次插入新的数据20，则直接插入3#页。

在这里插入图片描述
这个里面所发生的合并页的这个现象，就称之为 “页合并”。

3.2.4、索引设计原则

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

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

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

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

3.3、order by优化

MySQL的排序，有两种方式：

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

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

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

测试：

1、数据准备

把之前测试时为tb_user表所建立的部分索引删除

drop index idx_user_phone on tb_user; 
drop index idx_user_phone_name on tb_user; 
drop index idx_user_name on tb_user;

在这里插入图片描述

2、执行排序SQL

explain select id,age,phone from tb_user order by age;

在这里插入图片描述

explain select id,age,phone from tb_user order by age, phone;

在这里插入图片描述
由于 age, phone 都没有索引，所以此时再排序时，出现Using filesort， 排序性能较低。

3、创建索引

-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

4、创建索引后，根据age, phone进行升序排序

explain select id,age,phone from tb_user order by age;

在这里插入图片描述

explain select id,age,phone from tb_user order by age , phone;

在这里插入图片描述
建立索引之后，再次进行排序查询，就由原来的Using filesort， 变为了 Using index，性能就是比较高的了。

5、创建索引后，根据age, phone进行降序排序

explain select id,age,phone from tb_user order by age desc , phone desc;

在这里插入图片描述
也出现 Using index， 但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。 在MySQL8版本中，支持降序索引，我们也可以创建降序索引。

6、 根据phone，age进行升序排序，phone在前，age在后

explain select id,age,phone from tb_user order by phone , age;

在这里插入图片描述
排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候， age是第一个字段，phone是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现 Using filesort。

7、根据age, phone进行降序一个升序，一个降序

explain select id,age,phone from tb_user order by age asc , phone desc;

在这里插入图片描述
因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。

在这里插入图片描述
为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone 倒序排序。

8、创建联合索引 (age 升序排序，phone 倒序排序)

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

在这里插入图片描述
9、再根据age, phone进行降序一个升序，一个降序

explain select id,age,phone from tb_user order by age asc , phone desc;

在这里插入图片描述
升序/降序联合索引结构图示：

在这里插入图片描述
由上述的测试,我们得出order by优化原则：

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

  • 尽量使用覆盖索引。

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

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

3.4、group by优化

分组操作，我们主要来看看索引对于分组操作的影响。

首先我们先将 tb_user 表的索引全部删除掉。

drop index idx_user_pro_age_sta on tb_user; 
drop index idx_email_5 on tb_user; 
drop index idx_user_age_phone_aa on tb_user; 
drop index idx_user_age_phone_ad on tb_user;

在这里插入图片描述
接下来，在没有索引的情况下，执行如下SQL，查询执行计划：

explain select profession , count(*) from tb_user group by profession;

在这里插入图片描述
然后，我们在针对于 profession ， age， status 创建一个联合索引。

create index idx_user_pro_age_sta on tb_user(profession , age , status);

紧接着，再执行前面相同的SQL查看执行计划。

explain select profession , count(*) from tb_user group by profession;

在这里插入图片描述
再执行如下的分组查询SQL，查看执行计划：

在这里插入图片描述

我们发现，如果仅仅根据age分组，就会出现 Using temporary ；而如果是 根据profession,age两个字段同时分组，则不会出现 Using temporary。原因是因为对于分组操作，在联合索引中，也是符合最左前缀法则的。

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

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

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

3.5、limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比：

在这里插入图片描述
通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。

因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前 2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

3.6、count优化

select count(*) from tb_user;

在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的 count，MyISAM也慢。

  • InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

用法：count（*）、count（主键）、count（字段）、count（数字）

count 用法含义
count (主键)InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)
count (字段)没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count (数字)InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
count (*)InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)。

**所以尽量使用 count(*)。 **

3.7、update优化

我们主要需要注意一下update语句执行时的注意事项。

update course set name = 'javaEE' where id = 1;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下SQL时。

update course set name = 'SpringBoot' where name = 'PHP';

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。

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

本文章参考B站 黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括 ，仅供个人学习使用，部分内容为本人自己见解，与黑马程序员无关。

BugMaker.Chovy
关注
专栏目录
如何优化MySQL insert性能
tigernorth的专栏
10-20 1万+
对于一些数据量较大的系统，面临的问题除了是查询效率低下，还有一个很重要的问题就是插入时间长。我们就有一个业务系统，每天的数据导入需要4-5个钟。这种费时的操作其实是很有风险的，假设程序出了问题，想重跑操作那是一件痛苦的事情。因此，提高大数据量系统的MySQL insert效率是很有必要的。     经过对MySQL的测试，发现一些可以提高insert效率的方法，供大家参考参考。 1. 一条SQ
mysql大量插入优化_MySQL批量SQL插入性能优化
weixin_29854481的博客
01-19 597
对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，可能每天花费在数据导入上的时间就会长达几个小时之久。因此，优化数据库插入性能是很有意义的。网络上的牛人很多，总会有一些手段可以提高insert效率，大家跟我一起分享一下吧：1. 一条SQL语句插入多条数据。我们常用的插入语句大都是一条一个insert，如：INSERTINTO`insert_ta...
mysql 优化配置 大批量数据插入_MYSQL开发性能研究——批量插入的优化措施
weixin_39904268的博客
12-20 288
一、我们遇到了什么问题在标准SQL里面，我们通常会写下如下的SQL insert语句。 INSERT INTO TBL_TEST (id) VALUES(1);很显然,在MYSQL中，这样的方式也是可行的。但是当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，...
关于mysql上万条数据同时插入时的性能优化
weixin_33686714的博客
12-10 598
2019独角兽企业重金招聘Python工程师标准>>> ...
MySQL 进阶 索引 -- SQL优化（插入数据优化：导入本地文件数据、主键优化、order by优化、group by优化、limit优化、count优化、update优化）
天行健 君子以自强不息，地势坤 君子以厚德载物。
04-19 1013
MySQL 进阶 索引 SQL优化（插入数据优化：导入本地文件数据、主键优化、order by优化、group by优化、limit优化、count优化、update优化）
MySQL进阶-SQL优化（插入和主键优化）
04-06
MySQL进阶-SQL优化（插入和主键优化）
面试必问、工作实用的MYSQL进阶之SQL优化大全
07-27
4. **SQL优化技巧**： - 使用`EXPLAIN`分析SQL执行计划，关注`type`和`extra`，目标是降低全表扫描（All）并提高到ref或range级别。 - 尽量使用`VARCHAR`而非`CHAR`，节省空间。 - 避免使用`SELECT *`，应明确列...
mysql进阶学习
10-15
本资源摘要将涵盖 MySQL 进阶学习的主要知识点，包括数据库设计、SQL 优化、查询分析、索引优化、视图和存储过程等。 数据库设计 在 MySQL 进阶学习中，数据库设计是非常重要的一部分。开发者需要了解如何设计一个...
【MySQL进阶学习】优化索引与分区表
12-14
【MySQL进阶学习】优化索引与分区表 在MySQL数据库管理中，索引和分区是两个关键的优化策略，用于提升查询性能和管理大规模数据。这篇文章将深入探讨这两个主题，帮助你理解如何有效地利用它们。 优化索引是提高...
MySQL进阶一.zip，包括数据库备份文件、说明和sql文件
最新发布
04-11
在这个“MySQL进阶一.zip”压缩包中，我们有三个关键文件，分别是MySQL进阶一.docx文档、20240411084711.nb3笔记文件和test_advanced1.sql脚本文件。这些文件将帮助我们深入理解MySQL的高级特性和操作，特别是关于...
mysql 主键排序_MySQL按主键排序
weixin_39680154的博客
01-18 545
Some SQL servers allow for a generic statement such as ORDER BY PRIMARY KEY. I don't believe this works for MySQL, is there any such workaround that would allow for automated selects across multiple ...
mysql 主键重排序
chenyusbyongde的博客
11-18 361
ALTER TABLE `table_name` DROP `id`; ALTER TABLE `table_name` ADD `id`   int (11) NOT NULL  first;  //first 表示是将id 最为   第一列存放   ALTER TABLE `table_name` MODIFY COLUMN `id`  int (11)   NOT NUL
Mysql主键id重排序
qq_55952736的博客
05-19 777
来自：MySql自增Id重排问题_mysql 修改插入顺序id_无名°的博客-CSDN博客。消除因删除而产生错乱的主键id顺序。
数据库索引及优化
世上无难事，只要肯攀登
03-28 1309
数据库索引以及优化
MySQL索引
qq_56370103的博客
08-19 3343
索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。接下来就来了解一下索引的使用吧！
一个有趣的SQL优化案例
qq_39744051的博客
04-20 600
问题描述 分析 表的信息 估算cost start-up cost run cost 执行计划 实际执行时间 从内核视角来分析 解决方案 写在最后 Coding过程中经常会写SQL语句，有时写的SQL出现慢查询而被DBA鄙视。 今天我们一起从使用者，DBA，内核开发三个不同角度来分析和解决一个SQL性能问题，来重新认识逻辑优化与物理优化。 ..
（译）MySQL 8.0实验室---MySQL中的倒序索引（Descending Indexes）
weixin_33701294的博客
08-21 686
  译者注：MySQL 8.0之前，不管是否指定索引建的排序方式，都会忽略创建索引时候指定的排序方式（语法上不会报错），最终都会创建为ASC方式的索引，在执行查询的时候，只存在forwarded（正向）方式对索引进行扫描。关于正向索引和反向索引，逻辑上很容易理解，这里有两个相关的概念：正向索引或者反向（倒序）索引，两者都是在构建B树索引时候的相关字段排序方式，是B索引树的逻辑存储方式正向扫描（f...
MySQL性能优化的21个最佳实践（转载）
aowang3732的博客
12-02 712
今天，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。关于数据库的性能，这并不只是DBA才需要担心的事，而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构，对操作数据库时(尤其是查表时的SQL语句)，我们都需要注意数据操作的性能。这里，我们不会讲过多的SQL语句的优化，而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。　　...
mysql 反转索引_MySQL 8.0 倒序索引的应用
weixin_30810127的博客
01-19 985
大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子已经很久没写文章了，今天在浏览网站的时候，看到有人提了以下问题为什么下面的SQL没有用到index condition pushdown提问者判断到 下面的SQL 中column4的代价更高所以创建了idx1 ( column1,column2,column4,column3)这样的索引，他认为应该走icp但是实际上，没有走SE...
MySQL进阶SQL脚本案例资源合集：从入门到精通
资源摘要信息:"MySQL入门到进阶sql脚本案例资源合集.zip" MySQL作为一个广泛使用的开源关系型数据库管理系统（RDBMS），它以高效、稳定、可靠性著称。本资源合集是一套从基础到高级的SQL脚本案例集合，旨在帮助用户...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值