↓跳到主要内容
  1. Skills/
  2. thinking in SQL | 废话SQL，思维与实践/

浅谈 数据库应用与 SQL 调优的原理

··字数 4888·10 分钟
sql 实践 有悟方法 sql 优化
目录

若你没有太多的编程经验，刚入门 SQL，能正确写出需要的 SQL 查询就已经相当不错了。但 SQL 性能是一个无法回避的问题。当前未涉及到，只因数据量较小，或者服务器太强大。有人认为，使用大数据来代替传统关系型数据库，就可以不考虑性能问题了，不妥的。

什么是优化 #

这个问题，如同拿十五年前的桌面台式奔腾与当下性能不是最强的超级本比较一样。相同的程序都在性能差别很大的硬件上，当然表现很不一样。以前的硬件较落后（当然是与现在相比，我们要历史、辩证、客观🤔），CPU主频低，内存容量小且价格昂贵，所以，如何让程序稳定运行不要占用太多资源（也是这个原因，导致有悟一直对 java 桌面程序非常排斥、恐惧）、轻巧，及时回收垃圾等等，都是编程中的重点。如果没有硬件上的发展，当今 javascript 也就不能大肆其道，也就不会丰富绚丽的移动端界面。资源总是有被用完的时候，不是硬件先进了就不用考虑，而是在经济效益成本下，它有时并不是最优先需要解决的。

SQL 最终是被数据库的 SQL 引擎编译成内部字节码来执行的，它也遵循计算机程序的基本原理。

所有的优化，都可归纳为

优化
优化

因此，优化是有前提条件、有目标的在效率、资源、质量之间寻求平衡。

什么都别做，那就等于没什么需要优化。这就是最好的优化。😎

数据库应用如何优化 #

数据库优化概述 #

回到 SQL 优化的话题来。SQL 优化仅是数据库优化的一个方面，数据库服务本身是一个或者一组复杂的操作系统进程。

数据库优化体系
数据库优化体系

数据库优化涉及到多个方面：

  • 系统级别的架构设计
  • 硬件资源、操作系统等基础设施
  • 数据库实例的运行特性
  • 数据结构
  • SQL查询、过程、函数等应用层面的程序效率

越往底层，成本越高，见效越低，这往往是系统在设计阶段就规划下来的。而越往应用层次，灵活性越大。 其中：

  1. 架构设计优化是指针对实际所建的、包含数据库系统的总体架构性规划。一个系统建设的前期，这方面就已经决定下了来。如果涉及到架构优化，那往往就是更换数据库产品；
  2. 基础设施方面，通常可以通过提高硬件配置，为数据库服务运行提供更宽松的资源环境；
  3. 典型的数据库应用场景为在线事件操作（OLTP）、在线分析（OLAP）两种。它们对数据库的表现非常不同，OLTP 要求数据库能够更快速的反馈，适用于在线业务系统。OLAP 则要求数据库有更大的数据处理能力，适用于分析型系统。目前还没有出现一款能够完全通吃两种场景的数据库产品；
  4. 数据结构分为数据库内置实现与应用层次的用户自定义。数据库内置的实现从用户端无法更改的（难度太高），往往是产品升级。而应用层次的，比如表，用户可以根据自身需求来调整；
  5. SQL 查询、过程、函数这些用户自定义的逻辑过程，调优灵活性非常高，用户可以根据对数据库原理的熟悉程度、业务场景需要来选择合适的实现。

SQL 简要运行模型 #

SQL 简要运行模型
SQL 简要运行模型

在确定对 SQL 优化手段之前，先看了解 SQL 运行的基本原理。只有对这个过程有深刻认识，才能明白 SQL 调优策略的本质，做到举一反三。

本文并不探究数据库内部组件的微观世界，只为理解 “SQL 优化” 而补充部分基础知识。

保存在数据库中的数据，实际是保存在某种特定的文件系统上，这些特定的文件系统有自身的数据格式，它会影响实际数据最终数据被数据库读取到。

一个 SQL 查询的运行，大概是这样的：

  1. 数据库通过 SQL 解析器分析该 SQL，如果语法错误，那么就终止执行；

  2. 若能通过解析器，则被编译成数据库内部的代码（这些代码并不是最终的机器码，有点类似于JAVA的字节码，不同的数据库这些代码是不同的）

  3. 数据库的 SQL 执行器运行内部代码，读取、计算、保存数据。

    1. 若需要返回查询结果的，将内存中的结果集返回给客户端
    2. 若是数据修改、删除操作，数据库需要找到表对应的存储位置，对数据行进行修改、删除，持久化
    3. 若是数据新增操作，数据库找到为表分配的位置，将新数据写到可用空间，持久化

  5. 对于数据变更操作（增、删、改），数据库会使用日志技术，来保证数据的一致性

其中，数据库是如何读取到对应表的数据呢？通过保存到数据库中的数据字典，可确定所需要的表、段的位置（哪个主机、哪个存储器、哪个数据目录等），在获取数据位置后，再从该位置读取真实数据缓存在内存中。数据库中的数据字典是数据库级别的表、视图、返回查询结果集的函数或者过程。这些数据字典还有另外一个名称，叫元数据，描述数据的数据。没错，hadoop 也有一个充当元数据管理的部件。

