一、配置

首先有一点必须要明确，数据库服务器配置越强，肯定跑的越快。最影响数据库性能的肯定是硬盘，因为数据库的可靠性是靠写入磁盘实现的，由于短板效应，硬盘这个计算机最慢的东西决定了性能。SSD 是高性能最不可缺少的一部分，而且对于 SSD 来说，容量越大，速度越快。对于云主机来说则有一个 IOPS 的指标，在多租户的环境下，云服务提供商提供的虚拟化设备对单机的 IOPS 有固定的限制，会影响性能（当然包括超售）。

第二就是内存，内存影响缓存的大小，假如数据都缓存在内存中，那数据库查询速度肯定是和 Redis 之流是一样的。而且我们使用 Redis 这些内存 kv 数据库的最主要原因并不是速度快，而是分散主数据库的带宽和连接数压力，让主数据库有更多的资源服务于事务写的业务。

第三是网卡，我司数据库之前一直都是千兆网卡，速度即 125MB/s，这个速度其实不是很快，仅略高于普通硬盘的读取速度，而且有一部分带宽还要用于数据库之间的主从同步（当然数据库服务器不止一块网卡，比如我司数据库服务器插了4张网卡，2张冗余对外提供服务，另外2张用于数据库之间的内网同步）。虽然业务中好像没遇到过把数据库带宽用满的情况，当然前中台业务中也不允许出现那种读取大量数据把数据库带宽用满的业务场景，但是后台清算业务是有这种场景的，带宽越大，也能提高大批量数据的拉取速度。

二、设计

（一）、主键

首先需要讨论表的主键选择。一般来说，特别是 MySql 会要求主键使用自增数字，但是我觉得这是一种陈旧的思路。当在单机环境下，这是可以理解的手段，但是当业务量达到一定量级后，自增数字会成为分库分表的阻碍。

然而排除自增数字后，第二选择则是UUID/GUID，UUID 有128位，通常使用16进制表示，用’-‘间隔。根据 RFC4122，UUID 有5个版本，版本1、3、5中有60位是基于时间，版本4是全随机数。Java 和 .net 默认使用版本4实现，但 Oracle 11G中使用的是版本1，MySql 5.6中也用的版本1。

用 UUID 的缺点很明显，就是128bit 太大了，假如存储成 varchar或char，那占用更多，128字节，这会导致索引占用空间较大。还有就是假如使用版本4，数据随机分布，会导致 SqlServer 和 MySql（聚簇索引） 把连续插入的数据存储到分散的地方，然而对于 Oracle 来说，每条数据都有一个内部自增的 RowId，数据依旧存储在同一个段内。

对于这个问题，其实程序员也有办法，就产生了一个分区ID生产需求，诞生了一些ID 生成服务。比如说美团的ID生成服务——Leaf。但是Leaf这个项目其实也没有绕开 UUID 的逻辑（见下图），他的前几位依旧使用的时间，这个设计让 MySql 可以顺序的存下数据。

当然我还是推崇使用 UUID 作为主键的做法。我的自信来自于 google 云上 spanner数据库的设计建议指南。spanner 是一个分布式数据库，通过对主键进行自动分片，让数据分散在不同节点上，相似的主键同时插入会让单节点过热，这是 spanner 要求应用重新设计主键，其中 UUID 是推荐的一个选择。

在我司的应用中，主键大部分选择的是 UUID。这其实还带来一个优势，比如说我们的用户 ID 是 UUID，每天都要对全部用户进行资产清算，用 UUID 可以均匀对全部用户进行切片，例如首字母00-01的用户由 A 服务器的资产清算服务处理，02-03的由 B 服务器的资产清算服务处理，这让分布式处理变得方便分配。而使用自增 ID 或者美团 ID 生成服务生成的用户 ID 无法这样均匀分散。

（二）、索引

索引常见数据结构和表一样是一个B树，当然也有哈希索引和位图索引。索引最主要的优点是通过对索引的优先查询减少对磁盘上数据行的查询，因为索引通常体积较少，而且缓存在内存中，查询速度比磁盘读取快得多。

数据库默认会给主键建立 Unique 索引，别的索引我们一般会给用户字段建一个索引。 这其实是 RDMS 外键关系的体现之一，因为现在数据设计很少会设置外键关系，但建立索引这件事并不会就此省去。

