SQL_开发技巧(附阿里巴巴开发规范)

字段	含义
id	select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all
possible_key	显示可能应用在这张表上的索引，一个或多个
key	实际使用的索引，如果为NULL，则没有使用索引
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好

1.2、单表delete或者update语句，加个limit

好处：

结合order by使用
删除错误，通过binlog找回
避免长事务
数据量过大时，防止CPU拉满

语法：delete from user_login where id >6 limit 200;

1.3、where后字段，隐式转换

where后的条件不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较，最后导致索引失效

1.4、减少不必要的查询字段

指定字段查询可能会使用到联合索引，减少回表查询几率，降低消耗的CPU和IO以网络带宽资源

回表查询：先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取

数据的方式，就称之为回表查询

1.5、SQL命令行修改数据，养成begin + commit 事务的习惯

1.6、避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化

子查询性能差的原因：

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大
由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询

1.7、对应同一列进行or判断时，使用in代替or

in的值不要超过500个in操作可以更有效的利用索引，or大多数情况下很少能利用到索引

1.8、WHERE从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

不推荐：where date(create_time)='20190101'

推荐：where create_time >= '20190101' and create_time < '20190102'

二、数据库基本设计规范

2.1、名称设计

1、所有数据库对象名称必须使用小写字母并用下划线分割

2、所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）

3、数据库对象的命名要能做到见名识意，并且最后不要超过32个字符

4、临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀

5、所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

2.2、所有表必须使用Innodb存储引擎

没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好

2.3、数据库和表的字符集统一使用UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效

2.4、所有表和字段都需要添加注释

使用comment从句添加表和列的备注从一开始就进行数据字典的维护，不加注释比名称错别字都可怕（＞人＜；）

2.5、尽量控制单表数据量的大小，建议控制在500万以内

500万并不是MySQL数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题

可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

以垂直分表为例：

- 把不常用的字段单独放在一张表
- 把text，blob等大字段拆分出来放在附表中
- 经常组合查询的列放在一张表中

垂直分表的好处：

- 为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响
- 充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累

2.6、谨慎使用MySQL分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表谨慎选择分区键，跨分区查询效率可能更低建议采用物理分表的方式管理大数据

2.7、尽量做到冷热数据分离，减小表的宽度

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO）更有效的利用缓存，避免读入无用的冷数据经常一起使用的列放到一个表中（避免更多的关联操作）

2.8、禁止在表中建立预留字段

预留字段的命名很难做到见名识义预留字段无法确认存储的数据类型，所以无法选择合适的类型对预留字段类型的修改，会对表进行锁定

2.9、禁止在数据库中存储图片，文件等大的二进制数据

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时通常存储于文件服务器，数据库只存储文件地址信息

2.10、禁止在线上做数据库压力测试

2.11、禁止从开发环境，测试环境直接连接生成环境数据库

三、数据库字段设计规范

3.1、优先选择符合存储需要的最小的数据类型

原因：

列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多，索引的性能也就越差

方法：

1）将字符串转换成数字类型存储，如：将IP地址转换成整形数据

mysql提供了两个方法来处理ip地址：

1、inet_aton把ip转为无符号整型(4-8位)

2、inet_ntoa把整型的ip转为地址

插入数据前，先用inet_aton把ip地址转为整型，可以节省空间。显示数据时，使用inet_ntoa把整型的ip地址转为地址显示即可

2）对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型来存储

因为：无符号相对于有符号可以多出一倍的存储空间

SIGNED INT -2147483648~2147483647

UNSIGNED INT 0~4294967295

3） VARCHAR(N)中的N代表的是字符数，而不是字节数

使用UTF8存储255个汉字 Varchar(255)=765个字节。过大的长度会消耗更多的内存

3.2、避免使用TEXT、BLOB数据类型，最常见的TEXT类型可以存储64k的数据

建议把BLOB或是TEXT列分离到单独的扩展表中

Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。

而且对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型。

如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。

TEXT或BLOB类型只能使用前缀索引

因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的

3.3、避免使用ENUM类型

1、修改ENUM值需要使用ALTER语句

2、ENUM类型的ORDER BY操作效率低，需要额外操作

3、禁止使用数值作为ENUM的枚举值

3.4、尽可能把所有列定义为NOT NULL

原因：

1、索引NULL列需要额外的空间来保存，所以要占用更多的空间

2、进行比较和计算时要对NULL值做特别的处理

3.5、使用TIMESTAMP（4个字节）或DATETIME类型（8个字节）存储时间

TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

TIMESTAMP 占用4字节和INT相同，但比INT可读性高，超出TIMESTAMP取值范围的使用DATETIME类型存储

经常会有人用字符串存储日期型的数据（不正确的做法）缺点：

1、无法用日期函数进行计算和比较

2、用字符串存储日期要占用更多的空间

