数据库性能优化是项目实施过程中必须要做的事情,因为每个项目不同,对其数据的要求、性能压力也不同,因此没有办法给出所谓的标准。实际调整情况要根据客户现场提供的服务器、业务使用情况做出最优调整,因此给出数据库的调优方法和策略才能实现一劳永逸。 

本文结合了前人和自己的项目实施经验,对数据库性能优化给出调优思路,然后根据客户现场的实际情况印证给出的方式进行对应调优,从而实现数据库的性能灵活调整,保障调整过后的有效性,而不是生搬硬套,影响原有的性能。

1整体思路

对于数据库性能优化不能只着眼于数据库,而是要从整体考虑,从项目和实际角度出发,然后根据情况进行分析,以此决定应该从哪些方面入手、如何去做,下面给出几种相应的调优场景:

1.数据库部署时,根据现场的实际情况,考虑是否要进行应用和数据库分开部署,让数据库单独使用服务器资源,通过调整服务器配置和数据库配置,保证性能优化的空间;

2.考虑数据库读写分离,项目开发写入数据到主库,查询用从库,保证数据库写入、查询分开使用,避免事务冲突,提高数据库使用效率;

3.在实际使用过程中交互较慢的功能,针对使用sql进行分析,查看相关sql是否存在优化的可能,针对sql进行优化,提高sql效率,侧面提高数据库的性能。

2配置优化

硬性调整有两个方面,一是服务器层面,从服务器的配置上提高服务器的使用效率,以此扩大数据库的可优化空间;二是数据库层面,进行配置调整,保证数据库本身的配置是实际情况下最优的调整,尽可能的提高数据库使用的性能瓶颈。

2.1服务器

Linux系统中,sysctl命令被用于在内核运行时动态地修改内核的运行参数,可用的内核参数在目录/proc/sys中。它包含一些TCP/IP堆栈和虚拟内存系统的高级选项,用sysctl可以读取设置超过五百个系统变量。

修改配置文件位置如下:

调整内容如下:

2.2数据库

服务器优化主要是为了提高服务器本身的性能瓶颈,保证数据库在优化过程中不会因为服务器性能瓶颈影响到数据库的性能调整没有生效,提高数据库的优化空间,因此在数据库优化配置过程中,要根据服务器性能做适当调整,保证在合理范围之内能够生效,提高数据库的效率。

修改配置文件位置如下:

调整内容如下:

3常规优化

除了服务器和数据库调整之外,还要考虑针对日常sql使用的规范和相关数据处理是否恰当进行排查,要针对项目使用过程中出现问题的sql和数据内容及时做出调整或反馈,协助开发人员进行相关问题的处理,从性能优化的角度规范产品的开发。

3.1查询优化

SQL查询优化有多种方式,好的SQL能有效的提升查询性能,下面枚举典型的查询优化注意点:

1.不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段;

2.在SQL查询时尽量不要使用IN或者NOT IN,对于区间查询建议使用BETWEEN AND;

3.查询时尽量不要在where 子句中使用 != 或 <> 操作符,相关条件可使用< 、 <= 、 = 、 > 、 >= 、 BETWEEN AND;

4.查询过程中,如果是多表关联查询,则将数据量较大的表作为SQL查询的主体表,且关联查询时建议使用InnerJoin进行关联;

5.查询count的SQL不要有排序。

3.2索引添加

数据表索引的添加是在保证现有性能的前提下,最快、最显著提升数据库查询性能的方式,添加索引一般在创建表时创建索引列,索引一般选择主键或者外键,可以加快sql的查询速度。

除上述主键和外键添加索引外,也可以根据实际sql查询情况添加索引,添加规则就是sql的判断条件,where条件之后的判断,特别是相等判断所涉及表的字段,就可以作为索引列进行添加,提高sql的查询效率。

索引添加方式以navicat为例,点开设计表,然后选择索引标签,新增添加需要添加的索引,并选择相应的索引类型和方法即可,效果如下:

判断是否索引生效,通过执行sql前添加explain,查看索引添加前和添加后应用情况,参数解析如下:

type:连接类型可划分为几种类型执行效率分别为const>eq_ref>ref>range>index>all;

1.const:查询索引字段,并且表中最多只有一行匹配(只有主键查询只匹配一行才会是const,有些情况唯一索引匹配一行会是ref);

2.eq_ref主键或者唯一索引;

3.ref非唯一索引(主键也是唯一索引);

4.range索引的范围查询;

5.index (type=index extra = using index 代表索引覆盖,即不需要回表);

6.all全表扫描(通常没有建索引的列)。

extra:额外信息说明;

1.using temporary(组合查询返回的数据量太大需要建立一个临时表存储数据,出现这个sql应该优化);

2.using where (where查询条件);

3.using index(判断是否仅使用索引查询,使用索引树并且不需要回表查询);

4.using filesort(order by 太占内存,使用文件排序)。

3.3分表优化

