一、数据库设计
一般都使用 INNODB 存储引擎,除非读写比率 < 1%, 才考虑使用 MYISAM 存储引擎;其 他存储引擎请在 DBA 的建议下使用。
Stored procedure (包括存储过程,函数,触发器) 对于 MYSQL 来说还不是很成熟, 没有完善的出错记录处理,不建议使用。
UUID (),USER () 这样的 MySQL INSIDE 函数对于复制来说是很危险的,会导致主备数据不一致,所以请不要使用。如果一定要使用 UUID 作为主键,让应用程序来产生。
不要使用外键约束,如果数据存在外键关系,请在程序层面实现。
采用 UTF8 编码。
二、数据库对象
1、创建表的时候必须指定主键,并且主键建立后最好不要再有数据修改的需求
mysql从5.5版本开始默认使用innodb引擎,innodb表是聚簇索引表,也就是说数据通过主键聚集( 主键下存储该行的数据,索引指向主键值),如下图所示:
正是由于这种解构,如果后续对主键对应的值进行修改,就会导致索引节点的频繁分裂,性能会下降非常厉害。因此推荐开发的同事们使用和业务没有任何关联的自增id来做主键(切记不要使用uuid来做主键),此外也可以考虑使用其他的方式来生产自增的ID,比如使用Twitter的snowflake算法或者zk的DistributedAtomicLong来间接实现。使用自增主键而不是uuid做主键的优点主要有如下几点:
a、占用的数据量更小
b、数据顺序递增,不会导致索引节点的频繁分裂
c、数字类型比字符类型效率更高
正确招式:使用自增id作为主键
2、创建表选择字段的时候,在符合业务需求的情况下尽量小,数据类型尽量简单
数据类型尽量简单很好理解,比如,使用数字类型要比使用字符类型效率更高,因为不涉及到校对规则和字符集。而字段尽量小,说的是在满足业务需求的情况下,尽可能小。这么做的好处是:更小的字段类型占用更少的内存,占用更少的磁盘空间,占用更少的磁盘IO,以及占用更少的带宽。举个例子,如果一个varchar(50)的字段,不管你存储了几个字符,在查询的时候仍然需要申请50 byte的内存
3、创建表的时候显式申明存储引擎
这个没什么说的,使用什么引擎就申明什么引擎,防止被默认。有句话说得好:如果你不选择,那你就被选择,被选择的结果不一定是你自己想要的。命运还是要掌握的自己手里。
正确招式:显式申明ENGINE=xxxx;
4、创建表的时候显式申明字符集
如果不不想被乱码困扰,就老老实实的申明字符集,还是那句话:如果你不选择,那你就被选择,被选择的结果不一定是你自己想要的。命运还是要掌握的自己手里。
正确的招式:显式申明DEFAULT CHARSET=xxxx;
5、创建表的时候对经常要查询的列添加索引或者组合索引
索引直接影响后面的查询性能,尤其是数据量越大的时候,影响越明显。作为一个从事DBA生涯超过5年的DBA,遇到过无数次由于没有添加索引,导致的线上故障,请各位开发和DBA的同事谨记,一定要对经常要查询的列添加索引或者组合索引,防止线上事故的发生。
正确招式:建表的时候就添加对应INDEX
6、创建表的时候对字段和表添加COMMENT
这个主要是方便后续的维护,之前在小公司做DBA的时候,接手数据库时由于没有任何的COMMENT,导致完全不知道对应的表和字段是做什么用的,不得不经常麻烦开发的同事解释。这个小技巧用一个经典的广告词来解释就是:你好,我也好。
正确招式:对列和表都添加COMMENT做详细说明;
7、创建表的时候不要添加drop操作
有的开发同事在创建表之前喜欢添加, DROP TABLE IF EXISTS TABLEXXXX; 然后再来个CREATE TABLE,建议不要这么干,因为我之前遇到过由此带来的线上故障。将线上正在使用的表drop掉了。建议大家创建表的时候修改为:CREATE TABLE IF NOT EXISTS TABLEXXX .......;
正确招式:CREATE TABLE IF NOT EXISTS TABLEXXX .......;
8、创建表的时候,字段尽量不要为NULL
解决办法就是设置字段为NOT NULL,并设置字段的默认值。字段尽量不要为NULL的原因如下:
a、NULL需要占用额外的空间存储;
b、进行比较的时候会更复杂,还会导致你select (column)的时候不准确
c、含有NULL值的列,会对SQL优化产生影响,尤其是组合索引中
具体NULL会带来的问题大家可以查阅:https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html
正确招式:NOT NULL DEFAULT 'xxxxx';
附上1个机器简单的标准SQL 范例,大家创建表的时候可以参照下面的范例SQL来创建,范例如下:
CREATE DATABASE IF NOT EXISTS `dev_ops_db`;
CREATE TABLE IF NOT EXISTS `dev_ops_db`.`monitor_table_holiday` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`holiday_date` date NOT NULL default '1999-01-01' COMMENT '节假日日期',
`holiday_name` varchar(36) NOT NULL default '' COMMENT '节假日名称',
PRIMARY KEY (`id`),
KEY `holiday_date` (`holiday_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='节假日数据表';
-- 数据库建表语句的规范小结
建表语句的规范:
1.字段的设计
A. 数据类型尽量用数字类型,数字类型的比字符类型的要快很多。
B. 数据类型尽量小,这里的尽量小是指在满足可以预见的未来需求的前提下的,但是有不能太小,
上次监控系统里面的表 mon_tair_stat_detail_2012_1 的data_size 和 use_size定义的是int(15) 实际上最大长度是int(11) 只有bigint才有(15).
所以在建表的时候一定要预估这个字段最大的长度到底是多少。
C. 尽量不要允许NULL,除非必要,可以用NOT NULL+DEFAULT 的值来代替。
D. 少用TEXT和IMAGE,二进制字段的读写是比较慢的,而且,读取的方法也不多,大部分情况下最好不用。
2.字段名字的定义
A. 对字段名字的命名也要遵循一个规范。比如说时间: 目前我们定义时间的时候 都是create_time, modify_time,以后建表的时候,都按照这个标准来,就不要写成createtime这样的。
B. 布尔类型的值均以 is、has、exist 或者 can开头。
C. 记录行的状态都用status, 逻辑删除的标识用is_deleted。
D. 新建的表与之前的表的字段有相似或者相同的字段,字段的名称和类型也必须相同,切记不能随意的重新命名,例如:token_md5 就不要命名成tokenmd5了。
E. 每个字段的COMMENT必须写清楚,枚举类型必须写清楚每个值到底是什么意思。
F. 表名 字段名字统一用小写。
G.索引名称统一用字段名称,多个字段的索引加_ 比如 (uid,type) 索引名就是 uid_type。
H.字段名字不要用驼峰命名法 比如create_time 不要写成CreateTime.
I.字段命名关键字统一用下划线"_"分割,一般采用简写,关键字要准确,不能有歧义。
J.字段的排列顺序:自增列、int类型的、varchar类型、时间类型、状态类型(status,is_deleted)。基本原则是:主要内容在前,次要内容在后,不为空的在前,可以空的在后面。
K.时间类型尽量用 timestamp 4个字节,而不用datetime 8个字节。只用表示日期的字段用date类型。
L.时间类型自动修改,比如modify_time 在记录被修改的时候回被自动更新成当前的时间: `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
而create_time在记录被修改的时候则不必自动更新成当前的时间:`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'