MySQL 进阶（三）【SQL 优化】

1、SQL 优化

1.1、插入数据优化

1.1.1、Insert 优化

1、批量插入

插入多条数据时，不建议使用单条的插入语句，而是下面的批量插入：

INSERT INTO tb_name VALUES (),(),(),...;

批量插入建议一次批量 500~100 条，如果数据量比较大，建议通过多条批量插入语句来插入；

2、手动提交事务

        MySQL 默认会开启事务，但是默认每执行一次插入语句就开启和关闭一次事务，所以会有大量的事务启动关闭开销；建议使用手动提交事务

START TRANSACTIONS;
INSERT INTO tb_name VALUES (),(),(),...;
INSERT INTO tb_name VALUES (),(),(),...;
INSERT INTO tb_name VALUES (),(),(),...;
...
COMMIT;
3、主键顺序插入

在插入数据时，建议尽量顺序插入主键，而不是乱序插入：

主键顺序插入(高效)：1 2 3 4 5 ...

主键乱序插入(低效)：5 3 4 2 1 ...

1.1.2、load 大批量插入数据

使用 load 指令将本地磁盘文件中的数据插入到数据库表当中：

# 客户端连接MySQL服务器时，加上参数 --local-infile
mysql --local-infile -uroot -p
# 设置全局参数 local_infile = 1
set global local_infile = 1;
# 执行 load 指令将准备好的数据加载到表中
load data local infile '/path_to_data' into table table_name fields terminated by ',' lines terminated by '\n';

这里的 load 指令有点像 HQL 中的 load：

LOAD DATA [LOCAL] INPATH '/opt/module/data/xxx.txt' TO TABLE table_name;

HQL 的 load 命令并不需要指定分隔符，因为在建表的时候我们已经在 row format 中设置的文件的分隔符了；此外，这里想到今天使用 HQL load 命令的一些需要注意的问题：

  • 在向分桶表 load 数据的时候，不能从 local 直接 load，而是得先上传到 hdfs 上，再从 hdfs load 到分桶表才行

1.2、主键优化

1.2.1、数据组织方式

在 InnoDB 存储引擎当中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（IOT）

之前我们在学习索引的时候知道，InnoDB 存储引擎中的索引可以分为聚集索引和二级索引，而聚集索引正是由主键构成的一颗B+Tree，它的叶子节点存储的是行数据；

接下来，我们看一下当我们往数据库表中插入数据的时候，它的流程是什么样的？

1.2.2、页分裂

page 可以填满，也可以为空，但在 InnoDB 存储引擎规定，在一个 page 中至少包含 2 行数据；

主键顺序插入

当主键顺序插入时，一切都非常平静：

主键乱序插入

下面，我们看一下主键乱序插入时的情况：

上面，我们的主键都是乱序插入的，可以看到，现在来了一个主键为 50 的 row，按道理它是应该放到主键为 47 的前面，主键为 23 的后面（也就是 page 1）的，但是显然 page 1 现在已经存满了，那么接下来就会发生页分裂：

首先，page 1 （因为主键为 23 的数据当前理应插入到 page 1）会把自己 50% 之后的数据移动到一个新的 page 当中，然后将要主键为 23 的新数据页添加进去，最后将原本页之间的连接断开，重新建立页的连接；

所以，不难想到如果是大量数据的场景下，主键乱序插入时会出现频繁的页分裂现象，性能很低；与新增数据相反的是删除数据，删除数据又会引起页合并：

1.2.3、页合并

在 MySQL 中，当删除一行记录时，实际上数据并不会被物理删除，只是记录被标记为删除并且它的空间变得允许被其它记录回收使用；

当页中被删除的记录达到了 50%，InnoDB 会开始寻找最靠近的页（前或后），看看能否将两个页合并以优化空间使用；

 1.2.4、主键设置原则

  • 满足业务需求的情况下，尽量降低主键的长度。（因为在一张表中，聚集索引只有一个，而二级索引可以有多个，如果主键很长，二级索引也有很多，那么就会在存储时会消耗大量的磁盘空间，查询时也会消耗大量的磁盘IO）
  • 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键；（不会出现页分裂现象）
  • 尽量不要使用 uuid 做主键或者是其它自然主键，如身份证号；（主键长度太长，而且无序）
  • 尽量避免主键的修改；

1.3、order by 优化

  • Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作；所有不是通过索引直接返回排序结果的排序都是 FileSort 排序；
  • Using index：通过有序索引顺序扫描直接返回有序数据，这种情况就是 using index，不需要额外排序，操作效率高；

