1. 存储引擎定义

存储引擎特点：
– 负责数据的存储和检索
– 提供不同的存储机制
– 可插拔式架构设计
– 每个表可选择不同引擎

存储引擎作用：
– 管理数据文件
– 实现索引机制
– 提供事务支持
– 管理锁和并发

存储引擎优势：
– 灵活选择适合的引擎
– 满足不同业务需求
– 可根据场景优化
– 支持引擎迁移

2. 查看存储引擎

查看所有支持的引擎：
mysql> SHOW ENGINES;

输出示例：
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+

查看默认存储引擎：
mysql> SHOW VARIABLES LIKE ‘default_storage_engine’;

输出示例：
+————————+——–+
| Variable_name | Value |
+————————+——–+
| default_storage_engine | InnoDB |
+————————+——–+

3. 查看表引擎

查看单个表的引擎：
mysql> SHOW CREATE TABLE orders\G

输出示例：
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`id` int NOT NULL AUTO_INCREMENT,
`customer_id` int DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

查看数据库所有表的引擎：
mysql> SELECT
TABLE_NAME,
ENGINE,
TABLE_ROWS,
DATA_LENGTH,
INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘production_db’;

输出示例：
+——————-+——–+————+————-+————-+
| TABLE_NAME | ENGINE | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH|
+——————-+——–+————+————-+————-+
| orders | InnoDB | 10000 | 10485760 | 2097152 |
| customers | InnoDB | 1000 | 1048576 | 524288 |
| logs | MyISAM | 1000000 | 52428800 | 1048576 |
+——————-+——–+————+————-+————-+

4. 设置存储引擎

创建表时指定引擎：
mysql> CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2)
) ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (0.02 sec)

修改表的存储引擎：
mysql> ALTER TABLE products ENGINE=MyISAM;

输出示例：
Query OK, 0 rows affected (0.10 sec)

设置默认存储引擎：
mysql> SET default_storage_engine = ‘InnoDB’;

输出示例：
Query OK, 0 rows affected (0.00 sec)

5. 配置文件设置

设置默认引擎：
vim /etc/my.cnf

[mysqld]
default_storage_engine = InnoDB

重启MySQL：
systemctl restart mysqld

输出示例：
MySQL service restarted.

验证设置：
mysql> SELECT @@default_storage_engine;

输出示例：
+————————–+
| @@default_storage_engine |
+————————–+
| InnoDB |
+————————–+

1. InnoDB存储引擎

特点：
– MySQL默认存储引擎
– 支持事务（ACID）
– 行级锁定
– 外键约束
– 崩溃恢复

适用场景：
– 需要事务支持的应用
– 高并发读写
– 数据完整性要求高
– 需要外键约束

创建InnoDB表：
mysql> CREATE TABLE orders_innodb (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
amount DECIMAL(10,2),
status VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_customer (customer_id)
) ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (0.02 sec)

InnoDB文件结构：
ls -la /var/lib/mysql/production_db/

输出示例：
-rw-r—– 1 mysql mysql 8558 Apr 1 10:00 orders_innodb.frm
-rw-r—– 1 mysql mysql 98304 Apr 1 10:00 orders_innodb.ibd

2. MyISAM存储引擎

特点：
– MySQL早期默认引擎
– 不支持事务
– 表级锁定
– 全文索引
– 空间占用小

适用场景：
– 只读或读多写少
– 全文检索
– 日志表
– 数据仓库

创建MyISAM表：
mysql> CREATE TABLE logs_myisam (
id INT PRIMARY KEY AUTO_INCREMENT,
log_time TIMESTAMP,
log_level VARCHAR(10),
message TEXT,
INDEX idx_time (log_time)
) ENGINE=MyISAM;

输出示例：
Query OK, 0 rows affected (0.01 sec)

MyISAM文件结构：
ls -la /var/lib/mysql/production_db/

输出示例：
-rw-r—– 1 mysql mysql 8558 Apr 1 10:00 logs_myisam.frm
-rw-r—– 1 mysql mysql 0 Apr 1 10:00 logs_myisam.MYD
-rw-r—– 1 mysql mysql 1024 Apr 1 10:00 logs_myisam.MYI

3. MEMORY存储引擎

特点：
– 数据存储在内存中
– 极快的访问速度
– 不持久化
– 支持HASH索引

适用场景：
– 临时数据存储
– 缓存表
– 会话管理
– 快速查找表

创建MEMORY表：
mysql> CREATE TABLE cache_memory (
key_name VARCHAR(100) PRIMARY KEY,
key_value VARCHAR(500),
expire_time TIMESTAMP
) ENGINE=MEMORY;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查看内存表大小限制：
mysql> SHOW VARIABLES LIKE ‘max_heap_table_size’;

