一、数据库划分环境
保证各环境的MySQL服务器有对应的用户权限,权限划分明确:
· 开发环境:开发人员可拥有DDL\DML权限
· 测试环境:开发人员可拥有DDL\DML权限
· 生产环境:开发人员不允许直接操作数据库,仅对应用开放DML权限,禁止%网段的用户存在
二、命名规范
1、基本命名规范
· 库、表的命名要见名知意,不要超过32个字符,多个词汇以下划线分隔,词汇用英文而非拼音
· 库、表、字段全部采用小写,不要使用驼峰命名
· 避免使用到保留字,如关键字index
· 临时库、表名以tmp为前缀,以日期为后缀;备份库、表必须以bak为前缀,并以日期为后缀
2、索引命名规范
· 非唯一索引:按照“idx_索引名称”命名
· 唯一索引:按照“uniq_索引名称”命名
三、SQL规范
· 数据库字符集推荐设置为默认utf8mb4,数据库排序规则为utf8mb4_general_ci。因为utf8无法保存Emoji表情,如果JDBC连接串设置了characterEncoding为utf8或者做了上述配置仍旧无法正常插入emoji数据的情况,需要在代码中指定连接的字符集为utf8mb4。
· 单张表的索引不建议超过5个,列也不应该有太多
· 所有表、字段均应增加comment注释信息说明表、字段所代表的含义
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`name` varchar(255) DEFAULT NULL COMMENT '账户名',
`balance` int(11) DEFAULT NULL COMMENT '余额',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
· 每张表都需要定义主键,建议将第一个id字段配置为自增主键,禁止使用varchar类型作为主键语句设计
· 表必须包含create_time和update_time字段,即表必须包含创建时间和修改时间的字段,便于审计、跟踪记录、ETL任务
· 用尽量少的存储空间来存数一个字段的数据,能用int就不用char或varchar,能用tinyint的就不用int,比如取值范围为0-80时,使用TINYINT UNSIGNED
· 使用UNSIGNED存储非负数值
· 精确浮点数使用DECIMAL替代FLOAT和DOUBLE
· 存储时间字段时存储年使用YEAR类型、存储日期使用DATE类型、存储时间(精确到秒)使用TIMESTAMP类型,· 建议使用INT UNSIGNED存储IPV4
· 编写好的SQL先explain再执行,观察SQL是否有使用索引
1、基本命名规范
· 库、表的命名要见名知意,不要超过32个字符,多个词汇以下划线分隔,词汇用英文而非拼音
· 非唯一索引:按照“idx_索引名称”命名
· 唯一索引:按照“uniq_索引名称”命名
三、SQL规范
· 数据库字符集推荐设置为默认utf8mb4,数据库排序规则为utf8mb4_general_ci。因为utf8无法保存Emoji表情,如果JDBC连接串设置了characterEncoding为utf8或者做了上述配置仍旧无法正常插入emoji数据的情况,需要在代码中指定连接的字符集为utf8mb4。
· 所有表、字段均应增加comment注释信息说明表、字段所代表的含义
CREATE TABLE `account` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id', `name` varchar(255) DEFAULT NULL COMMENT '账户名', `balance` int(11) DEFAULT NULL COMMENT '余额', `create_time` datetime NOT NULL COMMENT '创建时间', `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
· 每张表都需要定义主键,建议将第一个id字段配置为自增主键,禁止使用varchar类型作为主键语句设计
· 表必须包含create_time和update_time字段,即表必须包含创建时间和修改时间的字段,便于审计、跟踪记录、ETL任务
· 用尽量少的存储空间来存数一个字段的数据,能用int就不用char或varchar,能用tinyint的就不用int,比如取值范围为0-80时,使用TINYINT UNSIGNED
· 使用UNSIGNED存储非负数值
· 精确浮点数使用DECIMAL替代FLOAT和DOUBLE
· 存储时间字段时存储年使用YEAR类型、存储日期使用DATE类型、存储时间(精确到秒)使用TIMESTAMP类型,· 建议使用INT UNSIGNED存储IPV4
explain select * from user where id=10086 or name='tanglu';
· 操作delete或者update语句加limit。一方面可以降低SQL写错的代价,如果误操作了数据也只是少部分,通过binlog恢复更方便;另一方面还可以避免长事务锁表,影响相关业务无法使用;最后还能防止数据量大时CPU跑高的问题。
delete from euser where age > 30 limit 200
· 如果语句存在where、order by、group by子句,查看多表关联的列是否已加索引,优先考虑组合索引
select * from user where address ='深圳' order by age ;
alter table user add index idx_address_age (address,age);
· 修改或删除重要数据前先备份,先备份,先备份
· 避免在索引字段上使用计算、NOT(!=、<>)、IN、NOT IN、IS NULL、IT NOT NULL、%模糊查询、数据类型隐式转换等情况,否则会导致索引失效
select * from user where userid =123; #不加单引号时MySQL会把数据类型做隐式转换,导致索引失效
select * from user where userid ='123';
· 尽量把所有列定义为NOT NULL并且设置默认值,因为NULL列需要额外的存储空间,且 MySQL 内部需要做特殊的处理。MySQL 难以优化 NULL 列的查询,它会使索引、索引统计更加复杂。
· 如果一次性需要修改或更新大量数据需分多次进行。避免产生大事务、主从延迟、CPU跑高等问题
发表评论: