接口
http://demo.sqle.actionsky.com:8889/sqle/v1/rules?filter_db_type=MySQL
{
"code": 0,
"message": "ok",
"data": [
{
"rule_name": "all_check_prepare_statement_placeholders",
"desc": "绑定的变量个数不建议超过阈值",
"annotation": "因为过度使用绑定变量会增加查询的复杂度，从而降低查询性能。过度使用绑定变量还会增加维护成本。默认阈值:100",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "100",
"desc": "最大绑定变量数量",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "all_check_where_is_invalid",
"desc": "禁止使用没有WHERE条件或者WHERE条件恒为TRUE的SQL",
"annotation": "SQL缺少WHERE条件在执行时会进行全表扫描产生额外开销，建议在大数据量高并发环境下开启，避免影响数据库查询性能",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_avoid_event",
"desc": "禁止使用event",
"annotation": "使用event会增加数据库的维护难度和依赖性，并且也会造成安全问题。",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_avoid_full_text",
"desc": "禁止使用全文索引",
"annotation": "全文索引的使用会增加存储开销，并对写操作性能产生一定影响。",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_avoid_geometry",
"desc": "禁止使用空间字段和空间索引",
"annotation": "使用空间字段和空间索引会增加存储需求，对数据库性能造成一定影响",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_avoid_text",
"desc": "使用TEXT 类型的字段建议和原表进行分拆，与原表主键单独组成另外一个表进行存放",
"annotation": "将TEXT类型的字段与原表主键分拆成另一个表可以提高数据库性能和查询速度，减少不必要的 I/O 操作。",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_all_index_not_null_constraint",
"desc": "建议为至少一个索引添加非空约束",
"annotation": "所有索引字段均未做非空约束，请确认下表索引规划的合理性。",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_alter_table_need_merge",
"desc": "存在多条对同一个表的修改语句，建议合并成一个ALTER语句",
"annotation": "避免多次 TABLE REBUILD 带来的消耗、以及对线上业务的影响",
"level": "notice",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_auto_increment",
"desc": "表的初始AUTO_INCREMENT值建议为0",
"annotation": "创建表时AUTO_INCREMENT设置为0则自增从1开始，可以避免数据空洞。例如在导出表结构DDL时，表结构内AUTO_INCREMENT通常为当前的自增值，如果建表时没有把AUTO_INCREMENT设置为0，那么通过该DDL进行建表操作会导致自增值从一个无意义数字开始。",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_auto_increment_field_num",
"desc": "建表时，自增字段只能设置一个",
"annotation": "MySQL InnoDB，MyISAM 引擎不允许存在多个自增字段，设置多个自增字段会导致上线失败。",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_bigint_instead_of_decimal",
"desc": "建议用BIGINT类型代替DECIMAL",
"annotation": "因为CPU不支持对DECIMAL的直接运算，只是MySQL自身实现了DECIMAL的高精度计算，但是计算代价高，并且存储同样范围值的时候，空间占用也更多；使用BIGINT代替DECIMAL，可根据小数的位数乘以相应的倍数，即可达到精确的浮点存储计算，避免DECIMAL计算代价高的问题",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_char_length",
"desc": "禁止char, varchar类型字段字符长度总和超过阈值",
"annotation": "使用过长或者过多的varchar，char字段可能会增加业务逻辑的复杂性；如果字段平均长度过大时，会占用更多的存储空间。",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "2000",
"desc": "字符长度",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_collation_database",
"desc": "建议使用规定的数据库排序规则",
"annotation": "通过该规则约束全局的数据库排序规则，避免创建非预期的数据库排序规则，防止业务侧出现排序结果非预期等问题。建议项目内库表使用统一的字符集和字符集排序，部分连表查询的情况下字段的字符集或排序规则不一致可能会导致索引失效且不易发现",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "utf8mb4_0900_ai_ci",
"desc": "数据库排序规则",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_blob_default_is_not_null",
"desc": "BLOB 和 TEXT 类型的字段默认值只能为NULL",
"annotation": "在SQL_MODE严格模式下BLOB 和 TEXT 类型无法设置默认值，如插入数据不指定值，字段会被设置为NULL",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_blob_notice",
"desc": "不建议使用 BLOB 或 TEXT 类型",
"annotation": "BLOB 或 TEXT 类型消耗大量的网络和IO带宽，同时在该表上的DML操作都会变得很慢",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_blob_with_not_null",
"desc": "BLOB 和 TEXT 类型的字段不建议设置为 NOT NULL",
"annotation": "BLOB 和 TEXT 类型的字段无法指定默认值，如插入数据不指定字段默认为NULL，如果添加了 NOT NULL 限制，写入数据时又未对该字段指定值会导致写入失败",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_char_length",
"desc": "CHAR长度大于20时，必须使用VARCHAR类型",
"annotation": "VARCHAR是变长字段，存储空间小，可节省存储空间，同时相对较小的字段检索效率显然也要高些",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_enum_notice",
"desc": "不建议使用 ENUM 类型",
"annotation": "ENUM类型不是SQL标准，移植性较差，后期如修改或增加枚举值需重建整张表，代价较大，且无法通过字面量值进行排序",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_not_null",
"desc": "表字段建议有NOT NULL约束",
"annotation": "表字段建议有 NOT NULL 约束，可确保数据的完整性，防止插入空值，提升查询准确性。",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_quantity",
"desc": "表的列数不建议超过阈值",
"annotation": "避免在OLTP系统上做宽表设计，后期对性能影响很大；具体规则阈值可根据业务需求调整，默认值：40",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "40",
"desc": "最大列数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_quantity_in_pk",
"desc": "主键包含的列数不建议超过阈值",
"annotation": "主建中的列过多，会导致二级索引占用更多的空间，同时增加索引维护的开销；具体规则阈值可根据业务需求调整，默认值：2",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "2",
"desc": "最大列数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_set_notice",
"desc": "不建议使用 SET 类型",
"annotation": "集合的修改需要重新定义列，后期修改的代价大，建议在业务层实现",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_timestamp_without_default",
"desc": "TIMESTAMP 类型的列必须添加默认值",
"annotation": "TIMESTAMP添加默认值，可避免出现全为0的日期格式与业务预期不符",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_type_integer",
"desc": "整型定义建议采用 INT(10) 或 BIGINT(20)",
"annotation": "INT(M) 或 BIGINT(M)，M 表示最大显示宽度，可存储最大值的宽度分别为10、20，采用 INT(10) 或 BIGINT(20)可避免发生显示截断的可能",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_without_comment",
"desc": "列建议添加注释",
"annotation": "列添加注释能够使列的意义更明确，方便日后的维护",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_column_without_default",
"desc": "除了自增列及大字段列之外，每个列都必须添加默认值",
"annotation": "列添加默认值，可避免列为NULL值时对查询的影响",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_composite_index_distinction",
"desc": "建议在组合索引中将区分度高的字段靠前放",
"annotation": "将区分度高的字段靠前放置在组合索引中有助于提高索引的查询性能，因为它能更快地减小数据范围，提高检索效率。",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_composite_index_max",
"desc": "复合索引的列数量不建议超过阈值",
"annotation": "复合索引会根据索引列数创建对应组合的索引，列数越多，创建的索引越多，每个索引都会增加磁盘空间的开销，同时增加索引维护的开销；具体规则阈值可以根据业务需求调整，默认值：3",
"level": "notice",
"type": "索引规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "3",
"desc": "最大索引列数量",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_create_function",
"desc": "禁止使用自定义函数",
"annotation": "自定义函数，维护较差，且依赖性高会导致SQL无法跨库使用",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_create_procedure",
"desc": "禁止使用存储过程",
"annotation": "存储过程在一定程度上会使程序难以调试和拓展，各种数据库的存储过程语法相差很大，给将来的数据库移植带来很大的困难，且会极大的增加出现BUG的概率",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_create_time_column",
"desc": "建议建表DDL包含创建时间字段且默认值为CURRENT_TIMESTAMP",
"annotation": "使用CREATE_TIME字段，有利于问题查找跟踪和检索数据，同时避免后期对数据生命周期管理不便 ，默认值为CURRENT_TIMESTAMP可保证时间的准确性",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "CREATE_TIME",
"desc": "创建时间字段名",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_create_trigger",
"desc": "禁止使用触发器",
"annotation": "触发器难以开发和维护，不能高效移植，且在复杂的逻辑以及高并发下，容易出现死锁影响业务",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_create_view",
"desc": "禁止使用视图",
"annotation": "视图的查询性能较差，同时基表结构变更，需要对视图进行维护，如果视图可读性差且包含复杂的逻辑，都会增加维护的成本",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_database_suffix",
"desc": "建议数据库名称使用固定后缀结尾",
"annotation": "通过配置该规则可以规范指定业务的数据库命名规则，具体命名规范可以自定义设置，默认提示值：_DB",
"level": "notice",
"type": "命名规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "_DB",
"desc": "数据库名称后缀",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_decimal_type_column",
"desc": "精确浮点数建议使用DECIMAL",
"annotation": "对于浮点数运算，DECIMAL精确度较高",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_field_not_null_must_contain_default_value",
"desc": "建议字段约束为NOT NULL时带默认值",
"annotation": "如存在NOT NULL且不带默认值的字段，INSERT时不包含该字段，会导致插入报错",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_full_width_quotation_marks",
"desc": "DDL语句中不建议使用中文全角引号",
"annotation": "建议开启此规则，可避免MySQL会将中文全角引号识别为命名的一部分，执行结果与业务预期不符",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_index_column_with_blob",
"desc": "禁止将BLOB类型的列加入索引",
"annotation": "BLOB类型属于大字段类型，作为索引会占用很大的存储空间",
"level": "error",
"type": "索引规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_index_count",
"desc": "索引个数建议不超过阈值",
"annotation": "在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销，太多与不充分、不正确的索引对性能都毫无益处；具体规则阈值可以根据业务需求调整，默认值：5",
"level": "notice",
"type": "索引规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "5",
"desc": "最大索引个数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_index_not_null_constraint",
"desc": "索引字段需要有非空约束",
"annotation": "索引字段上如果没有非空约束，则表记录与索引记录不会完全映射。",
"level": "warn",
"type": "索引规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_index_option",
"desc": "建议索引字段对区分度大于阈值",
"annotation": "选择区分度高的字段作为索引，可快速定位数据；区分度太低，无法有效利用索引，甚至可能需要扫描大量数据页，拖慢SQL；具体规则阈值可以根据业务需求调整，默认值：70",
"level": "notice",
"type": "索引优化",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "70",
"desc": "可选择性（百分比）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_index_prefix",
"desc": "建议普通索引使用固定前缀",
"annotation": "通过配置该规则可以规范指定业务的索引命名规则，具体命名规范可以自定义设置，默认提示值：idx_",
"level": "error",
"type": "命名规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "idx_",
"desc": "索引前缀",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_index_too_many",
"desc": "单字段上的索引数量不建议超过阈值",
"annotation": "单字段上存在过多索引，一般情况下这些索引都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大；具体规则阈值可以根据业务需求调整，默认值：2",
"level": "warn",
"type": "索引规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "2",
"desc": "单字段的索引数最大值",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_indexes_exist_before_creat_constraints",
"desc": "对字段创建约束前，建议先创建索引",
"annotation": "创建约束前，先行创建索引，约束可作用于二级索引，避免全表扫描，提高性能",
"level": "notice",
"type": "索引规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_is_exist_limit_offset",
"desc": "使用分页查询时，避免使用偏移量",
"annotation": "例如：LIMIT N OFFSET M 或 LIMIT M,N。当偏移量m过大的时候，查询效率会很低，因为MySQL是先查出m+n个数据，然后抛弃掉前m个数据；对于有大数据量的MySQL表来说，使用LIMIT分页存在很严重的性能问题",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_object_name_is_upper_and_lower_letter_mixed",
"desc": "数据库对象命名不建议大小写字母混合",
"annotation": "数据库对象命名规范，不推荐采用大小写混用的形式建议词语之间使用下划线连接，提高代码可读性",
"level": "notice",
"type": "命名规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_object_name_length",
"desc": "表名、列名、索引名的长度不建议超过阈值",
"annotation": "通过配置该规则可以规范指定业务的对象命名长度，具体长度可以自定义设置，默认最大长度：64。是MySQL规定标识符命名最大长度为64字节",
"level": "error",
"type": "命名规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "64",
"desc": "最大长度（字节）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_object_name_using_cn",
"desc": "数据库对象命名只能使用英文、下划线或数字，首字母必须是英文",
"annotation": "通过配置该规则可以规范指定业务的数据对象命名规则",
"level": "error",
"type": "命名规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_object_name_using_keyword",
"desc": "数据库对象命名禁止使用保留字",
"annotation": "通过配置该规则可以规范指定业务的数据对象命名规则，避免发生冲突，以及混淆",
"level": "error",
"type": "命名规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_pk_name",
"desc": "建议主键命名为\"PK_表名\"",
"annotation": "通过配置该规则可以规范指定业务的主键命名规则",
"level": "notice",
"type": "命名规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_pk_not_exist",
"desc": "表必须有主键",
"annotation": "主键使数据达到全局唯一，可提高数据检索效率",
"level": "error",
"type": "索引规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_pk_prohibit_auto_increment",
"desc": "不建议主键使用自增",
"annotation": "后期维护相对不便，过于依赖数据库自增机制达到全局唯一，不易拆分，容易造成主键冲突",
"level": "warn",
"type": "索引规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_pk_without_auto_increment",
"desc": "主键建议使用自增",
"annotation": "自增主键，数字型速度快，而且是增量增长，占用空间小，更快速的做数据插入操作，避免增加维护索引的开销",
"level": "error",
"type": "索引规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_pk_without_bigint_unsigned",
"desc": "主键建议使用 BIGINT 无符号类型，即 BIGINT UNSIGNED",
"annotation": "BIGINT UNSIGNED拥有更大的取值范围，建议开启此规则，避免发生溢出",
"level": "error",
"type": "索引规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_redundant_index",
"desc": "不建议创建冗余索引",
"annotation": "MySQL需要单独维护重复的索引，冗余索引增加维护成本，并且优化器在优化查询时需要逐个进行代价计算，影响查询性能",
"level": "error",
"type": "索引优化",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_table_character_set",
"desc": "建议使用指定数据库字符集",
"annotation": "通过该规则约束全局的数据库字符集，避免创建非预期的字符集，防止业务侧出现“乱码”等问题。建议项目内库表使用统一的字符集和字符集排序，部分连表查询的情况下字段的字符集或排序规则不一致可能会导致索引失效且不易发现",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "utf8mb4",
"desc": "数据库字符集",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_table_db_engine",
"desc": "建议使用指定数据库引擎",
"annotation": "通过配置该规则可以规范指定业务的数据库引擎，具体规则可以自定义设置。默认值是INNODB，INNODB 支持事务，支持行级锁，更好的恢复性，高并发下性能更好",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "Innodb",
"desc": "数据库引擎",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_table_partition",
"desc": "不建议使用分区表相关功能",
"annotation": "分区表在物理上表现为多个文件，在逻辑上表现为一个表，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据",
"level": "notice",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_table_rows",
"desc": "表行数超过阈值，建议对表进行拆分",
"annotation": "当表行数超过阈值时，对表进行拆分有助于提高数据库性能和查询速度。",
"level": "warn",
"type": "使用建议",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1000",
"desc": "表行数（万）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_table_size",
"desc": "不建议对数据量过大的表执行DDL操作",
"annotation": "大表执行DDL，耗时较久且负载较高，长时间占用锁资源，会影响数据库性能；具体规则阈值可以根据业务需求调整，默认值：1024",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1024",
"desc": "表空间大小（MB）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_table_without_comment",
"desc": "表建议添加注释",
"annotation": "表添加注释能够使表的意义更明确，方便日后的维护",
"level": "notice",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_table_without_if_not_exists",
"desc": "新建表建议加入 IF NOT EXISTS，保证重复执行不报错",
"annotation": "新建表如果表已经存在，不添加IF NOT EXISTS CREATE执行SQL会报错，建议开启此规则，避免SQL实际执行报错",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_transaction_isolation_level",
"desc": "事物隔离级别建议设置成RC",
"annotation": "RC避免了脏读的现象，但没有解决幻读的问题；使用RR，能避免幻读，但是由于引入间隙锁导致加锁的范围可能扩大，从而会影响并发，还容易造成死锁，所以在大多数业务场景下，幻读出现的机率较少，RC基本上能满足业务需求",
"level": "notice",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_unique_index",
"desc": "建议UNIQUE索引名使用 IDX_UK_表名_字段名",
"annotation": "通过配置该规则可以规范指定业务的UNIQUE索引命名规则",
"level": "error",
"type": "命名规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_unique_index_prefix",
"desc": "建议UNIQUE索引使用固定前缀",
"annotation": "通过配置该规则可以规范指定业务的UNIQUE索引命名规则，具体命名规范可以自定义设置，默认提示值：uniq_",
"level": "error",
"type": "命名规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "uniq_",
"desc": "索引前缀",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_update_time_column",
"desc": "建表DDL需要包含更新时间字段且默认值为CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
"annotation": "使用更新时间字段，有利于问题查找跟踪和检索数据，同时避免后期对数据生命周期管理不便 ，默认值为UPDATE_TIME可保证时间的准确性",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "UPDATE_TIME",
"desc": "更新时间字段名",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_check_varchar_size",
"desc": "定义VARCHAR 长度时不建议大于阈值",
"annotation": "MySQL建立索引时没有限制索引的大小，索引长度会默认采用的该字段的长度，VARCHAR 定义长度越长建立的索引存储大小越大；具体规则阈值可以根据业务需求调整，默认值：1024",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1024",
"desc": "VARCHAR最大长度",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_disable_alter_field_use_first_and_after",
"desc": "ALTER表字段禁止使用FIRST,AFTER",
"annotation": "FIRST,AFTER 的ALTER操作通过COPY TABLE的方式完成，对业务影响较大",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_disable_drop_statement",
"desc": "禁止除索引外的DROP操作",
"annotation": "DROP是DDL，数据变更不会写入日志，无法进行回滚；建议开启此规则，避免误删除操作",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_disable_fk",
"desc": "禁止使用外键",
"annotation": "外键在高并发场景下性能较差，容易造成死锁，同时不利于后期维护（拆分、迁移）",
"level": "error",
"type": "索引规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_disable_type_timestamp",
"desc": "不建议使用TIMESTAMP字段",
"annotation": "TIMESTAMP 有最大值限制（'2038-01-19 03:14:07' UTC），且会时区转换的问题",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_ghost_min_size",
"desc": "改表时，表空间超过指定大小(MB)时使用gh-ost上线",
"annotation": "开启该规则后会自动对大表的DDL操作使用gh-ost 工具进行在线改表；直接对大表进行DDL变更时可能会导致长时间锁表问题，影响业务可持续性。具体对大表定义的阈值可以根据业务需求调整，默认值：1024",
"level": "normal",
"type": "全局配置",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1024",
"desc": "表空间大小（MB）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_hint_drop_column",
"desc": "禁止进行删除列的操作",
"annotation": "业务逻辑与删除列依赖未完全消除，列被删除后可能导致程序异常（无法正常读写）的情况；开启该规则，SQLE将提醒删除列为高危操作",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_hint_drop_foreign_key",
"desc": "禁止进行删除外键的操作",
"annotation": "删除已有约束会影响已有业务逻辑；开启该规则，SQLE将提醒删除外键为高危操作",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_hint_drop_primary_key",
"desc": "禁止进行删除主键的操作",
"annotation": "删除已有约束会影响已有业务逻辑；开启该规则，SQLE将提醒删除主键为高危操作",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_hint_update_table_charset_will_not_update_field_charset",
"desc": "不建议修改表的默认字符集",
"annotation": "修改表的默认字符集，只会影响后续新增的字段，不会修表已有字段的字符集；如需修改整张表所有字段的字符集建议开启此规则",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_not_allow_renaming",
"desc": "禁止使用RENAME或CHANGE对表名字段名进行修改",
"annotation": "RENAME/CHANGE 表名/列名会对线上业务不停机发布造成影响，如需这种操作应当DBA手工干预",
"level": "error",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_osc_min_size",
"desc": "改表时，表空间超过指定大小(MB)审核时输出osc改写建议",
"annotation": "开启该规则后会对大表的DDL语句给出 pt-osc工具的改写建议【需要参考命令进行手工执行，后续会支持自动执行】；直接对大表进行DDL变更时可能会导致长时间锁表问题，影响业务可持续性。具体对大表定义的阈值可以根据业务需求调整，默认值：1024",
"level": "normal",
"type": "全局配置",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1024",
"desc": "表空间大小（MB）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "ddl_table_column_charset_same",
"desc": "建议列与表使用同一个字符集",
"annotation": "统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_avoid_where_equal_null",
"desc": "WHERE子句中禁止将NULL值与其他字段或值进行比较运算",
"annotation": "NULL在SQL中属于特殊值，无法与普通值进行比较。例如：column = NULL恒为false，即使column存在null值也不会查询出来，所以column = NULL应该写为column is NULL",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_affected_rows",
"desc": "UPDATE/DELETE操作影响行数不建议超过阈值",
"annotation": "如果 DML 操作影响行数过多，会导致查询性能下降，因为需要扫描更多的数据。",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "10000",
"desc": "最大影响行数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_aggregate",
"desc": "不建议使用聚合函数",
"annotation": "不建议使用SQL聚合函数,是为了确保查询的简单性、高性能和数据一致性。",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_alias",
"desc": "别名不建议与表或列的名字相同",
"annotation": "表或列的别名与其真实名称相同, 这样的别名会使得查询更难去分辨",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_batch_insert_lists_max",
"desc": "单条INSERT语句，建议批量插入不超过阈值",
"annotation": "避免大事务，以及降低发生回滚对业务的影响；具体规则阈值可以根据业务需求调整，默认值：100",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "100",
"desc": "最大插入行数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_explain_access_type_all",
"desc": "全表扫描时，扫描行数不建议超过指定行数（默认值：10000）",
"annotation": "全表扫描时，扫描行数不建议超过指定行数是为了避免性能问题；具体规则阈值可以根据业务需求调整，默认值：10000；如果设置为0，全表扫描都会触发规则",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "10000",
"desc": "最大扫描行数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_explain_extra_using_filesort",
"desc": "不建议使用文件排序",
"annotation": "大数据量的情况下，文件排序意味着SQL性能较低，会增加OS的开销，影响数据库性能",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_explain_extra_using_index_for_skip_scan",
"desc": "不建议对表进行索引跳跃扫描",
"annotation": "索引扫描是跳跃扫描，未遵循最左匹配原则，可能降低索引的使用效率，影响查询性能",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_explain_extra_using_temporary",
"desc": "不建议使用临时表",
"annotation": "大数据量的情况下，临时表意味着SQL性能较低，会增加OS的开销，影响数据库性能",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_explain_full_index_scan",
"desc": "不建议对表进行全索引扫描",
"annotation": "在数据量大的情况下索引全扫描严重影响SQL性能。",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_fuzzy_search",
"desc": "禁止使用全模糊搜索或左模糊搜索",
"annotation": "使用全模糊搜索或左模糊搜索将导致查询无法使用索引，导致全表扫描",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_in_query_limit",
"desc": "WHERE条件内IN语句中的参数个数不能超过阈值",
"annotation": "当IN值过多时，有可能会导致查询进行全表扫描，使得MySQL性能急剧下降；具体规则阈值可以根据业务需求调整，默认值：50",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "50",
"desc": "in语句参数最大个数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_index_selectivity",
"desc": "建议连库查询时，确保SQL执行计划中使用的索引区分度大于阈值",
"annotation": "确保SQL执行计划中使用的高索引区分度，有助于提升查询性能并优化查询效率。",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "70",
"desc": "可选择性（百分比）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_insert_columns_exist",
"desc": "INSERT 语句需要指定COLUMN",
"annotation": "当表结构发生变更，INSERT请求不明确指定列名，会发生插入数据不匹配的情况；建议开启此规则，避免插入结果与业务预期不符",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_insert_select",
"desc": "不建议使用INSERT ... SELECT",
"annotation": "使用 INSERT ... SELECT 在默认事务隔离级别下，可能会导致对查询的表施加表级锁。",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_is_after_union_distinct",
"desc": "建议使用UNION ALL,替代UNION",
"annotation": "UNION会按照字段的顺序进行排序同时去重，UNION ALL只是简单的将两个结果合并后就返回，从效率上看，UNION ALL 要比UNION快很多；如果合并的两个结果集中允许包含重复数据且不需要排序时的话，建议开启此规则，使用UNION ALL替代UNION",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_join_field_character_set_Collation",
"desc": "连接表字段的字符集和排序规则必须一致",
"annotation": "连接表字段的字符集和排序规则一致可避免数据不一致和查询错误，确保连接操作正确执行。",
"level": "error",
"type": "索引失效",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_join_field_type",
"desc": "建议JOIN字段类型保持一致",
"annotation": "JOIN字段类型不一致会导致类型不匹配发生隐式准换，建议开启此规则，避免索引失效",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_join_field_use_index",
"desc": "JOIN字段必须包含索引",
"annotation": "JOIN字段包含索引可提高连接操作的性能和查询速度。",
"level": "error",
"type": "索引失效",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_join_has_on",
"desc": "建议连接操作指定连接条件",
"annotation": "指定连接条件可以确保连接操作的正确性和可靠性，如果没有指定连接条件，可能会导致连接失败或连接不正确的情况。",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_limit_must_exist",
"desc": "建议DELETE/UPDATE 语句带有LIMIT条件",
"annotation": "LIMIT条件可以降低写错 SQL 的代价（删错数据），同时避免长事务影响业务",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_limit_offset_num",
"desc": "不建议LIMIT的偏移OFFSET大于阈值",
"annotation": "因为OFFSET指定了结果集的起始位置，如果起始位置过大，那么 MySQL 需要处理更多的数据才能返回结果集，这可能会导致查询性能下降。",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "100",
"desc": "offset 大小",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_check_math_computation_or_func_on_index",
"desc": "禁止对索引列进行数学运算和使用函数",
"annotation": "对索引列进行数学运算和使用函数会导致索引失效，从而导致全表扫描，影响查询性能。",
"level": "error",
"type": "索引失效",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_check_needless_func",
"desc": "避免使用不必要的内置函数",
"annotation": "通过配置该规则可以指定业务中需要禁止使用的内置函数，使用内置函数可能会导致SQL无法走索引或者产生一些非预期的结果。实际需要禁用的函数可通过规则设置",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "sha(),sqrt(),md5()",
"desc": "指定的函数集合（逗号分割）",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_not_equal_symbol",
"desc": "建议使用'\u003c\u003e'代替'!='",
"annotation": "'!=' 是非标准的运算符，'\u003c\u003e' 才是SQL中标准的不等于运算符",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_check_number_of_join_tables",
"desc": "使用JOIN连接表查询建议不超过阈值",
"annotation": "表关联越多，意味着各种驱动关系组合就越多，比较各种结果集的执行成本的代价也就越高，进而SQL查询性能会大幅度下降；具体规则阈值可以根据业务需求调整，默认值：3",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "3",
"desc": "最大连接表个数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_order_by_field_length",
"desc": "禁止对长字段排序",
"annotation": "对例如VARCHAR(2000)这样的长字段进行ORDER BY、DISTINCT、GROUP BY、UNION之类的操作，会引发排序，有性能隐患",
"level": "error",
"type": "使用建议",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "2000",
"desc": "可排序字段的最大长度",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_same_table_joined_multiple_times",
"desc": "不建议对同一张表连接多次",
"annotation": "如果对单表查询多次，会导致查询性能下降。",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_scan_rows",
"desc": "扫描行数超过阈值，筛选条件必须带上主键或者索引",
"annotation": "筛选条件必须带上主键或索引可降低数据库查询的时间复杂度，提高查询效率。",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "10",
"desc": "扫描行数量（万）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_select_for_update",
"desc": "不建议使用SELECT FOR UPDATE",
"annotation": "SELECT FOR UPDATE 会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞，在高并发下，容易造成数据库大量锁等待，影响数据库查询性能",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_select_limit",
"desc": "SELECT 语句需要带LIMIT",
"annotation": "如果查询的扫描行数很大，可能会导致优化器选择错误的索引甚至不走索引；具体规则阈值可以根据业务需求调整，默认值：1000",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1000",
"desc": "最大查询行数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_select_rows",
"desc": "查询数据量超过阈值，筛选条件必须带上主键或者索引",
"annotation": "筛选条件必须带上主键或索引可提高查询性能和减少全表扫描的成本。",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "10",
"desc": "查询数据量（万）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_select_with_order_by",
"desc": "SELECT 语句不能有ORDER BY",
"annotation": "ORDER BY 对查询性能影响较大，同时不便于优化维护，建议将排序部分放到业务处理",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_sort_direction",
"desc": "不建议在 ORDER BY 语句中对多个不同条件使用不同方向的排序",
"annotation": "在 MySQL 8.0 之前当 ORDER BY 多个列指定的排序方向不同时将无法使用已经建立的索引。在MySQL8.0 之后可以建立对应的排序顺序的联合索引来优化",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_spaces_around_the_string",
"desc": "引号中的字符串开头或结尾不建议包含空格",
"annotation": "字符串前后存在空格将可能导致查询判断逻辑出错，如在MySQL 5.5中'a'和'a '在查询中被认为是相同的值",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_sql_injection_func",
"desc": "不建议使用常见 SQL 注入函数",
"annotation": "攻击者通过SQL注入，可未经授权可访问数据库中的数据，存在盗取用户信息，造成用户数据泄露等安全漏洞问题",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_sql_length",
"desc": "建议将过长的SQL分解成几个简单的SQL",
"annotation": "过长的SQL可读性较差，难以维护，且容易引发性能问题；具体规则阈值可以根据业务需求调整，默认值：1024",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1024",
"desc": "SQL最大长度",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_sub_query_depth",
"desc": "子查询嵌套层数不建议超过阈值",
"annotation": "子查询嵌套层数超过阈值，有些情况下，子查询并不能使用到索引。同时对于返回结果集比较大的子查询，会产生大量的临时表，消耗过多的CPU和IO资源，产生大量的慢查询",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "3",
"desc": "子查询嵌套层数不建议超过阈值",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_subquery_limit",
"desc": "不建议在子查询中使用LIMIT",
"annotation": "部分MySQL版本不支持在子查询中进行'LIMIT \u0026 IN/ALL/ANY/SOME'",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_table_size",
"desc": "不建议对数据量过大的表执行DML操作",
"annotation": "DML操作大表，耗时较久且负载较高，容易影响数据库性能；具体规则阈值可以根据业务需求调整，默认值：1024",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1024",
"desc": "表空间大小（MB）",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_update_or_delete_has_where",
"desc": "建议UPDATE/DELETE操作使用WHERE条件",
"annotation": "因为这些语句的目的是修改数据库中的数据，需要使用 WHERE 条件来过滤需要更新或删除的记录，以确保数据的正确性。另外，使用 WHERE 条件还可以提高查询性能。",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_using_index",
"desc": "SQL查询条件需要走索引",
"annotation": "使用索引可以显著提高SQL查询的性能。",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_where_exist_func",
"desc": "避免对条件字段使用函数操作",
"annotation": "对条件字段做函数操作，可能会破坏索引值的有序性，导致优化器选择放弃走索引，使查询性能大幅度降低",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_where_exist_implicit_conversion",
"desc": "不建议在WHERE条件中使用与过滤字段不一致的数据类型",
"annotation": "WHERE条件中使用与过滤字段不一致的数据类型会引发隐式数据类型转换，导致查询有无法命中索引的风险，在高并发、大数据量的情况下，不走索引会使得数据库的查询性能严重下降",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_check_where_exist_not",
"desc": "不建议对条件字段使用负向查询",
"annotation": "使用负向查询，将导致全表扫描，出现慢SQL",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_where_exist_null",
"desc": "不建议对条件字段使用 NULL 值判断",
"annotation": "使用 IS NULL 或 IS NOT NULL 可能导致查询放弃使用索引而进行全表扫描",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_check_where_exist_scalar_sub_queries",
"desc": "不建议使用标量子查询",
"annotation": "标量子查询存在多次访问同一张表的问题，执行开销大效率低，可使用LEFT JOIN 替代标量子查询",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_check_with_limit",
"desc": "DELETE/UPDATE 语句不能有LIMIT条件",
"annotation": "DELETE/UPDATE 语句使用LIMIT条件将随机选取数据进行删除或者更新，业务无法预期",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_check_with_order_by",
"desc": "DELETE/UPDATE 语句不能有ORDER BY",
"annotation": "DELETE/UPDATE 存在ORDER BY会使用排序，带来无谓的开销",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_disable_select_all_column",
"desc": "不建议使用SELECT *",
"annotation": "当表结构变更时，使用*通配符选择所有列将导致查询行为会发生更改，与业务期望不符；同时SELECT * 中的无用字段会带来不必要的磁盘I/O，以及网络开销，且无法覆盖索引进而回表，大幅度降低查询效率",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_enable_explain_pre_check",
"desc": "使用EXPLAIN加强预检查能力",
"annotation": "通过 EXPLAIN 的形式将待上线的DML进行SQL是否能正确执行的检查，提前发现语句的错误，提高上线成功率",
"level": "warn",
"type": "全局配置",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_hint_count_func_with_col",
"desc": "避免使用 COUNT(COL)",
"annotation": "建议使用COUNT(*)，因为使用 COUNT(COL) 需要对表进行全表扫描，这可能会导致性能下降。",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_hint_delete_tips",
"desc": "建议在执行DELETE/DROP/TRUNCATE等操作前进行备份",
"annotation": "DROP/TRUNCATE是DDL，操作立即生效，不会写入日志，所以无法回滚，在执行高危操作之前对数据进行备份是很有必要的",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_hint_group_by_requires_conditions",
"desc": "建议为GROUP BY语句添加ORDER BY条件",
"annotation": "在5.7中，MySQL默认会对’GROUP BY col1, …’按如下顺序’ORDER BY col1,…’隐式排序，导致产生无谓的排序，带来额外的开销；在8.0中，则不会出现这种情况。如果不需要排序建议显示添加’ORDER BY NULL’",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_hint_in_null_only_false",
"desc": "避免使用 IN (NULL) 或者 NOT IN (NULL)",
"annotation": "查询条件永远非真，这将导致查询无匹配到的结果",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_hint_limit_must_be_combined_with_order_by",
"desc": "LIMIT 查询建议使用ORDER BY",
"annotation": "没有ORDER BY的LIMIT会导致非确定性的结果可能与业务需求不符，这取决于执行计划",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_hint_sum_func_tips",
"desc": "避免使用 SUM(COL)",
"annotation": "当某一列的值全是NULL时，COUNT(COL)的返回结果为0，但SUM(COL)的返回结果为NULL，因此使用SUM()时需注意NPE问题（指数据返回NULL）；如业务需避免NPE问题，建议开启此规则",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_hint_truncate_tips",
"desc": "不建议使用TRUNCATE操作",
"annotation": "TRUNCATE是DLL，数据不能回滚，在没有备份情况下，谨慎使用TRUNCATE",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_hint_use_truncate_instead_of_delete",
"desc": "删除全表时建议使用 TRUNCATE 替代 DELETE",
"annotation": "TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，同时TRUNCATE后表所占用的空间会被释放，而DELETE后需要手工执行OPTIMIZE才能释放表空间",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_must_match_left_most_prefix",
"desc": "禁止对联合索引左侧字段进行IN 、OR等非等值查询",
"annotation": "对联合索引左侧字段进行IN 、OR等非等值查询会导致联合索引失效",
"level": "error",
"type": "索引失效",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_must_use_left_most_prefix",
"desc": "使用联合索引时，必须使用联合索引的首字段",
"annotation": "使用联合索引时，不包含首字段会导致联合索引失效",
"level": "error",
"type": "索引失效",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_not_recommend_func_in_where",
"desc": "应避免在 WHERE 条件中使用函数或其他运算符",
"annotation": "函数或运算符会导致查询无法利用表中的索引，该查询将会全表扫描，性能较差",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_not_recommend_group_by_constant",
"desc": "不建议对常量进行 GROUP BY",
"annotation": "GROUP BY 1 表示按第一列进行GROUP BY；在GROUP BY子句中使用数字，而不是表达式或列名称，当查询列顺序改变时，会导致查询逻辑出现问题",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_not_recommend_group_by_expression",
"desc": "不建议ORDER BY 的条件为表达式",
"annotation": "当ORDER BY条件为表达式或函数时会使用到临时表，如果在未指定WHERE或WHERE条件返回的结果集较大时性能会很差",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_not_recommend_having",
"desc": "不建议使用 HAVING 子句",
"annotation": "对于索引字段，放在HAVING子句中时不会走索引；建议将HAVING子句改写为WHERE中的查询条件，可以在查询处理期间使用索引，提高SQL的执行效率",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_not_recommend_in",
"desc": "不建议使用IN",
"annotation": "当IN值过多时，有可能会导致查询进行全表扫描，使得MySQL性能急剧下降",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_not_recommend_not_wildcard_like",
"desc": "不建议使用没有通配符的 LIKE 查询",
"annotation": "不包含通配符的 LIKE 查询逻辑上与等值查询相同，建议使用等值查询替代",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": true
},
{
"rule_name": "dml_not_recommend_order_by_rand",
"desc": "不建议使用 ORDER BY RAND()",
"annotation": "ORDER BY RAND()使用了临时表，同时还要对其进行排序，在数据量很大的情况下会增加服务器负载以及增加查询时间",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_not_recommend_subquery",
"desc": "不推荐使用子查询",
"annotation": "有些情况下，子查询并不能使用到索引，同时对于返回结果集比较大的子查询，会产生大量的临时表，消耗过多的CPU和IO资源，产生大量的慢查询",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_not_recommend_sysdate",
"desc": "不建议使用 SYSDATE() 函数",
"annotation": "当SYSDATE()函数在基于STATEMENT模式的主从环境下可能造成数据的不一致，因为语句在主库中执行到日志传递到备库，存在时间差，到备库执行的时候就会变成不同的时间值，建议采取ROW模式的复制环境",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_not_recommend_update_pk",
"desc": "不建议UPDATE主键",
"annotation": "主键索引数据列的顺序就是表记录的物理存储顺序，频繁更新主键将导致整个表记录的顺序的调整，会耗费相当大的资源",
"level": "error",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_rollback_max_rows",
"desc": "在 DML 语句中预计影响行数超过指定值则不回滚",
"annotation": "大事务回滚，容易影响数据库性能，使得业务发生波动；具体规则阈值可以根据业务需求调整，默认值：1000",
"level": "notice",
"type": "全局配置",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1000",
"desc": "最大影响行数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "dml_rule_all_subquery_rewrite",
"desc": "ALL修饰的子查询重写",
"annotation": "如果ALL子查询的结果中存在NULL，这个SQL永远返回为空。正确的写法应该是在子查询里加上非空限制，或使用max/min的写法。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_diff_ordering_spec_type_warning",
"desc": "排序字段方向不同导致索引失效",
"annotation": "ORDER BY 子句中的所有表达式需要按统一的 ASC 或 DESC 方向排序，才能利用索引来避免排序；如果ORDER BY 语句对多个不同条件使用不同方向的排序无法使用索引",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_distinct_elimination_rewrite",
"desc": "子查询中的DISTINCT消除",
"annotation": "对于仅进行存在性测试的子查询,如果子查询包含DISTINCT通常可以删除,以避免一次去重操作。",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_exists_2_join_rewrite",
"desc": "EXISTS查询转换为表连接",
"annotation": "EXISTS子查询可以在适当情况下转换为JOIN来优化查询，提高数据库处理效率和性能。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_filter_predicate_push_down_rewrite",
"desc": "过滤谓词下推",
"annotation": "滤条件下推（FPPD）是一种通过将过滤条件提前应用于内部查询块，以减少数据处理量并提升SQL执行效率。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_grouping_from_diff_tables_rewrite",
"desc": "GROUPBY字段来自不同表",
"annotation": "如果分组字段来自不同的表，数据库优化器将没有办法利用索引的有序性来避免一次排序，如果存在等值条件，可以替换这些字段为来自同一张表的字段，以利用索引优化排序和提高查询效率。",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_join_elimination_rewrite",
"desc": "表连接消除",
"annotation": "在不影响结果的情况下通过删除不必要的表连接来简化查询并提升性能，适用于查询仅涉及到主表主键列的场景。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_limit_clause_push_down_rewrite",
"desc": "LIMIT下推至UNION分支",
"annotation": "Limit子句下推优化通过尽可能的 “下压” Limit子句，提前过滤掉部分数据, 减少中间结果集的大小，减少后续计算需要处理的数据量, 以提高查询性能。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "1000",
"desc": "OFFSET最大阈值",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_max_min_agg_rewrite",
"desc": "MAX/MIN子查询重写",
"annotation": "对于使用MAX/MIN的子查询，可以通过重写从而利用索引的有序来避免一次聚集运算。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_move_order_2_leading_rewrite",
"desc": "ORDER子句重排序优化",
"annotation": "如果一个查询中既包含来自同一个表的排序字段也包含分组字段，但字段顺序不同，可以通过调整分组字段顺序，使其和排序字段顺序一致，这样数据库可以避免一次排序操作。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_npe_rewrite",
"desc": "NPE重写",
"annotation": "SQL的NPE(Null Pointer Exception)问题是指在SQL查询中,当聚合列全为NULL时,SUM、AVG等聚合函数会返回NULL,这可能会导致后续的程序出现空指针异常。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_or_cond_4_select_rewrite",
"desc": "OR条件的SELECT重写",
"annotation": "如果使用OR条件的查询语句，数据库优化器有可能无法使用索引来完成查询，可以把查询语句重写为UNION或UNION ALL查询，以便使用索引提升查询性能。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_or_cond_4_up_delete_rewrite",
"desc": "OR条件的UPDELETE重写",
"annotation": "如果有使用OR条件的UPDATE或DELETE语句，数据库优化器有可能无法使用索引来完成操作，可以把它重写为多个DELETE语句，利用索引提升查询性能。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_order_elimination_in_subquery_rewrite",
"desc": "IN子查询中没有LIMIT的排序消除",
"annotation": "如果子查询没有LIMIT子句，那么子查询的排序操作就没有意义，可以将其删除而不影响最终的结果。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_ordering_from_diff_tables_rewrite",
"desc": "避免ORDERBY字段来自不同表",
"annotation": "当排序字段来自不同表时，若存在等值条件，可替换这些字段为来自同一张表的字段，利用索引避免额外排序，提升效率。",
"level": "warn",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_outer_2_inner_conversion_rewrite",
"desc": "外连接优化",
"annotation": "外连接优化指的是满足一定条件（外表具有NULL拒绝条件）的外连接可以转化为内连接，从而可以让数据库优化器可以选择更优的执行计划，提升SQL查询的性能。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_projection_pushdown_rewrite",
"desc": "投影下推(PROJECTION PUSHDOWN)",
"annotation": "投影下推指的通过删除DT子查询中无意义的列（在外查询中没有使用），来减少IO和网络的代价，同时提升优化器在进行表访问的规划时，采用无需回表的优化选项的几率。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_qualifier_sub_query_rewrite",
"desc": "修饰子查询重写优化",
"annotation": "ANY/SOME/ALL修饰的子查询用于比较值关系，但效率低下因为它们逐行处理比较。通过查询重写可以提升这类子查询的执行效率。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_query_folding_rewrite",
"desc": "查询折叠(QUERY FOLDING)",
"annotation": "查询折叠指的是把视图、CTE或是DT子查询展开，并与引用它的查询语句合并，来减少序列化中间结果集，或是触发更优的关于表连接规划的优化技术。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_rule_sattc_rewrite",
"desc": "SATTC重写优化",
"annotation": "SAT-TC重写优化通过分析和处理查询条件的逻辑关系，以发现矛盾、简化条件或推断新条件，从而帮助数据库优化器制定更高效的执行计划，提升SQL性能。",
"level": "notice",
"type": "DML规范",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": false,
"has_rewrite_power": true
},
{
"rule_name": "dml_sql_explain_lowest_level",
"desc": "SQL执行计划中type字段建议满足规定的级别",
"annotation": "验证 SQL 执行计划中的 type 字段，确保满足要求级别，以保证查询性能。",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"params": [
{
"key": "first_key",
"value": "range,ref,const,eq_ref,system,NULL",
"desc": "查询计划type等级，以英文逗号隔开",
"type": "string"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "optimize_index_enabled",
"desc": "索引创建建议",
"annotation": "通过该规则开启索引优化建议，提供两个参数配置来定义索引优化建议的行为。1. 列区分度最低值阈值（百分制）：配置当前表中列的区分度小于多少时，不作为索引的列；2. 联合索引最大列数：限制联合索引给到的列数最大值，防止给出建议的联合索引不符合其他SQL标准",
"level": "notice",
"type": "索引优化",
"db_type": "MySQL",
"params": [
{
"key": "multi_params_first_key",
"value": "2.00",
"desc": "列区分度最低值阈值（百分比）",
"type": "float64"
},
{
"key": "multi_params_second_key",
"value": "3",
"desc": "联合索引最大列数",
"type": "int"
}
],
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "sql_is_executed",
"desc": "停用上线审核模式",
"annotation": "启用该规则来兼容事后审核的场景，对于事后采集的DDL 和 DML 语句将不再进行上线校验。例如库表元数据的扫描任务可开启该规则",
"level": "notice",
"type": "全局配置",
"db_type": "MySQL",
"is_custom_rule": false,
"has_audit_power": true,
"has_rewrite_power": false
},
{
"rule_name": "rule_id_1789180250588975104",
"desc": "yunqutest",
"annotation": "测试222222222222222",
"level": "warn",
"type": "DDL规范",
"db_type": "MySQL",
"is_custom_rule": true,
"has_audit_power": true,
"has_rewrite_power": false
}
]
}
3370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