输出示例：
+———————+———-+
| Variable_name | Value |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

4. ARCHIVE存储引擎

特点：
– 高压缩比存储
– 只支持INSERT和SELECT
– 不支持索引
– 适合归档数据

适用场景：
– 历史数据归档
– 日志存储
– 审计记录
– 数据备份

创建ARCHIVE表：
mysql> CREATE TABLE history_archive (
id INT AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(1000),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=ARCHIVE;

输出示例：
Query OK, 0 rows affected (0.01 sec)

插入数据：
mysql> INSERT INTO history_archive (data) VALUES (‘test data’);

输出示例：
Query OK, 1 row affected (0.00 sec)

查询数据：
mysql> SELECT * FROM history_archive;

输出示例：
+—-+———–+———————+
| id | data | created_at |
+—-+———–+———————+
| 1 | test data | 2026-04-01 10:00:00 |
+—-+———–+———————+

5. CSV存储引擎

特点：
– 数据存储为CSV格式
– 可直接编辑文件
– 不支持索引
– 适合数据交换

适用场景：
– 数据导入导出
– 与其他系统集成
– 数据交换
– 报表生成

创建CSV表：
mysql> CREATE TABLE data_csv (
id INT NOT NULL,
name VARCHAR(100),
value DECIMAL(10,2)
) ENGINE=CSV;

输出示例：
Query OK, 0 rows affected (0.01 sec)

插入数据：
mysql> INSERT INTO data_csv VALUES (1, ‘Product A’, 100.00);

输出示例：
Query OK, 1 row affected (0.00 sec)

查看CSV文件：
cat /var/lib/mysql/production_db/data_csv.CSV

输出示例：
“1”,”Product A”,”100.00″

1. 事务支持对比

查看引擎事务支持：
mysql> SELECT
ENGINE,
TRANSACTIONS,
XA,
SAVEPOINTS
FROM information_schema.ENGINES;

输出示例：
+——————–+————–+——+———–+
| ENGINE | TRANSACTIONS | XA | SAVEPOINTS|
+——————–+————–+——+———–+
| InnoDB | YES | YES | YES |
| MyISAM | NO | NO | NO |
| MEMORY | NO | NO | NO |
| ARCHIVE | NO | NO | NO |
+——————–+————–+——+———–+

事务测试：
— InnoDB事务
mysql> START TRANSACTION;
mysql> INSERT INTO orders_innodb VALUES (NULL, 1, 100.00, ‘pending’, NOW());
mysql> ROLLBACK;

输出示例：
Query OK, 0 rows affected (0.00 sec)

— MyISAM无事务
mysql> INSERT INTO logs_myisam VALUES (NULL, NOW(), ‘INFO’, ‘test’);
mysql> ROLLBACK;

输出示例：
Query OK, 0 rows affected (0.00 sec)
— 数据已提交，无法回滚

2. 锁机制对比

锁类型对比：
+——————-+——————+——————+
| 特性 | InnoDB | MyISAM |
+——————-+——————+——————+
| 锁粒度 | 行级锁 | 表级锁 |
| 读锁 | 共享锁 | 共享锁 |
| 写锁 | 排他锁 | 排他锁 |
| 死锁检测 | 支持 | 不支持 |
+——————-+——————+——————+

InnoDB行锁测试：
mysql> CREATE TABLE lock_test (id INT PRIMARY KEY, value INT) ENGINE=InnoDB;
mysql> INSERT INTO lock_test VALUES (1, 100), (2, 200);

— 会话1：锁定行1
mysql> START TRANSACTION;
mysql> SELECT * FROM lock_test WHERE id = 1 FOR UPDATE;

输出示例：
+—-+——-+
| id | value |
+—-+——-+
| 1 | 100 |
+—-+——-+

— 会话2：可以访问行2
mysql> SELECT * FROM lock_test WHERE id = 2;

输出示例：
+—-+——-+
| id | value |
+—-+——-+
| 2 | 200 |
+—-+——-+

3. 索引类型对比

索引支持对比：
+——————-+——————+——————+
| 索引类型 | InnoDB | MyISAM |
+——————-+——————+——————+
| B-Tree | 支持 | 支持 |
| HASH | 支持（自适应） | 不支持 |
| 全文索引 | 支持（5.6+） | 支持 |
| 空间索引 | 支持 | 支持 |
+——————-+——————+——————+

创建全文索引：
— MyISAM全文索引
mysql> CREATE TABLE articles_myisam (
id INT PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT INDEX ft_idx (title, body)
) ENGINE=MyISAM;

输出示例：
Query OK, 0 rows affected (0.01 sec)

— InnoDB全文索引
mysql> CREATE TABLE articles_innodb (
id INT PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT INDEX ft_idx (title, body)
) ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (0.02 sec)

全文搜索：
mysql> INSERT INTO articles_innodb VALUES
(1, ‘MySQL Tutorial’, ‘MySQL is a database’),
(2, ‘PostgreSQL Guide’, ‘PostgreSQL is also a database’);

mysql> SELECT * FROM articles_innodb
WHERE MATCH(title, body) AGAINST(‘database’);

输出示例：
+—-+——————+—————————–+
| id | title | body |
+—-+——————+—————————–+
| 1 | MySQL Tutorial | MySQL is a database |
| 2 | PostgreSQL Guide | PostgreSQL is also a database|
+—-+——————+—————————–+

4. 外键约束对比

外键支持对比：
+——————-+——————+——————+
| 特性 | InnoDB | MyISAM |
+——————-+——————+——————+
| 外键约束 | 支持 | 不支持 |
| 级联更新 | 支持 | 不支持 |
| 级联删除 | 支持 | 不支持 |
+——————-+——————+——————+

创建外键约束：
mysql> CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100)
) ENGINE=InnoDB;