也就是说，我们在优化 order by 的时候，尽量优化成 using index；下面我们看一条普通的 order by 语句：

可以看到，在未建立索引时，order by 语句默认走的是 filesort；下面我们为 age 字段建立一个索引：

CREATE INDEX idx_student_no ON student(no);

注意：这里搜索时，我设置投影的字段是 id 和 no，其中 id 是主键，no 我们刚创建了索引，不能使用 select * ，因为只有使用覆盖索引（查询使用了索引，并且需要返回的列在该索引中全部能够找到对应的值）才能命中索引，不然索引不生效；

可以看到，即使是降序排序也是索引也是可以命中优化的；

 现在我们创建一个联合索引：idx_student_no_name，做一个测试：

可以看到，当 order by 的字段顺序和联合索引相反时，只有字段 no 能被索引命中，name 不可以，这是因为违背了最左前缀法则；继续：

可以看到，当 order by 的两个字段分别升序和降序排列时，降序的字段无法被索引命中，这是因为：

这是因为默认创建索引时，索引字段都是按照升序进行排列的，所以我们可以根据之后的需求在创建索引时，根据排序规则进行创建：

CREATE INDEX idx_student_no_name_ad ON student(no asc,name desc);

此时，再次查看查询计划：

可以看到，这样就解决了字段排序规则不同的问题；

 注意：

  • 只有联合索引需要注意创建时的排序规则，单列索引不需要，因为单列索引默认升序，反向扫描只需要反向扫描即可。
  • 如果不可避免 filesort ，大数据排序时，可以适当增加排序缓冲区的大小 sort_buffer_size （默认 256 K）

1.4、group by 优化

这里我们同样讨论的是索引对于 group by 的影响，因为分组也是通过索引来提高查询效率的：

可以看到，在未建立索引前，使用 group by 语句时效率很低（临时表），但是创建索引后就可以走索引了；

可以看到，这次当我们用 age 字段 group by 时，又出现了 temporary ，效率依然不够好，这是因为违背了联合索引的最左前缀法则；可当我们同时使用 profession 和 age 进行 group by 时，索引再次命中；

但是有时候业务逻辑不需要我们去 group by 那么多的字段怎么办？

其实，就像上面这样，我们也可以通过前面给联合索引左边的字段加个 where 条件，让它满足最左前缀法则即可；

1.5、limit 优化

在大数据场景下，比如 limit 10000000,10 ，此时 MySQL 需要排序前 100000010 条记录（存储引擎会把这 100000010 条记录返回给服务层的缓存），并返回后 10 条记录，其它丢弃，查询排序的代价非常大；这一类问题也叫做深度分页：

MySQL 深度分页是指在分页查询数据量比较大的表时，需要访问表中的某一段数据，而这段数据的位置非常靠后，需要通过较大的 offset 来获取目标数据。

阿里巴巴《Java 开发手册》：

1.5.1、覆盖索引 + 子查询

1.5.2、inner join 延迟关联

上面这两种方式没什么区别，下面是 inner join，上面是笛卡尔积，但是因为设置的过滤条件，所以等价于一个 inner join；对于 limit 的优化原理，简单来讲就是：控制返回的总页数。

        对于上面的 limit 10000000,10 来说，它会返回给服务端 10000010 条记录，然后再根据 offset 挨个抛弃前 10000000 条记录，返回给客户端剩余的 10 条记录。

        可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据跟只拷贝行数据里的其中一两个列字段耗时是不同的，这就让原本就耗时的操作变得更加离谱。

        因为前面的offset条数据最后都是不要的，就算将完整字段都拷贝来了又有什么用呢，所以我们可以将sql语句修改成下面这样：

select * from tb_user where id >=(select id from tb_user order by id limit 10000000, 1) order by id limit 10;

上面这条sql语句，里面先执行子查询 select id from page order by id limit 6000000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到6000000+1条数据，然后server层会抛弃前6000000条，只保留最后一条数据的id。

但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

在拿到了上面的id之后，假设这个id正好等于10000000，那sql就变成了

select * from tb_user where id >=(10000000) order by id limit 10;

这样 innodb 再走一次主键索引，通过B+树快速定位到id=6000000的行数据，时间复杂度是lg(n)，然后向后取10条数据。

关于深度分页，知乎这篇文章讲的很不错，我也是受启发于这篇文章；

