面向前端的数据库指南

发表于 分类于 本文字数： 4.9k 阅读时长 ≈ 18 分钟

数据库概览

一般一个数据库系统（Database System）可分为数据库（Database）与数据管理系统（Database Management System，DBMS）两个部分。数据库就是按照数据结构来组织、存储和管理数据的仓库，管理这个数据库的软件就称之为数据库管理系统。也可以更形象地类比成中药铺子。

关系型与非关系型

关系型数据库

  1. 以行和列的形式存储数据，结构稳定，具有逻辑性，易于理解，但不易修改。
  2. 查询能力强，可以操作很复杂的查询。
  3. 一致性高。由于并发高，在数据同步的时候一般采用锁来保证数据的可靠性。
  4. 事务支持使得对于安全性能很高的数据访问要求得以实现。

非关系型数据库

  1. 非关系型数据库主要使用key-value的方式存储数据，即是“no relational”，由于数据间没有关联性，相对来说层级扁平。而且不需要经过SQL层的解析，所以性能非常高。
  2. 可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
  3. 数据结构灵活，每个数据都可以有不同的结构。
  4. 由于降低了一致性的要求，所以查询速度更快。
  5. 不适用于复杂SQL操作。
  6. 不支持事务。

关系型数据库 V.S. 非关系型数据库

关系型数据库的最大特点就是事务的一致性：传统的关系型数据库读写操作都是事务的，具有ACID的特点，这个特性使得关系型数据库可以用于几乎所有对一致性有要求的系统中，如典型的银行系统。相反地，关系型数据库为了维护一致性所付出的巨大代价就是其读写性能比较差，而像微博、facebook这类SNS的应用，对并发读写能力要求极高；另外，其固定的表结构，因此，其扩展性极差。

于是，非关系型数据库应运而生，由于不可能用一种数据结构化存储应付所有的新的需求，因此，非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。必须强调的是，数据的持久存储，尤其是海量数据的持久存储，还是需要一种关系数据库这员老将。

主流数据库

数据库排行榜中商业Oracle、微软SQLServer、免费MySQL，三者是目前市场占有率最高（依安装量而非收入）的关系数据库，而且很有代表性。

三大关系型数据库

  1. Oracle
    Oracle主要应用在传统行业的数据化业务中，比如：银行、金融这样的对可用性、健壮性、安全性、实时性要求极高的业务；零售、物流这样对海量数据存储分析要求很高的业务。而且由于Oracle对复杂计算、统计分析的强大支持，在互联网数据分析、数据挖掘方面的应用也越来越多。
  2. MySQL
    MySQL基本是生于互联网，长于互联网。其应用实例也大都集中于互联网方向，MySQL的高并发存取能力并不比大型数据库差，同时价格便宜，安装使用简便快捷，深受广大互联网公司的喜爱。最大的一个特色，就是自由选择存储引擎。每个表都是一个文件，都可以选择合适的存储引擎。常见的引擎有 InnoDB、 MyISAM、 NDBCluster等。并且由于MySQL的开源特性，针对一些对数据库有特别要求的应用，可以通过修改代码来实现定向优化，例如SNS、LBS等互联网业务。
  3. MS SQLServer
    windows生态系统的产品，好处坏处都很分明。好处就是，高度集成化，微软也提供了整套的软件方案，基本上一套win系统装下来就齐活了。因此，不那么缺钱，但很缺IT人才的中小企业，会偏爱 MS SQL Server。例如，自建ERP系统、商业智能、垂直领域零售商、餐饮、事业单位等等。

两大非关系型数据库

