Schema與數據類型優化

只修改.frm文件

從前面的例子中可以看到修改表的.frm文件是很快的，但MySQL有時候會在沒有必要的時候也重建.如果愿意冒一些風險，可以讓MySQL做一些其他類型的修改而不用重建表。下面這些操作是有可能不需要重建表的:

• 1.移除(不是增加)一個列的AUTO_INCREMENT屬性
• 2.增加、移除，或更改ENUM和SET常量。如果移除的是已經有行數據用到其值的變量，查詢將會返回一個空字符串

基本的技術是為想要的表結構創建一個新的.frm文件，然后用它替換掉已經存在的那張表的.frm文件，像下面這樣:

• 1.創建一張有相同結構的空表，并進行所需要的修改(例如增加ENUM常量)
• 2.執行FLUSH TABLES WITH READ LOCK.這將會關閉所有正在使用的表，并且進制任何表被打開
• 3.交換.frm文件
• 4.執行UNLOCK TABLES來釋放第2步的讀鎖
  下面以給sakila.film表的rating列增加一個常量為例來說明。當前列看起來如下

mysql> SHOW COLUMNS FROM film LIKE 'rating';
+--------+------------------------------------+------+-----+---------+-------+
| Field  | Type                               | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| rating | enum('G','PG','PG-13','R','NC-17') | YES  |     | G       |       |
+--------+------------------------------------+------+-----+---------+-------+
1 row in set (0.07 sec)

假設我們需要為那些對電影更加謹慎的父母們增加一個PG-14的電影分級：

mysql> CREATE TABLE film_new LIKE film;
Query OK, 0 rows affected (0.03 sec)mysql> ALTER TABLE film_new-> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17','PG-14')-> DEFAULT 'G';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)

注意，我們是在常量列表的末尾增加一個新的值。如果把新增的值放在中間，例如PG-13之后，則會導致已經存在的數據的含義被改變:已經存在的R值將變成PG-14,而已經存在的NC-17將變成R，等等。
接下來用操作系統的命令交換.frm文件

/var/lib/mysql/sakila# mv film.frm film_tmp.film
/var/lib/mysql/sakila# mv film_new.frm film.frm
/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

再回到MySQL 命令行，現在可以解鎖表并且看到變更后的效果了:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (18.96 sec)
mysql> SHOW COLUMNS FROM film LIKE 'rating';
+--------+--------------------------------------------+------+-----+---------+-------+
| Field  | Type                                       | Null | Key | Default | Extra |
+--------+--------------------------------------------+------+-----+---------+-------+
| rating | enum('G','PG','PG-13','R','NC-17','PG-14') | YES  |     | G       |       |
+--------+--------------------------------------------+------+-----+---------+-------+
1 row in set (0.09 sec)

最后要做的事刪除為完成這個操作而創建的輔助表:

mysql>DROP TABLE film_new;

快速創建MyISAM索引

為了高效地載入數據到MyISAM表中，有一個常用的技巧是先禁用索引、載入數據，然后重新啟用索引:

mysql>ALTER TABLE test.load_data DISABLE KEYS;
mysql>ALTER TABLE test.load_data ENABLE KEYS;

這個技巧能夠發揮作用，是因為構建索引的工作被延遲到數據完全載入以后，這個時候已經可以通過排序來構建索引了，這樣做會快很多，并且使得索引樹的碎片更少、更緊湊。
不幸的是，這個辦法對唯一索引無效，因為DISABLE KEYS只對非唯一索引有效。MyISAM會在內存中構造唯一索引，并且為載入的每一行檢查唯一性。一旦索引的大小超過了有效內存大小，載入操作就會變得越來越慢。
在現代版本的InnoDB版本中，有一個類似的技巧，這依賴于InnoDB的快速在線索引創建功能。這個技巧是，先刪除所有的非唯一索引，然后增加新的列，最后重新創建刪除掉的索引。Percona Server可以自動完成這些操作步驟。也可以使用像前面說的ALTER TABLE的駭客方法來加速這個操作，但需要多做一些工作并且承擔一定的風險。這對備份中載入數據是很有用的，例如，當已經直到所有的數據都是有效的并且沒有必要做唯一性檢查就可以這么來操作。
下面是操作步驟:

• 1.用需要的表結構創建一張表，但是不包括索引。
• 2.載入數據到表以后構建.MYD文件
• 3.按照需要的結構創建另外一張空表，這次要包含索引。這回創建需要的.frm和.MYI文件
• 4.獲取讀鎖并刷新表
• 5.重命名第二張表的.frm和MYI文件，讓MySQL認為是第一張表的文件
• 6.釋放讀鎖
• 7.使用REPAIR TABLE來重建表的索引。該操作會通過排序來構建所有索引，包括唯一索引

創建高性能的索引

概述。

索引(在MySQL中也叫做"鍵(key)")是存儲引擎用于快速找到記錄的一種數據結構。這是索引的基本功能。
索引對于良好的性能非常關鍵。尤其是當表中的數據量越來越大時，索引對性能的影響語法重要。在數據量較小時負載較低時，不恰當的索引對性能的影響可能還不明顯，但當數據量逐漸增大時，性能則會急劇下降(除非特別說明，假設都是硬盤驅動器。固態硬盤驅動器有著完全不同的性能特性)。不過，索引卻經常被忽略，有時候甚至被誤解，所以在實際案例中經常會遇到由糟糕索引導致的問題。索引優化應該是對查詢性能優化是最有效的手段，索引能夠輕易將查詢性能提高幾個數量級，"最優"的索引有時比一個"好的"索引性能要好兩個數量級。創建一個真正“最有”的索引經常需要重寫查詢

索引基礎

在MySQL中，存儲引擎用類似的方法使用索引，其先在索引中找到對應值，然后根據匹配的索引記錄找到對應的數據行。假如要運行下面的查詢:

mysql> SELECT first_name FROM actor WHERE actor_id = 5;

如果在actor_id列上建有索引，則MySQL將使用該索引找到actor_id為5的行，也就是說，MySQL先在索引上按值進行查找，然后返回所有包含該值的數據行。索引可以包含一個或者多個列的值。如果索引包含多個列，那么列的順序也十分重要，因為MySQL只能高效地使用索引的最左前綴列，創建一個包含兩個列的索引，和創建兩個只包含一列的索引是大不相同的。

如果使用的是ORM，是否還需要關心索引？

簡而言之:是的，仍然需要理解索引，即使是適用對象關系映射(ORM)工具。
ORM工具能夠生產符合邏輯的、合法的查詢(多數時候)，除非只是生成非常基本的查詢(例如僅是根據主鍵查詢)，否則它很難生成適合索引的查詢。無論是多個復雜的ORM工具，在精妙和復雜的索引面前都是"浮云"。很多時候，即使是查詢優化技術專家也很難兼顧到各種情況，更別說ORM了