关系型数据库是迄今为止最靠谱的, 性能最均衡的数据持久化方案了.所以现在大多数的Web项目都是用关系型数据库来作为最终的数据存储方案.由于关系型数据库很多, 展开来说几年都说不完, 而且互联网项目呢大多都使用开源开放的关系型数据库, 所以本文的目标就定位为给大家提供一个针对最常用的开源关系型数据库(MySQL以及PostgreSQL)的通用入门指南, 而且是针对Web项目常用部分功能的最小集合, 以及最基础的数据库表结构设计方法, 目的在于学习完此部分内容后, 可以快速的上手开发项目. 本文会加入大量的外联引用, 主要用于指向具体的数据库官方文档.
继续阅读本文需要具备基础的Linux操作基础, 本文将不会涉及商业关系型数据库, 但是除了安装维护部分, 其余的内容可以参考借鉴.
一. 安装数据库
虽然对开发来说安装部署数据库有运维可以背锅, 但是开发环境还是得自己动手, 所以必要的安装数据库的技能还是需要具备的.
1.1 MySQL的安装
MySQL是互联网领域最流行的开源关系型数据库系统, 用户众多文档文章齐全, 上手入门最快, 问问题能回答的人数最多.
1.1.1 在Linux安装MySQL
因为Linux的发行版庞杂, 本人经验有限, 就暂时只提供Centos和Ubuntu(Debian) 两个最常用发行版的安装方式, arch粉们请原谅我的Lowbe.
Centos版安装方式: 传送门
Ubuntu版安装方式:传送门
1.1.2 在Mac的OSX系统上安装MySQL
Mac版安装方式:传送门
1.1.3 在windows上安装MySQL
windows版安装方式:传送门
1.2 PostgreSQL的安装
PostgreSQL是非常强大的开源关系型数据库, 在能提供共和MySQL相差无几的性能的时候, 更提供了更加强悍的功能, 支持更多的索引以及更标准的SQL标准支持, 是很多老鸟的首选
1.2.1 在Linux上安装PostgreSQL
Centos版安装方式:传送门
Ubuntu版安装方式:传送门
1.2.2 在Mac上安装PostgreSQL
Mac上用于开发的PostgreSQL有一个神器 PostgresApp, 下载 后直接拖入应用程序即可使用, 打开就是开启数据库, Cmd+q就直接关闭数据库退出了, 特别适合在开发机上用.
1.2.3 在Windows上安装PostgreSQL
Windows版的安装方式: 传送门
1.3 云服务的数据库
现在的云服务商大多数都提供了云数据库的服务, 因为大部分关系型数据库的性能还是依托与磁盘的性能, 而云主机所提供的磁盘, 性能只能相当于物理主机的1/20(阿里云实测结果), 如果选择了SSD型的加强磁盘性能版本的, 最多也只能和普通磁盘版的物理主机持平(实测结果其实并没有这么乐观).所以在云主机上直接安装数据库性能非常的不理想, 所以现在大多数云服务提供商都会提供云数据库服务来解决这个问题.
如果不差钱的话, 其实可以直接用云服务提供的数据库来作为开发数据库, 也可以解决开发机性能不足启动了数据库就卡成翔的问题.
二. Web项目数据库的表结构设计基础
数据库表结构设计可以当成一门大学课程来讲, 我们这里也假定读者具备基本的数据库知识, 建议大家读一读清华大学出版社出版的数据库原理这本书.这里我特地加了限定性的词[Web项目],因为对于互联网项目的表结构设计相对于传统的DBMS的设计原则有一些差别, 有的时候需要打破一些设计原则才行.
2.1 数据库建模的简单入门-实体-表映射
数据库建模是一门大学问, 如果要深入学习请参考下面列举的一些学习书目.
参考书目:
机械工业出版社 数据库设计教程
电子工业出版社 数据库设计凡人入门 : 关系数据库设计指南（第二版）
为了不对初学小白"误人子弟", 我这里简要介绍一个上手简单的入门级设计方法: 实体-表映射的入门级用法. 这种方法简单的来说分两个步骤:
2.1.1 定义概念模型-划分实体
概念模型的设计又是一个比较大的话题, 我们这里用一个简单的例子来说明. 假设我们要做一个多人留言评论的系统, 按照产品经理的设计我们得到了下面几个User Story:
US01: <用户>输入"用户名"和"密码"后登录系统
US02: <用户>输入"评论内容"保存<评论>
US03: <评论> 列表页面, 每条评论显示<用户>"昵称", "头像", "评论内容", "评论时间"
...
根据这三条User Story我们可以分析出 2 个实体 <用户> 和<评论>. 并且分析出每个实体的部分属性. <>扩起来是实体, ""扩起来的是实体属性, 正常的User Story并没有标注, 我这里标注出来只是为了方便大家阅读
根据上面User Story的实体以及实体之间的关系如下图:
这个例子很简单了, 以后有机会再来详细说说设计的问题, 接下来就是如何把这个实体模型映射到数据表.
2.1.2 实体映射到数据表
还是接上面的例子, 我们得到了有两个实体构成的实体模型, 其中Comment实体的user属性关联到User实体. 实体映射到数据表的方法最简单的就是一对一直接映射, 也就是Comment对应到Comment表, User直接对应到User表.
其实呢, 在显示评论的时候, 只需要昵称和头像, 并且User的属性在随着系统的扩展会需要扩充表的属性, 而且登录需要用的信息 user_name和password并不会变, 所以我们可以将User表拆分成两个表.
这样我们就将可变和不变的部分分割开了, 并且提供了将来两个方面的扩展能力, 一是可以新建新的UserLogin表来支持不同的登录方式, 比如微信登录, 另外可以通过增加User表的属性来扩展User实体的属性. 并且在读取评论和用户数据的时候, 就不用把不需要显示的用户登录信息给一起查询出来了.
2.2 表结构设计之其他Tips
因为并不是系统化的数据库技能学习, 所以这里把其他部分的要点整理成一些Tips, 供大家在设计的时候参考
2.2.1 数据列类型
2.2.1.1 主键类型
不同数据库因为本身设计上的区别, 在一些特性上也是有所区别的.
MySQL最常用的innodb引擎, 因为是基于BTree结构存储的, 所以数据表是天然必须有主键的,折这就涉及到如何设计表的主键, 一般来说都会有id列来作为主键列, 因为BTree的特征, MySQL最好是用自增id作为主键在性能上是最好的, 而如果用uuid作为主键的话, 在性能上就会受到影响.
而PostgreSQL的存储结构是基于HEAP的, 所以用什么类型作为主键并没有性能上的差别.
2.2.1.2 字符串类型
字符串类型对不同的数据库都有Char, Varchar, Text这三种最常用的类型
MySQL数据库的时候, 对于定宽数据尽量采用Char类型, 比如手机号码, 身份证号码, Hash后的密码.因为Varchar和Text在性能上低于Char, 但是对于变长数据Char又会浪费空间.
PostgreSQL数据库不存在这个问题, Char, Varchar, Text的性能是相差无几的, 不需要特别主意
2.2.1.3 日期时间类型
一般来说数据库会有 datetime类型和timestamp两类日期时间类型. 第一种datetime是类似包含了年,月,日,时,分,秒,毫秒,时区等几个部分的结构体, 而第二种时间戳是用整形来表示的格林威治时间1970年1月1日0时0分0秒到当前的秒数(或者毫秒数).由此可见这两种方式各自的特点就比较明显了.
第一, datetime包含时区信息, 而timestamp不包含时区信息
第二, datetime的时间范围比timestamp广, 比如公元1543年5月24日哥白尼去世, 这个可以用datetime来记录但是就没法用timestamp来记录了.
所以诸如 创建时间, 最近更新时间, 发布时间, 审核时间等等近期操作的时间, 最好采用timestamp类型来记录, 而涉及到历史时间的时候再用datetime.
2.2.1.4 布尔类型
MySQL和PostgreSQL都支持Boolean类型, 但是实现上有差异. MySQL会隐式的将Boolean转化成TinyInt, 而且查询的时候在对Boolean类型的列可以用true, false, 也可以用1, 0. 所以从性能上和使用上, 以及数据宽度上, Boolean和TinyInt并没有什么太大的区别.
PostgreSQL的Boolean类型不能应用到Gist, Gin索引种, 而在加上Btree-Gist和Btree-Gin插件后, Gist和Gin索引都可以使用Int和TinyInt类型的列. 所以从长远来看, 用TinyInt代替Boolean会比较划算一些.
2.2.2 约束与索引
2.2.2.1 外键约束
在很多企业级应用来说, 外键约束是一个保障数据完整性的重要措施. 但是在互联网领域来说, 这一剂良方就是毒药了.因为一旦对数据库进行拆分, 无论是水平拆分还是垂直拆分, 都会因为破坏外键约束而碰壁. 所以不管什么数据库, 不要外键约束, 在外键的列上加索引就好了.
2.2.2.2 索引设计
索引设计是个大话题, 展开写能写本书, 往深入了说能扯到具体数据库的查询优化器怎么工作的, 数据结构原理什么的, 枯燥又乏味, 小白一定会瞌睡, 但是小白同学经常又会疑惑, 那么多列要在哪些列上加索引呢?所以有两个极端的情况就是, 要么干脆就不知道加索引, 要么就是干脆暴力一波流, 所有列统统加了索引.
首先我们要明白索引是什么. 索引是数据表以外一个额外的数据结构, 用来对数据排序的. 比如假设有一个字段是age, 类型是tinyint, 我们对这个字段创建了索引idx_age, 索引类型是Btree, 那么其实就是数据库里创建了一个名字叫idx_age的Btree数据结构, 索引的节点结构是类似key-value的形式, 排序是按照key来排序的, 所以key值也就是age这个字段的值, 而value就是实际存储数据的数据行的地址. 所以当我们对表查询时有一个WHERE age<20 的条件, 执行计划器发现了age字段有对应的索引idx_age的时候, 就会先从索引idx_age上执行age<20这个条件, 因为索引是预先排序的, 通过几次匹配就能定位到age=20的节点, 然后一次性就能取出所有小于20的记录的地址.文字描述不够详尽, 详细的工作过程请参见数据结构-Btree
由此可见, 数据库在对字段建立索引后是一种空间换时间的策略, 假设在一个表的三个字段上都创建了索引, 那么在表中插入记录的时候, 需要将数据插入表还需要对三个索引执行插入的操作, 那么对比没有索引的表, 插入的效率就会降低. 同理在update数据的时候, 如果update的字段正好有索引, 也会降低update的效率. 但是相对的来说, 查询的效率得到了数量级的提升.
所以不管是MySQL还是PostgreSQL来说建索引要克制, 一波流是不行的.一个简单的原则就是,不在WHERE中出现的字段, 就不要建索引.
索引优化什么的又是一个大坑, 而且不同数据库的查询优化器的脾气不一样, 工作方式不一样, 支持的索引类型不一样, 所以下面我们分数据库来看看各自有那些坑可以绕过去的.
MySQL的查询优化器有个特性就是一条查询只能匹配到一条索引, 那么多个查询条件如果只能其中一个条件匹配到索引的话, 效率就会大打折扣, 如果要每个列的查询都能匹配到索引的话, 就需要建立联合索引.因此MySQL的索引要遵循下面的一些原则:
1). 最左前缀匹配原则: 假设有条件 a = 1 and b = 2 and c > 3 and d = 4, mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配. 如果你创建了联合索引(a, b, c, d), 那么在匹配到 c的时候就会打住, d条件就不能匹配到索引上了, 单数如果创建索引的顺序是(a, b, d, c), 那么四个列都可以匹配到索引上
2).尽量用区分度高的列建立索引, 因为MySQL的查询优化器在查询条件匹配的数据条数超过总数据量的一定比例后就会放弃使用索引. 比如"性别"字段, 男女比例基本趋近于1:1, 那么在gender这个字段上加索引的话, 无论是你用 gender=0还是gender=1都会导致查询优化器放弃索引.
3).=和in可以乱序, 比如a = 1 and b = 2 and c=3的时候, 索引(a, b, c)还是(a, c, b)都不会影响
4).索引列不能参与运算, 比如 a/2=10 就不能匹配到索引.
PostgreSQL的查询优化器就没有这么娇气了, 但是和MySQL还是有一定共性的, 比如 2, 3, 4原则实际上都是通用的.
对所有数据库来说, 针对字符串字段建立索引, 如果用Like条件 like '%xxx%' 和 like '%xxx' 是铁定会被放弃索引的, 只有like 'xxx%' 才会匹配到索引.
PostgreSQL支持了更多类型的索引, 包括 Gist, Gin, Brin和函数索引, 并且还能对JSON类型的字段内部的field建索引. 具体可以参考 从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景
三. 基本查询的原则
相对表结构设计来说, 互联网项目中的的SQL查询就相当的简单了, 事实上,第一原则就是, SQL一定要写得简单, 最好不要出现超级复杂的SQL语句, 事实上互联网项目基本上不会有出现复杂SQL的机会, 如果你发现非要写出一个复杂SQL的时候, 就意味着你该重构你的系统了.
3.1 尽量减少JOIN的表
据传阿里的内部规范里规定, 一条查询不能join超过三张表.但是如果需求非要超过3张表怎么办呢? 那就只有打破范式的规定在表里建冗余的字段, 然后通过程序的办法来解决不同步什么的问题.
3.2 WHERE条件的列一定要有索引
和之前表结构部分的原则一样, WHERE条件里提到的列一定要有索引, 或者在某个多列索引中, 不然查询就会丢索引
3.3 Like条件导致丢失索引的问题
同前面的描述 like '%xxx%'和like '%xxxx'都会导致索引丢失. 但是在数据量不够大的时候其实并不能立马看出性能上的区别, 所以在项目刚起步的时候模糊查询这么写并没有什么问题, 但是在数据量超过100w的时候基本上就该考虑用全文检索来替代 like '%xxxx%' 这类模糊查询
3.4 OR条件导致丢失索引的问题
OR查询毁所有, 一旦查询条件中出现 or 条件, 一定会导致查询优化器放弃索引, 所以如果遇到非要OR不可的时候, 用union来代替. 比如 SELECT * FROM T1 WHERE a=5 OR b=5, 可以用
(SELECT * FROM T1 WHERE a=5) UNION (SELECT * FROM T1 WHERE b=5) 来代替.
如果OR的两边是相同的列, 比如 SELECT * FROM T1 WHERE a=3 OR a=5, 那么可以用
SELECT * FROM T1 WHERE a in (3, 5) 来代替.
3.5 NOT IN条件导致丢失索引的问题
在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
举个栗子:
SELECT …
FROM EMPLOY
WHERE D_NO NOT IN (SELECT D_NO
FROM DEPARTMENT
WHERE D_NAME=’XXX’);
如果要换成高效的写法的话, 如下:
SELECT ….
FROM EMPLOY AS E
WHERE NOT EXISTS (SELECT ‘X’
FROM DEPARTMENT AS D
WHERE D.D_NO = E.D_NO
AND D_NAME = ‘XXX’);
小结
新入门的小白如果掌握到上面的这些姿势的话, 就不会在自己独立做项目的时候在遇到数据库设计的时候找不到北了, 所以呢本章就是 Web项目数据库快速指北.
下一章我们将从一个实际的项目出发来把前两章的内容连贯起来实战一把
to be continue...
