数据库笔记

发表于 更新于 分类于 ，

关系型数据库和非关系型数据库的区别

1.关系型数据库：指用关系模型来组织数据信息的数据库
关系模型指的是二维表格模型，而一个关系型数据库便是由二维表以及表之间的关系所构成的一个数据集合。
2.关系型数据库的优势：

  • 便于理解：二维表构造非常贴近逻辑；
  • 应用方便：支持通用的SQL（结构化查询语言）语句；
  • 易于维护：全部由表结构组成，文件格式一致；
  • 复杂操作：可以用SQL句子多个表之间做非常繁杂的查询；
  • 事务管理：促使针对安全性性能很高的数据信息浏览规定得到完成。
    3.关系型数据库存在的不足
  • 读写性能差，尤其是海量信息的效率高读写能力；
  • 固定不动的表构造，灵便度稍欠；
  • 高并发读写时，硬盘I/O存在瓶颈；
  • 可扩展性不足，不像web server和app server那样简单的添- 加硬件和服务节点来拓展性能和负荷工作能力。

4.非关系型数据库概念介绍
非关系型数据库：指非关系型的，分布式系统的，且一般不确保遵照ACID标准的数据储存系统。
非关系型数据库算是一种数据结构化储存的集合，可以是文档或键值对等。
5.非关系型数据库的类型：

  • 键值储存数据库
  • 列储存数据库
  • 文档型数据库
  • 图数据库
    6.非关系型数据库的优点
  • 格式灵活：数据存储格式非常多样，应用领域广泛，而关系型数据库则只适用基础的关系模型。
  • 性能优越：NOSQL是根据键值对的，不用历经SQL层的分析，因此 性能非常高。
  • 可扩展性：基于键值对，数据之间耦合度极低，因此容易水平扩展。
  • 低成本：非关系型数据库部署简易，且大部分可以开源使用。
    6.非关系型数据库的不足：
  • 不支持sql，学习和运用成本比较高；
  • 无事务处理机制；
  • 数据结构导致复杂查询不容易实现。

为什么使用索引

  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 帮助服务器避免排序和临时表
  • 将随机IO变为顺序IO。

执行一条SQL查询语句期间发生了什么

1.连接器：建立连接，管理连接、校验用户身份；
2.查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
3.解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
4.执行 SQL：执行 SQL 共有三个阶段：

  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

MyISAM和InnoDB实现B树索引方式的区别是什么？

1.MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引”
2.InnoDB，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，这被称为“聚簇索引”或者聚集索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。
在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

Innodb为什么要用自增id作为主键？

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面。

你了解MySQL的内部构造吗？一般可以分为哪两个部分？

1.服务层包括连接器、查询缓存、分析器、优化器、执行器等
2.存储引擎层负责数据的存储和提取

说一说Drop、Delete与Truncate的共同点和区别

Drop、Delete、Truncate都表示删除，但是三者有一些差别： Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除，会触发这个表上所有的delete触发器。 Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小。 Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。
因此，在不再需要一张表的时候，用Drop；在想删除部分数据行时候，用Delete；在保留表而删除所有数据的时候用Truncate。

文件索引和数据库索引为什么使用B+树?

1.文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适。数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入
2.最重要的是，B+树还有一个最大的好处：方便扫库。B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因。
3.B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针(红色部分)，因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一块盘中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了

数据库为什么要进行分库和分表呢？都放在一个库或者一张表中不可以吗

通过分表，可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同的表上的数据量少了，起到提高查询性能，缩短查询时间的作用

  • 水平分表：取模分表就属于随机分表，而时间维度分表则属于连续分表。 如何设计好垂直拆分，我的建议：将不常用的字段单独拆分到另外一张扩展表. 将大文本的字段单独拆分到另外一张扩展表, 将不经常修改的字段放在同一张表中，将经常改变的字段放在另一张表中。 对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。
  • 库内分表，仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘 IO、网络带宽等。

索引失效的情况

1.对索引使用左或者左右模糊匹配：因为B+树是按照索引值有序排列的，只能根据前缀进行比较
2.对索引使用函数：不过可以针对函数计算后的值建立一个索引，从而使得该索引的值是函数计算后的值，就可以通过扫描索引来查询数据
3.对索引使用表达式计算
4.对索引隐式类型转换
5.联合索引非最左匹配
6.WHEREA子句中的OR

数据库事务是什么，都有哪些操作？

事务即为数据库的操作序列，要么完全执行，要么完全不执行。满足ACID属性。
A:原子性：要么全做，要么全不做
C：一致性：即事务操作前后满足完整性约束，比如A给B转账，完成后，A的少了200元，B的多了200元
I：隔离性：事务可以是并发的，隔离性可以保证多个事务并发执行由于使用相同的数据而互不干扰。每个事务都有自己的数据空间。
D：持久性：事务完成后，对数据的修改是永久的，即使系统故障也不会丢失。
事务的操作：开启事务，回滚，提交。其中回滚在数据没有提交之前可以回滚到之前的版本。

脏读，幻读，不可重复读的概念

1.脏读：一个事务读到了另一个未提交事务修改过的数据（因为此时第二个事务有可能发生回滚操作）
2.不可重复读：在一个事务内多次读取同一个数据，出现了两次读到的数据不一样的情况（比如A在读一个数据时，B修改了这个数据，当A再次读这个数据时，读到的数据和上一次不一样）
3.幻读：在一个事务内多次查询某个符合查询条件的记录数量，前后两次记录数量不一样（比如数据增加或者被删除）

三者的严重程度：脏读 > 不可重复读 > 幻读

数据库隔离级别

1.读未提交：一个事务还没提交时，其所做的变更就可以被其它事务看到（会发生脏读，不可重复读和幻读）
2.读提交：一个事务提交后，它所做的变更才能被其它事务看到（会发生不可重复读和幻读）
3.可重复读：一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据一样（InnoDB默认的隔离级别）（会发生幻读）
4.串行化：会对记录加上读写锁，后访问的事务必须等前面的事务处理完才能继续执行。

MYSQL有哪些锁

1.全局锁：使得整个数据库处于只读状态
2.表级锁：

  • 表锁：分为读锁（共享锁）和写锁（独占锁）
  • 元数据锁（MDL)：对数据库表进行操作时，会自动的给这个表加上MDL，在事务提交后释放。
  • 意向锁：

MySQL如何实现事务提交和回滚

1.实现数据恢复：redo log
redo log分为两部分：

  • 内存中的redo log Buffer是日志缓冲区，这部分数据是容易丢失的
  • 磁盘上的redo log file是日志文件，这部分数据已经持久化到磁盘，不容易丢失
    在MySQL中可以自已控制log buffer刷新到log file中的频率，通过innodb_flush_log_at_trx_commit参数可以设置事务提交时log buffer如何保存到log file中，innodb_flush_log_at_trx_commit参数有3个值(0、1、2)，表示三种不同的方式
  • 为1表示事务每次提交都会将log buffer写入到os buffer，并调用操作系统的fsync()方法将日志写入log file，这种方式的好处是就算MySQL崩溃也不会丢数据，redo log file保存了所有已提交事务的日志，MySQL重新启动后会通过redo log file进行恢复。但这种方式每次提交事务都会写入磁盘，IO性能较差
  • 为0表示事务提交时不会将log buffer写入到os buffer中，而是每秒写入os buffer然后调用fsync()方法将日志写入log file，这种方式在MySQL系统崩溃时会丢失大约1秒钟的数据
  • 为2表示事务每次提交仅将log buffer写入到os buffer中，然后每秒调用fsync()方法将日志写入log file，这种方式在MySQL崩溃时也会丢失大约1秒钟的数据

2.undo log
和redo log类似，也分为内存缓冲区和磁盘的日志文件，但是undo log记录的是相反的语句，当事务需要回滚的时候，可以从undo log中找到相应的内容进行回滚操作。

3.二者的区别
为了保证数据的持久性，数据库在执行SQL操作数据之前会先记录redo log和undo log
redo log是重做日志，通常是物理日志，记录的是物理数据页的修改，它用来恢复提交后的物理数据页
undo log是回滚日志，用来回滚行记录到某个版本，undo log一般是逻辑日志，根据行的数据变化进行记录
redo/undo log都是写先写到日志缓冲区，再通过缓冲区写到磁盘日志文件中进行持久化保存
undo日志还有一个用途就是用来控制数据的多版本（MVCC）