MYSQL数据库设计规范与原则

  1. MYSQL数据库设计规范  
  2.    
  3.     1、数据库命名规范  
  4.         采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;  
  5.         命名简洁明确(长度不能超过30个字符);  
  6.         例如：user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;  
  7.         除非是备份数据库可以加0-9的自然数：user_db_20151210;  
  8.            
  9.     2、数据库表名命名规范  
  10.         采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;  
  11.         命名简洁明确,多个单词用下划线'_'分隔;  
  12.         例如：user_login, user_profile, user_detail, user_role, user_role_relation,  
  13.             user_role_right, user_role_right_relation  
  14.         表前缀'user_'可以有效的把相同关系的表显示在一起;  
  15.            
  16.     3、数据库表字段名命名规范  
  17.         采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;  
  18.         命名简洁明确,多个单词用下划线'_'分隔;  
  19.         例如：user_login表字段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time;  
  20.         每个表中必须有自增主键,add_time(默认系统时间)  
  21.         表与表之间的相关联字段名称要求尽可能的相同;  
  22.        
  23.     4、数据库表字段类型规范  
  24.         用尽量少的存储空间来存数一个字段的数据;  
  25.         例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);  
  26.         IP地址最好使用int类型;  
  27.         固定长度的类型最好使用char,例如：邮编;  
  28.         能使用tinyint就不要使用smallint,int;  
  29.         最好给每个字段一个默认值,最好不能为null;  
  30.        
  31.     5、数据库表索引规范  
  32.         命名简洁明确,例如：user_login表user_name字段的索引应为user_name_index唯一索引;  
  33.         为每个表创建一个主键索引;  
  34.         为每个表创建合理的索引;  
  35.         建立复合索引请慎重;  
  36.            
  37.     6、简单熟悉数据库范式  
  38.         第一范式(1NF)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);  
  39.             例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;  
  40.            
  41.         第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分;  
  42.             备注：必须先满足第一范式;  
  43.            
  44.         第三范式(3NF)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段;  
  45.             备注：必须先满足第二范式;  
  46.            
  47.         备注：往往我们在设计表中不能遵守第三范式,因为合理的沉余字段将会给我们减少join的查询;  
  48.               例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;  
  49.                  
  50.    
  51.        
  52. MYSQL数据库设计原则  
  53.    
  54.     1、核心原则  
  55.         不在数据库做运算;  
  56.         cpu计算务必移至业务层;  
  57.         控制列数量(字段少而精,字段数建议在20以内);  
  58.         平衡范式与冗余(效率优先；往往牺牲范式)  
  59.         拒绝3B(拒绝大sql语句：big sql、拒绝大事物：big transaction、拒绝大批量：big batch);  
  60.    
  61.     2、字段类原则  
  62.         用好数值类型(用合适的字段类型节约空间);  
  63.         字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);  
  64.         避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);  
  65.         少用text类型(尽量使用varchar代替text字段);  
  66.        
  67.     3、索引类原则  
  68.         合理使用索引(改善查询,减慢更新,索引一定不是越多越好);  
  69.         字符字段必须建前缀索引;  
  70.         不在索引做列运算;  
  71.         innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);  
  72.         不用外键(由程序保证约束);  
  73.        
  74.     4、sql类原则  
  75.         sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);  
  76.         简单的事务;  
  77.         避免使用trig/func(触发器、函数不用客户端程序取而代之);  
  78.         不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性);  
  79.         OR改写为IN(or的效率是n级别);  
  80.         OR改写为UNION(mysql的索引合并很弱智);  
  81.             select id from t where phone = ’159′ or name = ‘john’;  
  82.             =>  
  83.             select id from t where phone=’159′  
  84.             union  
  85.             select id from t where name=’jonh’  
  86.         避免负向%;  
  87.         慎用count(*);  
  88.         limit高效分页(limit越大，效率越低);  
  89.         使用union all替代union(union有去重开销);  
  90.         少用连接join;  
  91.         使用group by;  
  92.         请使用同类型比较;  
  93.         打散批量更新;  
  94.            
  95.     5、性能分析工具  
  96.         show profile;  
  97.         mysqlsla;  
  98.         mysqldumpslow;  
  99.         explain;  
  100.         show slow log;  
  101.         show processlist; 
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zhangtianle/p/7997998.html