第三部分 数据库和缓存（46题）
1. 列举常见的关系型数据库和非关系型都有那些？
关系型数据库： Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL
非关系型数据库： NoSql、Cloudant、MongoDb、Redis、HBase
2. MySQL常见数据库引擎及比较？
在实际工作中，选择一个合适的存储引擎是一个比较复杂的问题。每种存储引擎都有自己的优缺点，不能笼统地说谁比谁好。
InnoDB：MySQL默认存储引擎，支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
ISAM：ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。
MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。
MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。
3. 简述数据三大范式？
数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的数据库是需要满足一些规范的来优化数据存储方式。在关系型数据库中这些规范就可以称为范式，简单来说，就是根据需要，来优化数据存储方式！
第一范式：当关系模式R的所有属性都不能再分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。
满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。（说白了，就是关系模式R的所有属性不能再分解了，那么R就满足第一范式！）
第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。（说白了，就是非主属性都要依赖于每一个关键属性！）
第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。
4. 么是事务？MySQL如何支持事务？
事务是由一步或几步数据库操作序列组成逻辑执行单元，这系列操作要么全部执行，要么全部放弃执行。程序和事务是两个不同的概念。一般而言：一段程序中可能包含多个事务。（说白了就是几步的数据库操作而构成的逻辑执行单元）
MySQL如何支持事务？
MYSQL的事务处理主要有两种方法来实现事务的处理：
用begin,rollback,commit来实现
begin开始一个事务
rollback事务回滚
commit 事务确认
直接用set来改变mysql的自动提交模式
mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过
set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交
5. 简述数据库设计中一对多和多对多的应用场景？
一对一关系示例：一个学生对应一个学生档案材料，或者每个人都有唯一的身份证编号。
一对多关系示例：一个学生只属于一个班，但是一个班级有多名学生。
多对多关系示例：一个学生可以选择多门课，一门课也有多名学生。
6. 如何基于数据库实现商城商品计数器？
create table product(
id primary key auto_increment,
pname varchar(64),
pcount int);
7. 常见SQL
查看数据库：show databases;
使用数据库：use test;
查看表：show tables;
查看表结构：desc winton
建表：create table t1( id int not null primary key, name char(20) not null );
删除表：drop table test;
添加字段：alter table t1 add(score int not null);
移除字段：alter table t1 drop column score;
变更字段：alter table t1 change name score int not null;
全字段插入：insert into winton values(001,'zww'),(002,'rs');
个别字段插入：insert into winton(id) values(004);
单表全字段查询：select * from t1;
单表个别字段查询：select id from t1;
多表查询：select t1.id,t1.score,winton.name from t1,winton;
单表条件查询：select * from t1 where socre>90;
多表条件查询：select t1.id,t1.score,winton.name from t1,winton where t1.id=winton.id;
嵌套查询：select name from winton where id=(select id from t1 where score=90);
并查询：(select id from t1 )union(select id from winton);
交查询：select id from t1 where id in (select id from winton);
删除：delete from winton where id=4;
更新：update t1 set score=69 where id=2;
8. 简述触发器、函数、视图、存储过程？
触发器：触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。
create trigger trigger_name
after/before insert /update/delete on 表名
for each row
begin
sql语句：（触发的语句一句或多句）
end
函数：MySQL中提供了许多内置函数，还可以自定义函数（实现程序员需要sql逻辑处理）
自定义函数创建语法：
创建：CREATE FUNCTION 函数名称(参数列表)
RETURNS 返回值类型 函数体
修改： ALTER FUNCTION 函数名称 [characteristic …]
删除：DROP FUNCTION [IF EXISTS] 函数名称
调用：SELECT 函数名称(参数列表)
视图：视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影
create view view_name as select 语句
存储过程：把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现（经过第一次编译后再次调用不需要再次编译，比一个个执行sql语句效率高）
create procedure 存储过程名(参数,参数,…)
begin
//代码
end
9. MySQL索引种类
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
MySQL索引的类型：
1. 普通索引：这是最基本的索引，它没有任何限制
2.唯一索引：索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一
3.全文索引：全文索引仅可用于 MyISAM 表，可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加 （切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法）
4. 单列索引、多列索引：多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
5.组合索引（最左前缀）：简单的理解就是只从最左面的开始组合（实在单列索引的基础上进一步压榨索引效率的一种方式）
10. 索引在什么情况下遵循最左前缀的规则？
mysql在使用组合索引查询的时候需要遵循“最左前缀”规则
11. 主键和外键的区别？
1.主键是能确定一条记录的唯一标识
2.外键用于与另一张表的关联，是能确定另一张表记录的字段，用于保持数据的一致性
12. MySQL常见的函数？
聚合函数：
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
数学函数：
ABS(x) 返回x的绝对值
BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
13. 列举 创建索引但是无法命中索引的8种情况。
1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
2.对于多列索引，不是使用的第一部分，则不会使用索引
3.like查询是以%开头
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
6 对小表查询
7 提示不使用索引
8 统计数据不真实
9.单独引用复合索引里非第一位置的索引列.
14. 如何开启慢日志查询？
1 执行 SHOW VARIABLES LIKE “%slow%”，获知 mysql 是否开启慢查询 slow_query_log 慢查询开启状态 OFF 未开启 ON 为开启 slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
2 修改配置文件（ 放在[mysqld]下），重启 long_query_time 查询超过多少秒才记录
3 测试是否成功
4 慢查询日志文件的信息格式
15. 数据库导入导出命令（结构+数据）？
1.导出整个数据库
mysqldump -u用户名 -p密码 数据库名 > 导出的文件名
例如：C:\Users\jack> mysqldump -uroot -pmysql sva_rec > e:\sva_rec.sql
2.导出一个表，包括表结构和数据
mysqldump -u用户名 -p 密码 数据库名 表名> 导出的文件名
例如：C:\Users\jack> mysqldump -uroot -pmysql sva_rec date_rec_drv> e:\date_rec_drv.sql
3.导出一个数据库结构
例如：C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec > e:\sva_rec.sql
4.导出一个表，只有表结构
mysqldump -u用户名 -p 密码 -d数据库名 表名> 导出的文件名
例如：C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec date_rec_drv> e:\date_rec_drv.sql
5.导入数据库
常用source 命令
进入mysql数据库控制台，
如mysql -u root -p mysql>use 数据库
然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source d:wcnc_db.sql
16. 数据库优化方案？
总体思路从以下几个方面：
1、选取最适用的字段属性
2、使用连接（JOIN）来代替子查询(Sub-Queries)
3、使用联合(UNION)来代替手动创建的临时表
4、事务（当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰）
5.锁定表（有些情况下我们可以通过锁定表的方法来获得更好的性能）
6、使用外键（锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键）
7、使用索引
8、优化的查询语句（绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用）
17. char和varchar的区别？
char：定长，char的存取数度相对快
varchar：不定长，存取速度相对慢
18. 简述MySQL的执行计划？
执行计划explain命令是查看查询优化器如何决定执行查询的主要方法。
这个功能有局限性，并不总会说出真相，但它的输出是可以获取的最好信息，通过输出结果反推执行过程
19. 在对name做了唯一索引前提下，简述以下区别：
select * from tb where name = ‘Oldboy-Wupeiqi’
查找到tb表中所有name = ‘Oldboy’的数据
select * from tb where name = ‘Oldboy-Wupeiqi’ limit 1
查找到tb表中所有name = ‘Oldboy’的数据只取其中的第一条
20. 1000w条数据，使用limit offset 分页时，为什么越往后翻越慢？如何解决？
当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。
如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。
如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。
SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);
如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0，这个主键最好是int类型并且auto_increment
SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;
21. 什么是索引合并？
1、索引合并是把几个索引的范围扫描合并成一个索引。
2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。
3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。
22. 什么是覆盖索引？
如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，也就是说这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引。
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。
23. 简述数据库读写分离？
大多数互联网业务，往往读多写少，这时候，数据库的读会首先称为数据库的瓶颈，这时，如果我们希望能够线性的提升数据库的读性能，消除读写锁冲突从而提升数据库的写性能，那么就可以使用“分组架构”（读写分离架构）。
用一句话概括，读写分离是用来解决数据库的读性能瓶颈的。
其实就是将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。
24. 简述数据库分库分表？（水平、垂直）
1、IO瓶颈
第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。
第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。
2、CPU瓶颈
第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 ->，建立合适的索引，在业务Service层进行业务计算。
第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，增加CPU运算的操作 -> 水平分表。
1、水平分库
概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
结果：
- 每个库的结构都一样；
- 每个库的数据都不一样，没有交集；
- 所有库的并集是全量数据；
场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
分析：库多了，io和cpu的压力自然可以成倍缓解。
2、水平分表
概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
结果：
- 每个表的结构都一样；
- 每个表的数据都不一样，没有交集；
- 所有表的并集是全量数据；
场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。
3、垂直分库
概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
结果：
- 每个库的结构都不一样；
- 每个库的数据也不一样，没有交集；
- 所有库的并集是全量数据；
场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。
4、垂直分表
概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
结果：
- 每个表的结构都不一样；
- 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
- 所有表的并集是全量数据；
场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。
