51Testing软件测试论坛

标题: 关系型数据库查询优化浅析与实践 [打印本页]
作者: lsekfe    时间: 2021-1-8 10:25
标题: 关系型数据库查询优化浅析与实践
　 1通用数据库分类
　　1.1关系型数据库
　　关系型数据库是多个二维数据表的集合，数据以二维数据表的形式进行存储，数据表之间可以通过应用程序或者数据的主、外键建立特定的关联关系，让数据之间存在特定的应用联系。他的优点主要是数据访问简便，通过结构化查询语言SQL进行单一数据表或多数据表的联合查询、修改和删除操作。相较于非关系型数据库，对于大规模的数据的读写操作效率不高。
　　1.2非关系型数据库
　　非关系型数据库是各种存储方式的集合，存储形式包括图像、文本等，它不以关系型数据表的形式储存。他可以使用专用的硬件设备提升大规模数据的读写效率，通常被用作分析性场景。
　　2常用关系型数据库查询过程原理
　　对于关系型数据库来说，不管是MYSQL、ORACLE还是DB2其数据库的查询过程通常都分为5个步骤，以DB2为例其包括语法和语义检查、选择最优访问路径、生成数据库可识别的执行代码、按SQL命令进行数据操作等。下面以DB2为例对上述过程进行详细的介绍。
　　1)Precompile
　　Precompile是DB2的预编译，由于大部分的编译器不能识别SQL 语句，需要在执行编译（COMPILE）之前使用DB2的预编译器防止编译器在编译过程中出错。其中数据库会将程序分离成2部分。一部分是数据库从应用程序中剥离出的应用逻辑部分，我们称之为LOAD MODULE，该文件用作后续的COMPILE；另一部分是SQL语句，我们称之为DBRM，这部分就是我们执行数据库操作的代码。LOAD MODULE与DBMR通过token进行关联，保证两部分内容在应用执行时可以完美的匹配。
　　2)cics translation
　　该部分数据库会对cics相关的执行进行解析和转义，以保证在编译阶段数据库对cics相关指令可以识别
　　3)Compile
　　该部分数据库编译器会分析源程序并将源码翻译成机器指令，SQL的语义与语法检查都是通过该部分进行。
　　4)访问路径选择
　　该部分是数据库对sql语句的访问路径进行择优选择，该部分通过数据库的optimizer完成，实际工作会在Compile阶段一并完成。
　　3优化器最优访问路径选取原则
　　以DB2为例，数据库通过optimizer进行访问路径的选择，通常我们所说的sql语句优化大部分都是对查询语句的优化。数据库在执行一条sql语句的时候可以通过多条道路实现一条数据的读取功能，比如对于同一条记录的查询，数据库可以通过索引进行数据的筛查，我们称之为index scan 或者index only。对于多索引的数据表来说，针对不同的索引，访问路径又有多条道路可以选择。又或者数据库也可不使用索引而是通过全表扫描的方式进行数据读取。那么最终数据库会选择何种路径进行数据读取操作呢？这个就是最优访问路径的选择问题。
　　数据库优化器最优访问路径选取的方法并不复杂，其内部有一套标准的算法，首先数据库会将所有可获取到需求数据的访问路径进行读取成本的估算。并对所有路径估算出的开销进行比较，选择开销最小的作为这一条数据访问语句的最优访问路径以达到提升访问效率的目的。
　　影响访问路径选择的因素有很多，包括数据表的索引建立方式，如唯一索引、主键、分区索引、非分区索引、cluster index和非cluster index等等。同时数据表数据的摆布方式，包括数据表键值的重复率等等也对访问路径的选择起到很大的作用，甚至执行sql语句数据库所在的硬件设备容量也在访问路径选择的考量之中。
　　数据库最优访问路径的选取实际上就是访问开销最小的访问路径的选取，目的是降低sql语句执行时候的cpu和磁盘I/O的消耗，提升访问效率。
[attach]131832[/attach]
4高效查询语句书写原则
　　4.1避免不恰当的使用“SELECT *”
　　如果 SQL 语句使用了“SELECT *”，DB2 就需要把表的所有列都进行处理并且返回给用户，这显然会增加 I/O 和 CPU 的开销。如果这条 SQL 语句还包括了排序（Sort）操作（比如 ORDER BY），那么对全部这些列进行排序也会影响到性能。而且当表定义的列越多，每个列定义的数据类型（Data type）长度越长，这对性能的影响就可能越明显。
　　4.2避免在本地谓词的列上使用复杂表达式
　　本地谓词（Local predicate）是与连接谓词（Join predicate）相对应，它一般是指该谓词当中只包含一个表上的一个列。YEAR(Date) > 2004 和 Amount > 1000 都是两个本地谓词。然而在前一个谓词 YEAR(Date) > 2004 中，它对 Date 这个列有一个函数 YEAR 的调用。在这种情况下，即使 Date 上存在一个索引，DB2 也无法使用这个索引来访问数据。如果能够在确保语义不变的前提下，适当改写这个谓词，避免在 Date 列上调用函数，那么情况可能会有所不同。例如，这个谓词可以改写为如下的样子：
  1. Date > ‘ 2004-12-31 ’
