MySQL-SQL优化

文章目录

    • 1. SQL性能分析
      • 1.1 SQL执行频率
      • 1.2 慢查询日志
      • 1.3 profile详情
      • 1.4 explain
    • 2. SQL优化
      • 2.1 Insert 优化
      • 2.2 Group By 优化
      • 2.3 Order By 优化
      • 2.4 Limit 优化
      • 2.5 Count() 优化
      • 2.6 Update 优化
    • 3. 拓展
      • 3.1 请你说一下MySQL中的性能调优的方法？
      • 3.2 执行 SQL 响应比较慢，你有哪些排查思路？

1. SQL性能分析

1.1 SQL执行频率

  • MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

    SHOW GLOBAL STATUS LIKE 'Com_______';

  • 通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

1.2 慢查询日志

  • 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所SQL语句的日志。

  • MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

    # 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

  • 查看慢日志文件中记录的信息：/var/lib/mysql/localhost-slow.log。

1.3 profile详情

  • show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

    SELECT @@have_profiling ;

    在这里插入图片描述

  • 可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在session/global级别开启profiling：

    SET profiling = 1;

  • 案例：执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

    -- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

    查看每一条SQL的耗时情况：

    查看指定SQL各个阶段的耗时情况 :

1.4 explain

  • EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

    EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

  • Explain 执行计划中各个字段的含义:

    字段含义
    idselect查询的序列号，表示查询中执行select子句或者是操作表的顺序 (id相同，执行顺序从上到下；id不同，值越大，越先执行)。
    select_type表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接 或者子查询）、PRIMARY（主查询，即外层的查询）、 UNION（UNION 中的第二个或者后面的查询语句）、 SUBQUERY（SELECT/WHERE之后包含了子查询）等
    type表示连接类型，性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all 。
    possible_key显示可能应用在这张表上的索引，一个或多个。
    key实际使用的索引，如果为NULL，则没有使用索引。
    key_len表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长 度，在不损失精确性的前提下， 长度越短越好 。
    rowsMySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值， 可能并不总是准确的。
    filtered表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

2. SQL优化

2.1 Insert 优化

  1. 使用一条SQL语句插入多条数据而不是多条SQL语句：

    • 在 MySQL 中，插入多条数据有 2 种方式。第一种是使用一个 INSERT 语句插入多条数据。INSERT 语句的情形如下：

      INSERT INTO items(name,city,price,number,picture) VALUES ('耐克运动鞋','广州',500,1000,'001.jpg'),('耐克运动鞋 2','广州 2',500,1000,'002.jpg');

    • 第二种是一个 INSERT 语句只插入一条数据，执行多个 INSERT 语句来插入多条数据。INSERT 语句的情形如下：

      INSERT INTO items(name,city,price,number,picture) VALUES('耐克运动鞋','广州',500,1000,'001.jpg');
INSERT INTO items(name,city,price,number,picture) VALUES('耐克运动鞋 2','广州',500,1000,'002.jpg');

      一次性插入多条数据和多次插入数据所耗费的时间是不一样的。第一种方式减少了与数据库之间的连接等操作，其速度比第二种方式要快一些。所以插入大量数据时，建议使用第一种方法。

  2. 手动提交事务：MySQL 的事务自动提交模式默认是开启的，其对 MySQL 的性能也有一定得影响。比如你插入了 1000 条数据，MySQL 就会提交 1000 次，这大大影响了插入数据的速度。而如果我们把自动提交关掉，通过程序来控制，只要一次提交就可以了。

    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

  4. 如果一次性需要插入大批量数据(比如: 几百万的记录)，使用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' ;

2.2 Group By 优化

  • 在分组操作中，我们需要通过以下两点进行优化，以提升性能：
    1. 在分组操作时，可以通过索引来提高效率。
    2. 分组操作时，索引的使用也是满足最左前缀法则的。

2.3 Order By 优化

  • MySQL的排序，有两种方式：

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

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

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

  • 优化原则：

    1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
    2. 尽量使用覆盖索引。
    3. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
    4. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

2.4 Limit 优化

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

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

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

2.5 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(星)。

2.6 Update 优化

  • 尽量使用索引作为修改的限制条件，这样MySQL的锁是行锁，性能更高，否则行锁将升级为表锁。

  • 案例：表中有两个字段分别是id和name，其中只有id为主键索引。

    1. 行锁：

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

    2. 表锁：

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

3. 拓展

