MySQL面试题

基础知识

1. 什么是数据库、表、字段、记录？

   • 数据库: 存储和管理数据的容器，数据的逻辑集合[类似Excel]
   • 表: 结构化对象, 用于按行和列的形式组织数据。每个表代表某类实体[类似excel的sheet]
   • 字段：描述数据的某个属性或维度。类比为excel的表头信息
   • 记录：一条完整的数据项,类比为excel的一行数据

   概念	含义	类比
   数据库	存放表的容器	Excel 文件
   表	存储结构化数据的单元	Excel 表单
   字段	表的一列，描述属性	表头
   记录	表的一行，代表一个数据实体	每一行数据

2. 常见的数据类型：

   1. 数值类型:

      类型	描述	典型用途
      TINYINT	1 字节，范围 -128 ~ 127 或 0~255	状态标志、布尔类型
      SMALLINT	2 字节	年龄、评分
      INT / INTEGER	4 字节，常用整型	主键ID、自增ID
      BIGINT	8 字节	用户ID、交易流水号等大整数
      DECIMAL(m,d)	精确小数，存储为字符串	金额、利率（避免精度问题）
      FLOAT	单精度浮点数（不精确）	科学计算，非关键金额
      DOUBLE	双精度浮点数（更大范围）	科学计算

   2. 字符串类型:

      类型	描述	典型用途
      CHAR(n)	固定长度字符串（最多 255）	性别、状态（固定长度）
      VARCHAR(n)	可变长度字符串（最多 65535，总行宽限制）	用户名、邮箱等
      TEXT	长文本（最大 64KB）	评论、文章内容
      TINYTEXT / MEDIUMTEXT / LONGTEXT	更长文本	富文本、博客正文
      ENUM	枚举类型，固定几个可选值	性别、状态
      SET	多选集合	标签、多选项字段

   3. 日期和时间类型:

      类型	描述	示例
      DATE	仅日期（YYYY-MM-DD）	2025-05-27
      TIME	仅时间（HH:MM:SS）	12:30:00
      DATETIME	日期 + 时间	2025-05-27 12:30:00
      TIMESTAMP	时间戳，支持时区，常用于自动记录	2025-05-27 12:30:00
      YEAR	表示年份（4 位）	2025

3. 什么是主键、外键、唯一键，有何区别？

   1. 主键: 唯一标识一条记录的字段，不能重复、不能为NULL，主键会自动创建唯一索引
   2. 外键: 引用其他表主键的字段，用于简历两个表之间的关联关系.
      • 可以重复，但它引用的值必须在目标表中存在(或为NULL)
      • 用于维护数据一致性和完整性
   3. 唯一键: 保证某列或列组合的值在表中唯一但可以为NULL。
      • 可以拥有多个唯一键
      • 允许有一个NULL值
      • 不作为主键时也可以起到去重校验作用

4. 什么是自增主键？怎么实现？

   主键 ：指的是数据库为该字段自动生成唯一递增的整数值，通常用来唯一标识每条记录.

   • 插入新数据时,无需手动指定该字段的值
   • 数据库会自动为它分配一个比当前最大值大的整数
   • 保证每条记录主键唯一性。

5. 什么是 NULL？NULL 和空字符串 "" 有什么区别？

   1. NULL:
      • NULL代表的是空值或者未知值
      • 不是数字、字符串、零，代表没有数据
   2. 空字符串:
      • 代表的是有值，只是长度为0的字符串

6. WHERE 子句中 IN 和 EXISTS 有何区别？适用场景？

   1. IN: 更适合小集合、静态列表，null值会导致以外结果
   2. EXISTS: 会对A表的每一行，都执行一次子查询，不受NULL影响

7. 什么是视图（VIEW）？有什么作用？

   • 制度的虚拟表,用于**封装复杂查询、隐藏细节、提升安全性和复用性

8. LIMIT 和 OFFSET 的使用场景？分页查询如何优化？

   1. 基本用法

      SELECT * FROM orders LIMIT 10 OFFSET 20;
      等价于

      SELECT * FROM orders LIMIT 20, 10;
   2. 分页查询优化方法：
      1. 使用索引字段进行"基于位置"的分页,**使用唯一递增字段(如自增ID或时间)作为游标

      -- 第一次获取第一页
      SELECT * FROM orders ORDER BY id ASC LIMIT 10;
      
      -- 获取下一页：记录最后一条 id = 100
      SELECT * FROM orders WHERE id > 100 ORDER BY id ASC LIMIT 10;

9. 什么是事务？有哪些特性（ACID）？

   1. 事务: 数据库中一组操作的集合，要么成功要么失败，是一个最小的执行单元。
   2. ACID: 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)

中级知识

