SQL优化经验

下面的❤未必准确，是为了参考。重要的是提供各种方法，需要根据具体情况来选择

原因or结果

当我们收到系统的一个功能卡的反馈的问题时需要搞清楚的是这个功能卡是自身造成的还是收到了其他的影响造成的。我们可以把这个操作在用于性能优化的环境复现下看是否还卡（注意数据尽量和生产保持一致），如果发现并不卡则可能是其他原因造成，问题的症结不在这，另外下面的情况可以帮助我们明确：

• 系统负载高
  系统负载高既可能是由于反馈的问题造成的，也可能是其他问题造成从而导致了反馈的问题
• 写入高
  由于读和写在数据库中有锁的管控，特别是人们要读刚写入的数据从而消除自己内心的不安定感
• 读取高
  当系统卡的时候，人们反而会更频繁的刷新，甚至开多个窗口同时刷新从而更快的得到数据
• 网络
  有的时候是机房自身的网络（例如其他有上传和下载功能的系统占用了大量带宽），有时是

索引

单列索引&联合索引

如果有多个查询条件，理论上可以使用联合索引，但是一个表会被很多SQL查询，有的时候未必能找到多列都在where中。不过可以考虑把order by中的列放入联合索引第二列，但是我在Postgresql中联合索引和单个索引都有的情况下数据库的优化器选用了单列索引。另外联合索引可以当做窄表来使用。

索引列

这是个最容易想到的方法，大多是情况下需要查询条件有一个是必须的，从而对该列建立索引，这一列可能是时间（默认查当月）或者是某种权限限制（例如部门id）。

索引类型

不同的数据类型会使用不同类型的索引（例如存放GPS信息的数据类型），不过一般对于varchar和number等常见的类型来说，有B+Tree和Hash可选，前者大家都比较熟悉，后者用于取值范围是比较小的集合的情况（例如：性别取男女），可以参考java中的enum情况

综述

工作量：❤❤❤❤❤
效果：❤❤❤❤❤
资源占用：❤❤❤❤❤
业务侵入性：❤❤❤❤ 需要必须出现的where条件
平台相关性：❤❤❤❤ 有些特殊数据类型的索引比较特殊
使用前提：执行explain [analyse] 后对于全表扫描后大量过滤的地方可以考虑使用索引

错误示例

函数

在索引列上使用函数会导致索引失效，这一点特别是在日期比较时会出现，所以这个时候需要把函数施加在查询入参上而不是索引列上。这种是错误的函数使用方式

无效

另一种情况设置了索引的列过滤条件不够强，例如查询国籍为中国的员工，但是99%的员工都是中国的，那么这个查询反而会比没有索引时更慢，因为数据库需要先对索引进行一次IO，然后对表数据进行一次IO，本来第一次IO的目的是为了减少第二次IO的工作量，此时却未达到目的，但是如果查询国籍为美国的员工时，索引就会发挥出威力。后来Oracle针对这个情况会根据其自身的一些统计信息来放弃使用索引，后来MySQL和PostgreSQL也跟进了这一特性

冗余

表内冗余

一般用于状态这一类的字段，可以通过同一行其他字段来计算的

冗余列

如果查询条件中的一个字段是通过数据库表中多个字段计算而来，那么这个时候会造成全表扫描
工作量：❤❤ 工作量大，因为新增了列后需要在所有写入表的操作中修改代码以确保该列值正确。如果本身基于CQRS架构则工作量会少一点
效果：❤❤❤❤ 有效的避免了全表扫描，需要注意的是冗余列为select中列时效果没有where列效果好，但是反效果是让一行数据占用数量增加，会增加磁盘IO
资源占用：❤❤❤ 硬盘占用资源上升，反过来又导致数据库负载增加
业务侵入性：❤❤❤❤❤
平台相关性：❤❤❤❤❤
使用前提：读多写少，冗余列的计算规则简单且占用磁盘空间少。

函数列

有些数据库支持一些叫虚拟列或者计算列的方式，就是在表中新增一列，但这列的数据不需要通过程序来写入数据，而是通过其他几列自动计算出来，相当于数据库触发器设置了这一列的值，这就有点物化视图相对于视图的感觉，所以这一列的更新需要在数据库中显示update对应表才可以，那么这个函数也成为不可改函数，那就是说只要这一条记录没有变动，那么虚拟列就不应该有变动，那么就不能使用>now()这一类的运算了。这对于一些状态而言有些麻烦。可以解决上面索引失效问题但不是好的解决方案
工作量：❤❤❤❤ 工作量比靠代码来写入冗余列少很多
效果：❤❤❤❤ 和冗余列一样
资源占用：❤❤❤ 同冗余列
业务侵入性：❤❤❤❤❤
平台相关性：❤❤ 需要数据库支持，不过目前很多都支持
使用前提：函数列的计算规则固定（不会随时间经常变动）

冗余表

相对于冗余列而言更极端的情况则是设计冗余表，这就走向了CQRS。

宽表

