1.前言
最近刚刚从项目中抽身，虽然目前仍处于远程support的状态，不过基本上已处于收尾阶段，在这个长达小半年的封闭项目中，个人也是学到了很多，除了技术层面的，也有对自身能力的一个思考。
2.掰扯
从业起来，还未做过这种封闭项目，也没有做过如此规模宏大的项目，更没有做过这种7 * 24小时无休连轴转的项目。没错，十年一次的人口普查项目，意义还是十分重大的，TBase在这个项目了承接了HTAP的角色，既要录入人口的信息，还要做各种维度的统计、分析与计算，因为上面的领导需要时刻关注人口汇报的进度，以及基于前一天的汇总数据进行规划和排期等。
业务模型再加上基于全国人口这个庞大数字需要在短时间内采集完的这个场景，就注定了这个项目十分艰难，好在结果是可喜的，TBase扛住了。
3.技术问题
3.1.开发规范
3.1.1.开发规范
首先就是开发规范，这个项目是腾讯和ISV一同合作的，所以开发规范一定要定好。分布式数据库和集中式数据库不一样，虽然分布式通过Scale Out的方式可以准线性增加实例的计算能力，但是使用上和集中式差异很大。
3.1.2.查询规范
1．失败重试的机制很重要，业务有些任务跑批，但没有容错重试机制，出错后就全部出来效率极低。
2．显式指定列名，避免使用SELECT *，或在RETURNING子句中使用*。使用具体的字段列表，不要返回用不到的字段。这个算是一个通用的查询规范，可以降低IO
3．重复查询使用预备语句prepared statement，类似于Oracle的绑定变量，减少数据库硬解析的cpu开销。
4．判断结果存在性不要使用count，使用select 1 from tbl where xxx limit 1判断是否存满足条件的列，要比count快。
5．使用upsert简化逻辑，业务出现插入失败再去更新的操作序列时，可以考虑使用upsert替代，如冲突了什么都不做insert into test.upsert_test(id, "name") values(1, 'm'),(2, 'n'),(4, 'c') on conflict(id) do nothing;
6．读写分离，原则上写请求走主库，读请求走从库。除非需要严格的一致性保证，且检测到显著的复制延迟，并且一旦放到主库上需要时刻关注主库的负载情况，主库一旦挂了发生切换，RTO和RPO如何保障也是一个需要思考的点。
3.1.3.设计规范
TBase基于PostgreSQL的引擎，和MySQL还是有不少差异，比如MySQL会限制字段长度如varchar(3)，用多大就设多大；如果存储的字符串长度几乎相等，使用CHAR定长字符串类型可以减少空间碎片；限制使用text类型；字段要求not null，null的列使索引/索引统计/值比较都更加复杂等；
TBase没有过多的约束，如NULL在tuple的中有一个map专门存放(t_bits和t_infomask)，null值没有在元组的数据部分标记出来，但是最好也限制not null。比如空值与任何值的逻辑判断，其结果都为空值，例如NULL=NULL返回结果是NULL而不是TRUE/FALSE。文本类型包括char, char(n), varchar,varchar(n), text。除char(n)外并无本质存储区别。varchar的存储空间统一为4Byte + 实际的字符串长度，推荐使用varchar和text，避免使用char(n)且char(n)没有存储和性能优势。常规数值字段使用int，无特殊理由不要用smallint，性能与存储提升很小，但会有很多额外的问题。
当然也有共通的，比如要求有主键，禁止外键，禁止大对象如图片、音频等，能用数字的坚决不用字符串，要求字段加上注释，字段命名规范化和使用UTF8编码等等(对应到MySQL是utf8mb4，因为项目中会存储到emoji表情)。
3.1.4.索引规范
TBase得益于PostgreSQL原生支持多种索引的特性，诸如传统的Btree、Hash索引，也有和ES中的类似的GIN倒排索引(适用于稀疏检索，数组等)，也有适用于几何类型、空间类型的的GIST索引，也有适用于时序数据的BRIN索引(按范围检索很快)，也有各种插件化的定制索引如适用于全文检索的RUM索引，也有类似于redis中布隆过滤器的BLOOM索引(可以收敛结果集，排除绝对不满足条件的结果，剩余的结果里再挑选满足条件的结果)。
虽然索引多种多样，但是大多数时候Btree + 联合索引都能cover住业务场景，GIN索引也可能用上(但是按照边界查询效率很低，不支持唯一索引、更新性能影响比Btree大等)，再加上函数索引，部分索引、覆盖索引(index only scan)等，但是要注意索引越多对于写入的影响也是蛮大的，尤其是BTREE不建议对频繁访问的数据上使用非常离散的数据，例如UUID作为索引，索引页会频繁的分裂，重锁，IO和CPU开销都比较高。再加上索引也可能膨胀，需要维护，所以索引一定要知晓对应的业务场景，合理选择。
3.1.5.通用优化
业务方可能从功能方面实现了SQL，但是未考虑到SQL的效率。比如一些通用的优化，如
1)大多数场景用exists替代in，在TBase中使用=ANY(ARRAY[1,2,3,4])代替IN (1,2,3,4)效果更佳；
2)避免在where条件中等号的左侧进行表达式、函数操作；
3)当数据量大时，避免使用where 1=1的条件(防止SQL注入)；
4)MySQL中需要避免隐式转换，Tbase没有太多这方面的限制；
5)尽量使用覆盖索引(TBase叫Index only scan)
6)对于包含or的SQL，TBase里面会使用bitmap index scan进行索引的过滤(其实原理就是将随机IO转化为顺序IO，如果获取的结果集的占比比较小，但是元组数很多时，Bitmap Index Scan的性能要比Index Scan 好)，这样不需要改SQL语句，但MySQL大多数时候需要改写为union或union all。
7)避免长事务，这就要求应用程序一定要开启autocommit，长事务会导致表膨胀，年龄回收失败等，尽量开启事务后及时提交，避免大量的idle 事务。
8)控制锁的粒度，加锁越快越好，越短越好等。
3.2.问题处理
3.2.1.模糊查询
TBase承担的SQL中很多都有like xxx%这种的SQL，在TBase中可以用collate “C”(指定字符的排序和比较规则，C相当于忽略字符集带来的差异，LC_ COLLATE = C是PostgreSQL自带的唯一一个排序方法，不依赖于操作系统)或者创建指定的操作符varchar_pattern_ops，类似create index t_idx on test(info varchar_pattern_ops)。虽然业务也有like %xxx，可以通过反转索引reverse(xxx)实现走索引，不过已明确让业务禁止使用，不好维护。对于前后模糊(like '%xxxx%')，以及前后模糊的正则表达式(~ '.ab?cd[e-f]{1,10}-0.')，很多数据库无从下手，无法优化，只能全表扫描，对每条记录进行单独的处理。但在TBase中引入了pg_trgm插件，以支持 like %xxx%这种，不过需要三个字符以上才可以走索引(社区版的pg_bigm插件可以解决)，业务使用到了前后正则表达式查询，但量不大。
3.2.2.主从延迟
在项目中，业务反馈说插入的数据不能及时查看到。于是查看pg_stat_replication视图时，会发现主备之间的replay_lag延迟很大，平均15分钟左右的延迟，导致目前业务看到的数据还是15分钟以前的数据。原因是主上面update的频率特别高，业务也决定了update的频率特别高，和insert比率9 ：1。相较于innodb的undo + redo，PG系的redo + tuple的版本信息实现的MVCC(没有undo)，update会稍弱于MySQL，而且这种机制会带来PG里面一个很常见的问题表膨胀，但是不会有undo段不够的问题，同时不怕大事务，事务回滚特别快，只能说各有千秋。备库上的查询又很大，就会频繁提示terminating connection due to conflict with recovery和延迟持续上升(备库同步了一个主库的vacuum截断锁，延迟最大至max_standby_streaming_delay参数)，最后是降低了vacuum频率，调整vacuum相关参数，同时采用分区表和HOT降低表膨胀带来的负面影响。这一块就不细讲了，可调参数太多，专门整理过一篇文章。在PG12中引入了ALTER TABLE some_table SET (vacuum_truncate = off);这个表级参数可以关闭vacuum的排它锁。
3.2.3.统计视图的利用
因为PostreSQL自带的统计视图很多(pg_stat*开头，所以可以很方便的集成到各种各样的监控方案里)，再加上各种各样的Extension，也可以监控到底层诸如缓冲区使用率等。
查看大于1s的sql：
select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '1 s' order by query_start ;
查看被阻塞SQL：
select pid,pg_blocking_pids(pid),wait_event_type,query from pg_stat_activity where wait_event_type = 'Lock' and pid!=pg_backend_pid()，
查询无效垃圾索引：
select indexrelname,relname,pg_get_indexdef(indexrelname::regclass)as index_ddl from pg_stat_user_indexes where idx_scan = 0;注意如果一个 index 刚 create或者create不久，那么使用率低是正常的，并且主库和从库的index使用情况是不一样的，所以需要全面看。
查看大于5分钟的长事务：
select query,state from pg_stat_activity where state<>'idle' and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval '5 min' order by xact_start;
3.2.4.函数索引的坑
虽然TBase(PG)很早就支持函数索引，但是函数索引有局限性，业务的SQL有用到 (extract(year from age('2020-09-30', to_date(cihp.l4,'yyyyMMdd')))>=60这种类似的，很容易想到函数索引，但是创建函数索引的过程中可能会遇到error: functions in index expression must be marked immutable，官方不建议在非immutable的函数上建立索引，不过我们可以根据实际场景需要再包一层UDF，也可以alter function xxx immutable，但一定要注意函数索引稳定性可能带来的问题。
3.2.5.从服务器层面定位
出现问题的一般步骤是先使用top看最近的负载，负载在Linux中体现的是整体系统负载，即CPU负载 + DISK负载 + 网络负载 + 其余外设负载，并不能完全等同于CPU使用率。CPU us高一般都是慢SQL或索引不当导致的，sys高一般伴有大量的上下文切换和中断
内存的话TBase和Oracle一样是共享内存的架构，同时有私有内存，每个进程的私有内存空间中会缓存一些元数据，比如系统表数据，表定义，执行计划等等，当数据库里表，分区以及表字段很多时，它们的元数据会占用的内存也会比较多，通常，元数据缓存中占用内存最多的是下面两个系统表pg_statistic和pg_attribute，如果出现内存不足可以减少分区、通过alter table set statistics对不需要通过柱状图评估选择性的字段，减少收集的统计信息、拆分负载等。另外表锁是存储在内存中的(行级锁不是，存储在磁盘上，非ram)，上限由参数max_locks_per_transaction和max_connections控制，在shared_buffer中保留的锁的数量为max_connections x max_locks_per_transaction。超过就会提示ERROR: out of shared memory，要避免空间耗尽，导致无法获取新的Lock。特别注意，分区表多的情况更会发生，因为分区表的各个子表都是被视作为一个正常表的
磁盘的话因为TBase的mvcc机制，vacuum、checkpoint等都是很吃IO的大户，磁盘IO也要尽量跟上，可以用dd配合大的block测试磁盘性能(专业一点得用FIO等)否则会发现表膨胀严重(来不及vacuum)，统计信息不及时，crash之后recovery特别久(bgwriter不给力，share buffer维护的不好)等；
网络需要关注带宽、丢包率等，也可以用iftop抓取网络尖峰，同时需要注意TIME_WAIT和CLOSE_WAIT的TCP连接数(TCP 端口数量上限是 6.5w)，TIME_WAIT是主动关闭连接的一方保持的状态，而CLOSE_WAIT一般是由于对端主动关闭，而我方没有正确处理的原因引起的。说白了，就是程序写的有问题，属于危害比较大的一种。另外ss、mtr、tcpdump都是不错的工具
3.2.6.合理利用timeout
执行任何DDL前，都需要显式设置lock_timeout，如set lock_timeout to 3000;这个参数的意思若3s获取不到需要的锁，就提示cancel statement due to lock timeout，比如删除索引的时候需要获取表的排它锁，若表上仍有查询，就会阻塞，不加此参数，删除索引的进程就会一直阻塞，直到表上的查询全部结束，而后面来的SQL就会被这个删除索引的排它锁阻住，导致雪崩效应，实际定位过程中可以使用pg_blocking_pids(pid)结合pg_stat_activity获取被阻塞的进程pid；
同样的类似还有statement_timeout限制单条语句的执行时长，idle_in_transaction_session_timeout控制“idle in transaction”的长事务，长事务的危害太多。
3.2.7.统计信息的重要性
目前的大多数数据库都是CBO(基于代价的优化器，目前关系数据库的主流技术，CBO会将原有表达式保留，基于统计信息 + 代价模型，尝试探索生成等价关系表达式，最终取代价最小的执行计划)，而RBO(基于规则的优化器，动态适应性差，会将原有表达式裁剪掉，遍历一系列规则(Rule)，只要满足条件就转换，生成最终的执行计划。一些常见的规则包括分区裁剪(Partition Prune)、列裁剪、谓词下推(Predicate Pushdown)、投影下推(Projection Pushdown)、聚合下推、limit下推、sort下推、常量折叠(Constant Folding)、子查询内联转join等)都是很古老的数据库，TBase(PostgreSQL)是catalog-driven型的数据库，引擎运行过程中所有所需的数据、信息都存放在系统表中，统计信息不例外。统计信息是TBase中重要的组成部分，SQL优化、执行方式为代价模型。而这里的各路径的代价计算,则是依赖于系统表(pg_statistic)中的统计信息。这些统计信息，则是通过SQL命令vacuum和analyze分别写入pg_class和pg_statistic中的。因为项目中会经常涉及到数据导入导出，在导入导出完之后务必要做一次全库的analyze(很耗时，也可以针对业务立刻需要的表进行一次analyze，业务低谷期进行全库analyze)，不然会发现SQL执行计划有偏差，业务发现好几次有索引不走，非要seq scan，导致负载陡升，遇到慢SQL除了传统的三板斧，看执行计划、调索引、优化SQL，在TBase里面，还特别需要注意统计信息的重要性，虽然有后台进程autovacuum会定时收集更新。
另外优化器也有不智能的时候，项目中就遇到过一次，很简单的语义分析，但是没有分析出来导致了大量的重分布，这个时候就需要我们显式指定去告诉优化器。
3.2.8.优化SQL
碰到慢SQL，一定要慢慢看，动辄三四百行的SQL很多，得益于PG，explain易于理解(树状展示，cost，实际执行时间，buffer和IO)，看执行计划时，遵循从左往右看，从下往上看。按照查询计划树从底往上执行基于火山模型执行，即可以简单理解为每个节点执行返回一行记录给父节点（Bitmap Index Scan 除外），类似于这种
Sort
└── Hash Join
├── Seq Scan
└── Hash
└── Bitmap Heap Scan
└── Bitmap Index Scan
一般着重关注seq scan和nest loop，这些都是很耗时的步骤；然后相应调整索引(配合覆盖索引)、配合CTE(层级处理神器)、条件下推、利用索引优化order by等。
4.技术思考
虽然项目很辛苦，熬了大半年，但是对于个人而言还是收获满满，除了对PostgreSQL技术的沉淀，从硬件服务器层面也学会到了很多，如Load负载，更加深刻理解了这个指标的含义，负载在Linux中体现的是整体系统负载，即CPU负载 + DISK负载 + 网络负载 + 其余外设负载。同时也愈发觉得自己的能力栈缺口太多，如网络这一块，如何排查，如何抓包，一些常见的协议如http、tcp等，相较于磁盘、内存和CPU等，网络相对而言更难排查问题，这一块需要恶补。
另外数据库层面的知识，对于优化的手段，对于PostgreSQL来说，除了传统的三板斧，看执行计划、调索引、优化SQL，是否还有更多优化的空间？之前曾在德哥github上面看到过的例子《递归收敛优化》、《分组TOP性能提升44倍》等，都是在PostgreSQL另辟蹊径的优化大法，但由于能力尚浅，没有充分理解其精华所在，无法消化成自己知识体系，上了生产还是无法使用到。
以上种种便是我个人在项目中的一些思考，职业生涯还很长，任重而道远。