技术 · 思考 · 实践 · 成长

首页/MySQL8/正文

MySQL 8.0 存储引擎详解:从基础到深入

MySQL8610 次阅读

一、存储引擎的基本概念

1.1、什么是存储引擎

存储引擎是MySQL的核心组件,负责处理数据的存储,检索、更新和删除操作。不同的存储引擎有不同的数据结构、性能特点和适用场景。


-- 查看当前存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';

-- 查看所有可用的存储引擎
SHOW ENGINES;

-- 创建指定存储引擎的表
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(100)
) ENGINE=InnoDB;

-- 创建临时表(MEMORY引擎)
CREATE TEMPORARY TABLE temp_table (
  id INT,
  value VARCHAR(50)
) ENGINE=MEMORY;

1.2、存储引擎架构

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'my_table';

-- 创建不同引擎的表进行对比
CREATE TABLE innodb_example (
  id INT PRIMARY KEY,
  data TEXT
) ENGINE=InnoDB;
CREATE TABLE myisam_example (
  id INT PRIMARY KEY,
  data TEXT
) ENGINE=MyISAM;
CREATE TABLE memory_example (
  id INT PRIMARY KEY,
  data VARCHAR(255)
) ENGINE=MEMORY;
CREATE TABLE archive_example (
  id INT PRIMARY KEY,
  data TEXT
) ENGINE=ARCHIVE;

二、InnoDB 存储引擎深入解析

2.1、InnoDB架构和特性

InnoDB是MySQL8.0默认的存储引擎特性,支持事务、外键、行级锁等特性。


-- 查看InnoDB配置
SHOW VARIABLES LIKE 'innodb%';

-- InnoDB缓冲池配置
SELECT 
  VARIABLE_NAME,
  VARIABLE_VALUE
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME LIKE 'innodb_buffer_pool_size%';

-- 创建InnoDB表
CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE,
  INDEX idx_department (department),
  INDEX idx_salary (salary)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO employees (name, department, salary, hire_date) VALUES
('张三', '技术部', 15000.00, '2020-01-15'),
('李四', '销售部', 12000.00, '2020-03-20'),
('王五', '技术部', 18000.00, '2019-06-10'),
('赵六', '市场部', 10000.00, '2021-02-28'),
('钱七', '技术部', 16000.00, '2020-09-15');

2.2、InnoDB事务支持

InnoDB支持ACID特性,确保数据的一致性和完整性。
-- 测试事务
START TRANSACTION;

-- 插入数据
INSERT INTO employees (name, department, salary, hire_date) VALUES
('孙八', '销售部', 11000.00, '2021-01-10'),
('周九', '技术部', 20000.00, '2018-11-05');

-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE department = '技术部';
COMMIT;

-- 查看事务隔离级别
SELECT @@transaction_isolation;

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

2.3、InnoDB行级锁

InnoDB使用行级锁,提高并发性能。

-- 创建订单表
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_number VARCHAR(50) NOT NULL,
  customer_id INT,
  amount DECIMAL(10,2),
  status VARCHAR(20),
  FOREIGN KEY (customer_id) REFERENCES employees(id)
) ENGINE=InnoDB;

-- 测试行级锁
-- 会话1:启动事务
START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE id = 1;
-- 其他会话无法更新该行
-- 会话2:尝试更新(会被阻塞)
-- UPDATE orders SET amount = amount * 1.1 WHERE id = 1;
-- 会话1:提交
COMMIT;
-- 会话2的更新会成功

2.4、InnoDB MVCC和快照

InnoDB使用多版本并发控制(MVCC)实现一致性读。

-- 创建产品表
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  version INT DEFAULT 0
) ENGINE=InnoDB;

-- 测试MVCC
-- 会话1:查询产品
SELECT id, name, price, version FROM products WHERE id = 1;
-- 会话2:更新产品
START TRANSACTION;
UPDATE products SET price = 999.99, version = version + 1 WHERE id = 1;
COMMIT;

-- 会话1:再次查询(可以看到旧版本数据)
-- 在新事务中可以看到更新前的数据
START TRANSACTION;
SELECT id, name, price, version FROM products WHERE id = 1 FOR UPDATE;

三、其他存储引擎详解

3.1、MyISAM引擎

MyISAM引擎是mysql5.7之前的默认引擎,支持表级锁,性能好但不支持事务。

-- 创建MyISAM表
CREATE TABLE myisam_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  FULLTEXT KEY idx_name (name)
) ENGINE=MyISAM;

-- 插入数据
INSERT INTO myisam_table (name) VALUES
('MySQL 数据库'),
('MySQL 教程'),
('MySQL 存储引擎');

