【MySQL篇】MySQL8.0新特性

1. MySQL 8.0的主要新特性有哪些？

回答：MySQL 8.0的核心新特性包括：

默认认证插件变更：从
mysql_native_password改为
caching_sha2_password，提升安全性。数据字典：用InnoDB存储的事务性数据字典替代原有的
.frm、
.MYD等文件，元数据管理更可靠。窗口函数：支持
ROW_NUMBER()、
RANK()、
SUM() OVER()等，简化复杂统计分析。公共表表达式（CTE）：包括普通CTE和递归CTE，优化复杂查询结构（如树形层级查询）。真正的降序索引：此前版本仅语法支持降序，8.0实现物理降序存储，提升
ORDER BY DESC性能。原子DDL：DDL操作要么全成功、要么全回滚，避免元数据不一致。InnoDB增强：自增锁优化（批量插入更高效）、双写缓冲区并行刷新、锁等待超时可配置。JSON功能增强：支持
JSON_TABLE()（JSON转关系表）、
JSON_SCHEMA_VALIDATE()（JSON结构验证）。角色管理：支持创建角色并批量授权，简化权限管理。直方图统计信息：为非均匀分布数据生成直方图，帮助优化器选择更优执行计划。

2. MySQL 8.0连接时因认证插件导致失败，如何解决？

回答：因默认
caching_sha2_password与旧客户端不兼容导致的认证失败，解决方案包括：

修改用户认证插件：将用户的认证方式改回
mysql_native_password：

ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

配置MySQL默认认证插件：在
my.cnf中添加：

default_authentication_plugin=mysql_native_password

重启MySQL生效。

升级客户端：使用支持
caching_sha2_password的客户端（如MySQL Connector/J 8.0+、Navicat 12+）。

3. MySQL 8.0窗口函数的作用？举例说明常用窗口函数。

回答：窗口函数（Window Function）允许在一组与当前行相关的行上计算值，不分组聚合（保留原表行数），适用于排名、累计统计等场景。

常用窗口函数及示例：


ROW_NUMBER()：为分组内每行分配唯一序号（无并列）：

SELECT 
  name, score, 
  ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS rn 
FROM student;


RANK()：分组内排名（并列跳号，如1,2,2,4）：

SELECT 
  name, score, 
  RANK() OVER(PARTITION BY class ORDER BY score DESC) AS rk 
FROM student;


SUM() OVER()：分组内累计求和：

SELECT 
  date, sales, 
  SUM(sales) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_sales 
FROM sales_data;

4. MySQL 8.0递归CTE如何实现树形结构查询（如部门层级）？

回答：递归CTE通过
WITH RECURSIVE实现树形结构遍历，适用于部门、分类等层级数据。示例（部门表
dept含
id、
name、
parent_id）：

