秋招 面试 数据库

数据库基础知识

基础是最重要的

Posted by shmily on August 22, 2019

##索引 什么是索引：

  • 是一种快速查询表中内容的机制，类似于新华字典的目录
  • 运用在表中某个字段上，但是存储时，独立于表之外

索引表把数据变成是有序的

索引特点

索引的特点

（1）索引一旦建立,Oracle管理系统会对其进行自动维护, 而且由Oracle管理系统决定何时使用索引

（2）用户不用在查询语句中指定使用哪个索引

（3）在定义primary key或unique约束后系统自动在相应的列上创建索引

（4）用户也能按自己的需求，对指定单个字段或多个字段，添加索引

需要注意的是：Oracle是自动帮我们管理索引的，并且如果我们指定了primary key或者unique约束，系统会自动在对应的列上创建索引..

什么时候【要】创建索引

（1）表经常进行 SELECT 操作
（2）表很大(记录超多)，记录内容分布范围很广
（3）列名经常在 WHERE 子句或连接条件中出现

什么时候【不要】创建索引

（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中

###索引优缺点：

索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引)
唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
索引需要占物理和数据空间

###索引分类：

唯一索引：唯一索引不允许两行具有相同的索引值 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

深入理解索引可参考：

kb.cnblogs.com/page/45712/ www.cnblogs.com/drizzlewith…

三个范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段x → 非关键字段y

首先要明确的是：满足着第三范式，那么就一定满足第二范式、满足着第二范式就一定满足第一范式 第一范式：字段是最小的的单元不可再分

学生信息组成学生信息表，有年龄、性别、学号等信息组成。这些字段都不可再分，所以它是满足第一范式的

第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键。

其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的 学号为1024的同学，姓名为Java3y，年龄是22岁。姓名和年龄字段都依赖着学号主键。

第三范式：满足第二范式，非主键外的所有字段必须互不依赖

就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖 比如，我们大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那我们能不能在学生信息表添加系编号，系主任，系简介，系架构字段呢？不行的，因为这样就冗余了，非主键外的字段形成了依赖关系(依赖到学生信息表了)！正确的做法是：学生表就只能增加一个系编号字段。

数据库存储引擎的比较

数据库的存储引擎是对 表 进行某种设置，存储引擎决定该表的数据存数方式，数据更新方式，数据查询性能以及是否支持索引，事务处理性能等。MySQL常用的几种引擎有：MyISAM，InnoDB，MEMORY、CSV、BLACKHOLE、ARCHIVE、PERFORMANCE_SCHEMA、Merge等

MyISAM

MyISAM是MySQL默认的引擎(MySQL从5.5.8版本开始，innoDB存储引擎是默认的存储引擎)，使用一种表格锁定的机制来优化多个并发的读写操作。需要经常运行optimize table命令，来恢复被更新机制所浪费的空间，否则碎片也会随之增加，最终影响数据访问性能。

对事务完整性没有要求或者以select，insert为主的应用基本可以用这个引擎来创建表
        支持3种不同的存储格式，分别是：静态表，动态表，压缩表
        优点：
1. MyISAM表是独立于操作系统的，这说明可以轻松地将其从windows服务器移植到linux服务器。
2. MyISAM存储引擎在查询大量数据时非常迅速，这是它最突出的优点（为什么会非常迅速）
3. 另外进行大批量插入操作时执行速度也比较快
4. 单独保存了表的数据
        缺点：
1. MyISAM表没有提供对数据库事务的支持
2. 不支持行级锁和外键约束
3. 不适合用于经常update的表，效率低（为什么？）
  • MySQL是一个可移植的数据库，几乎在所有的系统上都能运行
  • 数据库实例：MySQL数据库由后台以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。数据库实例才是真正用于操作数据库文件的。
  • 数据库：物理操作系统文件或其他形式文件类型的集合，一个一个的 二进制文件组成。

  • MySQL数据库实例在系统上表现就是一个 进程。

          ps -ef | grep mysql    命令可以来观察启动后的进程情况（这个命令实际就是查看当前进程，能看到的进程即时mysql的一个实例）
  • 当启动实例的时候，MySQL数据库会去读取配置文件，根据配置文件的参数来启动数据库实例。在MySQL数据库中，可以没有配置文件，在这种情况下，MySQL会按照编译时的默认参数设置启动实例（mysql —help grep my.cnf查看MySQL数据库实例启动时，会在哪些位置查找配置文件），MySQL是按照/etc/my.conf—>/etc/mysql/my.cnf—>/usr/local/mysql/etc/my.cnf—>~/.my.cnf的顺序读取配置文件，MySQL会以最后一个配置文件中的参数为准。
  • 通常数据库的权限为mysql：mysql

  • 存储引擎是基于表的，而不是基于数据库的。

      格式文件 ————————————————————————————mytable.frm
  数据文件 ————————————————————————————mytable.MYD
  索引文件 ————————————————————————————mytable.MYI