有些数据库有表空间的概念，它实际起到的作用是，让用户自己定义表数据的逻辑位置，表空间下对应实际的存储设备。应用程序不直接对应硬件，而是由数据库自动管理。一个表的数据由一连串的数据段组成，这些数据段由数据库按照一定的规则在表空间上分配空间。

实际上，上述这个过程是非常复杂的。数据库需要使用多种手段去保证这个过程的数据一致性、运行效率。

🤔 带着下面这些数据库的微观问题，去学习数据库内部原理。本文不会给出答案。

  1. 如果一个查询重复执行比较频繁，那么数据库要提高整个过程效率，有哪些步骤是可以提前优化的？
  2. 往数据库添加数据时，数据表的数据段会不会发生变化，如何变化？
  3. 数据读取时，是否一性次完成了所有数据的读取？如果不是，那么数据库内部如何解决？
  4. select ... from 表名 在不排序的情况下，结果集的数据行顺序也是固定的吗？
  5. 数据行在修改、删除是否只是简单的覆盖、擦除变更的数据。频繁更新会出现什么情况？不同的数据类型可能出现什么情况？

数据库应用优化 #

有了对数据库内部原理、SQL运行机理的基本认识，在应用层面，大概具备了优化时需要平衡的具体对象。

SQL 的解析
SQL 解析是 CPU 密集型计算。在对延时高度敏感的系统中，毫秒必争。语句长短、复杂程度都会影响数据库具体的用时，在一些情况下，这些SQL是在使用时才动态解析的。
数据读、写
由 SQL 运行过程可知，数据总是从存储上被读取并复制到内存。从存储上读取数据、比CPU复制到内存或从内存读取数据要慢好多个级别。而读、写操作也有不同的操作频繁度、时间复杂度。也就是大多人常讲的IO问题。
应用设计：数据结构、数据类型
结合应用的需求来设计表结构、使用正确的数据类型。
数据的物理存储规划
结合实际应用需要，在表物理化时，设计适当的物理化参数，如分区、表空间、数据文件、数据段、数据块。
SQL逻辑
SQL逻辑是数据需求的描述，也是一种实现。可能存在另一个结果等效的优化实现。
SQL执行步骤的干预
在上面的运行中，隐藏了一个环节，SQL 语句在被编译成内部码的过程，是可以观察或干预的。

在实际的实践中，IO往往是常见的瓶颈。对于 OLTP 型的场景，首要考虑的是如何更快的返回结果、应对更多的同时访问的并发处理能力；对于 OLAP 型的场景，首先考虑的是一次性读取更多同一批数据、逻辑相邻的数据物理在连续存放。

常见的数据库应用优化手段 #

SQL 解析 #

SQL解析是所有 SQL 语句执行前的必要步骤。对于应用系统中执行次数较多、较频繁的语句，数据库提供了绑定变量的方法来缓存前一次 SQL 解析的结果，从而减少 SQL语句解析带来的 CPU 消耗。

这方面的优化在分析型的数据库系统中比较少考虑。

数据读、写 #

数据读、写是数据库底层的实现，我们只能通过表的物理设计、调整引发读写操作的SQL来改变读写性能表现。 对于一些在线的高并发库，读、写分离，分库分表的方式，来避免数据竞争。对相同表不同数据行进行人为隔离（如表分区，或者分到不同的数据库实例）

🤔 问题:

什么是数据竞争？

应用设计：表 #

表逻辑设计应该遵照三范式标准来设计。在数据分析系统中，大多数数据质量问题、都是由于业务系统（数据生产者）的表设计问题所造成的，而且带来的附加成本非常高）。

但有时为了减少表间关联的次数，使用反范式设计，减少了关联表，或者附加属性表。在这分析型数据系统非常常见。即大宽表。

在业务应用系统，还是尽量使用三范式，它对提升数据质量有非常大的帮助。

应用设计：数据类型 #

使用更节省空间、计算效率更高的等效数据类型。

比如表的主键ID，可能是字符型、数字型、UUID等。这些类型在比较时，性能表现是不一样的。 数字型的数据，在计算时（四则运算或比较大小）有天生的速度优势。那么在一场场景下，就可以考虑使用数字型来替代字符型作为表主键的数据类型。

物理化设计优化 #

数据库一般提供了这些物理化手段：

  • 主键、外键、唯一键索引，这些约束都可以通过索引来加快查询与关联。
  • 索引（带排序）
  • 分区表，对于记录数较多的表，可以使分区表来隔离数据读、写，提高IO效率。若不能满足，就可能需要考虑更复杂的分库分区方案。
  • 表空间，为表分区分区不同的表空间，这样在底层物理上的IO才是真正隔离的。
  • 分区索引，索引本质就是一个数据表，当表记录数较多，意味着索引也会比较大。这时可以使用分区索引的方式来平衡索引数据的读、写。
  • 如果有必要，为索引提供专用的索引表空间。这样在同时读取表中数据与索引数据时，数据库底层IO是分散的。
  • 评估表所定义的约束是否必要物理化，这些约束都会在每一行数据改变时触发约束检查，是会消耗资源的。