1. InnoDB 和 MyISAM 有什么区别？默认存储引擎是哪一个？
   1. InnoDB: 支持事务、写多、数据安全性高、行级锁、并发高
   2. MyISAM: 不支持事务、读多、全文搜索、表级锁、不支持事务、并发性差
2. B+ 树和哈希索引的区别是什么？为什么 InnoDB 使用 B+ 树？
   1. B+: 多叉树结构，有序、支持条件排序，支持联合索引、模糊也可以走索引、无冲突
   2. Hash: 无需、只支持等值查询、不支持索引、不支持、有冲突需额外处理
   3. InnoDB:
      1. 支持有序和范围查询
      2. 支持最左侧前缀匹配，适合联合索引
      3. 支持排序操作
      4. 哈希冲突高，不合适大量存储
3. COUNT(*)、COUNT(1)、COUNT(字段名) 的区别？性能如何？
   1. COUNT(*): 总行数，不管字段是否为NULL,性能较好，根据主键来主键进行统计
   2. COUNT(1)：统计总行数，1作为一个常量,也是根据主键统计，性能较好
   3. COUNT(Field): 统计非NULL的行数,性能较差，需要判断数据是否为NULL。
4. DELETE 和 TRUNCATE 有何区别？会触发触发器吗？
   1. DELETE: 行级锁，逐行删除,会触发触发器，支持事务
   2. TRUNCATE: 快速清空表，类似重建表
5. MySQL 中如何防止重复插入？（如 INSERT IGNORE / REPLACE）
   1. 使用唯一索引/主键保证数据唯一性
   2. INSERT IGNORE 当插入时遇到唯一键冲突错误时应该忽略，跳过该条记录
   3. REPLACE INTO 先删除旧记录在插入新记录
   4. INSERT ... ON DUPLICATE KEY UPDATE 发现重复则进行更新
6. 什么是幻读、脏读、不可重复读？分别在什么隔离级别下会发生？
   1. 幻读:
      • 同一事务中，两次读取"符合条件"的多条记录时,结果的记录数不同。
      • 由于另外一个事务插入了满足条件的新纪录
   2. 脏读:
      • 一个事务读取了另外一个未提交事务的数据
      • 一个事务回滚了，前一个事务读取到的数据就是"脏"的，不应该出现
   3. 不可重复度:
      • 同一事务中，两次读取同一条记录，结果不同
      • 原因是另一个事务在两次读取之间修改并提交了该记录
7. InnoDB 的 MVCC（多版本并发控制）是怎么工作的？
   1. MVCC多版本并发控制(Nulti-Version Concurrency Control)
   2. 关键组件:
      • trx_id: 每条记录创建或修改时记录它是哪个事务生成的
      • undo log: 每次更新数据时，保存旧版本(回滚日志)
      • read view: 快照读视图，决定你能看到哪些版本的数据
      • 隐藏列: 每条记录都有两个隐藏列：创建版本、删除版本
8. 什么是间隙锁（Gap Lock）？在哪些场景下会加 gap 锁？
   1. 间隙锁: 用于锁定某个索引直接的范围(而非记录),防止在该范围内插入新数据,从而防止幻读.
9. MySQL 的行锁和表锁有何区别？如何判断是否发生了锁？
   1. 区别：

      特性	行锁（Row Lock）	表锁（Table Lock）
      锁粒度	锁住单行记录	锁住整个表
      并发性	高，并发读写影响小	低，一旦锁表，其他线程无法并发写入
      开销	大，需要更复杂的锁机制和更多内存开销	小，管理简单
      死锁	容易产生，需要检测和处理	不会发生死锁
      使用场景	事务要求高的系统（如订单系统）	小型表或读多写少的操作，如后台批量操作

   2. 查看锁:

      -- 查看当前正在等待的锁
      SELECT * FROM information_schema.INNODB_LOCK_WAITS;
      -- 查看当前的锁信息（包括持有的锁）
      SELECT * FROM information_schema.INNODB_LOCKS;
      -- 查看当前运行的事务
      SELECT * FROM information_schema.INNODB_TRX;
      -- 查看当前锁表
      SHOW OPEN TABLES WHERE In_use > 0;

MySQL 用户创建

创建用户并授权以及远程登录配置

1. 创建用户账号

-- 语法
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

2. 授权

-- 授权所有权限
GRANT ALL privileges ON database_name.* TO 'username'@'host';
-- 授予特定权限(增、删、改、查)
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'useranme'@'%';
-- 撤销权限
REVOKE privilege_type ON database.table FROM 'username'@'host';
-- 查看用户权限
SHOW GRANTS FOR 'username'@'host';
-- 修改用户密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

3. 刷新权限