WITH RECURSIVE dept_tree AS (
  -- 锚点查询：根节点（parent_id=0）
  SELECT id, name, parent_id, 1 AS level 
  FROM dept 
  WHERE parent_id = 0
  UNION ALL
  -- 递归查询：子节点关联父节点
  SELECT d.id, d.name, d.parent_id, dt.level + 1 
  FROM dept d
  JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

5. MySQL 8.0原子DDL的优势是什么？举例说明。

回答：原子DDL确保DDL操作（如
CREATE TABLE、
DROP INDEX）具备原子性：要么完全执行成功，要么完全回滚，避免元数据和存储数据不一致。

示例：创建表并同时创建索引，若索引创建失败，原子DDL会回滚表的创建；而MySQL 5.7中表会被创建，但索引失败，导致无效表存在：

-- MySQL 8.0中若索引创建失败，表不会被创建
CREATE TABLE test (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  INDEX idx_name(name)
) ENGINE=InnoDB;

6. MySQL 8.0中InnoDB自增锁的优化是什么？

回答：MySQL 5.7中InnoDB自增锁是表级锁，批量插入（如
INSERT ... SELECT）时会持有锁直到语句结束，导致并发插入性能差；MySQL 8.0优化为轻量级锁：

对于简单插入（如
INSERT INTO t VALUES (NULL, ...)），自增ID分配后立即释放锁；对于批量插入，仅在分配连续自增ID期间持有锁，大幅提升并发插入效率。

7. MySQL 8.0如何创建和使用直方图统计信息？

回答：直方图用于描述列数据分布，帮助优化器选择更优索引。创建方式：

-- 为表t的col列创建直方图（buckets为桶数，1-1024）
ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 10 BUCKETS;

查看直方图：

SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS 
WHERE TABLE_NAME = 't' AND COLUMN_NAME = 'col';

优势：对于非均匀分布列（如订单状态，大部分为“已完成”），直方图能让优化器准确判断行数，避免选错索引。

8. MySQL 8.0角色管理的使用场景和示例？

回答：角色是权限的集合，适用于批量管理多个用户的权限（如给所有DBA授予相同权限）。示例：

创建角色并授权：

CREATE ROLE 'dba_role';
GRANT SELECT, INSERT, UPDATE ON *.* TO 'dba_role';

给用户赋予角色：

GRANT 'dba_role' TO 'admin'@'localhost';

激活角色（用户登录后生效）：

SET DEFAULT ROLE 'dba_role' TO 'admin'@'localhost';

9. MySQL 8.0中真正的降序索引如何实现？优势是什么？

回答：MySQL 5.7及更早版本中，
ORDER BY DESC使用升序索引时需反向扫描，效率较低；MySQL 8.0支持物理降序索引，索引数据按降序物理存储。

创建降序索引示例：

CREATE INDEX idx_score_desc ON student(score DESC);

优势：

当查询包含
ORDER BY score DESC时，可直接使用降序索引，无需反向扫描，提升排序性能；复合索引中支持混合升降序（如
(col1 ASC, col2 DESC)），满足更复杂的排序需求。

10. MySQL 8.0 JSON功能增强有哪些？举例说明
JSON_TABLE()的用法。

回答：MySQL 8.0对JSON的增强包括
JSON_TABLE()（将JSON数据转为关系表）、
JSON_SCHEMA_VALIDATE()（验证JSON结构）、
JSON_VALUE()（提取标量值）等。


JSON_TABLE()示例：将JSON数组转为行数据

SELECT * 
FROM JSON_TABLE(
  '[{"name": "Alice", "age": 20}, {"name": "Bob", "age": 25}]',
  '$[*]' COLUMNS(
    name VARCHAR(20) PATH '$.name',
    age INT PATH '$.age'
  )
) AS jt;

结果会生成两行数据：Alice（20）、Bob（25）。

11. MySQL 8.0数据字典的作用是什么？与旧版本元数据管理有何区别？

回答：MySQL 8.0的数据字典是存储元数据（表结构、列信息、索引等）的事务性存储引擎（基于InnoDB），替代了旧版本的
.frm、
.MYI等文件存储方式。

区别：

旧版本元数据分散在文件系统中，易因文件损坏导致元数据丢失；数据字典存储在InnoDB中，支持事务和崩溃恢复，更可靠；元数据访问更高效，避免文件I/O开销；支持原子DDL，依赖数据字典的事务特性实现。

12. MySQL 8.0如何管理用户密码策略？举例说明密码强度要求配置。

回答：MySQL 8.0通过
validate_password插件（默认启用）管理密码策略，可配置密码长度、复杂度等要求。

配置示例：

-- 设置密码最小长度为8
SET GLOBAL validate_password.length = 8;
-- 要求密码包含大小写字母、数字和特殊字符
SET GLOBAL validate_password.policy = STRONG;
-- 密码中与用户名字符重复的最大长度为0（禁止包含用户名）
SET GLOBAL validate_password.check_user_name = ON;

查看密码策略配置：

SHOW VARIABLES LIKE 'validate_password%';

13. MySQL 8.0中InnoDB双写缓冲区的优化是什么？

回答：MySQL 8.0支持双写缓冲区并行刷新，旧版本双写缓冲区刷新是串行的，而8.0可并行刷新多个页到双写缓冲区和数据文件，提升I/O效率；同时，双写缓冲区的存储从共享表空间（
ibdata1）分离到独立的双写文件（
#ib_16384_0.dblwr等），减少共享表空间碎片化，进一步优化性能。

14. MySQL 8.0优化器有哪些改进？举例说明。

回答：MySQL 8.0优化器改进包括：

直方图统计信息：基于列数据分布生成直方图，优化器能更准确估算行数，避免选错索引；隐式类型转换优化：减少因类型不匹配导致的全表扫描（如字符串列与数字比较时的优化）；子查询优化：将相关子查询转为连接查询，提升执行效率；哈希连接（Hash Join）：MySQL 8.0.18+支持哈希连接，替代部分嵌套循环连接，优化大表连接性能。

哈希连接示例：

-- 大表t1和t2连接时，优化器会自动选择哈希连接（适合无索引的等值连接）
SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;

15. MySQL 8.0如何进行主从复制的GTID配置？

回答：GTID（全局事务标识符）确保主从复制的一致性和故障切换效率，MySQL 8.0默认支持GTID。

配置步骤：

主库
my.cnf配置：

server-id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin

从库
my.cnf配置：

server-id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log = mysql-relay-bin

从库执行复制配置：

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_pass',
  MASTER_AUTO_POSITION = 1; -- 基于GTID自动同步
START SLAVE;

16. MySQL 8.0中
caching_sha2_password认证插件的优势是什么？

回答：
caching_sha2_password是MySQL 8.0默认认证插件，优势包括：

安全性更高：基于SHA-256哈希算法，比
mysql_native_password的SHA-1更安全；支持缓存机制：客户端首次认证后，后续连接可使用缓存的凭证，减少计算开销；支持TLS加密连接，防止密码传输过程中被窃取。

17. MySQL 8.0如何配置用户密码过期策略？

回答：MySQL 8.0支持精细化的密码过期管理，可通过全局配置或用户级配置实现：

全局默认策略：设置所有用户密码默认过期时间（如90天）：

SET GLOBAL default_password_lifetime = 90;

用户级策略：单独指定用户密码过期规则（如永不过期）：

ALTER USER 'user'@'host' PASSWORD EXPIRE NEVER;
-- 或指定过期间隔（如30天）
ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL 30 DAY;

强制立即过期：要求用户下次登录必须修改密码：

ALTER USER 'user'@'host' PASSWORD EXPIRE;

18. MySQL 8.0中InnoDB锁等待超时如何配置？

回答：MySQL 8.0新增
innodb_lock_wait_timeout的会话级配置，并优化锁等待监控：

全局配置（my.cnf）：

innodb_lock_wait_timeout = 50  # 默认50秒

会话级临时配置：

SET SESSION innodb_lock_wait_timeout = 30;

查看锁等待：通过
performance_schema监控锁等待事件：

SELECT * FROM performance_schema.events_waits_current WHERE event_name LIKE '%lock%';

19. MySQL 8.0全文索引有哪些增强？

回答：MySQL 8.0对全文索引（FTS）的增强包括：

支持中文分词：通过
ngram分词插件实现中文全文检索（需创建索引时指定分词器）：

CREATE FULLTEXT INDEX idx_content ON article(content) WITH PARSER ngram;

布尔模式增强：支持更丰富的布尔运算符（如
+必须包含、
-排除）；全文索引优化：提升大文本数据的索引效率和查询性能。

20. MySQL 8.0日志管理有哪些改进？

回答：MySQL 8.0在日志方面的改进包括：

慢查询日志增强：支持按执行时间、行数等条件过滤，可通过
slow_query_log_filter配置：

SET GLOBAL slow_query_log_filter = 'exec_time,rows_examined';

通用日志和慢查询日志支持JSON格式：便于日志分析工具解析：

log_output = FILE
slow_query_log_format = JSON

二进制日志（binlog）优化：支持事务压缩（
binlog_transaction_compression），减少日志体积；新增
binlog_gtid_simple_recovery参数，加速GTID日志恢复。

21. MySQL 8.0临时表有哪些优化？

回答：MySQL 8.0对临时表的优化包括：

临时表存储引擎默认改为InnoDB（原MyISAM），支持事务和崩溃恢复；临时表元数据存储在内存中（而非数据字典），创建/销毁更高效；支持临时表索引的在线创建（
ALTER TABLE ... ADD INDEX），不阻塞读写。

22. MySQL 8.0权限管理新增了哪些特性？

回答：除角色管理外，MySQL 8.0权限管理增强包括：

列级权限细化：支持对表的特定列授予权限（如仅允许更新
name列）：

GRANT UPDATE(name) ON db.t TO 'user'@'host';

动态权限：新增如
SYSTEM_VARIABLES_ADMIN（系统变量管理）、
BACKUP_ADMIN（备份管理）等细粒度权限，替代高风险的
SUPER权限；权限回收增强：支持
REVOKE ... FROM ROLE批量回收角色权限。

23. MySQL 8.0备份恢复工具mysqldump有哪些改进？

回答：MySQL 8.0的mysqldump增强包括：

支持原子DDL备份：确保备份过程中DDL操作的一致性；并行备份：通过
--parallel参数实现多表并行导出，提升备份速度；GTID备份集成：默认包含GTID信息，便于主从恢复时定位位点：

mysqldump --single-transaction --master-data=2 --gtid --all-databases > backup.sql

24. MySQL 8.0性能_schema（性能模式）有哪些增强？

回答：MySQL 8.0性能_schema优化包括：

新增更多监控表：如
events_transactions_summary_by_account_by_event_name（按账户统计事务）、
file_summary_by_instance（文件I/O实例统计）；降低监控开销：优化性能_schema的采样机制，默认开启更多监控项但性能影响更小；支持索引监控：通过
sys.schema_unused_indexes查看未使用的索引，辅助优化：

SELECT * FROM sys.schema_unused_indexes;

25. MySQL 8.0对字符集的支持有哪些变化？

回答：MySQL 8.0字符集改进包括：

默认字符集改为utf8mb4（原latin1），全面支持Unicode（包括emoji）；移除过时字符集：如
ucs2、
utf16等，推荐使用
utf8mb4；utf8mb4性能优化：提升utf8mb4字符集的排序和索引效率。

26. MySQL 8.0分区表有哪些改进？

回答：MySQL 8.0对分区表的增强包括：

支持InnoDB临时分区表：临时分区表数据仅会话可见，适合临时数据处理；分区表索引优化：支持分区表的全文索引和空间索引；分区维护增强：新增
ALTER TABLE ... REORGANIZE PARTITION的并行操作，加速分区合并/拆分。

27. MySQL 8.0哈希连接（Hash Join）与嵌套循环连接的区别？适用场景是什么？

回答：哈希连接是MySQL 8.0.18+新增的连接算法，与传统嵌套循环连接的核心区别如下：

特性	哈希连接（Hash Join）	嵌套循环连接（Nested Loop）
核心原理	构建小表哈希表，大表逐行匹配哈希表	外层表遍历，内层表按条件查找（依赖索引）
索引依赖	不依赖连接列索引	依赖内层表连接列索引（否则全表扫描）
适用场景	大表+大表等值连接、无索引连接	小表+大表连接、有索引的连接
内存占用	需占用内存存储哈希表（可通过 join_buffer_size调整）	内存占用低（仅需存储外层表当前行）

示例（哈希连接自动触发）：

-- 无索引的大表等值连接，优化器自动选择哈希连接
SELECT * FROM order_info o JOIN user_info u ON o.user_id = u.id;

28. MySQL 8.0 GTID复制的优势与限制？

回答：

优势：

故障切换简单：无需手动查找binlog文件和位点，从库自动通过GTID定位未执行的事务；复制一致性高：GTID唯一标识全局事务，避免重复执行同一事务；易于监控：通过
SHOW SLAVE STATUS的
Executed_Gtid_Set和
Retrieved_Gtid_Set可直观查看复制进度。

限制：

不支持非事务引擎：仅InnoDB支持GTID（MyISAM无事务，无法保证GTID一致性）；不支持临时表操作：包含
CREATE TEMPORARY TABLE的事务无法通过GTID复制；需严格保证事务一致性：主库不能执行
SET GTID_NEXT = ANONYMOUS的匿名事务（会导致从库同步失败）。

29. MySQL 8.0如何使用
JSON_SCHEMA_VALIDATE()验证JSON结构？

回答：
JSON_SCHEMA_VALIDATE()用于验证JSON数据是否符合指定的JSON Schema（结构规则），适用于接口入参校验、数据存储校验等场景。

示例（验证用户信息JSON需包含
name（字符串）、
age（18+整数））：

-- 定义JSON Schema规则
SET @schema = '{
  "type": "object",
  "properties": {
    "name": {"type": "string"},
    "age": {"type": "integer", "minimum": 18}
  },
  "required": ["name", "age"]
}';
 
