数据库基础(2)

一 数据库范式

第一范式（1NF）

数据库中的每一列（每个字段）都是不可拆分的最小单元，也就是确保每一列的原子性。

第一范式的目标是确保每列的原子性。

第二范式(2NF)

满足1NF后，要求表中的所有列，都必须依赖于主键，而不可以有任意一列与主键没有关系，即一个表只描述一件事情。

第三范式（3NF）

满足第二范式(2NF)之后，要求表中的每一列只与主键直接相关而不是间接相关（表中的每一列只可以依赖于主键）

二 redis数据库

1 Mysql和redis的区别

mysql和redis的区别 - zxh_python - 博客园

mysql是关系型数据库，主要用于存放持久化数据，将数据存放在硬盘中，读取速度较慢。每次在请求访问数据库时，都存在IO操作，如此反复地访问数据库，会在反复链接数据库上花费大量时间，从而导致效率低。而且反复访问也会使数据库的负载过高。

redis是非关系型数据库，同时是一种缓存数据库，即数据存放在缓存中，缓存数据的读取速度快，能大大提高运行效率，但保存时间有限。主要用于存储使用频繁的数据，这样可以减少访问数据库的次数，提高运行效率。

在web应用中，一般搭配使用mysql和redis，web每次先访问redis，如果没有找到，才会去访问mysql，同时找到的数据也会更新到缓存数据库中。

2 Redis数据结构

字符串

可以存储下面三种类型的值：字符串/整数/浮点数。

GET/SET/DEL字符串的值，

可以对字符串执行自增自减的操作。INCR/DECR/INCRBY/DECRBY/INCRBYFLOAT

可以利用自增操作，来作为一个计数器，比如记录文章的阅读次数/获取文章的阅读次数

INCR Key

Get Key

列表

可以支持链表结构。一个列表结构可以有序存储多个字符串。

Lpush/rpush可以用于将元素推入列表的左端和右端。

Lpop/rpop可以用于从列表的左端和右端弹出元素

lindex可以用于获取列表中给定位置的元素  (0为开始索引，-1为范围的结束索引)

lrange可以获取列表在给定范围上的所有元素

可以用来存储任务信息，最近浏览过的文章，常用联系人信息

集合（无序的）

集合和列表都可以存储多个字符串，但是列表可以存储多个相同的字符串，而集合通过散列表来保证自己存储的每个字符串都是不同的。

SADD/SREM/SISMEMBER/SMEMBERS    添加元素到集合/从集合中删除给定元素/判断元素是否存在集合中/返回集合所包含所有元素

SINTER/SUNION/SDIFF可以求集合的交集/并集/差集

记录已投票用户名单

转发微博抽奖：

（1）SADD key转发微博加入集合中

（2）SMEMBERS 获取所有用户

（3）SRANDMEMBER key[count]抽取count名中奖者

实现点赞/签到等功能

基于集合运算：可以实现关注模型：可能认识的人/我关注的人也关注他

散列

可以存储多个键值对之间的映射。和字符串相似，散列存储的值可以是字符串也可以是数字值。

HSET/HGET/HGETALL/HDEL

适于将相关的数据存储在一起

比如要存储用户的信息（年龄/住址/联系方式等）

有序集合

有序集合与散列一样，都可以存储键值对。

有序集合的键被称为成员，每个成员都是独一无二的。

有序集合的值被称为分值，分支必须是浮点数。

是redis里面唯一一个既可以根据成员访问元素也可以根据分值以及分值的排列顺序访问元素的结构

ZADD/ZREM/ZRANGE/ZRANGEBYSCORE    将一个带有给定分值的成员添加到有序集合中/删除给定成员/根据元素在有序集合中的位置从有序集合中获取多个元素/获取有序集合在给定分值范围内所有元素。

基于发表时间排序的文章列表/基于投票数量排序的文章列表/单日阅读排行榜

3 redis持久化

2种方式：

