云扬のBlog

与你共享美好生活

【MySQL】版本特性全梳理

1 MySQL 5.5版本的优缺点

1.1 MySQL 5.5引入的新功能

  1. 半同步复制：为降低数据丢失风险，MySQL 5.5新增半同步复制机制。主服务器需在至少一个从服务器确认接收事务日志后才提交事务，在数据一致性与性能间实现平衡。
  2. 实时线程优化：针对实时数据流场景（如实时数据采集、实时报告生成等），优化了线程处理逻辑，提升实时数据处理效率。
  3. Buffer Pool分实例：支持将InnoDB Buffer Pool划分为多个独立实例，减少内存竞争，更高效利用多核处理器资源，间接提升查询响应速度。
  4. 元数据锁定（MDL）：执行DDL语句时自动获取元数据锁，避免并发修改表结构导致的数据不一致问题，保障事务间的正确性。
  5. 默认存储引擎切换为InnoDB：替代MyISAM成为默认存储引擎，借助InnoDB对事务、行级锁及外键约束的支持，增强数据完整性与故障恢复能力。
  6. 安全性增强：强化密码策略（如复杂度要求、过期设置）、支持SSL/TLS加密连接，并优化用户权限管理，提升系统抗未授权访问能力。

1.2 MySQL 5.5的缺点

  1. 全文搜索局限：全文索引仅支持MyISAM引擎，不兼容InnoDB——而InnoDB因支持事务更受推荐，这导致使用InnoDB时无法直接借助内置全文搜索功能，需迁移至MyISAM或依赖Sphinx、Solr等第三方工具实现高效文本检索。
  2. 单线程复制瓶颈：采用单线程复制机制，在高并发写入场景下易出现同步延迟。单一复制线程难以应对高负载，成为性能短板（后续版本通过多线程复制部分解决此问题）。
  3. 并发处理能力不足：面对大量并发请求时，易因锁竞争、缓存命中率低、查询优化不足等问题导致响应延迟甚至超时。此外，InnoDB缓冲池容量有限，限制了大规模数据场景的处理能力，需通过精细配置或分库分表等策略缓解压力。

1.3 思维导图

2 MySQL 5.6版本的优缺点

2.1 MySQL 5.6引入的新功能

MySQL 5.6通过多项功能升级，显著提升了数据库的性能、安全性与可管理性，核心特性如下：

  1. GTID复制支持：全局事务标识符（Global Transaction Identifiers，GTIDs）为复制提供唯一事务标识，简化主从关系配置与故障恢复。借助GTID，可快速识别并跳过错误事务，提升复制可靠性。
  2. 密码策略精细化：允许管理员配置更严格的密码规则（如长度、复杂度、定期更换要求），降低弱密码导致的安全风险。
  3. 元数据锁优化：改进此前版本中DDL语句可能导致的长时间表级锁问题，减少锁的持有时间与范围，提升并发处理能力。
  4. 库级别并行复制：突破单线程复制限制，支持不同数据库间的复制任务并行执行，加速多库环境下的同步效率。
  5. 查询效率优化（ICP与MRR）：
  • ICP（Index Condition Pushdown，索引条件下推）：将部分WHERE条件下推至存储引擎层过滤索引记录，减少不必要的I/O操作。
  • MRR（Multi-Range Read，多范围读取）：通过批量处理相邻数据块减少磁盘寻道，加速多范围数据检索。
  1. Undo Log独立存储：支持将undo log从系统表空间分离至独立文件，便于管理与备份，同时降低单一表空间过大的风险。

2.2 MySQL 5.6的缺点

  1. 并行复制效率受限：库级别并行复制仅在各数据库负载完全独立时有效，对于跨库操作的应用，可能因资源竞争降低性能。
  2. 全局参数不可动态调整：缓冲池大小、连接数限制等关键配置需修改配置文件并重启服务才能生效，无法应对突发流量或实时调优需求，影响高可用性。
  3. 无原生JSON支持：处理半结构化数据时需依赖额外解析逻辑，增加开发复杂度与查询成本。