1.6、count 优化

  • count 是一个聚合函数，对于返回的结果集，一行一行进行判断，只有不是 NULL 才会计数（count(字段)的时候，count(*)或者count(id)依然计算null）；
SELECT COUNT(*) FROM table_name;

上面的 SQL  是查询当前表的总行数，不同的存储引擎的效率是不一样的：

  • 对于 MyISAM 而言，它会把表的总行数存到磁盘上，所以不加条件直接查询 count(*) 会直接返回结果，O（1）的时间复杂度；
  • 而对 InnoDB 而言，它就只能遍历整张表了，性能很低；

可以看到，如果 count(*) 的查询语句中包含 where 过滤条件，不管是 MYISAM 还是 InnoDB ，性能都很差，所以我们需要对它进行优化：

1.6.1、count 的几种用法

对于 count，我们使用的无非就是那几种：

  • count(主键)
    •  InnoDB 会遍历整张表，把每一行主键取出来返回给服务层，服务层拿到主键后直接进行累加（主键不可能为空）；
  • count(字段)
    •  InnoDB 会遍历整张表，把每一行字段值取出来返回给服务层，如果字段有 not null 约束，那么就直接按行累加；如果没有，那么就对非 null 的值进行计数
  • count(1)
    •  InnoDB 遍历整张表，但是不取值。服务层会对返回的每一行放一个数字 1 进去，直接按行进行累加
  • count(*)
    •  InnoDB 同样遍历整张表，但是不会把字段取出来，而是专门做了优化。服务层直接按行进行累加

按照排序效率：count(字段) < count(主键) < count(1) < count(*) ，所以尽量使用 count(*)，因为数据库专门对它做了优化；

1.7、update 优化

关于 update 语句需要注意的就是，update 的条件一定要是索引字段（比如主键），因为只有更新条件的字段是索引列才会是行锁，否则将是表锁

可以看到，当我们的更新条件是 no 字段时（不是索引列），当另一个客户端去更新数据时直接被阻塞，最后甚至超时更新失败；

所以，当我们在使用 update 语句的时候，一定要注意尽量使用索引字段做为更新条件去更新，否则就会出现行锁升级为表锁，并发性能就会降低；因为 InnoDB 的行锁是针对索引加的锁，而不是针对记录加的锁！ 

总结

  • 插入数据
    • inset 语句，大数据量（分成500~1000的记录批量插入）建议使用批量插入，而且建议手动事务（避免频繁创建销毁事务开销）、主键顺序插入（避免页分裂，顺序插入性能高）
    • 大批量数据：load data local infile
  • 主键优化
    •  主键设计应尽量短、顺序插入（建议 auto_increment 而不是 uuid，比如身份证号，不仅长度长，而且无序）
  • order by 优化
    •  using index：直接返回数据（不需要再去服务层缓冲区排序），性能高
    • using filesort：需要将查询返回的结果去服务层缓冲区去排序
    • 所以在对 order by 进行优化时，其实是使用索引来进行优化的；涉及导排序的字段尽量建立索引，同时注意创建索引时的升序降序问题
    • 尽量使用覆盖索引而不是 select *
  • group by 优化
    •  索引，多字段分组时遵循最左前缀法则
  • limit 优化
    •  深度分页性能低，使用覆盖索引 + 子查询
  • count 优化
    •  count(字段) < count(主键) < count(1) < count(*)
  • update 优化
    •  update 的条件字段尽量使用索引字段（尽量主键），InnoDB 的行锁是针对索引加的锁，而不是针对记录加的锁！

所以，总之我们在做 SQL 优化的时候，其实基本都是在针对索引进行优化；

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

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

相关文章

【CSS in Depth 2 精译】2.6 CSS 自定义属性（即 CSS 变量）+ 2.7 本章小结

【CSS in Depth 2 精译】2.6 CSS 自定义属性（即 CSS 变量）+ 2.7 本章小结

文章目录 2.6 自定义属性&#xff08;即 CSS 变量&#xff09;2.6.1 动态变更自定义属性 2.7 本章小结 当前内容所在位置 第一章 层叠、优先级与继承第二章 相对单位 2.1 相对单位的威力2.2 em 与 rem2.3 告别像素思维2.4 视口的相对单位2.5 无单位的数值与行高2.6 自定义属性 …
阅读更多...
讲讲 JVM 的内存结构（附上Demo讲解）

讲讲 JVM 的内存结构（附上Demo讲解）