数据库中单表的数据过大也会影响到sql的执行效率,因此针对一些特殊的表要进行定期分割,分割的方式可以是手动分割,或者建议产品进行定时分割,根据实际的业务场景,按需进行反馈,以此保证产品的场景覆盖。

针对数据分表,可以采用月表的方式进行分割,像认证日志、临时数据存储、报警日志、同步日志、分发日志等很容易引起单表数据过巨的数据存储,因此定期分表是很有必要的,效果如下:

3.4视图查询

针对过于复杂的查询(多表以及子查询等),或者多表联合查询后数据量相对较少的情况下,都可以通过创建视图的方式提高效率(相较于直接使用sql查询),使用视图可以定制用户数据,聚焦特定数据,同时可以简化数据操作,对于表中的数据有一定的保护作用,不易于对数据造成破坏。效果如下:

注:视图的使用根据实际情况进行选择,并且只适用于mysql数据库的部分优化,如果是sqlserver的数据库,反而会影响性能。

3.5事务优化

在数据库使用中尽量减少长事务,如果涉及到主表、从表、附属从表,同时操作三个数据表可能会同时成功以及同时失败,如果当前数据表的数据量较大,为了降低数据库的性能压力,我们可以采用批处理方式分别批处理三个数据表进行数据库性能的提升。

同时也要注意减少分布式事务的使用,一般的数据库均是支持分布式事务,当涉及到跨数据库的不同数据表操作时可以使用分布式事务。但为了提高性能损耗,尽量减少这种强一致性需求,更多情况下转化为最终一致性方式来满足业务需求,通常来说引入消息中间件是这种场景下的常规解决手段。

注:事务优化在实际项目中涉略应用较少,更多是在产品开发过程中需要考虑。

4监控优化

上述过程都是针对可预见问题的处理和优化方式,还有一种常见的情况就是在上线运行阶段一些性能问题的出现,针对这种情况,要能够掌握快速定位问题的方式,然后根据定位出来的问题,应用常规优化方式进行优化,保证数据库高效稳定的运行。

4.1定位方法

针对日常上线后一些问题的出现,比如登录缓慢、展现缓慢等问题,首先要定位是否是数据库造成的,这时可以通过查看cpu的占用率来查看数据库是否处于高性能状态下,命令为top(linux下执行),查看mysqld的CPU占用率是否一直处于高水平。

排除产品性能影响外,如果仍出现缓慢的情况,可以通过以下语句查看mysql的线程使用情况,如下:

效果如下:

同时查看数据库的最大连接数和线程数比较,看是否差距在不断缩小,如下:

效果如下:

同时查询正在执行的线程,查看是否出现执行时间过长的sql,命令如下: 

效果如下:

4.2问题处理

针对上述方式进行定位后,如果存在执行效率低的情况,这个时候会在上图所示的info中出现执行的sql语句,这时可以将这个sql语句copy出来,单独拿到数据库中执行查询,以此观察查询时间,如果时间过长,就根据文档中提到的常规优化方式进行sql语句的优化,并将优化后的sql在数据库中不断运行测试,直到运行效率达到要求为止。

将优化后的sql反馈给产品研发人员,定位相应的功能点以及sql使用情况,从产品角度进行sql修改,进行补丁修订,再反馈给现场进行替换测试,查看修改后的运行情况和效率。

5心得体会

本次数据库性能优化整理,是基于前人的经验和自己在实际项目中的应用做出总结,通过本篇文档的整理,也是自己对项目工作的回顾,加深对性能优化的认识,同时也是对自己的知识和能力的积累。 

5.1认知理解

随着企业信息化的不断发展,对于性能的要求会越来越高,因此如何提高系统性能将会成为基本技能,对于项目中的服务器如何调优、nginx如何调优、redis如何调优、产品如何调优以及数据库如何调优都应该是必须掌握的内容,只有这样,对于项目的把控才能游刃有余,不至于畏畏缩缩,心里没有底气。

5.2技能提升

通过在项目上的不断尝试和运维,自己的能力得到了锻炼,自己也不断的学习和了解到了不同参数对性能的影响,更加珍贵的是自己得到了这个难得机会,对系统性能和业务的联系有了认知,并且针对性能问题的排查也得到了训练,无论从哪个角度来说,收获都是颇丰的。

5.3自我反思

以前由于接触的项目当量没有上去,对于性能优化认知不足,导致实际运维过程中捉襟见肘、无从下手,而且对于优化内容,优化点也是不理解,对于性能优化的一些手段没有系统的梳理和掌握,导致频频失误,通过本次项目的高要求,对相关内容重新梳理和整理,为以后项目实施提供了借鉴。

产品的考验往往不在于功能,而是在于性能,通俗点说就是“是不是好用”,因为好用才能赢来更好的口碑,为此,行与不行往往不在于产品,而是在于人的认知和能力,因为产品本身的“能力”是高于人的,只有人的认证和能力都到位了,才不至于拖产品的后腿。