数据库入门（2）--MySQL基础知识_检查是否组成超键-CSDN博客

本文链接：https://blog.csdn.net/Geffin/article/details/90611649

一条SQL查询语句是如何执行的？

MySQL分为Server层和存储引擎层两部分。

在这里插入图片描述

连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存：查询请求先访问缓存(key 是查询的语句，value是查询的结果)。命中直接返回。不推荐使用缓存，更新会把缓存清除(关闭缓存：参数 query_cache_type 设置成 DEMAND)。
分析器：对 SQL 语句做解析，判断sql是否正确。
优化器：决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序。
执行器：执行语句，先判断用户有无查询权限，使用表定义的存储引擎。

日志系统：一条SQL更新语句是如何执行的？

redo log
MySQL WAL 技术，先写日志，再写磁盘。保证掉电重启，数据不丢失（crash-safe）。

redo log 是 InnoDB 引擎特有的日志。

当记录更新时，Innodb 先记录 redo log 再更新内存，这时更新就算完成。引擎往往会在系统空闲时刷盘。
在这里插入图片描述

redo log 是实现了类似环形缓冲区，一个指针 write pos 是当前记录的位置，另一个指针 checkpoint 是当前要擦除的位置，write pos 和checkpoint 之间是空闲部分。如果 write pos 快追上 checkpoint 时，代表缓冲区快满了，需要暂停刷盘。

binlog（归档日志）

Server层日志。binlog 日志只能用于归档，没有crash-safe能力。

作用：

恢复：利用binlog日志恢复数据库数据
复制：主从同步
审计：通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击

redo log与binlog的不同点

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。binlog 文件到一定大小，会切换到下一个文件。

回滚段

rollback segment称为回滚段，每个回滚段中有1024个undo log segment。每个undo操作在记录的时候占用一个undo log segment。
undo log有两个作用：提供回滚和多个行版本控制(MVCC)。
在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

什么是存储过程？有哪些优缺点？

存储过程是一些预编译的SQL语句。更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

索引是什么？有什么作用以及优缺点？

索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构
索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

索引的常见模型

哈希表，不适合做区间搜索。
有序数组，只适合静态数据，插入麻烦。
二叉搜索树，N叉树。

什么是事务？

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

数据库的乐观锁和悲观锁是什么？

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

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

使用索引查询一定能提高查询的性能吗？为什么

通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.

索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:

基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
基于非唯一性索引的检索

drop、delete与truncate的区别：

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别的：

delete和truncate只删除表的数据不删除表的结构
速度,一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

drop、delete与truncate分别在什么场景之下使用？

不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

超键、候选键、主键、外键分别是什么？

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

什么是视图？以及视图的使用场景有哪些？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
只暴露部分字段给访问者，所以就建一个虚表，就是视图。
查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异

三个范式是指：

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

存储引擎简介

在这里插入图片描述

事务的隔离级别

读未提交：一个事务还未提交，其他事务就可以看到它做的更改
读已提交：大多数数据库系统的默认隔离级别，一个事务在提交之后，其他事务才能看到它的更改
可重复读：mysql默认隔离级别，一个事务是启动和提交之间读到数据是一致的，它在未提交时，其他事务无法看到它做的更改
串行化：对同一行记录，写会加写锁，读会加读锁，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完才能继续执行。

事务隔离的实现

单条记录在更新的时候都会记录一条回滚操作，记录上的最新值通过回滚操作都能回到它的上一个版本，同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）

为什么尽量不使用长事务？

长事务会意味着系统中存在很老的事务视图，在这个事务提交之前，回滚记录都会保留，这会导致占用大量存储空间，除此之外，长事务还占用锁资源，可能会拖垮整个库。

全局锁

在这里插入图片描述

表级锁

mysql中的表级锁有两种，一种就是表锁，另一种是元数据锁（MDL）：meta data lock。

元数据锁是隐式锁，当访问某一张表时，数据库自动加的锁。当对表增删改查时：加MDL读锁当更改表结构时：加MDL写锁。读锁之间不互斥，也就是多个线程可以同时对一个表进行增删改查，但是读写锁和写写锁之间互斥，也即是当更改表结构时要等待读锁或写锁释放后才能进行更改。

行锁

死锁和死锁检测

在这里插入图片描述
事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁，事务A和事务B互相等待对方的资源释放，这就造成了死锁。

死锁解决

超时释放设置超时时间，通过参数innodb_lock_wait_timeout设置，当超过这个时间之后将自动释放锁资源。默认是50s
死锁检测设置innodb_deadlock_detect为on来开启死锁检测，但它会造成额外的负担，每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

索引的种类

单值索引：一个索引只包含一个列，一个表可以有多个单值索引，创建索引的语法：

第一种
CREATE INDEX indexName ON tableName(columnName);
第二种
ALTER table tableName ADD INDEX indexName(columnName);

唯一索引：索引列的值必须唯一，但是可以为空，创建索引的语法：

第一种
CREATE UNIQUE INDEX indexName ON tableName(columnName);
第二种
ALTER table tableName ADD UNIQUE [indexName] (columnName);

复合索引：一个索引包含多个列，创建索引的语法：

第一种
CREATE INDEX indexName ON tableName(columnName1, columnName2);
第二种
ALTER table tableName ADD INDEX indexName(columnName1, columnName2);

注意：索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

事务的四大特征(ACID)

原子性（Atomicity）：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
一致性（Consistency）：事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
隔离性（Isolation）：关于事务的隔离性数据库提供了多种隔离级别：一个事务的执行不能干扰其它事务。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）：事务完成之后，它对于数据库中的数据改变是永久性的。该修改即使出现系统故障也将一直保持。

SQL分类

DDL(Data Definition Languages)语句:数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter 等。
DML(Data Manipulation Language)语句:数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、update 和 select 等。
DCL(Data Control Language)语句:数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

关系数据库的特点

数据集中控制。在文件管理方法中，文件是分散的，每个用户或每种处理都有各自的文件，这些文件之间一般是没有联系的，因此，不能按照统一的方法来控制、维护和管理。而数据库则很好地克服了这一缺点，可以集中控制、维护和管理有关数据。
数据独立性高。数据库中的数据独立于应用程序，包括数据的物理独立性和逻辑独立性，给数据库的使用、调整、优化和进一步扩充提供了方便，提高了数据库应用系统的稳定性。
数据共享性好。数据库中的数据可以供多个用户使用，每个用户只与库中的一部分数据发生联系；用户数据可以重叠，用户可以同时存取数据而互不影响，大大提高了数据库的使用效率。
数据冗余度小。数据库中的数据不是面向应用，而是面向系统。数据统一定义、组织和存储，集中管理，避免了不必要的数据冗余，也提高了数据的一致性。
数据结构化，整个数据库按一定的结构形式构成，数据在记录内部和记录类型之间相互关联，用户可通过不同的路径存取数据。
统一的数据保护功能，在多用户共享数据资源的情况下，对用户使用数据有严格的检查，对数据库规定密码或存取权限，拒绝非法用户进入数据库，以确保数据的安全性、一致性和并发控制。