MySQL面试题及详细答案 155道（041-060）
《前后端面试题》专栏集合了前后端各个知识模块的面试题，包括html，javascript，css，vue，react，java，Openlayers，leaflet，cesium，mapboxGL，threejs，nodejs，mangoDB，MySQL，Linux… 。
文章目录
- 一、本文面试题目录
- 41.如何优化MySQL中的全文搜索性能？
- 42.在MySQL中如何实现跨库查询？
- 43.MySQL中的JSON数据类型有什么特点？
- 44.如何在MySQL中使用地理信息系统（GIS）功能？
- 45.如何在MySQL中实施审计跟踪（Audit Trail）？
- 46.MySQL中的插件式认证机制是什么？
- 47.如何在MySQL中管理大事务？
- 48.MySQL中的多版本并发控制（MVCC）是如何工作的？
- 49.如何在MySQL中使用压缩表来节省存储空间？
- 50.MySQL中如何利用资源组（Resource Groups）优化查询性能？
- 51. 简述在MySQL数据库中引擎MyISAM和InnoDB的区别？
- 52. MySQL中有哪几种锁？
- 53. CHAR和VARCHAR的区别？
- 54. 主键和候选键有什么区别？
- 55. LIKE声明中的％和_是什么意思？
- 56. 实践中如何优化MySQL？
- 57. 优化数据库的方法有哪些？
- 58. 简单描述MySQL中索引、主键、唯一索引、联合索引的区别，对数据库的性能有什么影响（从读写两方面）？
- 59. BLOB和TEXT有什么区别？
- 60. 什么情况下设置了索引但无法使用？
- 二、155道面试题目录列表
一、本文面试题目录
41.如何优化MySQL中的全文搜索性能？
MySQL的全文搜索（Full-Text Search）在处理大量文本数据时可能存在性能瓶颈，优化方式主要包括以下几点：
- 合理设计全文索引
- 仅对需要搜索的字段创建全文索引，避免冗余索引。
- 示例：对
articles表的title和content字段创建联合全文索引
CREATE FULLTEXT INDEX idx_article_search ON articles(title, content); - 调整最小词长（ft_min_word_len）
- 默认情况下，MySQL会忽略长度小于4的词（英文），可通过配置文件修改
ft_min_word_len参数（需重启服务并重建索引）。
# my.cnf [mysqld] ft_min_word_len = 2 - 默认情况下，MySQL会忽略长度小于4的词（英文），可通过配置文件修改
- 使用布尔模式（BOOLEAN MODE）过滤无关结果
- 减少返回结果集大小，提高搜索效率。
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('+mysql -bad' IN BOOLEAN MODE); - 分区表结合全文索引
- 对大表按时间或类别分区，缩小搜索范围。
CREATE TABLE articles (id INT,title VARCHAR(255),content TEXT,publish_date DATE ) PARTITION BY RANGE (YEAR(publish_date)) (PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025) ); CREATE FULLTEXT INDEX idx_article_search ON articles(title, content); - 定期优化索引
- 使用
OPTIMIZE TABLE重建全文索引，消除碎片。
OPTIMIZE TABLE articles; - 使用
42.在MySQL中如何实现跨库查询？
跨库查询指在一个查询中访问多个数据库的表，实现方式如下：
- 使用
数据库名.表名直接访问- 适用于同一MySQL实例下的多个数据库。
-- 查询db1库的users表和db2库的orders表 SELECT u.name, o.order_no FROM db1.users u JOIN db2.orders o ON u.id = o.user_id; - 创建视图封装跨库逻辑
- 简化跨库查询的使用。
-- 在db1库中创建跨库视图 CREATE VIEW user_orders AS SELECT u.name, o.order_no FROM db1.users u JOIN db2.orders o ON u.id = o.user_id;-- 查询视图 SELECT * FROM user_orders WHERE name = '张三'; - 使用Federated存储引擎
- 可访问远程MySQL实例的表（需先启用Federated引擎）。
-- 启用Federated引擎（需在配置文件中添加 federated 参数并重启） CREATE TABLE remote_orders (order_id INT,user_id INT,order_no VARCHAR(50) ) ENGINE=FEDERATED CONNECTION='mysql://user:password@remote_host:3306/db2/orders';-- 像本地表一样查询 SELECT * FROM remote_orders WHERE user_id = 100; - 通过中间件实现（如MyCat、Sharding-JDBC）
- 适用于分布式数据库场景，支持复杂的跨库join和分库分表。
43.MySQL中的JSON数据类型有什么特点？
MySQL 5.7及以上版本支持JSON数据类型，主要特点如下：
- 原生存储与验证
- JSON数据以二进制格式存储，确保语法正确性，避免存储无效JSON。
CREATE TABLE products (id INT PRIMARY KEY,info JSON -- 存储产品的JSON信息 );-- 插入有效JSON INSERT INTO products VALUES (1, '{"name": "手机", "price": 3999}');-- 插入无效JSON会报错 INSERT INTO products VALUES (2, '{"name": "电脑"}'); -- 语法正确 INSERT INTO products VALUES (3, 'name: 电脑'); -- 报错（无效JSON） - 丰富的JSON函数
- 支持查询、修改JSON数据，如
JSON_EXTRACT、JSON_SET等。
-- 提取JSON字段 SELECT JSON_EXTRACT(info, '$.name') AS product_name FROM products; -- 简化写法 SELECT info->'$.name' AS product_name FROM products;-- 更新JSON字段 UPDATE products SET info = JSON_SET(info, '$.price', 4999) WHERE id = 1; - 支持查询、修改JSON数据，如
- 索引支持
- 可通过生成列（Generated Column）为JSON字段创建索引。
-- 创建生成列关联JSON字段 ALTER TABLE products ADD COLUMN name VARCHAR(255) GENERATED ALWAYS AS (info->>'$.name') STORED; -- 为生成列创建索引 CREATE INDEX idx_product_name ON products(name);-- 使用索引查询 SELECT * FROM products WHERE name = '手机'; - 适合存储半结构化数据
- 无需预先定义字段结构，灵活应对多变的数据格式（如用户配置、日志等）。
44.如何在MySQL中使用地理信息系统（GIS）功能？
MySQL支持GIS（地理信息系统）功能，可存储和处理地理空间数据（如点、线、面），主要操作如下：
- GIS数据类型
- 常用类型：
POINT（点）、LINESTRING（线）、POLYGON（面）等。
CREATE TABLE locations (id INT PRIMARY KEY,name VARCHAR(100),coord POINT -- 存储经纬度点 ); - 常用类型：
- 插入地理数据
- 使用
ST_GeomFromText()或POINT()函数创建地理对象。
-- 插入点数据（纬度，经度） INSERT INTO locations VALUES (1, '北京', POINT(39.9042, 116.4074)), (2, '上海', ST_GeomFromText('POINT(31.2304 121.4737)')); - 使用
- 查询地理数据
- 计算距离、判断包含关系等。
-- 计算两点之间的距离（单位：米） SELECT ST_Distance_Sphere((SELECT coord FROM locations WHERE id = 1),(SELECT coord FROM locations WHERE id = 2)) AS distance_meters;-- 判断点是否在多边形内（例如：判断点是否在北京市范围内） SET @beijing_area = ST_GeomFromText('POLYGON((...))'); -- 多边形坐标 SELECT ST_Contains(@beijing_area, coord) AS is_in_beijing FROM locations WHERE id = 1; - 创建空间索引
- 优化地理数据查询性能。
-- 为POINT类型字段创建空间索引 CREATE SPATIAL INDEX idx_coord ON locations(coord);
45.如何在MySQL中实施审计跟踪（Audit Trail）？
审计跟踪用于记录数据库的操作行为（如登录、CRUD操作），便于安全审计和故障排查，实现方式如下：
- 使用触发器记录操作
- 对表的增删改操作创建触发器，记录到审计表。
-- 创建审计表 CREATE TABLE audit_log (id INT AUTO_INCREMENT PRIMARY KEY,table_name VARCHAR(50),operation VARCHAR(10), -- INSERT/UPDATE/DELETEuser VARCHAR(50),operation_time DATETIME,old_data JSON, -- 旧数据（UPDATE/DELETE）new_data JSON -- 新数据（INSERT/UPDATE） );-- 对users表创建INSERT触发器 DELIMITER // CREATE TRIGGER trg_users_insert AFTER INSERT ON users FOR EACH ROW BEGININSERT INTO audit_log (table_name, operation, user, operation_time, new_data)VALUES ('users', 'INSERT', CURRENT_USER(), NOW(), JSON_OBJECT('id', NEW.id, 'name', NEW.name)); END // DELIMITER ; - 启用MySQL企业版审计插件
- 企业版提供
audit_log插件，支持更全面的审计功能（需单独安装）。
# my.cnf 配置插件 [mysqld] plugin-load-add=audit_log.so audit_log_format=JSON # 日志格式（JSON/CSV） audit_log_file=/var/log/mysql/audit.log - 企业版提供
- 通过数据库代理记录日志
- 使用MyCat、ProxySQL等中间件，拦截所有SQL操作并记录日志。
- 监控二进制日志（binlog）
- 通过解析binlog获取数据变更记录（需开启binlog）。
-- 开启binlog SET GLOBAL log_bin = ON;
46.MySQL中的插件式认证机制是什么？
MySQL的插件式认证机制允许通过插件自定义用户认证方式，替代默认的密码认证，主要特点如下：
- 默认认证插件
- MySQL 5.7及以上默认使用
mysql_native_password，8.0及以上默认使用caching_sha2_password。
-- 查看用户使用的认证插件 SELECT user, host, plugin FROM mysql.user; - MySQL 5.7及以上默认使用
- 常见认证插件
mysql_native_password：传统密码哈希认证。caching_sha2_password：更安全的SHA-256哈希，支持缓存。sha256_password：基于SHA-256的认证（无缓存）。auth_socket：通过操作系统用户认证（适用于本地用户）。
- 修改用户认证插件
-- 将用户认证插件改为mysql_native_password ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; - 自定义认证插件
- 可通过C API开发自定义插件，实现如LDAP、OAuth2等认证方式（需编译并安装到MySQL插件目录）。
# 配置自定义插件 [mysqld] plugin-load-add=my_auth_plugin.so
47.如何在MySQL中管理大事务？
大事务（如批量插入/更新大量数据）可能导致锁表、日志膨胀等问题，管理方式如下：
- 拆分事务为小批次
- 将大事务拆分为多个小事务，减少锁持有时间。
-- 批量更新拆分为小批次 SET autocommit = 0; DECLARE i INT DEFAULT 0; WHILE i < 100000 DOUPDATE orders SET status = 1 WHERE id BETWEEN i AND i + 1000;COMMIT;SET i = i + 1001; END WHILE; - 调整事务隔离级别
- 对非核心业务使用较低的隔离级别（如
READ COMMITTED），减少锁竞争。
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; - 对非核心业务使用较低的隔离级别（如
- 优化SQL语句
- 避免在大事务中执行复杂查询或全表扫描。
- 监控事务日志
- 确保undo log和redo log有足够空间，避免事务中断。
# 调整日志大小（my.cnf） innodb_log_file_size = 1G innodb_log_buffer_size = 64M - 设置事务超时时间
- 防止大事务长时间阻塞。
-- 设置事务超时时间（秒） SET GLOBAL innodb_lock_wait_timeout = 60; -- 锁等待超时
48.MySQL中的多版本并发控制（MVCC）是如何工作的？
MVCC（Multi-Version Concurrency Control，多版本并发控制）是InnoDB存储引擎实现隔离级别的核心机制，通过保存数据的多个版本，实现读写不冲突，工作原理如下：
- 核心概念
- 隐藏列：InnoDB为每行数据添加
DB_TRX_ID（事务ID）和DB_ROLL_PTR（回滚指针）。DB_TRX_ID：记录最后修改该行的事务ID。DB_ROLL_PTR：指向undo log中该记录的历史版本。
- undo log：保存数据的历史版本，用于事务回滚和MVCC查询。
- read view：事务启动时生成的一致性视图，用于判断数据版本的可见性。
- 隐藏列：InnoDB为每行数据添加
- 工作流程
- 写操作：修改数据时，先复制旧版本到undo log，再更新当前行的
DB_TRX_ID和DB_ROLL_PTR。 - 读操作：根据当前事务的read view，从undo log中选择可见的历史版本（避免加锁）。
- 写操作：修改数据时，先复制旧版本到undo log，再更新当前行的
- 隔离级别与MVCC
- READ COMMITTED：每次查询重新生成read view，只能看到已提交的版本。
- REPEATABLE READ：事务启动时生成一次read view，确保多次查询结果一致。
- 优势
- 读写不阻塞，提高并发性能。
- 支持不同隔离级别，满足多样化需求。
49.如何在MySQL中使用压缩表来节省存储空间？
MySQL支持表级和页级压缩，减少存储空间并可能提升I/O性能，实现方式如下：
- InnoDB表压缩
- 通过
ROW_FORMAT=COMPRESSED启用，适用于读多写少的场景。
-- 创建压缩表 CREATE TABLE compressed_data (id INT PRIMARY KEY,content TEXT ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; -- 压缩块大小（4/8/16KB）- 注意：压缩会增加CPU开销，写入性能可能下降。
- 通过
- MyISAM表压缩
- 使用
myisampack工具压缩表（只读，无法写入）。
# 压缩表（需先停止MySQL或锁定表） myisampack /var/lib/mysql/dbname/mytable.MYI - 使用
- 分区表压缩
- 对分区表的特定分区启用压缩，平衡性能和存储。
CREATE TABLE logs (id INT,log_time DATETIME ) PARTITION BY RANGE (YEAR(log_time)) (PARTITION p2023 VALUES LESS THAN (2024) ROW_FORMAT=COMPRESSED,PARTITION p2024 VALUES LESS THAN (2025) ); - 压缩配置参数
# my.cnf 配置InnoDB压缩 [mysqld] innodb_compression_level = 6 # 压缩级别（1-9，越高压缩率越好但CPU开销大） innodb_compression_failure_threshold_pct = 5 # 压缩失败阈值
50.MySQL中如何利用资源组（Resource Groups）优化查询性能？
MySQL 8.0及以上版本支持资源组（Resource Groups），可对CPU资源进行分配和限制，优化查询性能，使用方式如下：
- 创建资源组
- 定义资源组的CPU核心限制和优先级。
-- 创建高优先级资源组（使用CPU核心0-1） CREATE RESOURCE GROUP high_priority TYPE = USER VCPU = 0-1 PRIORITY = 10; -- 优先级（1-10，越高优先级越高）-- 创建低优先级资源组（使用CPU核心2-3） CREATE RESOURCE GROUP low_priority TYPE = USER VCPU = 2-3 PRIORITY = 1; - 将会话或语句分配到资源组
-- 将当前会话分配到高优先级组 SET RESOURCE GROUP high_priority;-- 为特定语句指定资源组（需开启变量） SET GLOBAL resource_group_enforce = ON; SELECT /*+ RESOURCE_GROUP(high_priority) */ * FROM large_table; - 管理资源组权限
- 授予用户管理资源组的权限。
GRANT RESOURCE_GROUP_ADMIN ON *.* TO 'dba'@'localhost'; - 适用场景
- 为核心业务查询分配高优先级CPU资源。
- 限制批量任务（如数据导入）对CPU的占用，避免影响在线业务。
- 隔离不同用户或应用的资源使用。
| 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+】 |
51. 简述在MySQL数据库中引擎MyISAM和InnoDB的区别？
- 存储结构：MyISAM每个表在磁盘上存储成三个文件，.frm文件存储表定义，数据文件扩展名为.MYD，索引文件扩展名为.MYI。InnoDB所有表通常保存在同一个数据文件中，表大小受限于操作系统文件大小，一般为2GB。
- 存储空间：MyISAM可被压缩，存储空间较小，支持静态表、动态表、压缩表三种存储格式。InnoDB需要更多内存和存储，会在主内存中建立专用缓冲池用于高速缓冲数据和索引。
- 事务支持：MyISAM强调性能，执行速度快，但不提供事务支持。InnoDB提供事务支持。
- 表锁差异：MyISAM只支持表级锁。InnoDB支持事务和行级锁。
- 表主键：MyISAM允许没有索引和主键的表存在，索引保存行的地址。InnoDB如果没有设定主键或非空唯一索引，会自动生成一个6字节的主键。
- 增删改查操作：大量SELECT操作时，MyISAM是更好选择。大量INSERT或UPDATE操作时，InnoDB性能更优。
- 外键支持：MyISAM不支持外键，InnoDB支持外键。
52. MySQL中有哪几种锁？
- 表级锁：开销小，加锁快，不会出现死锁。但锁定粒度大，发生锁冲突概率最高，并发度最低。
- 行级锁：开销大，加锁慢，会出现死锁。不过锁定粒度最小，发生锁冲突概率最低，并发度最高。
- 页面锁：开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度也介于两者之间，并发度一般。
53. CHAR和VARCHAR的区别？
CHAR列长度固定为创建表时声明的长度，范围是1到255。存储时，会用空格填充到指定长度，检索时需删除尾随空格。VARCHAR是可变长度字符串，存储时只保存实际字符，加上用于记录长度的字节（长度小于255时用1字节记录，否则用2字节），能节省存储空间，适合存储长度不确定的数据。
54. 主键和候选键有什么区别？
表格的每一行都由主键唯一标识，一个表只有一个主键，主键也是候选键。候选键是可以唯一标识表中每一行的列或列组合，按照惯例，候选键可以被指定为主键，并且可用于外键引用。
55. LIKE声明中的％和_是什么意思？
％对应于0个或更多字符，可用于模糊匹配多个字符。例如，SELECT * FROM users WHERE username LIKE '%admin%'，会查询出用户名中包含“admin”的所有用户。_只是LIKE语句中的一个字符，用于匹配单个任意字符。如SELECT * FROM users WHERE username LIKE 'a_d'，会查询出用户名是3个字符，且第一个字符是“a”，第三个字符是“d”的用户。
56. 实践中如何优化MySQL？
- SQL语句及索引的优化：分析查询语句，为频繁查询、排序、分组的列建立合适索引，避免全表扫描；优化JOIN操作，确保连接条件正确；避免在索引列上进行计算、使用函数等。
- 数据库表结构的优化：选取合适的字段数据类型，尽量使用较小的数据类型存储数据，减少字段宽度；合理使用NOT NULL约束；对于取值范围固定的字段，可使用ENUM类型。
- 系统配置的优化：根据服务器硬件资源，合理配置MySQL的参数，如缓冲池大小、线程池大小等。
- 硬件的优化：升级服务器硬件，如增加内存、使用更快的磁盘（如SSD）等，提高数据库的读写性能。
57. 优化数据库的方法有哪些？
- 选取合适字段属性：尽可能减少定义字段宽度，把字段设置为NOT NULL，对于“省份”“性别”等字段，可使用ENUM类型。
- 优化查询语句：使用连接（JOIN）代替子查询，用联合（UNION）代替手动创建的临时表。
- 事务处理：合理使用事务，锁定表时优化事务处理，如减少锁持有的时间，避免长事务。
- 使用外键：合理使用外键可优化锁定表，但要注意外键会增加数据插入、更新的开销。
- 建立索引：为频繁查询的列建立索引，但要避免过度索引，对于查询很少涉及或重复值多的列，不宜建立索引。
58. 简单描述MySQL中索引、主键、唯一索引、联合索引的区别，对数据库的性能有什么影响（从读写两方面）？
- 索引：是一种特殊文件，包含对数据表里所有记录的引用指针，可提高数据查询速度，但会降低插入、删除、更新表的速度，因为写操作时要同时操作索引文件。
- 主键：是一种特殊的唯一索引，一张表中只能定义一个主键索引，用于唯一标识一条记录，使用
PRIMARY KEY创建。主键可加速数据查询，对插入数据有一定性能影响，因为要确保唯一性和更新索引。 - 唯一索引：能保证数据记录的唯一性，允许被索引的数据列包含NULL值，一张表可有多条唯一索引。查询性能上与普通索引类似，插入和更新时，需检查唯一性，性能略低于普通索引。
- 联合索引：可覆盖多个数据列，如
INDEX(columnA, columnB)。若查询条件能匹配联合索引的前列，可以提高查询性能；若不能匹配前列，则可能无法发挥索引作用。写入数据时，由于涉及多个列的索引维护，性能影响比单个索引更大。
59. BLOB和TEXT有什么区别？
BLOB是二进制对象，可容纳可变数量的数据。TEXT是不区分大小写的BLOB。两者唯一区别在于对BLOB值进行排序和比较时区分大小写，对TEXT值不区分大小写。BLOB主要用于存储二进制数据，如图像、音频、视频等；TEXT用于存储文本数据，如文章内容等。
60. 什么情况下设置了索引但无法使用？
- 以“%”开头的LIKE语句，如
SELECT * FROM table WHERE column LIKE '%value'，这种模糊匹配会导致索引失效，因为无法通过索引快速定位数据。 - OR语句前后没有同时使用索引，若
OR连接的列中只有部分列有索引，可能导致索引无法使用。例如SELECT * FROM table WHERE column1 = 'value1' OR column2 = 'value2'，若column2没有索引，可能全表扫描。 - 数据类型出现隐式转化，如
varchar类型字段不加单引号，可能会自动转换为int型，导致索引无法使用。例如SELECT * FROM table WHERE varchar_column = 123，若varchar_column有索引，也可能因类型转换而不使用索引。
二、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） |