快照持久化（RDB）：将存储在某一时刻的所有数据都写入硬盘中。

只追加文件（AOF）：在执行写命令时，将被执行的写命令复制到硬盘里面

快照持久化：

如果在新的快照文件创建完毕之前，redis/系统/硬件这三者之中任意一个崩溃了，redis将丢失最近一次创建快照之后写入的所有数据。

（1）通过bgsave来创建一个快照。redis会调用fork来创建一个子进程，子进程负责将快照写入硬盘，父进程继续处理命令请求。

（2）save命令来创建一个快照，接到save之后服务器在快照创建完成之前不再响应任何其它命令。save并不常用，通常只会在没有足够内存执行bgsave时才会用它。

如果配置了save

Save 60 1000当60秒内有1000次写入时,会触发bgsave命令。

在只使用快照持久化来保存数据时，如果系统发生崩溃，用户会丢失最近一次生成快照之后更改的所有数据。因此它只适用于即使丢失一部分数据也不会造成问题的应用程序

AOF持久化

会把被执行的写命令写到AOF文件的末尾，以此记录数据发生的变化。redis只要从头到尾从新执行一遍AOF文件所包含的写命令就可以恢复AOF文件所记录的数据集。

appendfsync选型和同步的频率：

Always     每个redis写命令都要同步写入硬盘，会严重降低redis速度

Everysec   每秒执行一次同步，显式地将多个写命令同步到磁盘

No  让操作系统来决定何时进行同步

为了兼顾数据安全和写入性能，可以设置everysec选项，redis每秒同步一次AOF文件时性能和不使用任何持久化特性相差无几。这样又可以保证即使发生崩溃，也最多丢失一秒内的数据

AOF文件的缺陷：

（1）AOF文件的体积太大，极端情况下体积不断增大的AOF文件会用完所有的硬盘空间。

（2）redis在重启之后需要重新执行AOF文件中的写命令来还原数据集，如果体积过大，还原操作执行的时间会非常长。

解决AOF文件体积不断增大的问题：

bgrewriteaof命令，该命令会移除AOF文件中冗余命令来重写AOF文件，使文件尽可能小。它的工作原理与bgsave差不多，都是创建一个子进程，由子进程负责对AOF文件重写，所以快照持久化因为创建子进程而导致的性能问题和内存占用问题在AOF中同样存在。

可以通过设置auto-aof-rewrite-percentage和auto-aof-rewrite-min-size来自动执行bgrewriteaof。

优势对比：

【redis】redis之持久化机制_远方不远-CSDN博客_redis持久化

4 REDIS容灾架构

 

如上图，redis数据落地有rdb和aof两种模式，可配置。redis会周期性的把更新的数据写入磁盘（rdb模式）或者把修改操作写入追加的记录文件（aof模式）。

rdb模式缺点：存在时间间隔T，灾难恢复时会丢失最大T时间的数据；

aof模式缺点：由于所有命令都写入aof文件，aof相对较大，所以redis也提供了aof文件重写机制来给aof瘦身；另外aof的写入模式选择也会对redis的性能产生影响（见图右）。

（1）主从同步

Redis的主从复制功能比较强，一个master可以拥有多个slave，而一个slave又可以拥有多个slave，如此下去，形成了多级服务器集群架构。

主从复制并不会阻塞master，因此可以把分流读操作到slave上，减轻master压力，同时也可关闭master的数据落地，改由slave数据落地，提升master的处理性能。

 

同步步骤（假设slave新注册）：

1、master收到slave的sync命令后，启动一个后台进程来生成rdb文件；

2、后台进程把生成的rdb文件发给slave；

3、master继续处理请求，同时把写命令保存到buffer中；

4、slave处理完rdb文件后，master把buffer中的命令继续同步给slave；

5、master对后续的写操作直接同步到slave；

（2）容灾切换

由于主从复制并不会阻塞master，为了进一步提升master处理性能，所以常见部署中master可以选择数据不落地，由slave来进行数据落地。

 

