MySQL面试题及详细答案 155道（061-080）

《前后端面试题》专栏集合了前后端各个知识模块的面试题，包括html，javascript，css，vue，react，java，Openlayers，leaflet，cesium，mapboxGL，threejs，nodejs，mangoDB，MySQL，Linux… 。

前后端面试题-专栏总目录

一、本文面试题目录

61. 如何查看MySQL中的死锁日志？

InnoDB存储引擎会记录死锁相关信息到错误日志中，可通过以下方式查看：

• 首先确定错误日志位置：执行SHOW VARIABLES LIKE 'log_error';，结果中的Value即为错误日志路径（如/var/log/mysql/error.log）。
• 直接查看日志文件：使用cat或tail等命令查看该文件，死锁信息通常包含LATEST DETECTED DEADLOCK关键字，日志中会详细记录死锁发生时的事务、SQL语句、锁定的资源等信息，帮助分析死锁原因。

示例死锁日志片段：

LATEST DETECTED DEADLOCK
------------------------
2023-10-01 10:00:00 0x7f1234567890
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 140737354125056, query id 1234 localhost root updating
UPDATE orders SET status = 1 WHERE id = 100*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 140737354132224, query id 1235 localhost root updating
UPDATE orders SET status = 2 WHERE id = 200
*** WE ROLL BACK TRANSACTION (2)

62. MySQL中什么是索引合并？

索引合并是MySQL优化器的一种策略，当查询条件中涉及多个索引列时，优化器可能会将多个单列索引合并使用，以提高查询效率。例如，当WHERE子句中使用OR连接两个索引列条件时，MySQL可能会分别使用两个索引查询，再合并结果。

示例：
假设有表users，id和name分别有单列索引，执行：

SELECT * FROM users WHERE id = 10 OR name = 'Alice';

优化器可能会分别使用id索引和name索引查询，再将结果合并，此时EXPLAIN的type列会显示index_merge。

注意：索引合并并非总是最优，复杂的合并可能导致性能下降，此时可考虑创建联合索引替代。

63. 如何在MySQL中实现行级锁？

InnoDB通过索引实现行级锁，只有通过索引条件检索数据时，才会使用行级锁；否则会使用表级锁。

共享锁（S锁）：允许事务读取一行数据，加锁方式为SELECT ... LOCK IN SHARE MODE;，其他事务可加共享锁，但不能加排他锁。

示例：

-- 事务1加共享锁
START TRANSACTION;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 此时事务2可加共享锁，但加排他锁会阻塞

排他锁（X锁）：允许事务更新或删除一行数据，加锁方式为SELECT ... FOR UPDATE;或默认的DML操作（INSERT/UPDATE/DELETE），其他事务不能加任何锁。

示例：

-- 事务1加排他锁
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 此时事务2加共享锁或排他锁都会阻塞

64. MySQL中char和varchar的区别？

特性	char	varchar
长度固定性	固定长度，不足补空格	可变长度，只存储实际数据长度
存储空间	占用固定空间（如char(10)占10字节）	额外1-2字节存储长度，实际数据占n字节
处理速度	快（无需计算长度）	稍慢（需处理长度信息）
适用场景	固定长度数据（如手机号、身份证号）	可变长度数据（如姓名、地址）

示例：

• char(10)存储"abc"时，实际存储"abc "（补7个空格）。
• varchar(10)存储"abc"时，实际存储"abc"（加1字节长度标识）。

65. 什么是MySQL的外键约束？如何创建外键？

外键约束用于保证两个表之间的参照完整性，即子表的外键列值必须匹配主表的主键列值或为NULL。

创建外键的语法：

-- 主表（用户表）
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL
);-- 子表（订单表），外键关联用户表id
CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,order_no VARCHAR(20),-- 外键约束CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE  -- 主表记录删除时，子表关联记录也删除ON UPDATE CASCADE  -- 主表记录更新时，子表关联记录也更新
);

外键作用：防止子表插入无效的关联数据，确保数据一致性。

