更新时间：2023-12-18 GMT+08:00
SQL使用规范
数据库基本设计规范
数据库索引设计规范
- 根据实际业务需求，减少使用无法利用索引优化的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上的索引失效。
修改方案：可在业务侧修改该条件，不使用函数。当返回结果集较小时，业务侧过滤满足条件的行。
数据库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。SELECT较多会导致数据库慢，写操作会导致锁表。
- 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）和视图实现业务逻辑，这些业务逻辑应该在业务层处理，避免对数据库产生逻辑依赖。
- 避免使用隐式类型转换。
类型转换规则具体如下：
- 两个参数至少有一个是NULL时，比较的结果也是NULL，特殊情况是使用 <=> 对两个NULL做比较时会返回 1，这两种情况都不需要做类型转换。
- 两个参数都是字符串，会按照字符串来比较，不做类型转换。
- 两个参数都是整数，按照整数来比较，不做类型转换。
- 十六进制的值和非数字做比较时，会被当做二进制串。
- 参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp。
- 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较。
- 有其他情况下，两个参数都会被转换为浮点数再进行比较。
- 如果一个索引建立在string类型上，如果这个字段和一个int类型的值比较，符合上述第 7 条。
如f_phone定义的类型是varchar，但where语句中使用f_phone in (098890)，两个参数都会被当成浮点型。这种情况下string转换后的float，导致MySQL无法使用索引，导致出现性能问题。
如果是 f_user_id ='1234567' 的情况，符合上述第 2 条，直接把数字当字符串比较。
- 业务允许的情况下，事务里包含SQL语句越少越好，尽量不超过5个。因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
- 避免使用自然连接（natural join）。
父主题： 开发规范
