今天也要加油啊江同学

复习日记——数据库篇

复习日记——数据库篇

该篇章以MySQL架构,存储引擎,数据类型,索引,MySQL查询,MySQL事务,MySQL锁机制,主从复制,其它几个部分来复习面试常问问题。

 

MySQL架构篇

【1】MySQL的架构是怎么样的?

1. 连接层:最上层是一些客户端和连接服务。主要完成一些类似于连接处理,授权认证,及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2. 服务层:第二层服务层,主要完成大部分的核心服务功能,包括查询解析,分析,优化,缓存,以及所有的内置函数。所有跨存储引擎的功能也都在这一层实现,包括触发器,存储过程,视图等。

3. 引擎层:第三层存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。

4. 存储层:第四层为数据存储层,主要是将数据存储在运行于该设备的文件系统之上,并完成与存储引擎的交互。

【2】MySQL的查询流程?一条SQL语句在MySQL中如何执行的?

        客户端请求——>连接器(验证用户身份,给予权限)——>查询缓存(存在缓存则直接返回,不存在则执行后续操作)——>分析器(对SQL进行词法分析和语法分析操作)——>优化器(主要对执行的SQL优化选择最优的执行方案方法)——>执行器(执行时会先看用户是否有执行权限,有才去使用这个引擎提供的接口)——>去引擎层获取数据返回(如果开启查询缓存则会缓存查询结构)

【3】什么是视图和存储过程?

        视图可以看作虚拟表或者存储查询,存储的是sql语句,不占实际空间

        存储过程相当于数据库中的程序,让数据库自己解决复杂的,用一般sql不能实现的功能。

 

 

【存储引擎篇】

【1】InnoDB?和MyISAM的区别

InnoDBMySQL目前默认的存储引擎,物理文件结构为:

1.  .frm文件,存储与表相关的元数据信息

2. .ibd文件或.ibdata文件。两者都是存放InnoDB数据的文件,之所以有两种形式,是因为InnoDB的数据存储方式可以通过配置决定是使用共享表空间存放存储数据还是用独享表空间存放存储数据。

MyISAM的区别:

1. InnoDB支持事务, MyISAM不支持(重要)

2. InnoDB支持外键,而MyISAM不支持,对一个包含外键的InnoDB表转为MyISAM会失败。

3. InnoDB是聚簇索引,MyISAM是非聚簇索引。聚簇索引的文件存放在主键索引的叶子结点上,因此InnoDB必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键过大,其它索引也都会很大。而MyISAM是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB最小的锁粒度是行锁,MyISAM最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限(重要)

5. InnDB不保存表的具体行数,MyISAM保存,因此select count(*) from tableMyISAM更快

        一张表,里面有ID自增主键,当insert17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID18还是15 ?如果表的类型是MyISAM,那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中,重启MySQL自增主键的最大ID也不会丢失;

        如果表的类型是InnoDB,那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中,所以重启数据库或对表进行OPTION操作,都会导致最大ID丢失。

        为什么InnoDB不保存总行数,这跟InnDB的事务特性有关。由于多版本并发控制(MVCC)的原因,InnoDB应该返回多少行也是不确定的。

 

 

【数据类型】

【1】MySQL的数据类型有哪些?

主要包括以下五大类:

1. 整数类型:BITBOOLTINY INTSMALL INTMEDIUM INTINTBIG INT

2. 浮点数类型:FLOATDOUBLEDECIMAL

3. 字符串类型:CHARVARCHARTINY TEXTTEXTMEDIUM TEXTLONGTEXTTINY BLOBBLOBMEDIUM BLOBLONG BLOB

4. 日期类型:DateDateTimeTimeStampTimeYear

5. 其它数据类型:BINARYVARBINARYENUMSETGeometryPointMultiPointLineStringMultiLineStringPolygonGeometryCollection

【2】 char和varchar的区别?

相同点:

1. char(n)varchar(n)中的n都代表字符的个数(不同编码中,英文和中文所占用的字节不同,ASCII里,英文是1个字符,中文2个字符;UTF-8编码中,英文是1个字符,中文3个字符;Unicode编码中,英文2个字符,中文2个字符)

2. 超过charvarchar最大长度n的限制后,字符串会被截断

不同点:

1. char会按照规定的长度分配存储空间,varchar会根据实际的数据分配最终的存储空间(实际字符占用字节空间+1)。因为varchar保存数据时出了保存字符串外还会加一个字节来记录长度(如果列声明长度大于255则使用2个字节来保存长度)

2. 能存储的最大空间限制不一样: char的存储上限为255字节

3. char在存储时会截断尾部的空格,而varchar不会。

