关于数据库的几点总结

2018-12-29 本文已影响204人 zh_harry

mysql.jpeg

范式与冗余

满足数据库的范式的目的就是为了减少或消除冗余，所以两者是一对矛盾，即满足范式可能就会出现冗余，而冗余就满足不了范式。所以在数据库的设计中要做一个平衡。
个人经验在以下场景可以考虑冗余

为了查询性能，减少io次数，比如冗余user_name,不需要为了显示用户名而再进行一次io，而这种场景带来的一个问题就是数据不一致，需要根据具体业务而定。
业务需要的数据冗余，比如订单的快照信息，因为用户下单那一刻的数据要快照下来以备查，防止以后产品信息的修改，影响订单的数据。

数据库范式的优点就是减少存储，如果有网络传输也会减少网络带宽，提高传输速度。比如说某种数据的原始数据的订阅，尽量满足数据范式，而需要在订阅端对数据进行进一步的解析处理，即适当加一些冗余，以满足查询性能。

总结起来就是空间和时间的平衡。

字段类型

char与varchar 的区别
char 是定长而varchar 是变长，尽量使用varchar 提高磁盘的使用率。
如果使用char 时，要注意' '的length长度。
int bigint
如果业务上不会出现负数，尽量用unsigned。避免空间浪费。
long（unsigned int） timestamp datetime
long 和timestmp的存储是相对于1970-1-1 的时间戮，而timestamp实际上是int的一种封装,都是4个字节INT，数据精确到秒级别。在MySQL 5.6.4及之后版本，可以将时间戳类型数据最高精确微秒(百万分之一秒)，数据类型定义为timestamp(N),N取值范围为0-6。所以使用long 会更精确，而可读性较差，性能相同。
而datetime 实际上类似于string,但占用8字节空间。不隐含时区概念，举个例子：同样的字段如果用datatime 存储，在中国看和美国看（修改计算机时区）都是一致的,而timestamp和long 则会有时区的差异。一致情况下尽量用long 和timestamp,而在业务声景需要时，比如机票的时间都是本地时间，全球来看都是这个时间，所以要用datatime。
字符集的选择
如果是内容中包含手机表情包尽量用utf8mb4。
字段为空
这个也是比较有争议的讨论，有人认为数据库应该根据业务来设计，业务场景的null 和''或0不是一个概念，那么尽量满足业务。而葱技术角度思考，字段尽量不为空，并加默认值，因为数据的null是不会被索引的，查询的性能会有影响。
float double vs big decimal
计算机底层用十进制的科学计数法来表示float double，这种表示法会产生一定的误差（计算机组成及汇编语言原理P17）有解释为什么。所以这里所有与钱相关的类型最都要使用bigdeciaml。

where 子句

where 子句尽量限制在安全范围内，比如更新某个的订单状态，最好加上当前用户的id 限制以防止被其他用户更新。
update 和delete 语句会对查询的记录加间隙锁，间隙锁的作用是防止，在update或者delete的时候修改了后面插入的数据。由于间隙锁的产生在高并发的条件下，会产生死锁。为避免死锁，在批量更新和删除时，可以先批量差出来再通过主键id更新。
where 子句不要现在函数，常见的如now()，因为函数不走索引。
where 子句的查询并发较高，字段较少且比较稳定时，需要创建复合索引，以尽量辟免全表扫描。
作为编码原则，应尽量遵守最左匹配原因，即最大化的利用索引。

field 字段

查询尽避免*,即减少磁盘io也减少网络开销。

order by

需要创建索引，有必要的情况需要复合索引。
为避免深度分页，一般分通过业务的排序字段来实现浅分页。而order by 如果非主键的情况，可能会出现重复（因为limit n 出现多条只取m条m<n 这里存在随机情况）对最终的查询结果会产生影响。所以最尽最在order by 后边加上主键id。以避免重复。

b+tree vs b tree

balance-tree 读(b tree)因为中文教科书印刷原因，很多教授读b减树，这里纠正一下。

b-tree.jpg

b+tree

b+tree.jpg

从上图可知b+tree 是比较适合mysql 索引结构的，两点原因

索引块不存在数据，只包含索引和指针，这样可以最大化的读取索引，从而减少索引的io操作。
数据节点间存在指针，以保证顺序。
所以索引的条件尽量保证范围是确定的比如= >= <= like 'prefix%' 会走索引，而!= like '%' in not exist 等将不会走索引，而null值不会包含在索引中，上文已经提到。

以上为经验之谈，可能不够严谨，未免有漏洞，有问题请专业dba指正。