数据库面试题

最新推荐文章于 2022-03-15 16:56:10 发布
最新推荐文章于 2022-03-15 16:56:10 发布
阅读量353 收藏 1
点赞数
分类专栏： sql

数据库面试题

2019年05月04日 21:29:19 李先森 阅读数 260

Section 1：小常识

1.union 和union all的区别？

union 会去重

2. 游标

游标（Cursor）是处理数据的一种方法，可以把游标当作一个指针，它可以指定结果表中的任何位置，对指定位置的数据进行处理。
显示游标：自己打开关闭
隐式游标：自动打开关闭

引用内容

3用户管理

#查看用户拥有的权限
select * from mysql.user;
select * from information_schema.USER_PRIVILEGES;
#删除用户
drop role admin;
#创建用户
create user admin identified by 'first_pd';
#给与权限
grant all privileges on *.* to admin ;
#改该用户密码
ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'Ldd123456';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

Section 1： DW Essential：

1.什么是DW？

就是一个集成的，面向主题，经过处理,基于OLAP的数据库。DW主要用于数据分析决策。
（
数据仓库是一个面向主题的（Subject Oriented）、集成的（Integrate）、相对稳定的（Non-Volatile）、反映历史变化（Time Variant）的数据集合。

数据仓库是为了便于多维分析和多角度展现而将数据按特定的模式进行存储所建立起来的关系型数据库，它的数据基于OLTP源系统。

首先，用于支持决策，面向分析型数据处理，它不同于企业现有的操作型数据库；）

2.OLTP与OLAP的区别

两种数据源模型
On line Transaction Processing On line analysis process
OLTP OLAP

用户 操作人员 决策人员

功能 日常操作 分析决策

DB设计 面积应用 面向主题

数据 当前的，最新的，细节的，二维的 | 历史的，概括的,多维集成的，统一的

存取及规模 读取少 | 大规模读取

实时性： 实时 | 非实时

维度 ： 单一 | 多维

3.Structure of DW

Initail Data Source >>ETL>> Target Data Source (ODS Operational Data Store)>> DW data warehouse >> DM DataMart>>
Data Front 前端工具 or BI tool

4.Difference between DW and DB

  1. 数据库面对事务transaction，用于日常更新，DB面对主题，用于数据决策分析；
  2. DB会存储当前交易数据，DW储存历史数据；
  3. DB主要用于insert ,DW主要用于select;

5 How to structure DW

确定业务主题，确定字段，确定维度和粒度，建表

6 星形模型与雪花模型的区别？

查询速度快，数据冗余高
1.星星的中心是一个大的事实表，发散出来的是维度表，每一个维度表用一个PK-FK连接到事实表，维度表之间彼此并不关联。一个事实表又包括一些度量值和维度。

查询速度慢，数据冗余少
2.雪花模型通过规范维度表来减少冗余度，也就是说，维度表数据已经被分组成一个个的表而不是使用一个大表。例如产品表被分成了产品大类和产品小类两个表。尽管这样做可以节省了空间，但是却增加了维度表的数量和关联的外键的个数。这就导致了更复杂的查询并降低了数据库的效率

7 数据仓库项目最重要或需要注意的是什么，以及如何处理？

数据仓库有两个重要目的,一是数据集成,二是服务BI
数据质量（数据的一致性，正确性，稳定性…）写定时监测脚本，去周期监测数据的异常，及时反馈。
数据效率（查询效率）

查询优化

1.分区分表分库。
2.避免全表扫描，首先应考虑在 where 及 orderby 涉及的列上建立索引
3 尽量不做 <> null的判断
4.避免大表之间的join。（join之前作限定，对大表做分页jion）
5 IO
6 存储过程 比select 快

8 Btree B-tree B+tree

在这里插入图片描述
在这里插入图片描述在这里插入图片描述

   三类索引(a:B树索引b:文本索引c:位图索引)


   B树：二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于走右结点；在二叉树的基础上，加入平衡算法，防止出现一侧节点过多。
   
   B-树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；
    
 所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
   
   B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

   B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

