盘点一下影响MySQL性能的因素

最新推荐文章于 2022-12-22 11:45:13 发布

K'illCode

最新推荐文章于 2022-12-22 11:45:13 发布

阅读量244

点赞数

分类专栏： mysql

mysql 专栏收录该内容

64 篇文章 1 订阅

订阅专栏

既然要优化数据库，我们就首先要知道，优化的是什么，或者说：什么因素影响了数据库的性能。

影响数据库因素主要因素总结如下：

商业需求对性能的影响*
系统架构（存储架构）及实现对性能的影响*
query语句对数据库性能的影响*
Schema设计对系统性能影响*
硬件环境对数据库的性能的影响*

商业需求对性能的影响

不合理需求造成资源投入产出比过低，这里我们就用一个看上去很简单的功能来分析。

需求：一个论坛帖子总量的统计

附加要求：实时更新

从功能上来看非常容易实现，执行一条 SELECT COUNT(*) from 表名的 Query 就可以得到结果。

但是，如果我们采用不是 MyISAM 存储引擎，而是使用的 Innodb 的存储引擎，那么大家可以试想一下，如果存放帖子的表中已经有上千万的帖子的时候，执行这条 Query 语句需要多少成本？

恐怕再好的硬件设备，都不可能在 10 秒之内完成一次查询吧。

注：没有 where 的 count(* ) 使用 MyISAM 要比 InnoDB 快得多。因为 MyISAM 内置了一个计数器，count(* ) 时它直接从计数器中读，而 InnoDB 必须扫描全表。所以在 InnoDB 上执行 count(*) 时一般要伴随 where，且 where 中要包含主键以外的索引列。

既然这样查询不行，那我们是不是该专门为这个功能建一个表，就只有一个字段，一条记录，就存放这个统计量，每次有新的帖子产生的时候，都将这个值增加 1。

这样我们每次都只需要查询这个表就可以得到结果了，这个效率肯定能够满足要求了。

确实，查询效率肯定能够满足要求，可是如果帖子产生很快，在高峰时期可能每秒就有几十甚至上百个帖子新增操作的时候，恐怕这个统计表又要成为大家的噩梦了。

要么因为并发的问题造成统计结果的不准确，要么因为锁资源争用严重造成整体性能的大幅度下降。

其实这里问题的焦点不应该是实现这个功能的技术细节，而是在于这个功能的附加要求“实时更新”上面。

当一个论坛的帖子数量很大了之后，到底有多少人会关注这个统计数据是否是实时变化的？

有多少人在乎这个数据在短时间内的不精确性？

恐怕不会有人会盯着这个统计数字并追究当自己发了一个帖子然后回头刷新页面发现这个统计数字没有加 1 吧？

所以只要去掉了这个“实时更新”的附加条件，就可以非常容易的实现这个功能了。

就像之前所提到的那样，通过创建一个统计表，然后通过一个定时任务每隔一定时间段去更新一次里面的统计值，这样既可以解决统计值查询的效率问题，又可以保证不影响新发贴的效率，一举两得。

系统架构及实现对性能的影响

所有数据都是适合在数据库中存放的吗？

数据库为我们提供了太多的功能，反而让很多并不是太了解数据库的人，错误的使用数据库中很多并不太擅长、或对性能影响很大的功能，最后却全部怪罪到数据库身上。

比如有些数据需要考虑是否存储在MySQL

流水队列数据
二进制多媒体数据
超大的文本数据
其它用户上传的文件、图片等资源

是否考虑了缓存

对于 Web 系统或者 APP 应用，是否有大量热读数据及无需及时变更的数据，然而这些数据可以考虑缓存起来，提高 MySQL 的性能及节约 DB 资源。比如笔者公司的优惠券列表、广告列表、配置规则信息等，属于用户附表信息，无需频繁更新，可以利用 Redis 缓存，让应用跑的更快，用户体验更好。

query 语句对数据库性能的影响

开发人员不能只关注查询结果不关注查询过程，比如每个用户查询各自相册列表（假设每个列显示 10 张相片），能够在相片后有留言，我们要查看留言的数量。

实现的话有好几种方案。

方案1：

select id,subject,url from photo where user_id=? limit 10

通过第一步的结果循环10次执行

select count(*) from photo_comment where photo_id=？

方案2：

第一步和上面是一样的，第二步通过程序拼装上面的到的 10 个 photo_id，通过 in 查询，

select photo_id,count(*) from photo_comment where photo_id in(?)group by photo_id"

一次得到 10 个 photo_id 所有的回复数量。

简要分析的话，方案2更简单一些。

也可以根据 explain 执行分析，具体看执行计划和性能损耗情况（cpu、io 的损耗情况）

mysql>set profiling=1;mysql>select id,subject,url from photo where user_id=? limit 10;mysql>select photo_id,count(*) from photo_comment where photo_id in(?)group by photo_id;mysql>show profiles;mysql>show profile cpu,block io for query ?;

如上系列操作，是分析 SQL 语句的执行计划和性能损耗情况。

Schema 设计对系统性能影响

论坛帖子案例：假设现在是高并发的一个论坛系统。

你需要考虑高并发的论坛最高的并发在哪里？

可能最高的并发是查看帖子标题列表，现在往往帖子标题后面会跟一个作者的昵称。然而根据需求帖子标题（作者昵称），这里需要关联（这里就需要有一个 join 查询）。

但是由于高并发业务尽量避免使用关联查询，尽量走单表查询，那此时就会在帖子表冗余作者的昵称，违反了范式设计，但是却提高了系统性能和 QPS。

关于 Schema 设计大多秉承的基础是基于范式设计，然而真实系统中其实个人总结有如下建立：

短小、精简（字段选型、表列数、char(N)、varchar(N) 等）
字段冗余
大小字段拆分（text、varchar(255) 等）
单表行数拆分

最终的目的就是：表小、行小、字段小

硬件环境对数据库的性能的影响

数据库是存取数据的地方，所以数据库主机的 IO 性能肯定是需要最优先考虑的一个因素，这一点不管是什么类型的数据库应用都是适用的。

在主机中决定 IO 性能部件主要由磁盘和内存所决定，当然也包括各种与 IO 相关的板卡。

如何去选择具体的硬件，一般要基于业务是 OLTP 还是 OLAP，这样你才能选择适合的数据库或对应的存储引擎。

OLTP系统：一般并发量大，整体数据量多，每次访问数据较少，访问数据比较离散, 有活跃数据并且比例不大。要大的内存活跃数据可以 Cache，访问频繁每次访问数据少，那么对磁盘的 IOPS 表现要好，吞吐量是次要的。并发高，CPU 要求高，网络交互频繁网络设备要求较高。
OLAP系统：一般数据统计类，大多选用商业数据库，不过基于 Oracle 的大多费用昂贵，MySQL 也确实提供的 Inforbright 列式存储的存储引擎，其此类业务具有数据量大，并发访问不多，每次访问需要检索的数据都比较多，访问集中，没有明显的活跃数据的特点，需要尽可能大的磁盘吞吐量，并发不多，CPU 要求不高。

总之：要根据自己系统的特性选择更适合更廉价的硬件设备。

总结

只有了解哪些因素会影响数据库的性能，才能结合业务去逐个优化和提升对应的性能，不管是存储层面、硬件层面、数据层面等。