TaurusDB SQL使用规范

数据库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。
  • 不做列运算：SELECT id WHERE age+1=10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
  • SQL语句尽可能简单：大语句拆小语句，减少锁时间；一条大SQL可以堵死整个库。
  • 不用SELECT*；
  • OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内。
  • 不用存储过程和触发器，在应用程序实现；
  • 避免%xxx式查询。
  • 少用JOIN，查询尽量不要涉及多个表。
  • 使用同类型进行比较，比如用'123'和'123'比，123和123比。
  • 尽量避免在WHERE子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
  • 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN1AND5；

数据库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 (*),(*),(*)....(*); 这里*的个数建议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）。

  自然连接没有显示定义连接列，而是隐含，会出现难以理解及无法移植问题。

• 对于千万或亿级大数据量的表，建议使用如下方法提升数据写入效率。
  1. 删除不必要的索引。

     更新数据时候，同时会更新索引数据。对于大数据量的表，避免创建大量的索引，影响更新速度。请根据业务评估，删除不必要的索引。

  2. 插入多条数据时，尽量选择批量插入。

     因为批量插入只需要远程请求一次数据库。

     示例如下：

     insert into tb1 values(1,'value1');
     insert into tb2 values(2,'value2');
     insert into tb3 values(3,'value3');

     优化为：

     insert into tb values(1,'value1'),(2,'value2'),(3,'value3');

  3. 插入多条数据时，尽量选择手动控制事务插入

     通过手动控制事务，可以将多条执行单元合并为一个事务，避免多个事务的开销，同时保证数据的完整性和一致性。

     示例如下：

     insert into table1 values(1,'value1'),(2,'value2'),(3,'value3');
     insert into table2 values(4,'value1'),(5,'value2'),(6,'value3');
     insert into table3 values(7,'value1'),(8,'value2'),(9,'value3');

     优化为：

     start transaction;
     insert into table1 values(1,'value1'),(2,'value2'),(3,'value3');
     insert into table2 values(4,'value1'),(5,'value2'),(6,'value3');
     insert into table3 values(7,'value1'),(8,'value2'),(9,'value3');
     commit;

     

     合并的语句不能过多，过多时可能会出现大事务，导致表被长时间锁定。请根据业务评估，合理控制事务中的语句个数。

  4. 使用主键，在插入数据时，尽量选择主键顺序插入，选择使用AUTO_INCREMENT自增主键。

     因为当主键乱序插入时，会产生“页分裂”，消耗性能。

     示例如下：

     主键乱序插入 : 6 2 9 7 2

     主键顺序插入 : 1 2 4 6 8

  5. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

     每次生成的UUID之间无序，插入时为主键乱序插入，会产生“页分裂”，消耗性能。

  6. 业务操作时，避免对主键的修改。

     修改主键后还需对索引结构进行修改，花费代价较大。

  7. 满足业务需求的情况下，尽量降低主键的长度。
  8. 不要使用外键来维护外键关系，通过程序来控制。
  9. 读写业务分离。读业务放到备库上，避免因IO导致插入慢。