20.2 数据库设计规范详解
20.2.3 SQL 使用规范详解
● （强制）禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
● （强制）禁止使用触发器，事件调度器（event scheduler）和视图实现业务逻 辑，这些业务逻辑应该在业务层处理，避免对数据库产生逻辑依赖。
● （强制）禁止隐式类型转换。
说明
转换规则具体如下：
● 两个参数至少有一个是NULL时，比较的结果也是NULL，例外是使用 <=> 对两个NULL 做比较时会返回 1，这两种情况都不需要做类型转换。
● 两个参数都是字符串，会按照字符串来比较，不做类型转换。
● 两个参数都是整数，按照整数来比较，不做类型转换。
● 十六进制的值和非数字做比较时，会被当做二进制串。
● 参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp。
● 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换 为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数 进行比较。
● 有其他情况下，两个参数都会被转换为浮点数再进行比较。
● 如果一个索引建立在string类型上，如果这个字段和一个int类型的值比较，符合第 g 条。如f_phone定义的类型是varchar，但where使用f_phone in (098890)，两个参数 都会被当成浮点型。发生这个隐式转换并不是最糟的，最糟的是string转换后的float，
mysql无法使用索引，这才导致了性能问题。如果是 f_user_id = ‘1234567’ 的情 况，符合第 b 条,直接把数字当字符串比较。
● （强制）禁止在where条件列上使用函数，会导致索引失效。如lower(email)，
f_qq % 4。可放到右边的常量上计算。返回小结果集不是很大的情况下，可以对 返回列使用函数，简化程序开发。
● （强制）业务允许的情况下，事务里包含SQL语句越少越好，尽量不超过5个。因 为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
● （强制）拒绝大事务。比如在一个事务里进行多个select，多个update，如果是高 频事务，会严重影响MySQL并发能力，因为事务持有的锁等资源只在事务
rollback/commit时才能释放。但同时也要权衡数据写入的一致性。
● （强制）所有的字符存储与表示，均以utf-8或者utf8mb4编码，表和字段需要有 注释，注意字符统计函数的区别。
说明
select length(“轻松工作”)；返回为12
select character_length(“轻松工作”)；返回为4
如果要使用表情，那么使用utfmb4来进行存储，注意它与utf-8编码的区别。
● （强制）避免使用自然连接（natural join）。
说明
自然连接没有显示定义连接列，而是隐含，问题是：一是不容易理解，二是容易出问题，
特别是无法移植。
● （推荐）事务里更新语句尽量基于主键或unique key。 如update … where id=XX; 否则会产生间隙锁(GAP Lock)，内部扩大锁定范围，导致系统性能下降，
产生死锁。
● （推荐）尽量不使用外键与级联，外键概念可以在应用层解决。
说明
（概念解释）学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更 新学生表中的student_id,同时触发成绩表中的student_id更新，则为级联更新。外键与级 联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库 更新风暴的风险；外键影响数据库的插入速度。
● （推荐）in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素 数量，控制在500个之内。
● （推荐）在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的 分页语句。
● （推荐）减少使用无法利用索引的order by，和业务沟通能不排序就不排序，或 将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，
数据库的CPU资源是极其宝贵的。
● （推荐）order by、group by、distinct这些SQL尽量利用索引直接检索出排序好 的数据。如where a=1 order by b可以利用key(a,b)。
● （推荐）包含了order by、group by、distinct这些查询的语句，where条件过滤 出来的结果集请保持在1000行以内，否则SQL会很慢。
● （推荐）能确定返回结果只有一条时，使用 limit 1。在保证数据不会有误的前提 下，能确定结果集数量时，多使用limit，尽快的返回结果。
● （推荐）涉及到复杂SQL时，务必先参考已有索引设计，先explain。先explain的 好处是可以为了利用索引，增加更多查询限制条件。
● （推荐）简单SQL拆分，不以代码处理复杂为由。比如 OR 条件：
f_phone=’10000’ or f_mobile=’10000’，两个字段各自有索引，但只能用 到其中一个。可以拆分成2个sql，或者union all。
● （推荐）使用join时，where条件尽量充分利用同一表上的索引。如 `select t1.a,t2.b * from t1,t2 and t1.a=t2.a and t1.b=123 and t2.c= 4` ，如果t1.c与t2.c 字段相同，那么t1上的索引(b,c)就只用到b了。此时如果把where条件中的t2.c=4 改成t1.c=4，那么可以用到完整的索引。这种情况可能会在字段冗余设计（反范 式）时出现。
● （推荐）考虑使用union all，少使用union，注意考虑去重。union all不去重，而 少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all。如果UNION结果中有使用limit，在2个子SQL可能有许多返回值的情况下，
各自加上limit。如果还有order by，请找DBA。
● （推荐）order by .. limit。这种查询更多的是通过索引去优化。
● （推荐）不要在SQL中进行复杂的运算或业务逻辑，这些都应该在业务层解决。
● （推荐）建议使用合理的分页方式以提高分页效率，大页情况下不使用跳跃式分 页。
说明
● 假如有类似下面分页语句:
`SELECT * FROM table1 ORDER BY ftime DESC LIMIT 10000,10;`
这种分页方式会导致大量的io，因为MySQL使用的是提前读取策略。
● 推荐分页方式：
```sql
-- 即传入上一次分页的界值
SELECT * FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10
SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000，
10) as t2 ON t1.id=t2.id
● （推荐）避免频繁对表进行COUNT操作。对大数据量表进行COUNT操作非常耗 时，一般都是秒级响应速度。如果有频繁对表进行COUNT操作的需求，请引入专 门的计数表解决。
● （推荐）delete,update语句改成select再explain。select最多导致数据库慢，写操 作才是锁表的罪魁祸首。
● （推荐）为了减少与数据库交互的次数，可以适度采用批量SQL语句。比如：
`INSERT INTO … VALUES (XX),(XX),(XX)....(XX);`这里XX的个数建议100个以内。
因为太多对性能没有明显的提升，并且容易形成大事务，造成负面影响。
● （推荐）TRUNCATE TABLE 比 DELETE速度快，且使用的系统和日志资源少，如 果删除的表上没有TRIGGER，且进行全表删除，建议使用TRUNCATE TABLE。
TRUNCATE TABLE不会把删除的数据写到日志文件中。
说明
TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同；TRUNCATE TABLE不 能和其它DML写在同一个事务里。
● （推荐）尽量不要使用负向查询，避免全表扫描。
说明
使用负向查询是指使用负向运算符，如：NOT, !=, <>, NOT EXISTS, NOT IN以及NOT LIKE 等等。如果使用负向查询，无法利用索引结构做二分查找，只好做全表扫描。
● （推荐）避免三个表join。需要join的字段，数据类型必须绝对一致；多表关联查 询时，保证被关联的字段需要有索引；在多表join中，尽量选取结果集较小的表作 为驱动表，来join其他表。即使双表join也要注意表索引、SQL性能。
● （推荐）任何新的select，update，delete上线，都要先explain，看索引使用情 况，尽量避免extra列出现：Using File Sort，Using Temporary，rows超过1000 的要谨慎上线。每天进行慢日志统计分析，去除慢日志语句。
说明 explain解读：
● type：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到 好）。
● possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上 若存在索引，则该索引将被列出，但不一定被查询使用。
● key：表示MySQL实际决定使用的键（索引）如果没有选择索引，键是NULL。要想 强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、
USE INDEX 或者IGNORE INDEX。
● ref：哪些列或常量被用于查找索引列上的值。
● rows：根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。
● Extra：
● Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分 组查询。
● Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”。
● Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据 表，只用索引表就完成了这个查询，这个叫覆盖索引。如果同时出现Using where，代表使用索引来查找读取记录， 也是可以用到索引的，但是需要查询 到数据表。
● Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引 就可以获取所有需要的数据，则会出现 Using where。如果type列是ALL或 index，而没有出现该信息，则你有可能在执行错误的查询：返回所有数据。
A 修订记录
发布日期 修改说明
2021-11-24 第十五次正式发布，包含以下内容：
GaussDB(for MySQL)数据库代理支持开启多个代理实例。
GaussDB(for MySQL)新增使用规范建议。
2021-10-31 第十四次正式发布，包含以下内容：
GaussDB(for MySQL)新增退订只读节点（包年/包月计费）。
GaussDB(for MySQL)新增专属资源池。
2021-09-31 第十三次正式发布，包含以下内容：
GaussDB(for MySQL)新增磁盘使用率监控指标。
2021-08-31 第十二次正式发布，包含以下内容：
GaussDB(for MySQL)刷新支持的监控指标单位。
GaussDB(for MySQL)优化修改实例内网安全组内容。
GaussDB(for MySQL)新增设置SSL数据加密。
GaussDB(for MySQL)新增修改实例备注。
GaussDB(for MySQL)新增实例主备切换事件监控。
GaussDB(for MySQL)优化GaussDB(for MySQL)自定义策略内 容。
GaussDB(for MySQL)新增设置全量SQL。
2021-06-10 第十一次正式发布，包含以下内容：
GaussDB(for MySQL)刷新事件监控支持的事件说明。
2021-05-31 第十次正式发布，包含以下内容：
GaussDB(for MySQL)支持规格降配置。
GaussDB(for MySQL)新增包周期实例转按需。
GaussDB(for MySQL)新增升级内核小版本。
GaussDB(for MySQL)新增支持的监控指标监控指标。
GaussDB(for MySQL)新增数据安全中心DSC。
发布日期 修改说明
2021-03-30 第九次正式发布，包含以下内容：
● 优化用户指南整体框架结构。
● 优化查看实例监控入口及界面内容。
● 优化开通读写分离约束限制内容。
● 优化参数模板管理约束限制内容。
2020-12-30 第八次正式发布，包含以下内容：
● GaussDB(for MySQL)新增查看proxy监控指标。
● GaussDB(for MySQL)新增创建proxy告警规则。
● GaussDB(for MySQL)新增事件监控。
● GaussDB(for MySQL)新增事件监控。