三.mysql问答合集

目录

• 三.MySQL
  • 3.1 关系型和非关系型数据库的区别
    • 关系型数据库
    • 非关系型数据库
  • 3.2 登录数据库的方式，如何远程登录
  • 3.3 MySQL的服务结构，当客户端发起请求后，处理过程
  • 3.4 如何设置或者重置MySQL密码
  • 3.5 DDL,DML,DQL,DCL等SQL语句的写法
    • DDL数据定义语句
    • DCL数据控制语言（用户权限）
    • DML数据操作语言
    • DQL数据查询
  • 3.6 什么是索引，索引的作用
  • 3.7 索引的种类
  • 3.8 b树和b+树的区别
  • 3.9 什么情况下会导致索引失效
  • 3.10 MySQL的explain
  • 3.11 什么是事务，事务的作用
  • 3.12 事务的ACID分别怎么体现
  • 3.13 事务的隔离级别
    • 未提交读(read uncommited)
    • 已提交读(read commited)
    • 可重复读(repeated read)
  • 3.14 什么是脏读，幻读，不可重复读
    • 脏读
    • 不可重复读
    • 幻读
  • 3.15 事务的锁有哪些，怎么用
  • 3.16 悲观锁和乐观锁的区别
  • 3.17 事务发生redo和undo的区别和过程
    • 区别
    • Redo过程
    • Undo过程
  • 3.18 在事务未提交的情况下，数据库服务器重启，会发生什么
  • 3.19 什么是MySQL的存储引擎，常见的存储引擎有哪些，innodb和myisam存储引擎的区别
  • 3.20 客户的数据库是myisam如何切换到innodb(大致步骤)
  • 3.21 MySQL的日志都有哪些
  • 3.22 什么是二进制日志binlog,它的作用是什么
  • 3.23 二进制日志中row模式和statement模式的优缺点
  • 3.24 慢查询日志分析(mysqldumpslow)
  • 3.25 MySQL的备份和恢复相关知识
  • 3.26 MySQL服务损坏，且没有备份的情况下，数据库服务如何恢复
    • 1. 评估损坏程度
    • 2. 尝试自动恢复
    • 3. 使用二进制日志恢复
    • 4. 使用第三方数据恢复工具
    • 5. 寻求专业人士帮助
    • 6. 预防措施
    • 注意事项
  • 3.27 MySQL主从架构原理
  • 3.28 MySQL主主架构原理
    • 基本原理:
  • 3.29 MySQL双主架构一致性的问题
    • 一、数据同步的时间差
    • 二、并发写操作的数据冲突
  • 3.30 主从架构存在的问题，解决思路
  • 3.31 主从复制，从库误操作写入数据怎么办(主从不同步，会导致SQL线程停止)
    • 第零步，停止主从复制
    • 第一步，查看出错的准确位置
    • 第二步，确认位置
    • 第三步，回滚数据
    • 第四步，开启从库
    • 或者1~3步直接在从库中修改成原来的样子
  • 3.32 主从复制，从库IO线程故障排查思路
    • 一、检查基本连接信息
    • 二、查看从库状态
  • 3.33 主从复制中的延时从库，半同步复制，过滤复制使用场景
  • 3.34 MySQL的MHA高可用架构的原理
  • 3.35 如何部署MHA高可用架构

三.MySQL

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

关系型数据库

• 使用表结构，格式一致；
• SQL语言通用，可用于复杂查询；
• 读写性能比较差，尤其是海量数据的高效率读写；
• 固定的表结构，灵活度稍欠；
• 高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

非关系型数据库

格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3、高扩展性；
4、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：
1、不提供sql支持，学习和使用成本较高；
2、无事务处理；
3、数据结构相对复杂，复杂查询方面稍欠。

3.2 登录数据库的方式，如何远程登录

1. 编辑 MySQL config 文件
2. 配置防火墙允许远程连接
3. 允许 root 远程登录
4. 连接到远程 MySQL 服务器

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
mysql> UPDATE mysql.user SET host='%' WHERE user='root';

3.3 MySQL的服务结构，当客户端发起请求后，处理过程

3.4 如何设置或者重置MySQL密码

use mysql;
UPDATE user SET authentication_string=PASSWORD("root") WHERE User="root";
flush privileges;

