當對於一個大表進行ALTER TABLE的時候,性能問題就產生了。MySQL大部分改動的步驟如下:根據新的表結構創建一個空表,從舊表中把資料取出來插入到新表中,在刪除舊表。這是個非常漫長的過程。許多人ALTER TABLE之後,都有等待1小時或者1天的痛苦經歷。
MySQL AB已經開始提升這方面的性能了。一些即將到來的特性是支援"線上"的操作,而不會去鎖定表。InnoDB的開發者也在積極努力開發以排序來創建索引。MyISAM已經支援這一特性了,結果就是索引更快並且壓縮了索引佈局。
並不是所有的ALTER TABLE都會使表重建。舉個例子,你可以用兩個方法更改或者刪除列預設值(一個快,一個慢)。如果你想更改一個film的租賃期限為原來的3天到現在的5天。方法如下:

 

mysql> ALTER TABLE sakila.film
-> MODIFY COLUMN rental_duration TINYINT(3) NOT Null DEFAULT 5;
用SHOW STATUS來監控這個語句,它做了1000次讀和1000次插入。換句話說就是複製一個表到新表中。即使這個列的類型,大小,是否為Null都沒有改變。
原理上來說,MySQL可以跳過創建新表。這個預設的值實際存儲在.frm檔中。因此你可以更改它而沒有必要接觸表。MySQL也不會做優化,然而任何的MODIFY COLUMN都會導致表的重建。
你可以使用ALTER COLUMN來修改:

 

mysql> ALTER TABLE sakila.film
-> ALTER COLUMN rental_duration SET DEFAULT 5;
這個語句修改了.frm檔而沒有去動作表。結果就是速度非常之快。
僅僅修改.frm檔
我們發現修改標的.frm檔速度非常快並且當它不能那麼做的時候,MySQL有的時候會重建表。如果你願意承擔一部分風險,你可以告訴MySQL做一些類型的修改而不去重建表。
你可以不用重建表來做如下類型的操作:
移除列的AUTO_INCREMENT屬性。
添加,移除,更改ENUM和SET。如果你移除了一個常量並且一些行包含這個值,查詢語句返回這個值將會是空字串
基本的技術就是創建一個.frm檔並且拷貝它到以存在表的.frm檔的位置。步驟如下:
創建一個空表,當然表佈局必須是準確的。除了一些更改的項。
執行FLUSH TABLES WITH READ LOCK.這一步將關閉使用的所有的表並且防止了這些表被打開。
交換.frm檔。
執行UNLOCK TABLES釋放讀鎖。
為了做例子,我們給skila.film表的rating列添加一個常量。當前的列如下:

 

MySQL> SHOW COLUMNS FROM sakila.film LIKE 'rating';
+--------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | |
+--------+------------------------------------+------+-----+---------+-------+
我們添加一個PG-14到這列中。

 

mysql> CREATE TABLE sakila.film_new LIKE sakila.film;
mysql> ALTER TABLE sakila.film_new
-> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17', 'PG-14')
-> DEFAULT 'G';
mysql> FLUSH TABLES WITH READ LOCK;
注意一下我們實在最後添加這個PG-14的,而不是在中間,那樣做的話就會修改現有的值了,R值變為PG-14,NC-17變為R依此類推。
現在連交換.frm檔,作業系統命令如下

 

root:/var/lib/MySQL/sakila# mv film.frm film_tmp.frm
root:/var/lib/mysql/sakila# mv film_new.frm film.frm
root:/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm
在回到MySQL提示符,我們要解鎖,再來看看更改的結果。

 

mysql> UNLOCK TABLES;
mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating'G
*************************** 1. row ***************************
Field: rating
Type: enum('G','PG','PG-13','R','NC-17','PG-14')
最後一步要做的就是刪除我們已創建的表。

 

mysql> DROP TABLE sakila.film_new;
快速的創建MyISAM索引
高效的讀取MyISAM表一般的技巧是,關閉鍵,讀取資料,重新啟用鍵。

 

mysql> ALTER TABLE test.load_data DISABLE KEYS;
-- load the data
mysql> ALTER TABLE test.load_data ENABLE KEYS;
這樣可以工作的原因是MyISAM延遲了創建鍵值直到資料讀取之後,重要的是,它可以有序的創建索引。結果就非床快並且無碎片和壓縮的索引樹。
不過,對於唯一索引這種方法就不行了。因為DISABLE KEYS僅僅應用於非唯一的索引。MyISAM在記憶體中創建唯一索引並且讀取每一行來校驗唯一性。一旦索引大小超出了記憶體大小,讀取會極度緩慢。
前一部分所說的ALTER TABLE的技巧,可以加速這個過程,前提是你需要多做一點工作和承擔一部分風險。這對於備份來說很有用。比如,當你發現所有的資料是不正確並且不需要做唯一性檢查。
你需要做的步驟如下:
創建一個期望的表。但是不要有任何的索引。
讀取資料來創建MYD檔。
創建另一個空表,這次要包含索引。這會創建.frm和.MYI檔。
用個讀鎖來刷新表。
對第二個表的.frm和.MYI進行重命名。因此MySQL會把它們當作第一個表使用。
釋放讀鎖。
使用REPAIR TABLE來創建表的索引。會以排序的方式創建索引,包括了唯一索引。
這個方法對於大表來說,速度格外的快。
創作者介紹
創作者 shadow 的頭像
shadow

資訊園

shadow 發表在 痞客邦 留言(0) 人氣()