一文带你快速掌握SQL优化（添加数据优化、orderby优化）

最新推荐文章于 2024-07-07 16:52:52 发布
最新推荐文章于 2024-07-07 16:52:52 发布
阅读量926 收藏 12
点赞数 27
分类专栏： MySQL 文章标签： sql oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_69580082/article/details/137121161
版权

文章目录

SQL优化

高效添加数据

Insert语句的优化

先回顾一下向数据库中添加数据的基本操作：

当我们想要向数据库中的表tb中插入一条数据时，可以采用insert into语句：

insert into tb values(1,'value1');

当我们想要向数据库插入多条数据时，可以执行多条insert into语句：

insert into tb1 values(1,'value1');
insert into tb2 values(2,'value2');
insert into tb3 values(3,'value3');
.....

但是当想插入数据很多时，行数会非常密集，而且代码要多次请求数据库，每次请求都会消耗一定的性能，要怎样进行优化呢？

优化方案1：批量插入

  • 一般情况下都采用批量插入来添加数据
  • 批量插入的思想就是把多行数据压缩成一行，只需要远程请求一次数据库，且代码更加简洁
  • 但是一次性批量插入的数据建议控制在500条之内，如果多于500条，则应该分多个批次处理
Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');

优化方案2：手动控制事务

  • 通过手动控制事务添加数据有多种好处

  • 一般情况下，MySQL自动为每条插入语句创建一个事务，这样可能会导致大量的日志记录，从而降低系统性能。通过手动控制事务，可以将多条执行单元合并为一个事务，从而避免了多个事务的开销。

  • 手动控制事务可以帮助我们保证数据的完整性和一致性。

反例：

Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');
Insert into tb values(4,'value1'),(5,'value2'),(6,'value3');
Insert into tb values(7,'value1'),(8,'value2'),(9,'value3');

正例：

start transaction;
Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');
Insert into tb values(4,'value1'),(5,'value2'),(6,'value3');
Insert into tb values(7,'value1'),(8,'value2'),(9,'value3');
commit;

优化方案3：主键顺序插入

  • 不管数据量如何，推荐采用主键顺序插入来添加数据。

  • 主键顺序插入，性能要高于乱序插入。原理见后续的主键优化部分。

#主键乱序插入 : 6 2 9 7 2  
#主键顺序插入 : 1 2 4 6 8

优化方案4：load指令添加数据

如果一次性需要插入大批量数据(比如几百万的记录)，使用insert语句可能需要花费几十分钟，此时可以使用MySQL数据库提供的load指令，这个过程只需要花费几十秒。

如何采用load指令大批量添加数据？

  • 如果是常规的连接数据库，只需要输入以下指令：
mysql -u root -p
  • 如果需要用load指令，需要额外添加-local-infile参数：
mysql –-local-infile -u root -p
  • 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关：
set global local_infile = 1;

  • 我们也可以事先通过以下指令来查看local_infile全局参数是否开启：

  • 如果local_infile显示为0，则表示开关并未开启，则需要手动设置为1。

select @@local_infile;

在这里插入图片描述
举个栗子：

  • 假设要上传100万条数据，要上传的文件路径是'/root/load_user_100w_sort.sql'，则往表tb1中添加数据的完整load指令是：
load data local infile '/root/load_user_100w_sort.sql' into table tb1 fields terminated by ',' lines terminated by '\n' ;

  • 其中，

    • load data local infile是固定格式；

    • into table tb1表示向表tb1添加数据；

    • fields terminated by ','表示每一个字段之间采用逗号分割；

    • lines terminated by '\n'表示每一行之间采用换行符分割。

主键优化的原理

为什么主键顺序插入的性能要大于乱序插入？

  • 首先了解在InnoDB存储引擎中数据的组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。
  • 在索引组织表中，页是InnoDB磁盘管理的最小单元，其固定大小为16K。页可以为空，也可以填充一半，也可以填充满。每个页至少包含2行数据(如果只包含一行数据则是链表结构；如果一行数据过大超过阈值会导致行溢出)，根据主键排列。