-- 全文搜索
SELECT * FROM myisam_table 
WHERE MATCH(name) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);
-- MyISAM特性
-- 表级锁
-- 全文索引
-- 压缩表

3.2、MEMORY引擎

MEMORY使用内存存储数据,速度极快但数据不持久。

-- 创建MyISAM表
CREATE TABLE myisam_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  FULLTEXT KEY idx_name (name)
) ENGINE=MyISAM;

-- 插入数据
INSERT INTO myisam_table (name) VALUES
('MySQL 数据库'),
('MySQL 教程'),
('MySQL 存储引擎');

-- 全文搜索
SELECT * FROM myisam_table 
WHERE MATCH(name) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);
-- MyISAM特性
-- 表级锁
-- 全文索引
-- 压缩表

3.3、ARCHIVE引擎

ARCHIVE引擎用于存储大量历史数据,支持压缩但不支持索引和事务。

-- 创建ARCHIVE表
CREATE TABLE archive_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=ARCHIVE;

-- 插入大量数据
-- 注意:ARCHIVE表适合大批量插入,不适合频繁更新
INSERT INTO archive_table (data) VALUES
('这是一些历史数据...'),
('这是另一条历史数据...');

-- 查询数据
SELECT COUNT(*) FROM archive_table;
-- ARCHIVE特点
-- 压缩存储
-- 只支持插入(不支持更新和删除)
-- 不支持索引

3.4、CSV引擎

CSV引擎以csv格式存储数据,适合数据交换。


-- 创建CSV表
CREATE TABLE csv_table (
  id INT,
  name VARCHAR(100),
  email VARCHAR(100)
) ENGINE=CSV;

-- 查看生成的CSV文件
-- 在数据目录下可以看到 .csv 文件
-- 插入数据
INSERT INTO csv_table VALUES (1, '张三', 'zhangsan@example.com');
-- 使用文本工具查看CSV文件
-- 在服务器上查看 .csv 文件的内容


四、存储引擎性能对比

4.1、性能测试表准备


