MYSQL数据库设计规范与原则

最新推荐文章于 2021-08-17 10:11:07 发布
liangfrank
最新推荐文章于 2021-08-17 10:11:07 发布
阅读量686
点赞数
转自loveking_阳http://www.cnblogs.com/lovekingly/p/5044278.html

MYSQL数据库设计规范与原则

 
          MYSQL数据库设计规范 
         
          1、数据库命名规范 
         
          采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线 
          '_' 
          组成; 
         
          命名简洁明确(长度不能超过30个字符); 
         
          例如： 
          user 
          , stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀; 
         
          除非是备份数据库可以加0-9的自然数：user_db_20151210; 
         
          2、数据库表名命名规范 
         
          采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线 
          '_' 
          组成; 
         
          命名简洁明确,多个单词用下划线 
          '_' 
          分隔; 
         
          例如：user_login, user_profile, user_detail, user_role, user_role_relation, 
         
          user_role_right, user_role_right_relation 
         
          表前缀 
          'user_' 
          可以有效的把相同关系的表显示在一起; 
         
          3、数据库表字段名命名规范 
         
          采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线 
          '_' 
          组成; 
         
          命名简洁明确,多个单词用下划线 
          '_' 
          分隔; 
         
          例如：user_login表字段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time; 
         
          每个表中必须有自增主键,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)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段; 
         
          备注：必须先满足第二范式; 
         
          备注：往往我们在设计表中不能遵守第三范式,因为合理的沉余字段将会给我们减少 
          join 
          的查询; 
         
          例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段; 
         
          MYSQL数据库设计原则 
         
          1、核心原则 
         
          不在数据库做运算; 
         
          cpu计算务必移至业务层; 
         
          控制列数量(字段少而精,字段数建议在20以内); 
         
          平衡范式与冗余(效率优先；往往牺牲范式) 
         
          拒绝3B(拒绝大sql语句：big sql、拒绝大事物：big  
          transaction 
          、拒绝大批量：big batch); 
         
          2、字段类原则 
         
          用好数值类型(用合适的字段类型节约空间); 
         
          字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能); 
         
          避免使用 
          NULL 
          字段( 
          NULL 
          字段很难查询优化、 
          NULL 
          字段的索引需要额外空间、 
          NULL 
          字段的复合索引无效); 
         
          少用text类型(尽量使用 
          varchar 
          代替text字段); 
         
          3、索引类原则 
         
          合理使用索引(改善查询,减慢更新,索引一定不是越多越好); 
         
          字符字段必须建前缀索引; 
         
          不在索引做列运算; 
         
          innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了); 
         
          不用外键(由程序保证约束); 
         
          4、sql类原则 
         
          sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库); 
         
          简单的事务; 
         
          避免使用trig/func(触发器、函数不用客户端程序取而代之); 
         
          不用 
          select  
          *(消耗cpu,io,内存,带宽,这种程序不具有扩展性); 
         
          OR 
          改写为 
          IN 
          ( 
          or 
          的效率是n级别); 
         
          OR 
          改写为 
          UNION 
          (mysql的索引合并很弱智); 
         
          select  
          id  
          from  
          t  
          where  
          phone = ’159′  
          or  
          name  
          = ‘john’; 
         
          => 
         
          select  
          id  
          from  
          t  
          where  
          phone=’159′ 
         
          union 
         
          select  
          id  
          from  
          t  
          where  
          name 
          =’jonh’ 
         
          避免负向%; 
         
          慎用 
          count 
          (*); 
         
          limit高效分页(limit越大，效率越低); 
         
          使用 
          union  
          all 
          替代 
          union 
          ( 
          union 
          有去重开销); 
         
          少用连接 
          join 
          ; 
         
          使用 
          group  
          by 
          ; 
         
          请使用同类型比较; 
         
          打散批量更新; 
         
          5、性能分析工具 
         
          show profile; 
         
          mysqlsla; 
         
          mysqldumpslow; 
         
          explain; 
         
          show slow log; 
         
          show processlist;