mysql操作规范

mysql操作规范

1. 遵循约定

1.1文档适用范围

该文档可以用于开发人员表结构设计及DBA人员做Schema Review约束使用。

1.2 MySQL特点

  • mysql是单进程多线程，不像Oracle那样是多进程的。

  • 每个mysql内部线程同时只能用到一个逻辑cpu线程。

  • 每个SQL同时只能用到一个逻辑CPU线程。

  • 无执行计划缓存（无类似ORACLE的library cache），不过MySQL的执行计划解析比较轻量级，效率还不错，这方面不会是瓶颈。

  • 没有thread pool时，如果有瞬间大量连接请求，性能会急剧下降。

    2. 开发规范

    2.1 命名规范

  • 库名、表名、字段名必须使用小写字母，并采用下划线分割

  • 库名、表名、字段名禁止查过32个字符

  • 库名、表名、字段名必须见名知意，命名与业务产品线等相关联

  • 库名、表名、字段名禁止使用MySQL关键字

  • 临时库、表名必须以tmp为前缀，并以日期为后缀，例如tmp_api_20190103。

  • 备份库、表名必须以bak为前缀，并以日期为后缀，例如bak_api_20190103。

    2.2 基础规范

  • 所有业务表使用InnoDB存储引擎。

  • 创建库和表的字符集和排序规则设置为DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

  • 所有表都需要添加注释;除主键外的其他字段都需要添加注释。推荐采用英文标点，避免出现乱码。

  • 禁止在数据库中存储图片、文件等大数据。

  • 每张表数据量控制在5000W以内。

  • 禁止在线上做数据库压力测试。

  • 禁止从开发、测试环境直连数据库。

  • 禁止开发人员操作线上数据库。

    2.3 库表设计

    2.3.1 总体原则

  • 禁止使用分区表

  • 将大字段、访问频率低的字段拆分到单独的表存储，分离冷热数据。

  • 推荐使用HASP进行水平拆分,表名后缀使用十进制数，数字必须从0开始。例如将member_api进行按hash散表，member_api_0,...member_api_31。

2.3.2 主键设计

  • 建议使用UNSIGNED INT/UNSIGNED BIGINT并且自增做为主键，顺序insert效率更高，表空间碎片率更低

  • 主键避免采用字符型，如VARCHAR/CHAR/UUID，会导致原本可以顺序写入的请求变成随机写入，效率更低。

  • 表拆分时如果需要全局唯一主键，可以采用发号器服务、redis全局自增，或某个全局DB里统一分配等多种方式生成全局唯一值。

2.3.4 字段设计

  • 建议为每个字段设置为NOT NULL属性，并设个默认值，可以减少存储开销及避免索引失效问题。同时为了避免程序写入失败，还可以增加默认值。

  • 建议使用UNSIGNED存储非负数值

  • 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。例如与钱相关的数据。

  • INT类型固定占用4字节存储，例如INT(4)仅仅代表显示字符宽度为4位，存储长度。

  • 强烈建议使用TINYINT类型来代替ENUM类型。ENUM类型在需要修改或增加枚举值时，需要在线DDL，成本较高；ENUM类型如果含有数字类型，可能会引起默认值混淆。

  • 不到万不得已不允许使用TEXT、BLOB数据类型。如果非要使用这种字段类型，考虑单独建一表，每次取数据利用主键只取1行，不要把TEXT、BLOB类型字段列表显示，TEXT、BLOB字段类型在排序时会产生大量IO消耗。

  • 禁止在数据库中存储明文密码。

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

  • 使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数。

  • 区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型，存储时间(精确到秒)建议使用TMISTAMP。

  • '创建时间'，建议使用: created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'

  • '更新时间'，建议使用: updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

  • 禁止往mysql时间类型的字段中传'0000-00-00 00:00:00' 这种值。date 类型默认值建议设为 '1001-01-01'，datetime 类型默认值建议设为'1001-01-01 00:00:00'，timestamp 类型默认值建议设为'1971-01-01 00:00:00'

2.3.5 索引设计

  • 单表中索引数量不建议超过5个。SSD硬盘现在可以稍微放宽限制。

  • 单个索引中的字段数不建议超过5个。

  • 索引名必须全部使用小写。

  • 唯一索引按照uniq_字段名进行命名。例如uniq_name。

  • 非唯一索引按照idx_字段名称进行命名，例如idx_member_id。

  • 组合索引建议包含所有字段名，过长字段名可以采用缩写格式，如idx_age_name_add。

  • 禁止冗余索引。

  • 禁止重复索引。

  • 禁止使用外键。

  • 联表查询时，JOIN列的数据类型必须相同，并且要建索引。

  • 不要在低基数列上建立索引，例如性别。

  • 选择区分度大的列建立索引。组合索引中，区分度大的字段放在最前面。

  • 对字符串使用前缀索引，前缀索引长度不超过8个字符。

  • 不要对过长的VARCHAR字段建立索引。优先考虑前缀索引。或添加CRC32 CRC64或MD5伪列并建立索引。例：SELECT * FROM urls WHERE url = 'http://wwww.shopperplus.com'; 建立crc_url伪列，程序使用时对url取其crc32值，然后select * from urls where crc_url=1215054814

  • 合理创建联合索引，(a，b，c)相当于(a)、(a,b)、(a,b,c)三个索引

  • 合理使用覆盖索引减少IO，避免排序。

  • 使用prepared statement，可以提升性能并避免SQL注入。

  • 用IN代替OR。SQL语句中的IN包含的值不应过多，应该少于1000个。

  • 禁止隐式转换。数值类型禁止加引号；字符串类型必须加引号。表进行join时，如果表或者库的字符集不一致也会发生隐式转换。

  • 避免使用CROSS JOIN和子查询，必要时推荐使用JOIN代替子查询。

  • 避免在MySQL中进行数学运算和函数运算。比如select format(mydate,’yyyy-mm-dd’)。

    2.4 SQL设计

  • 使用prepared statement，可以提升性能并避免SQL注入。

  • 用IN代替OR。SQL语句中的IN包含的值不应过多，应该少于1000个。

  • 禁止隐式转换。数值类型禁止加引号；字符串类型必须加引号。表进行join时，如果表或者库的字符集不一致也会发生隐式转换。

  • 避免使用CROSS JOIN和子查询，必要时推荐使用JOIN代替子查询。

  • 避免在MySQL中进行数学运算和函数运算。比如select format(mydate,’yyyy-mm-dd’)。MySQL新版本有所改善，MySQL5.7可以使用虚拟列，MySQL8.0开始支持函数索引。

  • 减少与数据库交互次数，尽量采用批量SQL语句。使用下面的语句来减少和db的交互次数：