-- 创建测试表
CREATE TABLE performance_innodb (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(100),
  INDEX idx_data (data)
) ENGINE=InnoDB;
CREATE TABLE performance_myisam (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(100),
  INDEX idx_data (data)
) ENGINE=MyISAM;
-- 生成测试数据
DELIMITER $$
CREATE PROCEDURE generate_test_data(IN engine_name VARCHAR(50), IN row_count INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  SET @sql = CONCAT('TRUNCATE TABLE performance_', engine_name);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
  WHILE i <= row_count DO
    SET @sql = CONCAT('INSERT INTO performance_', engine_name, ' (data) VALUES (CONCAT(''test_data_''', i, '''))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
-- 生成10万条测试数据
CALL generate_test_data('innodb', 100000);
CALL generate_test_data('myisam', 100000);


4.2、读取性能对比


-- 读取性能测试
SET @start_time = NOW();
-- InnoDB 读取性能
SELECT COUNT(*) FROM performance_innodb WHERE data LIKE '%test_data_50%';
SELECT 'InnoDB read time: ' + 
       TIMESTAMPDIFF(SECOND, @start_time, NOW()) + 
       ' seconds' AS result;
SET @start_time = NOW();
-- MyISAM 读取性能
SELECT COUNT(*) FROM performance_myisam WHERE data LIKE '%test_data_50%';
SELECT 'MyISAM read time: ' + 
       TIMESTAMPDIFF(SECOND, @start_time, NOW()) + 
       ' seconds' AS result;
-- 索引使用分析
EXPLAIN SELECT COUNT(*) FROM performance_innodb WHERE data LIKE '%test_data_50%';
EXPLAIN SELECT COUNT(*) FROM performance_myisam WHERE data LIKE '%test_data_50%';


4.3、写入性能对比

-- 写入性能测试
SET @start_time = NOW();
-- InnoDB 写入性能
INSERT INTO performance_innodb (data) VALUES ('test_insert_innodb');
SELECT 'InnoDB write time: ' + 
       TIMESTAMPDIFF(MICROSECOND, @start_time, NOW()) + 
       ' microseconds' AS result;
SET @start_time = NOW();
-- MyISAM 写入性能
INSERT INTO performance_myisam (data) VALUES ('test_insert_myisam');
SELECT 'MyISAM write time: ' + 
       TIMESTAMPDIFF(MICROSECOND, @start_time, NOW()) + 
       ' microseconds' AS result;
-- 批量写入测试
-- InnoDB 批量写入
SET @start_time = NOW();
INSERT INTO performance_innodb (data) 
SELECT CONCAT('batch_test_innodb_', i) FROM (
  WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 1000
  ) SELECT n FROM numbers
) AS t;
SELECT 'InnoDB batch insert: ' + 
       TIMESTAMPDIFF(MICROSECOND, @start_time, NOW()) + 
       ' microseconds' AS result;
-- MyISAM 批量写入
SET @start_time = NOW();
INSERT INTO performance_myisam (data) 
SELECT CONCAT('batch_test_myisam_', i) FROM (
  WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 1000
  ) SELECT n FROM numbers
) AS t;
SELECT 'MyISAM batch insert: ' + 
       TIMESTAMPDIFF(MICROSECOND, @start_time, NOW()) + 
       ' microseconds' AS result;


4.4、锁性能对比

-- 创建锁测试表
CREATE TABLE lock_test_innodb (
  id INT PRIMARY KEY,
  data VARCHAR(100)
) ENGINE=InnoDB;
CREATE TABLE lock_test_myisam (
  id INT PRIMARY KEY,
  data VARCHAR(100)
) ENGINE=MyISAM;
-- 插入测试数据
INSERT INTO lock_test_innodb VALUES (1, 'test');
INSERT INTO lock_test_myisam VALUES (1, 'test');
-- 测试InnoDB行级锁
-- 会话1:启动事务
START TRANSACTION;
UPDATE lock_test_innodb SET data = 'updated_innodb' WHERE id = 1;
-- 其他会话可以读取但不能更新该行
-- 测试MyISAM表级锁
-- 会话1:更新MyISAM表
UPDATE lock_test_myisam SET data = 'updated_myisam' WHERE id = 1;
-- 其他会话被阻塞,整个表被锁定
-- 并发测试
-- 创建并发测试存储过程
DELIMITER $$
CREATE PROCEDURE concurrency_test(IN engine_name VARCHAR(50))
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 100 DO
    SET @sql = CONCAT('UPDATE lock_test_', engine_name, ' SET data = CONCAT(''updated_'', i) WHERE id = 1');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

五、InnoDB高级特性

5.1、InnoDB缓冲池

缓冲池是    InnoDB的核心内存结构,用于缓存数据和索引。

-- 查看缓冲池状态
SELECT 
  VARIABLE_NAME,
  VARIABLE_VALUE
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME LIKE 'innodb_buffer_pool_size%';
-- 查看缓冲池使用情况
SELECT 
  BUFFERED_PAGES,
  FREE_PAGES,
  DATABASE_PAGES,
  DIRTY_PAGES
FROM information_schema.INNODB_BUFFER_POOL_STATUS;
-- 创建大表测试缓冲池
CREATE TABLE large_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  data VARCHAR(1000)
) ENGINE=InnoDB;
-- 插入大量数据测试缓冲池
INSERT INTO large_table (data) 
SELECT CONCAT('test_data_', i) 
FROM (
  WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 50000
  ) SELECT n FROM numbers
) AS t;


5.2、InnoDB日志系统

InnoDB使用Redo Log和Undo Log实现事务的持久性和一致性。

-- 查看日志配置
SHOW VARIABLES LIKE 'innodb_log%';
-- 创建事务日志测试表
CREATE TABLE transaction_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(10,2),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- 测试事务和日志
START TRANSACTION;
UPDATE transaction_log SET amount = amount * 1.1 WHERE id = 1;
-- 查看Redo Log状态
SHOW ENGINE INNODB STATUS;
COMMIT;

5.3、InnoDB表空间管理

InnoDB 支持独立的表空间和共享表空间管理模式。

-- 查看表空间信息
SELECT 
  TABLE_SCHEMA,
  TABLE_NAME,
  SPACE,
  TABLESPACE_NAME
FROM information_schema.INNODB_TABLES
WHERE TABLE_SCHEMA = 'your_database';
-- 创建独立表空间
CREATE TABLE independent_tablespace (
  id INT PRIMARY KEY,
  data TEXT
) ENGINE=InnoDB
TABLESPACE ts_innodb_file_per_table
DATA DIRECTORY = '/path/to/data';
-- 查看表空间文件
SHOW VARIABLES LIKE 'innodb_data_file_path';

六、存储引擎选择指南

6.1、选择存储引擎的考虑因素

-- 1. 事务需求
-- 如果需要事务,选择InnoDB
CREATE TABLE transactional_table (
  id INT PRIMARY KEY,
  data TEXT
) ENGINE=InnoDB;
-- 2. 读密集型应用
-- 如果是读密集型,考虑InnoDB(8.0后性能大幅提升)
CREATE TABLE read_heavy_table (
  id INT PRIMARY KEY,
  data TEXT
) ENGINE=InnoDB;
-- 3. 写密集型应用
-- 如果是写密集型,考虑InnoDB(支持行级锁)
CREATE TABLE write_heavy_table (
  id INT PRIMARY KEY,
  data TEXT
) ENGINE=InnoDB;
-- 4. 内存计算需求
-- 如果需要内存计算,使用MEMORY引擎
CREATE TABLE temporary_results (
  id INT PRIMARY KEY,
  result DECIMAL(10,2)
) ENGINE=MEMORY;
-- 5. 历史数据归档
-- 如果是历史数据归档,使用ARCHIVE引擎
CREATE TABLE archive_data (
  id INT PRIMARY KEY,
  data TEXT
) ENGINE=ARCHIVE;

七、存储引擎维护和优化

7.1、InnoDB表维护

-- 分析表
ANALYZE TABLE employees;
-- 优化表
OPTIMIZE TABLE employees;
-- 检查表
CHECK TABLE employees;
-- 修复表
REPAIR TABLE employees;
-- 查看表状态
SHOW TABLE STATUS LIKE 'employees';

7.2、表空间优化

-- 查看表空间使用情况
SELECT 
  TABLE_SCHEMA,
  TABLE_NAME,
  DATA_LENGTH,
  INDEX_LENGTH,
  DATA_FREE,
  TABLE_TYPE
FROM information_schema.TABLES
WHERE ENGINE = 'InnoDB'
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;
-- 清理碎片
ALTER TABLE engine=InnoDB;

7.3、参数调优

-- InnoDB缓冲池大小调优
SET GLOBAL innodb_buffer_pool_size = 2 * 1024 * 1024 * 1024;  -- 2GB
-- Redo Log配置
SET GLOBAL innodb_log_file_size = 512 * 1024 * 1024;  -- 512MB
SET GLOBAL innodb_log_files_in_group = 2;
-- 并线程数
SET GLOBAL innodb_thread_concurrency = 0;  -- 0表示不限制


八、故障排查

8.1、常见问题

-- 1. 表损坏
-- 检查表
CHECK TABLE broken_table;
-- 修复表
REPAIR TABLE broken_table;
-- 2. 空间不足
-- 查看表空间使用
SELECT TABLE_NAME, ROUND(DATA_LENGTH/1024/1024,2) AS size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database';
-- 清理空间
OPTIMIZE TABLE large_table;
-- 3. 锁等待
-- 查看锁等待
SHOW ENGINE INNODB STATUS;
-- 杀死死锁会话
KILL 123;

8.2、性能问题诊断

-- 查看慢查询
SELECT * 
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits;
-- 查看缓冲池命中率
SELECT 
  VARIABLE_VALUE AS pool_size,
  (VARIABLE_VALUE - VARIABLE_VALUE) / VARIABLE_VALUE AS hit_ratio
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME = 'innodb_buffer_pool_size';

九、总结和建议

9.1、存储引擎选择建议

-- 推荐的存储引擎选择

-- 1. 默认使用InnoDB

-- 2. 事务相关表必须使用InnoDB

-- 3. 读密集型应用优先考虑InnoDB(8.0后性能大幅提升)

-- 4. 写密集型应用必须使用InnoDB(行级锁)

-- 5. 内存计算使用MEMORY引擎

-- 6. 历史数据归档使用ARCHIVE引擎

-- 7. 全文搜索使用InnoDB(8.0+支持全文索引)


9.2、最佳实践

-- 1. 适当的索引设计
-- 为常用查询字段创建索引
CREATE TABLE best_practice_table (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_name (name),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB;
-- 2. 合理的表分区
-- 大表考虑分区
CREATE TABLE large_partitioned_table (
  id INT,
  data VARCHAR(1000),
  created_date DATE,
  PRIMARY KEY (id, created_date)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_date)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023)
);
-- 3. 定期维护
-- 定期执行维护操作
ANALYZE TABLE maintenance_table;
OPTIMIZE TABLE maintenance_table;
-- 4. 监控性能
-- 定期检查性能指标
SELECT * 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 10;


MySQL 8.0 存储引擎的选择和配置对数据库性能至关重要。InnoDB 作为默认存储引擎,在事务支持、并发性能和数据安全性方面表现优秀。

根据不同的业务场景选择合适的存储引擎,并进行适当的优化,可以充分发挥 MySQL 的性能优势。

好的习惯,排名第一的是:自律;排名第二的是:终身学习;排名第三的是:保持运动。拥有这三种习惯,美好人生垂手可得。

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读