面试10月15日 数据库（一）

1、什么是数据库管理系统（DBMS） 并举例一些常见的

RDBMS: 关系型数据库管理系统
比较适合于,安全级别要求高的数据以及关系较复杂的数据

NoSQL:非关系型数据库管理系统
适合于高性能存取数据,一般是配合RDBMS进行使用的
针对大数据处理分析,分布式架构更加擅长


RDBMS  ：
MySQL 、Oracle、MSSQL（SQL Server）、PG
 
NoSQL：Not Only SQL
键-值（key-value）：Redis, memcached
文档（document）：Mongodb

2、你用的是什么数据库，版本号是多少

原来使用的是5.6版本的mysql，后面今年过年后数据库升级到5.7.20

3、相比较与mysql5.6，5.7有哪些新特性，8.0你觉得怎么样

mysql5.7是5.6的新版bai本，在没有减少功能的情du况下新增了功能与进行了优化

1、原生json支持

MySQL 5.7可以定义原生json类型数据，对json数据进行有效性检查，查询不需要遍历所有字符串才能找到数据，通过虚拟列的功能可以对JSON中的部分数据进行索引。

2、多源复制

MySQL 5.7开始支持多源复制，也就是多主一从的复制架构。各个库汇总在一起，就算是其他库都挂了（整个机房都无法连接了），还有最后一个救命稻草。不需要每个库都做一个实例，也减少了DBA的维护成本。

3、InnoDB优化

MySQL 5.7针对innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_method提供了更加合适的默认值。

8.0

1、临时表改进
在mysql5.7中，所有的临时表都被创建在一个叫“ibtmp1”的表空间中，另外，临时表的元数据也将存储在内存中（不再存储在frm文件中）
在mysql8.0中，使用临时表存储引擎作为临时表（为优化join，union等操作而创建的）存储的默认引擎，从而其换掉了原有的内存存储引擎
新的引擎使得VARCHAR和VARBINARY列的存储更为高效（所有存储空间得到完全分配）。

2、取消默认MyISAM系统表

3、 UTF-8 编码：从 MySQL 8 开始，使用 utf8mb4 作为 MySQL 的默认字符集。

4、MySQL 8 大幅改进了对 JSON 的支持，

添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

5、结合NoSQL：MySQL 从 5.7 版本开始提供 NoSQL 存储功能，目前在 8.0 版本中这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。

4、一条sql语句的执行流程

5、如果超级管理员忘记密码了 怎么办

mysqld_safe --skip-grant-tables --skip-networking &
                跳过授权表            跳过tcp/ip链接

6、SQL语句的类型有哪些

DDL ：数据定义语言
DCL ：数据控制语言
DML ：数据操作语言
DQL： 数据查询语言

7、 列举一些常用的数据类型

tinyint  ： -128~127
int       ：-2^31~2^31-1
说明：手机号是无法存储到int的。一般是使用char类型来存储收集号
char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(255):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
enum('bj','tj','sh')：
枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。
DATETIME 占8个字节
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP 占4个字节
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

8、列举一些列的属性

约束(一般建表时添加):
**primary key** ：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
**not null**      ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
**unique key** ：唯一键
列值不能重复
**unsigned** ：无符号
针对数字列，非负数。

其他属性:
**key** :索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
**default**           :默认值
列中，没有录入值时，会自动使用default的值填充
**auto_increment**:自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
**comment ** : 注释

9、一些表的特性

存储引擎:
InnoDB（默认的）
字符集和排序规则:
utf8       
utf8mb4 可以支持表情符号 占4个字节

10、建库规范和建表规范

建库规范：
1.库名不能有大写字母   
2.建库要加字符集         
3.库名不能有数字开头
4. 库名要和业务相关
建表规范：
1. 表名小写
2. 不能是数字开头
3. 注意字符集和存储引擎
4. 表名和业务有关
5. 选择合适的数据类型
6. 每个列都要有注释
7. 每个列设置为非空，无法保证非空，用0来填充。

11、删除表几种必知

DELETE FROM stu
truncate table stu;
区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
truncate: DDL操作,对与表段中的数据页进行清空,速度快.

伪删除：用update来替代delete，最终保证业务中查不到（select）即可
1.添加状态列
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;
SELECT * FROM stu;
2. UPDATE 替代 DELETE
UPDATE stu SET state=0 WHERE id=6;
3. 业务语句查询
SELECT * FROM stu WHERE state=1;

12、什么是sql_mode

13、 什么是元数据

只要不是我们存储到数据库里的数据，大多都可以理解为元数据。描述数据库的任何数据—作为数据库内容的对立面—是元数据。因此，列名、数据库名、用户名、版本名以及从SHOW语句得到的结果中的大部分字符串是元数据。

14、DCL使用顺序以及各个作用是什么？

                                                      

15、MySQL常见的函数？ 

聚合函数：
　　 AVG(col)返回指定列的平均值
　　 COUNT(col)返回指定列中非NULL值的个数
　　 MIN(col)返回指定列的最小值
　　 MAX(col)返回指定列的最大值
　　 SUM(col)返回指定列的所有值之和
　　 GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
数学函数：
　　 ABS(x) 返回x的绝对值
　　 BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）

case when 判断 then 结果 end

16、数据库三大范式？

1）简单归纳：

　　第一范式（1NF）：字段不可分；
　　第二范式（2NF）：有主键，非主键字段依赖主键；
　　第三范式（3NF）：非主键字段不能相互依赖。