3.5 DDL,DML,DQL,DCL等SQL语句的写法

DDL数据定义语句

#库部分
create database db02 charset utf8;
# 创建数据库的时候添加属性
show create database db01;
# 查看建库语句
drop database db02;
# 删除数据库db02
alter database db01 charset utf8;
#修改定义库
#表部分
create table student(
sid int '学号',
sname varchar(20) not null comment '学生姓名',
sage tinyint unsigned not null comment '年龄',
sgender enum('m','f') not null default 'm' comment '学生性别',
cometime datetime not null comment '入学时间'
)charset utf8 engine innodb;
#建表语句实例
desc student;
# 查看表中列的定义信息
drop table student;
#删表
alter table student rename stu;
# 修改表名
alter table stu add age int;
# 添加列和列数据类型的定义
alter table stu add test varchar(20),add qq int;
# 添加多个列
alter table stu add classid varchar(20) first;
# 指定位置进行添加列(表首)
alter table stu add phone2 int after sid;
# 指定位置进行添加列（指定列）
alter table stu drop qq;
# 删除指定的列及定义
alter table stu modify sid varchar(20);
# 修改列及定义（列属性）
alter table stu change phone telphone int();
# 修改列及定义（列名及属性）

DCL数据控制语言（用户权限）

grant all on *.* to root@'192.168.175.%' identified by '123456';
# 授予root@'192.168.175.%'用户所有权限(非超级管理员)
revoke select on *.* from root@'192.168.175.%';
# 收回select权限
show grants for root@'192.168.175.%';
# 查看权限

DML数据操作语言