3.1 请你说一下MySQL中的性能调优的方法？

  • Mysql 性能调优方法可以从四个方面来说，分别是：表结构与索引、SQL 语句优化、Mysql 参数优化、硬件及系统配置。

  1. 硬件及系统配置：硬件方面、主要是 CPU 核数、磁盘的读写性能（减小寻道时间、旋转时间、传输时间），可以选择 SSD、网卡、内存等方面。
  2. MySQL参数优化：主要可以设置 Buffer_pool 的大小，建议占总内存的 70%左右。设置刷盘策略，平衡好数据安全性和性能的关系等。
  3. SQL语句优化：
    1. 通过慢查询分析需要优化的 SQL 进行合理优化、利用 explain、profile 等工具分析 SQL执行计划。
    2. 少用Select * ,多用Count(*)，避免返回不必要的数据列 。
    3. 尽量使用表连接代替子查询，表连接尽量使用内连接（inner join）而不是外连接（left join、right join）,内接时以小表驱动大表。
  4. 表结构与索引：
    1. 表结构优化：
      • 有些表在设计时设置了很多的字段，而有些字段的使用频率非常低。这样当这个表的数据量很大时，查询数据的速度
        就会很慢。对于这种情况，我们可以将这些使用频率较低的字段分离出来形成新表。
      • 表连接会降低数据库的查询速度，所以对于经常使用表连接查询的表，我们可以建立中间表来提高查询速度。
      • 一般情况下，设计数据库时应尽量让表符合三大范式。但是，有时为了提高查询速度，可以有意识地在表中增加冗余字段。
      • 选择合适的字段类型，比如char比varchar性能更高、tinyint比int占据空间更小。
    2. 索引方面的优化及相关知识，可以参考博主的另一篇文章：MySQL-索引（INDEX）-CSDN博客。
  5. 架构设计层面的优化：MySQL 是一个磁盘 IO 访问量非常频繁的关系型数据库在高并发和高性能的场景中。MySQL 数据库必然会承受巨大的并发压力，而此时，我们的优化方式可以分为几个部分。
    1. 搭建 Mysql 主从集群，单个 Mysql 服务容易单点故障，一旦服务器宕机，将会导致依赖 Mysql 数据库的应用全部无法响应。 主从集群或者主主集群可以保证服务的高可用性。
    2. 读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导致的性能影响。
    3. 引入分库分表机制，通过分库可以降低单个服务器节点的 IO 压力，通过分表的方式可以降低单表数据量，从而提升 sql 查询的效率。
    4. 针对热点数据，可以引入更为高效的分布式数据库，比如 Redis、MongoDB 等，他们可以很好的缓解 Mysql 的访问压力，同时还能提升数据检索性能。

3.2 执行 SQL 响应比较慢，你有哪些排查思路？

  • 如果执行 SQL 响应比较慢，我觉得可能有以下 4 个原因：

  1. 索引失效：首先，可以打开 MySQL 的慢查询日志，收集一段时间的慢查询日志内容，然后找出耗时最长的 SQL 语句，对这些 SQL 语句进行分析。 比如可以利用执行计划 explain 去查看 SQL 是否有命中索引。如果发现慢查询的 SQL 没有命中索引，可以尝试去优化这些 SQL 语句，保证 SQL 走索引执行。如果 SQL 结构没有办法优化的话，可以考虑在表上再添加对应的索引。

  2. 单表数据量数据过多，导致查询瓶颈的情况。即使 SQL 语句走了索引，表现性能也不会特别好。这个时候我们需要考虑对表进行切分。表切分规则一般分为两种，一种是水平切分，一种是垂直切分。

    • 水平切分：把一张数据行数达到千万级别的大表，按照业务主键切分为多张小表，这些小表可能达到 100 张甚至 1000 张。

    • 垂直切分：将一张单表中的多个列，按照业务逻辑把关联性比较大的列放到同一张表中去。

  3. 网络原因或者机器负载过高的情况，我们可以进行读写分离：比如 MySQL 支持一主多从的分布式部署，我们可以将主库只用来处理写数据的操作，而多个从库只用来处理读操作。在流量比较大的场景中，可以增加从库来提高数据库的负载能力，从而提升数据库的总体性能。

  4. 热点数据导致单点负载不均衡的情况：除了对数据库本身的调整以外，还可以增加缓存。将查询比较频繁的热点数据预存到缓存当中，比如 Redis、MongoDB、ES 等，以此来缓解数据的压力，从而提高数据库的响应速度。

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

Unity2D 学习笔记 0.Unity需要记住的常用知识

Unity2D 学习笔记 0.Unity需要记住的常用知识