讲讲 JVM 的内存结构 什么是 JVM 内存结构&#xff1f;线程私有程序计数器​虚拟机栈本地方法栈 线程共享堆​方法区​注意永久代​元空间​运行时常量池​直接内存​ 代码详解 什么是 JVM 内存结构&#xff1f; JVM内存结构分为5大区域&#xff0c;程序计数器、虚拟机栈、本地…
阅读更多...
头歌---数组之Fibonacci数列

头歌---数组之Fibonacci数列

一、数组初始化几种方式 1.数组定义时&#xff0c;数组元素全部赋初值 2.部分数组赋初值 >>>>>前三个元素已知初值 >>>>>后三个元素系统自动赋初值为0 注意: 当定义数组时&#xff0c;如果未对它的元素指定过初值&#xff0c;对于内部局部数组…
阅读更多...
【openwrt】Openwrt系统新增普通用户指南

【openwrt】Openwrt系统新增普通用户指南

文章目录 1 如何新增普通用户2 如何以普通用户权限运行服务3 普通用户如何访问root账户的ubus服务4 其他权限控制5 参考 Openwrt系统在默认情况下只提供一个 root账户&#xff0c;所有的服务都是以 root权限运行的&#xff0c;包括 WebUI也是通过root账户访问的&#xff0c;…
阅读更多...
【C++航海王：追寻罗杰的编程之路】哈希的应用——位图 | 布隆过滤器

【C++航海王：追寻罗杰的编程之路】哈希的应用——位图 | 布隆过滤器

目录 1 -> 位图 1.1 -> 位图的概念 1.2 -> 位图的应用 2 -> 布隆过滤器 2.1 -> 布隆过滤器的提出 2.2 -> 布隆过滤器的概念 2.3 -> 布隆过滤器的插入 2.4 -> 布隆过滤器的查找 2.5 -> 布隆过滤器的删除 2.6 -> 布隆过滤器的优点 2.7…
阅读更多...
视频监控汇聚平台LntonCVS视频集中存储平台解决负载均衡的方案

视频监控汇聚平台LntonCVS视频集中存储平台解决负载均衡的方案

随着技术的进步和企业对监控需求的增加&#xff0c;视频监控系统规模不断扩大&#xff0c;接入大量设备已成常态化挑战。为应对这一挑战&#xff0c;视频汇聚系统LntonCVS视频融合平台凭借其卓越的高并发处理能力&#xff0c;为企业视频监控管理系统提供可靠的负载均衡服务保障…
阅读更多...
6.Neo4j数据库备份

6.Neo4j数据库备份

对neo4j数据进行备份、还原、迁移操作时&#xff0c;要关闭neo4j。 将neo4j作为服务使用进行安装&#xff1a; neo4j install-service 先执行上面的命令&#xff0c;才能执行 neo4j stop 数据备份 执行备份命令&#xff1a; neo4j-admin dump --databasegraph.db --to/ne…
阅读更多...
C++的入门基础（二）

C++的入门基础（二）

目录 引用的概念和定义引用的特性引用的使用const引用指针和引用的关系引用的实际作用inlinenullptr 引用的概念和定义 在语法上引用是给一个变量取别名&#xff0c;和这个变量共用同一块空间&#xff0c;并不会给引用开一块空间。 取别名就是一块空间有多个名字 类型& …
阅读更多...
Docker基本管理1

Docker基本管理1

Docker 概述 Docker是一个开源的应用容器引擎&#xff0c;基于go语言开发并遵循了apache2.0协议开源。 Docker是在Linux容器里运行应用的开源工具&#xff0c;是一种轻量级的“虚拟机”。 Docker 的容器技术可以在一台主机上轻松为任何应用创建一个轻量级的、可移植的、自给自…
阅读更多...
Spring Web MVC入门(2)(请求1)

Spring Web MVC入门(2)(请求1)

目录 请求 1.传递单个参数 2.传递多个参数 3.传递对象 4.后端参数重命名(后端参数映射) 非必传参数设置 5.传递数组 请求 访问不同的路径就是发送不同的请求.在发送请求时,可能会带一些参数,所以学习Spring的请求,主要是学习如何传递参数到后端及后端如何接收. 1.传递单…
阅读更多...
Linux多线程编程-哲学家就餐问题详解与实现（C语言）

Linux多线程编程-哲学家就餐问题详解与实现（C语言）

