数据库开发规范

关注微信公众号掌握更多技术动态

---------------------------------------------------------------

一、三范式
    为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。在实际开发中最为常见的设计范式有三个：

1.第一范式
    确保每列保持原子性

列不可分
有主键
    根据实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

2.第二范式
确保表中的每列都和主键相关

每个表只描述一件事
主要针对联合主键而言，不存在部分依赖，每一列都跟联合主键有关系，而与联合主键中的其中一个键无关系
    比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

    这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的联合主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

    而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

    这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

3.第三范式
    确保每列都和主键列直接相关,而不是间接相关，不存在传递依赖

    第三范式需要确保数据表中的，每一列数据都和主键直接相关，而不能间接相关解决间接相关，把不直接相关的再建一张表，采用外键形式将两张表关联.

    比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段，因为添加后就会出现传递依赖 ：

订单编号--》客户编号， 客户编号--》客户详细信息 

如下面这两个表所示的设计就是一个满足第三范式的数据库表。

    这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

二、数据库表设计规范
数据库命名一般为项目名称+代表库含义的简写，数据库创建时必须添加默认字符集和校对规则子句。默认字符集为UTF8（已迁移dumbo的使用utf8mb4）

1.表与字段的规范
 无特殊要求使用Innodb和utf8mb4排序规则，Collation使用默认即可。一个请求从客户端到 Mysql 服务器，再到表，再返回给客户端，中间是经过多层字符集转换的。实际开发中，会统一均采用 utf8 ，这样就有效避免了各层字符集转换带来的性能影响。数据表、数据字段必须加入中文注释

(1)命名规范

①命名必须使用小写字母或数字并以下划线分隔 ， 禁止出现数字开头，禁止两个下划线中间只出现数字，名字要做到见名思意，不要超过32个字符。

说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。

正例： aliyun _ admin ， rdc _ config ， level 3_ name

反例： AliyunAdmin ， rdcConfig ， level _3_ name

②表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（ 1 表示是，0 表示否 ） 。POJO 类的 布尔 属性不加 is ，在 resultMap 中进行字段与属性之间的映射。

说明：任何字段如果为非负数，必须是 unsigned 。

正例：表达逻辑删除的字段名 is_deleted ，1 表示删除，0 表示未删除。

③字段名必须使用小写字母或数字并以下划线分隔 ， 禁止出现数字开头，禁止两个下划线中间只出现数字，名字要做到见名思意，不要超过32个字符。

说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。

正例： aliyun _ admin ， rdc _ config ， level 3_ name

反例： AliyunAdmin ， rdcConfig ， level _3_ name

④不同表之间存储相同数据的列名和列类型必须一致(关联列)

⑤表和字段名要加注释，同时修改时应该注意注释得更新。且表名不能使用复数，表名使用业务名称加表的作用。

⑥同一业务或者模块的表尽可能使用相同的前缀，表名称尽可能表达含义；

⑦常规表表名以t_开头，t代表table的意思，命名规则即 t + 模块（包含模块含义的简写）+ 表（包含表含义的简写），比如用户模块的教育信息表：t_user_eduinfo。

⑧不同类型表命名

临时表（RD、QA或DBA同学用于数据临时处理的表），命名规则：temp前缀+模块+表+日期后缀：temp_user_eduinfo_20210719
备份表（用于保存和归档历史数据或者作为灾备恢复的数据）命名规则，bak前缀+模块+表+日期后缀：bak_user_eduinfo_20210719
(2)类型规范

①小数类型为 decimal ，禁止使用 float 和 double 。

说明： float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。

②必须把字段定义为NOT NULL并且提供默认值

说明：NULL字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效

③禁止使用ENUM，可使用TINYINT代替

④禁止使用TEXT、BLOB类型(如果表的记录数在万级以下可以考虑)

⑤区分使用DATETIME和TIMESTAMP

说明：存储年使用YEAR类型、存储日期使用DATE类型、存储时间(精确到秒)建议使用TIMESTAMP类型。

DATETIME和TIMESTAMP都是精确到秒，优先选择TIMESTAMP，因为TIMESTAMP只有4个字节，而DATETIME8个字节，同时TIMESTAMP具有自动赋值以及⾃自动更新的特性。

补充：如何使用TIMESTAMP的自动赋值属性?

自动初始化，而且自动更新：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP 只是自动初始化：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP 自动更新，初始化的值为0：column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP 初始化的值为0：column1 TIMESTAMP DEFAULT 0

⑥VARCHAR存储

VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N
VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存
VARCHAR(N)，N>5000时，使用BLOB类型
(3)表必备三字段(非空)： id 主键(自增或发号器),  gmt _ create创建时间 ,  gmt _ modified更新时间 。

禁止UUID MD5 HASH这些作为主键(数值太离散了)

(4)优先选择符合存储需要的最小、最简单的数据类型。

选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。

    ①尽量使用数字型字段

      若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

    ②尽量使用位数较少的类型 ，比如能使用TINYINT/SMALLINT就不使用int

    

    ③尽可能的使用 varchar/nvarchar 代替 char/nchar 

          因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。只有在存储的字符串长度几乎相等，使用 char 定长字符串类型。(UTF-8这样的复杂字符集，每个字符使用不同的字节数进行存储也要使用varchar)

    varchar 长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text ，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

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

    ⑤对于可控范围的字段使用tinyint而不是varchar/enum(enum内部也是使用tinyint存储)