66. MySQL中如何实现分页查询的优化？

普通分页（LIMIT offset, rows）在offset很大时效率低（需扫描大量数据后丢弃），优化方式如下：

1. 基于主键的分页（适用于主键有序）：

-- 第1页：LIMIT 0, 10
SELECT * FROM users WHERE id > 0 LIMIT 10;
-- 第2页：以上一页最后一条id为起点
SELECT * FROM users WHERE id > 10 LIMIT 10;

原理：通过主键过滤跳过前面的数据，避免全表扫描，效率极高。

2. 使用索引覆盖分页：
   若查询只需要部分字段，可通过索引覆盖避免回表，再关联查询完整数据：

-- 先通过索引获取分页的id
SELECT id FROM users ORDER BY create_time LIMIT 100000, 10;
-- 再通过id查询完整数据（假设id为主键索引）
SELECT * FROM users WHERE id IN (...);

No.	大剑师精品GIS教程推荐
0	地图渲染基础- 【WebGL 教程】 - 【Canvas 教程】 - 【SVG 教程】
1	Openlayers 【入门教程】 - 【源代码+示例 300+】
2	Leaflet 【入门教程】 - 【源代码+图文示例 150+】
3	MapboxGL【入门教程】 - 【源代码+图文示例150+】
4	Cesium 【入门教程】 - 【源代码+综合教程 200+】
5	threejs【中文API】 - 【源代码+图文示例200+】
6	Shader 编程 【图文示例 100+】

67. 什么是MySQL的回表查询？如何避免？

回表查询：当使用非聚簇索引（二级索引）查询时，索引叶子节点只存储索引键和主键，需通过主键到聚簇索引中查找完整数据，这个过程称为回表。

示例：
表users主键为id，name为二级索引，执行：

SELECT * FROM users WHERE name = 'Alice';

• 步骤1：通过name索引找到Alice对应的主键id=10。
• 步骤2：通过id=10到聚簇索引中查询完整数据（回表）。

避免回表：使用索引覆盖，即查询的字段都包含在二级索引中，无需回表。

示例：创建联合索引(name, age)，查询：

SELECT name, age FROM users WHERE name = 'Alice';

此时索引已包含所需字段，无需回表。

68. MySQL中drop、truncate和delete的区别？

操作	性质	事务支持	自增ID重置	空间释放
DELETE	DML语句，逐行删除	支持（可回滚）	不重置	不释放（需OPTIMIZE）
TRUNCATE	DDL语句，清空表	不支持（不可回滚）	重置	释放
DROP	DDL语句，删除表结构	不支持	-	释放

示例：

-- 删除数据（可回滚）
DELETE FROM users WHERE age < 18;-- 清空表（不可回滚，自增ID重置）
TRUNCATE TABLE users;-- 删除表（不可恢复）
DROP TABLE users;

69. 如何在MySQL中创建自增主键？

自增主键通过AUTO_INCREMENT关键字实现，特点是自动生成唯一值，通常作为表的主键。

创建方式：

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,  -- 自增主键name VARCHAR(50) NOT NULL
);

特性：

• 默认从1开始，每次插入自动+1。
• 若插入时指定id值，且该值未被使用，则会使用指定值，后续自增值为MAX(id) + 1。
• 若删除表中数据，自增值不会重置（除非TRUNCATE表）。

修改自增值起始值：

ALTER TABLE users AUTO_INCREMENT = 1000;  -- 下次插入从1000开始

70. MySQL中如何处理并发插入问题？

并发插入可能导致数据一致性问题（如重复数据），解决方式如下：

1. 使用唯一索引：对唯一字段创建唯一索引，防止重复插入。

-- 创建唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);-- 插入时忽略重复数据
INSERT IGNORE INTO users (username) VALUES ('test');-- 插入时若重复则更新
INSERT INTO users (username, age) VALUES ('test', 20) 
ON DUPLICATE KEY UPDATE age = 20;

