MySQL设计原则

MYSQL数据库设计规范与原则

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 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;