-- 验证合法JSON（返回1）
SELECT JSON_SCHEMA_VALIDATE(@schema, '{"name": "Alice", "age": 20}') AS valid;
 
-- 验证非法JSON（缺少age，返回0）
SELECT JSON_SCHEMA_VALIDATE(@schema, '{"name": "Bob"}') AS valid;

30. MySQL 8.0
validate_password插件的
policy参数取值有哪些？分别代表什么？

回答：
validate_password.policy控制密码复杂度要求，取值及含义：

0/LOW：仅检查密码长度（默认8位），无复杂度要求；1/MEDIUM（默认）：检查长度+包含大小写字母、数字、特殊字符中的至少3种；2/STRONG：检查长度+复杂度+密码不能包含用户名/主机名+密码历史（需开启
validate_password.history）。

配置示例（设置为STRONG级别）：

SET GLOBAL validate_password.policy = STRONG;
-- 开启密码历史，禁止重复使用最近3次密码
SET GLOBAL validate_password.history = 3;

31. MySQL 8.0 InnoDB缓冲池（innodb_buffer_pool_size）的优化建议？

回答：缓冲池是InnoDB的核心缓存（缓存数据页和索引页），优化建议：

设置合理大小：物理内存的50%-70%（如16GB内存设置为10GB），避免占用过多内存导致系统Swap；开启缓冲池分区：当缓冲池超过4GB时，设置
innodb_buffer_pool_instances = 4-8（按内存大小调整），减少锁竞争；预加载数据：重启后通过
SELECT * FROM table FORCE INDEX(PRIMARY)预热高频访问表；监控缓冲池命中率：通过
sys.schema_unused_indexes或
SHOW ENGINE INNODB STATUS查看，命中率应≥95%：