mysql> CREATE TABLE orders_fk (
id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (0.02 sec)

测试外键约束：
mysql> INSERT INTO orders_fk VALUES (1, 999, 100.00);

输出示例：
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails

5. 崩溃恢复对比

崩溃恢复能力：
+——————-+——————+——————+
| 特性 | InnoDB | MyISAM |
+——————-+——————+——————+
| 崩溃恢复 | 自动恢复 | 需要修复 |
| 重做日志 | 支持 | 不支持 |
| 回滚日志 | 支持 | 不支持 |
| 数据安全 | 高 | 较低 |
+——————-+——————+——————+

InnoDB崩溃恢复：
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
…
LOG
Log sequence number 123456789
Log flushed up to 123456789
…

MyISAM修复：
mysql> REPAIR TABLE logs_myisam;

输出示例：
+————————+——–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————————+——–+———-+———-+
| production_db.logs_myisam| repair| status | OK |
+————————+——–+———-+———-+

1. 业务需求分析

需要考虑的因素：
– 是否需要事务支持
– 并发读写特点
– 数据完整性要求
– 查询模式

决策流程：
+——————-+——————+——————+
| 需求 | 推荐引擎 | 原因 |
+——————-+——————+——————+
| 需要事务 | InnoDB | ACID支持 |
| 高并发读写 | InnoDB | 行级锁 |
| 只读/读多写少 | MyISAM | 读性能好 |
| 临时数据 | MEMORY | 速度快 |
| 归档数据 | ARCHIVE | 压缩比高 |
+——————-+——————+——————+

2. 性能需求分析

读写性能对比：
mysql> — 创建测试表
mysql> CREATE TABLE perf_innodb (id INT PRIMARY KEY, data VARCHAR(100)) ENGINE=InnoDB;
mysql> CREATE TABLE perf_myisam (id INT PRIMARY KEY, data VARCHAR(100)) ENGINE=MyISAM;

写入性能测试：
mysql> INSERT INTO perf_innodb VALUES (1, ‘test’);
mysql> INSERT INTO perf_myisam VALUES (1, ‘test’);

输出示例：
Query OK, 1 row affected (0.00 sec)

读取性能测试：
mysql> SELECT * FROM perf_innodb WHERE id = 1;
mysql> SELECT * FROM perf_myisam WHERE id = 1;

输出示例：
+—-+——+
| id | data |
+—-+——+
| 1 | test |
+—-+——+

3. 数据安全需求

数据安全对比：
+——————-+——————+——————+
| 特性 | InnoDB | MyISAM |
+——————-+——————+——————+
| 崩溃恢复 | 自动 | 手动修复 |
| 数据一致性 | 高 | 较低 |
| 外键约束 | 支持 | 不支持 |
| 备份一致性 | 支持 | 需要锁表 |
+——————-+——————+——————+

高安全需求场景：
mysql> CREATE TABLE financial_records (
id INT PRIMARY KEY,
account_id INT,
amount DECIMAL(12,2),
transaction_time TIMESTAMP,
FOREIGN KEY (account_id) REFERENCES accounts(id)
) ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (0.02 sec)

4. 存储空间需求

存储空间对比：
mysql> SELECT
TABLE_NAME,
ENGINE,
TABLE_ROWS,
DATA_LENGTH / 1024 / 1024 AS data_mb,
INDEX_LENGTH / 1024 / 1024 AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘production_db’;

输出示例：
+——————-+——–+————+———+———-+
| TABLE_NAME | ENGINE | TABLE_ROWS | data_mb | index_mb |
+——————-+——–+————+———+———-+
| orders_innodb | InnoDB | 10000 | 10.00 | 2.00 |
| logs_myisam | MyISAM | 10000 | 5.00 | 1.00 |
+——————-+——–+————+———+———-+

ARCHIVE压缩效果：
mysql> CREATE TABLE logs_archive ENGINE=ARCHIVE AS
SELECT * FROM logs_myisam;

输出示例：
Query OK, 10000 rows affected (0.50 sec)

5. 运维成本分析

运维复杂度对比：
+——————-+——————+——————+
| 维护项目 | InnoDB | MyISAM |
+——————-+——————+——————+
| 崩溃恢复 | 自动 | 需要REPAIR |
| 碎片整理 | OPTIMIZE TABLE | OPTIMIZE TABLE |
| 监控指标 | 丰富 | 较少 |
| 备份恢复 | 在线热备 | 需要锁表 |
+——————-+——————+——————+

1. 功能特性对比

完整功能对比表：
+——————-+——–+——–+——–+———+——–+
| 特性 | InnoDB | MyISAM | MEMORY | ARCHIVE | CSV |
+——————-+——–+——–+——–+———+——–+
| 事务支持 | YES | NO | NO | NO | NO |
| 行级锁 | YES | NO | NO | NO | NO |
| 外键 | YES | NO | NO | NO | NO |
| 全文索引 | YES | YES | NO | NO | NO |
| B-Tree索引 | YES | YES | YES | NO | NO |
| HASH索引 | YES | NO | YES | NO | NO |
| 数据缓存 | YES | NO | N/A | NO | NO |
| 崩溃恢复 | YES | NO | NO | NO | NO |
+——————-+——–+——–+——–+———+——–+

2. 性能特性对比

创建性能测试表：
mysql> CREATE TABLE perf_test_innodb (
id INT AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100),
INDEX idx_data (data)
) ENGINE=InnoDB;

