SQL性能优化-表与索引的设计(更新中)

表的分类

表就是存储数据的主要结构，按照不同的维度去区分，表的类别有很多。
最普通的堆表、按照一定规则进行数据拆分的分区表、按照列进行组织的列存表、底层存储进行了抽象整合的段页式表、底层使用原地更新模式的inplace-update表、会话内临时存在的临时表、结构全局存在但数据会话存在的全局临时表、不带预写日志的unlogged表…理论上在不同维度下可以进行交叉组合，例如列存分区表、非日志临时表、列存段页式表等，具体是否支持就看数据库厂家是否有做了。
因此选用合适的表是一切的基础。

• 普通表 ：
  OG内一般称呼普通表，就是在append引擎模式下的堆表，也是最常见的表。一张表就是一系列文件，其中主要包括主文件、fsm文件、toast文件。
  数据按照行进行存储，一条条数据又直接从后向前首尾相接直接堆放在一个8K的称作“页”的结构之中，每当一个页满了之后，主文件会扩充大小，扩出来新的页进行数据的存储。当某些行被删除时，页面会出现空洞和不满的情况，此时需要用fsm这个结构与vacuum等来进行管理。toast文件则是用来存储过长的数据的一种方式，详细见下。
  最普通的表，算是万用的表，其他的表都以此表为基础对照进行衡量。

  • toast表： toast表并不是我们可创建的一类表，是过大尺寸属性存储机制的一种解决方案，全程为(The oversizeed-attribute storage technique)。以附属表的形式存在着，当我们创建一张表的时候，一定条件下会同时创建一张toast表，若我们表内的某行数据，有一列尺寸过大，则这列数据会存放到附属toast表内，表里则存放一个指向toast表的指针。

• 分区表 ：
  分区表的意思是，将数据按照一定的范围进行拆分，虽然对我们展现的还是一张表，但实际上底层是分了好几张表。因此在原理上，这与行存列存、a\u store等是不冲突的，具体看数据库支不支持。
  分区的好处是在进行where查询的时候，能少扫描数据。例如我们在订单表中，若要查找某一天的所有订单，暂时不考虑索引，则我们需要去扫描整个表的数据，并过滤出来满足这些条件的订单；但如果我们按照日期进行了分区，则我们只需要按照查找条件找到那个分区小表，然后扫描那个小表即可，可以提升性能，这个过程就叫做“分区剪枝”。
  可以看到，分区表的性能提升点主要在于剪枝，合适场景理论上性能是可以线性倍增的。但是需要注意的计算剪枝范围的过程也是有消耗的，且若存在同时能走索引的方案时，分区剪枝并不一定有优势。
  因此分区表主要适用与贴切分区剪枝的场景，例如一些范围类复杂查询、存在范围内的热点数据等的场景。

• 列存表 ：
  列存表的意思是，数据按照列进行存储和管理。OG中以CU为单位，一个CU内最多存6W行的单列数据，一个表由很多CU来组成，其中又有附属delta表来进行CU的管理等。
  列存表的好处在于能够仅去取我们需要的列进行查询和计算，因此在IO读取和内存计算上都能带来裁剪效果，因此对复杂查询，尤其是join操作上的性能提升十分巨大。
  但是列存表由于一行的数据并不在一起，因此对于OLTP场景，反而会水土不服十分差劲。

• 段页式表：
  段页式表的意思就是底层存储采用段页式的表。普通堆表的存储都是一个表创建对应的一组文件，这样有很多的缺点。当表很多或分区很多的时候，会生成大量的文件，管理不便；而且若一个表很大，那么对应的文件也会很大，虽然有文件过大时的截拆策略，但还是会受到磁盘容量的制约，让扩容十分的不便。
  因此段页式就是将底层存储的文件对应方法进行了提取整合，不同的表可以存在一个文件中，文件内按照段进行区分等，详细不展开。
  段页式带来的好处主要是操作系统文件管理、扩容等方面的便捷，对SQL性能理论上并没有什么提升。

• 非日志表(UNLOGGED TABLE)：
  非日志表就是不启用预写日志(WAL)的表。这些表在进行数据的insert、delete、update等操作的时候，不会产生预写日志，因此性能会很快。
  但这种表无法保证事务的ACID特性，当发生宕机时，未刷盘的数据可能会直接丢失，也没有预写日志来进行数据的找回，因此很多数据库都会有不同的做法，例如清空表数据等。

• 临时表与全局临时表：
  临时表就是仅在当前事务、单个会话或单用户所有会话等生命周期内有效的表，生命周期结束后会自动删除。
  全局临时表就是表结构在全局生效，但其中的数据在当前事务、单个会话或单用户所有会话等生命周期内有效，生命周期结束后数据自动清空，表不会被删除。

…

索引的分类

索引的分类也有很多。
聚簇索引、btree索引、hash索引、分区索引、全局索引…当然不冲突的维度也可以组合。
同时一类索引建立在不同的表上，也会有不同的行为差异。
选用合适的索引也是一切的基础。

聚簇索引和非聚簇索引 ：主要描述索引和数据的存放关系，聚簇索引里数据与索引存放在一起，例如InnoDB；非聚簇索引则索引和数据分开，例如PG。
OG内目前仅支持非聚簇索引，索引单独存放，按照key进行结构组织，能通过key找到对应元组在表中的位置，进而回表访问数据。

全局索引与分区索引 ： 此维度主要针对分区表，虽然对我们的使用体验上时一个索引，单底层为每一个分区都创建一个索引的就是分区索引，不区分分区直接建立一个索引则称为全局索引。
分区索引融合了分区表的性质，分区索引在使用前需要先找到对应的分区，即剪枝，然后才能找到这个分区上的索引，然后再进行查找；而全局索引则不用关注。
但在一些分区DDL操作上，分区索引能够表现出天然的适应性，全局索引却会"牵一区而动全身"，表现略差。

btree类索引 ： 最常见的索引，btree、b+tree等。不在赘述。

hash索引 ： 采用hash表的方式进行创建组织的索引，目前使用不多。相对于btree并没有多大优势。

…

如何创建表

• 根据业务选择合适的表类型
  如上

• 列的数量不宜太多
  行存表内，即使我们只查询一列，也会将一行数据都扫描上来，因此列的数量不宜设置太多，不然会IO较大，内存中计算。
  列数量太多时，建议非热点列，单独裁出去做一张表。

• 短的、热点的、定长的列尽量放在前面
  行存表内，当一行数据读取上来后，必然需要做的就是对这段比特串数据进行解释，取到我们想要的列，那么这列在这段比特串里越考前，肯定获取到的时间就越早。
  那么自然，这些热点列放在越前面越好。短的列放在前面，即使不需要也不会影响太大。但对于变长数据的列，若非toast存储，则也需要将这列数据的长度解释出来，才能继续解释下一列。

如何创建索引

• 根据业务选择合适的索引类型
  根据表的类型、业务内容，选择合适的索引类型。
  根据表内列的数据特征进行索引的创建

• 根据SQL选择合适的索引键
  参考业务SQL中用到的WHERE、JOIN、GROUP BY、ORDER BY等所用的条件，选择在哪些列上建立合适的索引。

• 根据表的数据特征选择合适的索引键
  索引最好建立在那些数据重复值低、分布均匀的列上。

• 索引数量不易过多
  虽然索引能够加快条件查询，但是数据的变更操作有需要额外去更新维护索引，带来额外的开销，因此在数据会更新的场景，索引的数量需要酌情考虑。