最大的特点！！！！可以转换为压缩只读表来节省空间 适合大量数据读而少量数据更新的混合操作，如果查询中包含较多的数据更新操作，应该使用InnoDB，其行级锁机制和多版本的支持为读取数据和更新的混合提供了良好的并发机制

InnoDB

  • 支持 事务，其设计主要面向在线事务处理的应用，其特点是行锁设计，支持外键，默认读取操作不会产生锁
  • MySQL从5.5.8版本开始，innoDB存储引擎是默认的存储引擎
  • innoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了sql标准的四种隔离级别，默认为repeatable级别。同时使用一种被称为next-key locking的策略来避免幻读（phantom）现象的产生
  • innoDB存储引擎还提供了插入缓冲，二次写，自适应哈希索引，预读等高性能和高可用的功能
  • 对于表中数据的存储，innoDB引擎采用了聚集的方式
  • 每个InnoDB表在数据库目录中以.frm格式的文件表示
  • InnoDB表空间tablespace被用于存储标的内容
  • 提供一组来记录事务性活动的日志文件
  • 用commit（提交），savepoint以及rollback（回滚）支持事务处理
  • 提供全部ACID兼容
  • 在MYSQL服务器崩溃后提供自动恢复
  • 行级锁定

  • 支持外键及引用的完整性，包括级联更新和删除

    优点：

    1. 提供事务支持。
    2. 提供具有提交，回滚和系统崩溃修复能力的事务安全（但是对比myisam引擎，写的处理效率会差一点，并且会占用更多的磁盘空间以保留数据和索引）。
    3. 支持多版本并发控制（即MVCC Multi-Version Concurrency Control）的行级锁，由于锁粒度小，写操作和更新操作并发高、速度快。适合更新频繁的表。
    4. 支持自增长列？。
    5. 支持外键。
    6. 适合于大容量数据库系统，支持自动灾难恢复。

MEMORY(又称heap)

为了得到最快的响应时间，MEMORY引擎采用的逻辑存储介质是系统内存。表级锁机制，字段属性不能包含TEXT或BLOB字段，在数据库目录中，每个表均以.frm格式文件表示

优点：
1. 在内存中存储表数据具有很高的性能（表数据及索引都被存储在内存中）。
2. 同时支持散列索引和B树索引。 
缺点：
1. MySQL守护进程崩溃时，所有memory数据都会丢失（因为存储在内存中）。
2. 不能使用长度可变的数据类型（如BLOB和TEXT）,但varchar类型可用（varchar在MySQL内部被当做char类型）

以下几种情况适合使用Memory引擎： 1、目标数据较小，且被频繁访问。 2、表数据时临时的、且要求立即使用 3、Memory表数据丢、不会对应用服务产生实质影响 注：B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。

  • cvs（Comma-Separated Values逗号分隔值） 使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中与该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。
  • ARCHIVE：
    • ARCHIVEarchive存储引擎非常适合存储大量独立的、作为历史记录的数据。区别于InnoDB和MyISAM这两种引擎，ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些。
    • 引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。

数据库定义了四种隔离级别:

  • Read Uncommitted（未提交读）
  • Read Committed（提交读）
  • Repeatable Read（重复读）
  • Serializable（串行化）

这四种隔离依次升高，隔离级别越低，系统开销越小，并发支持性更高。

使用命令show variables like '%isolation%';可以查看当前数据库使用的隔离级别

在介绍四种隔离级别前先说明三种在使用事务时会出现的特殊(大部分情况会导致错误)读类型。

脏读
	脏读是指一个事务a修改或添加了一条数据，在a事务提交之前，另一个事务b读到了这条数据，并进行了操作。a如果回滚的话，脏读可能会导致b操作不存在的数据。

不可重复读
	在一次事务中的两次相同条件的查询不一致，比如a事务执行select count(*) from user where name='alex' 这时事务b插入了一条数据name=alex并提交这就会导致事务a第二次查询的时候多了一个计数