INSERT ...ON DUPLICATE KEY UPDATE
REPLACE INTO
INSERT IGNORE
INSERT INTO VALUES()


- 避免在MySQL中进行数学运算和函数运算。

- 拆分复杂SQL为多个小SQL，避免大事务。简单SQL可以减少锁表时间，可以有效的使用多核CPU。

- 获取大量数据时，建议分批次获取数据，每次获取数据少于2000条，结果集应小于1M.

- 在业务逻辑准确的情况下使用UNION     ALL 代替UNION

- 统计行数用COUNT(*)。

- SELECT只获取必要的字段，禁止使用SELECT     *。

- SQL中避免使用now()、rand()、sysdate()、current_user()等结果不确定的函数。

- INSERT语句必须指定字段列表，禁止使用INSERT     INTO TABLE()。

- 禁止单条SQL语句同时更新多个表。

- 禁止在update时将”,”写成”and”,非常非常非常危险。
正确示例：
```mysql
update table set     uid=uid+1000,gid=gid+100 where id<=2;

错误示例：

update table set uid=uid+1000 and gid=gid+1000 where     id<=2;

此时uid=uid+1000 and gid=gid+1000将作为值(1或0)赋值给uid，并且无warning!

  • 禁止使用存储过程、触发器、视图、自定义函数等。

  • 建议使用合理的分页方式以提高分页效率。

  • 提交线上建表改避免使用存储过程、触发器、视图、自定义函数等。

  • 禁止在业务从库上执行后台管理和统计类功能的QUERY，必要时为统计类单独建从库。

  • 程序应有捕获SQL异常的处理机制，必要时通过ROLLBACK显式回滚。

  • 重要SQL必须建索引：update、delete的where条件列、order by、group by、distinct字段，夺标join字段。

  • 禁止使用%前导查询，例如: like ’%’，无法利用到索引。o

  • 禁止使用负向查询，例如not in、!=、not like 。

  • 使用EXPLAIN判断SQL语句是否合理利用索引，尽量避免extra列出现:Using File sort、Using Temporary。

  • 禁止使用order by rand()。

  • 多使用等值操作，少使用或不使用非等值操作。WHERE条件中的非等值(IN、BETWEEN、<、<=、>、>=)会导致后面的条件使用不了索引，因为不能同时用到两个索引条件。

  • 表的连接条件最好是查询结果集最少的为驱动表，后续表要有良好的索引

  • limit N以及limit M,N场景中，不管是M还是N的值都不宜过大（一般不超过一万）。当M（起始值）较大时，建议用延迟关联的方式优化，例如：

    select     * from (select * from t1 where id>(select id from t1 order by id desc     limit 935510,1) limit 10) t  order by  id desc或select * from t1      inner join (select id from t1 order by id desc limit 935510,1) t2     using (id)

3. 行为规范

  • 表结构变更必须通知DBA进行审核。

  • 禁止有SUPER、DDL、DCL权限的应用账号存在。

  • 重大项目的数据库方案选型和设计必须提前通知DBA参与。

  • 批量导入、导出数据必须通知DBA审核，并在执行过程中观察服务。

  • 批量更新数据，如UPDATE、DELETE操作，必须通知DBA审核，并在执行过程中观察服务。

  • 产品出现非数据库导致故障时，如被攻击，必须及时通知DBA，便于维护服务稳定。

  • 业务部门推广活动或上线新功能，必须提前通知DBA进行服务和访问量评估，并流出必要时间以便DBA完成扩容。

  • 出现业务部门认为误操作导致数据丢失，需要恢复数据的，必须第一时间通知DBA，并提供准确时间点、误操作语句等重要线索。

  • 提交线上建表该表需求，必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE、SELECT)，便于DBA进行审核和优化。

  • 对同一个表的多次ALTER操作必须合并为一次。

  • 不要在数据库中存放业务逻辑。

4. FAQ

1、库名、表名、字段名禁止超过32个字符。

库名、表名、字段名支持最多64个字符，但为了同意规范、易于辨识以及减少传输量，禁止超过32个字符。

2、库名、表名、字段名禁止使用MySQL关键字。

当库名、表名、字段名等属性含有保留字时，SQL语句必须使用反引号引用属性名称，这将使得SQL语句书写、SHELL脚本中的变量的转义变得非常复杂。【MySQL关键字】

3、禁止在数据库中存储明文密码。

采用加密字符串存储密码，并保证密码不可解密，同时采用随机字符串加盐保证密码安全。防止数据库数据被公司内部人员或黑客获取后，采用字典攻击等方式暴力破解用户密码。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/carry00/p/13860401.html