Unity2D 学习笔记 0.Unity需要记住的常用知识 前言调整Project SettingTilemap相关&#xff08;创建地图块&#xff09;C#脚本相关程序运行函数private void Awake()void Start()void Update() Collider2D碰撞检测private void OnTriggerStay2D(Collider2D player)private void…
阅读更多...
4.0 Zookeeper Java 客户端搭建

4.0 Zookeeper Java 客户端搭建

本教程使用的 IDE 为 IntelliJ IDEA&#xff0c;创建一个 maven 工程&#xff0c;命名为 zookeeper-demo&#xff0c;并且引入如下依赖&#xff0c;可以自行在maven中央仓库选择合适的版本&#xff0c;介绍原生 API 和 Curator 两种方式。 IntelliJ IDEA 相关介绍&#xff1a;…
阅读更多...
离开亚马逊7.5年后的真心话

离开亚马逊7.5年后的真心话

每周跟踪AI热点新闻动向和震撼发展 想要探索生成式人工智能的前沿进展吗&#xff1f;订阅我们的简报&#xff0c;深入解析最新的技术突破、实际应用案例和未来的趋势。与全球数同行一同&#xff0c;从行业内部的深度分析和实用指南中受益。不要错过这个机会&#xff0c;成为AI领…
阅读更多...
深度学习(14)--x.view()详解

深度学习(14)--x.view()详解

在torch中&#xff0c;常用view()函数来改变tensor的形状 查询官方文档&#xff1a; torch.Tensor.view — PyTorch 2.2 documentationhttps://pytorch.org/docs/stable/generated/torch.Tensor.view.html#torch.Tensor.view示例 1.创建一个4x4的二维数组进行测试 x torch.…
阅读更多...
鸿蒙开发系列教程（十四）--组件导航：Tabs 导航

鸿蒙开发系列教程（十四）--组件导航：Tabs 导航

Tabs 导航 Tabs组件的页面组成包含两个部分&#xff0c;分别是TabContent和TabBar。TabContent是内容页&#xff0c;TabBar是导航页签栏 每一个TabContent对应的内容需要有一个页签&#xff0c;可以通过TabContent的tabBar属性进行配置 设置多个内容时&#xff0c;需在Tabs…
阅读更多...
尚硅谷 Vue3+TypeScript 学习笔记（下）

尚硅谷 Vue3+TypeScript 学习笔记（下）

目录 五、组件通信 5.1. 【props】 5.2. 【自定义事件】 5.3. 【mitt】 5.4.【v-model】 5.5.【$attrs】 5.6. 【$refs、$parent】 5.7. 【provide、inject】 5.8. 【pinia】 5.9. 【slot】 1. 默认插槽 2. 具名插槽 3. 作用域插槽 六、其它 API 6.1.【shallowR…
阅读更多...
发送get请求并且发送请求头（header），java实现

发送get请求并且发送请求头（header），java实现

发送get请求时&#xff0c;发送请求头&#xff08;Header&#xff09;中的内容 方便第二次调用其他url时传递参数&#xff0c;例如userCode或者租户编码 调用方式 Autowired private HttpServletRequest request;先注入HttpServletRequestpublic xxx xxx(){String url &quo…
阅读更多...
【C++修行之道】（引用、函数提高）

【C++修行之道】（引用、函数提高）

目录 一、引用 1.1引用的基本使用 1.2 引用注意事项 1.3 引用做函数参数 1.4 引用做函数返回值 1.5 引用的本质 1.6 常量引用 1.7引用和指针的区别 二、函数提高 2.1 函数默认参数 2.2函数占位参数 2.3 函数重载 2.4函数重载注意事项 一、引用 1.1引用的基本使用 …
阅读更多...
Vue代理模式和Nginx反向代理(Vue代理部署不生效)

Vue代理模式和Nginx反向代理(Vue代理部署不生效)

在使用axios时&#xff0c;经常会遇到跨域问题。为了解决跨域问题&#xff0c;可以在 vue.config.js 文件中配置代理&#xff1a; const { defineConfig } require(vue/cli-service) module.exports defineConfig({transpileDependencies: true,devServer: {port: 7070,prox…
阅读更多...
Nginx 配置 SSL证书

Nginx 配置 SSL证书

成功配置SSL证书后&#xff0c;您将能够通过HTTPS加密通道安全访问Nginx服务器。 一、准备材料 SSL证书绑定的域名已完成DNS解析&#xff0c;即您的域名与主机IP地址相互映射。您可以通过DNS验证证书工具&#xff0c;检测域名DNS解析是否生效。具体操作&#xff1a; 【1】登录…
阅读更多...
python-游戏篇-初级-超级画板

python-游戏篇-初级-超级画板

