MySQL与外部应用共享SQL静态值的最优方案及性能问题咨询

问题1：这是实现数据库与后端两个平台间静态值共享的最优方案吗？ #

该方案不是最优选择，仅适合对性能要求极低、静态值调整非常频繁的场景。
这个方案的优势是实现了静态值单源维护，避免了前后端枚举定义不一致的问题，逻辑简单易维护。但缺陷非常明显：

• 存在严重的性能隐患，正如你遇到的情况，在复杂查询中嵌入该函数会导致执行效率大幅下降
• 阻碍索引生效：如果ourc.record_state这类过滤列建有索引，当查询条件中使用存储函数返回值作为匹配值时，MySQL优化器大概率无法将函数返回值识别为常量，会放弃走索引转而执行全表扫描
• 额外增加数据库负载：每次函数调用都会执行一次对global_custom_setting表的查询，高并发场景下会产生大量无意义的小查询，挤占正常业务的数据库资源

更推荐的替代方案：

• 优先选择前后端同步枚举的方案：后端JS代码维护枚举常量，数据库对应字段添加CHECK约束限制合法取值，上线前通过CI校验脚本自动比对两端枚举值是否一致，该方案完全没有运行时性能损耗，是静态值场景的首选
• 如果必须保留数据库单源存储静态值的逻辑，不要在SQL语句内嵌入函数调用，提前在业务代码中调用一次函数拿到对应静态值，再作为常量参数传入SQL，这样整个查询流程仅执行一次函数逻辑，不会产生额外损耗
• 如果一定要在数据库层面封装取值逻辑，可以把global_custom_setting表改造为内存表，或者用MySQL用户变量提前缓存常用的静态值，降低每次查询的开销

问题2：为什么MySQL没有将该函数识别为DETERMINISTIC类型仅做一次取值？ #

你对MySQL中DETERMINISTIC关键字的作用存在认知偏差：

1. DETERMINISTIC只是开发者向MySQL优化器提交的承诺声明，不是强制优化器进行常量折叠的指令。该声明的生效前提是「相同输入永远返回相同输出，且输出完全不受函数外部状态影响」，你的函数明显不符合该前提：函数返回值完全依赖global_custom_setting表的数据，只要表中数据发生变化，相同的入参就会返回不同的结果，所以这个DETERMINISTIC的声明本身是不符合规范的。
2. 即便声明符合规范，只要函数内部包含READS SQL DATA属性、存在查表逻辑，MySQL优化器就不会在查询执行前提前计算函数值。因为优化器无法保证查询执行过程中，函数依赖的表数据不会发生变化，所以只能在每一行数据匹配时都调用一次函数，相当于主查询返回多少行，就要执行多少次函数内的SELECT查询，性能自然会出现显著下降。
3. 补充说明：MySQL仅对完全不依赖外部状态的纯计算类DETERMINISTIC函数才会触发常量折叠优化，比如SELECT ABS(-10) FROM user，优化器会提前把ABS(-10)计算为10再执行查询，你的场景显然不满足这个前提。

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