生成列与触发器维护列的性能优化对比：哪种方案更优？

我有一张表，其中有个char(19)类型的列P。在部分查询场景中，需要检查一个10字符的变量是否匹配P的末尾字符串，之前用的查询条件是where P like CONCAT('%',variableName)，但性能很差。

优化思路是新增一个自动维护的char(10)类型列P10，之后用where P10 like variableName查询（配合索引能大幅提升性能）。现在想从性能角度对比两种实现方式：

• 方案1：使用生成列，执行语句：Alter TABLE MyTable add column P10 char(10) GENERATED ALWAYS AS substr(P,9,10)
• 方案2：通过触发器事件将P的子串填充到P10中

从性能层面看，方案1（生成列）更优，核心原因如下：

• 写入性能：触发器需要在每次P列进行插入/更新操作时额外执行触发逻辑，会增加事务的额外开销；而生成列是数据库原生支持的计算列，内部有专门的优化处理，写入时的性能损耗远低于触发器，也不会额外增加事务的复杂度。
• 读取与索引性能：生成列的值由数据库直接维护，读取时和普通列完全一致；虽然触发器填充的列也是存储实际值，但生成列在索引支持上更原生，部分数据库会对生成列的索引做更彻底的优化，避免触发器可能带来的一致性问题导致的索引额外校验步骤。
• 间接性能影响：生成列无需手动编写触发器逻辑，不会出现触发器漏写、逻辑错误的情况，数据库会自动保证P10与P的一致性，减少后续因数据不一致引发的性能问题（比如索引失效、无效查询）。

补充：只要你的数据库支持生成列（比如MySQL 5.7及以上、PostgreSQL 12及以上版本），优先选择生成列方案。只有当数据库不支持生成列时，再考虑触发器方案。

内容的提问来源于stack exchange，提问作者Thomas