数据库设计的规范：
一.数据库命名规范
二.数据库基本设计规范
三.数据库索引设计规范
四.数据库字段设计规范
五.数据库SQl开发规范
六.数据库操作行为规范
这是因为MySQL数据文件就是Linux下的一个问题，Linux是大小写敏感的，所以MySQL数据库和表的名称也是大小写敏感的：
Dbname和dbName代表两个不同的数据库
Table和table代表两个不同的表
为了不引起奇异，规定数据库名称和表名称必须使用小写字母+下划线的方式
2.所有的数据库名称禁止使用MySQL保留关键字
select id.username,from,age from tb_user;
上面的SQL语句有两个from，第一个是字段，第二个是SQL关键字，MySQL在执行查询时，并不能区分是关键字还是数据字段，那么需要：
select id.username,`from`,age from tb_user;
需要在from数据字段加反向引号，为了避免出现奇异，禁止数据字段使用SQL关键字。
MySQL关键字查询：
http://dev.mysql.com/doc/refman/5.7/en/keywords.html
3.数据库名称命名要见名识意，不要超过32字符
4.临时库表的前缀必须以tmp开头，并以日期为后缀
备份库表的前缀必须以bak开头，并以日期为后缀
5.所有存储相同数据的列名和列类型必须一致
e.g.我们在用户信息表和订单表中都会存储用户ID，customer_id,那么要求两个表中用户ID的名称和类型必须一致。
这样的列一般作为关联列使用，要是数据类型不一致，在进行联合查询的时候，MySQL需要先进行类型转换，有可能导致索引失效，降低查询效率。
二.数据库基本设计规范
1.表引擎使用InnoDB
在MySQL5.5以前Myisam是默认的存储引擎,MySQL5.5以后InnoDB为默认的存储引擎。
InnoDB在MySQL5.6及以后进行了很好的优化，InnoDB是一种支持事务、行级锁、有更好的恢复性，高并发下性能更好的存储引擎。
2.数据库和表的字符集统一使用UTF8编码格式
[说明]UTF8中一个中文字符占3个字节，其他的字符占一个字节。
3.所有的表和字段都需要添加注释 comment
4.尽量控制单表数据量的大小，建议控制在500万以内
[说明]MySQL在32位操作系统下，单个表的大小是不能超过2G的；
当数据库数据过大的时候，可以考虑历史数据归档，分库分表的手段来控制数据量的大小。
5.尽量做到冷热数据分离，减小表的宽度
减小磁盘IO，保证热数据的内存缓存命中率；
更有效的利用缓存，避免过多的读入无用的冷数据，避免使用select *
把经常使用的数据放到一个表里面。
6.禁止在表中建立预留字段
7.禁止在数据库中存储图片、文件等二进制的数据
8.禁止在生产环境做数据库压力测试
三.数据库索引设计规范
（建立索引的目的，通过索引查找数据，减少磁盘的随机IO，提高查询的性能）
1.限制每张表的索引个数，建议单张表的索引不超过5个
索引并不是越多越好，索引可以提高查询效率，同样降低可以降低查询效率，这是因为MySQL在进行查询的时候，会根据现有的索引生产一个最有的查询方案，索引过多导致生成查询方案的时间变长，降低查询的效率；
索引可以提高查询的效率，但降低了插入、修改数据的效率。
2. InnoDB表必须有一个主键
InnoDB是一个索引组织表，即数据存储的逻辑顺序和索引顺序是一致的。
每个表可以有多个索引，但是每个表的存储顺序只有一种，当有多个索引的时候，InnoDB表会按照主键的顺序来进行存储，所以要求每个InnoDB在创建的时候，必须有一个主键，若果没有主键，MySQL就会选择第一个非空唯一索引作为存储顺序，如果没有非空索引的话，MySQL就会自动生成一个6个字节的主键，而自动生成的主键并不是性能最好的。
【注意】
(1)InnoDB的主键不能够频繁的变动，这是因为InnoDB是索引组织表，主键的变动会导致表中数据存储的顺序发生改变，影响表的性能。
(2)主键不建议使用类似UUID,MD5,HASH,字符串作，因为这些值一般无法保证数据的顺序增长。
以MD5值为列子，我们无法保证后面插入的数据的MD5主键值要比之前数据的MD5值要大，那么存储的时候所以会将其插入到比他大的索引后面，导致大量的数据需要向后移动，造成大量IO操作和占用CPU资源的情况，建议使用MySQL的自增ID作为主键。
(3)常见的索引建议
SELECT,UPDATE,DELETE语句的WHERE从句中的列；
包含ORDER BY，GROUP BY，DISTINCT中的字段；
多表JOIN的关联列
(4)对于频繁的查找优先考虑使用覆盖索引；
InnoDB中主键作为一级索引，当我们按照一个二级索引查找数据的时候，InnoDB首先按照二级索引查找到对应的子节点的位置，而二级索引的子节点中保存的数据是该行主键的信息，要得到真实的数据，还要通过主键进行二次查找，而覆盖索引中，二级索引的键值是可以获得所有数据的，这样避免了进行二次查询。
四.数据库字段设计规范
1.优先选择符合存储需求的最小数据类型
将字符串转化为数字类型进行存储，e.g.将IP地址转化为数字进行存储，MySQL提供了两个函数，在我们存储的时候，调用INET_ATOM('255.255.255.255') = 429****95，将字符串类型的IP地址转换为数字类型，同样在取数据的时候，使用INET_NTOA(429****95) = '255.255.255.255'函数，将数字类型的IP地址还原为字符串
2.对于非负数据采用无符号整型进行存储
无符号相对于有符号数据，可以多出一倍的存储空间，e.g.
SIGNED INT有符int类型的范围在-2147483648到2147483647
UNSIGNED INT无符int类型的存储范围在0-4294967295之间。
3.MySQL中的VARCHAR(N)的N代表的是字符数，而不是字节数，这和其他的一些数据库中VARCHAR代表的存储单位不太一样，使用UTF8存储汉字的时候，每个汉字占3个字节。
4.避免使用TEXT、BLOB数据类型
建议把BLOB和TEXT列单独分离到扩展表中，TEXT和BLOB只能使用前缀索引
5.尽可能把所有的列定义为NOT NULL
索引列为NULL时，需要额外占用 索引空间存储NULL状态；
进行列的比较和计算时，需要对NULL进行特殊处理，这样有可能导致索引失效
6.时间日期的存储使用TIMESTAMP或DATETIME类型，不要使用字符串存储
缺点：无法使用日期函数进行比较；用字符串存储日期占用更多的空间，使用字符串需要使用16字节，要是使用DateType，那么只需要8字节；
同样的效果TIMESTAMP存储只需要4个字节，而DATETIME需要占用8个字节，但是TIMESTAMP只能存储时间在1970-01-01 00:00:01到2038-01-19 03:14:07之间的时间，TIMESTAMP实质上是使用INT类型来存储数据的，只是在显示的时候，进行转化。
7.数据库中存储的浮点类型数据包括非精准浮点(float, double)和精准浮点数据类型(decimal)
约定同财务相关的金额数据，一律使用decimal数据类型；
decimal数据类型占用的空间是由定义的宽度决定的，每4个字节可以存储9位数字，小数点单独占一个字节；
decimal数据类型可以用来存储比bigint更大的整数数据，这比使用VARCHAR存储数据更高效
五.数据库SQL开发规范
1.建议使用预编译语句进行数据库操作
预编译的SQL语句使用相同的查询计划，节省了MySQL生成查询计划的时间，而且可以防止SQL注入
2.避免数据类型的隐式转化
当数据类型不一致的时候，MySQL会进行数据的隐式转换，这会导致建立在列上的索引失效。
3.充分使用表上已存在的索引
(1)避免使用双%进行查询，e.g. LIKE '%123%' 或 LIKE '%123'都会导致列上的索引失效，但是使用 LIKE '123%' 的查询方式是可以利用到索引的。
(2)一个SQL只能利用复合索引中的一列进行范围查找
如果A、B、C三列进行联合查询，A列进行范围查询，那么B、C上的索引就不会再被用到了，此时我们应该将A列放在联合索引的右侧
(3)使用left join和not exists来替换not in，后者有可能造成索引失效
4.禁止使用Select * 进行查询, 应该使用Select <字段名> 进行查询
5.禁止使用不含字段列名的Insert语句，不应该使用insert into t values(1, 2, 3)这样的语句进行数据插入，而应该使用insert into t(a, b, c) values(1, 2, 3)这样的语句进行插入操作，防止表结构变更造成插入错误。
6.避免使用子查询，尽量转化为Join查询方式
子查询无法使用索引；子查询会产生临时表表操作，而这些临时表是没有索引的，会消耗过多的CPU和IO资源。
7.避免使用JOIN关联过多的表
每JOIN一个表就会占用一部分内存，MySQL可以通过join_buffer_size设置这部分内存的大小；
Join操作也会产生临时表操作，影响查询效率；
MySQL最多允许关联61个表，建议不要超过5个
8.减少同数据库交互的次数
数据库更适合处理批量操作，e.g.分页查询的时候，可以一次拉去很多条数据，分批进行返回，而不要分多次每次从数据库中拉去一小部分数据
9.使用IN代替OR， IN的值不要超过500个
IN操作可以有效的使用到索引，而OR一般是用不到索引的。
10.禁止使用order by rand()进行随机排序
rand()操纵会将所有的符合条件的数据加载到内存中进行排序，这样会消耗大量的CPU和IO资源，我们可以在SQL查询之前生成好随机查找的数据，然后执行SQL进行数据拉取
11.WHERE从句中禁止对列进行函数转换和计算
对列进行函数转换和计算，会导致无法使用索引，e.g. 当我们要查询日期的时候，可能使用下面的语句进行查找：
where date(createTime) = '20180423' 这样，我们便无法使用createTime上的索引，建议使用下面的语句：
where createTime >= '20180423' and createTime < '20180423' 这样就可以使用createTime的索引了
12. UNION和UNION ALL的操作，可以合并两个查询的结果集
当两个结果集之间明显没有重复值的时候，尽量使用UNION ALL，这是因为UNION会将所有的数据放到一个临时表中，然后进行去重操作
13.拆分复杂的大SQL为多个小SQL进行查询，再合并
MySQL的一个SQL只能使用一个CPU进行计算，大SQL拆分之后可以通过并行的方式查询，再合并多个结果集，从而提高查询的效率。
六.数据库操作行为规范
1.超过100w行数据的写操作，要分批进行
大量的写操作会造成严重的主从延时，造成数据库操作的阻塞；分批进行可以有效的避免大事务操作
对于大表结构的修改一定要使用pt-online-schema-change这样的工具进行，使用这个工具进行大表结构修改的时候，会首先复制创建一个结构相同的新表，在这个新表的机构上进行修改，再将原表中的数据复制到新表中，同时在原表上添加一些触发器，保证原表中新添加的数据能够及时添加到新表中,在原表中数据全部复制完成之后，会在原表上添加一个很短的时间锁，将新表的名称命名为原表的名称，并将原表删除掉,这样做可以避免大表修改产生的主从延迟的问题，避免在对表字段修改时产生的较长时间的锁表问题。
2.禁止为程序使用账号授予super权限
MySQL在达到最大链接数之后，还允许1个具有super权限的用户进行连接，当为前端用户授予super权限之后，DBA处理账号就无法再以super权限进行登录排查问题了，在为程序连接账号授予数据库权限时，应该遵循最小权限的原则，并不能有类似drop table的权限。
一.数据库命名规范
二.数据库基本设计规范
三.数据库索引设计规范
四.数据库字段设计规范
五.数据库SQl开发规范
六.数据库操作行为规范
一.数据库命名规范
1.所有的数据库名称和表名称必须使用小写字母并使用下划线分割这是因为MySQL数据文件就是Linux下的一个问题，Linux是大小写敏感的，所以MySQL数据库和表的名称也是大小写敏感的：
Dbname和dbName代表两个不同的数据库
Table和table代表两个不同的表
为了不引起奇异，规定数据库名称和表名称必须使用小写字母+下划线的方式
2.所有的数据库名称禁止使用MySQL保留关键字
select id.username,from,age from tb_user;
上面的SQL语句有两个from，第一个是字段，第二个是SQL关键字，MySQL在执行查询时，并不能区分是关键字还是数据字段，那么需要：
select id.username,`from`,age from tb_user;
需要在from数据字段加反向引号，为了避免出现奇异，禁止数据字段使用SQL关键字。
MySQL关键字查询：
http://dev.mysql.com/doc/refman/5.7/en/keywords.html
3.数据库名称命名要见名识意，不要超过32字符
4.临时库表的前缀必须以tmp开头，并以日期为后缀
备份库表的前缀必须以bak开头，并以日期为后缀
5.所有存储相同数据的列名和列类型必须一致
e.g.我们在用户信息表和订单表中都会存储用户ID，customer_id,那么要求两个表中用户ID的名称和类型必须一致。
这样的列一般作为关联列使用，要是数据类型不一致，在进行联合查询的时候，MySQL需要先进行类型转换，有可能导致索引失效，降低查询效率。
二.数据库基本设计规范
1.表引擎使用InnoDB
在MySQL5.5以前Myisam是默认的存储引擎,MySQL5.5以后InnoDB为默认的存储引擎。
InnoDB在MySQL5.6及以后进行了很好的优化，InnoDB是一种支持事务、行级锁、有更好的恢复性，高并发下性能更好的存储引擎。
2.数据库和表的字符集统一使用UTF8编码格式
[说明]UTF8中一个中文字符占3个字节，其他的字符占一个字节。
3.所有的表和字段都需要添加注释 comment
4.尽量控制单表数据量的大小，建议控制在500万以内
[说明]MySQL在32位操作系统下，单个表的大小是不能超过2G的；
当数据库数据过大的时候，可以考虑历史数据归档，分库分表的手段来控制数据量的大小。
5.尽量做到冷热数据分离，减小表的宽度
减小磁盘IO，保证热数据的内存缓存命中率；
更有效的利用缓存，避免过多的读入无用的冷数据，避免使用select *
把经常使用的数据放到一个表里面。
6.禁止在表中建立预留字段
7.禁止在数据库中存储图片、文件等二进制的数据
8.禁止在生产环境做数据库压力测试
三.数据库索引设计规范
（建立索引的目的，通过索引查找数据，减少磁盘的随机IO，提高查询的性能）
1.限制每张表的索引个数，建议单张表的索引不超过5个
索引并不是越多越好，索引可以提高查询效率，同样降低可以降低查询效率，这是因为MySQL在进行查询的时候，会根据现有的索引生产一个最有的查询方案，索引过多导致生成查询方案的时间变长，降低查询的效率；
索引可以提高查询的效率，但降低了插入、修改数据的效率。
2. InnoDB表必须有一个主键
InnoDB是一个索引组织表，即数据存储的逻辑顺序和索引顺序是一致的。
每个表可以有多个索引，但是每个表的存储顺序只有一种，当有多个索引的时候，InnoDB表会按照主键的顺序来进行存储，所以要求每个InnoDB在创建的时候，必须有一个主键，若果没有主键，MySQL就会选择第一个非空唯一索引作为存储顺序，如果没有非空索引的话，MySQL就会自动生成一个6个字节的主键，而自动生成的主键并不是性能最好的。
【注意】
(1)InnoDB的主键不能够频繁的变动，这是因为InnoDB是索引组织表，主键的变动会导致表中数据存储的顺序发生改变，影响表的性能。
(2)主键不建议使用类似UUID,MD5,HASH,字符串作，因为这些值一般无法保证数据的顺序增长。
以MD5值为列子，我们无法保证后面插入的数据的MD5主键值要比之前数据的MD5值要大，那么存储的时候所以会将其插入到比他大的索引后面，导致大量的数据需要向后移动，造成大量IO操作和占用CPU资源的情况，建议使用MySQL的自增ID作为主键。
(3)常见的索引建议
SELECT,UPDATE,DELETE语句的WHERE从句中的列；
包含ORDER BY，GROUP BY，DISTINCT中的字段；
多表JOIN的关联列
(4)对于频繁的查找优先考虑使用覆盖索引；
InnoDB中主键作为一级索引，当我们按照一个二级索引查找数据的时候，InnoDB首先按照二级索引查找到对应的子节点的位置，而二级索引的子节点中保存的数据是该行主键的信息，要得到真实的数据，还要通过主键进行二次查找，而覆盖索引中，二级索引的键值是可以获得所有数据的，这样避免了进行二次查询。
四.数据库字段设计规范
1.优先选择符合存储需求的最小数据类型
将字符串转化为数字类型进行存储，e.g.将IP地址转化为数字进行存储，MySQL提供了两个函数，在我们存储的时候，调用INET_ATOM('255.255.255.255') = 429****95，将字符串类型的IP地址转换为数字类型，同样在取数据的时候，使用INET_NTOA(429****95) = '255.255.255.255'函数，将数字类型的IP地址还原为字符串
2.对于非负数据采用无符号整型进行存储
无符号相对于有符号数据，可以多出一倍的存储空间，e.g.
SIGNED INT有符int类型的范围在-2147483648到2147483647
UNSIGNED INT无符int类型的存储范围在0-4294967295之间。
3.MySQL中的VARCHAR(N)的N代表的是字符数，而不是字节数，这和其他的一些数据库中VARCHAR代表的存储单位不太一样，使用UTF8存储汉字的时候，每个汉字占3个字节。
4.避免使用TEXT、BLOB数据类型
建议把BLOB和TEXT列单独分离到扩展表中，TEXT和BLOB只能使用前缀索引
5.尽可能把所有的列定义为NOT NULL
索引列为NULL时，需要额外占用 索引空间存储NULL状态；
进行列的比较和计算时，需要对NULL进行特殊处理，这样有可能导致索引失效
6.时间日期的存储使用TIMESTAMP或DATETIME类型，不要使用字符串存储
缺点：无法使用日期函数进行比较；用字符串存储日期占用更多的空间，使用字符串需要使用16字节，要是使用DateType，那么只需要8字节；
同样的效果TIMESTAMP存储只需要4个字节，而DATETIME需要占用8个字节，但是TIMESTAMP只能存储时间在1970-01-01 00:00:01到2038-01-19 03:14:07之间的时间，TIMESTAMP实质上是使用INT类型来存储数据的，只是在显示的时候，进行转化。
7.数据库中存储的浮点类型数据包括非精准浮点(float, double)和精准浮点数据类型(decimal)
约定同财务相关的金额数据，一律使用decimal数据类型；
decimal数据类型占用的空间是由定义的宽度决定的，每4个字节可以存储9位数字，小数点单独占一个字节；
decimal数据类型可以用来存储比bigint更大的整数数据，这比使用VARCHAR存储数据更高效
五.数据库SQL开发规范
1.建议使用预编译语句进行数据库操作
预编译的SQL语句使用相同的查询计划，节省了MySQL生成查询计划的时间，而且可以防止SQL注入
2.避免数据类型的隐式转化
当数据类型不一致的时候，MySQL会进行数据的隐式转换，这会导致建立在列上的索引失效。
3.充分使用表上已存在的索引
(1)避免使用双%进行查询，e.g. LIKE '%123%' 或 LIKE '%123'都会导致列上的索引失效，但是使用 LIKE '123%' 的查询方式是可以利用到索引的。
(2)一个SQL只能利用复合索引中的一列进行范围查找
如果A、B、C三列进行联合查询，A列进行范围查询，那么B、C上的索引就不会再被用到了，此时我们应该将A列放在联合索引的右侧
(3)使用left join和not exists来替换not in，后者有可能造成索引失效
4.禁止使用Select * 进行查询, 应该使用Select <字段名> 进行查询
5.禁止使用不含字段列名的Insert语句，不应该使用insert into t values(1, 2, 3)这样的语句进行数据插入，而应该使用insert into t(a, b, c) values(1, 2, 3)这样的语句进行插入操作，防止表结构变更造成插入错误。
6.避免使用子查询，尽量转化为Join查询方式
子查询无法使用索引；子查询会产生临时表表操作，而这些临时表是没有索引的，会消耗过多的CPU和IO资源。
7.避免使用JOIN关联过多的表
每JOIN一个表就会占用一部分内存，MySQL可以通过join_buffer_size设置这部分内存的大小；
Join操作也会产生临时表操作，影响查询效率；
MySQL最多允许关联61个表，建议不要超过5个
8.减少同数据库交互的次数
数据库更适合处理批量操作，e.g.分页查询的时候，可以一次拉去很多条数据，分批进行返回，而不要分多次每次从数据库中拉去一小部分数据
9.使用IN代替OR， IN的值不要超过500个
IN操作可以有效的使用到索引，而OR一般是用不到索引的。
10.禁止使用order by rand()进行随机排序
rand()操纵会将所有的符合条件的数据加载到内存中进行排序，这样会消耗大量的CPU和IO资源，我们可以在SQL查询之前生成好随机查找的数据，然后执行SQL进行数据拉取
11.WHERE从句中禁止对列进行函数转换和计算
对列进行函数转换和计算，会导致无法使用索引，e.g. 当我们要查询日期的时候，可能使用下面的语句进行查找：
where date(createTime) = '20180423' 这样，我们便无法使用createTime上的索引，建议使用下面的语句：
where createTime >= '20180423' and createTime < '20180423' 这样就可以使用createTime的索引了
12. UNION和UNION ALL的操作，可以合并两个查询的结果集
当两个结果集之间明显没有重复值的时候，尽量使用UNION ALL，这是因为UNION会将所有的数据放到一个临时表中，然后进行去重操作
13.拆分复杂的大SQL为多个小SQL进行查询，再合并
MySQL的一个SQL只能使用一个CPU进行计算，大SQL拆分之后可以通过并行的方式查询，再合并多个结果集，从而提高查询的效率。
六.数据库操作行为规范
1.超过100w行数据的写操作，要分批进行
大量的写操作会造成严重的主从延时，造成数据库操作的阻塞；分批进行可以有效的避免大事务操作
对于大表结构的修改一定要使用pt-online-schema-change这样的工具进行，使用这个工具进行大表结构修改的时候，会首先复制创建一个结构相同的新表，在这个新表的机构上进行修改，再将原表中的数据复制到新表中，同时在原表上添加一些触发器，保证原表中新添加的数据能够及时添加到新表中,在原表中数据全部复制完成之后，会在原表上添加一个很短的时间锁，将新表的名称命名为原表的名称，并将原表删除掉,这样做可以避免大表修改产生的主从延迟的问题，避免在对表字段修改时产生的较长时间的锁表问题。
2.禁止为程序使用账号授予super权限
MySQL在达到最大链接数之后，还允许1个具有super权限的用户进行连接，当为前端用户授予super权限之后，DBA处理账号就无法再以super权限进行登录排查问题了，在为程序连接账号授予数据库权限时，应该遵循最小权限的原则，并不能有类似drop table的权限。
扫一扫
2831
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
