MySQL 性能优化详解

MySQL 性能优化详解

  • 硬件升级
  • 系统配置优化
    • 调整buffer_pool
    • 数据预热
    • 降低日志的磁盘落盘
  • 表结构设计优化
  • SQL语句及索引优化
  • SQL优化实战案例

MySQL性能优化我们可以从以下四个维度考虑：硬件升级、系统配置、表结构设计、SQL语句和索引。

从成本上来说：硬件升级>系统配置>表结构设计>SQL语句及索引，然而效果却是由低到高。所以我们在优化的时候还是尽量从SQL语句和索引开始入手。
在这里插入图片描述

硬件升级

硬件升级这里不在过多赘述，升级更好配置的机器、机械硬盘更换为SSD等等。

系统配置优化

调整buffer_pool

通过调整buffer_pool使数据尽量从内存中读取，最大限度的降低磁盘操作，这样可以提升性能。查看buffer_pool数据的方法：

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_page_%'

在这里插入图片描述
可以看出总页数8192，空闲页数1024。

//查看buffer_pool大小 
SELECT @@innodb_buffer_pool_size/1024/1024

innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。我们修改mysql配置文件my.cnf,增加如下配置：

innodb_buffer_pool_size = 750M

然后重启MySQL。

数据预热

默认情况下，某条数据被读取过一次才会被缓存在innodb_buffer_pool里。所以数据库刚刚启动，可以进行一次数据预热，将磁盘上的数据缓存到内存中去。 预热脚本：

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb, ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache FROM ( SELECT engine,table_schema db,table_name tb, index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist FROM ( SELECT B.engine,A.table_schema,A.table_name, A.index_name,A.column_name,A.seq_in_index FROM information_schema.statistics A INNER JOIN ( SELECT engine,table_schema,table_name FROM information_schema.tables WHERE engine='InnoDB' ) B USING (table_schema,table_name) WHERE B.table_schema NOT IN ('information_schema','mysql') ORDER BY table_schema,table_name,index_name,seq_in_index ) A GROUP BY table_schema,table_name,index_name ) AA ORDER BY db,tb;

将脚本保存为：loadtomem.sql，执行命令：

mysql -uroot -p -AN < /root/loadtomem.sql > /root/loadtomem.sql

在需要进行数据预热时就执行下面的命令：

mysql -uroot < /root/loadtomem.sql > /dev/null 2>&1

降低日志的磁盘落盘

  • 增大redolog，减少落盘次数，innodb_log_file_size设置为0.25 * innodb_buffer_pool_size
  • 通用查询日志、慢查询日志可以不开，bin-log要开，慢日志查询可以遇到性能问题再开
  • 写redolog策略 调整innodb_flush_log_at_trx_commit参数为0或2。当然涉及安全性非常高的系统（金融等）还是保持默认的就行。

在配置文件里加上 innodb_flush_log_at_trx_commit =2 即可。

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'

表结构设计优化

  • 设计中间表
    • 设计中间表，一般针对于统计分析功能
  • 设计冗余字段
    • 为减少关联查询，创建合理的冗余字段
  • 拆表
    • 对于字段太多的大表，考虑拆表；对于表中经常不被使用的字段或存储数据比较多的字段，考虑拆表
  • 主键优化
    • 主键类型最好是int类型，建议自增主键（分布式系统下用雪花算法）
  • 字段的设计
    • 字段的宽度设得尽可能的小。
    • 尽量把字段设置为NOT NULL
    • 对于某些文本字段，如省份、性别等，我们可以把他们定义为enum类型。在mysql里enum类型被当作数值类型数据来处理，而数值型数据处理起来比文本类型快得多。

SQL语句及索引优化

  1. 学会用explain分析
  2. SQL语句中IN包含的值不应太多
    • MySQL对IN做了一些优化，将IN中的常量去不存在一个数组里，而且会进行排序。如果数值较多，这些步骤消耗也是比较大的。
  3. SELECT 语句务必指明字段名称
    • SELECT * 增加了很多不必要的消耗（CPU、IO、内存、网络带宽）
  4. 当只需要一条数据时，使用limit
  5. 排序字段加索引
  6. 如果查询条件中其他字段没有索引，少用or
    • or两边的字段中，如果有一个不是索引字段，则会造成该查询都不会走索引的情况。
    • select * from tbiguser where nickname='zy1' or loginname='zhaoyun3';
    • 如nickname是索引字段，loginname不是索引字段，则整体不会走索引。可以用union all代替
  7. 尽量用union all代替union
    • union和union all的区别是，union需要将结果集合并再进行唯一性过滤操作，这就会涉及到排序，增加了大量的CPU运算。当然，使用union all的前提条件是两个结果集没有重复数据。
  8. 区分in和exists、not in和not exists
    • exists：以外表为驱动表，先被访问。适合外表小而内表大的情况
    • in：先执行子查询。适合外表大而内表小的情况

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？

