mysql数据库设计规范与原则

一、命名规范与设计规范

1.1 数据库命名规范

采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_'组成;命名简洁明确(长度不能超过30个字符);例如：user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;除非是备份数据库可以加0-9的自然数：user_db_20151210;

1.2 数据库表名命名规范

采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’‘组成;命名简洁明确,多个单词用下划线’'分隔;例如:user_login,user_profile,user_detail,user_role, user_role_relation,user_role_right, user_role_right_relation表前缀’user_'可以有效的把相同关系的表显示在一起;

1.3 数据库表字段名命名规范

采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’‘组成;命名简洁明确,多个单词用下划线’‘分隔;例如：user_login表字段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time默认系统时间;
表与表之间的相关联字段名称要求尽可能的相同; 如 web_user 表里面的 userId 和 web_group 表里面的 userId 相对应.
当字段类型为枚举或者布尔型时，使用 CHAR(1)（或者CHAR(2)）类型，填写默认值，状态字段的默认值不能为null一般是设置成0或者-1，状态字段的描述写成 comment ‘团购券状态：1.已购买；2.已使用；3.退款中；4已退款’。
在设计时候尽量包含日期字段：CREATE_DATE(创建日期)，UPDATE_DATE(更新日期)等。
mysql中对日期约定一种录入方式，如’2014-12-31 00:00:00.0’.
默认值，数字类型默认值为0，字符串默认值为’’，日期默认值为’1900-01-01 00:00:00.0’

1.4 数据库表字段类型规范

**用尽量少的存储空间来存数一个字段的数据;**例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256); IP地址最好使用int类型;固定长度的类型最好使用char,例如：邮编;能使用tinyint就不要使用smallint,int;最好给每个字段一个默认值,最好不能为null;

1.5 数据库表索引规范

命名简洁明确,例如：user_login表user_name字段的索引应为user_name_index唯一索引;为每个表创建一个主键索引;为每个表创建合理的索引;建立复合索引请慎重;
值范围重复的比例少的，建立索引字段，比如 CREATE_DATE(录入时间)字段；值范围重复比率的字段不需要建立索引比如IS_RETURN(是否已退款)字段
WHERE条件后面的经常查询的字段，需要加索引，比如ORDER_GOODS 表的ORDER_SN(商品号)等

1.6 简单熟悉数据库范式

第一范式(1NF)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;
第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分;备注：必须先满足第一范式;
第三范式(3NF)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段;备注：必须先满足第二范式;
备注：往往我们在设计表中不能遵守第三范式,因为合理的沉余字段将会给我们减少join的查询;例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;

二、MYSQL数据库设计原则

2.1 核心原则

• 不在数据库做运算;
• cpu计算务必移至业务层;
• 控制列数量(字段少而精,字段数建议在20以内);
• 平衡范式与冗余(效率优先；往往牺牲范式)
• 拒绝3B(拒绝大sql语句：big sql、拒绝大事物：big transaction、拒绝大批量：big batch);

2.2 字段类原则

• 用好数值类型(用合适的字段类型节约空间);
• 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);
• 避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
• 少用text类型(尽量使用varchar代替text字段);

2.3 索引类原则

• 合理使用索引(改善查询,减慢更新,索引一定不是越多越好);
• 字符字段必须建前缀索引（前缀索引需要合适的长度，参考https://www.cnblogs.com/studyzy/p/4310653.html）;
• 不在索引做列运算;
• innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了)
• 不用外键(由程序保证约束);

2.4 sql类原则

1. sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);
2. 写到应用程序的SQL语句，禁止一切DDL操作，例如：create,drop,alter,grant,remove；如有特殊需要，请与dba协商同意方可使用
3. 当表连接时候，用于连接的两个表的字段如果数据类型不一致，则必须在一边加上类型转换的函数。杜绝mysql做隐式类型转换的情况
4. 避免在where字句中对字段施加函数，如果是业务要求的除外，但需要在编写时候咨询DBA比如DATE_FORMAT(p.PAYMENT_DATE, ‘%Y-%m-%d’) >= DATE_FORMAT(‘2014-10-01’, ‘%Y-%m-%d’)，就需要改正掉
5. 简单的事务
6. 避免使用trig/func(触发器、函数不用客户端程序取而代之)
7. 不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性)
8. OR改写为IN(or的效率是n级别)
9. 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’

10. 避免负向%(少用like ‘%王%’)
11. 慎用count(*);
12. limit高效分页(limit越大，效率越低)
13. 使用union all替代union(union有去重开销)
14. 少用连接join（join的层级不能超过三层，超过则在业务逻辑中分成多个sql语句）
15. 使用group by
16. 打散批量更新

2.6 sql建议

1. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
2. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
3. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：
   select id from t where num is null
   可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
   select id from t where num=0
4. 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
   select id from t where num=10 or num=20
   可以这样查询：
   select id from t where num=10
   union all
   select id from t where num=20
5. 下面的查询也将导致全表扫描：(不能前置百分号)
   select id from t where name like ‘%c%’
   下面走索引
   select id from t where name like ‘c%’
   若要提高效率，可以考虑全文检索。
6. in 和 not in 也要慎用，否则会导致全表扫描，如：
   select id from t where num in(1,2,3)
   对于连续的数值，能用 between 就不要用 in 了：
   select id from t where num between 1 and 3
7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
   select id from t where num=@num
   可以改为强制查询使用索引：
   select id from t with(index(索引名)) where num=@num
8. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
   select id from t where num/2=100
   应改为:
   select id from t where num=100*2
9. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
   select id from t where substring(name,1,3)=’abc’ –name以abc开头的id
   select id from t where datediff(day,createdate,’2005-11-30′)=0 –’2005-11-30′生成的id
   应改为:
   select id from t where name like ‘abc%’
   select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
10. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。
12. 不要写一些没有意义的查询，如需要生成一个空表结构：
    select col1,col2 into #t from t where 1=0
    这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
    create table #t(…)
13. 、很多时候用 exists 代替 in 是一个好的选择：
    select num from a where num in(select num from b)
    用下面的语句替换：
    select num from a where exists(select 1 from b where num=a.num)
14. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
16. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

三、性能分析工具

性能分析工具
show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;