# TaurusDB SQL使用规范
#### 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.c=t2.c 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 BETWEEN 1 AND 5;
      ```
      
     
   
- [数据库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;
       ```
       ![](https://support.huaweicloud.com/usermanual-taurusdb/public_sys-resources/caution_3.0-zh-cn.png)
       合并的语句不能过多，过多时可能会出现大事务，导致表被长时间锁定。请根据业务评估，合理控制事务中的语句个数。
       
    
    4. 使用主键，在插入数据时，尽量选择主键顺序插入，选择使用AUTO_INCREMENT自增主键。 因为当主键乱序插入时，会产生"页分裂"，消耗性能。
       示例如下：
       主键乱序插入 : 6 2 9 7 2
       主键顺序插入 : 1 2 4 6 8
       
    
    5. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。 每次生成的UUID之间无序，插入时为主键乱序插入，会产生"页分裂"，消耗性能。
       
    
    6. 业务操作时，避免对主键的修改。 修改主键后还需对索引结构进行修改，花费代价较大。
       
    
    7. 满足业务需求的情况下，尽量降低主键的长度。
    
    8. 不要使用外键来维护外键关系，通过程序来控制。
    
    9. 读写业务分离。读业务放到备库上，避免因IO导致插入慢。