MYSQL8优化

这篇具有很好参考价值的文章主要介绍了MYSQL8优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

概述

image.png

  • 设计上优化
  • 查询上优化
  • 索引上优化
  • 存储上优化

查看SQL执行频率

image.png
image.png

参数 解释
Com_select 执行select操作的次数,一次查询只累加1
Com_insert 执行INSERT操作的次数,对于批量插入的INSERT操作,值累加一次
Com_update 执行UPDATE操作的次数
Com_delete 执行DELETE操作的次数
Innodb_rows_read select查询返回的次数
Innodb_rows_inserted 执行INSERT操作插入的行数
Innodb_rows_updated 执行UPDATE操作更新的行数
Innodb_rows_deleted 执行DELETE操作删除的行数
Connections 试图连接MYSQL服务器的次数
Uptime 服务器工作时间
Slow_queries 慢查询的次数

查看当前会话统计结果

image.png

-- 查看当前会话SQL执行类型的统计结果
-- 7个_
show session status like 'Com_____'

image.png


查看数据库自上次启动至今统计结果

-- 查看全局(自上次数据库启动至今)执行类型的统计信息
-- 7个_
show global status like 'Com_____'

image.png


查看针对INnodb引擎的统计结果

-- 查看针对INnodb引擎的统计结果
show status like 'Innodb_rows_%'

show processlist定位低效率执行SQL

show processlist可以动态的查询正在执行中的sql语句
image.png


慢SQL查询

通过慢查询日志定位那些执行效率较低的SQL语句
https://blog.csdn.net/omaidb/article/details/129360788
image.png


显示正在运行的连接和查询

该命令查看当前MySQL在进行的线程,包括线程的状态,是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操作进行优化。
该命令会向服务器发送一个额外的查询,如果您的数据库服务器非常繁忙或者有很多连接,那么执行这个命令可能需要很长时间。

-- 显示当前正在运行的所有连接和它们所执行的查询
show processlist;

image.png

image.png


explain分析执行计划

https://blog.csdn.net/omaidb/article/details/120040657


show profiles分析sql执行耗时

image.png


开启profile

-- 查看当前mysql只是否支持profile
select @@have_profiling;

image.png

-- 查看profile是否开启
show variables like 'profiling';

image.png

-- 临时开启profile
set profiling=1;

image.png


profiles查看sql语句执行的耗时细节

-- 查看sql语句执行的耗时细节
show profiles;

image.png
image.png


profile查看sql执行线程的状态和耗时

通过show profile for query 查询id;语句,可以查看到sql执行过程中每个线程的状态和消耗的时间;

-- 查看第8条sql语句的耗时细节
show profile for query 20;

image.png


show profile分析SQL资源明细类型消耗

资源明细类型 解释
all 所有资源
cpu cpu资源
memory 内存
block io io
centext switch 连接转换
page faults 分页

image.png

-- 查看指定查询语句的资源消耗
SHOW PROFILE CPU, MEMORY, BLOCK IO FOR QUERY 133;
-- SHOW PROFILE CPU, MEMORY, BLOCK IO FOR QUERY 133;

profile结果字段解读

image.png

字段 含义
Status SQL语句执行的状态
Duration SQL执行过程中每一个步骤的耗时
CPU_user 当前用户占用的CPU
CPU_system 系统占用的CPU

trace分析优化器执行计划

image.png

-- 查看trace分析优化器是否开启
-- 查看trace最大能够使用的内存大小
show variables like 'optimizer_trace%';

image.png

-- 查看是否配置json格式
show variables like 'end_markers_in%';

image.png

optimizer_trace_max_mem_size参数的单位是字节(bytes)

-- 打开trace分析优化器,设置格式为json
set optimizer_trace='enabled=on',end_markers_in_json=on;

-- 设置trace最大能够使用的内存大小,单位是字节(bytes)
set optimizer_trace_max_mem_size=1000000;

image.png


检查information_schema.optimizer_trace

检查information_schema.optimizer_trace就可以知道mysql是如何执行的。

-- 检查infromation_schema.optimizer_trace
select * from information_schema.optimizer_trace\G;

image.png


疑问:为什么sql会自动选择b计划

mysql8内置了mysql优化器,会自动优化执行策略。
image.png


MYSQL数据索引优化

https://blog.csdn.net/omaidb/article/details/130292101


大批量插入数据优化

