MySQL性能调优-6.影响MySQL性能的相关因素

MySQL调优 MySQL

创建时间:2020-06-06 15:16

字数:1.9k 阅读:

评论:

影响MySQL Server性能相关因素

影响MySQL Server性能相关因素

商业需求对性能的影响

每次产品经理们提出新的项目(或者功能需求)的时候，应该要求他们同时给出该项目的预期收益的量化指标，以备项目上先后统计评估投入产出比率;
在每次项目进行过程中，应该详细记录所有的资源投入，包括人力投入，硬件设施的投入，以及其他任何项目相关的资源投入;
项目(或者功能需求)上线之后应该及时通过手机相关数据统计出项目的实际收益值，以便计算投入产出比率的时候使用;
技术部门应该尽可能推动设计出一个项目(或者功能需求)的投入产出比率的计算规则。在项目上线一段时间之后，通过项目实际收益的统计数据和项目的投入资源量，
计算出整个项目的实际投入产出值，并公布给所有参与项目的部门知晓，同时存放以备后查。

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

不适合存放在数据库中的数据:
1. 二进制多媒体数据
2. 流水队列数据
3. 超大文本数据
是否合理的运用了应用层Cache机制
对于 Web 应用，活跃数据的数据量总是不会特别的大，有些活跃数据更是很少变化。
我们的数据层实现都是最精简的吗?
例如: 现在要实现每个用户查看各自相册列表(假设每个列表显示10 张相片) 的时候，能够在相片名称后面显示该相片的留言数量。
方案一:
1、通过“SELECT id,subject,url FROM photo WHERE user_id = ? limit 10” 得到第一页的相片相关信息;
2 、通过第 1 步结果集中的 10 个相片 id 循环运行十次 “ SELECT COUNT(*) FROM photo_comment WHERE photh_id = ?” 来得到每张相册的回复数量然后再瓶装展现对象。

方案二:
1、和上面完全一样的操作步骤;
2、通过程序拼装上面得到的 10 个 photo 的 id，再通过 in 查询“SELECT photo_id,count(*) FROM
photo_comment WHERE photo_id in (?) GROUP BY photo_id” 一次得到 10 个 photo 的所有回复数量，再组装两个结果集得到展现对象。

比较如下:
1、从 MySQL 执行的 SQL 数量来看，第一种解决方案为 11(1+10=11)条 SQL 语句，第二种解决方案为 2 条 SQL 语句(1+1);
2、从应用程序与数据库交互来看，第一种为11 次，第二种为 2 次;
3、从数据库的 IO 操作来看，简单假设每次 SQL 为 1 个 IO，第一种最少 11 次 IO，第二种小于等于 11 次 IO，而且只有当数据非常之离散的情况下才会需要11 次;
4、从数据库处理的查询复杂度来看，第一种为两类很简单的查询，第二种有一条 SQL 语句有 GROUP BY 操作，比第一种解决方案增加了了排序分组操作;
5、从应用程序结果集处理来看，第一种11 次结果集的处理，第二中2 次结果集的处理，但是第二种解决方案中第二词结果处理数量是第一次的10 倍;
6、从应用程序数据处理来看，第二种比第一种多了一个拼装photo_id 的过程。

分析如下:
1、由于 MySQL 对客户端每次提交的 SQL 不管是相同还是不同，都需要进行完全解析，这个动作主要消耗的资源是数据库主机的 CPU，
那么这里第一种方案和第二种方案消耗CPU 的比例是 11:2。SQL 语句的解析动作在整个 SQL 语句执行过程中的整体消耗的 CPU 比例是较多的;
2、应用程序与数据库交互所消耗的资源基本上都在网络方面，同样也是11:2;
3、数据库 IO 操作资源消耗为小于或者等于 1:1;
4、第二种解决方案需要比第一种多消耗内存资源进行排序分组操作，由于数据量不大，多出的消耗在语句整体消耗中占用比例会比较小，大概不会超过20%，
大家可以针对性测试;
5、结果集处理次数也为 11:2，但是第二中解决方案第二次处理数量较大，整体来说两次的性能消耗区别不大;
6、应用程序数据处理方面所多出的这个 photo_id 的拼装所消耗的资源是非常小的，甚至比应用程序与 MySQL 做一次简单的交互所消耗的资源还要少。

结论:
1、提交的SQL会占用数据库主机CPU
2、应用程序与数据库交互会占用网络
3、SQL会占用IO
4、字段拼装的消耗非常小

SQL并非越精简越好

不应该对表有不必要的访问
不应重复执行相同的SQL

数据库因架构设计而引起性能问题和资源浪费问题

Cache 系统的不合理利用导致 Cache 命中率低下造成数据库访问量的增加，同时也浪费了 Cache 系统的硬件资源投入;
过度依赖面向对象思想
对可扩展性的过渡追求，促使系统设计的时候将对象拆得过于离散，造成系统中大量的复杂Join 语句，而 MySQL Server 在各数据库系统中的主要优势在于处理简单逻辑的查询，这与其锁定的机制也有较大关系;
对数据库的过渡依赖，将大量更适合存放于文件系统中的数据存入了数据库中，造成数据库资源的浪费，影响到系统的整体性能，如各种日志信息;
过度理想化系统的用户体验，使大量非核心业务消耗过多的资源，如大量不需要实时更新的数据做了实时统计计算。

Query语句对系统性能的影响

当MySQL Server的连接线程接收到Client端发送过来的SQL请求后，会经过一系列的分解Parse，进行相应的分析。
MySQL会通过查询优化器模块根据该 SQL 所设涉及到的数据表的相关统计信息进行计算分析，得出一个 MySQL认为最合理最优化的数据访问方式(执行计划)。
再根据所得到的执行计划通过调用存储引擎接口来获取相应数据，再将存储引擎返回的数据进行相关处理。
最后以 Client 端所要求的格式作为结果集返回给 Client 端的应用程序。

在数据库管理软件中，最大的性能瓶颈就是磁盘IO，当我们需要从数据库中查询某个数据的时候，
所消耗资源的多少主要就取决于数据库以一个什么样的数据读取方式来完成我们的查询请求，也就是取决于SQL语句的执行计划。

Schema设计对系统性能的影响

根据需求制定表
- 如: 讨论区系统需要经常访问帖子标题列表，而需要帖子和作者名应尽量放在同一表中，并且content帖子内容应单独建表。

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 yanglau0527@gmail.com