(5)将字段很多的表分解成多个表

    表字段数不建议超过 60 个，建议单行的总数据大小不要超过 64K.将使用频率低的字段拿出来新建一个表，完成分表，从而提高效率

核心表字段数量尽可能地少，有大字段要考虑拆分

(6)增加冗余字段

    适当的不遵循范式的要求，对于经查查询的外表字段可以在本表中增加冗余字段。比如经常要查一个学生的系名，就可以在学生表加一个系名的字段

    冗余字段应遵循：

不是频繁修改的字段。
不是唯一索引的字段。
不是 varchar 超长字段，更不能是 text 字段。
需要复杂计算的值可以冗余成一个字段，计算后再入库。

(7)@Transactional 事务不要滥用。事务会影响数据库的 QPS ，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。

(8)使用逻辑主键而非业务主键

业务主键（自然主键）：在数据库表中把具有业务逻辑含义的字段作为主键，称为“自然主键(Natural Key)”。
逻辑主键（代理主键）：在数据库表中采用一个与当前表中逻辑信息无关的字段作为其主键，称为“代理主键”。可用于数据迁移
复合主键（联合主键）：通过两个或者多个字段的组合作为主键。
(9)预留扩展json字段

软件行业唯一不变的就是变化，功能上线之后，很有可能需要对已有的功能增加一些合理的需求，完成这些工作必须通过添加字段解决，或者某些功能的实现需要通过增加字段来降低实现的复杂性等等。这些问题都会改动线上的数据库表结构，一旦改动就会导致锁表，会使所有的写入操作一直等待，直到表锁关闭，特别是对于数据量大的热点表，添加一个字段可能会因为锁表时间过长而导致部分请求超时，这可能会对企业间接造成经济上的损失。所以最好预留一个扩展字段，可以动态地增加任何字段，甚至是对象，扩展字段不要存储热点数据，只存储非热点数据，因为创建索引无意义。

最好在表设计之初可以预估一下需要预留多少个备用字段，再配合扩展字段，基本上可以把改变（添加字段）表结构的次数降至一个非常少的次数。

(10)存储规范

①必须使用varchar(20)存储手机号

②禁止使用小数存储国币、使用“分”作为单位，这样数据库里就是整数了

资金字段考虑统一*100处理成整型，避免使用decimal浮点类型存储

③使用VARBINARY存储大小写敏感的变长字符串或二进制内容

说明：VARBINARY默认区分大小写，没有字符集概念，速度快

④INT类型固定占用4字节存储

说明：INT(4)仅代表显示字符宽度为4位，不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示，Python、Java客户端等不具备这个功能

⑤所有存储相同数据的列名和列类型必须一致（在多个表中的字段如user_id，它们类型必须一致）

(11)多用逻辑删除，少用物理删除

update order set del_status=1,edit_date=now(),edit_user='admin' where id=123;

2.索引规范
定期考虑是否需要新建索引,建表时就要确定索引

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

    索引宁滥勿缺不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的 。

写多读少的场景，不推荐使用唯一索引，用应用程序保证唯一性。

(2)主键索引名为 pk_ 字段名；唯一索引名为 uk _字段名 ； 普通索引名则为 idx _字段名。

(3)字符字段最好不要做主键，在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。如果前缀区分不大而后缀区分大可以采用倒叙存储,或者采用hash的方式

如果区分度结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行

selecttivity = count(distinct c_name)/count(*) ;

(4)限制每张表的索引数量，建议每张表的索引数量不超过5个，并且针对复合索引，最常用的、区分度最高的(列中不同值数量/列的总行数)、字段长度小的放到最左侧。索引中的字段数建议不超过，唯一索引建议由 3 个或更少的字段组成。

(5)索引列定义为 not null

    索引null列需要额外空间保存，需要占用更多地空间，运算和比较的时候会占用更多的空间。该字段是可以为空的，那么还需要NULL标识位，MySQL会生成一个 1字节 的NULL标识列来记录；

(6)值分布稀少的字段不适合建立索引，比如性别。

(7)应尽可能的避免更新 clustered 主键索引数据列

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

(8)不用外键，由程序保证约束；尽量不用 UNIQUE，由程序保证约束

(9)联合索引还是多个单列索引的选择

    创建复合索引时，应该仔细考虑 列的顺序。对索引中的 所有列执行搜索或仅对 前几列执行搜索时复合索引非常有用； 仅对后面的任意列执行搜索时复合索引则没有用处。

    多个单列索引在 多条件查询时优化器会选择 最优索引策略， 可能只用一个索引，也可能将多个索引全用上！但多个单列索引底层会建立多个 B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有 多条件联合查询时最好建联合索引！

    建组合索引的时候，区分度最高的在最左边。如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。

    存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=?那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。

复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替； 
正确选择复合索引中的主列字段，一般是选择性较好的字段； 
复合索引的几个字段是否经常同时以 AND 方式出现在 WHERE 子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引； 
如果复合索引中包含的字段经常单独出现在 WHERE 子句中，则分解为多个单字段索引； 
如果复合索引所包含的字段超过 3 个，那么仔细考虑其必要性，考虑减少复合的字段； 
如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
    

(10)尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

(11)将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段，例如医院的住院费计算

3.其它内容
(1)不用函数和触发器，在应用程序实现

(2)当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用配制线程数量< 最大连接数，启用 SQL SERVER 的线程池来解决，如果还是数量 = 最大连接数+5，严重的损害服务器的性能。

