数据库-表、索引、触发器、分区表、查询语句使用注意事项

普通表、数据类型

1. 联机交易事务的表字段个数不超过个数N个(n<50?)批量业务表字段个数不超过500个。原因：单标字段个数越多，查询性能越差。
2. 不应使用浮点型数据类型存放金额，应使用整数型
3. 多表关联时，表示同一涵义的字段应使用相同的数据类型。原因：避免隐式转换。

索引

4. 索引中所有字段长度合计不得超过2704字节。原因：B树索引的列长度不得超过缓存页面(一般为8k)的1/3。
5. 单列索引字段总长度不能超过50字节。原因：索引大小会碰撞，带来存储开销，索引性能也会下降。
6. 一个表上索引最好不超过5个。
7. 对经常进行insert、update、delete操作的表，要尽量少的建立索引。原因：因为insert、delete操作种索引都需要重算，导致sql响应时间增长。
8. 当sql语句返回行数占整表总行数比例小于5%时，应该建立索引。
9. 在频繁进行分组、排序以及去重的列（group by、order by、distinct）的字段应建立索引。
10. 在键值频繁更新的索引，建议定期进行重建索引。
11. where过滤条件应包含索引前缀列的等值过滤条件。
12. 复合索引的字段不建议超过5个。

触发器

13. 不应再大数据量同步及对性能要求高的场景中使用触发器。原因：触发器多用于多表关联同步场景，对sql执行性能影响较大

分区表

14. 对于记录数超过500w且占用空间占用2GB的表，应使用分区表（备份表或老化表除外）
15. 分区的字段应是where条件之一
16. 分区的关键字不宜进行更新操作，避免数据重分布，影响性能。
17. 当表数据量很大进行分区时，应使用具有明显区间性的字段进行分区，如日期、区域等关键字

查询语句

18. 为避免数据库做隐式计算，在赋值、比较、运算时，应使用相同数据类型。
19. 应避免大量使用 SELECT… FOR UPDATE语句造成的不必要加锁
20. 考虑语句的效率问题，应尽量使用集合运算符
21. 在同一个查询语句中，若使用表的别名，应针对不同表使用不同的别名
22. 建议明确列出要查询的列，而不是select *,这样也可以减少查询输出列，减少网络带宽占用和响应速度
23. 统计表的所有记录数时，使用count(*)而不是count(COL)，因为后者不会统计null值
24. 应尽量避免不必要的排序，排序需要耗费大量的内存和CPU.
25. 使用order by进行排序时，应显式制定desc、asc
26. 不建议在查询条件中使用函数或表达式。原因：1、等号左边的字段使用表达式或函数，索引会失效
27. 宜使用区分度高的字段作为复合索引的第一个字段
28. 查询条件不应对索引字段使用： !=、<>比较符，避免索引失效
29. 查询条件中IN的候选子集不宜过大。原因：查询会对IN每一条数据进行等值比较，开销较大。建议用写入临时表、INNER JOIN来实现包含查询
30. 多表关联查询应明确指定各表的连接条件on,避免产生笛卡尔积。
31. 多表关联，建议对表使用别名，保证语句逻辑清晰
32. 复杂语句不宜使用子查询，建议改成表关联。因为子查询会产生临时表，返回结果集特别大时，对sql性能有影响

DML

33. 不应用单条UPDATE/DELETE 语句实现多个表的更新删除操作
34. update、delete同一语句应包含where语句，避免全表扫
35. 不应update子句同时更新多个列，被更新列同样是更新源