SELECT (1 - ( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests )) * 100 AS hit_rate FROM sys.schema_unused_indexes;

32. MySQL 8.0如何处理主从复制延迟？

回答：主从复制延迟的核心优化方向的是减少主库写入压力、提升从库同步效率：

主库优化：

开启binlog分组提交（
binlog_group_commit_sync_delay = 100），减少binlog刷盘次数；避免大事务（拆分批量插入/更新为小事务）。

从库优化：

开启并行复制（
slave_parallel_type = LOGICAL_CLOCK，
slave_parallel_workers = 4-8）；从库使用
innodb_flush_log_at_trx_commit = 2（牺牲部分一致性提升性能）；禁止从库执行慢查询、统计类SQL（避免占用资源）。

监控延迟：通过
SHOW SLAVE STATUS的
Seconds_Behind_Master查看延迟时间，超过30秒需排查。

33. MySQL 8.0
innodb_flush_log_at_trx_commit参数的取值及含义？

回答：该参数控制InnoDB日志（redo log）的刷盘策略，直接影响事务安全性和性能：

取值1（默认，ACID兼容）：每次事务提交时，redo log缓冲区数据刷盘并同步到磁盘（最安全，性能最低）；取值2：每次事务提交时，redo log缓冲区数据刷到操作系统缓存，但不强制同步到磁盘（依赖OS自动刷盘，崩溃可能丢失1秒内数据，性能中等）；取值0：每秒将redo log缓冲区数据刷盘并同步到磁盘（事务提交时不刷盘，崩溃可能丢失1秒内数据，性能最高）。