（2）解释：

　　1NF：原子性。 字段不可再分,否则就不是关系数据库;；
　　2NF：唯一性 。一个表只说明一个事物；
　　3NF：每列都与主键有直接关系，不存在传递依赖。

17、简述数据库设计中一对多和多对多的应用场景？

一对多：学生与班级---一个学生只能属于一个班级，一个班级可以有多个学生
多对多：学生与课程---一个学生可以选择多个课程，一个课程也可以被多个学生选择

18、1000w 条数据，使用 limit offset 分页时，为什么越往后翻越慢?如何解决?

当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，
你需增加order by，并且order by字段需要建立索引。

如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，
where会过滤数据，使数据失去连续性。

如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。

19、简述leftjoin和rightjoin的区别?

left join:外链接之左连接：优先显示左表全部记录
					#以左表为准，即找出所有员工信息，当然包括没有部门的员工
					#本质就是：在内连接的基础上增加左边有右边没有的结果
right join:外链接之右连接：优先显示右表全部记录
          #以右表为准，即找出所有部门信息，包括没有员工的部门
          #本质就是：在内连接的基础上增加右边有左边没有的结果

20、简述数据库分库分表?(水平、垂直)

水平分库
1、概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
2、结果：
每个库的结构都一样；
每个库的数据都不一样，没有交集；
所有库的并集是全量数据；
3、场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
4、分析：库多了，io和cpu的压力自然可以成倍缓解。

水平分表
1、概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
2、结果：
每个表的结构都一样；
每个表的数据都不一样，没有交集；
所有表的并集是全量数据；
3、场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
4、分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

垂直分库
1、概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
2、结果：
每个库的结构都不一样；
每个库的数据也不一样，没有交集；
所有库的并集是全量数据；
3、场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
4、分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

垂直分表
1、概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
2、结果：
2.1、每个表的结构都不一样；
2.2、每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
2.3、所有表的并集是全量数据；
3、场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
4、分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。
但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

水平分库方式主要根据用户属性（如地市）拆分物理数据库。一种常见的方式是将全省划分为个大区。
垂直分库方式：根据业务维度和数据的访问量等，进行数据的分离，剥离为多个数据库。例如，将一些
公用的配置信息存储到一个数据库中进行单独维护。

21、数据库索引种类 能具体讲讲他们吗？

聚集索引

(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)

辅助索引细分：

1.普通的单列辅助索引
2.联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表
查询
3.唯一索引
索引列的值都是唯一的.

22. 索引在什么情况下遵循最左前缀的规则？ 

mysql在使用组合索引查询的时候需要遵循“最左前缀”规则

23、列举 创建索引但是无法命中索引的8种情况。

• 1、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因）
• 2、对于多列索引，不是使用的第一部分(第一个)，则不会使用索引
• 3、like查询是以%开头
• 4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 5、如果mysql估计使用全表扫描要比使用索引快,则不使用索引
• 6 对小表查询
• 7 提示不使用索引
• 8 统计数据不真实
• 9.单独引用复合索引里非第一位置的索引列.

24. 在对name做了唯一索引前提下，简述以下区别：

select * from tb where name = ‘Oldboy’ -------------查找到tb表中所有name = ‘Oldboy’
的数据
select * from tb where name = ‘Oldboy’ limit 1------查找到tb表中所有name = ‘Oldboy’
的数据只取其中的第一条 

25、什么是索引合并?

 根据官方文档中的说明，我们可以了解到： 
1、索引合并是把几个索引的范围扫描合并成一个索引。
2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。 
3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

26、什么是覆盖索引? 

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点：
    1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
    2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据
的IO少很多。
    3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据
需要一次系统调用
    4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了
行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只
能用B-tree索引做覆盖索引。

当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息

27、mysql 下面那些查询不会使用索引

1、没有查询条件，或者查询条件没有建立索引
2、查询结果集是原表中的大部分数据，应该是25％以上。
3、索引本身失效，统计数据不真实
4、查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
5、隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
6、<> ，not in 不走索引（辅助索引）
7、like "%_" 百分号在最前面不走

28、索引有什么作用，有什么好处和坏处?

作用：
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。
数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执
行得更快，可快速访问数据库表中的特定信息。
优点：

1、大大加快数据的检索速度。
2、创建唯一性索引，保证数据库表中每一行数据的唯一性。
3、加速表和表之间的连接。
4、在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

1、索引需要占物理空间。
2、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

29、辅助索引(S)怎么构建B树结构的?

(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度

30、什么是回表，带来什么影响，如何减少？

 31、双11时遇到那种大量访问时，索引来不及设置时，应该如何解决，怎样提前做好准备？

32、聚集索引和辅助索引构成区别

聚集索引只能有一个,非空唯一,一般时主键
辅助索引,可以有多个,时配合聚集索引使用的
聚集索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构

33、关于索引树的高度受什么影响

1. 数据量级, 解决方法:分表,分库,分布式
2. 索引列值过长 , 解决方法:前缀索引
3. 数据类型:
变长长度字符串,使用了char,解决方案:变长字符串使用varchar
enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)
                                         1      2      3

34、索引是不是越多越好，可能会产生什么问题

索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用

35、建索引原则

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期

36、mysql索引的自优化，你还知道哪些优化算法

ICP、MRR、BKA
详情：https://blog.csdn.net/m0_37645820/article/details/90183782