MySQL服务器采用了模块化风格,各部分之间保持相对独立,尤其体现在存储架构上。存储引擎负责 管理数据存储,以及MySQL的索引管理。通过定义的API,MySQL服务器能够与存储引擎进行通信。目前使用最多的是MyISAM和InnoDB。 InnoDB被Oracle收购后,MySQL自行开发的新存储引擎Falcon将在MySQL6.0版本引进。
MyISAM引擎是一种非事务性的引擎,提供高速存储和检索,以及全文搜索能力,适合数据仓库等查询频繁的应用。MyISAM中,一个table实际保存为三个文件,.frm存储表定义,.MYD存储数据,.MYI存储索引。
InnoDB 则是一种支持事务的引擎。所以的数据存储在一个或者多个数据文件中,支持类似于Oracle的锁机制。一般在OLTP应用中使用较广泛。如果没有指定 InnoDB配置选项,MySQL将在MySQL数据目录下创建一个名为ibdata1的自动扩展数据文件,以及两个名为ib_logfile0和 ib_logfile1的日志文件。
创建table时可以通过engine关键字指定使用的存储引擎,如果省略则使用系统默认的存储引擎:
CREATE TABLE t (i INT) ENGINE = MYISAM;
查看系统中支持的存储引擎类型:
mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | YES | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | YES | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)
标准安装程序中只提供部分引擎的支持,如果需要使用其他的存储引擎,需要使用源代码加不同的参数重新编译。其中DEFAULT表明系统的默认存储引擎,可以通过修改配置参数来变更:
default-storage-engine=MyISAM
查看某个存储引擎的具体信息
mysql> show engine InnoDB status/G;
*************************** 1. row ***************************
Status:
=====================================
071201 14:34:13 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 10, signal count 10
Mutex spin waits 0, rounds 100, OS waits 3
RW-shared spins 12, OS waits 6; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 59141
Purge done for trx's n:o < 0 58137 undo n:o < 0 0
History list length 3
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3100
MySQL thread id 3, query id 32 localhost 127.0.0.1 root
show engine InnoDB status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
40 OS file reads, 35 OS file writes, 24 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 8755515
Log flushed up to 0 8755515
Last checkpoint at 0 8755515
0 pending log writes, 0 pending chkp writes
18 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 15529216; in additional pool allocated 1401728
Buffer pool size 512
Free buffers 480
Database pages 31
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 30, created 1, written 18
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 508, state: waiting for server activity
Number of rows inserted 2, updated 0, deleted 0, read 11
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified
*************************** 1. row ***************************
Status:
=====================================
071201 14:34:13 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 10, signal count 10
Mutex spin waits 0, rounds 100, OS waits 3
RW-shared spins 12, OS waits 6; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 59141
Purge done for trx's n:o < 0 58137 undo n:o < 0 0
History list length 3
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3100
MySQL thread id 3, query id 32 localhost 127.0.0.1 root
show engine InnoDB status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
40 OS file reads, 35 OS file writes, 24 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 8755515
Log flushed up to 0 8755515
Last checkpoint at 0 8755515
0 pending log writes, 0 pending chkp writes
18 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 15529216; in additional pool allocated 1401728
Buffer pool size 512
Free buffers 480
Database pages 31
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 30, created 1, written 18
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 508, state: waiting for server activity
Number of rows inserted 2, updated 0, deleted 0, read 11
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified