DBA面试总结（Mysql篇）

时间：2024-04-21 06:57:56

一、delete与trancate的区别

相同点

1.两者都是删除表中的数据，不删除表结构

不同点

1.delete支持按条件删除，TRUNCATE不支持。
2.delete 删除后自增列不会重置，而TRUNCATE会被重置。
3.delete是逐条删除（速度较慢），truncate是整体删除（速度较快）。
4.delete删除是一条一条删除，并不会改变表结构，属于DML，而truncate删除全部数据，属于DDL。
5.delete不会释放空间，而TRUNCATE会释放空间。
6.delete 支持回滚，TRUNCATE不支持。

7.对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATETABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器

8.delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

本文结合了以下内容与

https://blog.****.net/weixin_44224262/article/details/125538188

二、事务

特性：

1. 原子性（Atomicity）：

指事务是一个不可分割的单位，要么全部执行，要么完全不执行。如果事务中的任何一部分失败，整个事务将被回滚到初始状态，不会留下部分执行的结果。

2. 一致性（Consistency）：

指事务执行前后，数据库的状态必须是一致的。这意味着事务执行后，数据库中的数据必须满足所有的约束和规则，如唯一性约束、外键约束等。

3. 隔离性（Isolation）：

指多个事务并发执行时，每个事务的执行都应该与其他事务隔离开来，互不干扰。这可以防止并发执行时产生的一些问题，如脏读、不可重复读和幻读。

4. 持久性（Durability）：

指一旦事务提交，其结果应该是永久性的，即使系统发生故障，也不应该丢失提交的数据。

隔离级别

1. 读未提交：

在这个级别，一个事务可以读取另一个未提交事务的数据。这可能导致脏读问题，即事务A读取了事务B未提交的数据，如果事务B在提交前回滚，那么事务A读取到的数据就是“脏”数据。这是就会发生脏读。

2. 读已提交（Read Committed）：

这个级别要求事务只能读取已经提交的数据。这解决了脏读问题，但在高并发场景下，可能出现不可重复读和幻读问题。不可重复读是指，事务A在读取同一数据时，事务B修改了该数据并提交，导致事务A多次读取的结果不一致。这是不可重复读。不可重复读主要关注的是数据的修改。

3.可重复读（Repeatable Read）：

可重复读的实现方式并不是在事务开始时创建一个快照，然后事务内的所有查询都基于这个快照。实际上，这个“快照”的概念更接近于一种逻辑上的快照，它保证了在事务进行期间，看到的数据是一致的，但并非通过一个物理的快照实现。还有更离谱的说法是（在当前事务没结束时，不允许其他事务进行修改操作），这明显不太合理。

MySQL通过多版本并发控制（MVCC）机制来确保事务的可重复读。每个数据行都会关联一个版本号（或称为事务ID）。当一个事务开始时，它不会立即创建整个数据库的快照，而是会根据需要读取的数据行和这些行的版本号来确定可见的数据版本。事务只能看到在事务开始时已经提交的数据版本，而对于未提交或在事务开始后提交的数据版本，该事务是不可见的。这解决了不可重复读问题，但仍然存在幻读问题（如果一个事务读取了几行数据，然后另一个并发事务插入新行并提交，那么原事务再次读取同样的范围时，就会看到这些新的“幻影”行，这就是幻读现象）。幻读则主要关注新增或删除操作。

4.串行化（Serializable）：

这是最高的隔离级别。一个事务执行期间对数据加锁，其他数据不能访问该数据，可以避免所有并发问题，但效率最低，它要求事务串行化顺序执行，即事务只能一个接一个地执行，而不能并发执行。这样可以避免脏读、不可重复读和幻读问题，但会大大降低并发性能。

Mysql默认级别是可重复读，查询默认隔离级别：select @@tx isolation;修改默认隔离级别：select global transactionisolation level 级别字符串

三、MySQL中有哪几种锁？

MySQL中有三种锁：表级锁、行级锁和页面锁，表级锁是对整张表加锁，不会出现死锁，但并发度低，行级锁只对涉及的行加锁，支持高并发，但是开销大，可能出现死锁。页面锁是介于两者之间的一种锁，是对整个数据页加锁，可以减少锁冲突，但也可能出现死锁。死锁是指两个或多个事务在执行过程中因争夺资源造成的一种互相等待现象。避免或解决死锁的方法有以下几种：设计合理的索引和锁策略，尽量缩短锁的持有时间和范围。减少每个事务锁定的资源数量和和时间;为表添加合理的主键或唯一索引，避免表锁升级为行锁；设置合理的超时时间和重试机制遇到死锁时主动回滚并重新执行事务；

四、MySQL中有哪些不同的表类型（存储引擎）？

  1. InnoDB：

    • InnoDB是MySQL的默认存储引擎（Mysql5.5版本之后），它提供了事务安全（ACID兼容）的表。
    • 支持外键约束，支持自动增长列。
    • 提供行级锁定，从而提高了并发性能。
    • 支持崩溃恢复，支持热备。
    • 提供了MVCC（多版本并发控制）功能。读写分离，可重复读（在MVCC中，每个事务启动时，系统会为其分配一个唯一的事务id。当一个事务要访问某个数据时，系统会检查数据的版本号和启动时间，如果数据的版本号早于事务的启动时间，该事务可以访问数据，否则该事务需要等待其他事务完成对该数据的访问。MVCC通过使用版本号或时间戳来标记每个事务的读写版本，将读操作与写操作进行隔离，避免了读写冲突。因此，当一个事务正在读取某个数据时，其他事务可以继续并发地进行读操作，提高了数据库的并发性能。这种机制在长时间运行的事务中特别有用，可以避免锁资源长时间占用，影响其他事务的执行效率。此外，MVCC还提供了非阻塞的快照读取功能，适用于需要读取历史数据或快照数据的场景）
    • 数据和索引紧密捆绑，使用B+树结构，所有的表按照主键来组织。

  2. MyISAM：

    • MyISAM是早期MySQL版本中常用的存储引擎。
    • 它提供了全文索引功能。
    • 通常用于只读或大量插入的表。
    • 不支持事务和行级锁定还有外键，只支持表级锁定。
    • MyISAM的查询操作本身是原子的（即每次查询都是一个不可分割的操作），但MyISAM本身并不支持事务的原子性，即在多个查询组成的事务中无法保证原子性
    • 有较高的查询和插入速度，select count（）很快，因为把表的总行数储存在磁盘上，执行select count操作后直接返回
    • 索引和数据是分开的并且索引是有压缩，内存使用率对应提高了不少

  3. MEMORY (或 HEAP)：

    • 数据存储在内存中，速度非常快，但数据在MySQL服务器重启后会丢失。
    • 通常用于临时表或需要快速访问的表。
    • 不支持事务。
    • 支持数据类型有限，不支持text和blob类型，对于字符串类型的数据只支持固定长度的varchar会被自动存储为char类型
    • 支持表级锁
    • 使用hash索引

  4. CSV：

    • 数据以逗号分隔值（CSV）格式存储。
    • 可以使用文本编辑器查看和编辑。
    • 不支持索引。

  5. ARCHIVE：

    • 用于存储和检索大量归档数据。
    • 只支持INSERT和SELECT操作。
    • 使用zlib压缩来减小存储空间。

  6. BLACKHOLE：

    • 一个“黑洞”存储引擎，它会接受数据但不存储，读取操作会返回空内容。
    • 主要用于复制架构中的特殊目的。

  7. FEDERATED：

    • FEDERATED存储引擎允许你访问远程MySQL服务器上的表。
    • 在本地服务器上创建一个FEDERATED表实际上是一个指向远程服务器上真实表的链接。

  8. MERGE：

    • MERGE存储引擎允许你将多个MyISAM表当作一个表来查询。
    • 这对于分区表特别有用。

  9. NDBCLUSTER (或 NDB)：

    • NDBCLUSTER是MySQL Cluster的一部分，提供了分布式数据库解决方案。
    • 支持高可用性和数据冗余。

五、索引

索引是一种数据结构，可以帮助数据库快速查找和访问数据。索引的目的和作用有以下几下几点

提高查询效率，通过索引可以减少扫描的数据量，避免全表扫描，但是也会增加插入，更新和啊删除开销，以及占用磁盘空间等。因此在创建索引时要选择恰当的列，通常选择参与频繁的查询条件或排序操作的列，选择不同值多的列，较小的数据类型或长度的列，避免使用过多或过少的索引，一个表最多不超过五个，遵循最左前缀原则（如果创建了组合索引那么查询条件必须包含组合索引最左边的列）可以使用explain命令查询语句索引的使用情况例：EXPLAIN SELECT * FROM...

索引失效的情况：查询条件使用函数或表达式，查询条件中使用了隐式类型转换，查询条件使用了OR连接符，或查询条件使用！=、>、<等非等值符号

索引类型：

普通索引：最基本的索引类型，没有任何限制，可以创建多个普通索引。
唯一索引：唯一索引要求索引列的值不能重复，除了NULL值，可以创建多个唯一索引。
主键索引：主键索引是一种特殊的唯一索引，要求索引列的值不能重复且不能为NULL，一个表只能有一个主键索引。
组合索引：组合索引是由多个列组成的索引，可以加快多个列的查询条件的效率，但要注意遵循最左前缀原则。
全文索引：全文索引是一种针对文本内容的索引，可以支持模糊匹配和相关度排序，只适用于MyISAM和InnoDB表的CHAR、VARCHAR和TEXT类型的列。

创建和删除方式

创建索引：CREATE [UNIQUE] INDEX ON ( [ASC|DESC] [, …]);
删除索引：DROP INDEX ON ;
也可以在创建表或修改表时指定索引：
创建表时指定索引：CREATE TABLE (, …, [INDEX|UNIQUE|PRIMARY KEY] ( [ASC|DESC] [, …]));
修改表时指定索引：ALTER TABLE ADD [INDEX|UNIQUE|PRIMARY KEY] ( [ASC|DESC] [, …]);

六、备份恢复

1.使用cp或tar进行物理备份：

这种方法是直接复制或打包数据库的物理文件，数据文件，日志文件，配置文件等。优点:备份速度快，恢复速度快，不需要停止数据库服务。缺点是占用空间大，不能进行增量备份，不能跨平台恢复，需要注意文件权限和所有者。

2.使用mysqldump:

从数据库中导出sql语句或者数据，可以备份整个数据库也可以指定备份哪个数据库哪个表。

优点：可以进行增量备份，占用空间小，可跨平台恢复

缺点:备份速度慢恢复速度慢，可能会影响数据库服务

3.使用xtrbackup：

这种方法是使用xtra backup工具对InnoDB存储引擎的数据文件进行物理备份，可以实现热备份。

优点：备份速度快恢复速度快，可以进行增量备份和差异备份，不会锁表影响数据库服务。

缺点：但是缺点是占用空间大只支持InnoDB存储引擎。如果要实现增量备份和定点恢复还需要结合二进制日志。开启二进制服务：在配置文件中添加log-bin选项，并重启mysql服务。使用mysqldump或xtrabackup进行完全备份时需要添加--master-data=2选项，这样会在备份文件中记录二进制日志的文件名和位置。定期清理二进制日志要在配置文件中添加expire_logs_days选项并重启mysql服务

七、Mysql性能优化

可以从以下几个方面：

1. 选择最合适的字段属性，选择合适的数据类型和长度。避免使用过大或过小的字段，尽量把字段设置为not null 这样可以减少数据库比较null值开销

2.使用恰当的索引

3.使用连接代替子查询

4.使用union代替临时表

5.使用事务（多表操作放在一个事务），可以减少开销，并且避免数据不一致的风险

6.使用外键

7.查看和优化慢查询：MySQL有一个内置的慢查询日志，可以用来查看和优化慢查询。要启用慢查询日志，你需要在MySQL配置文件中设置以下参数：

slow_query_log=1 ：表示开启慢查询日志功能。
slow_query_log_file=/var/log/mysql/mysql-slow.log ：表示指定慢查询日志的文件路径和名称。
long_query_time=1 ：表示设置慢查询的阈值，单位是秒。如果一个查询的执行时间超过这个值，就会被记录到慢查询日

八、主从复制

MySQL中可以使用以下步骤来实现主从复制：

在主服务器上，修改配置文件，开启二进制日志功能，设置唯一的服务器ID，指定需要复制的数据库或表，重启MySQL服务。
在从服务器上，修改配置文件，设置唯一的服务器ID，开启中继日志功能，重启MySQL服务。
在主服务器上，创建一个专用的复制用户，并授予相应的权限。
在从服务器上，使用CHANGE MASTER TO命令，指定主服务器的IP地址、端口号、复制用户、密码、二进制日志文件名和位置等信息。
在从服务器上，使用START SLAVE命令，启动复制进程。
在主服务器或从服务器上，使用SHOW MASTER STATUS或SHOW SLAVE STATUS命令，查看复制状态和信息。

九、MySQL中如何监控和诊断性能问题？

1.使用SHOW PROCESSLIST命令:

查看当前正在执行的连接和语句，分析是否有慢查询、锁等待、死锁等情况。


2.使用SHOW STATUS命令:

查看MySQL服务器的运行状态和统计信息，分析是否有资源瓶颈、错误、警告等情况。


3.使用SHOW VARIABLES命令:

查看MySQL服务器的配置参数，分析是否有不合理或不适合的设置，影响性能或稳定性。


4.使用SHOW ENGINE命令:

查看存储引擎的状态和信息，分析是否有异常或错误发生，影响数据的完整性或可用性。
使用第三方工具或框架，如MySQL Workbench、Percona Toolkit、Performance Schema等，提供更多的监控和诊断功能和界面，方便分析和优化性能问题。

十、范式

在了解范式前先学习几个概念：

1.码：相当于键。设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K（这个“完全”不要漏了），那么我们称 K 为候选码，简称为码。、候选键=候选码：能够唯一标识一条记录的最小属性集任一候选键的任何真子集都不能唯一标识一个记录（比如在成绩表中（学号,课程号）是一个候选键，单独的学号，课程号都不能决定一条记录）

包含在任何一个码中的属性成为主属性。

第一范式：1NF的定义为：符合1NF的关系中的每个属性都不可再分

第二范式：表中不存在非主属性对码存在部分函数依赖。例：有一个学生表,（学号，课名）作为码，存在以下关系（学号，课名） → 姓名，有 学号 → 姓名，存在非主属性 姓名 对码（学号，课名）的部分函数依赖。

第三范式：表中不存在非主属性对码存在传递函数依赖。比如A->B，B->C

BC范式：表不存在着主属性对于码的部分函数依赖与传递函数依赖。

相关文章