insert into stu valus('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);

insert into stu(classid,birth.sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456),
('linux02',2,NOW(),'zhangsi',21,'f',NOW(),111,1234567);
# 插入多条数据

update student set sgender='f' where sid=1;
update mysql.user set password=PASSWORD('123456') where user='root' and host='localhost';

delete from student where sid=3;

truncate table student;
# DDL清空表中的内容

alter table student add status enum('1','0') default 1;
# 额外添加一个状态列
update student set status='0' where sid=1;
# 使用update
select * from student where status=1;
# 应用查询存在的数据

DQL数据查询

了解select的高级用法（不多简述）

3.6 什么是索引，索引的作用

特殊的数据库结构

加快数据库表中数据的检索速度，用于确保数据的唯一性。

3.7 索引的种类

• 主键索引
• 普通索引
• 唯一索引
• 全文索引
• 前缀索引
• 组合索引

3.8 b树和b+树的区别

B树的每个节点都存储关键字和数据，而B+树只有叶子节点存储数据，内部节点仅存储关键字和指向子节点的指针。

B+树因其优越的范围查询性能和索引空间利用率而被广泛用作默认的索引结构。

3.9 什么情况下会导致索引失效

1. 没有查询条件，或者查询条件没有建立索引。
2. 在查询条件上没有使用引导列。
3. 查询的数量是大表的大部分，应该是30％以上。
4. 索引本身失效。

3.10 MySQL的explain

2024 年 8月 随笔档案 - guixiang - 博客园 (cnblogs.com)

3.11 什么是事务，事务的作用

事务 是一组操作,把所有的操作 作为一个整体一起向系统提交 或 撤销操作请求，即这些操作要么同时成功，要么同时失败。

3.12 事务的ACID分别怎么体现

Atomic（原子性）

• 所有语句作为一个单元全部成功执行或全部取消。

Consistent（一致性）

• 如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。

• 在事务内看到的数据状态都是一样的。

Isolated（隔离性）

• 事务之间不相互影响。

Durable（持久性）

• 事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
• 软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。

3.13 事务的隔离级别

未提交读(read uncommited)

A事务已执行，但未提交；B事务查询到A事务的更新后数据；A事务回滚；

已提交读(read commited)

A事务执行更新；B事务查询；A事务又执行更新；B事务再次查询时，前后两次数据不一致；---不可重复读

可重复读(repeated read)

A事务无论执行多少次，只要不提交，B事务查询值都不变；B事务仅查询B事务开始时那一瞬间的数据快照；

mysql默认模式

3.14 什么是脏读，幻读，不可重复读

脏读

指一个线程中的事务读取到了另外一个线程中未提交的数据。

不可重复读

指一个线程中的事务读取到了另外一个线程中提交的update的数据。

幻读

指一个线程中的事务读取到了另外一个线程中提交的insert的数据。莫名情况下数据增加或减少（出现于可重复读）

3.15 事务的锁有哪些，怎么用

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

通过查询条件对某一行进行加锁，commit后解锁

//读锁（共享锁）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
//写锁（排他锁）
SELECT * FROM table_name WHERE ... FOR UPDATE;

当我们进行增删改的时候Innodb是默认给对应行自动加上了写锁的，select查询不会加锁。
在行锁中，写锁和读锁都是其他会话只能查看无法修改该行数据，自己可以修改，但是我们给某一行加了读锁后，其他会话可以给这一行加读锁，不能加写锁。当我们给某一行加了写锁后，其他会话不能给该行加锁

3.16 悲观锁和乐观锁的区别

悲观锁：认为在修改数据库数据的这段时间里存在着也想修改此数据的事务；

乐观锁：认为在短暂的时间里不会有事务来修改此数据库的数据；

3.17 事务发生redo和undo的区别和过程

区别

Redo（重做）	Undo（撤销）
用来记录数据页的物理修改，确保事务提交后数据的一致性，并在系统崩溃时能够恢复数据到提交状态。	用来记录事务中数据修改前的状态，以便在事务失败或需要回滚时，能够撤销事务所做的修改，恢复数据到修改前的状态。

Redo过程

1. 数据修改前：在事务对数据库进行修改之前，InnoDB存储引擎会将修改操作记录到redo log buffer（内存中的日志缓冲）中。
2. 数据修改：随后，对数据库中的数据进行实际的修改操作。
3. 日志写入：当事务提交时，InnoDB会将redo log buffer中的日志写入到磁盘上的redo log file中，确保即使在系统崩溃的情况下，也能通过redo log恢复数据。
4. 恢复数据：在系统崩溃或需要恢复数据时，可以通过redo log中的记录，将数据库恢复到事务提交时的状态。

Undo过程

1. 数据修改前：在事务对数据库进行修改之前，InnoDB存储引擎会生成undo log，记录数据修改前的状态。
2. 数据修改：对数据库中的数据进行修改。
3. 事务提交或回滚：
   • 如果事务成功提交，undo log可能会被清理（根据具体的undo日志保留策略）。
   • 如果事务失败或执行ROLLBACK命令，InnoDB会根据undo log中的记录，将数据库中的数据恢复到修改前的状态。
4. 读一致性：在并发事务中，undo log还用于实现读一致性，即确保读操作能够读取到事务开始前的数据状态。

3.18 在事务未提交的情况下，数据库服务器重启，会发生什么

​ 当系统提交了数据库DML指令，但未执行commit，系统宕机了。此时，数据库的数据不会改变，数据库连接会被清除，数据库事务也会被清除，也不会锁定数据

3.19 什么是MySQL的存储引擎，常见的存储引擎有哪些，innodb和myisam存储引擎的区别

​ MySQL数据库管理系统中负责存储和检索数据的组件，它们可以影响数据的存储方式、事务支持、并发性能等方面1。MySQL中常见的存储引擎有MyISAM、InnoDB、Memory、Archive等23。其中，InnoDB是MySQL的默认存储引擎，它提供了强大的事务处理、行级锁定和外键约束功能4。MyISAM则不支持事务、行级锁和外键约束的功能

3.20 客户的数据库是myisam如何切换到innodb(大致步骤)

​ 如使用wp_comments表格。只需运行ALTER命令即可将其转换为InnoDB存储引擎。注意：我们始终建议在对MySQL数据库运行任何操作之前对其进行备份。

ALTER TABLE wp_comments ENGINE=InnoDB;

3.21 MySQL的日志都有哪些

1. 重做日志（redo log）
2. 回滚日志（undo log）
3. 二进制日志（binlog）
4. 错误日志（errorlog）
5. 慢查询日志（slow query log）
6. 一般查询日志（general log)

比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。

3.22 什么是二进制日志binlog,它的作用是什么

是一种记录数据库中发生的更改的日志文件。 它记录了数据以及数据库结构的更改

它可以

• 有数据库搭建开始所有的二进制日志，可以把数据恢复到任意时刻
• 数据的备份恢复
• 数据的复制

3.23 二进制日志中row模式和statement模式的优缺点

