7.4優(yōu)化數(shù)據(jù)庫結(jié)構(gòu)

7.4.1設(shè)計選擇

MySQL將記錄數(shù)據(jù)和索引數(shù)據(jù)分別存放在不同的文件里。其他很多（幾乎所有）數(shù)據(jù)庫都將這記錄和索引數(shù)據(jù)存在同一個文件中。我們相信MySQL的選擇對于現(xiàn)在更大范圍的系統(tǒng)更合適。

另一個存儲記錄數(shù)據(jù)的方法是將每個字段的信息保存在獨立的區(qū)域中（例如 SDBM 和Focus）。這當每個查詢都要訪問不只一個字段的時候會打擊性能。由于當訪問越多的字段后，性能下降的越厲害，因此我們認為這種模式不適合正常目的的數(shù)據(jù)庫。

更多的情況是把索引和數(shù)據(jù)保存在一起(例如 Oracle/Sysbase等)。這樣的話，就能在索引的葉子頁面找到記錄的信息。這種布局的有利之處在于，很多時候由于索引被緩存的比較好，因此就能節(jié)省磁盤讀取，不過也有如下缺點：

• 由于需要通過讀取索引才能得到數(shù)據(jù)，因此掃描表就更慢了。
• 查詢時只能根據(jù)索引來取得數(shù)據(jù)。
• 需要更多的磁盤空間，因為必須從節(jié)點中復(fù)制索引(不能將記錄保存在節(jié)點中)。
• 刪除會使表變得更慢(因為刪除時并沒有更新節(jié)點中的索引)。
• 很難只緩存索引數(shù)據(jù)。

7.4.2讓數(shù)據(jù)變得更小巧靈活

優(yōu)化的最基本原則之一就是盡可能把數(shù)據(jù)表設(shè)計的占用更少磁盤空間。這能得到巨大的性能改善，因為磁盤讀取比較快，并且越小的表在處理查詢內(nèi)容時只需更少的主內(nèi)存。在小點的字段上做索引也只需更少的資源負載。

MySQL支持很多種不同的表類型以及記錄格式?？梢詻Q定每個表要采用那種存儲引擎/索引方式。根據(jù)不同的應(yīng)用程序選擇適當?shù)谋砀袷侥艽蟠筇岣咝阅堋Ｔ斍檎埧?#8220;15MySQL Storage Engines and Table Types”。

用以下方法可以提高表性能同時節(jié)省存儲空間：

• 盡可能使用最有效(最小的)數(shù)據(jù)類型。MySQL有好幾種特定的類型能節(jié)省磁盤和內(nèi)存。
• 盡可能使用更小的整數(shù)類型。例如，MEDIUMINT通常比更合適 INT。
• 盡可能定義字段類型為 NOT NULL。這會運行的更快，而且每個字段都會節(jié)省1個bit。如果在應(yīng)用程序中確實需要用到 NULL，那么就明確的指定它。不過要避免所有的字段默認值是 NULL。
• 在 MyISAM 表中，如果沒有用到任何變長字段(VARCHAR, TEXT, 或 BLOB字段)的話，那么就采用固定大小的記錄格式。這樣速度更快，不過可能會浪費點空間。詳情請看“15.1.3 MyISAMTable Storage Formats”。
• 表的主索引應(yīng)盡可能短。這樣的話會每條記錄都有名字標識且更高效。
• 只創(chuàng)建確實需要的索引。索引有利于檢索記錄，但是不利于快速保存記錄。如果總是要在表的組合字段上做搜索，那么就在這些字段上創(chuàng)建索引。索引的第一部分必須是最常使用的字段.如果總是需要用到很多字段，首先就應(yīng)該多復(fù)制這些字段，使索引更好的壓縮。
• 一個字段很有可能在最開始的一些數(shù)量字符是各不相同的，因此在這些字符上做索引更合適。MySQL支持在一個字段的最左部分字符做索引。索引越短，速度越快，不僅是因為它占用更少的磁盤空間，也因為這提高了索引緩存的命中率，由此減少了磁盤搜索。詳情請看“7.5.2Tuning Server Parameters”。
• 在某些情況下，把一個頻繁掃描的表分割成兩個更有利。在對動態(tài)格式表掃描以取得相關(guān)記錄時，它可能使用更小的靜態(tài)格式表的情況下更是如此。

7.4.3字段索引

所有的MySQL字段類型都能被索引。在相關(guān)字段上做索引對提高 SELECT 語句的性能最有效。

每個表的最大索引長度以及最多索引數(shù)量是由各自的存儲引擎定義好了的。詳情請看“15 MySQL Storage Engines and Table Types”。所有的存儲引擎對每個表都至少可以支持16個索引，索引長度最小是 256 字節(jié)。大部分存儲引擎的限制更高。

索引格式中使用 col_name(length) 語法，就能只對 CHAR 或 VARCHAR 字段最前面的 length 個字符做索引。象類似這樣只對字段的前綴部分做索引能讓索引文件更小。

MyISAM 和 InnoDB (從MySQL 4.0.14開始)存儲引擎還支持在 BLOB 和 TEXT 字段上做索引，但是必須指定索引的前綴長度，例如：

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

前綴的長度可以多達255字節(jié)(從MySQL 4.1.2開始，MyISAM 和 InnoDB 表支持1000字節(jié))。注意，前綴長度限制是以字節(jié)數(shù)衡量的，然而 CREATE TABLE 語句中的前綴長度理解成為字符個數(shù)。因此在指定字段索引前綴長度時要考慮到使用多字節(jié)字符集字段的情況了。

從MySQL 3.23.23開始，就可以創(chuàng)建 FULLTEXT 索引了，它們使用全文搜索。只有 MyISAM 表支持對 CHAR,VARCHAR 和 TEXT 字段做 FULLTEXT 索引。只對整個字段檢索有效，不支持部分(前綴)檢索。詳情請看“13.6 Full-Text Search Functions”。

從MySQL 4.1.0開始，還可以空間類型字段上做索引。目前，只有 MyISAM 存儲引擎支持空間類型?？臻g索引使用R樹索引。

MEMORY (HEAP) 存儲引擎支持哈希索引，從MySQL 4.1.0開始，它也支持B樹索引。

7.4.4 多字段索引

MySQL可以在多個字段上創(chuàng)建索引，可以由多達15個字段組成。對特定的字段類型，還可以使用前綴索引(詳情請看"7.4.3 Column Indexes”)。

多字段索引可以認為是由索引字段的值連接在一起而成，且經(jīng)過排序之后的數(shù)組。

MySQL以如下方法使用多字段索引：在 WHERE 子句中指定了已知數(shù)量的索引的第一個字段，查詢就很快了，甚至無需指定其他字段的值。

假定一個表結(jié)構(gòu)如下：

CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name));

索引 name 覆蓋了 last_name 和 first_name 字段。這個索引在字段 last_name 上或 last_name 和 first_name 一起的指定范圍內(nèi)查詢時能起到作用。因此這個索引在以下幾個查詢中都會被用到：

SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test
WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius'
AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test
WHERE last_name='Widenius'
AND first_name >='M' AND first_name < 'N';

不過，索引 name 在以下幾個查詢中不會被用到：

SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius' OR first_name='Michael';

關(guān)于MySQL如何使用索引來改善查詢性能的方式在下個章節(jié)中具體討論。