(3)需要定期删除(或者转移)过期数据的表，通过分表解决，按照2/8法则将操作频率较低的历史数据迁移到历史表中，按照时间或者则曾Id做切割点。MySQL在处理大表时，性能就开始明显降低，所以建议单表物理大小限制在16GB，表中数据行数控制在2000W内。

三、生产sql执行规范
1.使用邮件发sql语句

把要在线上执行的sql语句用邮件发过去，可以避免使用聊天工具的一些弊端，减少一些误操作的机会。而且有个存档，方便今后有问题的时候回溯原因。很多聊天工具只保留最近7天的历史记录，邮件会保留更久一些。

禁止非DBA对线上数据库进行写操作，修改线上数据需要提交工单，由DBA执行，提交的SQL语句必须经过测试

2.把sql语句压缩成一行

线上环境有时候需要通过命令行连接数据库，比如：mysql，你把sql语句复制过来后， 在命令行界面执行，由于屏幕滚动太快，这时根本无法确定sql是否都执行成功。

针对这类问题，强烈建议把sql语句压缩成一行，去掉多余的换行符和空格，可以有效的减少一些误操作。

sql压缩工具推荐使用：https://tool.lu/sql/

3.操作数据之前先select一下

在操作数据之前，把sql先改成select count(*)语句，比如：

update order set status=1 where status=0;

改成：

select count(*) from order where status=0;

查一下该sql执行后影响的记录行数，做到自己心中有数。也给自己一次测试sql是否正确，确认是否执行的机会。

4.操作数据sql加limit

即使通过上面的select语句确认了sql语句没有问题，执行后影响的记录行数是对的。

也建议你不要立刻执行，建议在正在执行的时候，加上limit + select出的记录行数。例如：

update order set status=1 where status=0 limit 1000;

假设有一次性更新的数据太多，所有相关记录行都会被锁住，造成长时间的锁等待，而造成用户请求超时。此外，加limit可以避免一次性操作太多数据，对服务器的cpu造成影响。还有一个最重要的原因：加limit后，操作数据的影响范围是完全可控的。

5.update时更新修改人和修改时间

在更新数据的时候，同时更新修改人和修改时间字段。

update order set status=1,edit_date=now(),edit_user='admin' where status=0;

这样在恢复数据时就能通过修改人和修改时间字段过滤数据了。后面需要用到的修改时间通过这条sql语句可以轻松找到：

select edit_user ,edit_date from `order` order by edit_date desc limit 50;

当然，如果是高并发系统不建议这种批量更新方式，可能会锁表一定时间，造成请求超时。

6.操作数据之前先做备份

如果只是修改了少量的数据，或者只执行了一两条sql语句，通过上面的修改人和修改时间字段，在需要回滚时，能快速的定位到正确的数据。

但是如果修改的记录行数很多，并且执行了多条sql，产生了很多修改时间。这时，你可能就要犯难了，没法一次性找出哪些数据需要回滚。

为了解决这类问题，可以将表做备份。

可以使用如下sql备份：

create table order_bak_2021031721 like`order`;insert into order_bak_2021031721 select * from`order`;

先创建一张一模一样的表，然后把数据复制到新表中。

也可以简化成一条sql：

create table order_bak_2021031722 select * from`order`;

创建表的同时复制数据到新表中。此外，建议在表名中加上bak和时间，一方面是为了通过表名快速识别出哪些表是备份表，另一方面是为了备份多次时好做区分。因为有时需要执行多次sql才能把数据修复好，这种情况建议把表备份多次，如果出现异常，把数据回滚到最近的一次备份，可以节省很多重复操作的时间。

恢复数据时，把sql语句改成select语句，先在备份库找出相关数据，每条数据对应一条update语句，还原到老表中。

7.中间结果写入临时表

有时候要先用一条sql查询出要更新的记录的id，然后通过这些id更新数据。批量更新之后，发现不对，要回滚数据。但由于有些数据已更新，此时使用相同的sql相同的条件，却查不出上次相同的id了。

针对这种情况，可以先将第一次查询的id存入一张临时表，然后通过临时表中的id作为查询条件更新数据。如果要恢复数据，只用通过临时表中的id作为查询条件更新数据即可。修改完，3天之后，如果没有出现问题，就可以把临时表删掉了。

8.字段增删改的限制

(1)新加的字段一定要允许为空

正常情况下，如果程序新加了字段，一般是先在数据库中加字段，然后再发程序的最新代码。如果数据库中新加的字段非空，最新的程序还没发，线上跑的还是老代码，这时如果有insert操作，就会报字段不能为空的异常。因为新加的非空字段，老代码是没法赋值的。所以说新加的字段一定要允许为空。

除此之外，这种设计更多的考虑是为了程序发布失败时的回滚操作。如果新加的字段允许为空，则可以不用回滚数据库，只需回滚代码即可.

(2)不允许删除字段

线上环境必填字段一定不能删除的。

(3)根据实际情况修改字段