• statement语句模式
  • 优点：简单明了，容易被看懂，就是sql语句，记录时不需要太多的磁盘空间
  • 缺点：记录不够严谨
• row行模式
  • 优点：记录更加严谨
  • 缺点：有可能会需要更多的磁盘空间，不太容易被读懂

3.24 慢查询日志分析(mysqldumpslow)

​ 将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件，通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

$PATH/mysqldumpslow -s c -t 10 /application/mysql/data/slow.log
#输出记录次数最多的10条SQL语句

[!TIP]

/application/mysql/data/slow.log 是慢查询日志路径

3.25 MySQL的备份和恢复相关知识

#数据库备份
mysqldump -u root -p wordpress > wordpress_database.sql 
#数据库还原
mysql > source /backup/mysqldump/wordpress_database.sql 

3.26 MySQL服务损坏，且没有备份的情况下，数据库服务如何恢复

在MySQL服务损坏且没有备份的情况下，数据库服务的恢复是一项具有挑战性的任务。尽管无法完全保证恢复所有丢失的数据，但以下是一些可能的恢复步骤和策略：

1. 评估损坏程度

• 检查日志文件：首先，检查MySQL的错误日志和其他相关日志文件，以了解服务损坏的具体原因和可能的错误信息。
• 检查数据文件：检查MySQL的数据文件（通常位于/var/lib/mysql目录下），查看是否有明显的损坏迹象，如文件缺失、损坏或大小异常。

2. 尝试自动恢复

• InnoDB崩溃恢复：如果MySQL使用的是InnoDB存储引擎，并且服务是因为崩溃而停止的，InnoDB会在MySQL服务重启时自动尝试恢复数据库到最近的一致性状态。这通常涉及回滚未提交的事务并应用重做日志中的更改。

3. 使用二进制日志恢复

• 查找二进制日志：MySQL的二进制日志（Binary Log）记录了数据库的所有更新操作，包括插入、更新和删除等。检查/var/lib/mysql目录下是否存在二进制日志文件（通常以mysql-bin.开头）。
• 分析日志：使用mysqlbinlog工具分析二进制日志文件，找出在损坏发生前执行的最后一个完整事务。
• 恢复数据：根据分析的结果，可以尝试将二进制日志中的SQL语句导出到一个文件中，并使用mysql命令将这些语句应用到新的或修复后的数据库中。

4. 使用第三方数据恢复工具

5. 寻求专业人士帮助

6. 预防措施

• 定期备份
• 加强数据库的监控和日志记录

注意事项

• 在进行任何恢复操作之前，请确保已经停止了MySQL服务，以避免在恢复过程中对数据造成进一步的损坏。
• 恢复后的数据可能需要进行验证和测试，以确保数据的完整性和准确性。

3.27 MySQL主从架构原理

​ 该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。即使事务中的语句都是交叉执行的,在事件写入二进制日志完成后，master通知存储引擎提交事务。

​ 下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

​ SQL slave thread处理该过程的最后一步。SQL线程从中继日志读取事件，更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

3.28 MySQL主主架构原理

它包含两个主数据库（Master），每个主数据库都可以处理读写请求，并且互为主备。

基本原理:

• 双向复制：在两个主服务器（Master1和Master2）之间实现双向的数据复制。每个主服务器都将其更改记录到二进制日志（binary log）中，并且每个主服务器都配置为另一个主服务器的从服务器，从而从对方的二进制日志中读取更改并应用到自己的数据库中。
• 数据一致性：通过持续的双向复制，两个主服务器的数据保持高度一致。这种一致性是异步的，但可以通过配置半同步复制来减少数据延迟和提高数据安全性。
• 故障转移：在主主架构中，如果其中一个主服务器发生故障，另一个主服务器可以立即接管服务，无需进行复杂的故障转移操作。这有助于实现高可用性和业务连续性。

3.29 MySQL双主架构一致性的问题

一、数据同步的时间差

• 异步复制
• 同步延迟

二、并发写操作的数据冲突

• 主键冲突：在双主架构中，如果两个主服务器同时尝试插入具有相同主键的记录，那么由于复制的时间差，这些插入操作可能会在各自的服务器上成功，但在同步到对方服务器时发生冲突。
• 数据覆盖：如果两个主服务器同时更新同一条记录的不同字段，那么由于复制的顺序和时机问题，后一个更新可能会覆盖前一个更新的结果，导致数据不一致。

