SQL性能优化系列——优化概览[一]

2019年7月27日11:24:59 发表评论 88
摘要

1.数据库调优的目标是什么?
2.数据库调优有哪些维度可以选择?
3.如何思考和分析数据库调优这事儿?

数据库调优的目标

简单说,数据库调优目标是为了让数据库运行更快、响应更快、吞吐量更大,但随着用户量不断增加,需要考虑并发问题,还有用户在进行不同业务操作时,数据库的事务处理和SQL查询都会有所不同。因此数据库调优目标还需要更精细的定位。如何确定调优目标呢?

1)用户反馈

通过用户的反馈,找到和数据相关的问题。

2)日志分析

通过查看数据库日志和操作系统日志等方式找出异常,定位问题。

3)服务器资源使用监控

通过监控服务器的CPU、内存、IO使用情况实时了解服务器性能使用,与历史情况对比。

4)数据库内部状况监控

数据库监控中,活动会话(Active Session)监控是一个重要指标,通过它可以清楚了解数据库当前的活动状态是否处于繁忙、是否存在SQL堆积等。

数据库调优有哪些维度可以选择?

数据库调优,调优对象是整个数据库管理系统,不仅仅局限于SQL优化,还包括数据库的部署配置、架构等。可以通过以下几个步骤梳理:

第一步,选择适合的DBMS

之前讲到了SQL和NoSQL两大阵营。在RDBMS中常用的由SQLServer、Oracle、MySQL等。如果对事务性处理及安全性要求比较高的话,可以选择商业数据库产品。这些数据库在事务处理和查询性能上都比较强。比如采用SQLServer,单表存储上亿条都没问题。如果数据表设计的好,即使不采用分库分表查询效率一样不差。

此外,也可以采用开源的MySQL,它有很多存储引擎可供选择,进行事务处理的话可以采用InnoDB,非事务处理可以采用MyISAM。

NoSQL阵营包括键值型数据库、文档型数据库、搜索引擎、列式存储和图形数据库。这些库的使用场景和优缺点各有不同,比如列式存储数据库可以大幅降低系统IO,适合于分布式文件系统和OLAP,但数据库频繁增删改列式存储就不恰当了,原因在前面的放弃系列已经说过不再赘述。

第二步,优化表设计

可以参考的优化原则:

1)表结构要尽量遵循第三范式的原则(后面讲到)。这样可以让数据结构更清晰、规范,减少冗余字段,同时减少了增删改数据时异常情况的发生。

2)如果分析查询应用比较多,尤其是需要进行多表联查时,可以采用反范式进行优化。反范式采用空间换时间的方式,通过增加冗余字段提高查询效率。

3)表字段的数据类型选择,关系到了查询效率的高低及存储空间的大小。如果字段可以采用数值类型就不要采用字符类型;字符长度要尽可能设计的短一点。就字符类型来说,当确定字符长度固定时,可以采用CHAR类型;长度不固定时,通常采用VARCHAR类型。

数据表的结构设计是基础,也很关键。好的表结构即使在业务发展和用户数增加的情况下依然发挥作用,不好的表结构设计会让数据表变得臃肿、查询效率降低。

第三步,优化逻辑查询

SQL查询优化,可分为逻辑查询优化和物理查询优化。逻辑查询优化就是通过改变SQL语句的内容让SQL执行效率更高,采用的方式是对SQL进行等价变换,对查询进行重写,重写查询的数学基础就是关系代数。

SQL的查询重写包括子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。

例如在EXISTS子查询和IN子查询时,会以小表驱动大表的原则选择适合的子查询。在WHERE子句中尽量避免对字段进行函数运算,它们会让字段的索引失效。

示例:查询内容开头为abc的内容有哪些?

如果在WHERE子句中使用函数,SQL如下:

SELECT comment_id, comment_text, comment_time FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'

如果采用查询重写的方式进行等价替换,SQL如下:

SELECT comment_id, comment_text, comment_time FROM product_comment WHERE comment_text LIKE 'abc%'

在数据量小时倒无所谓,数据量大时,第二种SQL查询效率会提升很多,执行时间是前者的1/10。

第四步,优化物理查询

物理查询优化是将逻辑查询的内容变为可被执行的物理操作符,从而为后续执行器的执行提供准备。它的核心是高效地建立索引,并通过这些索引做各种优化。

但是,索引并不是万能的,需要根据实际情况去创建索引:

1)如果数据重复度高,就不必创建索引。通常重复度超过10%的情况下,可以不创建这个字段的索引,比如性别字段,取值只有男和女。

2)要注意索引列的位置对索引使用的影响。比如在WHERE子句中对索引字段进行了表达式的计算,会造成这个字段的索引失效。

