目录
一：前置知识点
要学习如何优化，首先要对 MySQL 的逻辑架构深入的了解。图1是MySQL 逻辑架构图，可以让我们更清晰了解 MySQL 的运行原理。
图 1中，最上层是一些客户端和连接服务，包含本地sock 通信和大多数基于客户端/服务器端工具实现的TCP/IP 通信。主要完成一些连接处理、授权认证、及相关的安全方案等。在该层上引入了线程池的概念，为通过安全认证接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
第二层架构主要完成大多数的核心服务功能，如SL 接口、缓存的查询、SQL 的分析和优化以及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层上服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API与存储引擎进行通信。不同的存储引警具有的功能不同，可以根据自己的实际需要进行选取。数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
二：案例实施
1.MySQL单实例故障排查
（1）故障现象 1
ERROR 2002 (HY000):Can't connect to local MySQL server through socket/data/mysql/mysql.sock’(2)
问题分析:以上这种情况一般都是数据库未启动、mysql配置文件未指定 socket文件或者数据库端口被防火墙拦截导致。
解决方法:启动数据库或者防火墙开放数据库监听端口。
(2)故障现象2
ERROR 1045(28000):Access denied for user root'@ localhost’(usingpassword: NO)
问题分析:密码不正确或者没有权限访问。
解决方法:
> 修改 my.cnf 主配置文件,在[mysqld]下添加 skip-grant-tables=on，重启数据库。最后修改密码命令如下。
Mysq15.7版本
mysql> update mysql.user set authentication string=password('123456’)
where user=‘root’
and Host ='localhost’;
mysql> flush privileges；Mysq18.0
mysql> UPDATE mysql.user SET authentication_string='' WHERE user='root'
AND Host='localhost’;
mysql> FLUSH PRIVILEGES;
mysq1> ALTER USER 'root'@’localhost' IDENTIFIED BY '123456’再删除刚刚添加的 skip-grant-tables 参数，重启数据库，使用新密码即》可登录。重新授权，命令如下。
Mysq15.7
mysql> grant all on *.* to 'root'@'mysql-server' identified by ’123456';
Mysq18.0
mysq1> CREATE USER 'root'@’mysql-server' IDENTIFIED BY '123456’;
mysql> GRANT all ON *.* TO 'root'@'mysql-server’;(3) 故障现象3
在使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题
问题分析:如果 MySQL主机查询DNS 很慢或是有很多客户端主机时会导致连接很慢.由于开发机器是不能够连接外网的，在进行MySQL 连接时，DNS 解析是不可能完成的，从而也就明白了为什么连接那么慢了。
解决方法:修改 my.cnf 主配置文件，在[mysqld]下添加 skip-name-resolve,重启数据库可以解决。注意在以后授权里面不能再使用主机名授权。
（4）故障现象4
Can't open file:'xxx forums.MYI’.(errno:145
问题分析:
服务器非正常关机，数据库所在空间已满，或一些其它未知的原因，对数据库表造成了损坏。
可能是操作系统下直接将数据库文件拷贝移动，会因为文件的属组问题而产生这个错误.
解决方法:
可以使用下面的两种方式修复数据表(第一种方法仅适合独立主机用户)：
使用 MySQL 自带的专门用户数据表检查和修复工具myisamchk。一般情况下只有在命令行下面才能运行 myisamchk 命令。常用的修复命令为:
myisamchk -r 数据文件目录/数据表名.MYI;
通过 phpMyAdmin 修复，phpMyAdmin 带有修复数据表的功能，进入到某一个表中后，点击“操作”在下方的“表维护”中点击“修复表”即可。
注意:以上两种修复方式在执行前一定要备份数据库，
修改文件的属组(仅适合独立主机用户):
复制数据库文件的过程中没有将数据库文件设置为MySQL运行的帐号可读写(一般适用于 Linux和FreeBSD 用户)。
（5）故障现象 5
InnoDB: Error: page 14178 logsequence number 29455369832
InnoDB: is in the future! Current system log sequence number 29455369832
问题分析:innodb 数据文件损坏。
解决方法:修 my.cnf 配置文件,在[mysqld]下添加 innodb force recovery=4,启动数据库后备份数据文件，然后去掉该参数，利用备份文件恢复数据。
2.MySQL主从故障排查
| 故障现象 | 问题分析 | 解决方法 |
|---|---|---|
| 从库的Slave_IO_Running为NO （主从server-id冲突） |
主库和从库的server-id值相同，导致复制线程停止 |
1. 修改从库的my.cnf配置文件中的server-id为与主库不同的值2. 重启MySQL服务 3. 重新启动复制： STOP SLAVE; START SLAVE; |
| 从库的Slave_IO_Running为NO （数据冲突或丢失） |
主键冲突、主库删除/更新数据导致从库找不到记录（错误码：1007、1032、1062、1452等） | 方法一： 1. STOP SLAVE;2. SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;（跳过1个错误事件）3. START SLAVE;方法二： 设置从库为只读模式： SET GLOBAL read_only=TRUE; |
| Error initializing relay log position （中继日志损坏） |
从库的relay-bin中继日志文件损坏 |
1. 停止复制：STOP SLAVE;2. 清除并重置中继日志： - RESET SLAVE;（清除所有复制信息）- 或手动删除 relay-log.*文件后重启复制3. 重新配置复制起点： CHANGE MASTER TO MASTER_LOG_FILE='主库当前binlog', MASTER_LOG_POS=位置;4. 启动复制： START SLAVE; |
server-id冲突：主从库必须具有唯一
server-id，通常主库为1，从库递增（2,3,...）。数据冲突：若频繁出现数据不一致，需检查应用逻辑或考虑重做主从同步。
中继日志损坏：可能是磁盘故障或异常关机导致，建议定期监控从库状态。
3.MySQL优化
（1）硬件方面
说到服务器硬件，最主要的无非 CPU、内存、磁盘三大关键因素。
1.关于 CPU
CPU 对于 MySQL 应用，推荐使用 S.M.P.架构的多路对称 CPU。例如:可以使用两颗 IntelXeon 3.6GHz的 CPU。现在比较推荐用 4U 的服务器来专门做数据库服务器，不仅仅是针对于 MySQL。
2.关于内存
物理内存对于一台使用 MySQL 的 Database Server 来说，服务器内存建议不要小于 2GB，推荐使用 4GB 以上的物理内存。不过内存对于现在的服务器而言可以说是一个可以忽略的问题,工作中遇到了高端服务器基本上内存都超过了32G。
3.关于磁盘
磁盘寻道能力(磁盘 I/0)。以目前市场上普遍高转速 SAS 硬盘(15000 转/秒)为例，这种硬盘理论上每秒寻道 15000 次，这是物理特性决定的，没有办法改变。 MySQL 每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以通常认为磁盘 I/0 是制约 MySQL 性能的最大因素之一，通常是使用RAID-0+1 磁盘阵列，注意不要尝试使用 RAID-5，MySQL 在 RAID-5 磁盘阵列上的效率并不高。如果不考虑硬件的投入成本，也可以考虑固态(SSD)硬盘专门作为数据库服务器使用。数据库的读写性能肯定会提高很多。
（2）MySQL配置文件
1.核心性能优化项
| 参数 | 作用 | 建议配置 | 注意事项 |
|---|---|---|---|
| innodb_buffer_pool_size | InnoDB缓冲池大小，用于缓存数据和索引，直接影响读取性能。 | 物理内存的50%~70%（如64GB内存配40G）。 | 避免超过物理内存，防止系统交换（Swap）。 |
| innodb_log_file_size | 单个事务日志文件大小，影响写入性能和崩溃恢复时间。 | 1G~4G（需结合innodb_log_files_in_group配置日志组数量）。 |
修改后需重启MySQL生效。 |
| innodb_flush_log_at_trx_commit | 控制事务日志刷新策略，平衡性能与数据安全。 | 1（默认，完全持久化） 2（折中，每秒刷盘） 0（高性能，风险高）。 |
高并发写入场景可设为2（最多丢失1秒数据）。 金融类业务建议保持为1。 |
| max_connections | 最大客户端连接数，避免连接耗尽资源。 | 500~2000，需结合thread_cache_size（如100）缓存线程。 |
监控Threads_connected和Threads_running动态调整。 |
| tmp_table_size max_heap_table_size |
内存临时表大小上限，影响复杂查询（如GROUP BY、JOIN）。 | 64M~256M（两者值需一致，如128M）。 | 过小会导致使用磁盘临时表，性能下降；过大可能耗尽内存。 |
2.查询优化项
| 参数 | 作用 | 建议配置 | 注意事项 |
|---|---|---|---|
| query_cache_type | 启用查询缓存（缓存SELECT结果）。 （MySQL 8.0已移除） |
OFF（默认） |
高并发下易引发锁竞争，建议关闭。 仅旧版本（≤5.7）可谨慎使用。 |
| sort_buffer_size | 为每个排序操作分配的缓冲区大小（如ORDER BY、GROUP BY）。 |
2M~8M（如4M） |
过大会浪费内存，建议会话级按需调整：SET SESSION sort_buffer_size=4M;。 |
| join_buffer_size | 为无索引的JOIN操作分配的缓冲区大小。 | 4M~16M（如8M） |
仅对无索引JOIN有效，建议优先优化索引而非调大此值。 |
| read_buffer_size | 顺序扫描表时的缓冲区大小（如全表扫描）。 | 2M~8M（如4M） |
过大会增加内存开销，监控Handler_read_next调整。 |
| read_rnd_buffer_size | 随机读缓冲区大小（如按非索引排序后读取行）。 | 4M~16M（如8M） |
影响ORDER BY性能，过大可能导致OOM。 |
3.日志与监控
| 参数 | 作用 | 建议配置 | 注意事项 |
|---|---|---|---|
| slow_query_log | 启用慢查询日志，记录执行时间超过阈值的SQL。 | ON（默认OFF） |
需配合long_query_time使用，日志路径可通过slow_query_log_file指定。 |
| long_query_time | 定义慢查询的阈值（秒），超过该时间的SQL会被记录。 | 1~2（根据业务容忍度调整） |
微秒级精度可设为小数（如0.5）。分析工具：mysqldumpslow或pt-query-digest。 |
| log_error | 指定错误日志路径，记录启动/关闭、严重错误等信息。 | 明确路径（如/var/log/mysql/error.log） |
定期归档清理，避免磁盘占满。权限需确保MySQL用户可写入。 |
| binlog_format | 二进制日志格式，影响主从复制的数据一致性。 | ROW（推荐） |
STATEMENT（兼容性好但可能不安全），MIXED（混合模式）。 |
| expire_logs_days | 自动清理旧的二进制日志的天数，需结合备份策略。 | 7~14（根据备份频率调整） |
确保备份完成后再清理。动态设置：SET GLOBAL expire_logs_days=7;。 |
4.InnoDB高级优化
| 参数 | 作用 | 建议配置 | 注意事项 |
|---|---|---|---|
| innodb_io_capacity | 控制InnoDB后台任务（如刷脏页、合并插入缓冲）的I/O吞吐量上限。 | SSD：2000~4000HDD： 200~800（根据磁盘性能调整） |
过高可能导致I/O过载，过低影响后台任务效率。监控Innodb_buffer_pool_wait_free调整。 |
| innodb_flush_method | 定义数据文件和日志文件的刷盘方式，影响性能与数据安全。 | O_DIRECT（默认，绕过OS缓存，避免双缓冲） |
其他选项： - fdatasync（传统方式）- O_DSYNC（日志同步写）。 |
| innodb_thread_concurrency | 限制InnoDB内核并发线程数，避免线程竞争。 | 0（自适应，推荐）高并发场景可设为 CPU核数 × 2 |
非必要不建议修改，监控Innodb_threads_running动态调整。 |
| innodb_autoinc_lock_mode | 自增主键的锁模式，影响批量插入性能。 | 2（连续模式，高并发插入推荐） |
其他模式： - 0（传统锁，兼容性最好）- 1（默认，平衡模式）。 |
5.示例配置片段
物理资源 32核 CPU、64G内存、500G SSD
[mysqld]
# 核心配置
innodb_buffer_pool_size = 40G
innodb_log_file_size =2G
innodb_flush_log_at_trxcommit=2
max_connections=1000
thread_cache_size =100
#查询优化
tmp_table_size=128M
max_heap_table_size=128M
sort_buffer_size = 4M
join_buffer_size =8M
#日志与监控
slow_query_log = ON
long_query_time=1
log_error = /var/log/mysql/error.1og
binlog_format = ROw
expire_logs_days = 7
# InnoDB 高级
innodb_io_capacity=2000
innodb_flush_method =0 DIRECT
innodb_thread_concurrency=0
innodb_autoinc_lock_mode =2（3）SQL方面
SQL优化是确保数据库高效运行的关键，其核心在于通过减少资源消耗(如CPU、内存、磁盘 I/0)来提升査询响应速度，避免慢查询导致用户体验下降或系统崩溃。未优化的 SQL可能引发全表扫描、冗余计算或锁竞争，尤其在数据量大或高并发场景下,会导致服务器负载飙升、响应延迟,甚至影响业务连续性(如交易超时)。通过索引调优、查询改写、执行计划分析等手段，可显著降低数据库压力，支撑业务规模扩展，同时控制硬件成本与运维复杂度。
1.创建测试表并插入数据
| 步骤 | SQL命令 | 说明 |
|---|---|---|
| 创建测试数据库 | CREATE DATABASE test; |
创建名为test的数据库。 |
| 切换至测试数据库 | USE test; |
后续操作在test库中执行。 |
| 创建用户表 | ```sql | 定义users表结构： |
| CREATE TABLE users ( | - id：自增主键 |
|
| id INT PRIMARY KEY AUTO_INCREMENT, | - name：非空用户名（50字符） |
|
| name VARCHAR(50) NOT NULL, | - email：非空邮箱（100字符） |
|
| email VARCHAR(100) NOT NULL, | - age：非空年龄 |
|
| age INT NOT NULL, | - created_at：自动记录创建时间 |
|
| created_at DATETIME DEFAULT CURRENT_TIMESTAMP | ||
| ); |
| 步骤 | SQL命令 | 说明 |
|---|---|---|
| 定义存储过程 | ```sql | 创建名为insert_users的存储过程，用于批量插入10万条随机数据： |
| DELIMITER $$ | - 使用WHILE循环生成数据 |
|
| CREATE PROCEDURE insert_users() | - name和email格式为user[序号] |
|
| BEGIN | - age为0~99的随机整数 |
|
| DECLARE i INT DEFAULT 0; | ||
| WHILE i < 100000 DO | ||
| INSERT INTO users (name, email, age) | ||
| VALUES (CONCAT('user', i), CONCAT('user', i, '@example.com'), | ||
| FLOOR(RAND() * 100)); | ||
| SET i = i + 1; | ||
| END WHILE; | ||
| END$$ | ||
| DELIMITER ; | ||
| 执行存储过程 | CALL insert_users(); |
调用存储过程插入数据，耗时取决于硬件性能（通常几秒到几分钟）。 |
2.使用EXPLAIN 进行SQL 优化的步骤及实验验证
EXPLAIN 是 MySQL 中用于分析 SQL 执行计划的工具，通过模拟查询执行过程输出关键信息(如访问类型type、使用索引 key、预估扫描行数rows、额外操作 Extra 等)，帮助开发者识别全表扫描、索引失效等性能瓶颈，从而指导优化方向(如添加索引、改写查询或调整表结构)，是提升数据库效率不可或缺的诊断手段。
例如:
mysqI> EXPLAIN SELECT * FROM users WHERE name = ‘user123’；
| 字段 | 结果 | 问题诊断 |
|---|---|---|
| type | ALL |
全表扫描，效率极低。 |
| possible_keys | NULL |
未识别到可用索引。 |
| key | NULL |
实际未使用索引。 |
| rows | 100000 |
扫描全部10万行数据。 |
| 优化建议 | 为name字段添加索引：CREATE INDEX idx_name ON users(name); |
添加后type应变为ref，rows降至1。 |
| 字段 | 说明 | 优化关注点 | 示例分析 |
|---|---|---|---|
| id | 查询序列号，相同id为同一执行层，不同id按序执行（如子查询）。 | 复杂查询的嵌套层级，id越大优先级越高。 | 单次查询通常为1，子查询会递增（如2、3）。 |
| select_type | 查询类型： - SIMPLE：简单查询- PRIMARY：外层查询- SUBQUERY/DERIVED：子查询或派生表 |
识别复杂查询结构，避免不必要的派生表（DERIVED）。 |
SIMPLE表示无子查询或UNION。 |
| table | 访问的表名或别名。 | 确认查询涉及的表，检查是否有多余表关联。 | 若表名显示<subquery2>，说明是临时生成的子查询结果。 |
| type | 访问类型（性能从优到劣）：system > const > eq_ref > ref > range > index > ALL |
避免ALL（全表扫描），优先优化为ref或range。 |
示例中type=ALL：全表扫描，需为name字段添加索引。 |
| possible_keys | 可能使用的索引。 | 检查是否有合适索引未被使用（与key字段对比）。 |
示例中possible_keys=NULL：未识别到可用索引。 |
| key | 实际使用的索引。 | 确认是否命中最佳索引，未使用索引时需优化。 | 若key为NULL，说明未使用索引。 |
| rows | 预估扫描的行数。 | 行数越少效率越高，若值过大需优化索引或查询条件。 | 示例中rows=100000：扫描全部数据，性能极差。 |
| Extra | 附加信息： - Using index：覆盖索引- Using temporary：使用临时表- Using filesort：文件排序 |
发现潜在问题（如临时表、排序开销）。 | 若出现Using filesort，需优化ORDER BY字段索引。 |
3.优化步骤：添加索引
mysql> ALTER TABLE users ADD INDEX idx name(name)；
4.优化后查询及EXPLAIN 分析
mysql> EXPLAIN SELECT *FROM users WHERE name ='user123’;
优化后结果分析如下:
type=ref: 索引查找，效率高。
key=idx name: 命中新创建的索引。
rows=1: 仅扫描一行数据。
三：MySQL存储引擎
在数据库中保存的是一张张有着千丝万缕关系的表，所以表设计的好坏，将直接影响着整个数据库。而在设计表的时候，最关注的一个问题是使用什么存储引擎。
| 存储引擎 | 特点 | 适用场景 | 限制 |
|---|---|---|---|
| InnoDB | - 支持事务（ACID） - 行级锁 - 外键约束 - 崩溃恢复能力 |
高并发写操作、需要事务的业务（如订单、支付） | 占用空间较大 |
| MyISAM | - 表级锁 - 全文索引 - 高速读取 |
读多写少、日志分析、数据仓库 | 不支持事务、崩溃后数据易丢失 |
| Memory | - 数据存于内存 - 极速访问 - 临时表默认引擎 |
临时数据缓存、会话存储 | 服务重启后数据丢失、不支持TEXT/BLOB |
| Archive | - 高压缩比（约75%） - 只支持INSERT/SELECT |
日志归档、历史数据存储 | 不支持更新/删除/索引 |
| NDB | - 分布式集群支持 - 高可用性 |
电信级高可用需求 | 配置复杂、内存要求高 |
（1）表设计核心原则
存储引擎选择
事务需求：必选InnoDB（如银行交易）。
读多写少：考虑MyISAM（需定期备份）。
临时数据：使用Memory引擎（如用户会话）。
字段设计规范
整型优先：
INT比VARCHAR更高效。避免
NULL：用默认值（如0或空字符串）减少索引复杂度。大文本分离：
TEXT/BLOB字段单独存表。
索引优化
最左前缀原则：联合索引
(A,B,C)需按顺序使用。覆盖索引：SELECT字段尽量包含在索引中。
避免过度索引：每个索引增加写开销。
关系设计
一对一：主表拆分（如用户基础信息+扩展信息）。
一对多：外键关联（如订单-商品）。
多对多：中间表实现（如学生-课程关系表）。
（2）性能与安全的平衡
| 设计维度 | 高性能方案 | 高安全方案 |
|---|---|---|
| 存储引擎 | MyISAM（读快） | InnoDB（事务支持） |
| 字段类型 | 定长CHAR | 变长VARCHAR（节省空间） |
| 索引策略 | 冗余索引 | 最小化索引+外键约束 |
| 数据归档 | 定期删除 | 软删除+历史表 |
（3）经典错误案例
错误选型
场景：电商订单表使用MyISAM
问题：高峰期并发更新引发表锁超时
解决：迁移至InnoDB
索引失效
场景：
WHERE LEFT(name,3)='abc'问题：函数导致索引失效
解决：改用
name LIKE 'abc%'
过度规范化
场景：20个关联表查询
问题：JOIN性能灾难
解决：适当反规范化（如冗余统计字段）
在 MySQL 客户端中，使用以下命令可以査看 MySQL 支持的引擎。
mysql> show engines;
Engine:存储引擎的名称，如InnoDB、MyISAM 等。
Support:表示该存储引擎是否被支持，DEFAULT 表示默认引擎，YES表示支持(已开启)，N0 表示不支持(未开启)。
Comment:关于该存储引擎的简短描述。
Transactions:是否支持事务，YES或NO。
XA:是否支持分布式事务(XA事务)，用于跨数据库的事务
Savepoints:是否支持保存点，允许在事务中设置中间点，可以回滚到该点。
1.MyISAM 存储引擎
MyISAM 存储引擎不支持事务，也不支持外键，特点是访问速度快，对事务完整性没有要求，以SELECT、INSERT 为主的应用基本都可以使用这个引擎来创建表。
Mysq18.0之前的版本每个 MyISAM 表在磁盘上存储成 3 个文件，其中文件名和表名都相同，但是扩展名分别为:
表名.frm(存储表定义)
表名.MYD(MYData，存储数据)
表名.MYI(MYIndex，存储索引)
例如 mysq15.7 中 test 库下的 aa 表
[root@localhost]# ls /usr/local/mysql/data/test/
aa.frm aa.MYD aa.MYI Mysq18.0及之后的版本每个 MyISAM 表也在磁盘上存储成3个文件,分别为:
表名 _唯一编号.sdi(存储表定义)
表名.MYD(MYData，存储数据)
表名.MYI(MYIndex，存储索引)
例如 8.0.36 中 test 库下的 aa 表:
[root@localhost mysql]# ls /usr/local/mysql/data/test/
aa_369.sdi aa.MYD aa.MYI| 特性/格式 | 静态表（固定长度） | 动态表（可变长度） | 压缩表 |
|---|---|---|---|
| 存储方式 | 固定长度记录 | 可变长度记录 | 单独压缩每条记录 |
| 默认状态 | 是 | 需包含可变长度字段自动启用 | 需使用myisamchk工具创建 |
| 优点 | - 读写速度快 - 易缓存 - 故障恢复简单 |
- 占用空间少 | - 占用空间极小 |
| 缺点 | - 空间利用率低 | - 易产生碎片 - 故障恢复困难 |
- 只读不可修改 |
| 维护要求 | 无需特殊维护 | 需定期执行：OPTIMIZE TABLE 或 myisamchk -r |
不可修改，需解压后重建 |
| 空格处理 | 存储时补足空格，读取时自动去除 | 按实际长度存储 | 压缩存储 |
| 适用场景 | 字段长度固定的只读数据 | 包含VARCHAR/TEXT/BLOB字段的表 | 历史归档等只读数据 |
MyISAM文件分布与维护命令
| 功能 | 操作方式 | 示例命令/配置 |
|---|---|---|
| 分离数据/索引文件 | 建表时指定路径：DATA DIRECTORY 和 INDEX DIRECTORY |
sql<br>CREATE TABLE t (id INT) <br>DATA DIRECTORY='/data' <br>INDEX DIRECTORY='/index';<br> |
| 自动修复 | 在my.cnf中配置：--myisam-recover=BACKUP,FORCE |
服务器启动时自动检查修复异常关闭的表 |
| 手动检查表 | 使用CHECK TABLE语句 |
sql<br>CHECK TABLE users;<br> |
| 手动修复表 | 使用REPAIR TABLE语句或myisamchk工具 |
sql<br>REPAIR TABLE users;<br>或 bash<br>myisamchk -r /var/lib/mysql/db/users.MYI<br> |
| 碎片整理 | 使用OPTIMIZE TABLE或myisamchk -r |
sql<br>OPTIMIZE TABLE users;<br> |
2.InnoDB存储引擎
InnoDB 是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。MySQL 从5.5.5 版本开始，默认的存储引擎为 InnoDB。InnoDB 存储引擎还引入了行级锁定和外键约束，在以下场景中使用InnoDB存储引擎是最理想的选择:
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务支持 | ✅ 支持（ACID） | ❌ 不支持 |
| 崩溃恢复 | ✅ 自动灾难恢复 | ❌ 需手动修复（CHECK/REPAIR TABLE） |
| 外键约束 | ✅ 支持 | ❌ 不支持 |
| 锁机制 | ✅ 行级锁（默认） | ❌ 表级锁 |
| 全文索引 | ✅ 5.6+版本支持 | ✅ 原生支持 |
| 默认引擎 | ✅ 5.5.5+起默认 | ❌ 旧版本默认 |
| COUNT(*)效率 | ❌ 需全表扫描（含WHERE条件） | ✅ 缓存行数（无WHERE时直接读取） |
| AUTO_INCREMENT | ✅ 必须单列索引 | ✅ 允许组合索引 |
| 清空表效率 | ❌ 逐行删除（慢） | ✅ 重建表（快） |
| 存储结构 | ✅ 聚簇索引（数据与索引绑定） | ❌ 非聚簇索引（数据与索引分离） |
| 适用场景 | 高并发写入、事务型应用（如电商、支付） | 读密集型、无事务需求（如日志、报表） |
关键差异说明
事务处理
InnoDB：支持提交/回滚，适合订单、账户等需数据一致性的场景。
MyISAM：批量操作中途失败无法回滚。
并发性能
InnoDB：行级锁减少锁冲突，适合多线程更新。
MyISAM：表级锁导致写并发性能差。
索引特性
InnoDB的聚簇索引提升主键查询速度，但二级索引需二次查找。
MyISAM的全文索引在5.6前优于InnoDB。
维护成本
MyISAM需定期执行
OPTIMIZE TABLE减少碎片，而InnoDB自动管理空间。
InnoDB 表的数据文件有两种存储方式
共享表空间(System Tablespace)
独占表空间(File-Per-Table)
Mysq18.0之前,例如在 MySQL 5.7中，InnoDB 表的数据文件两种存储方式:
共享表空间:
所有表的数据和索引存储在ibdatal文件中(编译安装时，路径为/usr/local/mysql/data/ibdatal)
每个表都需要一个.frm 文件(如表名.frm)，用于存储表结构的元数据(列定义、索引等)。这些文件位于数据库目录下
例如 mysq15.7 中 test 库下的 aa 表
[root@localhost] # ls /usr/local/mysql/data/ibdatal
ibdatal
[root@localhost ]# ls /usr/local/mysql/data/test/
aa.frm独占表空间(默认启用):
每个表有独立的.ibd 文件(如表名.ibd)，存储数据和索引。同时，每个表都需要一个.frm 文件(如表名.frm)，用于存储表结构的元数据(列定义、索引等)。这些文件位于数据库目录下
例如 mysq15.7 中 test 库下的 aa 表
[root@localhost ]# ls /usr/local/mysql/data/test/
aa.frm aa.ibd在 MySQL 8.0中，InnoDB 表的数据文件表现如下:
独占表空间(默认启用):
每个表对应一个.ibd 文件(如表名.ibd)，包含数据、索引和元数据。
从MySQL8.0开始,frm 文件被移除,表结构信息不再以单独的.frm文件存在而是存储在系统表空间(如mysql.ibd)中
例如 mysq18.0 中 test 库下的 aa 表
[root@localhost ]# ls /usr/local/mysql/data/test/
aa. ibd共享表空间(可选):
若显式配置 innodb_file_per_table=0FF，数据会存储在 ibdatal 中,这时候会发现数据库目录中没有表的数据文件了
3.关于MyISAM与InnoDB选择使用
MyISAM 和InnoDB是 MySQL 数据库提供的两种存储引擎。两者的优劣可谓是各有千秋。InnoDB 会支持一些关系数据库的高级功能，如事务功能和行级锁，MyISAM 不支持。MyISAM 的性能更优，占用的存储空间少。所以，选择何种存储引擎，视具体应用而定。
(1) 如果应用程序一定要使用事务，毫无疑问要选择InnoDB 引擎。但要注意InnoDB 的行级锁是有条件的。在where 条件没有使用主键时，照样会锁全表比如 DELETE FROM mytable 这样的删除语句。
(2) 如果应用程序对查询性能要求较高，就要使用MISAM 了。MYISAM 索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于 InnoDB。 压缩后的索引也能节约一些磁盘空间。
4.关于MyISAM在MySQL8.0中的使用情况
| 评估维度 | 现状说明 | 风险/限制 | 替代方案建议 |
|---|---|---|---|
| 遗留系统兼容性 | 部分未迁移的老系统仍在使用 | - 官方已移除支持 - 需手动安装插件 - 无安全更新 |
逐步迁移至InnoDB，使用ALTER TABLE ... ENGINE=InnoDB转换 |
| 全文索引场景 | 早期版本简单实现的全文索引 | - InnoDB 5.6+已支持更好的全文索引 - MyISAM无事务安全 |
使用InnoDB全文索引或Elasticsearch等专业引擎 |
| 只读/报表场景 | 理论上仍有读性能优势 | - InnoDB缓冲池优化后差距<5% - MyISAM表锁限制并发 |
InnoDB+适当索引 或列式存储工具(如ClickHouse) |
| 空间效率 | 压缩表格式(COMPRESSED)节省空间 | - 只读不可修改 - 需myisamchk工具维护 |
InnoDB透明压缩(TPC) 或ZFS/Btrfs文件系统压缩 |
| 高并发写入 | ❌ 完全不适用 | 表级锁导致严重性能瓶颈 | 必须使用InnoDB行级锁 |
| 分布式扩展 | 分库分表架构下无优势 | 缺乏事务支持导致数据一致性难题 | InnoDB+分布式事务(XA)或柔性事务(Seata) |
5.修改默认的存储引擎
修改默认的存储引擎有四种方法，分别如下。
(1)通过 alter table 修改。
MySQL>alter table user_info engine=MyISAM;(2)通过修改 my.cnf，指定默认存储引擎并重启服务。
[root@Mysql /]#vim my.cnf
default-storage-engine=InnoDB(3)通过 create table 创建表时指定存储引擎
MySQL>create table engineTest(id int) engine=MyISAM;