char适合存储很短的,一般固定长度的字符串。例如,char非常适合存储密码的MD5值;同时对于非常短的列,char在存储空间上也更有效率。

 

 

【索引】

【1】对MySQL索引的理解?

官方定义:索引(index)是帮助MySQL高效获取数据的数据结构。因此索引的本质是:数据结构.

索引本身也很大,不可能全部存储在内存中,一般以索引文件的形式存储在磁盘上

平时说的索引一般指B+树。此外还有Hash索引。

MySQL创建表时会自动为主键添加索引。

索引的优点:

提高数据检索效率,降低数据库IO成本

降低数据排序的成本,降低CPU的消耗

索引的缺点:

索引也是一张表,保存了主键和索引字段,并指向实体表的记录,所以也需要占用内存。

虽然索引大大提高了查询速度,同时却会降低更新表的速度,因为更新表时,MySQL不仅要保存数据,还要保存索引文件。每次更新添加了索引列的字段,都会调整因为更新锁带来的键值变化后的索引信息。

【2】索引分类

数据结构角度:

B+树索引

Hash索引

Full-Text全文索引

R-Tree索引

物理存储角度:

聚集索引(clustered index)(定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引)

非聚集索引(non-clustered index),也叫辅助索引(secondary index)聚集索引和非聚集索引都是B+树结构

从逻辑角度:

主键索引

普通索引或单列索引

多列索引(最左前缀集合)

唯一索引或非唯一索引

空间索引

【3】为什么要使用B+树,不用B-树或者其它树?聚集索引和非聚集索引的区别是什么?

首先明白索引是在存储引擎层面实现的,而不是server层面,不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型,它们的实现和行为也可能有所差别。

MyISAMInnoDB都使用B+Tree的存储结构。

B-tree的每个节点不仅包含数据的key值,还有data值。而每一页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。

B+tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子结点只存储key值信息,这样大大加大每个节点存储的key值数量,降低B+Tree高度。

B+TreeB-Tree的几点不同:

  1. 非叶子节点只存储键值信息
  2. 所有叶子节点之间都有一个链指针(双向且链表头尾循环指向)
  3. 数据记录都存放在叶子结点中

 

B+Tree的性质:

  1. I/O次数取决于b+树的高度hb+树通过把真实数据放在叶子结点而不是内部节点降低树的高度。
  2. b+树的数据项是复合的数据结构,比如(name, age, sex)的时候,b+树按照从左到右的顺序来建立搜索树,比如当(张三,20, F)这样的数据来检索的时候,b+树会优先比较name来确定下一步搜索方向,如果name相同再依次比较agesex,最后得到检索的数据;但是当(20, F)这样的没有name 的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树时name就是第一个比较因子,必须要先按照name来搜索才能知道下一步去哪查询。比如当(张三,F)这样的数据来检索时,b+树可以通过name确定搜索方向,但age的缺失使得它只能在张三的数据中匹配性别是F的数据。(最左匹配原则)

MyISAM的索引和数据文件是分离的,索引结构中叶子结点的数据域存放的是数据记录的地址而不是数据记录,这称作非聚簇索引MyISAM的主索引与辅助索引区别不大,只是主索引不能有重复的关键字。

InnoDB索引结构中叶子结点的数据域存放的就是实际的数据记录。(对于主索引,此处会存放表中所有的数据记录。对于辅助索引,此处会引用主键,检索的时候通过主键到主键索引中找到对应数据行)。或者说,InnoDB的数据文件本身就是主键索引文件,这样的索引被称为聚簇索引,一个表只能有一个聚簇索引。

 

主键索引:

 

辅助(非主键)索引:

先通过辅助索引到达叶子结点获取对应的主键;再使用主键在主索引上进行对应的检索操作。这也就是所谓的回表查询

为什么不用Hash:不支持区间查找,不支持多列联合索引的最左匹配规则

【4】哪些情况需要索引,哪些情况不需要

哪些情况需要:

1. 主键自动建立唯一索引

2. 频繁作为查询条件的字段

3. 查询中与其他表关联的字段,外键关系建立索引

4. 单键/组合索引的选择问题,高并发下倾向创建组合索引

5. 查询中排序的字段,排序字段通过索引访问大幅提高排序速度

6. 查询中统计或分组字段

哪些情况不要创建索引

1. 表记录太少

2. 经常增删改的表

3. 数据重复且分布均匀,只应该为最经常查询和最经常排序的数据列建立索引

4. 就频繁更新的字段不适合创建索引(加重IO负担)

5. Where里用不到的字段不创建索引

【MySQL查询】

【1】几种JOIN

 

【MySQL事务】

【1】事务的基本要素,以及隔离级别有哪些?