面试注意：生产环境核心业务建议设为1（保证数据安全），非核心业务（如日志）可设为2平衡性能。

34. MySQL 8.0如何查看慢查询日志？如何优化慢查询？

回答：

1. 查看慢查询日志：

开启慢查询日志：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2; -- 执行时间超过2秒的视为慢查询

查看慢查询日志文件路径：

SHOW VARIABLES LIKE 'slow_query_log_file';

用
mysqldumpslow工具分析：

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log # 查看Top10执行时间最长的慢查询

2. 慢查询优化思路：

给查询条件列、连接列创建索引（避免全表扫描）；优化SQL语句（减少
SELECT *、避免
JOIN过多表、拆分子查询）；调整数据库配置（如增大
join_buffer_size、
sort_buffer_size）；分库分表（针对超大表，如按时间分区）。

35. MySQL 8.0并行复制的原理是什么？如何配置？

回答：并行复制是从库同时启动多个线程（worker线程）执行主库的binlog事务，提升同步效率，核心原理：

MySQL 8.0默认使用逻辑时钟（Logical Clock） 算法：主库中同一组提交的事务（无冲突）分配相同的逻辑时钟，从库可并行执行。

配置步骤：

# 从库my.cnf配置
slave_parallel_type = LOGICAL_CLOCK # 并行复制类型（逻辑时钟）
slave_parallel_workers = 4 # 并行worker线程数（建议=CPU核心数的50%-70%）
slave_preserve_commit_order = ON # 保证从库事务提交顺序与主库一致

