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表现是不一样的，需要灵活运用。

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

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

相关文章

智已汽车x-signature 登录算法 签到

智已汽车x-signature 登录算法 签到

智已汽车x-signature 登录算法 签到 python代码成品
阅读更多...
Android 使用 Canvas 和 Paint 实现圆角图片

Android 使用 Canvas 和 Paint 实现圆角图片

学习笔记 效果展示: 全部代码: public class YuanActivity extends AppCompatActivity {private ActivityYuanBinding binding;Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);// 通过 DataBinding 获取布局文件binding …
阅读更多...
掌控时间，成就更好的自己

掌控时间，成就更好的自己

在个人成长的道路上&#xff0c;时间管理是至关重要的一环。有效的时间管理能够让我们更加高效地完成任务&#xff0c;实现自己的目标&#xff0c;不断提升自我。 时间对每个人都是公平的&#xff0c;一天只有 24 小时。然而&#xff0c;为什么有些人能够在有限的时间里做出卓…
阅读更多...
flask-socketio相关总结

flask-socketio相关总结

flask-socketio是一个为flask应用程序添加的实时双向通信功能的扩展库&#xff0c;有了这个库&#xff0c;就可以在flask应用中应用websocket协议&#xff0c;帮助flask实现低延迟、双向的客户端、服务端通信。客户端通过任何SocketIO官方库&#xff0c;都能与服务器建立长连接…
阅读更多...
YOLOv8改进，YOLOv8引入CARAFE轻量级通用上采样算子，助力模型涨点

YOLOv8改进，YOLOv8引入CARAFE轻量级通用上采样算子，助力模型涨点

摘要 CARAFE模块的设计目的是在不增加计算复杂度的情况下，提升特征图的质量，特别是在视频超分辨率任务中，提升图像质量和细节。CARAFE结合了上下文感知机制和聚合特征的能力，通过动态的上下文注意力机制来提升细节恢复的效果。 理论介绍 传统的卷积操作通常依赖于局部区域…
阅读更多...
如何把阿里云ECS里的文件下载到本地（免登录免配置）

如何把阿里云ECS里的文件下载到本地（免登录免配置）

如何把阿里云ECS里的文件下载到本地&#xff08;免登录免配置&#xff09; 作为一个阿里云ECS的用户&#xff0c;Up时长会遇到希望把ECS里的文件下载到自己的个人电脑&#xff0c;然后在自己的电脑里面查看&#xff0c;保存或者发送给别人。最近发现阿里云新上了一个功能&…
阅读更多...
【Notepad++】---设置背景为护眼色（豆沙绿）最新最详细

【Notepad++】---设置背景为护眼色（豆沙绿）最新最详细

在编程的艺术世界里&#xff0c;代码和灵感需要寻找到最佳的交融点&#xff0c;才能打造出令人为之惊叹的作品。而在这座秋知叶i博客的殿堂里&#xff0c;我们将共同追寻这种完美结合&#xff0c;为未来的世界留下属于我们的独特印记。 【Notepad】---设置背景为护眼色&#xf…
阅读更多...
【Axios】如何在Vue中使用Axios请求拦截器

【Axios】如何在Vue中使用Axios请求拦截器

✨✨ 欢迎大家来到景天科技苑✨✨ &#x1f388;&#x1f388; 养成好习惯&#xff0c;先赞后看哦~&#x1f388;&#x1f388; &#x1f3c6; 作者简介&#xff1a;景天科技苑 &#x1f3c6;《头衔》&#xff1a;大厂架构师&#xff0c;华为云开发者社区专家博主&#xff0c;…
阅读更多...
在服务器上实现本地python文件的依赖

在服务器上实现本地python文件的依赖

1、在python中&#xff0c;一个python文件就可以视为一个模块进行导入 2、使用import 导入时&#xff0c;若使用pip 下载过可以直接导入 3、假如是自己写的同项目中的文件会去sys.path 中查找 比如说 我现在 test 下有一个 python文件 运行 下面的代码 打印的数据如上图所示p…
阅读更多...
emacs 折腾日记(一)——序言

emacs 折腾日记(一)——序言

初次知道emacs这个东西是在《程序员的呐喊》这本书。书中的作者提倡学习编译原理&#xff0c;推崇emacs。现在距离我知道emacs已经过去了快8年&#xff0c;期间不断的重复学习——放弃——学习的路子。与过去学习vim类似&#xff0c;vim我也经历过放弃到学习&#xff0c;最后有…
阅读更多...
【二分查找】力扣 875. 爱吃香蕉的珂珂