3.30 主从架构存在的问题，解决思路

1. 数据延迟
2. 数据一致性问题
3. 故障转移和恢复不及时,影响业务的连续性和可用性
4. 复制冲突
5. 配置和维护复杂

3.31 主从复制，从库误操作写入数据怎么办(主从不同步，会导致SQL线程停止)

​ 这种情况常发生于从库没有配置super_read_only=1，然后管理员错误地在从库增删了数据，导致从库与主库数据不一致。要解决这类问题，通常需要在从库执行反向操作，比如删掉这些错误新增的数据，通过手动的方式让主从数据恢复到之前一致状态。

第零步，停止主从复制

mysql > stop slave

第一步，查看出错的准确位置

​ 执行show slave status命令查看从库状态，获取当前已应用到的binlog信息，主要关注Last_Error中所提示的信息

第二步，确认位置

根据Last_Error中的报错信息获取具体出错的SQL

show binlog events in 'mysql-bin.032102' from 730019106 limit 10;  #找到对应行，该行中Info信息就是1973位置所做操作

第三步，回滚数据

定位出错误语句后只需要在从库执行反向操作处理这些数据即可，然后重新启动slave进程

第四步，开启从库

mysql > start slave

或者1~3步直接在从库中修改成原来的样子

3.32 主从复制，从库IO线程故障排查思路

一、检查基本连接信息

1. 确认主库地址和端口
2. 检查复制用户权限，密码
3. 检查网络连通性

二、查看从库状态

1. 执行SHOW SLAVE STATUS\G命令

   • 关注Slave_IO_Running字段，如果为No，则表示IO线程未运行。

   • 检查Last_IO_Error和Last_IO_Errno字段

     • 这些字段提供了IO线程停止运行的最后错误信息和错误代码。

   根据错误信息和代码，可以初步判断故障原因。

3.33 主从复制中的延时从库，半同步复制，过滤复制使用场景

延时从库是指故意配置从库延迟一定时间复制主库的数据。

• 适用于数据恢复；

半同步复制要求主库在事务提交前，至少有一个从库已经接收到并记录了该事务的binlog日志。

• 适用于数据一致性要求高、高可用性和容灾等场景；

过滤复制允许用户灵活指定哪些数据库或表需要被复制，哪些则不需要。

• 适用于业务隔离、性能优化和数据迁移等场景。

  （逐步迁移特定的数据库或表，减少迁移过程中的风险和复杂性。）

3.34 MySQL的MHA高可用架构的原理

1. 主节点监控
   • MHA Manager会不断监控主节点的状态，包括连接是否正常、主节点是否正常运行等。
2. 自动故障检测
   • 当MHA Manager检测到主节点出现故障（如主节点宕机）时，会自动检测并确认主节点是否真的不可用。
3. 故障切换
   • 一旦确认主节点不可用，MHA Manager会自动执行故障切换操作：
     • 从宕机的主节点保存二进制日志事件（binlog events），以确保数据不丢失。
     • 识别含有最新更新的从节点。
     • 应用差异的中继日志（relay log）到其他从节点，以确保所有从节点的数据一致性。
     • 将最新的二进制日志事件应用到其他从节点上。
     • 提升一个从节点为新的主节点。
     • 使其他从节点连接到新的主节点，并继续复制操作。
4. 数据一致性保证
   • 在故障切换过程中，MHA会确保数据的一致性，避免数据丢失或冲突。
5. 故障恢复
   • 当原始的主节点恢复正常后，MHA Manager会将其重新加入到主从复制架构中，并将其作为从节点与新的主节点同步数据。

3.35 如何部署MHA高可用架构

• 搭建三台mysql数据库

• 修改配置文件/etc/my.cnf

• server-id

• 开启binlog日志

• 创建主从复制用户

• 配置主从复制主机信息

• 重启mysql

• 开启GTID

• 正式部署MHA

• 工具包下载

• 安装依赖包

• 命令软连接（所有节点）

• 部署管理节点（mha-manager:mysql-db03）

• 编辑配置文件(manage节点)

• 配置ssh信任（所有节点）

• 启动测试（manage节点）

• 启动MHA