GaussDB(for MySQL)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导致插入慢。