2. 使用事务和锁：高并发场景下，通过SELECT ... FOR UPDATE加排他锁防止并发插入。

START TRANSACTION;
-- 检查数据是否存在（加锁防止其他事务插入）
SELECT * FROM users WHERE username = 'test' FOR UPDATE;
-- 若不存在则插入
INSERT INTO users (username) VALUES ('test');
COMMIT;

71. MySQL中什么是覆盖索引？如何创建？

覆盖索引：指查询的所有字段（SELECT子句）都包含在索引中，无需回表查询，可提高查询效率。

创建方式：根据查询字段创建包含这些字段的联合索引。

示例：
频繁执行查询：

SELECT id, name, age FROM users WHERE name = 'Alice';

此时可创建联合索引(name, age)（id为主键，自动包含在二级索引中），索引包含name、age和id，满足查询需求，实现覆盖索引。

72. MySQL中如何实现定时任务？

可通过EVENT（事件）实现定时任务，需先开启事件调度器：

-- 查看是否开启
SHOW VARIABLES LIKE 'event_scheduler';
-- 开启（临时）
SET GLOBAL event_scheduler = ON;
-- 永久开启需修改配置文件：event_scheduler = ON

创建定时任务示例：每天凌晨3点删除logs表中30天前的数据

DELIMITER //
CREATE EVENT clear_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS '2023-10-01 03:00:00'
DO
BEGINDELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //
DELIMITER ;

管理事件：

-- 查看事件
SHOW EVENTS;-- 禁用事件
ALTER EVENT clear_old_logs DISABLE;-- 删除事件
DROP EVENT clear_old_logs;

73. MySQL中如何查看表的结构？

常用命令：

1. DESCRIBE（或DESC）：查看表字段信息

DESCRIBE users;  -- 或 DESC users;

结果包含字段名、类型、是否为NULL、主键、默认值等。

2. SHOW CREATE TABLE：查看表创建语句（包含索引、引擎等详细信息）

SHOW CREATE TABLE users;

结果显示CREATE TABLE语句，可用于复制表结构。

3. INFORMATION_SCHEMA查询：更灵活的元数据查询

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'users' AND TABLE_SCHEMA = 'mydb';

74. MySQL中如何实现数据的导入和导出？

导出数据：

1. 使用mysqldump导出表结构和数据

# 导出整个数据库
mysqldump -u root -p mydb > mydb.sql# 只导出表结构
mysqldump -u root -p -d mydb > mydb_struct.sql# 只导出数据
mysqldump -u root -p -t mydb > mydb_data.sql

2. 使用SELECT ... INTO OUTFILE导出数据到文件

SELECT * FROM users INTO OUTFILE '/tmp/users.csv' 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

导入数据：

1. 使用SOURCE命令导入SQL文件

SOURCE /path/to/mydb.sql;

2. 使用LOAD DATA INFILE导入文件数据

LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

No.	大剑师精品GIS教程推荐
0	地图渲染基础- 【WebGL 教程】 - 【Canvas 教程】 - 【SVG 教程】
1	Openlayers 【入门教程】 - 【源代码+示例 300+】
2	Leaflet 【入门教程】 - 【源代码+图文示例 150+】
3	MapboxGL【入门教程】 - 【源代码+图文示例150+】
4	Cesium 【入门教程】 - 【源代码+综合教程 200+】
5	threejs【中文API】 - 【源代码+图文示例200+】
6	Shader 编程 【图文示例 100+】

75. 什么是MySQL的分区表？如何创建分区表？

分区表：将大表按规则拆分为多个小的子表（分区），逻辑上是一个表，物理上是多个文件，可提高查询效率（只扫描相关分区）。

常见分区类型：

• 范围分区：按列值范围分区（如按时间）。
• 列表分区：按列值枚举分区（如按地区）。
• 哈希分区：按哈希函数结果分区。

创建范围分区表示例（按create_time分年度存储）：

