mysql设计规范

1、Mysql设计规范1. 模型设计流程l 设计工具待定2. 命名规范2.1 基本原则数据模型中的表(Table)、字段(Column)、索引(Index)、视图(View)等数据库对象均应遵循以下命名规范：l 命名全部采用小写字母l 名称的各部分之间用下划线"_"连接l 命名应采用能够准确反映对象中文含义的英文单词或英文缩写，尽量不使用汉语拼音（中英文命名对照表详见附录A）l 命名长度一般不超过32个字符l 名称较长时使用英文缩写，相同缩写含义应相同l 禁止使用mysql保留字l 默认使用 innodb 存储引擎，使用其他引擎必须注明缘由l 使用utf8字符集l 表必须有主键

2、,推荐使用UNSIGNED自增列作为主键l 避免使用存储过程、触发器、视图、自定义函数等2.2 表名l 表名构成：模块名前缀 + 表主体名 + 表后缀（可选） l 模块名前缀：表归属模块l 表主体名：准确表达表含义，尽量使用英文全称，表名较长时使用英文缩写l 表后缀：可选项，表示关系（rlt）、明细（dtl）、周期（mm/dd）等表的类型2.3 字段名l 字段名需准确表达字段的含义l 可根据情况增加表名前缀l 不同表中相同字段名的含义、字段类型、取值含义应相同l 不使用数据库关键字l 尽量使用英文缩写l 尽量不使用单个单词（约定的通用字段除外）l 字段全部定义为 NOT NULL2.4 主键名

3、l 主键名构成：pk_ + 表名l 名称较长时使用表名缩写减小长度，注意表名缩写的唯一性2.5 索引2.5.1 索引命名l 索引名称全部使用小写l 非唯一索引按照“idx_字段名称_字段名称_字段名称”进行命名l 唯一索引按照“uq_字段名称_字段名称_字段名称”进行命名2.5.2 建立索引规范l 唯一索引由 3 个以下字段组成并且字段都是整形时，使用唯一索引作为主键。没有唯一索引或唯一索引不符合上述条件时，使用自增 id 作为主键。注意唯一索引不和主键重复l 单张表的索引数量控制在字段数的 20%以内，至多 5 个，索引数量过多会导致写入性能 的显著下降l 合理创建复合索引。首先要避免冗余，

4、ix_a_b_c 相当于同时创建了 ix-_a,ix_a_b,ix_a_b_c 三个索引；其次要避免索引过大，建议最大 4 列复合，列数过多很难提升索引的区分度，反而降低索引的性能l 合理使用覆盖索引l 对于长度大于 100 的 varchar 字段建立索引时，使用其他方法l 使用 EXPLAIN 判断 SQL 语句是否合理使用索引，尽量避免 extra 列出现 FILE SORT,USINGTEMPORARYl 索引不只用于 select 查询，update 和 delete 语句也需要根据 where 条件合理设计索引l where 条件中的非等值条件（IN,BETWEEN,<,&l

5、t;=,>,>=）会导致后面的条件无法使用索引2.6 视图l 视图名构成：v_ + 模块名前缀 + 视图主体名 + 后缀（可选） 3. 数据类型规范l 数值型- 存储精确浮点数使用 DECIMAL,替代 FLOAT 和 DOUBLE;- 根据字段长度选择合适的字段类型，如数字类型有 tinyint，smallint，mediuint，int，bigint 五种类型，分别占用 1byte，2byte，3byte，4byte，8byte。需要特别注意，int（10）和 int（2）无区别，应该采用 tinyint（2）替代 int（2）;- 尽量使用 tinyint 代替 enum 和

6、 set 类型，减少后台类型转换;- 使用UNSIGNED存储非负数值l 字符型- Varchar(N)中，N 表示的是字符数不是字节数，如 varchar（255），可以最大存储255 个汉字。N 值应尽可能小，单表 varchar 字段最大长度为 65536 个字节，在排序和创建临时表等内存操作时，会使用 N 值来申请内存，而非存储值的实际长度l 日期时间型- 存储年使用 year 类型，存储日期使用 date 类型- 存储日期使用 date 类型- 存储时间（精确到秒）使用timestamp 类型, 而非 datetime 类型, 因为 timestamp 使用 4 个字节，而 date

7、time 使用 8 个字节l 其它数据类型- 使用 int unsigned 存储 IP 地址，通过 MySQL 函数 inet_ntoa 和inet_aton 来进行转化- 避免使用 text、blob 字段类型4. SQL 语句设计l 使用 prepared statement，可以提升性能并且避免 SQL 注入l 降低 SQL 的复杂度，把 MySQL 尽量当做存储使用- 避免在 SQL 语句中进行数学运算、函数计算、逻辑判断等操作- 避免多表 join，尽量拆分成多条查询。如无法避免，在 join 表时应使用相同类型的列，并且在列上有索引- 避免使用存储过程、触发器、函数等l Inse

8、rt 语句使用 batch 提交（insert into table values （），（），（），）,values 的个数不超过 500；sql 语句中 in 包含的值不超过 500l Update，delete 语句避免使用 limit，如果确实需要分配处理大量数据，可以增加其他字段来限制每次处理的记录数，比如主键 idl 避免使用 select *l 避免使用 order by rand（），使用其他方式替换l 使用合理的分页方式以提高分页的效率l 统计表中记录数时使用 count(*),而不是 count（pk）或 count（1）l 数据库默认开启查询缓存，合理利用查询缓存提升 s

9、ql 效率l 当只需要 1 行数据时使用 limit 1l 拆分大的 delete 和 insertl Where 条件中使用合适的类型，数值不加引号，字符加引号，避免 MySQL 进行隐式类型转换，从而无法使用索引l 避免使用 or，对同一个字段将 or 改为 in，对不同字段将 or 改为 unionl 尽量避免负向查询，如 NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE 等l 针对同一张表的 alter table 操作，应该用逗号分隔，一次完成l 注意 MySQL 中 insert ignore into；insert on

10、 duplicate key update；replace into 的区别，在应用中合理使用5. 其他l 尽量减小事务：事务使用原则是即开即用，用完即关 ；事务无关操作放到事务外面, 减少锁资源的占用；在不破坏一致性前提下，使用多个短事务代替长事务l 可重复读（repeatable read）是 MySQL 的默认事务隔离级别，解决了脏读（dirty read）和幻读（phantom read）的问题，原则上禁止修改事务的隔离级别l 尽量使用短连接，完成查询后要主动释放连接，避免 MySQL 中出现大量 sleep 线程l 不管使用连接池还是直连 MySQL，执行查询前都需要考虑获取的 MySQL 连接可能已经断开，如断开可以重连。执行查询后都要检查查询是否成功，如不成功，考虑是否需要重新执行6. 模块划分模块名称英文名称模块前缀模块说明7. 文档列表l 逻辑模型（*.cdm）按业务需求功能及规范设计的逻辑模型（包括实体、关系、属性、码等）l 物理模型（*.pdm）从逻辑模型生成某特定数据库（如