重启从库后生效，通过
SHOW SLAVE STATUS的
Slave_workers_running查看并行线程是否运行。

36. MySQL 8.0
JSON_VALUE()与
JSON_EXTRACT()的区别？举例说明。

回答：两者均用于提取JSON数据，核心区别是
JSON_VALUE()返回标量值（字符串、数字等），
JSON_EXTRACT()返回JSON格式值：


JSON_EXTRACT()（别名
->）：返回JSON类型，适用于提取对象/数组：

SELECT JSON_EXTRACT('{"name": "Alice", "hobbies": ["reading", "hiking"]}', '$.hobbies') AS hobbies;
-- 结果：["reading", "hiking"]（JSON数组）


JSON_VALUE()：返回标量值（自动转换为MySQL数据类型），适用于提取单个字段：

SELECT JSON_VALUE('{"name": "Alice", "age": 20}', '$.name') AS name;
-- 结果：Alice（字符串类型）

面试技巧：提取单个值用
JSON_VALUE()（更简洁），提取复杂结构用
JSON_EXTRACT()。

37. MySQL 8.0如何禁止root用户远程登录？

回答：为提升安全性，禁止root远程登录的配置步骤：

查看当前root用户的host：

SELECT user, host FROM mysql.user WHERE user = 'root';

删除root用户的远程访问权限（保留localhost）：

DROP USER 'root'@'%'; -- 删除所有远程主机的root权限
FLUSH PRIVILEGES;

若需远程管理，创建专用管理员用户：

CREATE USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

38. MySQL 8.0 InnoDB的缓冲池碎片如何查看和优化？

回答：缓冲池碎片是指缓冲池中未被充分利用的空闲空间，会导致内存浪费和性能下降：

1. 查看碎片率：

SELECT 
  CONCAT(ROUND((1 - (Innodb_buffer_pool_free_pages / Innodb_buffer_pool_total_pages)) * 100, 2), '%') AS buffer_pool_fragmentation
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_free_pages', 'Innodb_buffer_pool_total_pages');

碎片率超过20%需优化。

2. 优化方法：

开启缓冲池自动整理（MySQL 8.0默认开启）：

innodb_buffer_pool_dump_pct = 25 # 每次dump 25%的热数据
innodb_buffer_pool_load_at_startup = ON # 重启时加载热数据

手动整理碎片：

ALTER TABLE table_name ENGINE=InnoDB; -- 重建表（仅适用于独立表空间）

合理设置
innodb_buffer_pool_size（避免过小导致频繁淘汰数据）。

39. MySQL 8.0新增了哪些数据类型或对现有数据类型有何优化？

回答：MySQL 8.0对数据类型的增强包括：

新增
BIT_VALUE类型优化：更好支持位运算，简化位数据存储；
JSON类型增强：支持JSON数据的原子操作（如
JSON_SET()原子更新），并优化JSON存储结构，提升查询效率；时间类型精度提升：
DATETIME、
TIMESTAMP支持微秒级精度（6位小数），且
TIMESTAMP范围扩展至
1970-01-01 00:00:01.000000到
2038-01-19 03:14:07.999999；
ENUM/
SET类型优化：支持动态修改枚举值（无需重建表），如
ALTER TABLE t MODIFY COLUMN e ENUM('a','b','c')。

40. MySQL 8.0存储过程和函数有哪些改进？

回答：MySQL 8.0对存储过程/函数的优化包括：

支持递归存储过程：无需额外配置即可实现递归调用，简化复杂逻辑（如树形数据计算）；新增错误处理机制：增强
DECLARE HANDLER的灵活性，支持
SQLWARNING、
NOT FOUND等条件的精细化捕获；性能优化：存储过程执行计划缓存优化，减少重复解析开销；权限细化：创建存储过程不再强制要求
SUPER权限，仅需
CREATE ROUTINE权限。

示例（递归存储过程计算阶乘）：

DELIMITER //
CREATE PROCEDURE factorial(IN n INT, OUT result INT)
BEGIN
  IF n <= 1 THEN
    SET result = 1;
  ELSE
    CALL factorial(n-1, result);
    SET result = n * result;
  END IF;
END //
DELIMITER ;

41. MySQL 8.0如何配置审计日志？

回答：MySQL 8.0可通过
audit_log插件实现审计（默认未启用），步骤如下：

安装插件：

INSTALL PLUGIN audit_log SONAME 'audit_log.so'; -- Linux
INSTALL PLUGIN audit_log SONAME 'audit_log.dll'; -- Windows

配置审计规则（my.cnf）：

audit_log_format = JSON          # 日志格式（JSON/RAW）
audit_log_events = CONNECT,QUERY # 审计事件（连接、查询、表访问等）
audit_log_file = /var/log/mysql/audit.log # 日志路径
audit_log_include_accounts = 'admin@%' # 仅审计admin用户

查看审计日志：直接读取日志文件或通过工具解析JSON格式日志。

42. MySQL 8.0的sys schema有哪些常用场景？举例说明。

回答：sys schema基于information_schema和performance_schema封装，提供直观的性能监控和诊断视图，常用场景：

查看未使用的索引：

SELECT * FROM sys.schema_unused_indexes;

定位慢查询SQL：

SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

查看表空间使用情况：

SELECT * FROM sys.schema_table_statistics ORDER BY data_length DESC;

检测死锁：

SELECT * FROM sys.innodb_lock_waits;

43. MySQL 8.0如何处理InnoDB死锁？

回答：InnoDB死锁处理步骤：

开启死锁日志（默认开启）：

innodb_print_all_deadlocks = ON # 记录所有死锁到错误日志

查看死锁信息：

错误日志中搜索
Deadlock found；通过
SHOW ENGINE INNODB STATUS查看最新死锁详情。

避免死锁的优化：

按固定顺序访问表（如先更新A表再更新B表）；缩短事务执行时间（避免长事务持有锁）；降低隔离级别（如从REPEATABLE READ改为READ COMMITTED）；使用
SELECT ... FOR UPDATE SKIP LOCKED跳过锁定行。

44. MySQL 8.0临时表的隔离级别和生命周期有哪些特点？

回答：MySQL 8.0临时表（
CREATE TEMPORARY TABLE）特性：

隔离性：会话级隔离，不同会话的临时表相互不可见，同名临时表会覆盖全局表（会话内优先访问临时表）；生命周期：会话断开时自动销毁，或通过
DROP TEMPORARY TABLE手动删除；存储引擎：默认InnoDB，支持事务和崩溃恢复（重启MySQL后未提交的临时表数据丢失）；元数据存储：临时表元数据存储在内存中，不写入数据字典，创建/销毁更快。