原语句：

select colname … from A表 where a.id not in (select b.id from B表)

优化后的语句：

select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

  1. 不建议使用%前缀模糊查询，不会走索引

  2. 避免在where子句中对字段进行表达式或函数操作

  3. 避免隐式类型转换 如where age=‘18’,如果确定是int类型，应写为where age = 18;

  4. 对于联合索引，要遵守最左前缀法则

    • 举例来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

  5. 必要时可以使用force index来强制查询使用某个索引

  6. 注意范围查询语句 对于联合索引来说，如果存在范围查询，比如between，>，<等条件时，会造成后面的索引字段失效

  7. 使用JOIN优化 LEFT JOIN里左边的表为驱动表，RIGHT JOIN里右边的表为驱动表，而INNER JOIN MySQL会自动找出数据少的表为驱动表

注意：

  • MySQL没有full join，可以用以下方式解决
select * from A left join B on B.name = A.name where B.name is null union all select * from B;
  • 尽量用inner join，避免left join
  • 合理利用索引字段作为on的限制字段
  • 利用小表去驱动大表

下图是join查询的原理图，从图中可以看出如果能够减少驱动表的话，就能减少嵌套循环中的次数，以减少IO总量及CPU运算的次数。
在这里插入图片描述

SQL优化实战案例

介绍：tbiguser表有10000000条记录，表结构如下：

create table tbiguser( id int primary key auto_increment, nickname varchar(255), loginname varchar(255), age int , sex char(1), status int, address varchar(255) );

创建存储过程，并执行，插入一千万条数据

CREATE PROCEDURE test_insert() BEGIN DECLARE i INT DEFAULT 1; WHILE i<=10000000 DO insert into tbiguser VALUES(null,concat('zy',i),concat('zhaoyun',i),23,'1',1,'beijing'); SET i=i+1; END WHILE ; commit; END;
call test_insert

还有tuser1表和tuser2表，两个表结构一致。

create table tuser1( id int primary key auto_increment, name varchar(255), address varchar(255) ); create table tuser2( id int primary key auto_increment, name varchar(255), address varchar(255) );

在这里插入图片描述
需求：tbiguser表按照地区分组统计求和，并且要求是在tuser1表和tuser2表中出现过的地区。
按照需求写出SQL：

SELECT COUNT(*) num,address FROM tbiguser WHERE address IN (SELECT address FROM tuser1) GROUP BY address UNION SELECT COUNT(*) num,address FROM tbiguser WHERE address IN (SELECT address FROM tuser2) GROUP BY address

执行时间：4.65s

第一次优化：
加索引。我们可以给address字段加索引。

ALTER TABLE tuser1 ADD INDEX idx_address(address); ALTER TABLE tuser2 ADD INDEX idx_address(address); ALTER TABLE tbiguser ADD INDEX idx_address(address);

执行时间0.9s
我们用explain分析sql
在这里插入图片描述
发现有两次都扫描了964147行，就是tbiguser这个大表扫描了两次。且有临时表使用。于是我们进行优化

第二次优化

SELECT COUNT(*) num,address FROM tbiguser WHERE address IN (SELECT address FROM tuser1) OR address IN (SELECT address FROM tuser2) GROUP BY address

执行时间0.65s
在这里插入图片描述
没有临时表了，大表也只扫描了一次。另外我尝试这样查询：

SELECT COUNT(*) num,address FROM tbiguser WHERE address IN (SELECT address FROM tuser1 UNION ALL SELECT address FROM tuser2) GROUP BY address

执行时间12s。

SELECT COUNT(x.id),x.address FROM (SELECT DISTINCT b.* FROM tuser1 a,tbiguser b WHERE a.address=b.address UNION ALL SELECT DISTINCT b.* FROM tuser2 a,tbiguser b WHERE a.address=b.address) X GROUP BY x.address;

执行时间5.8s

根据实践发现，sql查询优化没有定式，不同的数据量下相同的sql表现是不一样的，需要灵活运用。

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

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

Python_Flask02

Python_Flask02

所有人都不许学Java了&#xff0c;都来学Python&#xff01; 如果不来学的话请网爆我的老师 连接前的准备 安装pymysql 和 flask_sqlalchemy&#xff0c;安装第三下面两个所需要的包才能连接上数据库 pip install pymysql pip install flask_sqlalchemy pymysql是一个Pyth…
阅读更多...
YOLOv6