2.3 思维导图

3 MySQL 5.7版本的优缺点

3.1 MySQL 5.7引入的新功能

MySQL 5.7在性能、可靠性与灵活性上实现显著突破，核心升级包括：

  • 组复制：基于分布式算法的高可用方案，可在多服务器间同步数据，即使遭遇网络故障或节点下线，仍能保障数据一致性，简化容错与扩展架构搭建。
  • WRITESET并行复制：通过识别无冲突的数据修改，允许主从复制中并行处理事务，减少同步延迟，提升复制效率。
  • 虚拟列：支持动态计算字段（基于其他列值实时生成，不实际存储），适用于频繁复杂计算场景，减少冗余存储。
  • 原生JSON支持：直接提供JSON数据的解析、搜索、更新等操作能力，兼顾半结构化数据存储与关系型数据库的优势。
  • 全局参数动态调整：多数配置选项可在线修改无需重启，便于管理员根据负载实时优化性能。
  • InnoDB临时表：临时表可存储于磁盘并使用InnoDB引擎，解决大容量临时数据的内存不足问题，保障访问效率。

3.2 MySQL 5.7的缺点

  1. 社区版端口配置受限：仅支持默认3306端口，非默认端口需手动配置，增加复杂度与潜在安全风险。
  2. 大表加列成本高：向大数据量表添加新列时，需创建临时表并复制数据，过程耗时且占用大量资源，可能影响应用可用性。
  3. 线程优先级均一性问题：所有后台线程共享相同优先级与资源，高并发场景下可能出现关键任务被低优先级任务阻塞的性能瓶颈。

3.3 思维导图

3.4 实验：虚拟列功能验证

步骤1：创建薪资表（含虚拟列）

create table salary_info ( 
  id int auto_increment primary key, 
  name varchar(10) comment '员工姓名', 
  salary_hourly DECIMAL(10,2) comment '时薪', 
  hours_worked int comment '工作时长（单位：小时）', 
  salary DECIMAL(10,2) as (salary_hourly * hours_worked)  -- 虚拟列：自动计算薪资
);

步骤2：插入测试数据

insert into salary_info(name,salary_hourly,hours_worked) values ('a',100,8);

步骤3：查询结果

select * from salary_info;

可直接获取虚拟列salary的计算结果（如800.00）。

4 MySQL 8.0的新特性

MySQL 8.0通过一系列突破性升级，强化了性能、安全性与开发效率，核心特性如下：

  • 事务性数据字典：将元数据从文件系统迁移至InnoDB存储引擎，借助ACID特性保障元数据变更的原子性（支持回滚/提交），提升系统稳定性。
  • 快速加列：针对大型表的列添加操作，可通过仅更新元数据实现即时完成，无需锁表或复制全表，显著减少停机时间。
  • 原子DDL：所有DDL语句原子执行（要么全成、要么全不执行），避免复杂结构变更导致的数据库不一致。
  • 不可见索引：支持临时“隐藏”索引（无需删除），便于测试索引对查询的影响，保留随时启用的灵活性。
  • 降序索引：直接创建按字段值降序排列的索引，简化倒序查询编写，提升特定场景性能。
  • 角色管理：支持定义权限集合（角色）并批量分配给用户，简化权限维护。
  • 资源组：可对线程的CPU资源访问进行管控，通过分组调整任务优先级，优化资源分配。
  • 全局变量持久化：部分全局参数的配置可跨重启保留，避免重复配置。
  • Hash Join算法：新增哈希连接实现，优化大数据集关联查询性能。
  • 多端口支持：除默认3306端口外，可配置额外服务端口，适配复杂网络环境。
  • 自增主键持久化：服务器重启后可正确恢复自增ID的最大值，保障连续性与唯一性。

5 MySQL新特性-事务性数据字典

