myisam、innodb物理结构

1.MyIASM引擎：

     每张表有三个文件：

          .frm     :表结构

          .MYD   :表数据

          .MYI     :表索引

2.InnoDB引擎：

          所有表可以共享一个表空间文件.

          建议：每张表使用一个独立的表空间文件。

数据文件的保存目录见参数：

innodb_data_file_path

| innodb_data_file_path                    | ibdata1:12M:autoextend |

ibdata1:12M:autoextend

表示---- 文件名:大小:是否自动扩展:最大文件大小 例如：ibdata01.df:2048M:autoextend:max:100G

每张表使用独立表空间见参数：

mysql> show global variables like 'innodb_file_per_table%' ;

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

1 row in set (0.00 sec)

如果这个值为on表示启用了独立表空间文件。

 修改my.cnf中的

[MYSQLD]

innodb_file_per_table=on

重启mysql生效。

或者使用:

set global innodb_file_per_table=1  生效，只对以后的会话生效。

innodb数据文件：====

          .frm     ：表结构

          .ibd     ：表空间（表数据和表索引）

innodb_page_size：指定innodb中最小的管理单位，默认16k，类似于oracle的blocksize，可以通过在创建mysql实例的时候指定innodb_page_size设置，可以为4k，8k，16k

(root@localhost)[test2]> show engine innodb status\G            --查看Innodb的基础状态信息

*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2015-10-17 20:05:23 40832940 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 15 srv_active, 0 srv_shutdown, 4644 srv_idle
srv_master_thread log flush and writes: 4659
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 26
OS WAIT ARRAY INFO: signal count 26
Mutex spin waits 7, rounds 210, OS waits 5
RW-shared spins 21, rounds 630, OS waits 20
RW-excl spins 1, rounds 30, OS waits 1
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 30.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 8494
Purge done for trx's n:o < 8493 undo n:o < 0 state: running but idle
History list length 21
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 8, OS thread handle 0x40832940, query id 203 localhost root init
show engine innodb status
---TRANSACTION 8474, not started
MySQL thread id 4, OS thread handle 0x41a15940, query id 128 localhost root cleaning up
---TRANSACTION 8480, not started
MySQL thread id 3, OS thread handle 0x41a56940, query id 174 localhost root cleaning up
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
202 OS file reads, 468 OS file writes, 109 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 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1706574
Log flushed up to   1706574
Pages flushed up to 1706574
Last checkpoint at  1706574
0 pending log writes, 0 pending chkp writes
54 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 59909
Buffer pool size   8192
Free buffers       7986
Database pages     206
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 187, created 19, written 96
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 206, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 30952, id 1181079872, state: sleeping
Number of rows inserted 8, updated 0, deleted 0, read 140
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)