⚠️ 上面所提到的索引，它在某种程度不仅是加载了数据行的定位，若某个索引的字段完全包含了查询列表中所需的所有字段，那该查询会转换为对该索引的查询。

虽然索引有好处，但索引不是越多越好。因为索引本身也是一张数据表，它会占用数据存储，数据表中的数据发生变化时，它也会联动变化。

SQL 逻辑 #

在优化 SQL 时，首先检查 SQL 的业务逻辑是否有简化的实现。因为初次完成的时候，大多数情况下，第一目标是保证正确性，而忽略优化问题。可能存在更优的简单实现。

SQL 执行 #

数据库将 SQL 编译为内部码后在 SQL 的执行器中执行，这些内部码有一种可视化形式，叫 “执行计划”。它可以通过 explain select ... 或者 select ... for explain 的语句来获得。

执行计划是一个嵌套式的树型结构，从执行计划上可以看到 SQL 查询语句是按什么步骤来执行的。

explain
select *
from   v_customer_list;

例中的 v_customer_list 是 sakila - DVD 租赁示例数据库 中的一个视图。postgres 的执行计划如下:

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Hash Left Join  (cost=41.52..63.30 rows=599 width=123)
   Hash Cond: (city.country_id = cont.country_id)
   ->  Hash Left Join  (cost=38.07..55.22 rows=599 width=66)
         Hash Cond: (addr.city_id = city.city_id)
         ->  Hash Left Join  (cost=20.57..36.14 rows=599 width=57)
               Hash Cond: (cust.address_id = addr.address_id)
               ->  Seq Scan on customer cust  (cost=0.00..13.99 rows=599 width=20)
               ->  Hash  (cost=13.03..13.03 rows=603 width=41)
                     ->  Seq Scan on address addr  (cost=0.00..13.03 rows=603 width=41)
         ->  Hash  (cost=10.00..10.00 rows=600 width=13)
               ->  Seq Scan on city  (cost=0.00..10.00 rows=600 width=13)
   ->  Hash  (cost=2.09..2.09 rows=109 width=11)
         ->  Seq Scan on country cont  (cost=0.00..2.09 rows=109 width=11)
(13 rows)

执行计划的作用:

  • 在于让我们可以透视 SQL 执行的过程
  • 确认优化是否起作用
  • 还可以人为干预并改变执行计划

在执行计划中，有几个重要的操作动作或测度的概念，这此概念是数据库内部的实现：

* 联接方式:
	- hash join: 哈希联接
	- merge join: 合并联接
	- nested loop join: 嵌套联接，或循环联接
* 数据访问方式:
	- full scan: 全表扫描
	- index scan: 索引扫描
	- 分区:
* 并行度:
hash join
哈希联接，使用哈希函数来关联数据。需要返回数据行比较多时使用
merge join
与 hash join 适用于类似的场景，只是关联时数据的排序、方法不同。
nested loop join
嵌套联接，或者循环联接。顾名思义，从主表逐行到联表中查找匹配数据行。类似于 excel 里的 vlookup 函数
full scan （全表扫描）
读取全表数据后，确定对应的数据行。若在目标数据行非常少的时候，表数据量较大的情况下，这种方式非常低效。可以通过添加索引来加快目标数据行定位
index scan（索引扫描）
通过索引来定位目标数据行，是确定是否所设计的索引是否被优化器采用的重要观察手段。
分区
查询数据访问方式。如果是分区表，可用来观测数据分区访问的情况
并行度
对于分区、或者某些可以部分并行计算的步骤，数据库是否启动了并行功能。并行功能可能是一种数据库的商业收费特性，如 oracle数据库，需要在付费后才能使用。

数据库提供了一种手段来干预执行计划。通常是在 SQL 语句的某个固定位置插入专用的注释，这种注释具有特定的格式规则，就像在 SQL 执行器墨盒打开一个可以插入选项的开关。

这种特定格式的注释叫 sql hint，如在 oracle 上，hint 写在 select 关键字之后。关于 sql hint 的用法，参见看数据库的相关文档。

select /*+  parallel(8) */  *
from   ...

其它 #

如数据库有日志来保证数据的一致性。对每一项数据的变量（增、删、改）都会产生日志。如果数据变量操作是发生在数据迁移过程，往往这种一致是由人为来保证就可以了。先关闭日志，加载数据完毕后，再打开日志功能。不仅减少所产生的日志数据，还加快了数据迁移过程。这就是大批量数据导入时，要使用数据库的文本加载工具，而不使用标准的 insert ... values(...) sql 语句原因，它是由数据库提供了一种绕过日志模块的方法。

在加载大批量的时候，先将索引失效，在数据加载结果后，再重建索引，也是这种原理。

↑