3)要注意联合索引对索引使用的影响。在创建联合索引的时候会对多个字段创建索引,此时索引的顺序很关键。比如对字段x、y、z创建了索引,那么顺序是(x、y、z)还是(z、y、x),在执行时就会有差别。

4)要注意多个索引对索引使用的问题。索引并非多多益善,因为每个索引都需要存储空间。此外,过多的索引也会导致优化器在执行评估的时候增加筛选出索引的计算时间,影响评估的效率。

查询优化器对SQL语句进行等价变换之后,还需要根据数据表的索引情况和数据情况确定访问路径,这就决定了SQL执行所需要消耗的资源。SQL查询时需要对不同的数据表进行查询,因此在物理查询优化阶段也需要确定这些查询采用的路径,具体情况包括:

4.1)单表扫描:对于单表查询来说,可以全表扫描,也可以局部扫描。

4.2)两张表的连接:常用连接方式包括嵌套循环连接、HASH连接和合并连接。

4.3)多张表的连接:多表连接,顺序很关键,不同的连接路径,查询效率和搜索空间不同。多表连接时,搜索空间可能会达到很高的数量级,这样无疑会占用更多资源,因此需要调整连接顺序,将搜索空间控制在可接受的范围内。

物理查询优化是在逻辑查询优化后采用的物理优化技术,通过计算代价模型对各种可能的访问路径进行估算,找到执行方式中代价最小的作为执行计划。在这部分,需要重点掌握索引的创建和使用。

第五步,使用Redis或Memcached作为缓存

使用Redis或Memcached这种操作属于请求外援的查询方式,用以提升查询效率。

因为数据都是存储在数据库中,需要从数据库中取出数据进行业务逻辑操作,当用户数激增、频繁进行数据查询时,会消耗数据库很多资源。如果将数据存放到内存中,查询效率会大幅提升。

键值存储数据库可以解决这个问题,常用的有Redis和Memcached,它们可以将数据存储到内存中。

从可靠性来说,Redis支持持久化,将数据保存到硬盘上,但性能消耗也会比较大,Memcached仅仅是内存存储,不支持持久化。

从支持的数据类型说,Redis比Memcached多。不仅仅支持key-value类型的数据,还支持List、Set、Hash等数据结构。

因此当有持久化需求或更高级数据处理需求的时候,可以采用Redis,如果仅仅是简单的key-value存储则可以采用Memcached。

通常对于查询响应要求高的场景(响应时间段、吞吐量大)可以考虑采用内存存储,查询效率高。

第六步,库级优化

库级优化是站在数据库的维度进行的优化策略。比如控制一个库中数据表的数量。另外可以采用主从架构优化读写策略,如分库分表、读写分离等。

如果读和写的业务量都很大,并且它们都在同一个数据库服务器中操作,则数据库的性能会出现瓶颈,此时为了提升系统性能、优化用户体验,可以采用读写分离的方式降低主数据库的负载。比如用主数据库(master)完成写操作,用从数据库(slave)完成读操作。

此外,还可以对数据库分库分表。当数据量达到亿级以上时,需要把数据库进行切分,放到不同数据库服务器上,减少对单一数据库服务器的压力。如果使用的是MySQL,可以使用MySQL自带的分区表功能,也可以考虑自己做垂直切分或水平切分。如何确定使用垂直还是水平呢?

如果数据库中的数据表过多,可以采用垂直分库的方式,将关联的数据表部署在同一个数据库服务器上;如果数据表中的列过多,可以采用垂直分表的方式,将数据表拆分成多张表,把经常一起使用的列放到同一张表中。

如果数据表中的数据达到了亿级以上,可以考虑水平切分,将大的数据表拆分成多个字表,每张表采用相同的表结构。比如按年份划分,把不同年份的数放到不同的数据表中。

简单说,垂直分表就是将一张表拆成多张表,水平切分就是将单张数据量大的表按照某个属性维度分成多个表结构相同的小表。

注意:分拆在提升数据库性能的同时,也增加了维护成本和使用成本。

如何思考和分析数据库调优这事儿?

数据库调优中,目标就是响应时间更短、吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮助我们确定调优思路和方式,不过综合以上分析,可以从三个维度考虑:

1)选择适合的DBMS和数据表设计方式。

2)采用逻辑查询优化和物理查询优化。

逻辑查询优化就是通过SQL等价变换提升查询效率。物理查询优化则是通过索引和表连接方式等技术进行优化。

3)请求外援增强数据库性能。

单一数据库总会遇到各种限制,利用外援则可以取长补短。此外还可以考虑对数据库进行垂直或水平切分,突破单一数据库或数据表的访问限制,提升查询性能。

(本文完)

  • 我的微信
  • 微信扫一扫
  • weinxin
  • 微信公众号
  • 微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: