数据库专家学习进阶资料

https://storage.iamsjy.com/E-books/%E6%95%B0%E6%8D%AE%E5%BA%93/

一、mysql数据库存储引擎

1.1 innoDB存储引擎

　　是目前最常用的，支持事务，没有全文索引（1.2版本开始支持），在互联网大厂主要使用，具备高可用性，高性能已经高扩展性

1.2 MyISAM存储引擎

　　不支持事务和表锁设计，但支持全文索引。主要用于非etl场景，如报表查询等，缓冲池只缓存索引文件，不缓冲数据文件。5.0版本开始，支持256TB的表单数据。

　　由MYD和MYI组成。MYD存放数据文件，MYI存放索引文件。

1.3 NDB存储引擎

　　集群式存储引擎。 起结构是share nothing的集群架构，因此能提供更高的可用性。

       特点是：数据全部存在内存中（5.1版本开始可以将非索引数据放在磁盘中），因此主键查询极快，并且通过添加NDB数据存储节点（Data Node）可以线性提高数据库性能，是高可用，高性能的集群系统。

      有一点不同：NDB存储引擎的连接操作（JOIN）是在mysql 数据库层完成的，而不是存储引擎完成的。这意味着，复杂的连接操作需要巨大的网络开销，因此查询速度很慢。

1.4 Memory存储引擎

     Memory存储引擎（之前称为HEAP存储引擎）将表中数据存在内存中，如果数据库重启或者发生崩溃，表中数据全部消失。适合存储临时表，以及数据仓库中的维度表。

     默认使用哈希索引，而不是B+树索引，但可以手动配置B+树。

     只支持表锁，并发性较差，不支持TEXT和BLOB列类型。

     mysql数据库使用memory存储引擎作为临时表来存放查询的中间结果集。如果中间结果集大于Memory存储引擎表的容量设置时，又或者中间结果含义TEXT和BLOB列类型字段，则mysql数据库会把中间结果集转换到MyISAM存储引擎表而存放到磁盘中。之前提到的MyISAM不缓存数据文件，因此这时产生的临时表的性能对于查询会有损耗。

1.5  Archive存储引擎

     只支持Insert和select操作，5.1版本开始支持索引。archive存储引擎使用zlib算法将数据行进行压缩后存储，压缩比一般可以达到1:10。正如其名，主要用于存储归档数据，如日志信息。

     有行锁，支持高并发插入。但是本身并不是事务安全的存储引擎。设计目标主要是提供高速插入和压缩的功能。

1.6 Federated存储引擎

     并不存放数据，只是指向一台远程mysql数据库服务器上的表。类似于oracle的透明网关，当前Federated存储引擎只支持mysql数据库表，不支持异构数据库表。

1.7 Maria存储引擎

　　maria存储引擎是新开发的引擎，设计目标主要是用来取代原有的MyISAM存储引擎。可以看成是MyISAM存储引擎的后续版本。

　　特点是：支持缓存数据和索引文件，应用了行锁设计，提供了MVCC功能，支持事务和非事务安全的的选项，以及BlOB字符类型的处理性能。

 二、mysql主从复制

mysql主从复制原理就是主库创建一个专门用于给从库拉取binlog的账号，并且给这个账号授权，让他可以拉取哪个DB的那个表的binlog，具体的授权SQL是：

grant repliacation slave on xx.xx to username@ip identify by 'password'

这样从库就能登陆主库拉取binlog，那拉取binlog就得知道从哪个binlog的哪个位点拉取，现有的有两个方案：fileName + position 还有就是通过gtid自动找点。

什么是GTID？原理？

https://www.cnblogs.com/ZhuChangwu/p/13040214.html

同步、半同步、异步复制说的是 从库在主库上拉取binlog日志的模式。

同步：

主库写redolog 事物处于prepare状态、主库写binlog，然后从库拉取binlog去回放，从库回放成功后返回给主库ack确认，所有的从库都完成回放后主库提交事物。这样是可以保证主从数据一致的但是缺点就是速度太慢了。

半同步：

主库写redolog 事物处于prepare状态、主库写binlog，然后从库拉取binlog后返回给主库ack，在众多从库中只要收到一个ack主库就提交事物

异步复制：

主库根本不管从库有没有拉取回放binlog，直接写redo、binlog、然后提交事物

首先不允许出现主从数据不一致的情况：如果主从不一致对业务来说是有损的，一旦发生主从数据不一致的情况，从库就会出现断开连接的可能。

 三、索引，聚簇索引及B+树

3.1 B-树就是B树；B树与B+树区别是：

B树每个节点存储key和data，每个节点key的数量比分支数量少1个；

B+树叶子节点才存储dataj，也存key,其他节点只存key。每个节点key的数量比分支数量相等；

一次聊个痛快，B-，B+树的特点和使用场景

3.2  主键索引，唯一索引，普通索引的区别

　　唯一索引

3.3 索引设计的原则：

1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列。

2. 基数较小的类，索引效果较差，没有必要在此列建立索引。

3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。

4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

 

四、事务

4.1数据库隔离级别:

读未提交：在事务A中读取到了事务B中未提交的数据， 也叫做脏读。

读已提交（oracle默认）：读已提交， 说白了， 事务A先开启， 然后事务B再开启， 然后事务B commit一个事务操作， 修改数据 ， 那么这个修改是能被事务A读取到的， 这就叫做读已提交， 也是所谓的不可重复读，(因为重复读之后， 数据可能会发生变化)。

可重复读（mysql默认）：事务A开启后， 无论读取多少次， 得到的结果都和第一次得到的结果是一样的， 但是如果事务B在事务A第一次读取的范围内插入了一条数据的话， 会发生幻读， 两次读取结果又不一致了， Mysql的InnoDB引擎通过多版本并发控制MVCC解决了这个问题。

序列化：可串行化， 最高的事务隔离级别， 到是也是效率最低的事务隔离级别

4.2事务的四种特性：

1. Atomic 原子性: 事务不能被分割， 要么都做， 要么都不做。

2. Consistency 一致性: 可以用转账的例子解释一致性。

3. Isolation 隔离性 : 不同的事务， 彼此隔离， 互不干扰。

4. Durability 持久性: 也叫做用就行， 事务一旦被提交， 对数据库做出的修改将被持久化 。

 五、数据库范式

• 第一范式（1NF）：

  • 定义：所有字段的值都是不可再分的原子值。

  • 要求：每个列中的数据都是单一值，表中的每一行都是唯一的。

• 第二范式（2NF）：

  • 定义：在满足第一范式的基础上，所有非主键列完全依赖于主键。

  • 要求：表中不存在部分依赖，即每一个非主键列必须依赖于整个主键，而不是主键的一部分。

• 第三范式（3NF）：

  • 定义：在满足第二范式的基础上，所有非主键列直接依赖于主键，不存在传递依赖。

  • 要求：每个非主键列都直接依赖于主键，而不是通过其他非主键列间接依赖于主键。

• BC范式（Boyce-Codd Normal Form，BCNF）：

  • 定义：在满足第三范式的基础上，如果表中有候选键，所有列都必须依赖于候选键。

  • 要求：每个候选键的所有非主键属性都必须依赖于候选键。

• 第四范式（4NF）：

  • 定义：在满足BC范式的基础上，不存在多值依赖。

  • 要求：表中的每个多值依赖都必须被分解。

• 第五范式（5NF）：

  • 定义：在满足第四范式的基础上，不存在任何多余的连接依赖。

  • 要求：表中的任何连接依赖都必须被分解

 

六 .MySQL Explain各字段含义

 

字段名	含义
id	选择标识符
select_type	表示查询的类型
table	输出结果集的表
partitions	匹配的分区
type	表示表的连接类型
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
ref	列与索引的比较
rows	扫描出的行数(估算的行数)
filtered	按表条件过滤的行百分比
Extra	执行情况的描述和说明

 

MySQL：连Explain的Type类型都没搞清楚，怎敢说精通SQL优化？

 

七、sql 执行模式

 

数据库面试简答、30道高频面试题