面试之数据库学习

1. 数据库有哪几种范式。

范式（NF）：可以粗略的理解为一张数据表的表结构所符合的某种设计标准的级别。符合高一级范式的设计必定符合低一级别的范式。

关系可理解为一张带数据的表，而关系模式是这张数据表的表结构。

码：假如当K确定的情况下，该表除K之外的所有属性的值也就随之确定，那么K就是码。一张表可以有超过一个码，但只有一个主码。

关系中的某个属性或者某几个属性的组合，用于区分每个元组（可以把元组理解为一张表中的每条记录，也就是每一行）。

根据三种关系完整性约束中实体完整性的要求，关系中的码所包含的任一个属性都不能为空，所有属性的组合也不能重复

1NF：关系中的每个属性都不可再分。（1NF是所有关系型数据库的最基本要求）

2NF: 在1NF的基础上，消除了非主属性对于码的部分函数依赖。

3NF：在2NF的基础上，消除了非主属性对码的传递函数依赖。

1NF变2/3NF的过程就是将大数据表拆分成两个或者更过个更小的数据表。

3NF要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。

2.Mysql提供了哪几种事务隔离级别。

事务的四大特性： 原子性，一致性，隔离性和持久性。

原子性：事务中的操作要么不做，要么全做。

一致性：事务的执行结果必须使数据库从一个一致性状态变到另一个一致性状态。

隔离性：一个事务的执行。不能被其他事务干扰。

持久性：一个事务一旦提交，它对数据库中的数据的改变应该是持久性的。

MySQL数据的四种隔离级别：

read uncommited（读未提交）：最低级别，任何情况都无法保证。（两个个事务并发，事务B读取了事务A没有提交的数据）

read commited(读已提交):可避免脏读的发生。（两个并发的事务，事务A先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取数据时，数据已经发生了变化）

repeatable read(可重复读):可避免脏读，不可重复读的发生。（事务B的新增操作（提交），在事务A的查询操作之后，A打印结果时出现幻读）

serializable(串行话):可避免脏读，不可重复读，幻读的发生。 

MySQL默认的隔离级别是可重复读，Oracle默认的是读已提交。级别越高，执行效率越低，串行话即使已锁表的方式使得其他的线程只能在锁外等待。

在MySQL数据库中查看当前事务的隔离级别：

select @@tx_isolation;

在MySQL数据库中设置事务的隔离 级别：

set [glogal | session] transaction isolation level 隔离级别名称;

set tx_isolation=’隔离级别名称;’

其中，不可重复读和幻读的区别： 不可重复读重点在于update 和delete,而幻读的重点在于insert. 如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其他事务无法修改这些数据，就可以实现重复读了，但是这种方法无法锁住Insert的数据，所以当事务A先前读取了数据或者修改了全部数据，事务B还是可以Insert数据提交，这时事务A就会莫名奇妙多了一条之前没有的数据，这就是幻读。不能通过行锁来避免。需要串行话隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读，不可重复读和脏读等问题，但是会极大的降低数据库的并发能力。

3. MySQL中vchar与char的区别以及varchar(50)中50代表的涵义。

char的长度是不可变的，vchar的长度是可变的，取数据的时候，char类型要用trim()去掉多余的空格，而vchar是不需要的。 char的存取速度比vchar快，可谓是以空间换取时间效率。
varchar(50)中50的涵义： 最多存放50个字符

4. date, datetime和timestamp数据类型有什么区别。

date：保存精度到天。datetime和timestamp保存精度到秒。

datetime和timestamp的区别：

（1）受时区的影响不同。timestamp会跟随设置的时区变化而变化，而datetime保存的是绝对值不会变化。

（2）占用存储空间不同。timestamp存储占用4个字节，而datetime存储占用8个字节。

（3）可表示的时间范围不同。timestamp可表示范围'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'， 而datetime支持的范围更宽：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

（4）索引速度不同。timestamp更轻量，索引相对更快。

5. union和union all的区别

union在进行表连接后会去重。

union all只是简单的两个结果合并，会显示所有结果，如果有重复的结果，也会显示出来。

6. 简述mysql集中join的区别

内连接：内连接仅选出两张表中互相匹配的记录，即满足后面的where条件。

外连接： 会选出其他不匹配的记录，即使不满足后面的条件也可以显示出记录。 其中又分左连接和右连接。

左连接：会把左表的东西全部显示出来。右表只会显示符合条件的记录。 右连接正好相反。

自然连接：自然连接与内连接基本相同，不同之处在于自然连接只能是同名属性的等值连接，而内连接可以使用using或on子句来指定连接条件，连接条件中指出某两字段相等（可以不同名）。

7. drop, delete和truncate， alter的区别

drop:直接删掉表

truncate： 删除表中数据

delete： 执行一次删除一行

alter：ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

8. mysql有哪几种索引

B-Tree索引：每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

聚簇索引：一个表只能有一个聚簇索引。目前，只有solidDB和InnoDB支持聚簇索引，MyISAM不支持聚簇索引。一些DBMS允许用户指定聚簇索引，但是MySQL的存储引擎到目前为止都不支持。

