数据库

是一种专门管理数据的软件，应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据，数据本身如何存储到文件，应用程序并不关心。

常用数据类型

INT	 整型 4字节整数类型，范围约+/-21亿
BIGINT	长整型 8字节整数类型，范围约+/-922亿亿
DOUBLE	浮点型 8字节浮点数，范围约+/-10308
DECIMAL(M,N)  高精度小数 由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)  定长字符串 存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)  变长字符串 存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN  布尔类型 存储True或者False
DATE  日期类型 存储日期，例如，2018-06-22
TIME  时间类型 存储时间，例如，12:20:59
DATETIME  日期和时间类型 存储日期+时间，例如，2018-06-22 12:20:59

通常来说，BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两种类型是使用最广泛的。

SQL

SQL是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。

数据库操作

DDL(Data Definition Language)

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

DML(Data Manipulation Language)

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

DQL(Data Query Language)

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

行记录和列字段

在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个列字段组成的。

主键

能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

主键的要求

  • 主键最好不要修改
  • 主键也不应该允许NULL

选取主键

不使用任何业务相关的字段作为主键。

注：身份证号、手机号、邮箱地址看上去可以唯一的字段，均不可用作主键。

id

作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id

常见可作为id字段的类型

自增整数类型

数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键。

全局唯一GUID类型

使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

建议

  • 对于大部分应用来说，通常自增类型的主键就能满足需求。
  • id数据类型一般使用BIGINT，如果使用INT自增类型，当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错，使用BIGINT自增类型则可以最多约922亿条记录。

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

建议

没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

外键

在students表中，通过class_id的字段，可以把数据与另一张表classes关联起来，这种列称为外键。

理解

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

建议

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。

拆表

把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

索引

  • 在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。
  • 索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。
  • 通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。例如如果要经常根据score列进行查询，就可以对score列创建索引

索引的效率

  • 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，对该列创建索引就没有意义。
  • 可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。
  • 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。但是，这些列根据业务要求，又需要具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。

例如，我们假设students表的name不能重复：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

通过UNIQUE关键字我们就添加了一个唯一索引。

事务

是一系列的数据库操作，是数据库应用程序的基本单元。

四大特征

原子性

要么执行，要么不执行。

隔离性

所有操作全部执行完以前其它会话都不能看到过程。

一致性

事务前后，数据总额一致。

持久性

一旦事务提交，对数据的改变就是永久的。

锁机制

锁是网络数据库中的一个非常重要的概念，当多个用户同时对数据库并发操作时，会带来数据不一致的问题，所以，锁主要用于多用户环境下保证数据库完整性和一致性。

锁机制的目的

处理并发问题。

并发控制主要采用的技术手段

乐观锁
悲观锁

并发控制造成的锁

活锁

定义

指的是T1封锁了数据R，T2同时也请求封锁数据R，T3也请求封锁数据R，当T1释放了锁之后，T3会锁住R，T4也请求封锁R，则T2就会一直等待下去。

解决方法

采用“先来先服务”策略可以避免。

死锁

定义

就是我等你，你又等我，双方就会一直等待下去。

例如：T1封锁了数据R1，正请求对R2封锁，而T2封住了R2,正请求封锁R1，这样就会导致死锁。

死锁这种没有完全解决的方法，只能尽量预防。

锁的分类

数据库系统角度

排他锁

X锁，也叫写锁，用于数据写操作，独占，一个事务对对象加了排他锁其他事务就不能再加锁了。

性质

仅允许一个事务封锁此页，其他事务必须等到它释放才能加锁，事务结束释放锁。

共享锁

S锁，也叫读锁，用于所有的只读数据操作，非独占，允许多个并发事务读取他锁定的资源。

性质

多个事务可封锁同一个共享页，不能修改，读完释放锁。

更新锁

U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。

因为当使用共享锁时，修改数据操作分为两步：

  • 获得一个共享锁
  • 读取数据共享锁升级为排他锁，执行修改操作

如果有两个或多个事务同时对一个事务申请了共享锁，在修改数据时，这些事务都要将共享锁升级为排他锁。这时，这些事务都不会释放共享锁，而是一直等待对方释放，这样就造成了死锁。

解决办法

如果一个数据在修改前直接申请更新锁，在数据修改时再升级为排他锁，就可以避免死锁。

性质

预定要对此页加X锁，允许其他事务读取，不允许加U锁和X锁，当被读取的页需要被更新时升级为排他锁，事务结束释放。

程序员角度(依靠数据库锁机制实现的技术)

悲观锁(用排他锁(写锁)实现)

理解

很悲观，每次取数据的时候都会认为别人会修改，所以每次取数据都上锁，这样别人修改会遇到阻塞，直到它开锁。

分类

按范围

  • 行锁
  • 一行
  • 表锁
  • 整张表

数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。

举个例子，一个用户表user，有主键id和用户生日birthday。当你使用update … where id=?这样的语句时，数据库明确知道会影响哪一行，它就会使用行锁；

当你使用update … where birthday=?这样的的语句时，因为事先不知道会影响哪些行就可能会使用表锁。

乐观锁

理解

顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以，不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号等机制。

数据版本

为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

解决悲观锁问题

悲观锁数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。