45. MySQL 8.0如何管理InnoDB表空间？

回答：MySQL 8.0支持三种表空间管理模式：

独立表空间（默认）：每个表对应
.ibd文件，便于单表备份/迁移：

innodb_file_per_table = ON

通用表空间：多个表共享一个表空间文件，适合分区表或小表：

CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;
CREATE TABLE t (id INT) TABLESPACE ts1;

系统表空间：存储数据字典和undo日志（
ibdata1），可通过
innodb_data_file_path扩展大小：

innodb_data_file_path = ibdata1:12M:autoextend:max:10G

46. MySQL 8.0连接池配置的最佳实践是什么？

回答：连接池（如官方mysql-connector-j、第三方Druid）配置建议：

最大连接数（max_pool_size）：设置为
(CPU核心数 * 2) + 磁盘数（如8核CPU设为20），避免连接过多导致上下文切换；最小空闲连接（min_idle）：保留5-10个空闲连接，减少创建连接的开销；连接超时（connection_timeout）：设为30秒，释放长时间空闲的连接；MySQL端优化：

max_connections = 1000 # 最大连接数（根据服务器配置调整）
wait_timeout = 600     # 非交互连接超时时间
interactive_timeout = 600 # 交互连接超时时间

47. MySQL 8.0升级从5.7的注意事项有哪些？

回答：从MySQL 5.7升级到8.0的关键注意事项：

预检查：

运行
mysqlcheck --all-databases --check-upgrade检查兼容性；移除过时功能（如
query_cache相关配置、
mysql.user表的
password字段）。

认证插件：默认
caching_sha2_password，需确保客户端支持或修改为
mysql_native_password。数据字典升级：升级过程中会自动转换元数据到数据字典，需保证足够磁盘空间。配置文件更新：移除5.7中废弃的参数（如
innodb_large_prefix），适配8.0新参数。测试验证：升级后测试应用兼容性（如窗口函数、CTE的使用）、复制功能等。

48. MySQL 8.0如何实现读写分离？

回答：MySQL 8.0读写分离可通过中间件（如ProxySQL、MyCat）或应用层实现，核心步骤：

主从复制部署：主库写入，从库同步数据（开启GTID确保一致性）；中间件配置（以ProxySQL为例）：

配置主库（写节点）和从库（读节点）；规则路由：
SELECT语句路由到从库，
INSERT/UPDATE/DELETE路由到主库；

应用层优化：

使用读写分离数据源（如Spring Boot的
dynamic-datasource-spring-boot-starter）；强制走主库的场景（如实时性要求高的查询）添加注解或指定数据源。

49. MySQL 8.0对全文索引的ngram分词插件如何配置分词长度？

回答：ngram插件用于中文分词，默认分词长度为2（双字分词），可通过参数调整：

全局配置（my.cnf）：

ngram_token_size = 3 # 改为三字分词

会话级配置：

SET SESSION ngram_token_size = 1; # 单字分词（适合短文本）

创建索引时指定（需重启生效后）：

CREATE FULLTEXT INDEX idx_content ON article(content) WITH PARSER ngram;

注意：分词长度越小，索引越细但体积越大；长度越大，索引越粗但体积越小，需根据业务调整。

50. MySQL 8.0如何监控数据库连接数和状态？

回答：监控连接数和状态的常用方法：

查看当前连接数：

SHOW STATUS LIKE 'Threads_connected'; -- 当前连接数
SHOW STATUS LIKE 'Max_used_connections'; -- 历史最大连接数

查看连接详情：

SELECT * FROM information_schema.processlist WHERE COMMAND != 'Sleep'; -- 活跃连接

监控连接状态：

SHOW STATUS LIKE 'Connections'; -- 总连接数
SHOW STATUS LIKE 'Aborted_connects'; -- 失败连接数（排查连接异常）

通过performance_schema监控：

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_STATE IS NOT NULL;