SQL开发手册（自用版）

发表于2023-09-14更新于2024-03-03字数统计2.4k阅读时长14分阅读次数

一、SELECT查询技巧

1、对查询进行优化，应尽量避免全表扫描(非必要不使用SELECT * FROM table1)，首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引(建立索引需满足建立索引规约)。
2、应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，创建表时 NULL 是默认值，但大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0，-1 作为默认值。
3、应尽量避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的 LIKE(索引文件具有B-Tree的最左前缀匹配特性)。
4、应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用UNION 合并查询。

  • 反例：
1
SELECT id FROM t WHERE num = 10 OR num = 20;
  • 正例：
1
2
3
SELECT id FROM t WHERE num = 10
UNION ALL
SELECT id FROM t WHERE num = 20;

5、IN 和 NOT IN 也要慎用，否则会导致全表扫描。对于连续的数值，能用 BETWEEN 就不要用 IN。

  • 反例：
1
SELECT id FROM t WHERE num IN (1, 2, 3);
  • 正例：
1
SELECT id FROM t WHERE num BETWEEN 1 AND 3;

6、如果在 WHERE 子句中使用参数(指数据库暂时无法确定其值的局部变量，并不是mybatis中的参数)，也会导致全表扫描而不是走索引。
7、应尽量避免在 WHERE 子句中对字段进行表达式操作，应尽量避免在 WHERE 子句中对字段进行函数操作。

  • 说明：WHERE 子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，从而可能使用索引，进而避免表搜索。
  • 反例：
1
2
3
SELECT * FROM order_master WHERE SUBSTRING(order_master_sn, 1, 4) = '2023';
SELECT * FROM order_master WHERE payment / 2 < 500;
SELECT * FROM f_user WHERE CONVERT(CHAR(10), birthday, 112) = '19991217'
  • 正例：
1
2
3
SELECT * FROM order_master WHERE order_master_sn LIKE '2023%'
SELECT * FROM order_master WHERE payment < 500 * 2
SELECT * FROM f_user WHERE birthday = '1999-12-17'

8、很多时候用 EXISTS 代替 IN 是一个好的选择：SELECT num from a WHERE num IN(SELECT num FROM b)。用下面的语句替换：SELECT num FROM a WHERE EXISTS(SELECT 1 FROM b WHERE num = a.num)。
9、当在 SQL 语句中连接多个表时，尽量为表起别名，同时把别名前缀于每个 Column 上（即order_master.order_master_id在多表查询时给order_master起别名om，然后替换为om.order_master_id）。该操作可减少解析的时间并减少那些由 Column 歧义引起的语法错误。
10、尽量使用 “>=”，不要使用 “>”。

  • 说明：使用>=逻辑更加清晰、避免遗漏数据、能够轻松的修改边界值、可以有效避免误操作。

11、选择最有效率的表名顺序（只在基于规则的优化器中有效）。

  • 说明：Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在 FROM 子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表。

12、可以通过将不需要的记录在 GROUP BY 之前过滤掉来提高 GROUP BY 语句的效率。

  • 反例：
1
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER'
  • 正例：
1
SELECT JOB, AVG(SAL) FROM EMPWHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB

13、SQL 语句用大写，因为较多主流数据库总是先把小写的字母转换成大写的再执行。
14、使用ISNULL() 来判断是否为NULL值。

  • 说明：NULL与任何值的直接比较都为NULL。
    • NULL <> NULL的返回结果是NULL，而不是false。
    • NULL = NULL的返回结果是NULL，而不是true。
    • NULL <> 1的返回结果是NULL，而不是true。
  • 反例：在SQL语句中，如果在NULL前换行，影响可读性。 SELECT * FROM table WHERE column1 IS NULL AND column3 IS NOT NULL;而ISNULL(column) 是一个整体，简洁易懂。从性能数据上分析，ISNULL(column) 执行效率更快一些。

15、代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。
16、sql.xml配置参数使用：#{}，#param# ，不要使用 ${} （此种方式容易出现SQL注入）。
17、不允许直接拿HashMap与Hashtable作为查询结果集的输出。

  • 反例：某同学为避免写一个<resultMap>xxx</resultMap>，直接使用Hashtable来接收数据库返回结果，结果出现日常是把bigint转成Long值，而线上由于数据库版本不一样，解析成BigInteger，导致线上问题。

18、当只要一行数据时使用 LIMIT 1 。

  • 说明：当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。在这种情况下，加上 LIMIT 1 可以增加性能。这样一来，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。

19、使用子查询优化大分页查询

  • 说明：这种优化方式只使用与id是正序的情况。
  • 反例：
1
SELECT score, name FROM student ORDER BY score DESC LIMIT 1000000, 10;
  • 正例：
1
2
3
4
SELECT score, name FROM student
WHERE id >= (SELECT id FROM student LIMIT 1000000, 1)
ORDER BY score
LIMIT 10;

二、索引

1、业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

  • 说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

2、超过三个表禁止join。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

  • 说明：即使双表join也要注意表索引、SQL性能。

3、索引的使用规范：

  • 索引的创建要与应用结合考虑，且一个表尽量不要超过 6 个索引；
  • 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index_name 来强制指定索引；
  • 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；
  • 要注意索引的维护，周期性重建索引，重新编译存储过程；
  • 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。例如对该字段的前20个字符建立索引（ALTER table user ADD INDEX idx_desc(desc(20));）；
  • 如果需要使用索引，不要使用uuid作为主键，不能保证其自增性，会导致数据库对B+树的操作更复杂。

4、应尽可能的避免更新 Clustered 索引数据列， 因为 Clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 Clustered 索引数据列，那么需要考虑是否应将该索引建为 Clustered 索引。

  • 说明：聚集索引（Clustered Index）是一种SQL数据库中的索引类型，它对表中的数据行进行物理排序。每个表只能有一个聚集索引，因为它决定了数据行在磁盘上的物理存储顺序。这也意味着表中的数据行按照聚集索引的键列的值进行排序，并且物理存储位置与索引的结构紧密关联。

三、数据库表的设计

1、只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
2、尽可能的使用 varchar, nvarchar 代替 char, nchar。

  • 说明：首先，变长字段存储空间小，可以节省存储空间；其次，对于查询来说，在一个相对较小的字段内搜索效率会更高。

3、表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（1表示是，0表示否）。

  • 说明：任何字段如果为非负数，必须是unsigned。
  • 正例：表达逻辑删除的字段名is_deleted，1表示删除，0表示未删除。

4、varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引率。
5、在数据库中不能使用物理删除操作，要使用逻辑删除。

  • 说明：逻辑删除在数据删除后可以追溯到行为操作。不过会使得一些情况下的唯一主键变得不唯一，需要根据情况来酌情解决。