Section 2 ：pertain concept

事实表

事实表是包含大量数据值的一种结构。事实数据表可能代表某次银行交易,包含一个顾客的来访次数，并且这些数字信息可以汇总，以提供给有关单位作为历史的数据。
每个数据仓库都包含一个或者多个事实数据表。事实数据表只能包含数字度量字段和使事实表与维度表中对应项的相关索引字段.，该索引包含作为外键的所有相关性维度表的主键。
事实数据表中的“度量值”有两中：一种是可以累计的度量值，另一种是非累计的度量值。用户可以通过累计度量值获得汇总信息。

索引（Mysql为例）

#1.索引类型：
①primary： 唯一性，且一张表只有一个主键，且not null
②unique:唯一性：可以为null ，一张表可以有多个unique，
③normal
④fulltext 用于较长的文本索引
#2.索引方法
①Btree ：树结构算法；支持范围查找；非全表扫描；
(从根节点一步步扫描到叶节点)
②hash算法： hash算法；不支持范围，只能做=，in，等价于的计算；全表扫描‘’
( 简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。)

维度表

用来描述事实表的某个重要方面，维度表中包含事实表中事实记录的特性：有些特性提供描述性信息，有些特性指定如何汇总事实数据表数据，以便为分析者提供有用的信息，维度表包含帮助汇总数据的特性的层次结构

缓慢变化维:

在实际情况下，维度的属性并不是静态的，它会随着时间的流失发生缓慢的变化。
处理方法: 1新信息直接覆盖旧信息，2,保存多条记录，并添加字段加以区分（用y,n;0,1,2或用时间来区别新旧记录）
3.保存多条记录，并添加字段加以区分4.另外建表保存历史记录.5混合模式

粒度:（granularity）

是指数据仓库的数据单位中保存数据的细化或综合程度的级别，细化程度越高，粒度就越小。

钻取:

首先从某一个汇总数据出发，查看组成该数据的各个成员数据。

KPI（Key Performance Indication）关键业绩指标用来衡量业绩好坏比如销售这个主题,销售增长率、销售净利润就是一个KPI

E T L&抽取方式

extract/transformation/load寻找数据，整合数据，并将它们装入数据仓库的过程。

ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程，目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析的依据。

抽取方式（增量抽，全量抽）：

如何实现增量抽取

业务系统会记录业务发生的时间，我们可以用来做增量的标志,每次抽取之前首先判断ODS中记录最大的时间，然后根据这个时>间去业务系统取大于这个时间所有的记录。

游标 cursor

可以把游标当作一个指针，它可以指定结果表中的任何位置

Section 3 ：工作中常见问题

1.备份

#1.备份类型
①根据是否需要数据库离线
冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；
温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求；
热备（hot backup）：备份的同时，业务不受影响。
（注：
1、这种类型的备份，取决于业务的需求，而不是备份工具
2、MyISAM不支持热备，InnoDB支持热备，但是需要专门的工具）
#2.根据要备份的数据集合的范围
完全备份：full backup，备份全部字符集。
增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。
差异备份：differential backup 上次完全备份以来改变了的数据。
建议的恢复策略：
完全+增量+二进制日志
完全+差异+二进制日志
#3[具体备份操作](https://www.cnblogs.com/fengzhongzhuzu/p/9101782.html)

2.mysql大表建立索引

可以将表中数据转移到临时表，然后在空表上建立索引，完成之后再将数据重新insert到索引表中

3.数据库优化

  1. 加服务器配置 语句优化；
  2. 减少sql代码的计算量。尽量不用子查询，in/not in/LIKE ，若用子查询尽量在里面加限制条件；
  3. 储存过程快于sql代码，编译过的 I/O，
  4. 网速等
  5. optimize 清理表delete碎片
  6. 建立索引，
  7. ETL分层
  8. 插入大量记录 alter table 表名 disable keys
  9. 建立查询缓存
show variables like "%query_cache%";#0关 1开 2限定sql_cache
select sql_cache * from table ;
  • 1
  • 2

4.declare 和 set 的区别

