mysql开发要注意什么_mysql开发注意事项

1.重要行为规范

1) 不允许擅自导出线上数据

2) 批量更新，如洗数据，避开高峰期，并通知运维team，直接执行sql的需要由运维同事操作

3) 统计分析、跨租户管理、批量数据操作、导入导出功能上线前必须经过审查

4) 复杂SQL，因各种特殊原因不符合本规范的SQL需要经过审查上线

5) 所有DDL操作需要找运维组执行，并经过审查(建表、改表、建索引、改索引、建存储过程)

2. 命名规范

1) 库名、表名、索引名必须使用小写字母，并采用下划线分割

比如表 eb_product，eb代表业务模块名，关系表则使用eb_product_category，超长使用简写

普通索引以idx_col1col2命名，唯一索引以uk_col1col2命名。如idx_companyid_personid

2) 字段名使用全小写字母

如companyid、personid、username

3) 库名、表名、字段名、索引名禁止超过32个字符，须见名之意

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

4) 库名、表名、字段名、索引名禁止使用MySQL保留字

5) 临时库、表名必须以tmp为前缀，并以日期为后缀

如eb_product_tmp20170225

6) 备份库、表必须以bak为前缀，并以日期为后缀

如eb_product_bak20170225

3. 表设计规范

1) 使用Innodb存储引擎

2) 使用UTF8MB4字符集，兼容EMOJ

3) 所有表要加注释

表注释

字段注释

类似status型的字段需指明主要值的含义，如”0-离线，1-在线”

4) 控制单表字段数量

单表字段不允许超过30个，再多的话考虑垂直分表，一是冷热数据分离，二是大字段分离，三是常在一起做条件和返回列的不分离

5) 所有表都必须要显式指定主键

所有表必须显式指定主键

主键使用bigint类型

主键使用framework中的id生成器获取(建设中)

6) 禁止使用外键

7) 适度使用存储过程、视图，禁止使用触发器、事件

使用存储过程必须报DBA

8) 分表策略

单表一到两年内数据量超过500w或数据容量超过10G考虑分表

不使用MySQL分区表

4.字段设计规范

1) 所有字段设置为not null

索引字段一定要定义为not null 。因为null值会影响cordinate统计，影响优化器对索引的选择

如果不能保证insert时一定有值过来，定义时使用default ‘’ ，或 0

2) 同一意义的字段定义必须相同

比如不同表中都有userid 字段，那么它的类型、字段长度要设计成一样

3) 禁止使用float、double类型，使用decimal存储浮点型

对于货币、金额这样的类型，使用decimal，如 decimal(9,2)。float默认只能精确到6位有效数字

4) 用尽量少的存储空间来存数一个字段的数据

能使用int就不要使用varchar

能用varchar(16)就不要使用varchar(256)

5) 对于type、status类的字段用数值而不要直接用字符串

如obj_type用1,2,3 代替 'feed','blog','task'

使用tinyint或者int

减少存储空间

加快查询速度

减少数据传输IO

减少coding时拼写错误的可能

6)表达布尔值的字段，数据类型采用unsigned tinyint

7) 使用timestamp存储时间

datetime和timestamp类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为 1970-01-01 08:00:01到2038-01-19 11:14:07，timestamp就够用。

timestamp可以在insert/update行时，自动更新时间字段(如updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

timestamp显示与时区有关，内部总是以 UTC 毫秒 来存的。还受到严格模式的限制

8) 禁止使用blob、text类型保留大文本、文件、图片，使用其他方式存储，MySQL只保存指针信息

TEXT类型与VARCHAR类似，存储可变长度，最大限制也是2^16，但是它20bytes以后的内容是在数据页以外的空间存储，对它的使用需要多一次寻址，没有默认值。一般用于存放容量平均都很大、操作没有其它字段那样频繁的值。

BLOB可以看出varbinary的扩展版本，内容以二进制字符串存储，无字符集，区分大小写，不要在数据库里存储图片。

text和blob上面一般不会去建索引，而是利用全文搜索引擎。

另外尽可能把text/blob拆到另一个表中

9) 字段允许适当冗余，以提性能，但必须考虑数据同步的情况

冗余字段不能是频繁修改的字段。

冗余字段不能是varchar超长字段，更不能是text字段

5.索引设计规范

1) 单表索引个数限制

索引是双刃剑，会增加维护负担，增大IO压力，索引占用空间是成倍增加的

单张表的索引数量控制在5个以内，每个索引字段不超过5个。

2) 禁止重复、冗余索引

对同一字段建多个索引

(a)和(a,b)是冗余索引,索引最左原则

InnoDB表是一棵索引组织表，普通索引最终指向的是主键地址，所以把主键做最后一列是多余的。如shopid作为主键，联合索引(userid,shopid)上的shopid就完全多余

3) 索引尽量建在选择性高的列上

不在低基数列上建立索引，例如性别、类型。但有一种情况，idx_feedid_feedtype (feedid,feedtype)，如果经常用feed_type = 1 比较，而且能过滤掉90%行，那这个组合索引就值得创建。

索引选择性计算方法(基数 ÷ 数据行数) Selectivity = Cardinality / Total Rows 越接近1说明使用索引的过滤效果越好

4) 不在频繁更新的列上创建索引

5)重要的SQL必须使用索引

update、delete语句中的where条件列

多表、大表join的连接字段

order by、group by、distinct字段

6) 对字符串字段使用前缀索引

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

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

6.业务设计规范

1) 在所有业务表中建社区ID字段(companyid)字段

减少联查

方便客户数据导出

利于多租户数据安全保护

2) 重要业务数据表、主数据表建createtime、updatetime字段

方便未来按时间做分表或清理数据

方便出问题时追查数据变更时间

7.SQL规范

1) 任何新的select,update,delete上线，都要先explain，看索引使用情况

update，delete 换成select再explain

非统计分析、批量执行业务SQL在线上还原库中执行时间不允许超过2s

SQL优化指标：至少要达到range级别，要求是ref级别，如果可以是consts最好

2) 杜绝直接 SELECT*读取全部字段

3) 使用prepared statement，禁止拼SQL

4) 禁止使用非同类型的列进行等值查询

隐式转换不使用索引

5) 禁止在where条件列上使用函数

对索引列使用函数导致索引失效

6) 禁止使用like '%xxx'

会导致索引失效，有这种搜索需求时，考虑全文搜索

7) 尽量不使用子查询，改用join

8) 不使用负向查询，如 not in/like

9) in的数量不超过500个

10)不在SQL中进行数学运算

MySQL不擅长数学运算和逻辑判断

无法使用索引

11)join表不超过三个，否则考虑优化业务设计或表设计

12)非统计分析类业务杜绝使用case when then等复杂语句

13) 执行批量插入业务时，减少与数据库交互的次数，尽量采用批量SQL语句

14) 不要使用count(列名)或count(1)来替代count(*)，因为count(*)会统计值为NULL的，其他则不会

15)不写危险SQL

带有companyid条件的SQL，companyid必须强制有值

杜绝where 1=1 这样无意义或恒真的条件

SQL中不允许出现DDL语句 "$"