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.rhkb.cn/news/487108.html

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

相关文章

PCB设计规范

PCB设计规范

过孔设计 过孔盖油工艺&#xff08;也成为连塞带印&#xff09;&#xff1a;常规工艺、免费工艺&#xff0c;无特殊情况也建议使用此工艺。过孔大小建议直径在0.3mm-0.5mm之间。最省钱&#xff0c;效果最好。 非金属化槽孔 PCB制造商在加工非金属化槽孔时通常采用锣刀加工。最…
阅读更多...
MVC基础——市场管理系统（二）

MVC基础——市场管理系统（二）

文章目录 项目地址三、Produtcts的CRUD3.1 Products列表的展示页面（Read）3.1.1 给Product的Model里添加Category的属性3.1.2 View视图里展示Product List3.2 增加Product数据（Add）3.2.1 创建ViewModel用来组合多个Model3.2.2 在_ViewImposts里引入ViewModels3.2.3 添加Add的…
阅读更多...
vivado中，generate output product 和Create HDL wrapper的作用

vivado中，generate output product 和Create HDL wrapper的作用

generate output product 以zynq的ip核举例&#xff0c;没有generate output product之前&#xff0c;在ip source 什么也看不到。 但是同样的一个ip核&#xff0c;generate output product之后&#xff0c;会生成综合&#xff0c;布线和仿真文件&#xff0c;约束文件等等。 …
阅读更多...
uni-app 组成和跨端原理 【跨端开发系列】

uni-app 组成和跨端原理 【跨端开发系列】

&#x1f517; uniapp 跨端开发系列文章&#xff1a;&#x1f380;&#x1f380;&#x1f380; uni-app 组成和跨端原理 【跨端开发系列】 uni-app 各端差异注意事项 【跨端开发系列】uni-app 离线本地存储方案 【跨端开发系列】uni-app UI库、框架、组件选型指南 【跨端开…
阅读更多...
双目相机的标定，视差图，深度图，点云生成思路与实现。

双目相机的标定，视差图，深度图，点云生成思路与实现。

该文档记录从双目相机标定到点云生成的所有过程&#xff0c;同时会附上代码。 代码直接能跑。https://github.com/stu-yzZ/stereoCamera 目录 大致思路如下&#xff1a; 一、相机标定 1、相机参数介绍 2、单目相机标定 3、双目相机标定 二、图片畸变矫正 三、极线矫正…
阅读更多...
Selenium：强大的 Web 自动化测试工具

Selenium：强大的 Web 自动化测试工具

Selenium&#xff1a;强大的 Web 自动化测试工具 在当今的软件开发和测试领域&#xff0c;自动化工具的重要性日益凸显。Selenium 就是一款备受欢迎的 Web 自动化测试工具&#xff0c;它为开发者和测试人员提供了强大的功能和便利。本文将详细介绍 Selenium 是什么&#xff0c…
阅读更多...
基于 Spring Boot + Vue 的宠物领养系统设计与实现

基于 Spring Boot + Vue 的宠物领养系统设计与实现

引言 近年来&#xff0c;随着人们生活水平的提高&#xff0c;宠物逐渐成为许多家庭的重要成员。然而&#xff0c;宠物的流浪和弃养问题日益严重&#xff0c;这促使社会对宠物领养的需求不断增长。为解决宠物领养中信息不对称、领养流程复杂等问题&#xff0c;设计并实现一个基…
阅读更多...
佑驾创新冲刺上市：交付进度延后，研发投入缩减，刘国清为实控人

佑驾创新冲刺上市：交付进度延后，研发投入缩减，刘国清为实控人

近日&#xff0c;深圳佑驾创新科技股份有限公司&#xff08;MINIEYE&#xff0c;下称“佑驾创新”&#xff09;通过港交所聆讯并披露了聆讯后资料集&#xff08;即招股书&#xff09;。据贝多财经了解&#xff0c;佑驾创新获得了IPO备案通知书&#xff0c;拟在港交所上市。 对…
阅读更多...
JS中的原型链与继承

JS中的原型链与继承

原型链的类比 JS中原型链&#xff0c;本质上就是对象之间的关系&#xff0c;通过protoype和[[Prototype]]属性建立起来的连接。这种链条是动态的&#xff0c;可以随时变更。 这个就跟C/C中通过指针建立的关系很相似&#xff0c;比如&#xff0c;通过指针建立一个链表&#xf…
阅读更多...
数据结构——图(遍历,最小生成树,最短路径)

数据结构——图(遍历,最小生成树,最短路径)

目录 一.图的基本概念 二.图的存储结构 1.邻接矩阵 2.邻接表 三.图的遍历 1.图的广度优先遍历 2.图的深度优先遍历 四.最小生成树 1.Kruskal算法 2.Prim算法 五.最短路径 1.单源最短路径--Dijkstra算法 2.单源最短路径--Bellman-Ford算法 3.多源最短路径--Floyd-…
阅读更多...
华为云云日志服务 HarmonyOS NEXT采集最佳实践