5.1 事务性数据字典是什么

事务性数据字典是数据库管理系统的核心组件，用于存储表、字段、索引等结构信息。MySQL不同版本的实现差异如下：

  • 数据字典定义：集中存储数据库结构描述（如表名、字段类型、约束等），为应用访问数据提供基础元信息。
  • 8.0之前版本：元数据以文件形式存储，包括：
  • db.opt：数据库创建基本信息；
  • .frm文件：表结构定义；
  • .ibd文件：数据与索引实际存储。
  • 8.0版本事务性数据字典：将所有元数据迁移至InnoDB管理，借助其ACID特性保护元数据完整性。即使遭遇断电等异常，也可通过日志重做恢复，避免元数据丢失。

5.2 数据字典的好处

  • 保障元数据与业务数据的一致性；
  • 支持元数据操作的事务回滚与故障恢复；
  • 简化管理流程，提升系统扩展性与性能。

6 MySQL 8.0新特性-快速加列

6.1 8.0之前版本加列的问题

  • 易导致主从同步延迟；
  • 占用大量CPU、IO资源；
  • 操作耗时（随表数据量增长而增加）。

6.2 MySQL加列的三种算法

向表中添加新列时，MySQL支持三种算法，适用场景不同：

  1. COPY（复制）：
  • 过程：创建临时表，复制原表数据及新列信息后替换旧表；
  • 优势：兼容性强，适用于多数场景；
  • 劣势：耗时久、资源消耗大，过程中表会被锁定。
  1. INPLACE（就地更新）：
  • 过程：直接修改现有文件结构，避免全量复制；
  • 优势：比COPY更快、更省空间，部分场景支持在线操作；
  • 劣势：并非所有DDL都支持，可能自动降级为COPY模式，存在锁竞争风险。
  1. INSTANT（即时）：
  • 过程：仅更新内存元数据，不修改磁盘数据格式；
  • 优势：几乎瞬时完成，对应用影响极小；
  • 劣势：有约束（如不支持新增列设默认值）。

选择建议：

  • 追求速度：优先INSTANT；
  • 大规模数据集且需减少停机：选INPLACE；
  • 小型数据库或简单场景：选COPY。

6.3 快速加列的注意事项

  • 仅支持在表尾添加新列；
  • 需MySQL 8.0.12及以上版本；
  • ALGORITHM=INSTANT 不支持 DROP COLUMN 操作，该操作仍需要重建表。（会报错）。

6.4 实验：三种加列算法对比

步骤1：创建测试表

CREATE TABLE test (    
  id INT NOT NULL AUTO_INCREMENT,    
  name VARCHAR(50) NOT NULL,    
  age INT NOT NULL,    
  PRIMARY KEY (id) 
);

步骤2：插入测试数据

INSERT INTO test (name, age) VALUES ('小明', 20); 
INSERT INTO test (name, age) VALUES ('小红', 22); 
-- 循环插入以扩大数据量
insert into test(name, age) select name, age from test; 
......

步骤3：对比加列耗时

alter table test add column a varchar(10),algorithm=copy; 
alter table test add column b varchar(10),algorithm=inplace; 
alter table test add column c varchar(10),algorithm=instant;

结果显示INSTANT算法耗时显著低于其他两种。


步骤4：验证删除操作限制

alter table test drop column b,algorithm=instant;

执行会报错，（INSTANT不支持删除字段）

步骤4：验证混合操作限制

alter table test add column d varchar(10),drop column c,algorithm=instant;

执行会报错（INSTANT不支持同时增删字段）。

alter table test add column d varchar(10),drop column c,algorithm=inplace;

执行成功。

7 MySQL 8.0新特性-不可见索引

7.1 不可见索引的作用

  • 减少索引对写入操作的性能损耗（隐藏后不参与写入维护）；
  • 便于调试SQL（验证索引是否必要）；
  • 简化索引管理（无需删除即可临时禁用）。