YOLOv6

YOLOv6 是继 YOLOv5 之后&#xff0c;由 Meituan 的团队开发的一个目标检测模型。YOLOv6 的目标是进一步提高模型的性能&#xff0c;特别是在处理速度、准确度、以及模型的精简化方面&#xff0c;并且它在一些特定任务上进行了优化。YOLOv6 引入了多个创新&#xff0c;并优化了…
阅读更多...
VBA信息获取与处理第四个专题第二节：将工作表数据写入VBA数组

VBA信息获取与处理第四个专题第二节：将工作表数据写入VBA数组

《VBA信息获取与处理》教程(版权10178984)是我推出第六套教程&#xff0c;目前已经是第一版修订了。这套教程定位于最高级&#xff0c;是学完初级&#xff0c;中级后的教程。这部教程给大家讲解的内容有&#xff1a;跨应用程序信息获得、随机信息的利用、电子邮件的发送、VBA互…
阅读更多...
Linux安装Cuda和多个Cuda版本切换

Linux安装Cuda和多个Cuda版本切换

解决的问题&#xff1a;服务器上跑深度学习代码&#xff0c;通常都需要用到Cuda。有时候跑的不同程序要求的配置Cuda版本可能也不同&#xff0c;会需要不同Cuda版本的替换。 Linux安装Cuda CUDA官网&#xff0c;下载安装&#xff0c;网址为&#xff1a;https://developer.nvi…
阅读更多...
云渲染特效广告一秒费用预估是多少？

云渲染特效广告一秒费用预估是多少？

在计算云渲染特效广告每秒钟的费用时&#xff0c;我们需要综合考虑多个关键因素&#xff0c;包括特效的复杂性、所需的渲染计算能力以及对渲染质量的具体要求。通常情况下&#xff0c;影视特效级别的广告因其场景极其复杂&#xff0c;每帧渲染所需时间较长&#xff0c;从而导致…
阅读更多...
【计算机组成原理统考历年真题解析】2010年真题

【计算机组成原理统考历年真题解析】2010年真题

1. 下列选项中&#xff0c;能缩短程序执行时间的措施是: I.提高CPU时钟频率 II.优化数据通路结构 III,对程序进行编译优化 A.仅I和II B.仅I和III C.仅II和III D.I、II和III D 2. 假定有4个整数用8位补码分别表示为 r1FEH&#xff0c;r2F2H&#xff0c;r390H&#xff0c;r4F…
阅读更多...
一、理论基础-PSI

一、理论基础-PSI

之前参加了隐语第2期&#xff0c;对隐语SecretFlow框架有了大致的了解&#xff0c;这次参加隐语第4期&#xff0c;学习下PSI和PIR。 一、PSI定义 首先介绍PSI的定义&#xff0c;PSI&#xff08;隐私集合求交&#xff0c;Private Set Intersection即PSI)是安全多方计算&#x…
阅读更多...
php 系统函数 记录

php 系统函数 记录

PHP intval() 函数 PHP函数介绍—array_key_exists(): 检查数组中是否存在特定键名 如何使用PHP中的parse_url函数解析URL PHP is_array()函数详解&#xff0c;PHP判断是否为数组 PHP函数介绍&#xff1a;in_array()函数 strpos定义和用法 strpos() 函数查找字符串在另一字符串…
阅读更多...
数据挖掘之回归算法

数据挖掘之回归算法

引言 回归分析是数据挖掘中最常见的技术之一&#xff0c;它用于建立自变量&#xff08;或称特征&#xff09;与因变量&#xff08;或目标变量&#xff09;之间的数学关系。回归模型不仅在统计学中占据重要地位&#xff0c;也广泛应用于预测、优化、风险管理等各个领域。在数据…
阅读更多...
鸿蒙DevEco Profiler无法识别设备

鸿蒙DevEco Profiler无法识别设备

一、问题 DevEco Studio运行项目处可以识别到设备信息&#xff0c;但是Profiler工具无法识别 二、背景知识 注意 DevEco Profiler工具不支持模拟器进行调优。macOS 12及以上系统版本支持使用DevEco Profiler工具。 知识来源&#xff1a;文档中心 三、解决方案 重启DevEco …
阅读更多...
网上商城系统设计与实现

网上商城系统设计与实现