修改字段名称。修改字段名称也不允许，跟删除必填字段的问题差不多。如果把程序部署好了，还没来得及修改数据库中表字段名称。这时所有涉及该字段的增删改查，都会报字段不存在的异常。如果先把数据库中字段名称改了，程序还没来得及发。这时所有涉及该字段的增删改查，同样也会报字段不存在的异常。所以，线上环境字段名称一定不要修改。先增加一个冗余字段，程序操作冗余字段，当程序部署后再删除旧字段
修改字段类型。修改字段类型时一定要兼容之前的数据。例如：tinyint改成int可以，但int改成tinyint要仔细衡量一下。varchar改成text可以，但text改成varchar要仔细衡量一下。
修改字段长度。字段长度建议改大，通常情况下，不建议改小。如果一定要改小，要先确认该字段可能会出现的最大长度，避免insert操作时出现字段太长的异常。此外，建议改大也需要设置一个合理的长度，避免数据库资源浪费。
(4)禁止使用不含字段列表的INSERT语句

如：insert into values ('a','b','c');  应使用  insert into t_name(c1,c2,c3) values ('a','b','c'); 。

9.流程规范

所有的建表操作需要提前告知 DBA 该表涉及的查询 SQL；
所有的建表需要确定建立哪些索引后才可以建表上线；
所有的改表结构、加索引操作都需要将涉及到所改表的查询 SQL 发出来告知 DBA 等相关人员；
在建新表加字段之前，建议开发人员提前发出给 DBA 评估、优化和审核；
批量导入、导出数据必须提前通知 DBA 协助观察；
大批量统计更新，如临时统计，避开高峰期，并通知 DBA；
推广活动或上线新功能必须提前通知 DBA 进行流量评估；
及时处理已下线业务的 SQL。
禁止在线上做数据库压力测试
汇总库开启Audit审计日志功能，出现问题时方可追溯核心业务数据库变更需在凌晨执行
业务部门程序出现bug等影响数据库服务的问题，请及时通知DBA便于维护服务稳定
线上数据库的变更操作必须提供对应的回滚方案
10.避免大数据查询

MySQL采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在server端保存完整的结果集。所以，如果客户端读结果不及时，会堵住MySQL的查询过程，但是不会把内存打爆。而对于InnoDB引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于InnoDB对LRU算法做了改进，冷数据的全表扫描，对Buffer Pool的影响也能做到可控。全表扫描还是比较耗费IO资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。

禁止在主库进行后台统计操作，避免影响业务，可以在离线从库上执行后台统计
不在业务高峰期批量更新、查询数据库
禁止在数据库中跑大查询
对数据的更新要打散后批量更新，不要一次更新太多数据
获取大量数据时，建议分批次获取数据，每次获取数据少于2000条，结果集应小于1M

(1)查询结果集存储

获取一行，写到net_buffer。这块内存的大小是由参数net_buffer_length定义，默认16k
重复获取行，直到net_buffer写满，调用网络接口发出去
若发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer
若发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送
因此一个查询在发送过程中，占用的MySQL内部的内存最大就是net_buffer_length这么大，不会达到200G，socket send buffer 也不可能达到200G（默认定义/proc/sys/net/core/wmem_default），若socket send buffer被写满，就会暂停读数据的流程

(2)优化的LRU算法

扫描过程中，需要新插入的数据页，都被放到old区域
一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域
再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（New区），很快就会被淘汰出去。
11.大数据读取规范

读取百万数据时建议使用游标查询，避免分页问题和oom问题

对大量数据进行处理时，为防止内存泄漏情况发生，也可以采用游标方式进行数据查询处理。这种处理方式比常规查询要快很多。

当查询百万级的数据的时候，还可以使用游标方式进行数据查询处理，不仅可以节省内存的消耗，而且还不需要一次性取出所有数据，可以进行逐条处理或逐条取出部分批量处理。一次查询指定 fetchSize 的数据，直到把数据全部处理完。

Mybatis 的处理加了两个注解：@Options 和 @ResultType

