mysql資料庫檔案簡介和應用

存放目錄：

用 whereis my.cnf 檢視mysql配置檔案的目錄，檢視my.cnf的datadir參數可找到mysql資料庫檔案的存放目錄。

本機存放的目錄為/var/lib/mysql，進入該目錄可以看到以資料庫名命名的檔案夾。檔案夾裡便存放着該資料庫的所有資料庫檔案。

資料庫檔案簡介：

資料庫檔案分為 : mysql所建的資料庫檔案、mysql所用引擎建立的資料庫檔案。

.frm 儲存與之同名的表的原資料資訊，包含表結構定義等。與引擎無關。該檔案可用來恢複或修改表結構。

MyISAM引擎的檔案：

*.myd (表資料檔案)  

*.myi (索引檔案)

InnoDB引擎的檔案：

ibdata1、ibdata2等：系統表空間檔案，存儲InnoDB系統資訊和使用者資料庫表資料和索引，所有表共用。

*.ibd檔案：單表表空間檔案，每個表使用一個表空間檔案（file per table），存放使用者資料庫表資料和索引。

b_logfile1、ib_logfile2 :日志檔案，日志檔案大小在my.cnf檔案中配置：innodb_log_file_size  = 256M  innodb_log_files_in_group = 2

Innodb存儲引擎可以使用共享表空間或獨立表空間。

1.獨立表空間： 為每個表建立一個.ibd檔案用來存儲資料，這樣，mysql就将innodb表的資料存入各自對應的.ibd檔案中，但結構等資訊還是會寫入ibdata。

　　　　　　　　将innodb_file_per_table加到配置檔案中，便可使用獨立表空間。

2.共享表空間： 将innodb_file_per_table關閉之後，建立innoDB表時隻生成.frm檔案，資料和索引都儲存在共享表空間ibdata1中。這樣的缺點是拷貝時必須拷貝整個大檔案，

                      而且删除表後容易産生碎片。

                      ibdata1的大小在my.cnf檔案中配置：innodb_data_file_path = ibdata1:10G:autoextend:max:500M

應用：

一、恢複實體備份:

1.MyISAM引擎的表可以簡單的複制每個表的.frm,.MYI,.MYD檔案到新資料庫對應的檔案夾中即可。

2.Innodb引擎的表，一直沒有通過拷貝資料庫檔案而成功恢複過。有待日後再補充。

二、通過修改.frm來加快alter table操作的速度：

altertable操作速度慢，以表test為例：

mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT '',
  `name1` char(1) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------+------
| id   | name |
+------+------
|    1 |      |  
|    3 |      |   
|    4 |      |    
+------+------
3 rows in set (0.00 sec)　　      

通過show profile來分析alter執行速度。

mysql> alter table test add column name1 char default "";
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> show profile for query 8;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000082 |
| checking permissions | 0.000010 |
| checking permissions | 0.000012 |
| init                 | 0.000054 |
| Opening tables       | 0.000023 |
| System lock          | 0.000011 |
| Table lock           | 0.000014 |
| setup                | 0.000033 |
| creating table       | 0.002701 |
| After create         | 0.000120 |
| copy to tmp table    | 0.000589 |
| rename result table  | 0.002465 |
| end                  | 0.000067 |
| query end            | 0.000012 |
| freeing items        | 0.000044 |
| cleaning up          | 0.000011 |
+----------------------+----------+
16 rows in set (0.00 sec)      

從日志可看出，這個alter實作的方法是：用新的結建構立一個空表，從舊表中查出所有資料插入新表，然後删除舊表。

不過不是所有的alter操作都會引起表重建。比如alter column。 （alter table 允許使用alter column, modify column, change column語句修改列，這三種操作都是不一樣的，這個日後再詳細說）

alter column語句直接修改.frm檔案而不涉及表資料，是以會快很多。

移除一個列的auto_increment屬性；增加、移除或更改ENum和set常量，是可以不用重建表的，這些就可以通過修改.frm檔案直接來改變表結構。

方法如下：

1.建立一張相同結構的空表，并進行所需要的修改。

2.flush tables with read lock 來關閉所有正在使用的表，并禁止任何表被打開。

3.交換.frm檔案

4.執行 unlock tables來釋放步驟2的讀鎖。