然而索引不是万能的，在一个表上建立过多的索引会导致插入和更新速度变慢，这十分影响性能。经过测试，在没有索引的表上插入100w行数据（80个字段）用时仅20s，当建立一个主键后，插入速度则减慢到1分30秒，有12个不同索引时，插入需要4分钟。

（三）、分区表

分区表是数据库最伟大的功能之一。通常我们会根据时间字段建立分区表，这样根据时间自然增长的数据会不断的自动归并到历史表中，而且使用同一个表明。当然我们需要付出的代价是查询必须带上分区的字段，不然查询会在全部的分区表上并行执行，这会读到大量冷数据，导致查询速度变慢。

在全部分区表上并行执行的功能也是分区表的优势特点，这让运行一些统计语句的执行速度变快。

三、hint，SQL的魔法

因为清算部门的工作特点，每天都需要执行大量的统计 sql，并且数据都是批量处理的，与前中台业务有云泥之别，数据量有千万级的差距，前中台只需要关注单个用户的数据，而清算需要关注所有用户的数据。每天处理亿级表时经常遇到慢查询，我通过对这些慢查询的优化，积累了不少经验。

sql 执行时，虽然数据库有专门的执行优化器对语句执行进行优化，它的优化主要根据表和索引分析结果进行优化，分析结果更新不及时以及查询条件里的陷阱都会导致执行速度不是最优。具体的优化器的逻辑细节可以参看这篇文章。

比如说 where status in (0, 1) and applytime > sysdate-1，这样的一句语句，status 和 applytime 都有索引，优化器可能会判断 status 索引的区分更高而选用这个索引，然而实际上这个查询会 status 里 90%的数据，导致实际的全表扫描， applytime 反而有更好区分度。这时我们就需要 hint 让 sql 执行强制走另一条更优路线。

这里只有 Oracle 的 hint 语法，参考两篇官方文档：文档1，文档2。

（一）、并行，永远的神

并行主要有 3 个，PARALLEL、PQ_DISTRIBUTE、PARALLEL_INDEX。

PARALLEL 是 sql 执行的并行度，假如查询使用的索引指定的并行度>1 或者查询分区表多个分区时，默认会并行执行 sql。其中 inert 和 update 默认不能并行，需要先执行 alter session enable parallel dml。（然而在实际操作时好像没什么卵用，可能是因为索引锁住了

PQ_DISTRIBUTE 则是对连接（join）进行并行，一般不会启用。

PARALLEL_INDEX 对索引进行并行查询。

（二）、索引类

索引类常用的有 INDEX、INDEX_JOIN、INDEX_FFS、INDEX_SS。

INDEX 是强制使用某一个索引。

INDEX_JOIN 则会合并（join）2个索引，通过合并索引让查询实际读取更少的数据行。然而这个实际上也不定会快，因为索引合并也是需要时间的，而且需要用到这个 hint 的时候，需要反思索引设计是否合理，是否应该直接把两个索引合并。

INDEX_FFS 经常会被优化器自动使用到，它是index fast full scan，指的只对索引进行访问，不对表进行访问。

INDEX_SS 是 index skip scan，用于查询条件并不是联合索引的第一列时强制使用某个索引。这个适用条件其实相当诡异，主要是索引字段设计上的问题产生的。一般就算用了这个 hint 也不会很快，但是我这边实际用的过程中对一个亿级表的读取速度提高了5倍。

（三）、join类

因为外键关系和设计范式 ，join 在 sql 用的地方相当多。我18年写的最长的一条 sql 整了60行，用了5个 join 和3个 in 子查询。而今年写的最长的 sql 整了250行，用了10个join 和1个 in 子查询。

相关 hint 主要有 LEADING、USE_NL、USE_HASH、USE_MERGE。

LEADING 永远的神，指定关联顺序。

UES_HASH 是 hash join，一般当目标数据量比较小时，优化器会倾向于使用这个。

USE_NL 是 nested loop， 一般当目标数据量比较大时，优化器会倾向于使用这个。 但是因为 loop 的关系，速度通常不会很快，所以常常需要强制使用 USE_HASH，但当（通过查询条件筛选后）结果数据集比较小时，速度会比 hash join 快

USE_MERGE 会按序进行合并，感觉用的情况比较少。偶尔会看到优化器用到这个。

