Web总结:数据库系统

前言

数据也是Web应用最重要的部分,而数据库恰好也是Web应用最容易出瓶颈的地方。经过几年的学习、实践我逐渐总结出了一套自己的数据库设计、实践原则,有一些是参考企业的,同时自己的优化方法也在里面。

选择合适的数据库

Mysql是目前的主流数据库之一,几乎可以承担起所有Web站点的数据处理操作(通过集群、主从等优化手段)。

大型应用会使用Oracel作为数据库(比如银行、证券、电信)。

另外值得一提的是NOSQL,由于它的灵活性高于关系型数据库,所以常用于作为缓存系统,如MemCache、Redis。

目前JS全栈流派会使用MongoDB作为后台数据库。即便如此,我还是推荐使用关系型数据库作为主数据库,而NOSQL作为辅助数据库。

一些原则

以下的设计都是基于Mysql的。

范式和冗余

关系型数据库的范式有六种,这是理论上的。

在实际开发中,往往达到第三范式即可,并添加一些冗余字段以方便查询。

主外键

建立逻辑上的主外键,但不建立硬性的主外键关系。

逻辑上的主外键意味着主外键关系的维护交于程序来完成,而不是数据库系统。这样可以避免主外键冲突而引起的不必要bug。

尽量不使用组合主键。

字段类型和大小

在为一个字段指定类型时,尽量使用整数类型。因为查询效率高,存储空间小。

整数类型字段,尽量使用 unsigned 。如果确实需要表示负数,那就用有符号的整数类型。

如果字段长度已知,务必使用char而不是varcharvarchar容易产生数据碎片,影响效率。比如存储MD5哈希值。

尽量避免可空字段,并给字段设置默认值。NULL 是一个非常恶心的东西,可能会引起索引分裂,并且有时候会引起意料之外的BUG。

索引

不要随意建立索引,应当根据慢查询建立适当的索引。比如经常需要排序、分组的字段,可以建立索引。

把组合主键、值唯一的字段建立为唯一索引。

尽量使用数据量比较少的索引。比如在整数类型上建立,而不是在文本类型上。这也意味着,在对 text 等数据量比较大的字段建立索引时,应取字段的前面几个字符建立索引即可,而不是建立全文索引。

尽量选择取值范围更大的字段建立索引。比如我们不应该在 性别字段上建立索引,因为它的取值太有限了。

尽量扩展索引,而不是建立一个新的。比如已有索引(user_id),现在要建立class_id的索引,可以考虑建立为(user_id, class_id)。

索引的建立是否恰当,最终取决于查询速度是否提高了。所以建议根据慢查询日志来建立适当的索引。

编码

尽量使用utf8mb4编码,这是四字节的UTF-8编码,是符合标准意义的。而utf8编码是用三字节存的,所以不能保存emoji表情。

查询

尽量避免大SQL查询。

尽量基于索引查询。

比起构造一个复杂的SQL查询做一次查询,简单、短小的基于索引的多次查询效率会更高。

实例:用户表

这张表取自我的毕业设计,先看表结构:

mark

上面所有的整数类型都是 unsigned 类型的

  • id 主键字段必有
  • type 标识用户类型。unsigned tinyint(0~255)足矣。
  • login 登录名。必须是变长的,因为这是用户设置的,最多不超过 32 字符。
  • telephone 手机号码。也可以用 bigint 存储。也可以用 char(11) 来存,也可以用 char(13) 来存(区号2位),这里用了 varchar(24) 是考虑到不同国家长度也不一样,干脆用变长字符来存了。
  • email 邮箱地址。目前最长的邮箱是 32 字符,理论最长是 320 字符,这里折中取了 128 字符。
  • password 密码。这里是是使用 Hash:make 方法生成的,最长只有 60 位,所以长度是 60,并且为了避免编码问题,使用 binary 字段来存储。
  • gender 性别。也可以使用 enum 存储,但强烈不推荐,更何况只是用来存储整数形式的内容。所以直接用 tinyint 类型。
  • register_iplast_login_ip 。ipv4地址。32 位的ipv4地址正好可以用一个int类型保存。php里使用ip2long函数即可完成转换。如果遇到 ipv6 地址,就需要用 binary(16) 来存了。
  • create_timeupdate_timedelete_time 是Laravel框架所使用的用于记录数据时间的字段,所以没办法设置为了 可以为空

看看索引:

1
UNIQUE KEY `type` (`type`,`login`)

这主要用于区分不同类型的用户。

坚持原创文章分享,您的支持将鼓励我继续创作!