@Mapper public interface BigDataSearchMapper extends BaseMapper<BigDataSearchEntity> { // 方式一 多次获取，一次多行 @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ") @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000000) Page<BigDataSearchEntity> pageList(@Param("page") Page<BigDataSearchEntity> page, @Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper); // 方式二 一次获取，一次一行 @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ") @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 100000) @ResultType(BigDataSearchEntity.class) void listData(@Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper, ResultHandler<BigDataSearchEntity> handler); }

(1)@Options

ResultSet.FORWORD_ONLY：结果集的游标只能向下滚动
ResultSet. SCROLL_INSENSITIVE：结果集的游标可以上下移动，当数据库变化时，当前结果集不变
ResultSet.SCROLL_SENSITIVE：返回可滚动的结果集，当数据库变化时，当前结果集同步改变
fetchSize：每次获取量
(2)@ResultType

@ResultType(BigDataSearchEntity.class)：转换成返回实体类型

注意： 返回类型必须为 void ，因为查询的结果在 ResultHandler 里处理数据，所以这个 hander 也是必须的，可以使用 lambda 实现一个依次处理逻辑。

注意：

虽然上面的代码中都有 @Options 但实际操作却有不同：

方式一是多次查询，一次返回多条；
方式二是一次查询，一次返回一条；
原因：

Oracle 是从服务器一次取出 fetch size 条记录放在客户端，客户端处理完成一个批次后再向服务器取下一个批次，直到所有数据处理完成。

MySQL 是在执行 ResultSet.next() 方法时，会通过数据库连接一条一条的返回。flush buffer 的过程是阻塞式的，如果网络中发生了拥塞，send buffer 被填满，会导致 buffer 一直 flush 不出去，那 MySQL 的处理线程会阻塞，从而避免数据把客户端内存撑爆。

非流式查询和流式查询区别：

非流式查询：内存会随着查询记录的增长而近乎直线增长；
流式查询：内存会保持稳定，不会随着记录的增长而增长其内存大小取决于批处理大小BATCH_SIZE的设置，该尺寸越大，内存会越大所以BATCH_SIZE应该根据业务情况设置合适的大小；
另外要切记每次处理完一批结果要记得释放存储每批数据的临时容器，即上文中的gxids.clear();

四、安全规范
1.敏感信息存储

(1)禁止在数据库中存储明文密码，需把密码加密后存储

说明：对于加密操作建议由公司的中间件团队基于如mybatis的扩展，提供统一的加密算法及密钥管理，避免每个业务线单独开发一套，同时也与具体的业务进行了解耦

(2)禁止在数据库中明文存储用户敏感信息，如手机号等

说明：对于手机号建议公司搭建统一的手机号查询服务，避免在每个业务线单独存储

(3)禁止开发直接给业务同学导出或者查询涉及到用户敏感信息的数据，如需要需上级领导审批

(4)涉及到导出数据功能的操作，如包含敏感字段都需加密或脱敏

(5)跟数据库交互涉及的敏感数据操作都需有审计日志，必要时要做告警

2.数据库连接

(1)对连接数据库的IP需设置白名单功能，杜绝非法IP接入

(2)线上连接数据库的用户名、密码建议定期进行更换

(3)程序连接不同的数据库要使用不同的账号

(4)对于程序连接数据库账号，遵循权限最小原则

(5)使用内网域名而不是ip连接数据库

(6)禁止使用应用程序配置文件内的帐号手工访问线上数据库

(7)禁止从测试、开发环境直连线上数据库

五、Redis开发规范
1.键值设计

(1)key名设计

①可读性和可管理性

以业务名(或数据库名)为前缀(防止key冲突)，用冒号分隔

# 业务名:表名:id ugc:video:1

②简洁性

保证语义的前提下，控制key的长度(key的长度尽量小于30个字符)，当key较多时，内存占用也不容忽视。对于业务名或业务数据名，可以使⽤相应的英⽂单词的

⾸字⺟表⽰

user:{uid}:friends:messages:{mid} 简化为u:{uid}:fr:m:{mid}

key本⾝是字符串，底层的数据结构是SDS。SDS结构中会包含字符串⻓ 度、分配空间⼤⼩等元数据信息。从Redis 3.2版本开始，当key字符串的⻓度增加时，SDS中的元数据也会 占⽤更多内存空间。

③不要包含特殊字符

反例：包含空格、换行、单双引号以及其他转义字符

④Redis的key尽量设置ttl，以保证不使用的Key能被及时清理或淘汰。

(2)value设计

①拒绝bigkey

防止网卡流量、慢查询，string类型控制在10KB以内，hash、list、set、zset元素个数不要超过5000。Redis是使⽤单线程读写数据，bigkey的读写操作会阻塞线程，降低Redis的处理效率。

情况⼀：键值对的值⼤⼩本⾝就很⼤，例如value为1MB的String类型数据。为了避免String类型的 bigkey，在业务层，我们要尽量把String类型的数据⼤⼩控制在10KB以下。
情况⼆：键值对的值是集合类型，集合元素个数⾮常多，例如包含100万个元素的Hash集合类型数据。为 了避免集合类型的bigkey，我给你的设计规范建议是，尽量把集合类型的元素个数控制在1万以下。
非字符串的bigkey，不要使用del删除，使用hscan、sscan、zscan方式渐进式删除，同时要注意防止bigkey过期时间自动删除问题(例如一个200万的zset设置1小时过期，会触发del操作，造成阻塞，而且该操作不会不出现在慢查询中(latency可查))，查找方法和删除方法

Redis的4种集合类型List、Hash、Set和Sorted Set，在集合元素个数⼩于 ⼀定的阈值时，会使⽤内存紧凑型的底层数据结构进⾏保存，从⽽节省内存。例如，假设Hash集合的hashmax-ziplist-entries配置项是1000，如果Hash集合元素个数不超过1000，就会使⽤ziplist保存数据。 紧凑型数据结构虽然可以节省内存，但是会在⼀定程度上导致数据的读写性能下降。所以，如果业务应⽤更 加需要保持⾼性能访问，⽽不是节省内存的话，在不会导致bigkey的前提下，你就不⽤刻意控制集合元素个 数了。

常见的产生bigkey场景

例如一些社交类产品，粉丝列表，为了减少对于db的访问，会根据注册用户的id来绑定相关的list结构存储粉丝信息，假若遇到了某些明星，大v，那么如果这个list没有做过相关的调优处理就很容易转换为一个bigkey。
假设用list来存储用户缓存信息，当访问量增加的时候也很容易产生bigkey。
将相关的数据存储到redis的一些复杂数据结构中（list，set相关类型）的时候，需要考虑，是否每个存储项的字段都有必要存入，如果是无关必要的字段则可以忽略掉。
Redis提供了扫描大key的方法

redis-cli -h $host -p $port --bigkeys -i 0.01  

需要注意的是当在线上实例进行大key扫描时，Redis的QPS会突增，为了降低扫描过程中对Redis的影响，需要控制扫描的频率，使用-i参数控制即可，它表示扫描过程中每次扫描的时间间隔，单位是秒。

使用这个命令的原理，其实就是Redis在内部执行scan命令，遍历所有key，然后针对不同类型的key执行strlen、llen、hlen、scard、zcard来获取字符串的长度以及容器类型(list/dict/set/zset)的元素个数。

而对于容器类型的key，只能扫描出元素最多的key，但元素最多的key不一定占用内存最多，这一点需要我们注意下。不过使用这个命令一般我们是可以对整个实例中key的分布情况有比较清晰的了解。

②选择适合的数据类型

例如：实体类型(要合理控制和使用数据结构内存编码优化配置,例如ziplist，但也要注意节省内存和性能之间的平衡)。

反例：

set user:1:name tom set user:1:age 19 set user:1:favor football

正例:

hmset user:1 name tom age 19 favor football

尽可能的使用Hash数据结构。因为Redis在储存小于100个字段的Hash结构上，其存储效率是非常高的。所以在不需要集合(set)操作或list的push/pop操作的时候，尽可能的使用Hash结构。比如，在一个web应用程序中，需要存储一个对象表示用户信息，使用单个key表示一个用户，其每个属性存储在Hash的字段里，这样要比给每个属性单独设置一个key-value要高效的多。通常情况下倘若有数据使用string结构，用多个key存储时，那么应该转换成单key多字段的Hash结构。如上述例子中介绍的Hash结构应包含，单个对象的属性或者单个用户各种各样的资料。Hash结构的操作命令是HSET(key, fields, value)和HGET(key, field)，使用它可以存储或从Hash中取出指定的字段。

③控制key的生命周期

redis不是垃圾桶，建议使用expire设置过期时间(条件允许可以打散过期时间，防止集中过期)，不过期的数据重点关注idletime。

(3)使⽤⾼效序列化⽅法和压缩⽅法

Redis中的字符串都是使⽤⼆进制安全的字节数组来保存的，所以，可以把业务数据序列化成⼆进制数据写⼊到Redis中。但是，不同的序列化⽅法，在序列化速度和数据序列化后的占⽤内存空间这两个⽅⾯，效果是不⼀样的。⽐如说，protostuff和kryo这两种序列化⽅法，就要⽐Java内置的序列化⽅法（java-build-in-serializer）效率更⾼。

此外，业务应⽤有时会使⽤字符串形式的XML和JSON格式保存数据。这样做的好处是，这两种格式的可读性好，便于调试，不同的开发语⾔都⽀持这两种格式的解析。缺点在于，XML和JSON格式的数据占⽤的内存空间⽐较⼤。为了避免数据占⽤过⼤的内存空间，我建议使⽤压缩⼯具（例如snappy或gzip），把数据压缩后再写⼊Redis，这样就可以节省内存空间了。

(4)使⽤整数对象共享池

整数是常⽤的数据类型，Redis内部维护了0到9999这1万个整数对象，并把这些整数作为⼀个共享池使⽤。换句话说，如果⼀个键值对中有0到9999范围的整数，Redis就不会为这个键值对专⻔创建整数对象了，⽽是会复⽤共享池中的整数对象。

这样⼀来，即使⼤量键值对保存了0到9999范围内的整数，在Redis实例中，其实只保存了⼀份整数对象，可以节省内存空间。基于这个特点，建议在满⾜业务数据需求的前提下，能⽤整数时就尽量⽤整数，这样可以节省实例内存。那什么时候不能⽤整数对象共享池呢？主要有两种情况。

第⼀种情况是，如果Redis中设置了maxmemory，⽽且启⽤了LRU策略（allkeys-lru或volatile-lru策略），那么，整数对象共享池就⽆法使⽤了。这是因为，LRU策略需要统计每个键值对的使⽤时间，如果不同的键值对都共享使⽤⼀个整数对象，LRU策略就⽆法进⾏统计了。
第⼆种情况是，如果集合类型数据采⽤ziplist编码，⽽集合元素是整数，这个时候，也不能使⽤共享池。因为ziplist使⽤了紧凑型内存结构，判断整数对象的共享情况效率低。好了，到这⾥，我们了解了和键值对使⽤相关的四种规范，遵循这四种规范，最直接的好处就是可以节省内存空间。
(5)不同的业务数据分实例存储

虽然可以使⽤key的前缀把不同业务的数据区分开，但是，如果所有业务的数据量都很⼤，⽽且访问特 征也不⼀样，我们把这些数据保存在同⼀个实例上时，这些数据的操作就会相互⼲扰。

假如数据采集业务使⽤Redis保存数据时，以写操作为主，⽽⽤⼾统计业务使⽤Redis时，是以读查询为主，如果这两个业务数据混在⼀起保存，读写操作相互⼲扰，肯定会导致业务响应变慢。 建议你把不同的业务数据放到不同的 Redis 实例中。这样⼀来，既可以避免单实例的内存使⽤量过⼤，也可以避免不同业务的操作相互⼲扰。

2.命令使用

单元素操作是基础；是指每⼀种集合类型对单个数据实现的增删改查操作。例如，Hash类型的HGET、
HSET和HDEL，Set类型的SADD、SREM、SRANDMEMBER等。这些操作的复杂度由集合采⽤的数据结构决

定，例如，HGET、HSET和HDEL是对哈希表做操作，所以它们的复杂度都是O(1)；Set类型⽤哈希表作为底

层数据结构时，它的SADD、SREM、SRANDMEMBER复杂度也是O(1)。Set类型的SADD也⽀持同时增加多个元素。此时，这些操作的复杂度，就是由单个元素操作复杂 度和元素个数决定的。例如，HMSET增加M个元素时，复杂度就从O(1)变成O(M)了。

范围操作⾮常耗时；指集合类型中的遍历操作，可以返回集合中的所有数据，⽐如Hash类型的HGETALL和Set类型的SMEMBERS，或者返回⼀个范围内的部分数据，⽐如List类型的LRANGE和ZSet类型的ZRANGE。
这类操作的复杂度⼀般是O(N)，⽐较耗时，应该尽量避免。SCAN系列操作（包括HSCAN，SSCAN和ZSCAN），这类操作实现了渐进式遍历，每次只返回有限数量的数据。这样⼀来，相⽐于HGETALL、SMEMBERS这类操作来说，就避免了 ⼀次性返回所有元素⽽导致的Redis阻塞。

统计操作通常⾼效；指集合类型对集合中所有元素个数的记录，例如LLEN和SCARD。这类操作复杂度只有
O(1)，这是因为当集合类型采⽤压缩列表、双向链表、整数数组这些数据结构时，这些结构中专⻔记录了元

素的个数统计，因此可以⾼效地完成相关操作。

例外情况只有⼏个。是指某些数据结构的特殊记录，例如压缩列表和双向链表都会记录表头和表尾的偏移量。
这样⼀来，对于List类型的LPOP、RPOP、LPUSH、RPUSH这四个操作来说，它们是在列表的头尾增删元

素，这就可以通过偏移量直接定位，所以它们的复杂度也只有O(1)，可以实现快速操作。

(1)O(N)命令关注N的数量

例如hgetall、lrange、smembers、zrange、sinter等并非不能使用，但是需要明确N的值。有遍历的需求可以使用hscan、sscan、zscan代替。

第⼀个建议是，你可以使⽤SSCAN、HSCAN命令分批返回集合中的数据，减少对主线程的阻塞。
第⼆个建议是，你可以化整为零，把⼀个⼤的Hash集合拆分成多个⼩的Hash集合。这个操作对应到业务层，就是对业务数据进⾏拆分，按照时间、地域、⽤⼾ID等属性把⼀个⼤集合的业务数据拆分成多个⼩集合数据。例如，当你统计⽤⼾的访问情况时，就可以按照天的粒度，把每天的数据作为⼀个Hash集合。
最后⼀个建议是，如果集合类型保存的是业务数据的多个属性，⽽每次查询时，也需要返回这些属性，那么，你可以使⽤String类型，将这些属性序列化后保存，每次直接返回String数据就⾏，不⽤再对集合类型做全量扫描了。
(2)禁用命令

禁止线上使用keys、flushall、flushdb等，通过redis的rename机制禁掉命令，或者使用scan的方式渐进式处理。在线上应⽤Redis时，就需要禁⽤这些命令。具体的做法是，管理员⽤rename-command命令在配置⽂件中对这些命令进⾏重命名，让客⼾端⽆法使⽤这些命令。

KEYS，按照键值对的key内容进⾏匹配，返回符合匹配条件的键值对，该命令需要对Redis的全局哈希表进⾏全表扫描，严重阻塞Redis主线程；集群场景下更不能使用，如果Redis阻塞超过10秒，如果有集群的场景，可能导致集群判断Redis已经故障，从而进行故障切换；以上的情况严重会导致应用程序出现雪崩的情况。
KEYS命令的性能随着数据库数据的增多而越来越慢
KEYS命令会引起阻塞，连续的 KEYS命令足以让 Redis 阻塞
channel:rule:store:storecode*

FLUSHALL，删除Redis实例上的所有数据，如果数据量很⼤，会严重阻塞Redis主线程；
FLUSHDB，删除当前数据库中的数据，如果数据量很⼤，同样会阻塞Redis主线程。
scan也应该避免使用。scan和keys的区别在于：scan只会扫部分表,通过游标接着往下扫，所以扫的数据比keys少，相对于keys比较不容易照成阻塞，但不代表它不会导致阻塞
(3)合理使用select

redis的多数据库较弱，使用数字进行区分，很多客户端支持较差，同时多业务用多数据库实际还是单线程处理，会有干扰。

不推荐使用多个db，使用多个db会增加Redis的负担，每次访问不同的db都需要执行SELECT命令，如果业务线不同，建议拆分多个实例，还能提高单个实例的性能

(4)使用批量操作提高效率

原生命令：例如mget、mset。
非原生命令：可以使用pipeline提高效率。
但要注意控制一次批量操作的元素个数(例如500以内，实际也和元素字节数有关)。注意两者不同：

原生是原子操作，pipeline是非原子操作。
pipeline可以打包不同的命令，原生做不到
pipeline需要客户端和服务端同时支持。
(5)Redis集群版本在使用Lua上有特殊要求

①所有key都应该由 KEYS 数组来传递，redis.call/pcall 里面调用的redis命令，key的位置，必须是KEYS array, 否则直接返回error，"-ERR bad lua script for redis cluster, all the keys that the script uses should be passed using the KEYS arrayrn" ②所有key，必须在1个slot上，否则直接返回error, "-ERR eval/evalsha command keys must in same slotrn"

(6)避免使用复杂度高的命令

如果在使用Redis时，发现访问延迟突然增大建议查看一下Redis的慢日志。Redis提供了慢日志命令的统计功能，通过以下设置，就可以查看有哪些命令在执行时延迟比较大。

首先设置Redis的慢日志阈值，只有超过阈值的命令才会被记录，这里的单位是微妙，例如设置慢日志的阈值为5毫秒，同时设置只保留最近1000条慢日志记录：

# 命令执行超过5毫秒记录慢日志   CONFIG SET slowlog-log-slower-than 5000   # 只保留最近1000条慢日志   CONFIG SET slowlog-max-len 1000  

设置完成之后，所有执行的命令如果延迟大于5毫秒，都会被Redis记录下来，执行SLOWLOG get 5查询最近5条慢日志：

127.0.0.1:6379> SLOWLOG get 5 1) 1) (integer) 32693       # 慢日志ID    2) (integer) 1593763337  # 执行时间    3) (integer) 5299        # 执行耗时(微妙)    4) 1) "LRANGE"           # 具体执行的命令和参数       2) "user_list_2000"       3) "0"       4) "-1" 2) 1) (integer) 32692    2) (integer) 1593763337    3) (integer) 5044    4) 1) "GET"       2) "book_price_1000" ...