所有 in 子查询都可以改写成 inner join。一般来说当 in 的结果集比较小时，会比 join 快。但是也有 hint 针对子查询，UNNEST 、PUSH_PRED、PUSH_SUBQ。 UNNEST 会让子查询变为 join，PUSH_PRED 会把视图外的查询条件传入视图（join 关联的子查询），PUSH_SUBQ 会让不能合并的子查询优先执行优先用于查询条件。

（三）、其他

其他还需要介绍的有 APPEND、MATERIALIZE、DRIVING_SITE、DYNAMIC_SAMPLING。

APPEND 能提高 insert 速度，原理大致是不考虑数据行空间压缩，批量导入数据时非常实用。

MATERIALIZE 用于 with 表达式，可以强制将表达式计算为临时表，从而提高查询性能。

DRIVING_SITE 用于查询存在 dblink 的情况，用于决定查询在那个库上执行。假如 A 库的表 o 比较大，B 库的表 p 比较小，则 DRIVING_SITE(o)，让数据从 B 库拉取到 A 库进行执行。有时通过对表 p 查询结果集字段加 to_char() 也能达到优先拉取数据的效果。

DYNAMIC_SAMPLING 则是对 sql 执行的数据集进行分析，默认值是 0，修改为 1 或者 2 通常就足以避免上文说到的查询条件存在陷阱的情况。使用 DYNAMIC_SAMPLING 也能让执行计划判断 cost 更加准确。（当传入最大值10时，运行分析执行计划，速度会非常慢，因为这相当于执行了整个语句）

四、客户端配置优化

（一）、连接池优化

经过大量的测试，发现纯托管代码的驱动 Oracle.ManagedDataAccess.dll 创建数据库连接的性能很差，在多线程情况下经查报出：ORA-12537: Network Session: End of file； ORA-12170: TNS:Connect timeout occurred；
ORA-12170: TNS:Connect timeout occurred；ORA-03113: end-of-file on communication channel；ORA-03135: connection lost contact；TNS-12607: TNS: Connect timeout occurred；Pooled connection request timed out；TTCException 等。

针对这种情况需要修改连接字符串的配置：Min Pool Size （最小连接池大小）、 Incr Pool Size （连接池扩容时新增大小）、 Connection Timeout （获取连接超时时间，单位s）。例如；
Min Pool Size=5;Incr Pool Size=10;Max Pool Size=100;Connection Timeout=60; 或者对于接口业务：Min Pool Size=10;Incr Pool Size=10;Max Pool Size=100;Connection Timeout=1;

（二） 、缓冲区优化

FetchSize 控制数据缓冲区大小，当获取大量数据时，调大 FetchSize 可以减少数据库读取次数。默认值为 131072 ，单位为字节，我这边应用中调整为了 13107200。FetchSize 可以在创建 OracleCommand 时设置，也可以在ExecuteReader 后在 OracleDataReader 上设置（这时获取到了RowSize，也可以设成 RowSize 的倍数）。还可以在应用程序的配置中设置，在 app.config 中添加：

  <configSections>
    <section name="oracle.manageddataaccess.client"
      type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.19.1, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
  </configSections>
  
  <oracle.manageddataaccess.client>
    <version number="*">
      <settings>
        <setting name="FetchSize" value="131072000" />
      </settings>
    </version>
  </oracle.manageddataaccess.client>

对于 .net core 则需要在启动时配置 OracleConfiguration，参考这个。

（三）、ORM

通常使用 Dapper 用于 orm，但当列名与字段名不相同时会比较麻烦。这里会说明一些 ado.net 的细节。

1、使用 DataReader 比读成 DataTable 快20%，因为省去了 DataTable 中构建红黑树的时间。

2、DataTable.NewRow() 不是线程安全的，不能并行调用。

3、DataRow[“colName”] 或者 DataRecord[“colName”] 这种用法不是 O(1) 的，虽然内部有构建一个 ColumnNameDict 字典，但 ColumnName 默认是全大写的，传入一个非全大写的值会导致查询 miss，产生一个 O(n) 的遍历搜索。（然而差距很小，10w行也就差1s）

4、OracleParamter 构建时 ParamterName 应当带有”:”，不带”:”的话驱动内部会自动加上。（并没有意义）

5、使用 OracleCommand.ArrayBindCount 进行批量处理时，如果传入数据数组大小为 0，可能会报错或者插入一行 Type.ToString() 的值。

6、因为连接是池化的，Connection 实际并不需要调用 Close()，只需要调用 Dispose()。 （并没有意义）