宽表是在冗余列的基础上更进一步，冗余列和函数列是为了减少查询时的计算从而避免全表扫描和利用索引，而宽表则是为了减少jion查询的，这也意味着为了保证宽表数据的准确，必然频繁的去写，可以在以下两种场景使用从而避免大量写宽表

• 维表数据变动不频繁，从而减少update，如果需要保留维表历史数据，则此时使用最恰当（否则就需要维表做快照）
• 维表数据准确不重要，有的时候为了减少对业务的影响，一些维表就会在非高峰期变动，例如价格，部门名称等，这种情况可以通过定时任务来刷新宽表，最好有gmt_modify字段

工作量：❤ 工作量非常大
效果：❤❤❤❤❤ 多表jion变单表保证where高效性
资源占用：❤ 静态资源（磁盘）和动态资源（吞吐）都占用很多
业务侵入性：❤❤❤❤❤
平台相关性：❤❤❤❤❤ 需要数据库支持，不过目前很多都支持
使用前提：一些报表分析以及贯穿多个业务流程

窄表

与宽表避免jion查询不同，窄表是为了减少jion的代价，当然也可以把窄表视为垂直分表的一种。在页面查询中大量的情况是通过id关联另一张表获取name，或者通过外键继续关联下去，而对于维表来说可能为了满足自己业务需要有一大堆的字段，特别是产品经理的最爱：备注，会导致一次磁盘块只能存放少量数据，而窄表可以减少IO特别是在分布式环境下，将对foreign table的jion转换为对本地窄表的jion。
工作量：❤❤❤ 只需要讲写原表的地方加上对窄表的修改即可，并且大多数情况下窄表的数据不会变动（毕竟列少且一般不参与业务操作），大多出现在编辑操作中会修改，业务动作时不会修改，大多在insert时就确定了
效果：❤❤❤ 如果where条件命中了窄表就悲剧了
资源占用：❤❤❤
业务侵入性：❤❤❤❤❤
平台相关性：❤❤❤❤❤
使用前提：一个业务域关联另一个业务域只是为了取一个字段来显示

读写分离

当单机的硬件资源无法再提升的时候，读写分离就从架构上把多台服务器整合成一台服务器。这个是一个比较成熟的系统也是大家普遍能接受的方案，另外大家对这个方案的接受也为最终的CQRS做了铺垫，否则用户/产品经理是无法接受理论上的延迟。这里只讨论用于提高系统吞吐的读写分离而不包括为了高可用采用的集群。那么这个时候主库和从库一般是在同一个机房甚至同一个机架上，那么这个时候主从之间的延迟相对于互联网上的延迟而言是非常小的，所以忽略它。

错误

写多读少

一个显而易见的事实是从库的数据也需要更新，这里并没有黑魔法。所以从库的写入会随着主库的写入而增加，从整个系统的角度来看写操作被放大了。如果是一个写多读少的系统，或者是系统瓶颈不在读而是写

用于写入的读

需要注意的是这里的写和读应当是针对业务操作而言，并不是对于单条SQL语句。一个写业务操作也可能需要从数据库读取数据（从而判断走哪一条逻辑分支）。这个时候为了维持数据的一致性，也就是可重复读这样的事务性要求，就需要多次数据库操作（包括读写）在同一个事务，也就是同一个库。
效果：❤❤❤❤
资源占用：❤
业务侵入性：❤❤❤❤❤
平台相关性：❤❤❤❤❤
使用前提：读多写少

分区&表分区

分区&表分区是分库的前奏，在试图保持数据库的逻辑视图不变的情况下更改下层的物理结构。分区一般是对表进行水平划分，一方面可以将分区看作是更上一层的索引，也就是索引的索引或者粗粒度的索引。表分区将不同的表划分到不同的物理资源上，相当于增加了物理资源增加了吞吐力。
其中分区又可以分为两种方案，一种是根据表中某一个字段（这个字段相当于粗粒度索引）来对表进行划分，这就要求这个字段是非空的，这种方案的可能问题是数据倾斜。另一个种是根据id来hash，但是这样的话join的时候可能有问题。
效果：❤❤❤❤
资源占用：❤
业务侵入性：❤❤❤❤❤
平台相关性：❤❤❤❤❤
使用前提：读多写少

缓存

相对于前面各种针对数据库本身的优化，而缓存的使用其实超出了本文的题目限制。但是作为提供整体优化的一种方案，这里也介绍一下。
可以说缓存的使用比较成熟，一般这个方案的技术点在于缓存的更新。缓存一般可以放在本地也可以使用Redis这样外部缓存，但总的来说都是将数据放入内存而非硬盘。但其实数据库本身也会使用内存来缓存硬盘数据。
如果采用了CQRS的架构，只有Query使用缓存的话，则问题不大。另外Spring集成了缓存组件，可以在数据修改时自动作废缓存
效果：❤❤❤❤
资源占用：❤❤
业务侵入性：❤❤❤
平台相关性：❤❤❤❤
使用前提：读多写少