接手一个烂摊子之后：金仓数据库开发规范实战笔记

接手一个烂摊子之后金仓数据库开发规范实战笔记从一个凌晨三点的故障说起去年接手一个电商中台项目上线才两个月就开始频繁出问题。最严重的一次是凌晨三点订单创建接口大面积超时。排查到最后发现是一张订单表三个月的数据量不到一千万条但每秒钟几百个INSERT操作加上十几个索引的维护开销把服务器的IO完全打满了。打开那张表的定义我惊呆了67个字段其中15个是VARCHAR(500)以上13个索引有一半从来没有被查询用过没有主键用的一个联合唯一索引代替表的fillfactor是默认的100意味着UPDATE会产生大量版本链这不是个案是开发规范缺失的典型后果。接手之后我花了两周时间梳理了一套针对金仓数据库的开发规范现在拿出来分享一下。一、整体设计原则先把大方向定下来1.1 字符集统一金仓支持多种字符集但同一个实例里混用UTF8和GBK会在跨库查询时出现乱码或转换开销。我们的规矩是所有实例统一用UTF8。-- 创建数据库时明确指定字符集CREATEDATABASEorder_db ENCODINGUTF8;1.2 每个应用独立SCHEMA不要把所有表都扔在public里。不同应用、不同模块用独立的SCHEMA隔离权限管理也方便。-- 为订单服务创建独立SCHEMACREATESCHEMAorder_svcAUTHORIZATIONorder_app;-- 设置默认SCHEMASETsearch_pathTOorder_svc,public;1.3 表的硬性约束单表列数不超过80列必须有主键或唯一约束外键必须建索引主外键类型要一致触发器能不用就不用-- 正确的建表示例CREATETABLEorder_svc.t_orders(order_id BIGSERIAL,order_noVARCHAR(32)NOTNULL,user_idINTEGERNOTNULL,amountDECIMAL(12,2)NOTNULL,statusSMALLINTDEFAULT0,created_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP,updated_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP,CONSTRAINTpk_orders_order_idPRIMARYKEY(order_id),CONSTRAINTuk_orders_order_noUNIQUE(order_no));1.4 大表处理策略金仓官方建议单表超过5000万条或100GB就要考虑分区或归档。我们在实践中把阈值定得更保守一些——2000万条就触发评估。-- 按月分区示例CREATETABLEorder_svc.t_orders_202501PARTITIONOForder_svc.t_ordersFORVALUESFROM(2025-01-01)TO(2025-02-01);1.5 大对象字段的处理图片、文件不要直接存数据库。金仓虽然支持BLOB/CLOB但大对象和业务数据混在一起IO会成为瓶颈。我们定的规则是文件存OSS或NAS数据库只存路径实在要存LOB单独建一张表存放通过外键关联-- 大对象单独存储CREATETABLEorder_svc.t_order_attachments(attach_id BIGSERIALPRIMARYKEY,order_idBIGINTNOTNULL,file_pathVARCHAR(256)NOTNULL,file_sizeINTEGER,-- 不要在大对象字段上建索引file_contentBLOB);二、命名规范让代码可读可维护命名混乱是很多项目的通病。一套清晰的命名规范能让新人接手时少花一半时间。2.1 表命名格式TB_ 应用名 模块名 表描述-- 示例TB_SHOP_ORDER-- 订单表TB_SHOP_PRODUCT-- 商品表TB_SHOP_USER-- 用户表2.2 索引命名-- 普通索引IDX_表名_字段1_字段2CREATEINDEXidx_order_user_idONt_orders(user_id);-- 唯一索引UID_表名_字段CREATEUNIQUEINDEXuid_order_order_noONt_orders(order_no);-- 主键PK_表名_主键列ALTERTABLEt_ordersADDCONSTRAINTpk_orders_order_idPRIMARYKEY(order_id);2.3 其他对象命名对象类型前缀示例视图V_v_order_summary序列SEQ_seq_order_id函数FUNC_func_calc_amount存储过程P_p_refresh_order临时表TMP_tmp_order_import_20250417_zhang一个重要提醒所有对象名长度不要超过30个字符。金仓对长对象名虽然支持但会给后续维护带来麻烦。三、字段设计选对类型比什么都重要3.1 类型选择的几个原则原则一用对类型别用字符存数字-- 错误用字符存日期create_timeVARCHAR(20)-- ❌-- 正确用DATE类型create_timeDATE-- ✅-- 错误用字符存金额amountVARCHAR(20)-- ❌-- 正确用DECIMALamountDECIMAL(12,2)-- ✅原则二选最小的够用类型-- 状态字段用SMALLINT2字节别用INTEGER4字节statusSMALLINTDEFAULT0-- 年龄用SMALLINT就够了ageSMALLINT-- 定长字符串用CHAR变长用VARCHARcountry_codeCHAR(2)-- 固定2位addressVARCHAR(200)-- 长度不固定原则三能用数值不用字符数值类型比较效率比字符串高得多。这个差异在大表关联查询时特别明显。3.2 填坑经验fillfactor的设置金仓有一个很实用的参数叫fillfactor控制每个数据页的填充率。默认是100表示写满。对于频繁UPDATE的表建议设为80CREATETABLEorder_svc.t_order_status_log(log_id BIGSERIAL,order_idBIGINTNOTNULL,old_statusSMALLINT,new_statusSMALLINT,change_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP)WITH(fillfactor80);为什么金仓的UPDATE本质上是标记旧行插入新行。如果页面满了新行只能放到其他页面导致读取时需要扫描多个页面。预留20%的空间可以让新行留在同一个页面内这就是HOT更新Heap-Only Tuple性能会好很多。3.3 冗余字段用空间换时间大表关联查询代价很高。适当冗余一些字段可以减少JOIN。-- 订单表里冗余用户姓名避免每次都要关联用户表CREATETABLEt_orders(order_id BIGSERIAL,user_idINTEGERNOTNULL,user_nameVARCHAR(64),-- 冗余字段amountDECIMAL(12,2));代价是更新用户姓名时要同步更新订单表。需要在设计时权衡。四、索引设计少而精4.1 索引数量的控制金仓官方建议单表索引不超过5个。我们内部的标准更严——核心表不超过3个。为什么每个索引都会增加INSERT、UPDATE、DELETE的开销。订单表每插入一条记录要维护主键索引、唯一索引、普通索引…索引越多写入越慢。4.2 索引字段的选择选择性原则把过滤效果最好的字段放前面。假设有两个字段status只有3种值每个值占30%user_id唯一值很多每个值占0.01%那么索引应该是(user_id, status)而不是(status, user_id)。避免冗余索引-- 已经有联合索引CREATEINDEXidx_user_statusONt_orders(user_id,status);-- 这个单字段索引就是冗余的因为联合索引已经能覆盖CREATEINDEXidx_user_idONt_orders(user_id);-- ❌ 不需要4.3 外键必须建索引这是金仓官方特别强调的如果子表外键没有索引父表删除记录时会锁住子表所有记录。-- 子表CREATETABLEt_order_items(item_id BIGSERIAL,order_idBIGINTNOTNULL,product_idINTEGERNOTNULL);-- 外键索引必须建CREATEINDEXidx_order_items_order_idONt_order_items(order_id);4.4 分区表的索引策略金仓的分区索引有个重要原则分区索引必须包含分区列且分区列要放在索引末尾。-- 按月分区的订单表CREATETABLEt_orders(order_id BIGSERIAL,order_noVARCHAR(32),created_dateDATENOTNULL-- 分区键)PARTITIONBYRANGE(created_date);-- 正确的分区索引分区键在最后CREATEINDEXidx_orders_order_noONt_orders(order_no,created_date);4.5 不同索引类型的使用场景金仓支持B-tree、Hash、GIN、GiST、BRIN等多种索引类型。日常开发中B-tree覆盖了90%的场景但有几种情况值得留意BRIN索引适合时间序列表日志、流水索引很小但查询效率不错。-- 日志表用BRIN索引几GB的数据索引可能只有几十MBCREATEINDEXidx_log_createdONt_logUSINGBRIN(created_date);GIN索引适合数组、JSONB字段和全文检索。-- JSONB字段的GIN索引CREATEINDEXidx_products_attrsONt_productsUSINGGIN(attributes);五、SQL编写规范5.1 绑定变量必须用高并发场景下不用绑定变量会导致SQL每次都要硬解析CPU会被占满。-- 错误拼接SQLEXECUTEIMMEDIATESELECT * FROM t_orders WHERE order_id ||v_id;-- 正确用绑定变量EXECUTEIMMEDIATESELECT * FROM t_orders WHERE order_id $1USINGv_id;5.2 避免隐式类型转换-- 假设user_id是INTEGER类型-- 错误传入字符串触发隐式转换SELECT*FROMt_ordersWHEREuser_id123;-- 正确传入数值SELECT*FROMt_ordersWHEREuser_id123;隐式转换会让索引失效这个坑踩一次就记住了。5.3 SELECT只取需要的列-- 错误SELECT *SELECT*FROMt_ordersWHEREorder_id12345;-- 正确只取需要的字段SELECTorder_no,amount,statusFROMt_ordersWHEREorder_id12345;在订单这种宽表上SELECT *会多读大量不需要的数据网络传输和内存占用都更大。5.4 COUNT(*) vs COUNT(列)-- 统计行数用COUNT(*)SELECTCOUNT(*)FROMt_ordersWHEREstatus1;-- 统计某列非NULL值数量用COUNT(列)SELECTCOUNT(user_id)FROMt_orders;COUNT(*)在金仓里优化得很好不要自己写成COUNT(1)或COUNT(主键)。六、连接池管理那些容易被忽视的坑6.1 连接数不是越多越好一个常见的误区并发高就调大max_connections。实际上连接数超过CPU核心数的10倍系统就会开始抖动。因为CPU大部分时间花在上下文切换上而不是真正处理请求。我们定的规则每个CPU核心不超过10个连接。32核的服务器连接数控制在300以内。6.2 防止会话泄漏会话泄漏是开发规范里最容易忽视的问题。异常处理不当连接没释放积少成多把连接池占满。// Java代码示例必须用try-with-resources或finally释放连接try(ConnectionconndataSource.getConnection();PreparedStatementstmtconn.prepareStatement(sql)){// 执行SQL}catch(SQLExceptione){// 记录日志确保连接被关闭log.error(Database error,e);}6.3 登录/注销策略不要为每个SQL请求都创建新连接。连接建立的开销很大TCP握手、认证、分配内存。正确做法使用连接池让连接复用。七、写在最后这套规范推行了半年效果很明显P1级故障从每月3-4次降到了半年1次新人接手项目的上手时间也从2周缩短到了3天。规范的目的是统一认知、减少踩坑不是给开发设障碍。每一条规则的背后都对应着一次真实的线上故障。希望这份总结对你有帮助。