NoSQL主要有以下几类：临时性键值存储（memcached、Redis）、永久性键值存储（ROMA、Redis）、面向文档的数据库（MongoDB、CouchDB）、面向列的数据库（Cassandra、HBase）。最热门的是MongoDB 和 Redis。下面从以下几个维度对redis、mongoDB进行对比：

  1. 性能
    性能都比较高，总体来讲，TPS方面redis优于mongodb；

  2. 可操作性
    redis数据丰富，较少的网络IO次数；
    mongodb支持丰富的数据表达、索引，类似关系型数据库MySQL，支持的查询语言非常丰富。
    总体来讲，MongoDB优于Redis。

  3. 内存空间的大小和数据量的大小
    redis在2.0版本后增加了自己的VM特性，突破物理内存的限制，可以对key value设置过期时间（类似memcache）；
    mongoDB适合大数据量存储，依赖系统虚拟内存管理，采用镜像文件存储；内存占有率比较高，官方建议独立部署在64位系统。
    不同的应用角度看，各有优势。

  4. 可用性
    redis依赖客户端来实现分布式读写，主从复制时，每次从节点重新连接主节点都要依赖整个快照,无增量复制；不支持自动sharding,需要依赖程序设定一致hash机制。
    mongoDB支持master-slave,replicaset（内部采用paxos选举算法，自动故障恢复）,auto sharding机制，对客户端屏蔽了故障转移和切分机制。
    总体来讲，MongoDB优于Redis；单点问题上，MongoDB应用简单，相对用户透明，Redis比较复杂，需要客户端主动解决。

  5. 可靠性(持久化)
    redis依赖快照进行持久化，aof增强了可靠性的同时，对性能有所影响;
    MongoDB从1.8版本开始采用binlog方式（MySQL同样采用该方式）支持持久化的可靠性。
    对于数据持久化和数据恢复，MongoDB优于Redis

  6. 数据一致性（事务支持）
    redis事务支持比较弱，只能保证事务中的每个操作连续执行;
    mongoDB不支持事务。
    此方面，Redis优于MongoDB。

  7. 数据分析
    mongoDB内置了数据分析的功能，Redis不支持

  8. 应用场景
    redis：数据量较小的更性能操作和运算上;
    MongoDB:主要解决海量数据的访问效率问题。

MySQL

组内推荐使用MySQL，所以接下来介绍下MySQL。

重要概念

主键(primary key)

能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

外键（foreign key）

是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键（即该表中的主键没有一个外键和它相关联）。

约束（constraints）

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
常用SQL约束：

  • NOT NULL - 非空约束，指定某列不为空。
  • UNIQUE - 保证某列的每行必须有唯一的值。
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK - 保证列中的值符合指定的条件。
  • DEFAULT - 规定没有给列赋值时的默认值。

索引(index)

是用来快速地寻找那些具有特定值的记录。主要是为了检索的方便，是为了加快访问速度， 按一定的规则创建的，一般起到排序作用。所谓唯一性索引，这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

存储过程(proceduer)

存储过程是保存在数据库上的一段可执行代码。与java中的函数类似。通过一系列的SQL语句，根据传入的参数（也可以没有），通过简单的调用，完成比单个SQL语句更复杂的功能，存储在数据库服务器端，只需要编译过一次之后再次使用都不需要再进行编译。主要对存储的过程进行控制。

触发器(trigger)

触发器是一个特殊的存储过程，不同的是存储过程要用CALL来调用，而触发器不需要使用CALL也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MYSQL自动调用。触发器可以让你在执行INSERT,UPDATE或者DELETE的时候，执行一些特定的操作，可以在MYSQL中指定实在SQL语句执行前触发还是执行后触发。

事务(transaction)

特点：

  • 原子性(atomicity)：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。
  • 一致性(consistency)：在事务处理执行前后，数据库是一致的(数据库数据完整性约束)。
  • 隔离性(isolcation)：一个事务处理对另一个事务处理的影响。
  • 持续性(durability)：事务处理的效果能够被永久保存下来 。

过程：

  • Start transaction：开始一个事务。
  • Commit：提交所做的修改。
  • Rollback：回滚所做的修改。如果在操作时出错，应该从新开始一个事务。

总结：事务从Start transaction到Commit或者Rollback结束，这中间的语句是一个整体，如果执行Rollback，那么这些动作都会回滚。如果执行Commit那么这些动作全部执行成功。

锁(lock)

InnoDB主要实现了两种类型的行锁：共享锁和排它锁。InnoDB行锁是通过给索引项加锁实现的，如果没有索引，InnoDB会通过隐藏的聚簇索引来对记录加锁。对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；对于一般的Select语句，InnoDB不会加任何锁。

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同的数据集的排他锁（我读的时候，你可以读，但是不能写）。
  • 排他锁（X）：允许获得排他锁的事务更新数据，但是组织其他事务获得相同数据集的共享锁和排他锁（我写的时候，你不能读也不能写）。

何时在InnoDB中使用表锁：
InnoDB在绝大部分情况会使用行级锁，因为事务和行锁往往是我们选择InnoDB的原因，但是在如下情况我们也考虑使用表级锁：

  • 当事务需要更新大部分数据时，表又比较大，如果使用默认的行锁，不仅效率低，而且还容易造成其他事务长时间等待和锁冲突。
  • 事务比较复杂，很可能引起死锁导致回滚。