哈希索引：哈希索引基于哈希表实现，只有精确索引所有列的查询才有效。

9. mysql复制原理和InnoDB事务与日志的实现方式

MySQL复制的工作原理可以分为以下三个步骤：

• 1.主服务器把数据更新记录到二进制日志中；
• 2.从服务器把主服务器的二进制日志copy到自己的中继日志（Relay Log）里；
• 3.从服务器重做中继日志中的时间，把更新应用到自己的数据库上；

从服务器有两个线程，一个是I/O线程，负责读取主服务器上的二进制日志，并将其保存为中继日志； 另一个是SQL线程，负责复制执行中继日志的内容。

Innodb总共有四种日志类型，简单介绍如下：

• 1.错误日志：记录出错信息，也记录一些警告信息或者正确的信息
• 2.慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中
• 3.二进制日志：记录对数据库执行更改的所有操作
• 4.查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行

日志的存放形式

隔离性：通过锁来实现

原子性、一致性和持久性是通过redo和undo来完成的

10. Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

区别：

• 1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
• 2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
• 3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
• 4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
• 5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高
• 6. 锁机制不同， Innodb为行级锁，myisam为表级锁。

11. innodb的读写参数优化

TODO

12. mysqldump和xtrabackup实现原理

(1)、备份计划

视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。

(2)、备份恢复时间

物理备份恢复快，逻辑备份恢复慢
这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考
20G的2分钟（mysqldump）
80G的30分钟(mysqldump)
111G的30分钟（mysqldump)
288G的3小时（xtra)
3T的4小时（xtra)
逻辑导入时间一般是备份时间的5倍以上

(3)、备份恢复失败如何处理

首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。

(4)、mysqldump和xtrabackup实现原理

mysqldump：mysqldump 属于逻辑备份。加入--single-transaction 选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)，
之后显式开启一个事务(START TRANSACTION /*!40100 WITH CONSISTENTSNAPSHOT */)，这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。 如果加上--master-data=1 的话，在刚开始的时候还会加一个数据库的读锁
(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(showmaster status)，马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务

Xtrabackup:xtrabackup 属于物理备份，直接拷贝表空间文件，同时不断扫描产生的 redo 日志并保存下来。最后完成 innodb 的备份后，会做一个 flush engine logs 的操作(老版本在有 bug，在5.6 上不做此操作会丢数据)，确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交
概念，因为 xtrabackup 并不拷贝 binlog，所以必须保证所有的 redo log 都落盘，否则可能会丢最后一组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点，数据文件虽然不是一致性的，但是有这段时间的 redo 就可以让数据文件达到一致性(恢复的时候做的事
情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，备份完后解锁。 这样就做到了完美的热备。

13. MySQL binlog的几种日志录入格式以及区别

 statement-based，row-based和混合模式(MIXED FORMAT) 

区别：
当使用 statement-based 方式，Master 将 SQL 语句写入binary log 文件，slave 获取到 binary log 文件以后，执行其中的 SQL 语句。
当使用 row-based 方式，Master 会将表中每一行的修改都记录到 binary log 文件中，slave 获取到 binary log 文件后逐行实现这些修改。
你也可以配置 MySQL 混合使用这两种方式(MIXED FORMAT)，即同时使用 statement-based 和 row-based ，在记录日志时，MySQL 会选择最适合的方式来记录日志。当使用混合日志时，statement-based 是默认的日志格式，但是在某些情况下会自动切换为 row-based 格式来记录日志，具体格式由使用的存储引擎和执行的语句共同决定。

每种日志格式都有优点和缺点，对于大部分用户，混合模式(MIXED FORMAT) 也许是最好的。

14. sql执行慢的原因有哪些以及有哪些常见的数据库的优化方法：

SQL执行慢的原因
1、硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
2、没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除，一是为了做数据分析,二是为了不破坏索引 ）
3、数据过多（分库分表）
4、服务器调优及各个参数设置（调整my.cnf）

优化： 加索引 杀进程 sql语句优化 加缓存 重建结构 读写分离 分区

15. MySQL复制流程

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中； 2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中； 3. 从：sql执行线程——执行relay log中的语句；

16.视图的作用

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。
创建视图：create view XXX as XXXXXXXXXXXXXX;
对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

17. 如何在最快时间之内重启500台db

可以使用批量 ssh 工具 pssh 来对需要重启的机器执行重启命令。 也可以使用 salt（前提是客户端有安装 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多线程工具同时操作多台服务器。

18.存储过程与触发器的区别

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则

19. Cassandra常用的配置有哪些？

1、Main runtime properties(主要的cassandra运行时属性)

a) cluster_name:集群名，同一集群的多个节点，集群名要一致

b) seeds: 种子节点，集群中的全部机器的ip，以逗号隔开

c) storage_port: Cassandra服务器与服务器之间连接的端口号，一般不需要修改，但要保证此端口上没有防火墙

d) listen_address: Cassandra集群中服务器与服务器之间相互通信的地址。如果留空，将默认使用服务器的机器名