复制代码
下面来看一个具体的示例：
  1.   SQL 1：select l_quantity, l_comment from lineitem
  2. 　　where l_orderkey + 100 = 200
  3. 　　SQL 2：select l_quantity, l_comment from lineitem
  4. 　　where l_orderkey = 100
复制代码
SQL 1 的访问路径图
[attach]131833[/attach]
SQL 2 的访问路径图
[attach]131834[/attach]
4.3避免用复杂表达式构建连接谓词
　　让数据库可以有多种访问路径的选择，避免由于人为编写不恰当的SQL语句限制优化器的选择。
　　T1.C1=T2.C2 就是一个典型的连接谓词，这种写法也是常见的连接谓词的形式。对于这种常见的连接谓词，DB2 可以考虑采用几种不同的表连接方式（Join Method），常见的连接方法有嵌套循环连接（Nested-Loop-Join, NLJ），归并排序连接（Merge-Scan-Join, MSJoin），哈希连接（Hash-Join）等。DB2 优化器会根据实际情况选择从中选取一个性能最佳的来将 T1 和 T2 连接起来。
　　但是在连接谓词 T1.C1 * T1.C2 = T2.C2 中，“＝”左边不是一个列名，而是一个表达式，它涉及 T1 表上不同列之间的计算。对于这样一个用复杂表达式构建的连接谓词，DB2 只能用 Nested-Loop-Join 这种最基本的方式来建立 T1 和 T2 之间的连接，而不考虑用其他的连接方式，从而也就无法选择最优的连接方式。
　　看下面这个示例：
  1. SQL 1：
  2. 　　SELECT l_comment, o_comment FROM lineitem, order
  3. 　　WHERE l_orderkey = o_orderkey + 100
  4. 　　SQL 2：
  5. 　　SELECT l_comment, o_comment FROM lineitem, order
  6. 　　WHERE l_orderkey = o_orderkey
复制代码
SQL 1 的访问路径图
[attach]131835[/attach]
SQL 2 的访问路径图
[attach]131836[/attach]
如果想在满足业务逻辑需求的情况下，同时保证连接谓词的简洁，也可以考虑增加一个新的列（例如 SQL 1 中，定义新的列 o_orderkey2，其值等于 o_orderkey + 100），直接构造连接谓词（l_orderkey = o_orderkey2），从而最大程度的提高 SQL 语句的性能。
　　4.4避免连接谓词中的数据类型不一致
　　在用连接谓词连接不同的表的时候，即使对于 T1.C1=T2.C1 这样典型的连接谓词，也应该确保 T1.C1 和 T2.C1 具有同样的数据类型。连接谓词中两个列的数据类型定义的不一致会导致 DB2 放弃使用某些表连接方式。比如 Hash-Join 这种表连接方式对连接谓词就有更多的限制条件，条件之一就是连接谓词中的两个列的数据类型必须完全一致，否则 Hash-Join 不能使用。此外，如果 T1.C1 的数据类型是 CHAR，GRAPHIC，DECIMAL 或者 DECFLOAT，那么 T2.C1 除了需要是相同的数据类型外，它所定义的数据类型的长度也需要和 T1.C1 一致。更多的表连接方式意味着 DB2 可以有更多的选择来将表连接在一起，并从中选出最优的方案。
　　看下面这个示例：
  1. SQL 1： SELECT l_comment, o_comment FROM lineitem, order
  2. 　　WHERE l_orderkey = o_orderkey