mysql> CREATE TABLE perf_test_myisam (
id INT AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100),
INDEX idx_data (data)
) ENGINE=MyISAM;

输出示例：
Query OK, 0 rows affected (0.02 sec)

批量插入测试：
mysql> INSERT INTO perf_test_innodb (data)
SELECT CONCAT(‘data’, n) FROM
(SELECT @row := @row + 1 AS n FROM
information_schema.columns,
(SELECT @row := 0) r LIMIT 10000) t;

输出示例：
Query OK, 10000 rows affected (0.50 sec)

3. 并发性能对比

InnoDB并发测试：
mysql> — 会话1
mysql> START TRANSACTION;
mysql> UPDATE perf_test_innodb SET data = ‘updated’ WHERE id = 1;
— 不提交

mysql> — 会话2
mysql> UPDATE perf_test_innodb SET data = ‘updated2’ WHERE id = 2;
— 可以执行，行级锁

输出示例：
Query OK, 1 row affected (0.00 sec)

MyISAM并发测试：
mysql> — 会话1
mysql> INSERT INTO perf_test_myisam (data) VALUES (‘new’);
— 持有写锁

mysql> — 会话2
mysql> SELECT * FROM perf_test_myisam;
— 等待写锁释放

输出示例：
— 等待中…

4. 存储空间对比