幻读
	幻读与不可重复读相反，事务a与事务b是完全隔离的，事务a执行'select id from user得到的id为1和2.
	这个时候b事务在user表中添加了一条数据id=3并提交,然后事务a想添加一条id为3的数据，如果id是唯一的，那a就会发现插不进去并提示dumplicate entry 3 for key id，原因是事务a阻止事务b的插入行为。

注意不要混淆不可重复读和幻读

  • Read Uncommited 【级别最低，什么都避免不了】

    在RU模式下，即使事务没有commit,在其他事务中仍可以读到未提交的数据。 RU是所有隔离级别中最低的一种。RU模式会导致 脏读

  • Read Commited 【可避免脏读】

    RC模式下，事务只能读取到已经commit的数据。 比如事务a在执行时，如果事务b没有提交,a是读不到b的数据的。如果b提交a便能读到b修改的数据。 RC可以避免脏读，但是会导致不可重复读。

大部分系统使用的是RC模式

  • Repeatable Read 【可避免脏读，不可重复读】

    RR（重复读）模式下，事务的多次Read不会受其他事务的影响（无论提交与否） 可以理解为RR模式下事务a在创建的时候获取了一次当前时刻数据的快照，快照不受其余事务的影响。 RR模式可能会导致幻读，因为无法感知其余事务，可能导致重复的插入。 mysql innoDB的RR模式可以一定程度避免幻读，该特性是通过 间隙锁（gap lock) 来实现的,间隙锁的原理可以参照这篇文章http://www.jianshu.com/p/bf86…

InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

  • Serializable 【可以避免脏读，不可重复读，虚读】

    串行化，顾名思义，是将所有读写操作完全串行。 串行化是所有隔离级别中最高的 每次读都需要获得表级共享锁，读写相互都会阻塞 串行化对资源的开销大，对并发支持不好，只在，某些场景下使用。

    数据库乐观锁悲观锁是什么

  • 确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。

  • 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

在查询完数据的时候就把事务锁起来，直到提交事务 实现方式：使用数据库中的锁机制

  • 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

在修改数据的时候把事务锁起来，通过version的方式来进行锁定 实现方式：使用version版本或者时间戳

数据库底层原理

  • mysql的表数据以及相关的索引文件存在低层磁盘上面
  • 不加索引查询非常慢，磁盘i/o查找
  • 索引是帮助mysql高校获取数据的排好序的数据结构
  • mysql的底层是b树，为什么用b树？（二叉树会变成单边增长，顺序排列没有索引和二叉树一样。红黑树也是一种 平衡二叉树（hashmap的低层），为什么不用红黑树？数据大的时候层数太深 2^n=100w 怎么改造红黑树使得数据库可以用 改成b树）
  • B树（左边小于根节点，右边大于根节点，然后load到内存，速度就会很快）
  • b+树（b树变种，优于b树）非叶子节点不存储data，data数据全都存到叶子节点，但是关键的点会有冗余的存储，只存储key，可以增大度，叶子节点不存储指针，顺序访问指针，提高区间访问的性能
  • MyISAM存储索引实现 都是形容数据库表的，不是数据库
  • MyISAM表 .frm表结构，myd 存数数据 myi数据的索引 主键索引和非主键索引都差不多
  • InnoDB .ibd数据和索引都存在b+树上面 表数据文件本身就是按b+树组织的的一个索引结构文件 聚集索引-叶节点包含了完整的
  • 为什么mysql页文件默认16k

  • 为什么innodb表必须有主键，并且推荐使用整形自增主键？

      正如我们上面介绍InnoDB存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索
  引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没
  有显式指定索引列的话，MySQL会从表中选择数据不重复的列建立索引，如果没有符合的列，
  则MySQL自动为InnoDB表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为
  整型。
  • uuid是什么
  • 联合索引的低层存储长什么样子

    ！！！那为什么推荐使用整型自增主键而不是选择UUID？

UUID是字符串，比整型消耗更多的存储空间；

在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速；

自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。 在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。

为什么非主键索引结构叶子节点存储的是主键值？

保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息（真实名称、手机号、收货地址···）修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。

https://juejin.im/post/5a9ca0d6518825555c1d1acd#heading-39

CATALOG
    FEATURED TAGS
    iOS 开发技巧 Obj-C Runtime Blog Mac 效率 终端 Git Xcode 秋招 面试 java kubernetes namespace 实习