复制代码
对于 SQL 1，DB2 优化器采用了归并排序 (MSJoin) 的连接方法对两个数据表进行了连接操作 ，这里连接谓词 l_orderkey = o_orderkey 中的 2 个列的数据类型完全一致都为 integer 类型。如果改动其中一个列的数据类型为 double 类型，此时 DB2 就只能采用嵌套循环连接方法进行连接操作，而对比之后就会发现，使用嵌套循环连接的 Total Cost 较高，这意味着性能较差。
　　INTEGER = INTEGERINTEGER= DOUBLE
[attach]131837[/attach]
[attach]131838[/attach]
4.5确保在连接谓词中使用等号
　　典型的连接谓词通常是形如 T1.C1=T2.C1 这样的形式，注意到这里是用“=”这个操作符将左右两边的列连接起来。理论上，也可以使用其他的操作符来构造连接谓词，比如“<”或者“>”这样的比较运算符。但是实际上基于性能的考虑，在连接谓词中应该只使用“=”，尽量避免使用其他的比较运算符。
　　在连接谓词 T1.C1 < T2.C1 中，使用了“<”这个比较运算符。 对于这样的 SQL 语句，DB2 只能采用 Nested-Loop-Join 这种最基本的方式来建立 T1 和 T2 之间的连接，而不考虑用其他的连接方式。在某些实际的应用场景当中，出于业务逻辑上的要求，出现 T1.C1 < T2.C1 这样的连接谓词可能是不可避免的。在这种情况下，基于性能优化的考虑，应该在 T1 和 T2 上都建立适当的索引，使得 T1.C1 < T2.C1 这个谓词能够使用索引。其中的原因在于，DB2 只能使用 Nested-Loop-Join 来建立 T1 和 T2 之间的连接，此时应该确保有合适使用的索引能够让 Nested-Loop-Join 采用 Index-Scan 这种数据访问方法，从而尽可能提高性能。
　　看下面的示例，
  1.   SQL 1： SELECT l_comment, o_comment FROM lineitem, order
  2. 　　WHERE l_oderkey >o_orderkey
  3. 　　SQL 2： SELECT l_comment, o_comment FROM lineitem, order
  4. 　　WHERE l_oderkey =o_orderkey
复制代码
　在 SQL 1 中连接谓词是通过大于号连接的，DB2 只能采用嵌套循环连接 (Nested-Loop-Join) 这种最基本的方式来建立两个表之间的连接。在 SQL2 中连接谓词中采用“=”连接，此时 DB2 优化器选用了归并排序（MSJoin）的连接方式，它的 Total cost 比 SQL 1 的要低很多，具有较好的性能。注意这里 SQL 1与 SQL 2 在语义上是不等价的，在这里用这样的示例是为了说明连接谓词中不使用等号的写法会导致访问路径完全不同，从而影响查询性能。
　　SQL 1 的访问路径图
[attach]131839[/attach]
SQL 2 的访问路径图
[attach]131840[/attach]
4.6恰当安排 Group By 子句中列的顺序
　　通常情况下，SQL 语句中的 GROUP BY 子句会导致数据库不得不通过一个排序（SORT）操作来实现对数据的分组，而排序被认为是一个比较耗费 CPU 和内存的操作。实际上某些情况下，如果写法得当，当中的排序操作是可以避免的。具体来说，在写 GROUP BY 子句的时候，应该考虑到数据库中已经存在的索引的情况。如果 GROUP BY 子句中所有的列恰好包括在某个索引的键（Key column）的范围之内而且是处于开始的位置，那么在写 GROUP BY 子句的时候，就应该按照该索引上键的先后顺序来写 GROUP BY 子句。
　　比如说有如下的 SQL 语句：
  1. SELECT C2, C3, C1, AVG(C4)
  2. 　　FROM T1
  3. 　　GROUP BY C2, C3, C1
复制代码
　一般情况下，GROUP BY C2, C3, C1这样的写法都会导致数据库的一个排序操作。但假定表 T1 上已经存在一个索引 IX1(C1, C2, C3, C4), 这里注意到 GROUP BY 子句中引用到的列（C2，C3，C1）正好是索引 IX1 中的前三个键，那么就可以通过改变 GROUP BY 子句中列的顺序的办法来避免这个排序操作。
　　可以把 SQL 语句改写为如下所示：
  1. SELECT C1, C2, C3, AVG(C4)
  2. 　　FROM T1
  3. 　　GROUP BY C1, C2, C3