基本要素:ACID

A:原子性:要么都发生,要么都不发生

C:一致性:在事务开始前和事务结束后,数据库的完整性约束没有被破坏

I:隔离性:一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的

D:持久性:在事务完成以后,该事务对数据库所做的更该不会被回滚。

并发事务处理带来的问题:

1. 更新丢失。两个事务不知道彼此的存在

2. 脏读。事务A读取了B更新的数据然后B回滚

3. 不可重复读。 A多次读取同一数据,BA多次读取的过程中对数据进行了更新并提交,导致A多次读取时结果不一致

4. 幻读。A读取了几行数据,然后B插入了一些数据,之后查询时A就会发现多了一些原本不存在的记录。

幻读和不可重复读的区别:不可重复读重点在修改。幻读在新增或者删除。

并发事务处理带来的问题的解决方法:

1. 更新丢失通常是应该完全避免的,但防止更新丢失,除了数据库事务控制器解决外,还需要应用程序对要更新的数据加必要的锁,因此,防止更新丢失应该是应用的责任。

2. 脏读,不可重复读和幻读,都是数据库读一致性的问题,必须由数据库提供一定的事务隔离机制来解决。

加锁

多版本并发控制(MVCC

 

事务隔离级别

1. 读未提交 READ-UNCOMMITTED 允许读取尚未提交的数据变更

2. 读已提交 READ-COMMITTED。 允许读取并发事务已经提交的数据,可以阻止脏读

3. 可重复读 REPEATABLE-READ。 对同一字段的多次读取结果都是一致的,除非数据被该事务本身修改,可以阻止脏读和不可重复读

4. 可串行化SERIALIZABLE。 完全服从ACID,所有事务依次逐个执行,可以防止脏读,不可重复读以及幻读。

 

MySQL默认的隔离级别是可重复读。同时,InnoDB在可重复读级别下使用的是Next-Key Lock算法,因此可以避免幻读的产生。

 

【2MVCC多版本并发控制

MySQL的大多数事务型存储引擎实现都不是简单的行级锁。基于提高并发性考虑,一般都同时实现了多版本并发控制(MVCC

典型的MVCC实现方式分为乐观和悲观并发控制两种。

InnDBMVCC,是通过在每行记录后面保存两个隐藏的列实现。一个列保存了行的创建时间,一个保存行的过期时间(删除时间)。这里存储的不是真正的时间,而是系统版本号(system version number)。每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。

MVCC只在读提交和可重复读两种隔离级别下工作。

【3】事务是如何通过日志来实现的

事务的隔离性通过锁实现,而事务的原子性,一致性和持久性则通过事务日志实现。

事务日志包括redo undo两种。

 

【MySQL锁机制】

【1】MySQL有哪几种锁?什么是乐观锁和悲观锁?

读锁(共享锁):针对同一份数据,多个读操作可以同时进行,不会相互影响

写锁(排他锁):当前写操作没有完成前,它会阻断其它写锁和读锁

加锁机制:

乐观锁与悲观锁是两种并发控制的思想,可用于解决丢失更新问题。

乐观锁会乐观地假定大概率不会发生并发更新冲突;访问,处理数据过程不加锁,只在更新数据时再根据版本号或时间戳判断是否有冲突。有则处理,无则提交事务。用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式。

悲观锁会悲观地假定大概率会发生并发更新冲突;访问,处理数据前就加派他锁,在整个数据处理过程中锁定数据,事务提交或回滚后才释放锁。与乐观锁相对应,悲观锁是由数据库自己实现了的,要用的时候直接调用数据库的相关语句即可。

【主从复制】

【1】复制的基本原理

slavemaster中读取binlog来进行数据同步

1. master将改变记录到二进制日志(binlog)中。

2. slavemasterbinlog拷贝到它的中继日志(relay log)中

3. slave重做中继日志中的事件,将改变应用到自己的数据库中。复制过程是异步且串行化的。

【2】如何保证复制成功?master挂掉怎么办?

【其它】

【1】数据库三范式

第一范式:数据库中的字段都是单一属性的,不可再分。

第二范式:数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖(属性完全依赖于主键,且不能只与主键的某一部分相关)。

第三范式:在第二范式的基础上,数据表中不存在非关键字段对任一候选关键字段的传递函数依赖(属性不依赖于其它非主属性,属性直接依赖于主键;也就是说不存在传递关系)。

 

【2】百万级别或以上的数据是如何删除的

删除数据的速度和创建的索引数量是成正比的。

因此:

1. 先删索引

2. 删除无用数据

3. 删除完成后重新创建索引

4. 与之前的直接删除相比快得多,更不用说万一删除中断,一切都会回滚。

xinyu