最近研究mysql的性能优化,网上学习到部分知识,总结下来,分享下。
这个专题计划写4篇文章,目前先提供第一部分:
总体大纲如下图:
第一篇先写:影响性能的因素
一、影响性能的因素:
1.1不合理的需求:
需求:一个论坛帖子总量的统计
附加要求:实时更新
1,初级阶段:SELECT COUNT(*)
2,新建一个表,在这个表中更新这个汇总数据(频率问题)
3,真正的问题在于,实时?创建一个统计表,隔一段时间统计一次并存入;
1.2无用功能堆积:
1,无用的列堆积;
2,错误的表设计;
3,无用的表关联;
2.1哪些数据不适合放在数据库中:
1,二进制数据;(会降低查询效率,增加IO次数)
2,流水队列数据;(类似日志写在文本文件里面)
3,超大文本;
2.2合理的cache,哪些数据适合放到cache中?
1,系统配置信息;
2,活跃的用户的基本信息;(session缓存、redis缓存)
3,活跃用户的定制化信息;
4,基于时间段的统计数据;
5,读>>>写的数据;
2.3减少数据库的交互:
N+1问题的解决:什么是N+1问题,一个对象有一个关联的对象,在a对象的列表里面要现实关联对象的相关属性。我们用一条sql把a对象的内容查出来,用N条sql把关联对象查出来。
解决方式:
1,使用链接查询;(join会返回大结果集;将关联字段作为冗余字段,放在a表中。缺点是关联更新操作变多)
2,使用1+1查询;(用两条sql去解决,在Java端进行数据字段的设置,从而不用关联,推荐此方式)
2.4过度依赖数据库SQL语句的功能:
常见的:交叉表查询;不必要的表连接查询;
拆分大SQL,进行SQL拆分,多查询几次,在Java端进行组装合并。
2.5重复执行相同的SQL。
2.6其他常见系统架构和实现问题:
1、Cache 系统的不合理利用导致Cache 命中率低下造成数据库访问量的增加,同时也浪费了Cache系统的硬件资源投入;
2、过度依赖面向对象思想,对系统可扩展性的过渡追求,促使系统设计的时候将对象拆得过于离散,造成系统中大量的复杂Join语句,而MySQL Server 在各数据库系统中的主要优势在于处理简单逻辑的查询,这与其锁定的机制也有较大关系;
4、对数据库的过渡依赖,将大量更适合存放于文件系统中的数据存入了数据库中,造成数据库资源的浪费,影响到系统的整体性能,如各种日志信息;
5、过度理想化系统的用户体验,使大量非核心业务消耗过多的资源,如大量不需要实时更新的数据做了实时统计计算。
3.1 SQL引起性能问题的原因:
SQL的执行过程;
1. 客户端发送一条查询给服务器;
2. 服务器先会检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果。否则进入下一阶段;
3. 服务器端进行SQL解析、预处理,再由优化器根据该SQL所涉及到的数据表的统计信息进行计算,生成对应的执行计划;
4. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询;
5. 将结果返回给客户端。
磁盘IO
SQL执行的最大瓶颈在于磁盘的IO,即数据的读取;不同SQL的写法,会造成不同的执行计划的执行,而不同的执行计划在IO的上面临完全不一样的数量级,从而造成性能的差距;
3.2 Schema(表结构)设计对系统的性能影响:
1,冗余数据的处理;适当的数据冗余可以提高整体查询性能。补充下:数据库的三大范式
第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库,是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值;
第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。
第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 (不允许有冗余数据)
2,大表拆小表,有大数据的列单独拆成小表;
1,在一个数据库中,一般不会设计属性过多的表;
2,在一个数据库中,一般不会有超过500/1000万数据的表(拆表,按照逻辑拆分,按照业务拆分);
3,有大数据的列单独拆成小表(富文本编辑器,CKeditor);
3,根据需求的展示设置更合理的表结构;
4,把常用属性分离成小表;
1,减少查询常用属性需要查询的列;
2,便于常用属性的集中缓存;
3.3硬件环境对性能的影响:
1,提高IO指标
IOPS:每秒可提供的IO 访问次数;
IO 吞吐量:每秒的IO 总流量;
2,提高CPU计算能力;
3,如果是单独的数据库服务器,提高网络能力;
3.4数据库系统场景:
两种典型的数据库应用场景:
①联机事务处理OLTP(on-line transaction processiing):OLTP是传统的关系型数据库的主要应用,主要是基本的、日常的事务处理,例如银行交易。
②联机分析处理OLAP(on-line tanalytical processiing):OLAP是数据仓库系统的主要应用,支持复杂分析操作,侧重决策支持,并且提供直观易懂的查询结果,数据仓库就是一个典型的应用场景。
OLTP的数据库应用特点:
1,系统总体数据量较大,但活动数据量较小;
2,IO访问频繁,单涉及数据量较小,分布离散;
3,并发很高;
4,网络交互数据量较小,但交互频繁;
OLTP系统硬件架构选型:
1,大量的合理的cache设计,能够大大减少数据库的交互;应尽量的扩大内存容量;
2,IOPS指标要求较高;
3,CPU的计算能力,并行计算能力要求较高;
4,对外网络要求较高;
OLAP特点:
1,数据量非常大,数据访问集中,数据活跃度分布平均;
2,并发访问较低,
3,每次检索的数量非常多;
OLAP架构选型:
1,硬盘存储容量需要非常大;
2,对存储设备的IO吞吐量要求很高;
3,CPU要求较低;
4,对外网络要求不高;
4 综合考虑
需求和架构及业务实现要求在现有基础上优化:55%
而实际上我们能做的部分:1、SQL语句查询的优化占比30%左右 2、服务器数据库自身硬件的提升占比15%左右