华为云云日志服务 HarmonyOS NEXT采集最佳实践

鸿蒙背景介绍 华为鸿蒙HarmonyOS系统是面向万物互联的全场景分布式操作系统&#xff0c;支持手机、平板、智能穿戴、智慧屏等多种终端设备运行&#xff0c;提供应用开发、设备开发的一站式服务的平台。2024 年 1 月 18 日正式推出 HarmonyOS NEXT 鸿蒙星河开发者预览&#xff…
阅读更多...
在Ubuntu上使用IntelliJ IDEA：开启你的Java开发之旅！

在Ubuntu上使用IntelliJ IDEA：开启你的Java开发之旅！

你好&#xff0c;年轻的学徒&#xff01;&#x1f9d1;‍&#x1f4bb; 是时候踏上进入Java开发世界的史诗之旅了&#xff0c;我们的得力助手将是强大的IntelliJ IDEA。准备好了吗&#xff1f;出发吧&#xff01; 在我们开始之前&#xff0c;我们需要下载这个工具。但是&#…
阅读更多...
图片预处理技术介绍4——降噪

图片预处理技术介绍4——降噪

图片预处理 大家好&#xff0c;我是阿赵。   这一篇将两种基础的降噪算法。   之前介绍过均值模糊和高斯模糊。如果从降噪的角度来说&#xff0c;模糊算法也算是降噪的一类&#xff0c;所以之前介绍的两种模糊可以称呼为均值降噪和高斯降噪。不过模糊算法对原来的图像特征的…
阅读更多...
基础算法--双指针

基础算法--双指针

两数之和 点击&#xff1a;题目链接 解法一&#xff1a;暴力解法 时间复杂度&#xff1a;O(N^2) 算法思路&#xff1a;两层for循环即可列出所有两个数字的组合&#xff0c;判断是否等于目标值 算法流程&#xff1a; 两层 for 循环&#xff1a; 外层 for 循环依次枚举第⼀个…
阅读更多...
WPF+LibVLC开发播放器-音量控制和倍速控制

WPF+LibVLC开发播放器-音量控制和倍速控制

界面 界面上增加音量的控件和倍速控制控件 音量控制 主要也是一个Slider进度条控件来实现音量调节 我们这里设置默认的最大值为100&#xff0c;默认Value值也为100&#xff0c;默认声音开到最大 这里目前完全由前端控制音量调节&#xff0c;可以直接使用ValueChanged事件实…
阅读更多...
【C语言练习（3) —水仙花数判断】

【C语言练习（3) —水仙花数判断】

系列文章目录 文章目录 系列文章目录前言题目解题思路结果总结与反思 前言 通过水仙花数巩固之前学习知识点&#xff0c;锻炼自己的写敲代码能力&#xff0c;只有写才能发现问题、找问题、解决问题 题目 求出所有5位数中的所有水仙花数&#xff08;Lily Number&#xff09;&a…
阅读更多...
【专题】2024年11月新能源汽车、智能汽车行业报告汇总PDF洞察（附原数据表）

【专题】2024年11月新能源汽车、智能汽车行业报告汇总PDF洞察（附原数据表）

原文链接&#xff1a;https://tecdat.cn/?p38520 随着科技的飞速发展与社会的持续变革&#xff0c;新能源汽车与智能汽车行业正步入全新的发展阶段&#xff0c;成为全球瞩目的焦点领域。本报告深入且全面地剖析了 2024 年 11 月该行业的多方面状况。从汽车消费市场来看&#…
阅读更多...
【C++图论 BFS算法】2467. 树上最大得分和路径|2053

【C++图论 BFS算法】2467. 树上最大得分和路径|2053

本文涉及知识点 C图论 CBFS算法 LeetCode2467. 树上最大得分和路径 一个 n 个节点的无向树&#xff0c;节点编号为 0 到 n - 1 &#xff0c;树的根结点是 0 号节点。给你一个长度为 n - 1 的二维整数数组 edges &#xff0c;其中 edges[i] [ai, bi] &#xff0c;表示节点 a…
阅读更多...
Mysql | 尚硅谷 | 第02章_MySQL环境搭建

Mysql | 尚硅谷 | 第02章_MySQL环境搭建

Mysql笔记&#xff1a;第02章_MySQL环境搭建 说明&#xff1a;本内容整理自尚硅谷B站MySQL视频>>尚硅谷B站MySQL视频 文章目录 Mysql笔记&#xff1a;第02章_MySQL环境搭建第02章_MySQL环境搭建 1. MySQL的卸载步骤1&#xff1a;停止MySQL服务步骤2&#xff1a;[软件](h…
阅读更多...
【网络篇】TCP知识

【网络篇】TCP知识

TCP首部格式&#xff1f; 为什么需要 TCP 协议&#xff1f; TCP 工作在哪一层&#xff1f; IP 层是不可靠的&#xff0c;它不保证网络包的交付、不保证网络包的按序交付也不保证网络包中的数据的完整性。如果需要保障网络数据包的可靠性&#xff0c;那么就需要由上层&#xff0…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023