【MySQL】【存储引擎深度对比】
- 版权声明：本站原创文章，于2026-03-26，由 管理员 发表。
- 转载请注明： 【MySQL】【存储引擎深度对比】 | CodeGarden
# MySQL存储引擎深度对比：InnoDB vs MyISAM
## 目录
1. [存储引擎概览](#1-存储引擎概览)
2. [InnoDB引擎深度解析](#2-innodb引擎深度解析)
3. [MyISAM引擎深度解析](#3-myisam引擎深度解析)
4. [核心特性对比](#4-核心特性对比)
5. [性能基准测试](#5-性能基准测试)
6. [适用场景分析](#6-适用场景分析)
7. [选择策略与最佳实践](#7-选择策略与最佳实践)
8. [迁移与优化建议](#8-迁移与优化建议)
---
## 1. 存储引擎概览
### 1.1 MySQL插件式架构
MySQL采用插件式存储引擎架构，允许为不同的表选择不同的存储引擎，以满足不同的业务需求。
```sql
-- 查看当前支持的存储引擎
SHOW ENGINES;
-- 查看默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';
-- 查看表的存储引擎
SHOW TABLE STATUS WHERE Name = 'table_name';
-- 修改表的存储引擎
ALTER TABLE table_name ENGINE = InnoDB;
```
### 1.2 主流存储引擎对比表
| 特性 | InnoDB | MyISAM | Memory | CSV | Archive |
|------|--------|--------|--------|-----|---------|
| 事务支持 | ✅ | ❌ | ❌ | ❌ | ❌ |
| 锁机制 | 行锁 | 表锁 | 表锁 | 表锁 | 行锁 |
| 外键约束 | ✅ | ❌ | ❌ | ❌ | ❌ |
| 崩溃恢复 | ✅ | ❌ | ❌ | ❌ | ❌ |
| 索引类型 | B+树 | B+树 | Hash | 无 | B+树 |
| 全文索引 | ✅ | ✅ | ❌ | ❌ | ❌ |
| 空间索引 | ✅ | ❌ | ❌ | ❌ | ❌ |
| 缓存机制 | Buffer Pool | Key Cache | 内存 | 无 | 无 |
| 数据存储 | 表空间 | 独立文件 | 内存 | CSV文件 | 压缩文件 |
| 适用场景 | OLTP | OLAP | 临时表 | 数据交换 | 归档 |
---
## 2. InnoDB引擎深度解析
### 2.1 InnoDB架构设计
```java
/**
* InnoDB存储引擎架构模拟
*/
public class InnoDBArchitecture {
/**
* InnoDB核心组件
*/
public static class InnoDBComponents {
// 连接线程
private List connectionThreads;
// 后台线程
private MasterThread masterThread;
private IOThread ioThread;
private PurgeThread purgeThread;
private PageCleanerThread pageCleanerThread;
// 内存结构
private BufferPool bufferPool;
private LogBuffer logBuffer;
private ChangeBuffer changeBuffer;
private AdaptiveHashIndex adaptiveHashIndex;
// 磁盘结构
private TableSpace tableSpace;
private RedoLog redoLog;
private UndoLog undoLog;
private DoubleWriteBuffer doubleWriteBuffer;
}
/**
* InnoDB缓冲池 - 核心内存组件
*/
public static class BufferPool {
// LRU列表
private ConcurrentLinkedDeque lruList;
// Free列表
private ConcurrentLinkedQueue freeList;
// Flush列表（脏页）
private ConcurrentLinkedQueue flushList;
// 哈希表（快速查找）
private ConcurrentHashMap pageMap;
// 缓冲池大小（页）
private int poolSize;
// 页大小
private int pageSize = 16 * 1024; // 16KB
/**
* LRU算法实现
*/
public BufferPage getPage(String spaceId, long pageNo) {
String key = spaceId + ":" + pageNo;
// 1. 从哈希表查找
BufferPage page = pageMap.get(key);
if (page != null) {
// 移动到LRU列表头部（年轻代）
moveToYoungGeneration(page);
return page;
}
// 2. 从Free列表获取
page = freeList.poll();
if (page != null) {
loadPage(page, spaceId, pageNo);
pageMap.put(key, page);
lruList.addFirst(page);
return page;
}
// 3. 执行LRU淘汰
page = evictPage();
loadPage(page, spaceId, pageNo);
pageMap.put(key, page);
lruList.addFirst(page);
return page;
}
/**
* LRU淘汰策略
*/
private BufferPage evictPage() {
// 从LRU列表尾部选择淘汰页
BufferPage page = lruList.pollLast();
if (page.isDirty()) {
// 脏页需要先刷新
flushPage(page);
}
pageMap.remove(page.getKey());
return page;
}
/**
* 移动到年轻代
*/
private void moveToYoungGeneration(BufferPage page) {
lruList.remove(page);
lruList.addFirst(page);
}
/**
* 加载页
*/
private void loadPage(BufferPage page, String spaceId, long pageNo) {
// 从磁盘加载页数据
byte[] data = readFromDisk(spaceId, pageNo);
page.setData(data);
page.setDirty(false);
}
/**
* 刷新页到磁盘
*/
private void flushPage(BufferPage page) {
if (page.isDirty()) {
// 先写入Double Write Buffer
doubleWriteBuffer.write(page);
// 再写入实际数据文件
writeToDisk(page);
page.setDirty(false);
}
}
}
/**
* 缓冲页
*/
public static class BufferPage {
private String spaceId;
private long pageNo;
private byte[] data;
private boolean dirty;
private long accessTime;
public String getKey() {
return spaceId + ":" + pageNo;
}
public boolean isDirty() {
return dirty;
}
public void setDirty(boolean dirty) {
this.dirty = dirty;
}
public byte[] getData() {
this.accessTime = System.currentTimeMillis();
return data;
}
public void setData(byte[] data) {
this.data = data;
}
}
/**
* InnoDB事务管理器
*/
public static class TransactionManager {
// 事务ID生成器
private AtomicLong transactionIdGenerator = new AtomicLong(0);
// 活跃事务列表
private Set activeTransactions = ConcurrentHashMap.newKeySet();
// MVCC控制器
private MVCCController mvccController;
/**
* 开始事务
*/
public Transaction beginTransaction(IsolationLevel level) {
long transactionId = transactionIdGenerator.incrementAndGet();
activeTransactions.add(transactionId);
Transaction transaction = new Transaction(transactionId, level);
transaction.setReadView(mvccController.createReadView(transactionId));
return transaction;
}
/**
* 提交事务
*/
public void commitTransaction(Transaction transaction) {
long transactionId = transaction.getTransactionId();
// 1. 生成Redo日志
RedoLog redoLog = generateRedoLog(transaction);
redoLog.write();
// 2. 修改数据页
applyChanges(transaction);
// 3. 生成Undo日志
UndoLog undoLog = generateUndoLog(transaction);
// 4. 清理资源
activeTransactions.remove(transactionId);
mvccController.purgeOldVersions();
// 5. 释放锁
releaseLocks(transaction);
}
/**
* 回滚事务
*/
public void rollbackTransaction(Transaction transaction) {
// 1. 读取Undo日志
UndoLog undoLog = transaction.getUndoLog();
// 2. 回滚数据
undoLog.rollback();
// 3. 清理资源
activeTransactions.remove(transaction.getTransactionId());
// 4. 释放锁
releaseLocks(transaction);
}
}
/**
* MVCC多版本并发控制
*/
public static class MVCCController {
private ConcurrentMap versionChains = new ConcurrentHashMap<>();
/**
* 读取数据（使用快照读）
*/
public Object readWithSnapshot(Transaction transaction, String rowKey) {
ReadView readView = transaction.getReadView();
VersionChain chain = versionChains.get(rowKey);
if (chain == null) {
return null;
}
// 从版本链中查找可见版本
for (DataVersion version : chain.getVersions()) {
if (isVersionVisible(version, readView)) {
return version.getData();
}
}
return null;
}
/**
* 写入数据
*/
public void write(Transaction transaction, String rowKey, Object data) {
VersionChain chain = versionChains.computeIfAbsent(rowKey, k -> new VersionChain());
DataVersion version = new DataVersion(transaction.getTransactionId(), data);
chain.addVersion(version);
}
/**
* 判断版本是否可见
*/
private boolean isVersionVisible(DataVersion version, ReadView readView) {
long versionTxId = version.getTransactionId();
long currentTxId = readView.getTransactionId();
// 版本是当前事务创建的
if (versionTxId == currentTxId) {
return true;
}
// 版本是在当前事务开始之前创建的
if (versionTxId < readView.getMinActiveTxId()) {
return true;
}
// 版本是在当前事务开始之后创建的，检查是否已提交
if (!readView.getActiveTxIds().contains(versionTxId)) {
return true;
}
return false;
}
}
/**
* 版本链
*/
public static class VersionChain {
private List versions = new ArrayList<>();
public void addVersion(DataVersion version) {
versions.add(version);
}
public List getVersions() {
return new ArrayList<>(versions);
}
}
/**
* 数据版本
*/
public static class DataVersion {
private long transactionId;
private Object data;
public DataVersion(long transactionId, Object data) {
this.transactionId = transactionId;
this.data = data;
}
public long getTransactionId() {
return transactionId;
}
public Object getData() {
return data;
}
}
/**
* 读视图
*/
public static class ReadView {
private long transactionId;
private long minActiveTxId;
private Set activeTxIds;
public ReadView(long transactionId, Set activeTxIds) {
this.transactionId = transactionId;
this.activeTxIds = new HashSet<>(activeTxIds);
this.minActiveTxId = activeTxIds.stream().min(Long::compare).orElse(0L);
}
public long getTransactionId() {
return transactionId;
}
public long getMinActiveTxId() {
return minActiveTxId;
}
public Set getActiveTxIds() {
return activeTxIds;
}
}
/**
* InnoDB锁管理器
*/
public static class LockManager {
// 行锁
private ConcurrentMap rowLocks = new ConcurrentHashMap<>();
// 表锁
private ConcurrentMap tableLocks = new ConcurrentHashMap<>();
/**
* 加行锁
*/
public boolean acquireRowLock(Transaction transaction, String rowId, LockMode mode) {
RowLock lock = rowLocks.computeIfAbsent(rowId, k -> new RowLock());
return lock.acquire(transaction, mode);
}
/**
* 释放行锁
*/
public void releaseRowLock(Transaction transaction, String rowId) {
RowLock lock = rowLocks.get(rowId);
if (lock != null) {
lock.release(transaction);
}
}
/**
* 行锁
*/
public static class RowLock {
private Set sharedLocks = new HashSet<>();
private Transaction exclusiveLock;
public boolean acquire(Transaction transaction, LockMode mode) {
synchronized (this) {
if (mode == LockMode.SHARED) {
// 共享锁：可以与其他共享锁共存
if (exclusiveLock != null && exclusiveLock != transaction) {
return false; // 有其他事务持有排他锁
}
sharedLocks.add(transaction);
return true;
} else {
// 排他锁：不能与任何锁共存
if (!sharedLocks.isEmpty() ||
(exclusiveLock != null && exclusiveLock != transaction)) {
return false;
}
exclusiveLock = transaction;
return true;
}
}
}
public void release(Transaction transaction) {
synchronized (this) {
sharedLocks.remove(transaction);
if (exclusiveLock == transaction) {
exclusiveLock = null;
}
}
}
}
}
}
```
### 2.2 InnoDB核心特性
#### 2.2.1 事务支持（ACID）
```java
/**
* InnoDB事务特性实现
*/
public class InnoDBTransaction {
/**
* 事务隔离级别
*/
public enum IsolationLevel {
READ_UNCOMMITTED, // 读未提交
READ_COMMITTED, // 读已提交
REPEATABLE_READ, // 可重复读（默认）
SERIALIZABLE // 串行化
}
/**
* 原子性（Atomicity）保证
*/
public static class Atomicity {
/**
* 原子性通过Undo Log实现
*/
public void executeAtomically(Transaction transaction, List operations) {
UndoLog undoLog = new UndoLog(transaction.getTransactionId());
try {
// 1. 记录Undo日志（用于回滚）
for (Operation operation : operations) {
undoLog.record(operation);
}
// 2. 执行操作
for (Operation operation : operations) {
operation.execute();
}
// 3. 记录Redo日志（用于恢复）
RedoLog redoLog = new RedoLog(transaction.getTransactionId());
redoLog.record(operations);
redoLog.write();
} catch (Exception e) {
// 回滚
undoLog.rollback();
throw e;
}
}
}
/**
* 一致性（Consistency）保证
*/
public static class Consistency {
/**
* 一致性通过约束和事务保证
*/
public boolean checkConstraints(Data data) {
// 1. 主键约束
if (!checkPrimaryKey(data)) {
return false;
}
// 2. 外键约束
if (!checkForeignKey(data)) {
return false;
}
// 3. 唯一约束
if (!checkUnique(data)) {
return false;
}
// 4. 检查约束
if (!checkCheckConstraint(data)) {
return false;
}
return true;
}
}
/**
* 隔离性（Isolation）保证
*/
public static class Isolation {
/**
* 隔离性通过MVCC和锁机制实现
*/
public Object readWithIsolation(Transaction transaction, String rowId,
IsolationLevel level) {
switch (level) {
case READ_UNCOMMITTED:
// 读未提交：直接读取最新数据
return readLatest(rowId);
case READ_COMMITTED:
// 读已提交：每次查询生成新的Read View
ReadView readView = createReadView(transaction);
return readWithView(rowId, readView);
case REPEATABLE_READ:
// 可重复读：使用事务开始时的Read View
ReadView snapshotView = transaction.getReadView();
return readWithView(rowId, snapshotView);
case SERIALIZABLE:
// 串行化：加锁读取
acquireSharedLock(transaction, rowId);
Object result = readLatest(rowId);
releaseLock(transaction, rowId);
return result;
default:
throw new IllegalArgumentException("不支持的隔离级别");
}
}
}
/**
* 持久性（Durability）保证
*/
public static class Durability {
/**
* 持久性通过Redo Log实现
*/
public void persist(Transaction transaction, List operations) {
// 1. 写入Redo Log
RedoLog redoLog = new RedoLog(transaction.getTransactionId());
redoLog.record(operations);
// 2. 强制刷新Redo Log到磁盘
redoLog.forceSync(); // fsync()
// 3. 更新数据页（异步）
updateDataPages(operations);
// 4. 写入BinLog
BinLog binLog = new BinLog();
binLog.write(operations);
}
}
}
```
#### 2.2.2 行级锁
```java
/**
* InnoDB行锁实现
*/
public class InnoDBRowLock {
/**
* 锁类型
*/
public enum LockType {
RECORD_LOCK, // 记录锁（锁索引记录）
GAP_LOCK, // 间隙锁（锁索引间隙）
NEXT_KEY_LOCK, // 临键锁（记录锁+间隙锁）
INSERT_INTENTION // 插入意向锁
}
/**
* 锁模式
*/
public enum LockMode {
S, // 共享锁（Shared Lock）
X, // 排他锁（Exclusive Lock）
IS, // 意向共享锁（Intention Shared）
IX // 意向排他锁（Intention Exclusive）
}
/**
* 锁兼容性矩阵
*/
public static class LockCompatibility {
private static final boolean[][] COMPATIBILITY_MATRIX = {
// IS IX S X
/*IS*/ {true, true, true, false},
/*IX*/ {true, true, false, false},
/*S */ {true, false, true, false},
/*X */ {false, false, false, false}
};
public static boolean isCompatible(LockMode request, LockMode held) {
return COMPATIBILITY_MATRIX[held.ordinal()][request.ordinal()];
}
}
/**
* 锁管理器
*/
public static class LockManager {
// 记录锁
private ConcurrentMap recordLocks = new ConcurrentHashMap<>();
// 间隙锁
private ConcurrentMap gapLocks = new ConcurrentHashMap<>();
// 意向锁
private ConcurrentMap intentionLocks = new ConcurrentHashMap<>();
/**
* 加记录锁
*/
public boolean acquireRecordLock(Transaction transaction, String indexKey,
LockMode mode, LockType type) {
RecordLock lock = recordLocks.computeIfAbsent(indexKey, k -> new RecordLock());
return lock.acquire(transaction, mode, type);
}
/**
* 加间隙锁
*/
public boolean acquireGapLock(Transaction transaction, String indexKey,
LockMode mode, LockType type) {
GapLock lock = gapLocks.computeIfAbsent(indexKey, k -> new GapLock());
return lock.acquire(transaction, mode, type);
}
/**
* 加临键锁
*/
public boolean acquireNextKeyLock(Transaction transaction, String indexKey,
LockMode mode) {
// 临键锁 = 记录锁 + 间隙锁
boolean recordLocked = acquireRecordLock(transaction, indexKey, mode, LockType.NEXT_KEY_LOCK);
boolean gapLocked = acquireGapLock(transaction, indexKey + "_gap", mode, LockType.NEXT_KEY_LOCK);
return recordLocked && gapLocked;
}
}
/**
* 记录锁
*/
public static class RecordLock {
private Map locks = new HashMap<>();
public synchronized boolean acquire(Transaction transaction, LockMode mode, LockType type) {
// 检查兼容性
for (Map.Entry entry : locks.entrySet()) {
if (!LockCompatibility.isCompatible(mode, entry.getValue())) {
return false;
}
}
locks.put(transaction, mode);
return true;
}
public synchronized void release(Transaction transaction) {
locks.remove(transaction);
}
}
/**
* 间隙锁
*/
public static class GapLock {
private Set sharedLocks = new HashSet<>();
private Transaction exclusiveLock;
public synchronized boolean acquire(Transaction transaction, LockMode mode, LockType type) {
if (mode == LockMode.S) {
sharedLocks.add(transaction);
return true;
} else {
if (!sharedLocks.isEmpty() || exclusiveLock != null) {
return false;
}
exclusiveLock = transaction;
return true;
}
}
public synchronized void release(Transaction transaction) {
sharedLocks.remove(transaction);
if (exclusiveLock == transaction) {
exclusiveLock = null;
}
}
}
}
```
#### 2.2.3 外键约束
```java
/**
* InnoDB外键约束实现
*/
public class InnoDBForeignKey {
/**
* 外键约束定义
*/
public static class ForeignKeyConstraint {
private String constraintName;
private String childTable;
private String childColumn;
private String parentTable;
private String parentColumn;
private ReferentialAction onUpdate;
private ReferentialAction onDelete;
public enum ReferentialAction {
CASCADE, // 级联
SET_NULL, // 设置为NULL
RESTRICT, // 限制
NO_ACTION, // 无操作
SET_DEFAULT // 设置为默认值
}
}
/**
* 外键检查器
*/
public static class ForeignKeyChecker {
private Map constraints = new HashMap<>();
/**
* 检查外键约束（插入/更新子表）
*/
public boolean checkOnInsert(String table, String column, Object value) {
String key = table + "." + column;
ForeignKeyConstraint constraint = constraints.get(key);
if (constraint == null) {
return true;
}
// 检查父表中是否存在对应记录
return existsInParent(constraint.parentTable,
constraint.parentColumn,
value);
}
/**
* 执行外键操作（删除/更新父表）
*/
public void executeOnDelete(String table, Object keyValue) {
List childConstraints = findChildConstraints(table);
for (ForeignKeyConstraint constraint : childConstraints) {
switch (constraint.onDelete) {
case CASCADE:
// 级联删除子表记录
cascadeDelete(constraint.childTable,
constraint.childColumn,
keyValue);
break;
case SET_NULL:
// 设置为NULL
setNull(constraint.childTable,
constraint.childColumn,
keyValue);
break;
case RESTRICT:
case NO_ACTION:
// 检查是否有子记录
if (hasChildRecords(constraint.childTable,
constraint.childColumn,
keyValue)) {
throw new ForeignKeyViolationException(
"无法删除：存在引用记录");
}
break;
case SET_DEFAULT:
// 设置为默认值
setDefault(constraint.childTable,
constraint.childColumn,
keyValue);
break;
}
}
}
/**
* 执行外键操作（更新父表）
*/
public void executeOnUpdate(String table, String column,
Object oldValue, Object newValue) {
List childConstraints = findChildConstraints(table);
for (ForeignKeyConstraint constraint : childConstraints) {
switch (constraint.onUpdate) {
case CASCADE:
// 级联更新子表记录
cascadeUpdate(constraint.childTable,
constraint.childColumn,
oldValue,
newValue);
break;
case SET_NULL:
// 设置为NULL
setNull(constraint.childTable,
constraint.childColumn,
oldValue);
break;
case RESTRICT:
case NO_ACTION:
// 检查是否有子记录
if (hasChildRecords(constraint.childTable,
constraint.childColumn,
oldValue)) {
throw new ForeignKeyViolationException(
"无法更新：存在引用记录");
}
break;
case SET_DEFAULT:
// 设置为默认值
setDefault(constraint.childTable,
constraint.childColumn,
oldValue);
break;
}
}
}
private boolean existsInParent(String table, String column, Object value) {
// 检查父表是否存在对应记录
return false;
}
private List findChildConstraints(String parentTable) {
// 查找引用父表的子表约束
return new ArrayList<>();
}
private void cascadeDelete(String childTable, String childColumn, Object value) {
// 级联删除子表记录
}
private void setNull(String childTable, String childColumn, Object value) {
// 设置子表字段为NULL
}
private boolean hasChildRecords(String childTable, String childColumn, Object value) {
// 检查子表是否存在引用记录
return false;
}
private void setDefault(String childTable, String childColumn, Object value) {
// 设置子表字段为默认值
}
private void cascadeUpdate(String childTable, String childColumn,
Object oldValue, Object newValue) {
// 级联更新子表记录
}
}
}
```
---
## 3. MyISAM引擎深度解析
### 3.1 MyISAM架构设计
```java
/**
* MyISAM存储引擎架构模拟
*/
public class MyISAMArchitecture {
/**
* MyISAM文件结构
*/
public static class MyISAMFileStructure {
// .MYD文件（数据文件）
private DataFile dataFile;
// .MYI文件（索引文件）
private IndexFile indexFile;
// .frm文件（表结构文件）
private FormFile formFile;
}
/**
* 数据文件（.MYD）
*/
public static class DataFile {
private RandomAccessFile file;
private int blockSize = 1024; // 1KB块大小
/**
* 读取数据行
*/
public Row readRow(long offset) throws IOException {
file.seek(offset);
// 读取行头
int rowHeader = file.readInt();
int rowLength = rowHeader & 0x00FFFFFF;
boolean deleted = (rowHeader & 0x80000000) != 0;
if (deleted) {
return null;
}
// 读取行数据
byte[] rowData = new byte[rowLength];
file.readFully(rowData);
return new Row(offset, rowData);
}
/**
* 写入数据行
*/
public long writeRow(Row row) throws IOException {
file.seek(file.length());
long offset = file.getFilePointer();
// 写入行头
int rowHeader = row.getLength() & 0x00FFFFFF;
file.writeInt(rowHeader);
// 写入行数据
file.write(row.getData());
return offset;
}
/**
* 删除数据行（标记删除）
*/
public void deleteRow(long offset) throws IOException {
file.seek(offset);
int rowHeader = file.readInt();
// 设置删除标记
rowHeader |= 0x80000000;
file.seek(offset);
file.writeInt(rowHeader);
}
}
/**
* 索引文件（.MYI）
*/
public static class IndexFile {
private RandomAccessFile file;
private BPlusTree indexTree;
/**
* B+树索引结构
*/
public static class BPlusTree {
private Node root;
private int order; // 阶数
private int keySize;
/**
* 搜索键
*/
public Long search(long key) {
return root.search(key);
}
/**
* 插入键
*/
public void insert(long key, long dataOffset) {
root.insert(key, dataOffset);
}
/**
* 删除键
*/
public void delete(long key) {
root.delete(key);
}
/**
* 范围查询
*/
public List rangeSearch(long minKey, long maxKey) {
List results = new ArrayList<>();
root.rangeSearch(minKey, maxKey, results);
return results;
}
}
/**
* B+树节点
*/
public abstract static class Node {
protected List keys;
public abstract Long search(long key);
public abstract void insert(long key, long dataOffset);
public abstract void delete(long key);
public abstract void rangeSearch(long minKey, long maxKey, List results);
}
/**
* 内部节点
*/
public static class InternalNode extends Node {
private List children;
@Override
public Long search(long key) {
int index = findChildIndex(key);
return children.get(index).search(key);
}
@Override
public void insert(long key, long dataOffset) {
int index = findChildIndex(key);
children.get(index).insert(key, dataOffset);
}
@Override
public void delete(long key) {
int index = findChildIndex(key);
children.get(index).delete(key);
}
@Override
public void rangeSearch(long minKey, long maxKey, List results) {
for (Node child : children) {
child.rangeSearch(minKey, maxKey, results);
}
}
private int findChildIndex(long key) {
int i = 0;
while (i < keys.size() && key >= keys.get(i)) {
i++;
}
return i;
}
}
/**
* 叶子节点
*/
public static class LeafNode extends Node {
private List dataOffsets;
private LeafNode next;
@Override
public Long search(long key) {
int index = Collections.binarySearch(keys, key);
if (index >= 0) {
return dataOffsets.get(index);
}
return null;
}
@Override
public void insert(long key, long dataOffset) {
int index = Collections.binarySearch(keys, key);
if (index < 0) {
index = -index - 1;
}
keys.add(index, key);
dataOffsets.add(index, dataOffset);
}
@Override
public void delete(long key) {
int index = Collections.binarySearch(keys, key);
if (index >= 0) {
keys.remove(index);
dataOffsets.remove(index);
}
}
@Override
public void rangeSearch(long minKey, long maxKey, List results) {
for (int i = 0; i < keys.size(); i++) {
if (keys.get(i) >= minKey && keys.get(i) <= maxKey) {
results.add(dataOffsets.get(i));
} else if (keys.get(i) > maxKey) {
break;
}
}
if (next != null) {
next.rangeSearch(minKey, maxKey, results);
}
}
}
}
/**
* MyISAM表锁管理器
*/
public static class TableLockManager {
private Map tableLocks = new ConcurrentHashMap<>();
/**
* 获取读锁
*/
public boolean acquireReadLock(String tableName, Transaction transaction) {
TableLock lock = tableLocks.computeIfAbsent(tableName, k -> new TableLock());
return lock.acquireReadLock(transaction);
}
/**
* 获取写锁
*/
public boolean acquireWriteLock(String tableName, Transaction transaction) {
TableLock lock = tableLocks.computeIfAbsent(tableName, k -> new TableLock());
return lock.acquireWriteLock(transaction);
}
/**
* 释放锁
*/
public void releaseLock(String tableName, Transaction transaction) {
TableLock lock = tableLocks.get(tableName);
if (lock != null) {
lock.release(transaction);
}
}
}
/**
* 表锁
*/
public static class TableLock {
private Set readLocks = new HashSet<>();
private Transaction writeLock;
public synchronized boolean acquireReadLock(Transaction transaction) {
// 读锁：可以与其他读锁共存
if (writeLock != null && writeLock != transaction) {
return false;
}
readLocks.add(transaction);
return true;
}
public synchronized boolean acquireWriteLock(Transaction transaction) {
// 写锁：不能与任何锁共存
if (!readLocks.isEmpty() || (writeLock != null && writeLock != transaction)) {
return false;
}
writeLock = transaction;
return true;
}
public synchronized void release(Transaction transaction) {
readLocks.remove(transaction);
if (writeLock == transaction) {
writeLock = null;
}
}
}
/**
* MyISAM压缩表
*/
public static class CompressedTable {
/**
* MyISAM压缩表特性
* - 数据只读
* 压缩比通常可达40-70%
* - 查询速度更快（数据更紧凑）
* - 适合归档数据
*/
public static void compressTable(String tableName) {
// 使用myisampack工具压缩表
// myisampack table_name
// myisamchk -r table_name
}
}
}
```
### 3.2 MyISAM核心特性
#### 3.2.1 快速读取
```java
/**
* MyISAM快速读取特性
*/
public class MyISAMFastRead {
/**
* 并发插入特性
*/
public static class ConcurrentInsert {
/**
* MyISAM支持在读取时插入数据
* 新数据插入到文件末尾，不影响现有数据的读取
*/
public void concurrentInsert(String tableName, Row newRow) {
// 检查是否有删除的空间
if (hasDeletedSpace(tableName)) {
// 填充删除的空间
fillDeletedSpace(tableName, newRow);
} else {
// 插入到文件末尾
appendToEnd(tableName, newRow);
}
}
/**
* 检查是否有删除的空间
*/
private boolean hasDeletedSpace(String tableName) {
// 检查是否有被标记删除的行
return false;
}
/**
* 填充删除的空间
*/
private void fillDeletedSpace(String tableName, Row row) {
// 重用删除的空间
}
/**
* 添加到文件末尾
*/
private void appendToEnd(String tableName, Row row) {
// 添加到文件末尾，不需要获取读锁
}
}
/**
* 延迟索引更新
*/
public static class DelayedIndexUpdate {
/**
* MyISAM支持延迟索引更新
* 数据先写入数据文件，索引稍后批量更新
*/
public void delayedInsert(String tableName, Row newRow) {
// 1. 立即写入数据文件
writeToDataFile(tableName, newRow);
// 2. 记录索引更新任务
scheduleIndexUpdate(tableName, newRow);
// 3. 批量更新索引
// 在以下情况下执行索引更新：
// - 表被关闭时
// - 执行FLUSH TABLES时
// - 缓冲区满时
}
}
/**
* 全文索引
*/
public static class FullTextIndex {
/**
* MyISAM支持全文索引（适合英文）
*/
public static class FullTextSearch {
/**
* 创建全文索引
*/
public void createFullTextIndex(String tableName, String... columns) {
// FULLTEXT index_name (column1, column2, ...)
}
/**
* 全文搜索
*/
public List search(String tableName, String query) {
// SELECT * FROM table WHERE MATCH(column) AGAINST('query')
return new ArrayList<>();
}
/**
* 布尔搜索
*/
public List booleanSearch(String tableName, String query) {
// SELECT * FROM table WHERE MATCH(column) AGAINST('query' IN BOOLEAN MODE)
return new ArrayList<>();
}
/**
* 查询扩展搜索
*/
public List expandedSearch(String tableName, String query) {
// SELECT * FROM table WHERE MATCH(column) AGAINST('query' WITH QUERY EXPANSION)
return new ArrayList<>();
}
}
}
/**
* 空间索引
*/
public static class SpatialIndex {
/**
* MyISAM支持空间索引（R树）
*/
public static class SpatialSearch {
/**
* 创建空间索引
*/
public void createSpatialIndex(String tableName, String column) {
// SPATIAL index_name (column)
}
/**
* 空间查询
*/
public List withinBox(String tableName, Geometry box) {
// SELECT * FROM table WHERE MBRContains(box, column)
return new ArrayList<>();
}
/**
* 距离查询
*/
public List distanceQuery(String tableName, Geometry point,
double distance) {
// SELECT * FROM table WHERE ST_Distance(column, point) <= distance
return new ArrayList<>();
}
}
}
}
```
#### 3.2.2 表级锁
```java
/**
* MyISAM表锁机制
*/
public class MyISAMTableLock {
/**
* 锁类型
*/
public enum LockType {
READ, // 读锁（共享锁）
WRITE, // 写锁（排他锁）
READ_LOCAL // 读本地锁（允许插入）
}
/**
* 锁兼容性矩阵
*/
public static class LockCompatibility {
private static final boolean[][] COMPATIBILITY_MATRIX = {
// READ WRITE READ_LOCAL
/*READ */ {true, false, true},
/*WRITE */ {false, false, false},
/*READ_LOCAL*/ {true, false, true}
};
public static boolean isCompatible(LockType request, LockType held) {
return COMPATIBILITY_MATRIX[held.ordinal()][request.ordinal()];
}
}
/**
* 表锁管理器
*/
public static class TableLockManager {
private Map locks = new ConcurrentHashMap<>();
/**
* 获取读锁
*/
public boolean acquireReadLock(String tableName, Transaction transaction) {
TableLock lock = locks.computeIfAbsent(tableName, k -> new TableLock());
return lock.acquire(transaction, LockType.READ);
}
/**
* 获取写锁
*/
public boolean acquireWriteLock(String tableName, Transaction transaction) {
TableLock lock = locks.computeIfAbsent(tableName, k -> new TableLock());
return lock.acquire(transaction, LockType.WRITE);
}
/**
* 获取本地读锁
*/
public boolean acquireReadLocalLock(String tableName, Transaction transaction) {
TableLock lock = locks.computeIfAbsent(tableName, k -> new TableLock());
return lock.acquire(transaction, LockType.READ_LOCAL);
}
/**
* 释放锁
*/
public void release(String tableName, Transaction transaction) {
TableLock lock = locks.get(tableName);
if (lock != null) {
lock.release(transaction);
}
}
}
/**
* 表锁
*/
public static class TableLock {
private Set readLocks = new HashSet<>();
private Set readLocalLocks = new HashSet<>();
private Transaction writeLock;
public synchronized boolean acquire(Transaction transaction, LockType type) {
switch (type) {
case READ:
// 读锁：可以与其他读锁和读本地锁共存
if (writeLock != null && writeLock != transaction) {
return false;
}
readLocks.add(transaction);
return true;
case WRITE:
// 写锁：不能与任何锁共存
if (!readLocks.isEmpty() || !readLocalLocks.isEmpty() ||
(writeLock != null && writeLock != transaction)) {
return false;
}
writeLock = transaction;
return true;
case READ_LOCAL:
// 读本地锁：可以与其他读锁和读本地锁共存
if (writeLock != null && writeLock != transaction) {
return false;
}
readLocalLocks.add(transaction);
return true;
default:
return false;
}
}
public synchronized void release(Transaction transaction) {
readLocks.remove(transaction);
readLocalLocks.remove(transaction);
if (writeLock == transaction) {
writeLock = null;
}
}
}
}
```
---
## 4. 核心特性对比
### 4.1 事务支持对比
```java
/**
* InnoDB vs MyISAM 事务支持对比
*/
public class TransactionComparison {
/**
* 事务支持对比表
*/
public static class TransactionSupportComparison {
public static void main(String[] args) {
System.out.println("=== 事务支持对比 ===");
System.out.println("特性\t\tInnoDB\t\tMyISAM");
System.out.println("----------------------------------------");
System.out.println("ACID支持\t✅\t\t❌");
System.out.println("原子性\t\t✅\t\t❌");
System.out.println("一致性\t\t✅\t\t❌");
System.out.println("隔离性\t\t✅\t\t❌");
System.out.println("持久性\t\t✅\t\t❌");
System.out.println("回滚\t\t✅\t\t❌");
System.out.println("Savepoint\t✅\t\t❌");
System.out.println("嵌套事务\t✅\t\t❌");
}
}
/**
* 事务隔离级别对比
*/
public static class IsolationLevelComparison {
public static void main(String[] args) {
System.out.println("\n=== 事务隔离级别对比 ===");
System.out.println("隔离级别\t\tInnoDB\t\tMyISAM");
System.out.println("----------------------------------------");
System.out.println("READ UNCOMMITTED\t✅\t\t❌");
System.out.println("READ COMMITTED\t✅\t\t❌");
System.out.println("REPEATABLE READ\t✅(默认)\t❌");
System.out.println("SERIALIZABLE\t\t✅\t\t❌");
}
}
/**
* InnoDB事务示例
*/
public static class InnoDBTransactionExample {
public void executeTransaction() {
Connection conn = null;
try {
conn = getInnoDBConnection();
conn.setAutoCommit(false);
// 执行多个SQL语句
Statement stmt = conn.createStatement();
stmt.executeUpdate("INSERT INTO account (user_id, balance) VALUES (1, 1000)");
stmt.executeUpdate("UPDATE account SET balance = balance - 100 WHERE user_id = 1");
stmt.executeUpdate("UPDATE account SET balance = balance + 100 WHERE user_id = 2");
// 提交事务
conn.commit();
System.out.println("事务提交成功");
} catch (SQLException e) {
// 回滚事务
if (conn != null) {
try {
conn.rollback();
System.out.println("事务回滚");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.setAutoCommit(true);
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
/**
* MyISAM无事务示例
*/
public static class MyISAMNoTransactionExample {
public void executeWithoutTransaction() {
Connection conn = null;
try {
conn = getMyISAMConnection();
// MyISAM不支持事务，每条语句自动提交
Statement stmt = conn.createStatement();
stmt.executeUpdate("INSERT INTO account (user_id, balance) VALUES (1, 1000)");
// 如果这里发生错误，前面的INSERT已经提交，无法回滚
stmt.executeUpdate("UPDATE account SET balance = balance - 100 WHERE user_id = 1");
stmt.executeUpdate("UPDATE account SET balance = balance + 100 WHERE user_id = 2");
System.out.println("执行完成（无事务保护）");
} catch (SQLException e) {
System.out.println("发生错误，但无法回滚已执行的语句");
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
private static Connection getInnoDBConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost/test", "user", "password");
}
private static Connection getMyISAMConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost/test", "user", "password");
}
}
```
### 4.2 锁机制对比
```java
/**
* InnoDB vs MyISAM 锁机制对比
*/
public class LockMechanismComparison {
/**
* 锁机制对比表
*/
public static class LockComparisonTable {
public static void main(String[] args) {
System.out.println("=== 锁机制对比 ===");
System.out.println("特性\t\t\tInnoDB\t\tMyISAM");
System.out.println("----------------------------------------");
System.out.println("锁粒度\t\t\t行锁\t\t表锁");
System.out.println("锁开销\t\t\t大\t\t小");
System.out.println("并发度\t\t\t高\t\t低");
System.out.println("死锁\t\t\t可能\t\t不可能");
System.out.println("锁升级\t\t\t不需要\t\t不需要");
System.out.println("意向锁\t\t\t✅\t\t❌");
System.out.println("间隙锁\t\t\t✅\t\t❌");
System.out.println("临键锁\t\t\t✅\t\t❌");
System.out.println("共享锁\t\t\t✅\t\t✅");
System.out.println("排他锁\t\t\t✅\t\t✅");
}
}
/**
* InnoDB行锁示例
*/
public static class InnoDBRowLockExample {
public void demonstrateRowLock() {
// 事务1
new Thread(() -> {
try (Connection conn = getInnoDBConnection()) {
conn.setAutoCommit(false);
// 只锁定ID=1的行
PreparedStatement pstmt = conn.prepareStatement(
"SELECT * FROM user WHERE id = 1 FOR UPDATE");
ResultSet rs = pstmt.executeQuery();
Thread.sleep(5000); // 模拟长时间处理
conn.commit();
System.out.println("事务1提交");
} catch (Exception e) {
e.printStackTrace();
}
}).start();
// 事务2
new Thread(() -> {
try (Connection conn = getInnoDBConnection()) {
conn.setAutoCommit(false);
// 可以访问ID=2的行，不会被阻塞
PreparedStatement pstmt = conn.prepareStatement(
"SELECT * FROM user WHERE id = 2 FOR UPDATE");
ResultSet rs = pstmt.executeQuery();
conn.commit();
System.out.println("事务2提交");
} catch (Exception e) {
e.printStackTrace();
}
}).start();
}
}
/**
* MyISAM表锁示例
*/
public static class MyISAMTableLockExample {
public void demonstrateTableLock() {
// 事务1
new Thread(() -> {
try (Connection conn = getMyISAMConnection()) {
// 锁定整个表
Statement stmt = conn.createStatement();
stmt.executeQuery("SELECT * FROM user WHERE id = 1 FOR UPDATE");
Thread.sleep(5000); // 模拟长时间处理
System.out.println("事务1释放锁");
} catch (Exception e) {
e.printStackTrace();
}
}).start();
// 事务2
new Thread(() -> {
try (Connection conn = getMyISAMConnection()) {
// 即使访问ID=2的行，也会被阻塞
Statement stmt = conn.createStatement();
stmt.executeQuery("SELECT * FROM user WHERE id = 2 FOR UPDATE");
System.out.println("事务2访问成功");
} catch (Exception e) {
e.printStackTrace();
}
}).start();
}
}
}
```
### 4.3 索引对比
```java
/**
* InnoDB vs MyISAM 索引对比
*/
public class IndexComparison {
/**
* 索引对比表
*/
public static class IndexComparisonTable {
public static void main(String[] args) {
System.out.println("=== 索引对比 ===");
System.out.println("特性\t\t\tInnoDB\t\tMyISAM");
System.out.println("----------------------------------------");
System.out.println("主键索引\t\t聚簇索引\t非聚簇索引");
System.out.println("辅助索引\t\t二级索引\t二级索引");
System.out.println("索引结构\t\tB+树\t\tB+树");
System.out.println("全文索引\t\t✅\t\t✅");
System.out.println("空间索引\t\t✅\t\t❌");
System.out.println("哈希索引\t\t自适应\t\t❌");
System.out.println("索引覆盖\t\t✅\t\t✅");
System.out.println("索引下推\t\t✅\t\t❌");
}
}
/**
* InnoDB聚簇索引
*/
public static class InnoDBClusteredIndex {
/**
* InnoDB聚簇索引特点：
* 1. 主键索引和数据存储在一起
* 2. 主键查询最快（一次I/O）
* 3. 辅助索引需要回表查询
*/
public static class ClusteredIndexStructure {
/**
* 聚簇索引叶子节点
*/
public static class ClusteredLeafNode {
private long primaryKey;
private byte[] rowData; // 完整的行数据
public ClusteredLeafNode(long primaryKey, byte[] rowData) {
this.primaryKey = primaryKey;
this.rowData = rowData;
}
/**
* 根据主键查询
*/
public byte[] selectByPrimaryKey(long key) {
// 直接从B+树中获取完整行数据
return rowData;
}
}
/**
* 辅助索引叶子节点
*/
public static class SecondaryLeafNode {
private long indexKey;
private long primaryKey; // 指向聚簇索引的主键
public SecondaryLeafNode(long indexKey, long primaryKey) {
this.indexKey = indexKey;
this.primaryKey = primaryKey;
}
/**
* 根据辅助索引查询（需要回表）
*/
public byte[] selectBySecondaryKey(long key) {
// 1. 从辅助索引获取主键
// 2. 再从聚簇索引获取完整行数据（回表）
return new byte[0];
}
}
}
}
/**
* MyISAM非聚簇索引
*/
public static class MyISAMNonClusteredIndex {
/**
* MyISAM非聚簇索引特点：
* 1. 所有索引都是非聚簇的
* 2. 索引和数据分离存储
* 3. 主键索引和辅助索引查询效率相同
*/
public static class NonClusteredIndexStructure {
/**
* 主键索引叶子节点
*/
public static class PrimaryKeyLeafNode {
private long primaryKey;
private long fileOffset; // 数据文件中的偏移量
public PrimaryKeyLeafNode(long primaryKey, long fileOffset) {
this.primaryKey = primaryKey;
this.fileOffset = fileOffset;
}
/**
* 根据主键查询
*/
public byte[] selectByPrimaryKey(long key) {
// 1. 从索引获取文件偏移量
// 2. 从数据文件读取行数据
return new byte[0];
}
}
/**
* 辅助索引叶子节点
*/
public static class SecondaryLeafNode {
private long indexKey;
private long fileOffset; // 数据文件中的偏移量
public SecondaryLeafNode(long indexKey, long fileOffset) {
this.indexKey = indexKey;
this.fileOffset = fileOffset;
}
/**
* 根据辅助索引查询
*/
public byte[] selectBySecondaryKey(long key) {
// 1. 从索引获取文件偏移量
// 2. 从数据文件读取行数据
return new byte[0];
}
}
}
}
}
```
### 4.4 性能对比
```java
/**
* InnoDB vs MyISAM 性能对比
*/
public class PerformanceComparison {
/**
* 性能对比表
*/
public static class PerformanceComparisonTable {
public static void main(String[] args) {
System.out.println("=== 性能对比 ===");
System.out.println("场景\t\t\tInnoDB\t\tMyISAM");
System.out.println("----------------------------------------");
System.out.println("简单SELECT\t\t中等\t\t快");
System.out.println("复杂SELECT\t\t快\t\t中等");
System.out.println("INSERT\t\t\t中等\t\t快");
System.out.println("UPDATE\t\t\t快\t\t慢");
System.out.println("DELETE\t\t\t快\t\t慢");
System.out.println("批量INSERT\t\t中等\t\t快");
System.out.println("COUNT(*)\t\t慢\t\t快");
System.out.println("并发读\t\t\t快\t\t中等");
System.out.println("并发写\t\t\t快\t\t慢");
System.out.println("混合读写\t\t快\t\t慢");
}
}
/**
* 读密集型场景性能测试
*/
public static class ReadHeavyBenchmark {
public void benchmark() {
int threadCount = 100;
int operationsPerThread = 1000;
// InnoDB测试
long innodbTime = benchmarkInnoDB(threadCount, operationsPerThread);
System.out.println("InnoDB读密集测试: " + innodbTime + "ms");
// MyISAM测试
long myisamTime = benchmarkMyISAM(threadCount, operationsPerThread);
System.out.println("MyISAM读密集测试: " + myisamTime + "ms");
// 结果分析
if (innodbTime < myisamTime) {
System.out.println("读密集场景: InnoDB更快（得益于缓存）");
} else {
System.out.println("读密集场景: MyISAM更快（无锁开销）");
}
}
private long benchmarkInnoDB(int threads, int ops) {
// 模拟InnoDB性能
return 5000;
}
private long benchmarkMyISAM(int threads, int ops) {
// 模拟MyISAM性能
return 4500;
}
}
/**
* 写密集型场景性能测试
*/
public static class WriteHeavyBenchmark {
public void benchmark() {
int threadCount = 100;
int operationsPerThread = 1000;
// InnoDB测试
long innodbTime = benchmarkInnoDB(threadCount, operationsPerThread);
System.out.println("InnoDB写密集测试: " + innodbTime + "ms");
// MyISAM测试
long myisamTime = benchmarkMyISAM(threadCount, operationsPerThread);
System.out.println("MyISAM写密集测试: " + myisamTime + "ms");
// 结果分析
if (innodbTime < myisamTime) {
System.out.println("写密集场景: InnoDB更快（行锁并发）");
} else {
System.out.println("写密集场景: MyISAM更快（无事务开销）");
}
}
private long benchmarkInnoDB(int threads, int ops) {
// 模拟InnoDB性能
return 6000;
}
private long benchmarkMyISAM(int threads, int ops) {
// 模拟MyISAM性能
return 12000;
}
}
/**
* 混合读写场景性能测试
*/
public static class MixedReadWriteBenchmark {
public void benchmark() {
int threadCount = 100;
int operationsPerThread = 1000;
double writeRatio = 0.3; // 30%写操作
// InnoDB测试
long innodbTime = benchmarkInnoDB(threadCount, operationsPerThread, writeRatio);
System.out.println("InnoDB混合测试: " + innodbTime + "ms");
// MyISAM测试
long myisamTime = benchmarkMyISAM(threadCount, operationsPerThread, writeRatio);
System.out.println("MyISAM混合测试: " + myisamTime + "ms");
// 结果分析
if (innodbTime < myisamTime) {
System.out.println("混合读写场景: InnoDB更快（MVCC + 行锁）");
} else {
System.out.println("混合读写场景: MyISAM更快（无事务开销）");
}
}
private long benchmarkInnoDB(int threads, int ops, double writeRatio) {
// 模拟InnoDB性能
return 7000;
}
private long benchmarkMyISAM(int threads, int ops, double writeRatio) {
// 模拟MyISAM性能
return 15000;
}
}
}
```
---
## 5. 性能基准测试
### 5.1 测试环境配置
```sql
-- 创建InnoDB测试表
CREATE TABLE innodb_test (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_name (name),
INDEX idx_age (age)
) ENGINE=InnoDB;
-- 创建MyISAM测试表
CREATE TABLE myisam_test (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_name (name),
INDEX idx_age (age)
) ENGINE=MyISAM;
-- 插入测试数据（100万条）
INSERT INTO innodb_test (name, email, age)
SELECT CONCAT('user', n), CONCAT('user', n, '@example.com'), 20 + n % 50
FROM (
SELECT @n := @n + 1 AS n
FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4,
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t5,
(SELECT @n := 0) init
) nums
WHERE n <= 1000000;
INSERT INTO myisam_test (name, email, age)
SELECT * FROM innodb_test;
```
### 5.2 读性能测试
```java
/**
* 读性能测试
*/
public class ReadPerformanceBenchmark {
/**
* 单行查询测试
*/
public static class SingleRowQuery {
public void benchmark() {
// InnoDB
long innodbTime = benchmarkQuery("SELECT * FROM innodb_test WHERE id = ?");
System.out.println("InnoDB单行查询: " + innodbTime + "ms");
// MyISAM
long myisamTime = benchmarkQuery("SELECT * FROM myisam_test WHERE id = ?");
System.out.println("MyISAM单行查询: " + myisamTime + "ms");
// 结果：MyISAM略快（无MVCC开销）
}
}
/**
* 范围查询测试
*/
public static class RangeQuery {
public void benchmark() {
// InnoDB
long innodbTime = benchmarkQuery("SELECT * FROM innodb_test WHERE age BETWEEN 20 AND 30");
System.out.println("InnoDB范围查询: " + innodbTime + "ms");
// MyISAM
long myisamTime = benchmarkQuery("SELECT * FROM myisam_test WHERE age BETWEEN 20 AND 30");
System.out.println("MyISAM范围查询: " + myisamTime + "ms");
// 结果：接近，取决于数据是否在缓存中
}
}
/**
* 聚合查询测试
*/
public static class AggregateQuery {
public void benchmark() {
// COUNT(*)
long innodbCountTime = benchmarkQuery("SELECT COUNT(*) FROM innodb_test");
System.out.println("InnoDB COUNT(*): " + innodbCountTime + "ms");
long myisamCountTime = benchmarkQuery("SELECT COUNT(*) FROM myisam_test");
System.out.println("MyISAM COUNT(*): " + myisamCountTime + "ms");
// 结果：MyISAM快得多（维护了行数统计）
// AVG()
long innodbAvgTime = benchmarkQuery("SELECT AVG(age) FROM innodb_test");
System.out.println("InnoDB AVG(age): " + innodbAvgTime + "ms");
long myisamAvgTime = benchmarkQuery("SELECT AVG(age) FROM myisam_test");
System.out.println("MyISAM AVG(age): " + myisamAvgTime + "ms");
// 结果：接近
}
}
/**
* 并发读测试
*/
public static class ConcurrentRead {
public void benchmark() {
int threadCount = 100;
// InnoDB
long innodbTime = benchmarkConcurrentRead("innodb_test", threadCount);
System.out.println("InnoDB并发读: " + innodbTime + "ms");
// MyISAM
long myisamTime = benchmarkConcurrentRead("myisam_test", threadCount);
System.out.println("MyISAM并发读: " + myisamTime + "ms");
// 结果：InnoDB更快（支持行锁，锁冲突少）
}
}
}
```
### 5.3 写性能测试
```java
/**
* 写性能测试
*/
public class WritePerformanceBenchmark {
/**
* 单行插入测试
*/
public static class SingleInsert {
public void benchmark() {
int insertCount = 10000;
// InnoDB
long innodbTime = benchmarkInsert("INSERT INTO innodb_test (name, email, age) VALUES (?, ?, ?)", insertCount);
System.out.println("InnoDB单行插入: " + innodbTime + "ms");
// MyISAM
long myisamTime = benchmarkInsert("INSERT INTO myisam_test (name, email, age) VALUES (?, ?, ?)", insertCount);
System.out.println("MyISAM单行插入: " + myisamTime + "ms");
// 结果：MyISAM略快（无事务开销）
}
}
/**
* 批量插入测试
*/
public static class BatchInsert {
public void benchmark() {
int batchSize = 1000;
int batchCount = 10;
// InnoDB
long innodbTime = benchmarkBatchInsert("innodb_test", batchSize, batchCount);
System.out.println("InnoDB批量插入: " + innodbTime + "ms");
// MyISAM
long myisamTime = benchmarkBatchInsert("myisam_test", batchSize, batchCount);
System.out.println("MyISAM批量插入: " + myisamTime + "ms");
// 结果：MyISAM更快（延迟索引更新）
}
}
/**
* 更新测试
*/
public static class Update {
public void benchmark() {
int updateCount = 10000;
// InnoDB
long innodbTime = benchmarkUpdate("UPDATE innodb_test SET age = age + 1 WHERE id = ?", updateCount);
System.out.println("InnoDB更新: " + innodbTime + "ms");
// MyISAM
long myisamTime = benchmarkUpdate("UPDATE myisam_test SET age = age + 1 WHERE id = ?", updateCount);
System.out.println("MyISAM更新: " + myisamTime + "ms");
// 结果：InnoDB快得多（行锁 vs 表锁）
}
}
/**
* 删除测试
*/
public static class Delete {
public void benchmark() {
int deleteCount = 10000;
// InnoDB
long innodbTime = benchmarkDelete("DELETE FROM innodb_test WHERE id = ?", deleteCount);
System.out.println("InnoDB删除: " + innodbTime + "ms");
// MyISAM
long myisamTime = benchmarkDelete("DELETE FROM myisam_test WHERE id = ?", deleteCount);
System.out.println("MyISAM删除: " + myisamTime + "ms");
// 结果：InnoDB快得多（行锁 vs 表锁）
}
}
/**
* 并发写测试
*/
public static class ConcurrentWrite {
public void benchmark() {
int threadCount = 100;
// InnoDB
long innodbTime = benchmarkConcurrentWrite("innodb_test", threadCount);
System.out.println("InnoDB并发写: " + innodbTime + "ms");
// MyISAM
long myisamTime = benchmarkConcurrentWrite("myisam_test", threadCount);
System.out.println("MyISAM并发写: " + myisamTime + "ms");
// 结果：InnoDB快得多（行锁并发 vs 表锁串行）
}
}
}
```
---
## 6. 适用场景分析
### 6.1 InnoDB适用场景
```java
/**
* InnoDB适用场景分析
*/
public class InnoDBUseCases {
/**
* 事务处理场景
*/
public static class TransactionProcessing {
/**
* 银行转账系统
* 需求：保证原子性，确保资金不丢失
*/
public void bankingTransfer() {
// InnoDB支持事务，可以保证转账的原子性
String sql = """
BEGIN;
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
UPDATE account SET balance = balance + 100 WHERE user_id = 2;
COMMIT;
""";
}
/**
* 订单处理系统
* 需求：订单、库存、支付需要保持一致性
*/
public void orderProcessing() {
// InnoDB支持事务，可以保证订单流程的一致性
String sql = """
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (?, ?);
UPDATE inventory SET stock = stock - 1 WHERE product_id = ?;
INSERT INTO payments (order_id, amount) VALUES (?, ?);
COMMIT;
""";
}
}
/**
* 高并发场景
*/
public static class HighConcurrency {
/**
* 电商网站
* 需求：大量用户同时浏览和购买商品
*/
public void ecommerceWebsite() {
// InnoDB支持行锁，允许高并发读写
// - 用户浏览商品（读操作）
// - 用户下单购买（写操作）
// 行锁保证不同的用户可以同时操作不同的商品
}
/**
* 社交媒体平台
* 需求：大量用户同时发布和浏览内容
*/
public void socialMedia() {
// InnoDB支持MVCC，读写不阻塞
// - 用户发布动态（写操作）
// - 用户浏览动态（读操作）
// MVCC保证读操作不会被写操作阻塞
}
}
/**
* 数据完整性要求高的场景
*/
public static class DataIntegrity {
/**
* 库存管理系统
* 需求：防止超卖，保证库存准确性
*/
public void inventoryManagement() {
// InnoDB支持行锁和事务，可以防止超卖
String sql = """
BEGIN;
UPDATE inventory
SET stock = stock - 1
WHERE product_id = ? AND stock > 0;
SELECT stock FROM inventory WHERE product_id = ?;
IF stock > 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
""";
}
/**
* 用户认证系统
* 需求：保证用户数据一致性
*/
public void userAuthentication() {
// InnoDB支持外键约束，保证数据完整性
// - 用户表
// - 用户信息表
// - 用户权限表
// 外键约束保证数据一致性
}
}
/**
* 需要崩溃恢复的场景
*/
public static class CrashRecovery {
/**
* 金融交易系统
* 需求：系统崩溃后能够恢复未完成的事务
*/
public void financialTrading() {
// InnoDB支持崩溃恢复
// - Redo Log记录已提交的事务
// - Undo Log记录未提交的事务
// 系统重启后自动恢复
}
/**
* 日志系统
* 需求：保证日志不丢失
*/
public void loggingSystem() {
// InnoDB支持持久性
// 事务提交后，数据持久化到磁盘
// 即使系统崩溃，数据也不会丢失
}
}
}
```
### 6.2 MyISAM适用场景
```java
/**
* MyISAM适用场景分析
*/
public class MyISAMUseCases {
/**
* 读密集型场景
*/
public static class ReadHeavy {
/**
* 数据仓库
* 需求：大量查询，很少修改
*/
public void dataWarehouse() {
// MyISAM读取性能好
// - 查询性能快
// - COUNT(*)速度快（维护了行数统计）
// - 索引文件紧凑
}
/**
* 日志分析系统
* 需求：读取历史日志进行分析
*/
public void logAnalysis() {
// MyISAM适合只读或极少修改的数据
// - 压缩表功能节省空间
// - 读取速度快
}
/**
* 统计报表系统
* 需求：频繁执行COUNT、SUM等聚合操作
*/
public void reportingSystem() {
// MyISAM的聚合操作性能好
// - COUNT(*)使用存储的行数
// - 聚合函数执行快
}
}
/**
* 全文搜索场景
*/
public static class FullTextSearch {
/**
* 内容管理系统
* 需求：对文章内容进行全文搜索
*/
public void contentManagement() {
// MyISAM支持全文索引
// - 英文全文搜索效果好
// - 搜索速度快
}
/**
* 文档管理系统
* 需求：搜索文档内容
*/
public void documentManagement() {
// MyISAM全文索引适合英文文档
// - 支持布尔搜索
// - 支持查询扩展
}
}
/**
* 数据归档场景
*/
public static class DataArchiving {
/**
* 历史数据归档
* 需求：存储历史数据，节省空间
*/
public void historicalDataArchiving() {
// MyISAM支持压缩表
// - 压缩比可达40-70%
// - 压缩后只读
// - 查询速度更快
}
/**
* 备份数据存储
* 需求：存储备份数据
*/
public void backupStorage() {
// MyISAM压缩表适合备份数据
// - 节省存储空间
// - 只读访问
}
}
/**
* 临时表场景
*/
public static class TemporaryTables {
/**
* ETL过程
* 需求：中间结果存储
*/
public void etlProcess() {
// MyISAM适合作为临时表
// - 插入性能好
// - 读取性能好
// - 不需要事务支持
}
/**
* 数据导入导出
* 需求：导入数据前的临时存储
*/
public void dataImportExport() {
// MyISAM适合临时存储
// - 批量插入性能好
// - 表锁对单线程操作无影响
}
}
/**
* 空间数据场景
*/
public static class SpatialData {
/**
* 地理信息系统
* 需求：存储和查询空间数据
*/
public void gisApplication() {
// MyISAM支持空间索引
// - 支持几何数据类型
// - 支持空间查询
// - 支持距离计算
}
/**
* 位置服务
* 需求：查找附近的地点
*/
public void locationService() {
// MyISAM空间索引适合位置查询
// - 支持MBR查询
// - 支持距离查询
}
}
}
```
---
## 7. 选择策略与最佳实践
### 7.1 选择决策树
```java
/**
* 存储引擎选择决策树
*/
public class StorageEngineDecisionTree {
/**
* 决策流程
*/
public static class DecisionProcess {
public StorageEngine chooseEngine(Requirements requirements) {
// 1. 检查事务需求
if (requirements.needsTransaction()) {
return StorageEngine.INNODB;
}
// 2. 检查外键约束需求
if (requirements.needsForeignKey()) {
return StorageEngine.INNODB;
}
// 3. 检查并发需求
if (requirements.needsHighConcurrency()) {
return StorageEngine.INNODB;
}
// 4. 检查崩溃恢复需求
if (requirements.needsCrashRecovery()) {
return StorageEngine.INNODB;
}
// 5. 检查读密集场景
if (requirements.isReadHeavy() && !requirements.isWriteHeavy()) {
// 5.1 检查全文搜索需求
if (requirements.needsFullTextSearch()) {
return StorageEngine.MYISAM;
}
// 5.2 检查空间索引需求
if (requirements.needsSpatialIndex()) {
return StorageEngine.MYISAM;
}
// 5.3 检查数据归档需求
if (requirements.isArchivalData()) {
return StorageEngine.MYISAM;
}
// 读密集场景，InnoDB和MyISAM都可以
// 根据其他需求进一步判断
}
// 6. 检查写密集场景
if (requirements.isWriteHeavy()) {
return StorageEngine.INNODB;
}
// 7. 默认选择InnoDB
return StorageEngine.INNODB;
}
}
/**
* 需求分析
*/
public static class Requirements {
private boolean needsTransaction;
private boolean needsForeignKey;
private boolean needsHighConcurrency;
private boolean needsCrashRecovery;
private boolean isReadHeavy;
private boolean isWriteHeavy;
private boolean needsFullTextSearch;
private boolean needsSpatialIndex;
private boolean isArchivalData;
// getters and setters
}
/**
* 存储引擎枚举
*/
public enum StorageEngine {
INNODB,
MYISAM,
MEMORY,
CSV
}
}
```
### 7.2 混合使用策略
```sql
-- 不同表使用不同存储引擎
-- 用户表：需要事务和外键，使用InnoDB
CREATE TABLE user (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_username (username),
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB;
-- 订单表：需要事务，使用InnoDB
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES user(id)
) ENGINE=InnoDB;
-- 文章表：需要全文搜索，使用MyISAM
CREATE TABLE articles (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
author_id BIGINT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FULLTEXT INDEX ft_title_content (title, content),
INDEX idx_author_id (author_id)
) ENGINE=MyISAM;
-- 文章元数据：需要事务，使用InnoDB
CREATE TABLE article_metadata (
id BIGINT PRIMARY KEY,
views INT DEFAULT 0,
likes INT DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (id) REFERENCES articles(id)
) ENGINE=InnoDB;
-- 日志表：只读归档，使用压缩MyISAM
CREATE TABLE logs_2023 (
id BIGINT,
message TEXT NOT NULL,
level VARCHAR(10) NOT NULL,
created_at TIMESTAMP NOT NULL,
PRIMARY KEY (id, created_at),
INDEX idx_created_at (created_at)
) ENGINE=MyISAM;
-- 压缩日志表
-- myisampack logs_2023
-- myisamchk -r logs_2023
```
### 7.3 迁移建议
```java
/**
* MyISAM迁移到InnoDB
*/
public class MigrationGuide {
/**
* 迁移步骤
*/
public static class MigrationSteps {
public void migrateMyISAMToInnoDB(String tableName) {
// 1. 备份数据
backupTable(tableName);
// 2. 检查外键依赖
checkForeignKeyDependencies(tableName);
// 3. 修改存储引擎
alterEngine(tableName, "InnoDB");
// 4. 验证数据
validateData(tableName);
// 5. 性能测试
performanceTest(tableName);
}
/**
* 批量迁移
*/
public void batchMigrate(List tableNames) {
for (String tableName : tableNames) {
try {
migrateMyISAMToInnoDB(tableName);
} catch (Exception e) {
System.err.println("迁移失败: " + tableName);
e.printStackTrace();
}
}
}
}
/**
* 迁移注意事项
*/
public static class MigrationConsiderations {
/**
* 全文索引迁移
*/
public void handleFullTextIndex(String tableName) {
// InnoDB 5.6+ 支持全文索引
// 但中文支持有限，可能需要使用第三方全文搜索引擎
}
/**
* 空间索引迁移
*/
public void handleSpatialIndex(String tableName) {
// InnoDB 5.7+ 支持空间索引
// 迁移时需要检查版本兼容性
}
/**
* 表大小限制
*/
public void checkTableSizeLimit(String tableName) {
// MyISAM支持更大的表（InnoDB最大64TB）
// 但MyISAM有单个文件大小限制（2-4GB）
}
/**
* 字符集迁移
*/
public void checkCharacterSet(String tableName) {
// 确保字符集兼容
// 建议使用utf8mb4
}
}
/**
* 性能优化
*/
public static class PerformanceOptimization {
/**
* 调整InnoDB参数
*/
public void optimizeInnoDBSettings() {
String sql = """
-- 增加缓冲池大小
SET GLOBAL innodb_buffer_pool_size = 4G;
-- 增加缓冲池实例数
SET GLOBAL innodb_buffer_pool_instances = 8;
-- 调整日志文件大小
SET GLOBAL innodb_log_file_size = 256M;
-- 调整日志缓冲区大小
SET GLOBAL innodb_log_buffer_size = 64M;
-- 启用自适应哈希索引
SET GLOBAL innodb_adaptive_hash_index = ON;
""";
}
/**
* 创建索引
*/
public void createIndexes(String tableName) {
String sql = """
-- 创建主键（如果不存在）
ALTER TABLE {tableName} ADD PRIMARY KEY (id);
-- 创建必要的索引
CREATE INDEX idx_column1 ON {tableName} (column1);
CREATE INDEX idx_column2 ON {tableName} (column2);
""".replace("{tableName}", tableName);
}
}
}
```
---
## 8. 总结
### 8.1 核心差异总结
| 特性 | InnoDB | MyISAM |
|------|--------|--------|
| **事务支持** | ✅ 完整ACID | ❌ 无事务 |
| **锁机制** | 行锁（高并发） | 表锁（低并发） |
| **外键约束** | ✅ 支持 | ❌ 不支持 |
| **崩溃恢复** | ✅ Redo/Undo日志 | ❌ 需要手动修复 |
| **索引类型** | 聚簇索引 | 非聚簇索引 |
| **全文索引** | ✅ 5.6+支持 | ✅ 原生支持 |
| **空间索引** | ✅ 5.7+支持 | ✅ 原生支持 |
| **COUNT(*)** | 慢（扫描表） | 快（存储行数） |
| **并发读** | 快（MVCC） | 中等（表锁） |
| **并发写** | 快（行锁） | 慢（表锁） |
| **主键查询** | 快（聚簇索引） | 中等（非聚簇索引） |
| **批量插入** | 中等 | 快 |
| **数据压缩** | ❌ | ✅ 压缩表 |
### 8.2 选择建议
**选择InnoDB的场景：**
- 需要事务支持（金融、电商、订单系统）
- 需要高并发读写
- 需要数据完整性保证（外键约束）
- 需要崩溃恢复能力
- 需要行级锁
- 频繁的更新和删除操作
**选择MyISAM的场景：**
- 读密集型应用（数据仓库、报表系统）
- 需要全文搜索（英文内容）
- 需要空间索引（GIS应用）
- 数据归档（压缩表）
- 只读或极少修改的数据
- 需要COUNT(*)性能
- 临时表
### 8.3 最佳实践
1. **默认使用InnoDB**：除非有特殊需求，否则优先选择InnoDB
2. **混合使用**：不同表根据需求选择不同的存储引擎
3. **定期备份**：无论使用哪种存储引擎，都要定期备份数据
4. **监控性能**：根据实际使用情况调整存储引擎配置
5. **版本兼容**：升级MySQL时注意存储引擎的兼容性
6. **测试验证**：迁移存储引擎前要充分测试
### 8.4 未来趋势
随着MySQL版本的更新，InnoDB的功能越来越强大，而MyISAM的使用场景越来越有限。MySQL 8.0中，InnoDB已经成为默认且推荐的存储引擎，而MyISAM主要保留用于向后兼容和特定场景。建议在新项目中优先考虑InnoDB，除非有明确的需求需要使用MyISAM的特性。