3.6、同财务相关的金额类数据必须使用decimal类型

1、非精准浮点：float，double

2、精准浮点：decimal

Decimal类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据

3.7、尽量使用varchar代替char

四、索引设计规范

4.1、限制每张表上的索引数量，建议单张表索引不超过5个

索引并不是越多越好！索引可以提高效率同样可以降低效率

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率

因为mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能

4.2、禁止给表中的每一列都建立单独的索引

5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好

4.3、每个Innodb表必须有个主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的

每个表都可以有多个索引，但是表的存储顺序只能有一种 Innodb是按照主键索引的顺序来组织表的。
不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）不要使用UUID、MD5、HASH、字符串列作为主键（无法保证数据的顺序增长）
主键建议使用自增ID值

4.4、常见索引列建议

1、出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列

2、包含在ORDER BY、GROUP BY、DISTINCT中的字段

3、多表join的关联列

并不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好

4.5、如何选择索引列的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少

1、区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）

2、尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）

3、使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

4.6、避免建立冗余索引和重复索引

因为这样会增加查询优化器生成执行计划的时间

重复索引示例：primary key(id)、index(id)、unique index(id)
冗余索引示例：index(a,b,c)、index(a,b)、index(a)

4.7、优先考虑联合索引(覆盖索引)

覆盖索引：就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引

对于频繁的查询优先考虑使用覆盖索引，覆盖索引的好处：

1、避免Innodb表进行索引的二次查询

Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率

2、可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO

4.8、索引SET规范

尽量避免使用外键约束：

1、不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引

2、外键可用于保证数据的参照完整性，但建议在业务端实现

3、外键会影响父表和子表的写操作从而降低性能

五、数据库操作行为规范

5.1、正确书写格式

有个好的书写格式会随着业务量变多的时候，还你一个好的阅读环境

反例：

正例：

5.2、操作自测

1、执行变更SQL语句的时候，先在测试环境执行自测，避免有错误放在生产环境

2、当有依赖关系时，先修改表结构，再执行数据

3、执行修改或删除重要文件前，备份先

5.3、建议使用预编译语句进行数据库操作

5.4、充分利用表上已经存在的索引

1、避免使用双%号的查询条件，如a like '%123%'，（如果无前置%,只有后置%，是可以用到列上的索引的）

2、一个SQL只能利用到复合索引中的一列进行范围查询，如：有 a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到，在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧。

3、使用left join或 not exists来优化not in操作，因为not in 也通常会使用索引失效

5.5、JOIN好用但要适度

对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置

在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大

如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性

同时对于关联操作来说，会产生临时表操作，影响查询效率Mysql最多允许关联61个表，建议不超过5个

5.6、减少同数据库的交互次数

数据库更适合处理批量操作合并多个相同的操作到一起，可以提高处理效率

5.7、禁止使用order by rand() 进行随机排序

会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源

推荐在程序中获取一个随机值，然后从数据库中获取数据的方式

5.8、在明显不会有重复值时使用UNION ALL而不是UNION

1、UNION会把两个结果集的所有数据放到临时表中后再进行去重操作

2、UNION ALL不会再对结果集进行去重操作

5.9、拆分复杂的大SQL为多个小SQL

1、大SQL：逻辑上比较复杂，需要占用大量CPU进行计算的SQL

2、MySQL：一个SQL只能使用一个CPU进行计算

3、SQL拆分后可以通过并行执行来提高处理效率

5.10、超100万行的批量写（UPDATE、DELETE、INSERT）操作，要分批多次进行操作

1、大批量操作可能会造成严重的主从延迟

主从环境中，大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况

2、binlog日志为row格式时会产生大量的日志

大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因。

3、避免产生大事务操作

大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对MySQL的性能产生非常大的影响。

特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批。

5.11、对于大表使用pt-online-schema-change修改表结构

对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的

pt-online-schema-change它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器,把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉，把原来一个DDL操作，分解成多个小的批次进行

好处：

1、避免大表修改产生的主从延迟

2、避免在对表字段进行修改时进行锁表

附录：阿里巴巴开发手册

建表规约

【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint
（1 表示是，0 表示否）。
说明：任何字段如果为非负数，必须是 unsigned。
注意：POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在设置从 is_xxx 到Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取值含义与取值范围。
正例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。
【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只
出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
【强制】表名不使用复数名词。
说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。
【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
【强制】主键索引名为 pk字段名；唯一索引名为 uk字段名；普通索引名则为 idx_字段名。
说明：pk 即 primary key；uk 即 unique key；idx_ 即 index 的简称。
【强制】小数类型为 decimal，禁止使用 float 和 double。
说明：在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。
【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
【强制】varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
【强制】表必备三字段：id, create_time, update_time。
说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time, update_time的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。
【推荐】表的命名最好是遵循“业务名称_表的作用”。
正例：alipay_task / force_project / trade_config
【推荐】库名与应用名称尽量一致。
【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
1）不是频繁修改的字段。
2）不是唯一索引的字段。
3）不是 varchar 超长字段，更不能是 text 字段。
正例：各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取。
【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索
速度。
正例：无符号值可以避免误存负数，且扩大了表示范围。

