数据库设计规范与原则/分区分表

一，数据库设计规范
1.数据库命名规范 
   采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成; 
   例如：user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀; 
2.数据表命名规范   
   采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;  
    命名简洁明确,多个单词用下划线'_'分隔;   
3.数据表字段命名规范  
采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成; 
命名简洁明确,多个单词用下划线'_'分隔; 
例如：user_login表段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time;  4.字段类型规范  
用尽量少的存储空间来存数一个字段的数据;  例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256); 
IP地址最好使用int类型; 
固定长度的类型最好使用char,例如：邮编; 
能使用tinyint就不要使用smallint,int; 
最好给每个字段一个默认值,最好不能为null;
5.数据库表索引规范
 命名简洁明确,例如：user_login表user_name字段的索引应为user_name_index唯一索引; 
6.简单熟悉数据库范式（三大范式）
第一范式(1NF)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);  例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段; 
第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分;
第三范式(3NF)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段; 
二．MYSQL数据库设计原则 
１．核心原则
       不在数据库做运算或写业务逻辑（存储过程，触发器）;  控制列数量(字段少而精,字段数建议在20以内);  平衡范式与冗余(效率优先；往往牺牲范式) ; 拒绝3B(拒绝大sql语句：big sql、拒绝大事物：bigtransaction、拒绝大批量：big batch);   
2．字段类原则
用好数值类型(用合适的字段类型节约空间);  
字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能); 
避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效); 
少用text类型(尽量使用varchar代替text字段);  
3．索引类原则  
合理使用索引(改善查询,减慢更新,索引一定不是越多越好); 
字符字段必须建前缀索引;  不在索引做列运算;  
innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);  减少使用外键(由程序保证约束);
4．sql类原则 
sql语句尽可能简单；简单的事务;   避免使用trig/func(触发器、函数不用客户端程序取而代之);
 不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性);  
条件查询or改为IN;OR改写为UNION(mysql的索引合并很弱智);  
 避免负向%;  如id !=１；少用连接join;  
===========================================================
三．mysql分区分表
(1)简介
mysql数据库中的数据是以文件的形式存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看）。
使用myisam引擎的一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。使用innoDB引擎的/mysql/data/数据库名目录下一张表有一个frm文件存放数据结构，其他的数据部分全部都存在在/mysql/data目录下的ibdata文件中。

(2)分表分区是什么?
    分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。
    分区就是把一张表的数据分成N多个区域，分区后，表面上还是一张表，但数据散列到多个位置根据数据量的大小，结合实际业务。
(3)分区
mysql5.1及以上支持分区功能 
分区方式有：range分区，list分区,hash分区,key分区,子分区。
①range分区     
 建表的时候添分区按照id的范围

CREATE TABLE IF NOT EXISTS `user` (   
id int ........
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1   -PARTITION BY RANGE (id) (     
   PARTITION p0 VALUES LESS THAN (3),                                                                           PARTITION p1 VALUES LESS THAN (6),                                                                           PARTITION p2 VALUES LESS THAN MAXVALUE,                            );

对现有的表进行分区

alter table aa partition by RANGE(id)(
PARTITION p1 VALUES less than (1),   
PARTITION p2 VALUES less than (5),   - PARTITION p3 VALUES less than MAXVALUE);  
//删除一个分区  
 alter table aa drop partition p2;  

②list分区
LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分 区是从属于一个连续区间值的集合。如下：
官方文档规定  LIST 分区是，分区字段必须包含在主键字段内

CREATE TABLE IF NOT EXISTS `list_part` (  `id` int(11) NOT NULL COMMENT '用户ID',  
`province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',  
) ENGINE=INNODB  DEFAULT CHARSET=utf8  AUTO_INCREMENT=1   -PARTITION BY LIST (province_id) ( 
PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),    PARTITION p1 VALUES IN (9,10,11,12,16,21),  PARTITION p2 VALUES IN (13,14,15,19),    PARTITION p3 VALUES IN (17,18,20,22,23,24)   );  

③hash分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以 及指定被分区的表将要被分割成的分区数量。

CREATE TABLE IF NOT EXISTS `hash_part` (     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',     `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',      `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',      PRIMARY KEY (`id`)    ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1    PARTITION BY HASH(id)   -> PARTITIONS 3; 

④key分区
按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用 户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。

CREATE TABLE IF NOT EXISTS `key_part` (      `news_id` int(11) NOT NULL  COMMENT '新闻ID',     `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',      `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',      `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'   ) ENGINE=INNODB  DEFAULT CHARSET=utf8    PARTITION BY LINEAR HASH(YEAR(create_time))   
PARTITIONS 3;  

⑤子分区
子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区。这 也被称为复合分区（composite partitioning）。
  1，如果一个分区中创建了子分区，其他分区也要有子分区
  2，如果创建了了分区，每个分区中的子分区数必有相同
  3，同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）

CREATE TABLE IF NOT EXISTS `sub_part` (     `news_id` int(11) NOT NULL  COMMENT '新闻ID',     `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',      `u_id`  int(11) NOT NULL DEFAULT 0s COMMENT '来源IP',      `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'    ) ENGINE=INNODB  DEFAULT CHARSET=utf8    PARTITION BY RANGE(YEAR(create_time))  SUBPARTITION BY HASH(TO_DAYS(create_time))(    PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),    PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),    PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)); 

⑥分区管理⑦⑧⑨⑩
   1，删除分区
    alter table user drop partition p4;  
   2，新增分区
    range添加新分区 
    alter table user add partition(partition p4 values less than MAXVALUE);
    list添加新分区
    alter table list_part add partition(partition p4 values in (25,26,28));  
   hash重新分区 
    alter table hash_part add partition partitions 4;  
   key重新分区
    alter table key_part add partition partitions 4;