image.png


0.开启允许从本地导入数据

-- 开启local_infile
set global local_infile=1;

-- 查看local_infile是否开启
show global variables LIKE 'local_infile';

image.png

-- 加载数据
load data local infile '/data/sql_data/sql2.log' into table tb_user fields terminated by ',' lines terminated by '\n';

1.主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。如果InnoDB表没有主键,那么系统会自动默认创建一个内部列作为主键,所以如果可以给表创建一个主键,可将可以利用这点,来提高导入数据的效率
image.png
image.png
image.png


2.关闭唯一性校验

-- 查看唯一性校验
show variables LIKE 'unique_checks';

image.png

-- 关闭唯一性校验
set unique_checks=0;

image.png

-- 查看是否允许客户端可以使用LOAD DATA LOCAL INFILE语句将本地文件加载到MySQL服务器
show global variables LIKE 'local_infile';

image.png


3.优化插入语句

如果需要同时对一张表插入很多行数据时,应该尽量使用多个值表的insert语句。
这种方式将大大的缩减客户端与数据库之间的连接,关闭等消耗。使得效率比分开执行的单个insert语句快。
image.png


4.在事务中进行数据插入

插入多条数据后一次性提交。
image.png
image.png


优化order by语句

不要用select *
*改为索引字段,就会使用索引。
如果*这里包含非索引字段,就要额外排序,效率

orde by后面的多个排序字段的排序顺序尽量相同
orde by后面的多个排序字段顺序尽量和组合索引字段顺序一致


数据准备

image.png


两种排序方式

image.png


数据准备

image.png


Filesort的优化

image.png
MySQL 8 不再支持 max_length_for_sort_data 系统变量,该变量在 MySQL 5.7 中已被废弃。这是因为在 MySQL 8 中,排序和分组操作默认使用字典排序(collation)而不是二进制排序(binary sorting)。

-- 查看可用于排序的最大数据长度
show variables LIKE 'max_length_for_sort_data';

image.png

-- 查看排序操作所使用的缓冲区大小
show variables LIKE 'sort_buffer_size';

image.png


优化子查询

通常情况下,SQL多表连接查询(Join)比子查询效率高。这是因为在使用子查询时,需要执行多个查询语句,而在使用关联查询时只需要执行单个查询语句。
连接查询(Join)不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
image.png
image.png
image.png
性能由高到低的连接类型为:
system > const > eq_ref > ref > range > index > ALL


优化limit查询

image.png

优化前的结果:
image.png

优化后的结果:
image.png
image.png
image.png
image.png文章来源地址https://www.toymoban.com/news/detail-420458.html


SQL优化总结

  • 合理设计数据库架构:要考虑哪些列需要建立索引,合理的数据库设计可以减少冗余,提高性能。
  • 优化查询语句:使用正确的索引、避免全表扫描、使用 UNION ALL 替代 UNION、减少不必要的连接等。
  • 使用分页优化:当需要查询大量数据并分页显示时,可以通过 LIMIT 和 OFFSET 来进行优化。
  • 避免使用 SELECT *:只选择需要的列而不是全部列,可以提高性能。
  • 使用合适的数据类型:使用小的数据类型来存储数据可以减少磁盘空间、提高查询速度。
  • 使用分区表:可以将大型表分成小的片段,以减少查询时间。
  • 使用缓存:使用缓存可以减少查询数据库的次数,提高性能。
  • 定期优化表:定期进行表优化,可以减少磁盘碎片,提高性能。
  • 避免使用子查询:尽量避免使用子查询,因为它们会引起额外的开销。
  • 减少 JOIN 操作次数:JOIN 操作需要消耗大量 CPU 和内存资源,所以应该尽可能减少 JOIN 操作的次数。
  • 使用索引:使用索引可以加快查询速度,但也要注意不要滥用索引,否则会导致性能下降。
  • 使用 EXPLAIN 进行查询计划分析:使用 EXPLAIN 可以查看 MySQL 如何执行查询,从而找到查询效率低下的原因。