发生灾难时，client直接切换服务到slave，由于没有一致性校验机制，不能保障slave中的数据是最新的。

如果只部署Master，灾难恢复时，Master会自动从落地的rdb/aof文件中读取数据进行恢复，除aof的AOF_FSYNC_ALWAYS模式外，其他都存在少量数据丢失。

三 SQL语句优化

主要原则就是应尽量避免全表扫描，应该考虑在where及order by 涉及的列上建立索引。

建立索引不是建的越多越好，原则是：

第一：一个表的索引不是越多越好，也没有一个具体的数字，根据以往的经验，一个表的索引最多不能超过6个，因为索引越多，对update和insert操作也会有性能的影响，涉及到索引的新建和重建操作。

第二：建立索引的方法论为：

1. 多数查询经常使用的列；
2. 很少进行修改操作的列；

1. 索引需要建立在数据差异化大的列上

2、复合索引(形如(x,y,uid)索引的索引)

先看这样一条语句这样的：select* from users where area =’beijing’ and age=22;

使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。因为Mysql查询每次只使用一个索引。

例如我们建立了一个这样的索引（area,age,salary），那么其实相当于创建了（area,age,salary）,(area,age),(area)三个索引，

3、like语句优化

SELECT id FROM A WHERE name like '%abc%'

 由于abc前面用了“%”，因此该查询必然走全表查询，除非必要，否则不要在关键词前加%，优化成如下

SELECT id FROM A WHERE name like 'abc%'

4、where子句使用 ！= 或 <> 操作符优化

在where子句中使用 ！= 或 <>操作符，索引将被放弃使用，会进行全表查询。

 如SQL:SELECT id FROM A WHERE ID != 5 优化成：SELECT id FROM A WHERE ID>5 OR ID<5

5、where子句中使用 IS NULL 或 IS NOT NULL 的优化

在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。

如SQL:SELECT id FROM A WHERE num IS NULL 优化成num上设置默认值0，确保表中num没有null值，然后SQL为：SELECT id FROM A WHERE num=0

7、where子句使用IN 或 NOT IN的优化

in和not in 也要慎用，否则也会导致全表扫描。

方案一：between替换in

如SQL:SELECT id FROM A WHERE num in(1,2,3) 优化成：SELECT id FROM A WHERE num between 1 and 3

方案二：exist替换in

如SQL:SELECT id FROM A WHERE num in(select num from b ) 优化成：SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)

方案三：left join替换in

如SQL:SELECT id FROM A WHERE num in(select num from B) 优化成：SELECT id FROM A LEFT JOIN B ON A.num = B.num

8、where子句中对字段进行表达式操作的优化

不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。

如SQL:SELECT id FROM A WHERE num/2 = 100 优化成：SELECT id FROM A WHERE num = 100*2

如SQL:SELECT id FROM A WHERE substring(name,1,3) = 'abc' 优化成：SELECT id FROM A WHERE LIKE 'abc%'

如SQL:SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0 优化成：SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'

如SQL:SELECT id FROM A WHERE year(addate) <2016 优化成：SELECT id FROM A where addate<'2016-01-01'

9、任何地方都不要用 select * from table ，用具体的字段列表替换"*"，不要返回用不到的字段

12、批量插入优化

INSERT into person(name,age) values('A',14)
INSERT into person(name,age) values('B',14)
INSERT into person(name,age) values('C',14)

可优化为：

INSERT into person(name,age) values('A',14),('B',14),('C',14),

13、利用limit 1 、top 1 取得一行

有时要查询一张表时，你知道只需要看一条记录，你可能去查询一条特殊的记录。可以使用limit 1 或者 top 1 来终止数据库索引继续扫描整个表或索引。

如SQL：SELECT id FROM A LIKE 'abc%' 优化为：SELECT id FROM A LIKE 'abc%' limit 1

16、尽量用 union add 替换 union

union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union