【二分查找】力扣 875. 爱吃香蕉的珂珂

一、题目 二、思路 速度 k&#xff08;单位&#xff1a;根/小时&#xff09;是存在一个取值范围的。 速度越大肯定在规定的时间之内一定会吃完全部的香蕉&#xff0c;但也是可以确定出一个上界的。由于只要保证一小时之内&#xff0c;可以吃完香蕉数目最多的那一堆的香蕉&…
阅读更多...
如何找到你的决定性优势

如何找到你的决定性优势

在任何高风险竞争中&#xff0c;无论是争取客户还是发展职业生涯&#xff0c;拥有决定性优势至关重要。沃伦巴菲特称之为“持久竞争优势”&#xff0c;迈克尔波特将其称为“竞争优势”。无论名称如何&#xff0c;核心理念是相同的&#xff1a; 永远不要参与你没有绝对优势的竞争…
阅读更多...
【JavaWeb后端学习笔记】SpringBoot框架下Http请求参数接收

【JavaWeb后端学习笔记】SpringBoot框架下Http请求参数接收

Http请求参数接收 1、简单参数2、实体参数3、数组参数4、集合参数5、日期参数6、Json格式参数&#xff08;常用&#xff09;7、路径参数&#xff08;常用&#xff09;8、接收请求参数常用的几个注解 Http请求能携带各种格式的请求参数。因此也就需要不同的接收方式。 1、简单参…
阅读更多...
Qt6.8 QGraphicsView鼠标坐标点偏差

Qt6.8 QGraphicsView鼠标坐标点偏差

ui文件拖放QGraphicsView&#xff0c;src文件定义QGraphicsScene赋值给图形视图。 this->scene new QGraphicsScene();ui.graph->setScene(this->scene);对graphicview过滤事件&#xff0c;只能在其viewport之后安装&#xff0c;否则不响应。 ui.graph->viewport…
阅读更多...
macmini安装ubuntu网卡驱动BCM4360

macmini安装ubuntu网卡驱动BCM4360

安装成功效果如下 成功连接wifi 成功分配到IP 执行命令如下 1. sudo apt update 2. sudo apt install broadcom-sta-dkms 3. 重启电脑
阅读更多...
网络测速工具

网络测速工具

1. SPEEDTEST https://www.speedtest.net/ 2. 测速网 测速网 - 专业测网速, 网速测试, 宽带提速, 游戏测速, 直播测速, 5G测速, 物联网监测,Wi-Fi 7,Wi-Fi 6,FTTR,全屋Wi-Fi - SpeedTest.cn 3. 字节比特换算 bps&#xff08;bits per second&#xff09; 字节和比特的换算…
阅读更多...
docker安装victoriametrics

docker安装victoriametrics

docker安装victoriametrics 1、单机版安装2、victoriametrics增删改查2.1 、插入数据2.1.1 组装数据插入victoriametrics(java代码插入)2.1.2 Prometheus数据插入victoriametrics2.1.3 官网push到victoriametrics写法 2.2 、查询2.2.1 、Instant query&#xff08;即时查询&…
阅读更多...
用ZipOutputStream生成的zip压缩包无法用WinRAR软件进行解压

用ZipOutputStream生成的zip压缩包无法用WinRAR软件进行解压

1、问题 用WinRAR软件无法解压用ZipOutputStream生成的zip压缩包&#xff0c;而用360压缩就可以解压 2、原因 流没有正常关闭 3、解决办法 可以使用try-with-resources来自动关闭ZipOutputStream 例&#xff1a; public void compressedFile(String businessId, HttpServle…
阅读更多...
WEB_星河飞雪_Windows（全）

WEB_星河飞雪_Windows（全）

Windows基础 这一节就主要将一些Windows的一些基础命令。 文件系统及其简述管理机制 首先linux操作系统有一个核心的概念就是——在linux中一切都是文件&#xff0c;几乎很多重要的东西都挂在根目录下&#xff08;“/”&#xff09;,它采用的是fhs目录结构&#xff08; File…
阅读更多...
人工智能中的深度学习：原理与实践

人工智能中的深度学习：原理与实践

什么是深度学习&#xff1f; 深度学习&#xff08;Deep Learning&#xff09;是机器学习的一个分支&#xff0c;旨在通过模拟人脑的神经网络结构来解决复杂的任务。深度学习通过多层神经网络&#xff0c;自动从数据中学习特征&#xff0c;避免了传统机器学习中手动特征工程的繁…
阅读更多...
最新文章

Copyright @ 2022~2023