查看表空间使用：
mysql> SELECT
TABLE_NAME,
ENGINE,
TABLE_ROWS,
DATA_LENGTH / 1024 / 1024 AS data_mb,
INDEX_LENGTH / 1024 / 1024 AS index_mb,
(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS total_mb
FROM information_schema.TABLES
WHERE TABLE_NAME LIKE ‘perf_test%’;

输出示例：
+——————-+——–+————+———+———-+———-+
| TABLE_NAME | ENGINE | TABLE_ROWS | data_mb | index_mb | total_mb |
+——————-+——–+————+———+———-+———-+
| perf_test_innodb | InnoDB | 10000 | 1.00 | 0.50 | 1.50 |
| perf_test_myisam | MyISAM | 10000 | 0.50 | 0.25 | 0.75 |
+——————-+——–+————+———+———-+———-+

5. 适用场景总结

场景推荐：
+——————-+——————+————————+
| 场景 | 推荐引擎 | 原因 |
+——————-+——————+————————+
| OLTP系统 | InnoDB | 事务、行锁、外键 |
| 数据仓库 | MyISAM/InnoDB | 读性能/压缩 |
| 日志系统 | MyISAM/ARCHIVE | 写性能/压缩 |
| 会话管理 | MEMORY | 速度 |
| 数据交换 | CSV | 兼容性 |
| 历史归档 | ARCHIVE | 压缩比 |
+——————-+——————+————————+

1. InnoDB监控

查看InnoDB状态：
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2026-04-01 10:00:00 0x7f8b1c0b4700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
—————–
BACKGROUND THREAD
—————–
srv_master_thread loops: 123456 srv_active, 0 srv_shutdown…
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 12345
…

查看InnoDB变量：
mysql> SHOW VARIABLES LIKE ‘innodb%’;

输出示例：
+——————————————+————————+
| Variable_name | Value |
+——————————————+————————+
| innodb_buffer_pool_size | 134217728 |
| innodb_log_file_size | 50331648 |
| innodb_flush_log_at_trx_commit | 1 |
+——————————————+————————+

查看InnoDB状态变量：
mysql> SHOW STATUS LIKE ‘Innodb%’;

输出示例：
+—————————————+————-+
| Variable_name | Value |
+—————————————+————-+
| Innodb_buffer_pool_read_requests | 1000000 |
| Innodb_buffer_pool_reads | 1000 |
| Innodb_row_lock_waits | 50 |
+—————————————+————-+

2. MyISAM监控

查看MyISAM状态变量：
mysql> SHOW STATUS LIKE ‘Key%’;

输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 107163|
| Key_blocks_used | 100 |
| Key_read_requests | 50000 |
| Key_reads | 100 |
| Key_write_requests | 1000 |
| Key_writes | 500 |
+————————+——-+

计算键缓存命中率：
mysql> SELECT
Key_read_requests,
Key_reads,
ROUND((1 – Key_reads / Key_read_requests) * 100, 2) AS hit_ratio
FROM (
SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Key_read_requests’) AS Key_read_requests,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Key_reads’) AS Key_reads
) t;

输出示例：
+——————-+———-+———–+
| Key_read_requests | Key_reads| hit_ratio |
+——————-+———-+———–+
| 50000 | 100 | 99.80 |
+——————-+———-+———–+

3. 表级监控

查看表统计信息：
mysql> SELECT
TABLE_SCHEMA,
TABLE_NAME,
ENGINE,
TABLE_ROWS,
DATA_LENGTH / 1024 / 1024 AS data_mb,
INDEX_LENGTH / 1024 / 1024 AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘production_db’
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

输出示例：
+————–+——————-+——–+————+———+———-+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | TABLE_ROWS | data_mb | index_mb |
+————–+——————-+——–+————+———+———-+
| production_db| orders | InnoDB | 50000 | 50.00 | 10.00 |
| production_db| logs | MyISAM | 100000 | 100.00 | 20.00 |
+————–+——————-+——–+————+———+———-+

4. 性能监控

创建监控视图：
mysql> CREATE VIEW v_engine_stats AS
SELECT
ENGINE,
COUNT(*) AS table_count,
SUM(TABLE_ROWS) AS total_rows,
SUM(DATA_LENGTH) / 1024 / 1024 AS total_data_mb,
SUM(INDEX_LENGTH) / 1024 / 1024 AS total_index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN (‘mysql’, ‘information_schema’, ‘performance_schema’)
GROUP BY ENGINE;

查询监控数据：
mysql> SELECT * FROM v_engine_stats;

输出示例：
+——–+————+————+—————+—————-+
| ENGINE | table_count| total_rows | total_data_mb | total_index_mb |
+——–+————+————+—————+—————-+
| InnoDB | 10 | 100000 | 100.00 | 20.00|
| MyISAM | 5 | 500000 | 200.00 | 40.00|
+——–+————+————+—————+—————-+

5. 告警配置

创建告警存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE check_engine_health()
BEGIN
— 检查MyISAM表是否需要修复
SELECT TABLE_SCHEMA, TABLE_NAME, ‘MyISAM table may need repair’ AS alert
FROM information_schema.TABLES
WHERE ENGINE = ‘MyISAM’
AND TABLE_ROWS = 0
AND DATA_LENGTH > 0;

— 检查InnoDB行锁等待
SELECT ‘High row lock waits detected’ AS alert
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_row_lock_waits’
AND VARIABLE_VALUE > 100;
END //
mysql> DELIMITER ;

执行检查：
mysql> CALL check_engine_health();

输出示例：
Empty set (0.00 sec)