文章目录 开发环境要求运行方法PyCharmVScode 代码main.pytools.py 效果 开发环境要求 本系统的软件开发及运行环境具体如下。 操作系统&#xff1a;Windows 7、Windows 10。Python版本&#xff1a;Python 3.7.1。开发工具&#xff1a;PyCharm 2018。Python内置模块&#xff…
阅读更多...
13. Threejs案例-绘制3D文字

13. Threejs案例-绘制3D文字

13. Threejs案例-绘制3D文字 实现效果 知识点 FontLoader 一个用于加载 JSON 格式的字体的类。 返回 font&#xff0c;返回值是表示字体的 Shape 类型的数组。 其内部使用 FileLoader 来加载文件。 构造器 FontLoader( manager : LoadingManager ) 参数类型描述managerLo…
阅读更多...
微信小程序（三十四）搜索框-带历史记录

微信小程序（三十四）搜索框-带历史记录

注释很详细&#xff0c;直接上代码 上一篇 新增内容&#xff1a; 1.搜索框基本模板 2.历史记录基本模板 3.细节处理 源码&#xff1a; index.wxml <!-- 1.点击搜索按钮a.非空判断b.历史记录&#xff08;去重&#xff09;c.清空搜索框d.去除前后多余空格2.删除搜索 3.无搜索…
阅读更多...
问题：老年人心理健康维护与促进的原则为________、________、发展原则。 #媒体#知识分享

问题：老年人心理健康维护与促进的原则为________、________、发展原则。 #媒体#知识分享

问题&#xff1a;老年人心理健康维护与促进的原则为________、________、发展原则。 参考答案如图所示
阅读更多...
深度学习驱动下的自然语言处理进展及其应用前景

深度学习驱动下的自然语言处理进展及其应用前景

文章目录 每日一句正能量前言技术进步应用场景挑战与前景自然语言处理技术当前面临的挑战未来的发展趋势和前景 伦理和社会影响实践经验后记 每日一句正能量 一个人若想拥有聪明才智&#xff0c;便需要不断地学习积累。 前言 自然语言处理&#xff08;NLP&#xff09;是一项正…
阅读更多...
【C++第二阶段】空指针访问成员函数常成员函数常成员属性

【C++第二阶段】空指针访问成员函数常成员函数常成员属性

你好你好&#xff01; 以下内容仅为当前认识&#xff0c;可能有不足之处&#xff0c;欢迎讨论&#xff01; 文章目录 空指针访问成员函数常成员函数&常成员属性 空指针访问成员函数 类对象类型的空指针可以访问成员函数&#xff0c;但是不能够访问带有成员属性的成员函数。…
阅读更多...
[C#] 如何使用ScottPlot.WPF在WPF桌面程序中绘制图表

[C#] 如何使用ScottPlot.WPF在WPF桌面程序中绘制图表

什么是ScottPlot.WPF&#xff1f; ScottPlot.WPF 是一个开源的数据可视化库&#xff0c;用于在 WPF 应用程序中创建高品质的绘图和图表。它是基于 ScottPlot 库的 WPF 版本&#xff0c;提供了简单易用的 API&#xff0c;使开发人员能够通过简单的代码创建各种类型的图表&#…
阅读更多...
二十、K8S-1-权限管理RBAC详解

二十、K8S-1-权限管理RBAC详解

目录 k8s RBAC 权限管理详解 一、简介 二、用户分类 1、普通用户 2、ServiceAccount 三、k8s角色&角色绑定 1、授权介绍&#xff1a; 1.1 定义角色&#xff1a; 1.2 绑定角色&#xff1a; 1.3主体&#xff08;subject&#xff09; 2、角色&#xff08;Role和Cluster…
阅读更多...
算法学习——LeetCode力扣栈与队列篇2

算法学习——LeetCode力扣栈与队列篇2

算法学习——LeetCode力扣栈与队列篇2 150. 逆波兰表达式求值 150. 逆波兰表达式求值 - 力扣&#xff08;LeetCode&#xff09; 描述 给你一个字符串数组 tokens &#xff0c;表示一个根据 逆波兰表示法 表示的算术表达式。 请你计算该表达式。返回一个表示表达式值的整数。…
阅读更多...
数据结构 - 线索树

数据结构 - 线索树

一、 为什么要用到线索二叉树&#xff1f; 我们先来看看普通的二叉树有什么缺点。下面是一个普通二叉树&#xff08;链式存储方式&#xff09;&#xff1a; 乍一看&#xff0c;会不会有一种违和感&#xff1f;整个结构一共有 7 个结点&#xff0c;总共 14 个指针域&#xff0c…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023