复制代码
通过这样改变 GROUP BY 子句中列的顺序使其与索引 IX1 中的键顺序一致，数据库就可以利用 IX1 来访问其已经排序的键值并直接返回进行下一步操作，从而避免额外的排序操作，从而带来查询性能上的提高。
　　需要指出的是，通过这样改写 GROUP BY 子句来避免排序，可能会导致最终返回结果的顺序不一致。在实际的业务逻辑当中，需要用户来确认是否其关注返回结果的顺序性。
　　下面来看一个具体的示例：
  1.    SQL 1：SELECT AVG(o_shippriority) FROM order
  2. 　　GROUP BY o_custkey , o_orderkey, o_orderdate
  3. 　　SQL 2：SELECT AVG(o_shippriority) FROM order
  4. 　　GROUP BY o_orderkey, o_orderdate, o_custkey
复制代码
这里 2 个 SQL 唯一的差别就在于 GROUP BY 子句中列的顺序不同。假定order 表上存在一个索引 PXO@OKODCKSPOP (O_ORDERKEY,O_ORDERDATE,O_CUSTKEY,O_SHIPPRIORITY,O_ORDERPRIORITY)。
　　由于 SQL 1 中的 GROUP BY 子句的列顺序与索引 PXO@OKODCKSPOP 的键顺序不一致，DB2 无法直接利用这个索引，所以 DB2 需要基于这 3 个列做一次排序（Sort），然后进行分组合并，排序的结果还需要通过临时文件（Wkfile）来保存。如果调整 GROUP BY 子句中的列顺序如 SQL 2 所示，使其与索引 PXO@OKODCKSPOP 的键顺序一致，DB2 通过这个索引返回的结果就已经是有序的，这样就省去了排序操作。对比两者的访问路径图可以看出来，SQL 2 所花费的成本（Total Cost）会少很多，性能上有较大的提高。
　　SQL 1 的访问路径图
[attach]131841[/attach]
SQL 2 的访问路径图
[attach]131842[/attach]
4.7活用FETCH FIRST N ROWS ONLY字句
　　在 DB2 的 SQL 语法中，FETCH FIRST n ROWS ONLY 表示只取回结果集当中的前 n 条记录，在应用中经常会碰到判断满足条件的数据是否存在这样的逻辑，为达到目的有些程序员会用count（*）的结果来作为判断依据，众所周知count（*）的性能是不高的，对于此类场景可以考虑用FETCH FIRST 1 ROWS ONLY替代。
　　4.8多用indexable 谓词避免使用Non-indexable谓词
　　indexable的布尔类型谓词才能成为用来匹配索引的谓词，而Non-indexable的谓词是不能用来匹配索引的，在谓词设计中，首先需要应用的基本原则是尽量避免使用Non-indexable 谓词。
　　Non-indexable谓词包括：
　　1）谓词左边的列在表达式或函数中 DATE + 10 < CURRENTDATE，        LENGTH(C1) = 4
　　2）与字段比较的对象是不同类型的值或变量，如变量定义与字段类型不符。
　　3）字段之间的比较   C1 < C2
　　4）BETWEEN col1 AND col2
　　5）非谓词，即包含NOT，NOT NULL，NOT LIKE，NOT IN，NOT BETWEEN，<>
　　6）LIKE谓词使用时将通配符放在最前面。C1 LIKE ‘%ABC’
　　4.9谓次匹配尽量多的索引字段
　　一个查询语句谓词与索引的key column匹配越多，过滤率越高，查询的效率也越好。
　　4.10谓词中加入分区表分区键值
　　对于分区表而言，尽可能在SQL语句中加入分区键值列作为谓词，将查询范围限定在某一个或某几个分区中，避免整表扫描，可以极大地减少I/O开销。
　　5总结
　　本文主要介绍了当下使用最广泛的两类数据库（关系型数据库及非关系型数据库）的区别和各自优势进行了简要阐述。从农行核心应用实际出发，重点从开发人员角度，对关系型数据库查询语句的书写与数据库访问性能的关系进行了阐述，通过实例给出了高效SQL语句的书写方法及背后的技术原理。










作者: Miss_love    时间: 2021-1-8 13:38
支持分享



欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/) Powered by Discuz! X3.2