到了这里,关于MYSQL8优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL 数据存储和优化------MySQL索引原理和优化 ---- (架构---索引---事务---锁---集群---性能---分库分表---实战---运维)持续更新

    Mysql架构体系全系列文章主目录(进不去说明还没写完) https://blog.csdn.net/grd_java/article/details/123033016 本文只是整个系列笔记的第二章:MySQL索引原理和优化,只解释索引相关概念。 索引可以提高查询效率,影响where查询和order by排序,它可以从多方面进行分类,但是实际创建时

    2024年02月02日
    浏览(51)
  • mysql高级三:sql性能优化+索引优化+慢查询日志

    内容介绍 单表索引失效案例 0 、思考题: 如果把 100 万数据插入 MYSQL ,如何提高插入效率 (1)关闭自动提交,只手动提交一次 (2)删除除主键索引外其他索引 (3)拼写mysql可以执行的长sql,批量插入数据 (4)使用java多线程 (5)使用框架,设置属性,实现批量插入 1、

    2024年02月12日
    浏览(68)
  • MySQL索引优化:提升查询速度的实战解析

    当涉及到大型数据库和复杂查询时,索引在MySQL中是一个重要的性能优化工具。通过使用索引,可以加速查询速度,减少查询的执行时间。下面是一个详细的MySQL添加索引的教程,使用Markdown格式进行说明。 步骤1:选择合适的列 首先,需要选择哪些列需要添加索引。通常情况

    2024年02月12日
    浏览(63)
  • 【MySQL索引与优化篇】InnoDB数据存储结构

    索引结构给我们提供了高效的索引方式,不过索引信息以及数据记录都是保存在文件上的,确切说是存储在页结构中。另一方面,索引是在存储引擎中实现的,MySQL服务器上的 存储引擎 负责对表中数据的读取和写入工作。不同存储引擎中 存放的格式 一般是不同的。 由于 I

    2024年02月07日
    浏览(52)
  • MySQL强制使用索引的两种方式及优化索引,使用MySQL存储过程创建测试数据。

    一、MySQL强制使用索引的两种方式 1、使用 FORCE INDEX 语句: 使用 FORCE INDEX(索引名称)走索引: 2、使用 USE INDEX 语句: 使用 USE INDEX(索引名称)走索引: FORCE INDEX 或 USE INDEX 的区别? FORCE INDEX :这个语句指示MySQL强制查询使用特定的索引。它会忽略优化器的选择,无论索引

    2024年02月03日
    浏览(47)
  • PostgreSQL性能调优:优化查询和索引设计

    随着数据量的增长和业务需求的变化,数据库性能成为了许多企业关注的焦点之一。在众多的数据库管理系统中,PostgreSQL因其稳定性和可靠性而备受青睐。然而,即使是最强大的系统也需要合适的调优,以确保其能够高效地处理大规模数据和复杂查询。 本文将介绍如何在P

    2024年02月07日
    浏览(54)
  • MySQL数据库索引优化指南:提升查询效率的利器

    本文将详细探讨MySQL数据库索引的概念、作用以及不同类型的索引,包括主键索引、唯一索引和普通索引。通过实际案例分析,我们将深入理解索引的工作原理,并提供实用的优化建议,帮助读者提升数据库性能。

    2024年02月09日
    浏览(88)
  • 深入解析MySQL视图、索引、数据导入导出:优化查询和提高效率

    目录 1. 视图(View): 什么是视图? 为什么要使用视图? 视图的优缺点 1) 定制用户数据,聚焦特定的数据 2) 简化数据操作 3) 提高数据的安全性 4) 共享所需数据 5) 更改数据格式 6) 重用 SQL 语句 示例操作 没使用前 使用后 2. 索引(Index): 什么是索引? 为什么要使用索引?

    2024年02月13日
    浏览(61)
  • MySQL进阶篇:索引(概述,结构,分类,语法,SQL性能分析,索引使用,设计原则)

    索引(index)是帮助MysQL 高效获取数据的数据结构 ( 有序 )。 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。 优缺点: MySQL的索引是在存储

    2024年01月20日
    浏览(49)
  • MySQL高级篇复盘笔记(一)【存储引擎、索引、SQL优化、视图、触发器、MySQL管理】

    ❤ 作者主页:欢迎来到我的技术博客😎 ❀ 个人介绍:大家好,本人热衷于 Java后端开发 ,欢迎来交流学习哦!( ̄▽ ̄)~* 🍊 如果文章对您有帮助,记得 关注 、 点赞 、 收藏 、 评论 ⭐️⭐️⭐️ 📣 您的支持将是我创作的动力,让我们一起加油进步吧!!!🎉🎉 连接层

    2024年02月06日
    浏览(73)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包