SQL 使用规范建议

最近更新时间:2024-05-10 11:22:31

我的收藏
本文为您介绍在创建 TDSQL-C MySQL 版集群后的 SQL 使用规范建议。

数据库基本设计规范

所有的字符存储与表示,均以 utf-8 或者 utf8mb4 编码,表和字段需要有注释信息。
尽量避免使用大事务。
说明:
例如在一个事务里进行多个 select 或 update 语句,如果是高频事务,会严重影响 MySQL 并发能力,因为事务持有的锁等资源只在事务 rollback/commit 时才能释放。但同时也要评估数据写入的一致性。

数据库 SQL 查询规范

当使用 ORDER BY .. LIMIT 查询时,优先考虑通过索引优化查询语句,提高执行效率。
使用 ORDER BY、GROUP BY、DISTINCT 执行查询时,where 条件过滤出来的结果集请保持在1000行以内,否则会降低查询效率。
使用 ORDER BY、GROUP BY、DISTINCT 语句时,优先利用索引检索排序好的数据。如 where a=1 order by b 可以利用 key(a,b)。
使用 JOIN 连接查询时,where 条件尽量充分利用同一表上的索引。
说明:
例如,select t1.a, t2.b from t1,t2 where 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。
在代码中实现分页查询逻辑时,若 COUNT 为0应直接返回,避免执行后面的分页语句。
避免频繁对表进行 COUNT 操作。对大数据量表进行 COUNT 操作耗时会较长,一般都是秒级响应速度。如果有频繁对表进行 COUNT 操作的需求,请引入专门的计数表解决。
确定返回结果只有一条时,使用 limit 1。在保证数据无误的前提下,可以确定结果集数量时,尽量使用 limit 查询,尽可能快速返回结果。
评估 DELETE 和 UPDATE 语句效率时,可以将语句改成 SELECT 后执行 explain(explain 命令可以帮助我们分析 SQL 查询语句的执行计划和性能瓶颈)。但需注意,如果频繁执行 SELECT 语句会导致数据库性能慢,因此在使用 explain 命令分析 SQL 查询语句时,需尽量减少 SELECT 语句的执行次数,在分析 SQL 查询语句时,需要综合考虑查询效率和数据库性能,权衡利弊,选择最优的方案。
TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和日志资源少,如果删除的表上没有触发器,且进行全表删除,建议使用 TRUNCATE TABLE。
说明:
TRUNCATE TABLE 不会把删除的数据写到日志文件中。
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
TRUNCATE TABLE 不能和其它 DML 写在同一个事务里。
尽量不要使用负向查询,避免全表扫描。
说明:
使用负向查询是指使用负向运算符,如:NOT, !=, <>, NOT EXISTS, NOT IN 以及 NOT LIKE 等。如果使用负向查询,无法利用索引结构做二分查找,只能做全表扫描。
避免对三个表以上执行 JOIN 连接。需要 JOIN 的字段,数据类型必须保持一致。
多表关联查询时,保证被关联的字段需要有索引;在多表 join 中,尽量选取结果集较小的表作为驱动表,用来 join 其他表。即使双表 join 也要关注表索引、SQL 性能情况。

数据库 SQL 开发规范

对于简单 SQL,优先考虑拆分。
说明:
如 OR 条件:f_phone='10000' or f_mobile='10000',两个字段各自有索引,但只能用到其中一个。可以拆分成2个 SQL,或者使用 union all。
需要在 SQL 中进行复杂的运算或业务逻辑时,优先考虑在业务层实现。
使用合理的分页方式以提高分页效率,大页情况下不使用跳跃式分页。
说明:
例如有类似下面分页语句: SELECT * FROM table1 ORDER BY ftime DESC LIMIT 10000,10; 这种分页方式会导致大量的 IO,因为 MySQL 使用的是提前读取策略。
推荐分页方式:即传入上一次分页的界值。 SELECT * FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10;
在事务里使用更新语句时,尽量基于主键或 unique key,否则会产生间隙锁,内部扩大锁定范围,导致系统性能下降,产生死锁。
尽量不使用外键与级联,外键概念在应用层处理。
说明:
例如,学生表中的 student_id 是主键,那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id,同时触发成绩表中的 student_id 更新,则为级联更新。
外键与级联更新适用于单机低并发,不适合分布式、高并发集群。
级联更新是强阻塞,存在数据库更新风暴的风险,外键影响数据库的插入速度。
减少使用 in 操作,in 后的集合元素数量不超过500个。
为了减少与数据库交互的次数,可以适度采用批量 SQL 语句。例如:INSERT INTO … VALUES (XX),(XX),(XX)....(XX); 这里 XX 的个数建议100个以内。
避免使用存储过程,存储过程难以调试和扩展,更没有移植性。
避免使用触发器、事件调度器(event scheduler)和视图实现业务逻辑,这些业务逻辑应该在业务层处理,避免对数据库产生逻辑依赖。
避免使用隐式类型转换。
说明:
类型转换规则具体如下:
1. 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,特殊情况是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换。
2. 两个参数都是字符串,会按照字符串来比较,不做类型转换。
3. 两个参数都是整数,按照整数来比较,不做类型转换。
4. 十六进制的值和非数字做比较时,会被当做二进制串。
5. 参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp。
6. 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较。
7. 有其他情况下,两个参数都会被转换为浮点数再进行比较。
8. 如果一个索引建立在 string 类型上,如果这个字段和一个 int 类型的值比较,符合上述第7条。 如 f_phone 定义的类型是 varchar,但 where 语句中使用 f_phone in (098890),两个参数都会被当成浮点型。这种情况下 string 转换后的 float,导致 MySQL 无法使用索引,导致出现性能问题。 如果是 f_user_id ='1234567' 的情况,符合上述第2条,直接把数字当字符串比较。
业务允许的情况下,事务里包含 SQL 语句越少越好,尽量不超过5个。因为过长的事务会导致锁数据较久,MySQL 内部缓存、连接消耗过多等问题。
避免使用自然连接(natural join)。
说明:
自然连接没有显示定义连接列,而是隐含,会出现难以理解及无法移植问题。

数据库索引设计规范

根据实际业务需求,减少使用无法利用索引优化的 order by 查询语句。Order by、group by、distinct 这些语句较为耗费 CPU 资源。
涉及到复杂 SQL 语句时,优先参考已有索引进行设计,通过执行 explain,查看执行计划,利用索引,增加更多查询限制条件。
使用新的 SELECT、UPDATE、DELETE 语句时,都需要通过 explain 查看执行计划中的索引使用情况,尽量避免 extra 列出现:Using File Sort,Using Temporary。当执行计划中扫描的行数超过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,而没有出现该信息,则您有可能在执行错误的查询,返回了所有的数据。
在 WHERE 条件列上使用函数,会导致索引失效。
说明:
如 WHERE left(name, 5) = 'zhang',left 函数会导致 name 上的索引失效。可在业务侧修改该条件,不使用函数。当返回结果集较小时,业务侧过滤满足条件的行。