在这里插入图片描述
页分裂与页合并现象

主键顺序添加数据时的过程是怎样的？

  • 主键顺序插入，就是先在第一个页中填写数据，如果第一个页满了就写第二个页中，依此类推

  • 从磁盘中申请一页，主键按照顺序进行插入
    在这里插入图片描述

  • 当第一页存满之后，会继续申请第二页，页与页之间通过双向指针进行连接；当第二页也存满，就会申请第三页；
    在这里插入图片描述
    主键乱序添加数据时的过程是怎样的？

  • 主键乱序插入，不是依此往后插入，因为叶子节点主键之间是有序的，所以就产生了页分裂现象

  • 假设1页和2页都已经存满，而此时主键50想要插入，不会直接写入新的页，因为索引结构的叶子节点是有顺序的。

  • 按照顺序，主键50应该存储在47之后。
    在这里插入图片描述
    在这里插入图片描述

  • 但1页显然已经存满，随后会进行“页分裂”的过程，即先开启第三页，然后将1页后一半的数据，移动到3页，然后在3页中插入50。
    在这里插入图片描述

  • 最后2#和3#两页位置互换，以符合主键排序规则。最终过程如下：
    在这里插入图片描述
    与页分裂相对，还有页合并现象：

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

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

总结

  • 插入多条数据时，尽量选择批量插入

    • 因为批量插入只需要远程请求一次数据库，且代码更加简洁

  • 插入多条数据时，尽量选择手动控制事务插入

    • 因为通过手动控制事务，可以将多条执行单元合并为一个事务，从免了多个事务的开销，同时保证数据的完整性和一致性。

  • 插入大量数据时，可以选择MySQL提供的load指令插入

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

    • 因为当主键乱序插入时，会产生“页分裂”，消耗性能

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

    • 因为每次生成的UUID之间无序，插入时为主键乱序插入，会产生“页分裂”，消耗性能

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

    • 因为修改主键后还需对索引结构进行修改，花费代价较大。

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

order by优化

MySQL两种排序方式：

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

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

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

两种排序方式案例演示

假设在表tb_user中包含有两个字段age和phone，我们想通过这两个字段进行排序。

假如我们没有创建age和phone字段的索引，直接进行order by排序：

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

结果如下：由于字段age和phone都没有建立索引，因此使用Using filesort排序
在这里插入图片描述
假如我们先创建字段age和phone的联合索引，再进行排序：

create index idx_user_age_phone_aa on tb_user(age,phone);

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

在这里插入图片描述

如何使用order by进行SQL优化

在上面我们创建了字段age和phone的联合索引，此时在表中默认是按照升序排列的。

我们可以通过以下指令，表示查看表tb_user当中的索引：

show index from tb_user;

可以发现age和phone的联合索引默认按照A，即升序排列
在这里插入图片描述

情况一（得到优化）

假如我们此时根据age, phone进行降序排序：

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

结果如下：Backward index scan，表示反向扫描索引，也属于采用索引排序。因为在MySQL中默认索引的叶子节点升序排序的，

而此时我们查询排序时按照降序查询，因此为反向扫描索引，这种方式性能是比较好的，因为通过索引排序
在这里插入图片描述

情况二（未得到优化）

假如根据phone，age进行升序排序，phone在前，age在后。

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

结果如下：由于此时排序顺序为phone在前，age在后，而创建联合索引时的顺序是age在前，phone在后，不满足最左前缀法则，因此通过using filesort排序
在这里插入图片描述

情况三（未得到优化）

假如根据age, phone进行降序一个升序，一个降序

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

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

情况四（得到优化）

重新创建字段age和phone的联合索引，**并且指定了排序的顺序，**age 升序排序，phone 倒序排序

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