通过查看慢日志记录，就可以知道在什么时间执行哪些命令比较耗时，如果你的业务经常使用O(n)以上复杂度的命令，例如sort、sunion、zunionstore，或者在执行O(n)命令时操作的数据量比较大，这些情况下Redis处理数据时就会很耗时。

如果你的服务请求量并不大，但Redis实例的CPU使用率很高，很有可能是使用了复杂度高的命令导致的。

解决方案就是，不使用这些复杂度较高的命令，并且一次不要获取太多的数据，每次尽量操作少量的数据，让Redis可以及时处理返回。

(7)避免一次操作过多数据

查询数据时，一次尽量获取较少的数据，在不确定容器元素个数的情况下，避免使用LRANGE key 0 -1，ZRANGE key 0 -1这类操作，应该设置具体查询的元素个数，推荐一次查询100个以下元素

写入数据时，一次尽量写入较少的数据，例如HSET key value1 value2 value3...，控制一次写入元素的数量，推荐在100以下，大数据量分多个批次写入

(8)慎⽤MONITOR命令

Redis的MONITOR命令在执⾏后，会持续输出监测到的各个命令操作，所以，我们通常会⽤MONITOR命令 返回的结果，检查命令的执⾏情况。 但是，MONITOR命令会把监控到的内容持续写⼊输出缓冲区。如果线上命令的操作很多，输出缓冲区很快 就会溢出了，这就会对Redis性能造成影响，甚⾄引起服务崩溃。 所以，除⾮⼗分需要监测某些命令的执⾏（例如，Redis性能突然变慢，我们想查看下客⼾端执⾏了哪些命 令），你可以偶尔在短时间内使⽤下MONITOR命令，否则，我建议你不要使⽤MONITOR命令。

