分享好友 站长动态首页 网站导航

关于MySQL数据库性能优化方法,看这一篇文章就够了

2022-06-02 07:00 · 头闻号数据库

数据库大量应用程序开发项目中,大多数情况下,数据库的操作性能成为整个应用的性能瓶颈。数据库的性能是程序员需要去关注的事情,当设计数据库表结构以及操作数据库(尤其是查询数据时),都需要注意数据操作的性能。本文我们以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 操作次数降低,直接提升性能。

(1) 数据类型选择

原则是:数据行的长度不要超过8020字节,如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片,降低查询效率;字段的长度在最大限度的满足可能的需要的前提下,应该尽可能的设得短一些,这样可以提高查询的效率,而且在建立索引的时候也可以减少资源的消耗。

(2) 字符编码

字符集直接决定了数据在MySQL中的存储编码方式,由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异,所以通过使用合适的字符集,可以帮助我们尽可能减少数据量,进而减少IO操作次数。

(3) 尽量使用 NOT NULL

NULL 类型比较特殊,SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL有差异,会进入索引中,但如果是一个组合索引,那么这个NULL 类型的字段会极大影响整个索引的效率。虽然 NULL空间上可能确实有一定节省,倒是带来了很多其他的优化问题,不但没有将IO量省下来,反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL,也是一个很好的表结构设计优化习惯。

3. 数据库架构优化

分布式和集群化:

4. 其他优化

(1) 适当使用视图加速查询。

把表的一个子集进行排序并创建视图,有时能加速查询(特别是要被多次执行的查询)。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。视图中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以查询工作量可以得到大幅减少。

(2) 算法优化。

尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。

(3)封装存储过程。

经编译和优化后存储在数据库服务器中,运行效率高,可以降低客户机和服务器之间的通信量,有利于集中控制,易于维护。

免责声明:本平台仅供信息发布交流之途,请谨慎判断信息真伪。如遇虚假诈骗信息,请立即举报

举报
反对 0
打赏 0
更多相关文章

评论

0

收藏

点赞