在哲学家就餐问题中&#xff0c;假设有五位哲学家围坐在圆桌前&#xff0c;每位哲学家需要进行思考和进餐两种活动。他们的思考不需要任何资源&#xff0c;但进餐需要使用两根筷子&#xff08;左右两侧各一根&#xff09;。筷子是共享资源&#xff0c;哲学家们在进行进餐时需要…
阅读更多...
IDEA中Git常用操作及Git存储原理

IDEA中Git常用操作及Git存储原理

Git简介与使用 Intro Git is a free and open source distributed version control system designed to handle everything from small to very large projects with speed and efficiency. Git是一款分布式版本控制系统&#xff08;VSC&#xff09;&#xff0c;是团队合作开发…
阅读更多...
【pbootcms】新环境搭建环境安装时发生错误

【pbootcms】新环境搭建环境安装时发生错误

【pbootcms】新环境搭建环境安装时发生错误 提示一下内容&#xff1a; 登录请求发生错误&#xff0c;您可按照如下方式排查: 1、试着删除根目录下runtime目录,刷新页面重试 2、检查系统会话文件存储目录是否具有写入权限; 3、检查服务器环境pathinfo及伪静态规则配置; 先按照…
阅读更多...
Pygame开发五子棋之人机对战游戏

Pygame开发五子棋之人机对战游戏

引言 Pygame是一个基于Python的开源游戏开发库&#xff0c;它包含了丰富的多媒体功能&#xff0c;尤其是针对游戏开发所需的各种组件。如果你对游戏开发感兴趣&#xff0c;但又不想从底层开始编写所有东西&#xff0c;Pygame可以成为一个理想的起点。本文将介绍Pygame的基本概…
阅读更多...
javaScript的面试重点--预解析

javaScript的面试重点--预解析

目录 一.前言 二.预解析案例 一.前言 关于预解析&#xff0c;我们通过今天学习就能够知道解析器运行JS分为哪两步&#xff1b;能够说出变量提升的步骤和运行过程&#xff1b;能够说出函数提升的步骤和运行过程。 二.预解析案例 预解析&#xff0c;简而言之&#xff0c;也就是…
阅读更多...
Gstreamer学习3.1------使用appsrc灌颜色信号数据

Gstreamer学习3.1------使用appsrc灌颜色信号数据

这个视频内容讲解的离散余弦变换&#xff0c;讲的很好&#xff0c; 离散余弦变换可视化讲解_哔哩哔哩_bilibili 其中讲到&#xff0c;把颜色变化转换为曲线的处理&#xff0c; 在前面的学习中&#xff0c;我们知道了可以向appsrc来灌数据来进行显示 Gstreamer学习3----灌数据…
阅读更多...
昇思25天学习打卡营第21天|基于MindSpore的DCGAN生成漫画头像

昇思25天学习打卡营第21天|基于MindSpore的DCGAN生成漫画头像

基于MindSpore的DCGAN生成漫画头像 GAN基础原理 生成对抗网络&#xff08;GAN&#xff09;的基础原理是通过两个互相博弈的模型&#xff0c;生成模型和判别模型&#xff0c;来实现对数据分布的学习并产生新的、与真实数据极其相似的数据实例。 生成对抗网络&#xff08;GAN&a…
阅读更多...
SwiftUI 截图（snapshot）视频画面的极简方法

SwiftUI 截图（snapshot）视频画面的极简方法

功能需求 在 万物皆可截图：SwiftUI 中任意视图（包括List和ScrollView）截图的通用实现 这篇博文中，我们实现了在 SwiftUI 中截图几乎任何视图的功能，不幸的是它对视频截图却无能为力。不过别着急，我们还有妙招。 在上面的演示图片中，我们在 SwiftUI 中可以随心所欲的截图…
阅读更多...
【python数据结构精讲】双端队列

【python数据结构精讲】双端队列

通过总结《流畅的Python》等书中的知识&#xff0c;总结Python中常用工具的方法。 deque&#xff0c;学名双端队列。 1. 常用方法 append()&#xff1a;队列尾部添加appendleft()&#xff1a;队首添加pop()&#xff1a;移除队列最后一个元素popleft()&#xff1a;移除队列第一…
阅读更多...
Reinforced Causal Explainer for GNN论文笔记

Reinforced Causal Explainer for GNN论文笔记

论文&#xff1a;TPAMI 2023 图神经网络的强化因果解释器 论文代码地址&#xff1a;代码 目录 Abstract Introduction PRELIMINARIES Causal Attribution of a Holistic Subgraph​ individual causal effect (ICE)​ *Causal Screening of an Edge Sequence Reinforc…
阅读更多...
最新文章

Copyright @ 2022~2023