关于MySQL数据库性能优化方法,看这一篇文章就够了
wptr33 2024-12-28 15:58 18 浏览
数据库大量应用程序开发项目中,大多数情况下,数据库的操作性能成为整个应用的性能瓶颈。数据库的性能是程序员需要去关注的事情,当设计数据库表结构以及操作数据库(尤其是查询数据时),都需要注意数据操作的性能。本文我们以MySQL数据库为例进行讨论。
一、数据库优化目标
1、减少 IO 次数
IO永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过90%的时间都是 IO 操作所占用的,减少 IO 次数是 SQL 优化中需要第一优先考虑,当然,也是收效最明显的优化手段。
2、降低 CPU 计算
除了 IO 瓶颈之外,SQL优化中需要考虑的就是 CPU 运算量的优化了。order by,group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后,降低 CPU计算也就成为了我们 SQL 优化的重要目标。
MySql查询过程
二 数据库优化方法
1.SQL语句优化
明确了优化目标之后,我们需要确定达到我们目标的方法。对于SQL语句来说,达到上述2个优化目标的方法其实只有一个,那就是改变SQL的执行计划,让他尽量“少走弯路”,尽量通过各种“捷径”来找到我们需要的数据,以达到“减少IO次数”和“降低CPU计算”的目标。
1)尽量少 join。MySQL 的优势在于简单,但这在某些方面其实也是其劣势。MySQL优化器效率高,但是由于其统计信息的量有限,优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join,一方面由于其优化器受限,再者在Join这方面所下的功夫还不够,所以性能表现离Oracle等关系型数据库前辈还是有一定距离。但如果是简单的单表查询,这一差距就会极小甚至在有些场景下要优于这些数据库前辈。
2)尽量少排序
3)排序操作会消耗较多的 CPU 资源,所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。
4)尽量避免 select *,并尽量用join代替子查询
5)尽量少使用“or”关键字
当 where 子句中存在多个条件以“或”并存的时候,MySQL 的优化器并没有很好的解决其执行计划优化问题,再加上 MySQL 特有的 SQL 与 Storage 分层架构方式,造成了其性能比较低下,很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。
6)尽量用 union all 代替 union
union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的 CPU 运算,加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候,尽量使用 union all 而不是 union。
7)避免类型转换
8)能用DISTINCT的就不用GROUP BY
9)尽量不要用SELECT INTO语句 ?
10)从全局出发优化,而不是片面调整
SQL 优化不能是单独针对某一个进行,而应充分考虑系统中所有的 SQL,尤其是在通过调整索引优化 SQL的执行计划的时候,千万不能顾此失彼,因小失大。
2.表结构优化
MySQL数据库是基于行(Row)存储的数据库,而数据库操作 IO 的时候是以 page(block)的方式,也就是说,如果我们每条记录所占用的空间量减小,就会使每个page中可存放的数据行数增大,那么每次 IO 可访问的行数也就增多了。反过来说,处理相同行数的数据,需要访问的 page 就会减少,也就是 IO 操作次数降低,直接提升性能。
数据类型选择
原则是:数据行的长度不要超过8020字节,如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片,降低查询效率;字段的长度在最大限度的满足可能的需要的前提下,应该尽可能的设得短一些,这样可以提高查询的效率,而且在建立索引的时候也可以减少资源的消耗。 ? ?
1)数字类型:非万不得已不要使用DOUBLE,不仅仅只是存储长度的问题,同时还会存在精确性的问题。同样,固定精度的小数,也不建议使用DECIMAL,建议乘以固定倍数转换成整数存储,可以大大节省存储空间,且不会带来任何附加维护成本。
2)字符类型:定长字段,建议使用 CHAR 类型(char查询快,但是耗存储空间,可用于用户名、密码等长度变化不大的字段),不定长字段尽量使用 VARCHAR(varchar查询相对慢一些但是节省存储空间,可用于评论等长度变化大的字段),且仅仅设定适当的最大长度,而不是非常随意的给一个很大的最大长度限定,因为不同的长度范围,MySQL也会有不一样的存储处理。
3)时间类型:尽量使用TIMESTAMP类型,因为其存储空间只需要DATETIME 类型的一半。对于只需要精确到某一天的数据类型,建议使用DATE类型,因为他的存储空间只需要3个字节,比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值,因为这太不直观,会给维护带来不必要的麻烦,同时还不会带来任何好处。
4)ENUM &SET:对于状态字段,可以尝试使用 ENUM 来存放,因为可以极大的降低存储空间,而且即使需要增加新的类型,只要增加于末尾,修改结构也不需要重建表数据。
字符编码
字符集直接决定了数据在MySQL中的存储编码方式,由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异,所以通过使用合适的字符集,可以帮助我们尽可能减少数据量,进而减少IO操作次数。
尽量使用 NOT NULL
NULL 类型比较特殊,SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL有差异,会进入索引中,但如果是一个组合索引,那么这个NULL 类型的字段会极大影响整个索引的效率。虽然 NULL空间上可能确实有一定节省,倒是带来了很多其他的优化问题,不但没有将IO量省下来,反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL,也是一个很好的表结构设计优化习惯。
3.数据库架构优化
分布式和集群化
1)负载均衡。负载均衡集群是由一组相互独立的计算机系统构成,通过常规网络或专用网络进行连接,由路由器衔接在一起,各节点相互协作、共同负载、均衡压力,对客户端来说,整个群集可以视为一台具有超高性能的独立服务器。MySQL一般部署的是高可用性负载均衡集群,具备读写分离,一般只对读进行负载均衡。
2)读写分离。读写分离简单的说是把对数据库读和写的操作分开对应不同的数据库服务器,这样能有效地减轻数据库压力,也能减轻io压力。主数据库提供写操作,从数据库提供读操作,其实在很多系统中,主要是读的操作。当主数据库进行写操作时,数据要同步到从的数据库,这样才能有效保证数据库完整性。
3)数据切分。通过某种特定的条件,将存放在同一个数据库中的数据分散存放到多个数据库上,实现分布存储,通过路由规则路由访问特定的数据库,这样一来每次访问面对的就不是单台服务器了,而是N台服务器,这样就可以降低单台机器的负载压力。
4.其他优化
1)适当使用视图加速查询。把表的一个子集进行排序并创建视图,有时能加速查询(特别是要被多次执行的查询)。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。视图中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以查询工作量可以得到大幅减少。
2)算法优化。尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。
3)封装存储过程。经编译和优化后存储在数据库服务器中,运行效率高,可以降低客户机和服务器之间的通信量,有利于集中控制,易于维护。
最后:
1)关注+私信回复:“测试”,可以免费领取一份10G软件测试工程师面试宝典文档资料。以及相对应的视频学习教程免费分享!,其中包括了有基础知识、Linux必备、Mysql数据库、抓包工具、接口测试工具、测试进阶-Python编程、Web自动化测试、APP自动化测试、接口自动化测试、测试高级持续集成、测试架构开发测试框架、性能测试等。
2)关注+私信回复:"入群" 就可以邀请你进入软件测试群学习交流~~
相关推荐
- 【推荐】一款开源免费、美观实用的后台管理系统模版
-
如果您对源码&技术感兴趣,请点赞+收藏+转发+关注,大家的支持是我分享最大的动力!!!项目介绍...
- Android架构组件-App架构指南,你还不收藏嘛
-
本指南适用于那些已经拥有开发Android应用基础知识的开发人员,现在想了解能够开发出更加健壮、优质的应用程序架构。首先需要说明的是:AndroidArchitectureComponents翻...
- 高德地图经纬度坐标批量拾取(高德地图批量查询经纬度)
-
使用方法在桌面上新建一个index.txt文件,把下面的代码复制进去保存,再把文件名改成index.html保存,双击运行打开即可...
- flutter系列之:UI layout简介(flutter ui设计)
-
简介对于一个前端框架来说,除了各个组件之外,最重要的就是将这些组件进行连接的布局了。布局的英文名叫做layout,就是用来描述如何将组件进行摆放的一个约束。...
- Android开发基础入门(一):UI与基础控件
-
Android基础入门前言:...
- iOS的布局体系-流式布局MyFlowLayout
-
iOS布局体系的概览在我的CSDN博客中的几篇文章分别介绍MyLayout布局体系中的视图从一个方向依次排列的线性布局(MyLinearLayout)、视图层叠且停靠于父布局视图某个位置的框架布局(M...
- TDesign企业级开源设计系统越发成熟稳定,支持 Vue3 / 小程序
-
TDesing发展越来越好了,出了好几套组件库,很成熟稳定了,新项目完全可以考虑使用。...
- WinForm实现窗体自适应缩放(winform窗口缩放)
-
众所周知,...
- winform项目——仿QQ即时通讯程序03:搭建登录界面
-
上两篇文章已经对CIM仿QQ即时通讯项目进行了需求分析和数据库设计。winform项目——仿QQ即时通讯程序01:原理及项目分析...
- App自动化测试|原生app元素定位方法
-
元素定位方法介绍及应用Appium方法定位原生app元素...
- 61.C# TableLayoutPanel控件(c# tabcontrol)
-
摘要TableLayoutPanel在网格中排列内容,提供类似于HTML元素的功能。TableLayoutPanel控件允许你将控件放在网格布局中,而无需精确指定每个控件的位置。其单元格...
- 12个python数据处理常用内置函数(python 的内置函数)
-
在python数据分析中,经常需要对字符串进行各种处理,例如拼接字符串、检索字符串等。下面我将对python中常用的内置字符串操作函数进行介绍。1.计算字符串的长度-len()函数str1='我爱py...
- 如何用Python程序将几十个PDF文件合并成一个PDF?其实只要这四步
-
假定你有一个很无聊的任务,需要将几十个PDF文件合并成一个PDF文件。每一个文件都有一个封面作为第一页,但你不希望合并后的文件中重复出现这些封面。即使有许多免费的程序可以合并PDF,很多也只是简单的将...
- Python入门知识点总结,Python三大数据类型、数据结构、控制流
-
Python基础的重要性不言而喻,是每一个入门Python学习者所必备的知识点,作为Python入门,这部分知识点显得很庞杂,内容分支很多,大部分同学在刚刚学习时一头雾水。...
- 一周热门
-
-
C# 13 和 .NET 9 全知道 :13 使用 ASP.NET Core 构建网站 (1)
-
因果推断Matching方式实现代码 因果推断模型
-
面试官:git pull是哪两个指令的组合?
-
git pull命令使用实例 git pull--rebase
-
git 执行pull错误如何撤销 git pull fail
-
git pull 和git fetch 命令分别有什么作用?二者有什么区别?
-
git fetch 和git pull 的异同 git中fetch和pull的区别
-
git pull 之后本地代码被覆盖 解决方案
-
还可以这样玩?Git基本原理及各种骚操作,涨知识了
-
git命令之pull git.pull
-
- 最近发表
- 标签列表
-
- git pull (33)
- git fetch (35)
- mysql insert (35)
- mysql distinct (37)
- concat_ws (36)
- java continue (36)
- jenkins官网 (37)
- mysql 子查询 (37)
- python元组 (33)
- mysql max (33)
- vba instr (33)
- mybatis 分页 (35)
- vba split (37)
- redis watch (34)
- python list sort (37)
- nvarchar2 (34)
- mysql not null (36)
- hmset (35)
- python telnet (35)
- python readlines() 方法 (36)
- munmap (35)
- docker network create (35)
- redis 集合 (37)
- python sftp (37)
- setpriority (34)