结果如下：
在这里插入图片描述

索引结构可视化

如果按照字段age和phone都进行升序排序，索引结构示意图是怎样的？

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

对应的索引结构如下：
在这里插入图片描述
如果按照字段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)。

参考学习资料：黑马程序员

cy的Java学习笔记
关注
专栏目录
Sql优化(六):如何优化order by?
nzbing的博客
09-09 737
order by
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL
热门推荐
dvlinker的技术专栏
10-08 4万+
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL。
数据库sql之order by优化
Yangyg_0818的博客
02-12 670
建立复合索引（age,birth），使用order by排序产生filesort情况 情况1：Select *from user where age>20 order by age;没有filesort,用到了index索引,原因是age是索引头 情况2：Select *from user where age>20 order by age, birth;没有filesort且用到...
34.进阶-SQL优化-order by优化
最新发布
m0_63464979的博客
07-07 870
也出现 Using index， 但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。F. 根据phone，age进行升序排序，phone在前，age在后。
SQL高级之order by优化
youhebuke225的博客
06-13 2622
假设 KEY a_b_c(a,b,c)
SQL表连接、 order by 优化
ljl_name的专栏
03-07 554
select * from (select t2.* from (select rownum r, t1.* from (Select * From (Select * From crm_Vd健康档案 Where 1 = 1 And 有效状态b = '1') T order by 显示顺序, 系统序号) t1 where rownum <= 100) t2 where t2.r > 0...
一文掌握数据库最核心的功能——优化器
华章IT官方博客
04-16 1092
导读：优化器是数据库最核心的功能，也是最复杂的一部分。它负责将用户提交的SQL语句根据各种判断标准，制定出最优的执行计划，并交由执行器来最终执行。优化器算法的好坏、能力的强弱，直接决定了...
一文带你掌握 OceanBase 社区版部署细节及原理
seeyousoonhhh的博客
08-24 1996
作者：庆涛。 DBA，熟悉 Oracle / MySQL / SQLServer / OceanBase , 现主要从事 OceanBase 产品和解决方案推广工作。 个人公众号：OceanBase技术闲谈 OceanBase在2021年6月1日发布了社区版后（官网：https://open.oceanbase.com/)，立即有很多网友开始下载使用。部分朋友反馈在安装部署上不是很顺利，本文主要是提供一种简易的社区版安装方法，帮助大家快速了解有关原理以及具体操作步骤。 在社区版发布之前，在Ocea.
【ChatGPT】实用 Prompt 指令大全 —— 一文教你如何更好地挖掘 GPT 的价值
AI天才研究院
04-15 2万+
ChatGPT 是由 OpenAI 开发的一种先进的大型语言生成模型，能够理解和生成准确、自然且连贯的文本。文本生成数据分析内容审核语义理解自然语言推理以及其他众多任务ChatGPT是一种基于GPT-3.5架构的大型语言模型，由OpenAI公司开发。该模型在自然语言处理领域有着广泛的应用，可以用于文本生成、文本分类、情感分析、问答系统等任务。本文将从模型的架构、训练方式、优缺点等方面对ChatGPT进行详细介绍。一、模型架构。
MySQLSQL排序优化
三思的博客
03-21 2617
文章目录一、MySQL排序原理1.1 MySQL如何处理排序操作？1.2 两种排序扫描算法：1.3 排序优化方法论二、排序优化手段2.1 利用索引优化order by limit2.2 只对驱动表进行排序2.3 临时表排序2.4 利用覆盖索引优化排序 一、MySQL排序原理 1.1 MySQL如何处理排序操作？ 1）当SQL中排序字段可有效利用索引时，无论是单表查询还是多表关联查询，通过索引的有序性，我们可以快速的对order by字段进行排序检索。 2）当SQL中排序字段无法有效利用索引时：   a
如何优化sql中的orderBy？
weixin_45701550的博客
05-22 1166
在使用数据库进行数据查询时，难免会遇到基于某些字段对查询的结果集进行排序的需求。在sql中通常使用orderby语句来实现。将需要排序的字段放到 该关键词后，如果有多个字段的话，就用","分割。 select * from table t order by t.column1,t.column2; 上面的sql表示查询表table中数据，然后先按照column1排序，如果column1相同的话，在按照column2排序，排序的方式默认是降序。当然排序方式也是可以指定的。在被排序字段后添加 DESC,ASE
SQL 排序查询优化
kf_panda
10-21 483
优化前 mysql> explain SELECT ROADMONITORID_,TRAFFICID_,PLATENUMBER_,PASSTIME_,SPEED_,PLATECOLOR_ FROM hadoop_firstinto -> WHERE PASSTIME_>='2015-01-23 13:59:16' AND PASSTIME_<='2015-10-23 13:59:16'
MySQL进阶-----order by优化和group by优化
m0_73633088的博客
04-13 2114
本期我们继续学习sql优化，其中分为order by 排序优化和group by分组优化。数据准备：可以查阅此链接（MySQL 进阶-----索引使用规则-CSDN博客。
第48讲：SQL优化之ORDER BY排序查询的优化
江晓龙的博客
02-17 1069
针对3.5中排序后的现象，两个字段不同的排序规则，索引默认是升序排序，nl字段的升序排序没有任何效率问题，但是lxfs字段当下拿到的是nl字段升序后的结果集，还想要对lxfs字段降序排序，只能拿到排序缓冲区中进行额外的排序，因为在nl字段升序后的结果集里，lxfs字段也是升序的，但是我们要的是降序的效果，所以只能额外排序了。为不同的字段设置了不同的排序规则后，在索引的叶子节点里就已经排序好了，上面图的所有字段排序规则都是升序，下面图中lxfs字段是降序，可以看到每个索引元素中手机号都是降序排序了。
SQL优化之利用索引排序
cuiruidu3106的博客
07-10 3490
近期在做数据库的慢SQL优化，从数据库慢日志看到一个SQL执行时间大概1.7s，高峰时候达到十几秒 mysql>SELECTid,service_seq,order_item_seq,s...
order by 原理和优化
Meiko记录
10-12 1837
一个使用order by 的简单例子 假设用一张员工表，表结构如下： CREATE TABLE `staff` ( `id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id', `id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码', `name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名', `age` INT ( 4 ) NOT NULL COMMENT '年龄', `city` VAR
order by 语句怎么优化？
ma_nong33的博客
02-02 412
它们的切换通过一个参数控制的这个参数就是max_length_for_sort_data 它表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为。也就是说，这条SQL语句需要排序。如果要排序的数据小于sort_buffer_size，排序在sort_buffer 内存中完成，如果要排序的数据大于sort_buffer_size，则。：如果sort_buffer内存足够，那效率是最高的，但如果sort_buffer内存不够的话，就需要用到磁盘临时文件，排序的性能会很差。
SQL Server数据库查询性能优化
Vincero
11-06 417
<br />•       在SQL查询中应尽量使用索引列加快查询速度。<br />•       在并行处理时注意锁的粒度。<br />•       尽量使用正逻辑而不是非逻辑。<br />•       避免使用关键字like。<br />•       只要可能，尽量在搜索条件中使用精确的比较和值的域。<br />•       使用order by会影响查询的速度。任何order by语句的非索引项或者计算表达式将减低查询。<br />•       连接查询比子查询效率高。<br />•    
精通MySQL索引查询优化：一文教你掌握
这篇文章旨在帮助读者深入理解并掌握如何优化MySQL中的索引，以提高查询效率。以下是一些关键点： 1. **了解MySQL存储引擎** MySQL支持多种存储引擎，其中InnoDB是最常用的一个，它支持事务处理、行级锁定，并采用...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值