7.2 不可见索引的使用

  1. 建表时定义不可见索引
create table invisible_test(
  id int not null auto_increment primary key,
  a int,
  b int,
  key idx_a(a) invisible  -- 定义不可见索引
);
  1. 修改索引可见性
-- 切换为可见
alter table invisible_test alter index idx_a visible;

-- 切换为不可见
alter table invisible_test alter index idx_a invisible;
  1. 查看索引可见性
-- 方式1：查看表结构
show create table invisible_test;

-- 方式2：查看索引详情
show index from invisible_test\G

8 MySQL版本选择建议

  • 兼容性：优先匹配应用程序的版本支持范围；
  • 安全性：选择包含最新安全补丁的版本，规避已知漏洞（如2022年因重大Bug被下架的MySQL 8.0.29）；
  • 性能：根据业务负载选择（例如MySQL 8.0相比5.5性能提升显著）；
  • 功能需求：需使用新特性（如JSON支持、快速加列）时，选择对应版本；
  • 官方支持：避免选用已停止更新或维护的版本（如超期的旧版本）。

9 MySQL 8.4的新特性

9.1 系统变量默认值变更

参数参数作用默认值变化
innodb_io_capacity控制每秒可用于 InnoDB 后台任务的 I/O 数默认值改成了10000，之前是200
innodb_buffer_pool_instancesInnoDB缓冲池的区域数之前默认值是8，如果innodb_buffer_pool_size<= 1G，则为1。
从8.4开始，如果innodb_buffer_pool_size<= 1G，则为1；如果innodb_buffer_pool_size>1G，则是在下面两个计算中选一个最小值：innodb_buffer_pool_size / innodb_buffer_pool_chunk_size这个结果的1/2；可用逻辑CPU数量的1/4
innodb_change_buffering决定哪些操作会使用change buffer之前的版本默认值是all，表示innodb_change_buffering会缓存插入、删除标记操作和后台发生的物理删除操作；从8.4开始，默认是none，表示不缓存这些修改操作
group_replication_consistency控制组复制的一致性级别默认值改成了BEFORE_ON_PRIMARY_FAILOVER，以前是EVENTUAL

9.2 MySQL 8.4其他变化

  • 密码认证调整：默认禁用mysql_native_password插件，如需启用，可在启动时添加--mysql_native_password=ON参数或在配置文件中设置mysql_native_password=ON；
  • 克隆插件优化：放宽版本限制，支持同一主版本内不同小版本间的克隆操作。

10 本章总结

版本新特性缺点
5.5的优缺点半同步复制、实时线程、Buffer Pool可拆分为多个Instances、元数据锁、默认存储引擎改成了InnoDB、安全性改进不支持全文搜索、不支持并行复制、并发能力差
5.6的优缺点支持GTID复制、元数据锁的优化、基于库的并行复制、Undo Log 可以保存在独立表空间中基于库的并行复制在大多数场景都不适用，很多全局参数不能动态修改，不支持JSON数据类型
5.7的优缺点组复制、基于WRITESET的并行复制、虚拟列、原生支持JSON类型、支持动态调整很多全局参数、支持临时表设置为InnoDB存储引擎社区版无额外端口、大表增加字段开销大、所有线程优先级都一样，并且都是共享的
8.0的新特性事务性数据字典、快速加列、不可见索引、降序索引、角色管理、资源组、持久化全局变量、hash join、额外端口、自增主键的持久化
8.4的优缺点系统变量默认值优化、默认禁用mysql_native_password插件以增强安全性、克隆插件支持同一主版本内不同小版本间的操作、性能与稳定性进一步提升
Tags:
Previous Article
Next Article

发表回复

Your email address will not be published. Required fields are marked *.

*
*

近期文章

站点统计

  • 0
  • 23
  • 58
  • 2
  • 59

归档

分类

Java (4) Linux (2) MySQL (44) 网站建设 (5) 读书笔记 (4)