FLUSH PRIVILEGES;

4. 远程登录配置

-- ubuntu修改配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
bind-address = 0.0.0.0
-- 验证绑定地址
SHOW VARIABLES LIKE 'bind_address';
SHOW VARIABLES LIKE 'port';

5. 重启MySQL

sudo service mysql restart

6. 确认端口监听

sudo netstat -tuln | grep 3306
# 查看mysql的监听端口号
sudo ss -tlnp | grep mysqld

MySQLbinlog

mysqlbinlog是MySQL官方工具，用于读取和解析二进制日志(Binary Log)。二进制日志记录了数据库的更改操作(如INSERT、UPDATE、DELETE等)主从复制等场景。

note

前提：二进制日志必须启用（log_bin=ON），否则无法使用 mysqlbinlog。

启用二进制

在MySQL配置文件(my.cnf 或 my.ini)中添加以下配置：

[mysqld]
log_bin = /path/to/mysql-bin
server_id = 1  # 唯一标识，必须设置

检查是否启用:

SHOW VARIABLES LIKE 'log_bin';

查看当前日志文件和位置

SHOW MASTER STATUS;

列出所有日志文件

SHOW BINARY LOGS;

mysqlbinlog基本使用

mysqlbinlog [options] /path/to/mysql-bin.000001

• log_file：二进制日志文件路径（例如 /var/lib/mysql/mysql-bin.000001）。
• [options]：控制输出格式、范围等。

常用选项

1. 输出控制

• --verbose, -v：将 ROW 格式的日志转换为伪 SQL 语句，显示修改前后的行数据。
• --base64-output=DECODE-ROWS：解码 ROW 格式的 Base64 编码数据，需配合 --verbose 使用。
• --short-form, -s：仅显示简单输出（不包含注释或元数据）。
• --hexdump：显示事件的十六进制转储，便于调试。

2. 范围过滤

• --start-position=#：从指定位置开始解析。
• --stop-position=#：在指定位置停止解析。
• --start-datetime="YYYY-MM-DD HH:MM:SS"：从指定时间开始解析。
• --stop-datetime="YYYY-MM-DD HH:MM:SS"：在指定时间停止解析。

3. 数据库和表过滤

• --database=db_name：仅解析指定数据库的日志。
• --table=tbl_name：仅解析指定表的日志（需配合 --database）。

4. 远程访问

• --read-from-remote-server：从远程服务器读取二进制日志。
• --host=host_name：远程服务器的主机名。
• --user=user_name：远程服务器的用户名。
• --password=password -p：远程服务器的密码（无空格，或提示输入）。
• --port=port_number：远程服务器的端口号（默认 3306）。
• --socket=socket_file：远程服务器的套接字文件路径。

5. 输出格式

• --result-file=file：将输出保存到指定文件。
• --raw：以原始二进制格式输出（用于复制或备份）。
• --binlog-row-event-max-size=#: 设置最大行事件大小（默认 4294967040 字节）。

6.其他选项

• --disable-log-bin：解析时禁用二进制日志，防止生成新日志。
• --character-sets-dir=path: 指定字符集文件路径。
• --set-charset=charset_name: 设置输出字符集。

使用示例

1. 查看二进制日志的基本内容

• 解析日志文件，显示所有事件：

  mysqlbinlog /var/lib/mysql/mysql-bin.000001

2. 解析 ROW 格式日志

• 如果二进制日志格式为 ROW（binlog_format=ROW），解码行数据：

  mysqlbinlog --base64-output=decode-rows -v /path/to/mysql-bin.000001

3. 按时间范围解析ROW格式日志

• 仅显示特定时间段的事件：

  mysqlbinlog --base64-output=decode-rows -v --start-datetime="2025-09-02 16:10:00" --stop-datetime="2025-09-02 16:30:32" /path/to/mysql-bin.000001

4. 过滤特定数据库或表

• 仅显示 hcb 数据库的 wxusers 表事件：

  mysqlbinlog --verbose --base64-output=DECODE-ROWS --database=hcb /var/lib/mysql/mysql-bin.000001 | grep -A 10 "wxusers"

5. 远程访问二进制日志

• 从远程服务器读取日志：

  mysqlbinlog --read-from-remote-server --host=localhost --user=root --password --port=3306 mysql-bin.000001

6. 保存输出到文件

• 将解析结果保存到文件：

  mysqlbinlog --verbose --base64-output=DECODE-ROWS --result-file=output.sql /var/lib/mysql/mysql-bin.000001

7. 恢复数据

• 将二进制日志转换为 SQL 并执行以恢复数据：

  mysqlbinlog --start-position=123 --stop-position=456 /var/lib/mysql/mysql-bin.000001 | mysql -u root -p