文末获取源码和万字论文&#xff0c;制作不易&#xff0c;感谢点赞支持。 题目&#xff1a;网上商城系统设计与实现 摘 要 现代经济快节奏发展以及不断完善升级的信息化技术&#xff0c;让传统数据信息的管理升级为软件存储&#xff0c;归纳&#xff0c;集中处理数据信息的管理…
阅读更多...
微服务的熔断、限流和降级

微服务的熔断、限流和降级

1 服务问题 系统的运行是避免不了问题的产生&#xff0c;而服务问题可以从如下两个角度来分析&#xff1a; 服务系统问题&#xff1a;如机器宕机、网络、机房故障&#xff0c;虽然这个是有问题但概率很小&#xff0c;在面试的时候简单提即可。服务程序问题&#xff1a;如业务响…
阅读更多...
C++ 哈希表、unordered_map 和 unordered_set 的实现

C++ 哈希表、unordered_map 和 unordered_set 的实现

哈希表&#xff08;Hash Table&#xff09;是一种常用的数据结构&#xff0c;它能够以接近常数时间复杂度的效率完成插入、删除和查找操作。在现代编程中&#xff0c;标准模板库&#xff08;STL&#xff09;提供了 unordered_map 和 unordered_set&#xff0c;它们都是基于哈希…
阅读更多...
多行为推荐-KBS 24|基于HyperGRU对比网络的短视频推荐多行为序列建模

多行为推荐-KBS 24|基于HyperGRU对比网络的短视频推荐多行为序列建模

论文&#xff1a;https://www.sciencedirect.com/science/article/abs/pii/S0950705124004751?via%3Dihub 关键词&#xff1a;短视频推荐&#xff0c;多行为推荐&#xff0c;对比学习&#xff0c;RNN 1 动机 这是我第一次看短视频推荐里涉及到多行为的论文&#xff0c;动机还…
阅读更多...
【AI系统】ESPNet 系列

【AI系统】ESPNet 系列

ESPNet 系列 本文将会介绍 ESPNet 系列&#xff0c;该网络主要应用在高分辨率图像下的语义分割&#xff0c;在计算内存占用、功耗方面都非常高效&#xff0c;重点介绍一种高效的空间金字塔卷积模块&#xff08;ESP Module&#xff09;&#xff1b;而在 ESPNet V2 上则是会更进…
阅读更多...
基于vpk180边缘场景下分布式神经网络训练模型部署

基于vpk180边缘场景下分布式神经网络训练模型部署

本项目目标在于针对边缘场景实现P2P的分布式训练过程&#xff0c;设计方案为将神经网络训练过程对应的裸机程序部署在了PS端的ARM Cortex-A72核上&#xff0c;传输方案采用开发板板载的GTM收发器硬件资源通过外部QSFP-DD光模块光传输至对端&#xff0c;最终完成了设计目标。 整…
阅读更多...
ros项目dual_arm_pick-place（urdf文件可视化查看）

ros项目dual_arm_pick-place（urdf文件可视化查看）

前言 一直想写一些项目的讲解&#xff0c;今天&#xff08;2024.12.05&#xff09;可以说正式开始了。 dual_arm_pick-place项目&#xff0c;是关于两个机械臂协同传递物品。 正文 这次的话&#xff0c;给大家讲一下里面的urdf文件。 这篇文章主要来看一下项目中的urdf文件…
阅读更多...
spring boot 配置文件加载的加载和使用

spring boot 配置文件加载的加载和使用

文章目录 添加配置信息使用ConfigurationProperties注入属性使用Value注入属性两种方式对比分析自定义配置添加自定义配置加载并使用配置信息 添加配置信息 在实际开发中&#xff0c;应用程序的配置文件可能会变得非常复杂&#xff0c;属性的数量也会很多。为了更好地组织和管…
阅读更多...
17. Threejs案例-Three.js创建多个立方体

17. Threejs案例-Three.js创建多个立方体

17. Threejs案例-Three.js创建多个立方体 实现效果 知识点 WebGLRenderer (WebGL渲染器) WebGLRenderer 是 Three.js 中用于渲染 WebGL 场景的核心类。它负责将场景中的对象渲染到画布上。 构造器 new THREE.WebGLRenderer(parameters) 参数类型描述parametersObject可选…
阅读更多...
QNX的内存布局和启动入口

QNX的内存布局和启动入口

参考资料： QNX官网文档 内存布局 添加图片注释，不超过 140 字（可选） 查看系统内存布局 # pidin syspage=asinfo Header size=0x00000108, Total Size=0x0000d1b0, #Cpu=8, Type=257 Section:asinfo offset:0x0000bdf0 size:0x00000d00 elsize:0x000000200000
阅读更多...
最新文章

Copyright @ 2022~2023