局部变量和全局变量
declare @x int default 3 ；
set @x=3;

5存储过程与事物区别

存储过程针对某段经常调用的程序,里面可以包含事务但是无法回滚，（若某个存储过程有三句sql，前两句执行正确，第三局保存，前两句结果会执行成功）；
事务强调整体性，里面可有多段sql代码，可以有调用存储过程，但是代码要么全部执行，要么全部不执行。

HOLD ON!
关注
专栏目录
数据库常见面试题（附答案）
csdn问鼎
03-13 45万+
1.事务四大特性 原子性，要么执行，要么不执行 隔离性，所有操作全部执行完以前，其它会话不能看到过程 一致性，事务前后，数据总额一致 持久性，一旦事务提交，对数据的改变就是永久的 2.数据库隔离级别，每个级别会引发什么问题，mysql默认是哪个级别 脏读：事务B读取事务A还没有提交的数据 不可重复读：两次事务读的数据不一致 幻读:事务A修改了数据，事务B也修改了数据，这时在事务A看...
mysql 索引和order by,在where子句和order by子句之间的MySQL索引
weixin_42399388的博客
02-18 340
My table structure is something like below:CREATE TABLE test (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,field_1 VARCHAR(60) NOT NULL,field_2 INT(10) UNSIGNED NULL,field_3 INT(10) UNSIGNED NULL,field...
MySQL 基础知识+索引相关
09-07 351
1.0基础知识 一张数据表中具有百万级的数据时，如何精确且快速的拿出其中某一条或多条记录成为了人们思考的问题。 　　 InnoDB存储引擎的出现让这个问题得到了很好的解决，InnoDB存储引擎是以索引来进行数据的组织，而索引在MySQL中也被称之为键，因此UNIQUE KEY，PRIMARY KEY约束字段会作为索引字段。 　　 当没有明确指出PRIMAY KEY时，InnoDB存储引擎会自动的创建一个6字节的隐藏主键用于组织数据，但是由于该主键是隐藏的所以对查询没有任何帮助。 　...
数据库技术:SQL语句优化教程：加快查询速度、建立索引及优化sql实例
while_int的博客
03-15 2314
怎么加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在where及order by 涉及的列上建立索引。 建立索引不是建的越多越好，原则是： 第一：一个表的索引不是越多越好，也没有一个具体的数字，根据以往的经验，一个表的索引最多不能超过6个，因为索引越多，对update和insert操作也会有性能的影响，涉及到索引的新建和重建操作。 第二：建立索引的方法论为： 多数查询经常使用的列； 很少进行修改操作的列； 索引需要建立在数据差异化大的列上 利用以上的基础我们讨论一下如何优化sql. 1
sql order by与索引之间的关系（where条件出现字段才有效）
weixin_41981080的博客
10-08 9537
ORDER BY 通常会有两种实现方法，一个是利用有序索引自动实现，也就是说利用有序索引的有序性就不再另做排序操作了。另一个是把结果选好之后再排序。 用有序索引这种，当然是最快的，不过有一些限制条件，来看下面的测试。 测试数据：student表有两个字段id ,sid ,id是主键。一共有20W条记录，id从1到200000,sid也是从1到200000的数据。 第一种情况 : order by的...
sql语句优化之一：尽量使用索引避免全表扫描
wendy432的专栏
08-26 1万+
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： selec
数据库面试题集
09-24
这份"数据库面试题集"涵盖了数据库理论、SQL语言、数据库设计、性能优化和事务管理等多个方面，对于准备数据库相关的面试或者提升自己的数据库技能都有极大帮助。 1. **数据库基础** - 数据库的类型：关系型数据库...
mysql面试题 MySQL面试题 数据库面试题 SQL面试题
最新发布
03-29
mysql, 面试题, 数据库, 数据管理, 数据库管理, 数据库设计, sql, 数据查询, 数据库优化, 数据库安全, 数据库备份, 数据库恢复, 数据库性能, 数据库索引, 数据库事务, 数据库存储引擎, 数据库连接池, 数据库分库分表...
SQL优化 总结 精简
开着奥迪卖小猪
06-30 984
索引： 　　考虑在 where 及 order by 涉及的列上建立索引 　　经常同时存取多列，且每列都含有重复值可考虑建立组合索引，且查询越频繁的字段放前面 　　按需使用聚集与非聚集索引，聚集不适合频繁更新、适合范围查询( > , =,和 order by、group by ，注意复合索引的顺序，选择性高的建议放前面 　　不要在数据选择性不高的字段建立索引 　　索引控制在6个以内为好 　　大字段
mysql索引笔记
weixin_30838921的博客
08-06 73
mysql索引 众所周知，索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引用的是B+树索引。 聚集索引 非聚集索引 　　该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。 　　分成普通索引，唯一索引，全文索引。 索引结构 　　BTree索引　　Hash...
数据库常见索引解析（B树，B-树，B+树，B*树，位图索引，Hash索引）
热门推荐
wl044090432的博客
12-01 2万+
B树        即二叉搜索树：        1.所有非叶子结点至多拥有两个儿子（Left和Right）；        2.所有结点存储一个关键字；        3.非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；        如：                B树的搜索，从根结点开始，如果查询的关键字与结点的关键字相等，那么就命中；否则，
oracleB树索引和位图索引使用探讨
chunchong的专栏
11-26 713
前言 众所周知建立索引是为了提高数据库查询效率。正解的索引确实能够数倍的提高数据库查询效率，但一个错误的索引将会把数据库拖慢，甚至拖死。 本文意在探讨如何选择索引类型。 正文 Oracle常用的有两种索引类型：B树索引和位图索引。 一、      B树索引 B树索引：B树索引是最常用的索引，它的存储结构类似于书的目录索引结构，有分支节点和叶子节点，分支节点相当于书的大目录，叶子节点相当...
mysql 优化建议_Mysql语句优化建议
weixin_42499335的博客
02-17 124
一、建立索引1)考虑在 where 及 order by 涉及的列上建立索引2)对于模糊查询， 建立全文索引3)对于多主键查询，建立组合索引二、避免陷阱然而，一些情况下可能使索引无效：1)在 where 子句中对字段进行表达式操作2)在 where 子句中使用 or 来连接条件，如：select id from t where num=10 or num=20可以改为select id from ...
记录数过亿条的表数据维护-数据删除
weixin_30482181的博客
02-04 158
　　对一些big table，由于数据删除的时候时间会很长，事务很大，所需的undo段将会比较大，未提交的话，undo段数据会受到保护，这将影响其它事务的操作-执行时间会变长或者挂起，所以删除大表数据的时候尽量将大事务切分成小事务去做，下面的pl/sql 匿名块是删除表数据时每10万行一提交。 1 declare 2 3 cursor cur is select rowi...
当Select语句有Where的范围条件以及Order By时的索引字段顺序问题
老紫竹的专栏
07-27 4023
比如查询最近7天，阅读数量最大的20个帖子。 select top 20 * from t_article where datetime>DATEADD(day,-7, getdate()) order by countView desc  建立索引时，必须将 countView设置在前面，datetime 设置在后面索引才有效。以上测试在 SQL Server 2005 下测
索引失效的情况及优化策略
weixin_42713690的博客
05-15 485
MYSQL避免全表扫描，首先考虑在where及order by涉及的列上建立索引。 导致引擎放弃使用索引而进行全表扫描的情况： 1、where字句对字段进行null判断。 可以设置默认值为0，确保列中无null值。 2、where字句使用!= 或<>操作符。 3、where子句使用OR来连接条件。 可以使用union all代替。 4、where字句中in 和not in 要慎用 5、where字句中like尽量以"常量"开头，不要以'%'开头。 ...
Oracle数据库面试试题解析
"这篇文档包含了Oracle数据库相关的面试试题，涵盖了SQL操作、事务概念以及触发器等核心知识点。" 1. **SQL字符串操作函数** - `ASCII()` 函数：返回字符串的第一个字符对应的ASCII码值。 - `CHR()` 函数：根据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值