1. 默认引擎设置

配置默认引擎：
vim /etc/my.cnf

[mysqld]
default_storage_engine = InnoDB

重启MySQL：
systemctl restart mysqld

输出示例：
MySQL service restarted.

验证设置：
mysql> SELECT @@default_storage_engine;

输出示例：
+————————–+
| @@default_storage_engine |
+————————–+
| InnoDB |
+————————–+

2. 表引擎规范

创建表时明确指定引擎：
mysql> CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
amount DECIMAL(10,2),
status VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_customer (customer_id),
INDEX idx_status (status)
) ENGINE=InnoDB
COMMENT=’订单表’;

输出示例：
Query OK, 0 rows affected (0.02 sec)

引擎选择规范：
+——————-+——————+——————+
| 表类型 | 推荐引擎 | 说明 |
+——————-+——————+——————+
| 核心业务表 | InnoDB | 事务、外键 |
| 日志表 | MyISAM/InnoDB | 根据需求选择 |
| 配置表 | InnoDB | 数据安全 |
| 临时表 | MEMORY | 性能 |
| 归档表 | ARCHIVE | 压缩 |
+——————-+——————+——————+

3. 引擎变更规范

变更前检查：
mysql> — 检查表大小
mysql> SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_NAME = ‘target_table’;

输出示例：
+————+————-+————-+
| TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH|
+————+————-+————-+
| 100000 | 104857600 | 20971520 |
+————+————-+————-+

执行引擎变更：
mysql> ALTER TABLE target_table ENGINE=InnoDB;

输出示例：
Query OK, 100000 rows affected (30.00 sec)

变更后验证：
mysql> SELECT ENGINE, TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_NAME = ‘target_table’;

输出示例：
+——–+————+
| ENGINE | TABLE_ROWS |
+——–+————+
| InnoDB | 100000 |
+——–+————+

4. 权限管理规范

查看表权限：
mysql> SHOW GRANTS FOR ‘app_user’@’%’;

输出示例：
+———————————————————–+
| Grants for app_user@% |
+———————————————————–+
| GRANT SELECT, INSERT, UPDATE ON production_db.* TO … |
+———————————————————–+

设置引擎相关权限：
mysql> GRANT SELECT ON production_db.* TO ‘readonly_user’@’%’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 文档规范