e) native_transport_port: 默认的CQL本地服务端口，本地的cql客户端与服务器交互的端口

2、Changing the location of directories(相关的文件目录)

a) data_file_directories: 数据文件存放的目录，一个或多个

b) commitlog_directory: 提交信息的日志文件存放的目录

c) saved_caches_directory: 缓存存放的目录

20.不考虑事务的隔离性（Isolation）可能会出现哪些问题？

脏读 不可重复读 虚读(幻读)

21.使用Redis有哪些好处？

(1) 速度快，因为数据存在内存中，类似于HashMap，HashMap的优势就是查找和操作的时间复杂度都是O(1) 
(2) 支持丰富数据类型，支持string，list，set，sorted set，hash 
(3) 支持事务，操作都是原子性，所谓的原子性就是对数据的更改要么全部执行，要么全部不执行 
(4) 丰富的特性：可用于缓存，消息，按key设置过期时间，过期后将会自动删除

22.Redis数据结构有哪些？

String—字符串（key-value 类型）
Hash—字典(hashmap) Redis的哈希结构可以使你像在数据库中更新一个属性一样只修改某一项属性值
List—列表 实现消息队列
Set—集合 利用唯一性
Sorted Set—有序集合 可以进行排序 可以实现数据持久化

23. redis相比memcached有哪些优势

(1) memcached所有的值均是简单的字符串，redis作为其替代者，支持更为丰富的数据类型 
(2) redis的速度比memcached快很多 
(3) redis可以持久化其数据

24.Memcached服务特点及工作原理是什么

a、完全基于内存缓存的
b、节点之间相互独立
c、C/S模式架构，C语言编写，总共2000行代码。
d、异步Ｉ/O 模型，使用libevent作为事件通知机制。
e、被缓存的数据以key/value键值对形式存在的。
f、全部数据存放于内存中，无持久性存储的设计，重启服务器，内存里的数据会丢失。
g、当内存中缓存的数据容量达到启动时设定的内存值时，就自动使用LRU算法删除过期的缓存数据。
h、可以对存储的数据设置过期时间，这样过期后的数据自动被清除，服务本身不会监控过期，而是在访问的时候查看key的时间戳,判断是否过期。
j、memcache会对设定的内存进行分块，再把块分组，然后再提供服务。

25.  Memcached服务在不同企业业务应用场景中的工作流程

a、当web程序需要访问后端数据库获取数据时会优先访问Memcached内存缓存，如果缓存中有数据就直接获取返回前端服务及用户，如果没有数据（没有命中），在由程序请求后端的数据库服务器，获取到对应的数据后，除了返回给前端服务及用户数据外，还会把数据放到Memcached内存中进行缓存，等待下次请求被访问，Memcache内存始终是数据库的挡箭牌，从而大大的减轻数据库的访问压力，提高整个网站架构的响应速度，提升了用户体验。

b、当程序更新，修改或删除数据库中已有的数据时，会同时发送请求通知Memcached已经缓存的同一个ID内容的旧数据失效，从而保证Memcache中数据和数据库中的数据一致。

如果在高并发场合，除了通知Memcached过程的缓存失效外，还会通过相关机制，使得在用户访问新数据前，通过程序预先把更新过的数据推送到memcache中缓存起来，这样可以减少数据库的访问压力，提升Memcached中缓存命中率。

c、数据库插件可以再写入更新数据库后，自动抛给MC缓存起来，自身不Cache.

26. Memcached服务在企业集群架构中应用场景

一、作为数据库的前端缓存应用
a、完整缓存（易），静态缓存
例如：商品分类（京东），以及商品信息，可事先放在内存里，然后再对外提供数据访问，这种先放到内存，我们称之为预热，（先把数据存缓存中），用户访问时可以只读取memcached缓存，不读取数据库了。
b、执点缓存（难）
需要前端web程序配合，只缓存热点的数据，即缓存经常被访问的数据。
先预热数据库里的基础数据，然后在动态更新，选读取缓存，如果缓存里没有对应的数据，程序再去读取数据库，然后程序把读取的新数据放入缓存存储。

特殊说明 ：

1、如果碰到电商秒杀等高并发的业务，一定要事先预热，或者其它思想实现，例如：称杀只是获取资格，而不是瞬间秒杀到手商品。
那么什么是获取资格？

就是在数据库中，把0标成1.就有资格啦。再慢慢的去领取商品订单。因为秒杀过程太长会占用服务器资源。

2、如果数据更新，同时触发缓存更新，防止给用户过期数据。

c、对于持久化缓存存储系统，例如：redis，可以替代一部分数据库的存储，一些简单的数据业务，投票，统计，好友关注，商品分类等。nosql= not only sql

27. Memcached是什么，有什么作用？

Memcached是一个开源的，高性能的内存缓存软件，从名称上看Mem就是内存的意思，而Cache就是缓存的意思。

Memcached的作用：通过在事先规划好的内存空间中临时绶存数据库中的各类数据，以达到减少业务对数据库的直接高并发访问，从而达到提升数据库的访问性能，加速网站集群动态应用服务的能力。