Mysql数据库、表、字段、索引等的命名很大程度上会影响sql语句查询的快慢。接下来就说明一下数据库的命名规范和设计原则等。
一、数据库设计规范
1.数据库命名规范
a、采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
b、命名简洁明确(长度不能超过30个字符);
c、例如:user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;
d、除非是备份数据库可以加0-9的自然数:user_db_20151210;
2.数据库表名命名规范
a、采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
b、命名简洁明确,多个单词用下划线'_'分隔;
例如:user_login, user_profile, user_detail, user_role, user_role_relation,
user_role_right, user_role_right_relation
注:表前缀'user_'可以有效的把相同关系的表显示在一起;
3.数据库表字段名命名规范
a、采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
b、命名简洁明确,多个单词用下划线'_'分隔;
例如:user_login表字段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time;
c、每个表中必须有自增主键,add_time(默认系统时间)
d、表与表之间的相关联字段名称要求尽可能的相同;
4.数据库表字段类型规范
用尽量少的存储空间来存数一个字段的数据;
例如:能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);
IP地址最好使用int类型;
固定长度的类型最好使用char,例如:邮编;
能使用tinyint就不要使用smallint,int;
最好给每个字段一个默认值,最好不能为null;
5.数据库表索引规范
命名简洁明确,例如:user_login表user_name字段的索引应为user_name_index唯一索引;
为每个表创建一个主键索引;
为每个表创建合理的索引;
建立复合索引请慎重;
6.简单熟悉数据库范式
1、第一范式(1NF):字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);
例如:姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;
2、第二范式(2NF):一个表必须有主键,即每行数据都能被唯一的区分;
备注:必须先满足第一范式;
3、第三范式(3NF):一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段;
备注:必须先满足第二范式;
注意:往往我们在设计表中不能遵守第三范式,因为合理的沉余字段将会给我们减少join的查询;
例如:相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;
二、数据库设计原则
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;
请使用同类型比较;
打散批量更新;
三、数据库结构的优化
1、选择合适的数据类型
1、数据类型选择
数据类型的选择,重点在于“合适”二字,如何确定选择的数据类型是否合适了?
- 使用可以存下你的数据的最小的数据类型。(时间类型数据:可以使用varchar类型,可以使用int类型,也可以使用时间戳类型)
- 使用简单的数据类型,int要比varchar类型在mysql处理上简单。(int类型存储时间是最好的选择)
- 尽可能的使用not null定义字段。(innodb的特性所决定,非not null的值,需要额外的在字段存储,同时也会增加IO和存储的开销)
4.尽量少用text类型,非用不可时最好考虑分表。
2.例子
(1)使用int来存储日期时间,利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换。
时间存储为int:insert into test (timestr) values (unix_timestamp('2018-05-29 16:00:00'));
int转为时间:select FROM_UNIXTIME(timestr) from test;
(2)在我们的外部应用中,都要记录ip地址,大部分场合都是varchar(15)进行存储,就需要15个字节进行存储,但是bigint只需要8个字节进行存储,当数据量很大的时候(千万级别的数据),相差7个字节,但是不能小看这7个字节。
使用bigint(8)来存储ip地址,利用INET_ATON(),INET_NTOA()两个函数来进行转换。
ip存储为bigint:insert into sessions (ipaddress)values (inet_aton('192.168.0.1'));
bigint转为ip:select inet_ntoa(ipaddress) from sessions;
2.数据库表的垂直拆分
所谓的垂直拆分,就是把原来一个有很多列的表拆分成多个表,这解决了表的宽度问题。
1.垂直拆分原则
通常垂直拆分可以按以下原则进行:
- 把不常用的字段表单独存放到一个表中。
- 把大字段独立存放到一个表中。
- 把经常一起使用的字段放到一起。
3.数据库表的水平拆分
表的水平拆分是为了解决单表数据量过大的问题,水平拆分的表每一个表的结构都是完全一致的,水平拆分原因:如果单表的数据量达到上亿条,那么这时候我们尽管加了完美的索引,查询效率低,写入的效率也相应的降低。
通常水平拆分的方法为:
1.对customer_id进行hash运算,如果要拆分为5个表则使用mod(customer_id,5)取出0-4个值。
2.针对不动的hashid把数据存储到不同的表中。
水平拆分面临的挑战:
1.跨分区表进行数据查询
前端业务统计:业务上给不同的用户返回不同的业务信息,对分区表没有大的挑战。
2. 统计及后台报表操作
但是对后台进行报表统计时,数据量比较大,后台统计时效性比较低,后台就用汇总表,将前后台的表拆分开。