3.客户端使用

(1)避免多个应用使用一个Redis实例

不相干的业务拆分，公共数据做服务化。

(2)使用连接池

可以有效控制连接，同时提高效率。

(3)熔断功能

高并发下建议客户端添加熔断功能(例如netflix hystrix)

(4)合理的加密

(5)数据很重要，请使用 Try/Except

如果必须确保关键性的数据可以被放入到 Redis 的实例中，我强烈建议将其放入 try/except 块中。几乎所有的Redis客户端采用的都是“发送即忘”策略，因此经常需要考虑一个 key 是否真正被放到 Redis 数据库中了。

(6)解决Redis 的并发竞争 Key 问题

同时有多个子系统去 Set 一个 Key。不推荐使用 Redis 的事务机制。因为生产环境，基本都是 Redis 集群环境，做了数据分片操作。一个事务中有涉及到多个 Key 操作的时候，这多个 Key 不一定都存储在同一个 redis-server 上。

①如果对这个 Key 操作，不要求顺序

这种情况下，准备一个分布式锁，大家去抢锁，抢到锁就做 set 操作即可，比较简单。

②如果对这个 Key 操作，要求顺序

假设有一个 key1，系统 A 需要将 key1 设置为 valueA，系统 B 需要将 key1 设置为 valueB，系统 C 需要将 key1 设置为 valueC。

期望按照 key1 的 value 值按照 valueA > valueB > valueC 的顺序变化。这种时候我们在数据写入数据库的时候，需要保存一个时间戳。

假设时间戳如下：

系统 A key 1 {valueA  3:00}

系统 B key 1 {valueB  3:05}

系统 C key 1 {valueC  3:10}

那么，假设系统 B 先抢到锁，将 key1 设置为{valueB 3:05}。接下来系统 A 抢到锁，发现自己的 valueA 的时间戳早于缓存中的时间戳，那就不做 set 操作了

(7)控制Redis实例的容量

Redis单实例的内存⼤⼩都不要太⼤，根据我⾃⼰的经验值，建议你设置在 2~6GB ，键的数量控制在1千万以内。这样⼀来，⽆论是RDB快照，还是主从集群进⾏数据同步，都能很快完成，不会阻塞正常请求的处理。

不要什么都往Redis中放，尽量放些QPS比较高的数据，内存的开销很昂贵的，可以考虑硬盘存放。

(8)控制并发连接数量

短时间写入大量缓存redis，很有可能导致连接打满，无法获取到缓存
————————————————
版权声明：本文为CSDN博主「编程广角镜」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_39403646/article/details/134010001