对象	年龄区间	类型	字节	表示范围
人	150 岁之内	tinyint unsigned	1	无符号值：0 到 255
龟	数百岁	smallint unsigned	2	无符号值：0 到 65535
恐龙化石	数千万年	int unsigned	4	无符号值：0 到约 43 亿
太阳	约 50 亿年	bigint unsigned	8	无符号值：0 到约 10 的 19 次方

索引规约

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据
实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b无法排序。
【推荐】利用覆盖索引来进行查询操作，避免回表。
说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。
正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现：using index。
【推荐】利用延迟关联或者子查询优化超多分页场景。
说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL改写。
正例：先快速定位需要获取的 id 段，然后再关联：
SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id
【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts
最好。
说明：
1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2） ref 指的是使用普通的索引（normal index）。
3） range 对索引进行范围检索。
反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range还低，与全表扫描是小巫见大巫。
【推荐】建组合索引的时候，区分度最高的在最左边。
正例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=?那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。
【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
【参考】创建索引时避免有如下极端误解：
1）索引宁滥勿缺。认为一个查询就需要建一个索引。
2）吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
3）抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。

SQL 语句

【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的标
准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1,
col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
【强制】当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为
NULL，因此使用 sum()时需注意 NPE 问题。
正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column), 0) FROM table;
【强制】使用 ISNULL()来判断是否为 NULL 值。
说明：NULL 与任何值的直接比较都为 NULL。
1） NULL<>NULL 的返回结果是 NULL，而不是 false。
2） NULL=NULL 的返回结果是 NULL，而不是 true。
3） NULL<>1 的返回结果是 NULL，而不是 true。
反例：在 SQL 语句中，如果在 null 前换行，影响可读性。select * from table where column1 is null and column3 is not null; 而ISNULL(column)是一个整体，简洁易懂。从性能数据上分析，ISNULL(column)执行效率更快一些。
【强制】代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
【强制】数据订正（特别是删除或修改记录操作）时，要先 select，避免出现误删除，确认无
误才能执行更新语句。
【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或
表名）进行限定。
说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。
正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出1052异常：Column 'name' in field list is ambiguous。
【推荐】SQL 语句中表的别名前加 as，并且以 t1、t2、t3、...的顺序依次命名。
说明：1）别名可以是表的简称，或者是依照表在 SQL 语句中出现的顺序，以 t1、t2、t3 的方式命名。2）别名前加 as 使别名更容易识别。
正例：select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;
【推荐】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控
制在 1000 个之内。
【参考】因国际化需要，所有的字符存储与表示，均采用 utf8 字符集，那么字符计数方法需
要注意。
说明：
SELECT LENGTH("轻松工作")；返回为 12
SELECT CHARACTER_LENGTH("轻松工作")；返回为 4
如果需要存储表情，那么选择 utf8mb4 来进行存储，注意它与 utf8 编码的区别。
【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE
无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

ORM 映射

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
说明：1）增加查询分析器解析成本。2）增减字段容易与 resultMap 配置不一致。3）无用字段增加网络消耗，尤其是 text 类型的字段。
【强制】POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行
字段与属性之间的映射。
说明：参见定义 POJO 类以及数据库字段定义规定，在 sql.xml 增加映射，是必须的。
【强制】不要用 resultClass 当返回参数，即使所有类属性名与数据库字段一一对应，也需要
定义；反过来，每一个表也必然有一个与之对应。
说明：配置映射关系，使字段与 DO 类解耦，方便维护。
【强制】sql.xml 配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL 注入。
【强制】iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。
说明：其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取
start,size 的子集合。
正例：
Map<String, Object> map = new HashMap<>(16);
map.put("start", start);
map.put("size", size);
【强制】不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
反例：某同学为避免写一个xxx，直接使用 HashTable 来接收数据库返回结果，结果出现日常是把 bigint 转成 Long 值，而线上由于数据库版本不一样，解析成 BigInteger，导致线上问题。
【强制】更新数据表记录时，必须同时更新记录对应的 update_time 字段值为当前时间。
【推荐】不要写一个大而全的数据更新接口。传入为 POJO 类，不管是不是自己的目标更新字段，都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL 时，不要更新无改动的字段，一是易出错；二是效率低；三是增加 binlog 存储。
【参考】@Transactional 事务不要滥用。事务会影响数据库的 QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
【参考】中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时带上此条件；表示不为空且不为 null 时执行；表示不为 null 值时执行。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)