CREATE TABLE orders (id INT PRIMARY KEY,order_no VARCHAR(20),create_time DATETIME
)
PARTITION BY RANGE (YEAR(create_time)) (PARTITION p2021 VALUES LESS THAN (2022),PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024),PARTITION p_other VALUES LESS THAN MAXVALUE
);

查询2022年数据时，只扫描p2022分区：

SELECT * FROM orders WHERE YEAR(create_time) = 2022;

76. MySQL中如何查看和杀死慢查询进程？

1. 查看当前进程：

-- 查看所有进程
SHOW PROCESSLIST;-- 查看完整信息（包括SQL语句）
SHOW FULL PROCESSLIST;

结果中Id为进程ID，State为状态（如Sending data表示执行中），Info为执行的SQL。

2. 杀死慢查询进程：

-- 根据进程ID杀死
KILL 123;  -- 123为要终止的进程Id

场景：当某条SQL长时间执行导致阻塞时，可通过SHOW PROCESSLIST找到对应的Id，再用KILL终止。

77. MySQL中如何实现表的复制？

1. 复制表结构和数据：

-- 方法1：基于查询
CREATE TABLE users_copy AS SELECT * FROM users;-- 方法2：先复制结构，再插入数据
CREATE TABLE users_copy LIKE users;  -- 复制结构（包括索引）
INSERT INTO users_copy SELECT * FROM users;

2. 跨数据库复制：

-- 复制到其他库
CREATE TABLE db2.users_copy LIKE db1.users;
INSERT INTO db2.users_copy SELECT * FROM db1.users;

注意：CREATE TABLE ... AS SELECT不会复制索引和约束，CREATE TABLE ... LIKE会复制表结构、索引和约束。

78. 如何在MySQL中使用CASE表达式？

原理说明：
CASE表达式用于在SQL中实现条件逻辑，类似于其他编程语言中的if-else或switch语句。它允许根据条件返回不同的值，可以用于SELECT、WHERE、ORDER BY等子句中。

示例代码：

-- 简单CASE表达式
SELECT product_name,category,CASE categoryWHEN 'Electronics' THEN 'Tech'WHEN 'Clothing' THEN 'Apparel'ELSE 'Other'END AS category_group
FROM products;-- 搜索CASE表达式
SELECT order_id,total_amount,CASE WHEN total_amount > 1000 THEN 'High'WHEN total_amount > 500 THEN 'Medium'ELSE 'Low'END AS order_priority
FROM orders;

79. MySQL中的字符集转换是如何工作的？

原理说明：
MySQL中的字符集转换发生在数据存储、检索和比较过程中。当客户端和服务器字符集不一致时，MySQL会自动进行转换。转换规则基于连接参数、表定义和列定义的字符集设置。

示例代码：

-- 创建表时指定字符集
CREATE TABLE my_table (name VARCHAR(50) CHARACTER SET utf8mb4
) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- 查看当前连接的字符集设置
SHOW VARIABLES LIKE 'character_set%';-- 修改连接字符集
SET NAMES utf8mb4;

80. 在MySQL中如何进行表的重命名？

原理说明：
MySQL提供了RENAME TABLE语句用于重命名表，也可以使用ALTER TABLE语句。RENAME TABLE更高效，支持原子性操作，可以同时重命名多个表。

示例代码：

-- 使用RENAME TABLE语句
RENAME TABLE old_table TO new_table;-- 使用ALTER TABLE语句
ALTER TABLE old_table RENAME TO new_table;-- 批量重命名表
RENAME TABLE table1 TO new_table1,table2 TO new_table2;

二、155道面试题目录列表

文章序号	MySQL面试题155道
1	MySQL面试题及详细答案155道（01-20）
2	MySQL面试题及详细答案155道（20-40）
3	MySQL面试题及详细答案155道（41-60）
4	MySQL面试题及详细答案155道（61-80）
5	MySQL面试题及详细答案155道（81-100）
6	MySQL面试题及详细答案155道（101-120）
7	MySQL面试题及详细答案155道（121-140）
8	MySQL面试题及详细答案155道（141-155）