避免死锁
在InnoDB中，锁是逐步获得的，就造成了死锁的可能。当两个事务都需要获得对方持有的锁才能够继续完成事务，导致双方都在等待，就会产生死锁。有多种方法可以避免死锁，这里只介绍常见的三种：

  • 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；
  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
  • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

MySQL三大范式和反范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。但是有些时候一昧的追求范式减少冗余，反而会降低数据读写的效率，这个时候就要反范式，利用空间来换时间。

  • 第一范式：确保每列的原子性。
  • 第二范式：确保表中的每列都和主键相关。
  • 第三范式：确保每列都和主键列直接相关,而不是间接相关。
  • 反三范式：没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于DML的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的。

使用

  1. 首先安装MySQL，可参照此教程=>Mac安装MySQL。（注：一定要记住临时密码并重置）；
  2. 安装可视化工具：sequel pro或者Navicat都可以（我用sequel pro举例）；
  3. 然后打开 sequel pro就可以在里面进行与数据库的连接，连接时输入的信息有：
    1
    2
    3
    4
    5
    Host –在本机上面就输入本机的IP地址（127.0.0.1），   
    username – 默认是 root   
    password – 就是数据库的密码   
    database – 可以不填   
    port – 端口号 默认是 3306
  4. 连接后就可以进行各种操作啦（附上简单教程）

常用命令

基本会增删改查即可（SQL语法学习地址）

  • SELECT - 从数据库中提取数据
  • UPDATE - 更新数据库中的数据
  • DELETE - 从数据库中删除数据
  • INSERT INTO - 向数据库中插入新数据
  • CREATE DATABASE - 创建新数据库
  • ALTER DATABASE - 修改数据库
  • CREATE TABLE - 创建新表
  • ALTER TABLE - 变更（改变）数据库表
  • DROP TABLE - 删除表
  • CREATE INDEX - 创建索引（搜索键）
  • DROP INDEX - 删除索引

争议点

该不该摒弃外键

矛盾焦点

数据库设计是否需要外键。这里有两个问题：一个是如何保证数据库数据的完整性和一致性；二是第一条对性能的影响。

正方观点

  1. 由数据库自身保证数据一致性，完整性，更可靠，因为程序很难100％保证数据的完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。
  2. 有主外键的数据库设计可以增加ER图的可读性，这点在数据库设计时非常重要。
  3. 外键在一定程度上说明的业务逻辑，会使设计周到具体全面。

反方观点

  1. 数据库需要维护外键的内部管理；
  2. 外键等于把数据的一致性事务实现，全部交给数据库服务器完成，可控性就会降低。你自己写代码控制的话，相当于许多数据逻辑自己控制，虽然麻烦，但是出了问题有利于自己查找并针对性解决。
  3. 有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，进行完整性检查是一个耗费时间和资源的过程。在并发小的情况下，这种开销应该没什么区别，但是在高并发的情况下，数据库的外键对性能的影响肯定是很高的；
  4. 外键还会因为需要请求对其他表内部加锁而容易出现死锁情况；
  5. 可以用事务或应用程序保证数据的完整性。

结论

外键是否采用看业务应用场景，以及开发成本的，大致列下什么时候适合，什么时候不适合使用：

  1. 互联网行业应用不推荐使用外键：
    用户量大，并发度高，为此数据库服务器很容易成为性能瓶颈，尤其受IO能力限制，且不能轻易地水平扩展；若是把数据一致性的控制放到事务中，也即让应用服务器承担此部分的压力，而引用服务器一般都是可以做到轻松地水平的伸缩；

  2. 传统行业建议使用外键：
    软件应用的人数有限，换句话说是可控的；数据库服务器的数据量也一般不会超大，且活跃数据有限。综合上述2句话描述，也即数据库服务器的性能不是问题，所以不用过多考虑性能的问题；另外，使用外键可以降低开发成本，借助数据库产品自身的触发器可以实现表与关联表之间的数据一致性和更新；最后一点，使用外键的方式，还可以做到开发人员和数据库设计人员的分工，可以为程序员承担更多的工作量；

  3. 需要注意的是：MySQL允许使用外键，但是为了完整性检验的目的，在除了InnoDB表类型之外的所有表类型中都忽略了这个功能。如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

学习重点

  • 熟练运用增删改查命令
  • 用事务来代替外键来保证完整性
  • 用索引提高查询速度
  • 熟练运用锁来优化数据库

推荐书籍

《MySQL必知必会》（适合入门）
《高性能MySQL》
《MySQL技术内幕-innorDB搜索引擎》