创建引擎使用文档表：
mysql> CREATE TABLE engine_documentation (
table_name VARCHAR(100) PRIMARY KEY,
engine VARCHAR(20),
reason TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

记录引擎选择原因：
mysql> INSERT INTO engine_documentation (table_name, engine, reason)
VALUES (‘orders’, ‘InnoDB’, ‘核心业务表，需要事务支持’);

输出示例：
Query OK, 1 row affected (0.01 sec)

1. OLTP系统场景

创建OLTP核心表：
mysql> CREATE TABLE accounts (
id INT PRIMARY KEY AUTO_INCREMENT,
account_no VARCHAR(20) UNIQUE,
balance DECIMAL(12,2) NOT NULL DEFAULT 0,
status TINYINT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

mysql> CREATE TABLE transactions (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
account_id INT,
amount DECIMAL(12,2) NOT NULL,
transaction_type ENUM(‘deposit’, ‘withdraw’, ‘transfer’),
reference_id VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_account (account_id),
INDEX idx_created (created_at),
FOREIGN KEY (account_id) REFERENCES accounts(id)
) ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (0.02 sec)

事务操作示例：
mysql> START TRANSACTION;
mysql> UPDATE accounts SET balance = balance – 100 WHERE id = 1;
mysql> UPDATE accounts SET balance = balance + 100 WHERE id = 2;
mysql> INSERT INTO transactions (account_id, amount, transaction_type)
VALUES (1, -100, ‘transfer’), (2, 100, ‘transfer’);
mysql> COMMIT;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 日志系统场景

创建日志表：
mysql> CREATE TABLE access_logs (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ip_address VARCHAR(45),
request_url VARCHAR(500),
http_status INT,
response_time INT,
user_agent VARCHAR(500),
INDEX idx_time (log_time),
INDEX idx_ip (ip_address)
) ENGINE=MyISAM;

输出示例：
Query OK, 0 rows affected (0.01 sec)

插入日志：
mysql> INSERT INTO access_logs (ip_address, request_url, http_status, response_time)
VALUES (‘192.168.1.1’, ‘/api/users’, 200, 50);

输出示例：
Query OK, 1 row affected (0.00 sec)

3. 缓存系统场景

创建缓存表：
mysql> CREATE TABLE session_cache (
session_id VARCHAR(64) PRIMARY KEY,
user_id INT,
data JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expire_at TIMESTAMP,
INDEX idx_expire (expire_at)
) ENGINE=MEMORY;

输出示例：
Query OK, 0 rows affected (0.01 sec)

缓存操作：
mysql> INSERT INTO session_cache (session_id, user_id, expire_at)
VALUES (UUID(), 1, DATE_ADD(NOW(), INTERVAL 30 MINUTE));

输出示例：
Query OK, 1 row affected (0.00 sec)

4. 归档系统场景

创建归档表：
mysql> CREATE TABLE order_history_archive (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
customer_id INT,
amount DECIMAL(10,2),
status VARCHAR(20),
order_date TIMESTAMP,
archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=ARCHIVE;

输出示例：
Query OK, 0 rows affected (0.01 sec)

归档数据：
mysql> INSERT INTO order_history_archive (order_id, customer_id, amount, status, order_date)
SELECT id, customer_id, amount, status, created_at
FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR); 输出示例： Query OK, 10000 rows affected (5.00 sec) 5. 数据交换场景 创建CSV交换表： mysql> CREATE TABLE data_exchange (
id INT NOT NULL,
name VARCHAR(100),
value DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=CSV;

输出示例：
Query OK, 0 rows affected (0.01 sec)

导出数据：
mysql> INSERT INTO data_exchange (id, name, value)
SELECT id, name, price FROM products;

输出示例：
Query OK, 100 rows affected (0.01 sec)

查看CSV文件：
cat /var/lib/mysql/production_db/data_exchange.CSV

输出示例：
“1”,”Product A”,”100.00″,”2026-04-01 10:00:00″
“2”,”Product B”,”200.00″,”2026-04-01 10:00:00″

1. 表优化

InnoDB表优化：
mysql> OPTIMIZE TABLE orders;

输出示例：
+————————+———-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————————+———-+———-+———-+
| production_db.orders | optimize | note | Table does not support optimize, doing recreate + analyze… |
| production_db.orders | optimize | status | OK |
+————————+———-+———-+———-+

MyISAM表优化：
mysql> OPTIMIZE TABLE logs_myisam;

输出示例：
+————————+———-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————————+———-+———-+———-+
| production_db.logs_myisam| optimize| status | OK |
+————————+———-+———-+———-+

2. 表分析

分析表统计信息：
mysql> ANALYZE TABLE orders;

输出示例：
+————————+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————————+———+———-+———-+
| production_db.orders | analyze | status | OK |
+————————+———+———-+———-+

查看表统计信息：
mysql> SHOW TABLE STATUS LIKE ‘orders’\G

输出示例：
*************************** 1. row ***************************
Name: orders
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 10000
Avg_row_length: 1024
Data_length: 10485760
Index_length: 2097152

3. 表检查

检查表完整性：
mysql> CHECK TABLE orders;

输出示例：
+————————+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————————+——-+———-+———-+
| production_db.orders | check | status | OK |
+————————+——-+———-+———-+

MyISAM表修复：
mysql> REPAIR TABLE logs_myisam;

输出示例：
+————————+——–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————————+——–+———-+———-+
| production_db.logs_myisam| repair| status | OK |
+————————+——–+———-+———-+

4. 引擎迁移

MyISAM迁移到InnoDB：
mysql> — 创建备份
mysql> CREATE TABLE logs_backup LIKE logs_myisam;
mysql> INSERT INTO logs_backup SELECT * FROM logs_myisam;

输出示例：
Query OK, 10000 rows affected (1.00 sec)

mysql> — 执行迁移
mysql> ALTER TABLE logs_myisam ENGINE=InnoDB;

输出示例：
Query OK, 10000 rows affected (5.00 sec)

mysql> — 验证迁移
mysql> SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_NAME = ‘logs_myisam’;

输出示例：
+——–+
| ENGINE |
+——–+
| InnoDB |
+——–+

5. 监控维护

创建维护脚本：
mysql> DELIMITER //
mysql> CREATE PROCEDURE maintenance_all_tables()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE t_name VARCHAR(100);
DECLARE cur CURSOR FOR
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND ENGINE = ‘InnoDB’;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO t_name;
IF done THEN
LEAVE read_loop;
END IF;

SET @sql = CONCAT(‘ANALYZE TABLE ‘, t_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;

SELECT ‘Maintenance completed’ AS result;
END //
mysql> DELIMITER ;

执行维护：
mysql> CALL maintenance_all_tables();

输出示例：
+———————-+
| result |
+———————-+
| Maintenance completed|
+———————-+

# 案例：电商订单系统

# 问题描述：
# 需要设计高并发、数据一致性要求高的订单系统

# 步骤1：创建表结构
mysql> CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

mysql> CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
status TINYINT DEFAULT 1,
INDEX idx_status (status)
) ENGINE=InnoDB;

mysql> CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
total_amount DECIMAL(12,2),
status ENUM(‘pending’, ‘paid’, ‘shipped’, ‘completed’, ‘cancelled’),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_customer (customer_id),
INDEX idx_status (status),
FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

mysql> CREATE TABLE order_items (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
INDEX idx_order (order_id),
INDEX idx_product (product_id),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

# 输出示例：
# Query OK, 0 rows affected (0.02 sec)

# 步骤2：创建订单事务
mysql> DELIMITER //
mysql> CREATE PROCEDURE create_order(
IN p_customer_id INT,
IN p_product_id INT,
IN p_quantity INT
)
BEGIN
DECLARE v_stock INT;
DECLARE v_price DECIMAL(10,2);
DECLARE v_order_id INT;

START TRANSACTION;

— 检查库存
SELECT stock, price INTO v_stock, v_price
FROM products WHERE id = p_product_id FOR UPDATE;

IF v_stock < p_quantity THEN ROLLBACK; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock'; END IF; -- 创建订单 INSERT INTO orders (customer_id, total_amount, status) VALUES (p_customer_id, v_price * p_quantity, 'pending'); SET v_order_id = LAST_INSERT_ID(); -- 创建订单项 INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (v_order_id, p_product_id, p_quantity, v_price); -- 扣减库存 UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id; COMMIT; SELECT v_order_id AS order_id; END // mysql> DELIMITER ;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 步骤3：测试订单创建
mysql> INSERT INTO customers (name, email) VALUES (‘Customer A’, ‘a@test.com’);
mysql> INSERT INTO products (name, price, stock) VALUES (‘Product 1’, 100.00, 10);

mysql> CALL create_order(1, 1, 2);

# 输出示例：
# +———-+
# | order_id |
# +———-+
# | 1 |
# +———-+

# 步骤4：验证数据一致性
mysql> SELECT * FROM orders WHERE id = 1;

# 输出示例：
# +—-+————-+————–+———+———————+
# | id | customer_id | total_amount | status | created_at |
# +—-+————-+————–+———+———————+
# | 1 | 1 | 200.00 | pending | 2026-04-01 10:00:00 |
# +—-+————-+————–+———+———————+

mysql> SELECT stock FROM products WHERE id = 1;

# 输出示例：
# +——-+
# | stock |
# +——-+
# | 8 |
# +——-+

# 案例：网站访问日志系统

# 问题描述：
# 需要存储大量日志数据，主要进行写入和统计查询

# 步骤1：创建日志表
mysql> CREATE TABLE access_logs (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ip_address VARCHAR(45),
request_method VARCHAR(10),
request_url VARCHAR(500),
http_status INT,
response_time INT,
user_agent VARCHAR(500),
referer VARCHAR(500),
INDEX idx_time (log_time),
INDEX idx_ip (ip_address),
INDEX idx_status (http_status)
) ENGINE=MyISAM;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 步骤2：创建全文索引
mysql> ALTER TABLE access_logs ADD FULLTEXT INDEX ft_url (request_url);

# 输出示例：
# Query OK, 0 rows affected (0.10 sec)

# 步骤3：批量插入日志
mysql> INSERT INTO access_logs
(ip_address, request_method, request_url, http_status, response_time)
VALUES
(‘192.168.1.1’, ‘GET’, ‘/api/users’, 200, 50),
(‘192.168.1.2’, ‘POST’, ‘/api/orders’, 201, 100),
(‘192.168.1.3’, ‘GET’, ‘/api/products’, 200, 30);

# 输出示例：
# Query OK, 3 rows affected (0.00 sec)

# 步骤4：统计查询
mysql> SELECT
DATE(log_time) AS log_date,
COUNT(*) AS total_requests,
AVG(response_time) AS avg_response_time,
SUM(CASE WHEN http_status = 200 THEN 1 ELSE 0 END) AS success_count
FROM access_logs
GROUP BY DATE(log_time);

# 输出示例：
# +————+—————-+——————-+—————+
# | log_date | total_requests | avg_response_time | success_count |
# +————+—————-+——————-+—————+
# | 2026-04-01 | 3 | 60.0000 | 2 |
# +————+—————-+——————-+—————+

# 步骤5：全文搜索
mysql> SELECT * FROM access_logs
WHERE MATCH(request