7.4優(yōu)化數(shù)據(jù)庫結(jié)構(gòu)
7.4.1設(shè)計(jì)選擇
MySQL將記錄數(shù)據(jù)和索引數(shù)據(jù)分別存放在不同的文件里。其他很多（幾乎所有）數(shù)據(jù)庫都將這記錄和索引數(shù)據(jù)存在同一個(gè)文件中。我們相信MySQL的選擇對(duì)于現(xiàn)在更大范圍的系統(tǒng)更合適。
另一個(gè)存儲(chǔ)記錄數(shù)據(jù)的方法是將每個(gè)字段的信息保存在獨(dú)立的區(qū)域中（例如 SDBM 和Focus）。這當(dāng)每個(gè)查詢都要訪問不只一個(gè)字段的時(shí)候會(huì)打擊性能。由于當(dāng)訪問越多的字段后，性能下降的越厲害，因此我們認(rèn)為這種模式不適合正常目的的數(shù)據(jù)庫。
更多的情況是把索引和數(shù)據(jù)保存在一起(例如 Oracle/Sysbase等)。這樣的話，就能在索引的葉子頁面找到記錄的信息。這種布局的有利之處在于，很多時(shí)候由于索引被緩存的比較好，因此就能節(jié)省磁盤讀取，不過也有如下缺點(diǎn)：
- 由于需要通過讀取索引才能得到數(shù)據(jù)，因此掃描表就更慢了。
- 查詢時(shí)只能根據(jù)索引來取得數(shù)據(jù)。
- 需要更多的磁盤空間，因?yàn)楸仨殢墓?jié)點(diǎn)中復(fù)制索引(不能將記錄保存在節(jié)點(diǎn)中)。
- 刪除會(huì)使表變得更慢(因?yàn)閯h除時(shí)并沒有更新節(jié)點(diǎn)中的索引)。
- 很難只緩存索引數(shù)據(jù)。
7.4.2讓數(shù)據(jù)變得更小巧靈活
優(yōu)化的最基本原則之一就是盡可能把數(shù)據(jù)表設(shè)計(jì)的占用更少磁盤空間。這能得到巨大的性能改善，因?yàn)榇疟P讀取比較快，并且越小的表在處理查詢內(nèi)容時(shí)只需更少的主內(nèi)存。在小點(diǎn)的字段上做索引也只需更少的資源負(fù)載。
MySQL支持很多種不同的表類型以及記錄格式?？梢詻Q定每個(gè)表要采用那種存儲(chǔ)引擎/索引方式。根據(jù)不同的應(yīng)用程序選擇適當(dāng)?shù)谋砀袷侥艽蟠筇岣咝阅?。詳情?qǐng)看“15MySQL Storage Engines and Table Types”。
用以下方法可以提高表性能同時(shí)節(jié)省存儲(chǔ)空間：
- 盡可能使用最有效(最小的)數(shù)據(jù)類型。MySQL有好幾種特定的類型能節(jié)省磁盤和內(nèi)存。
- 盡可能使用更小的整數(shù)類型。例如，
MEDIUMINT通常比更合適INT。 - 盡可能定義字段類型為
NOT NULL。這會(huì)運(yùn)行的更快，而且每個(gè)字段都會(huì)節(jié)省1個(gè)bit。如果在應(yīng)用程序中確實(shí)需要用到NULL，那么就明確的指定它。不過要避免所有的字段默認(rèn)值是NULL。 - 在
MyISAM表中，如果沒有用到任何變長字段(VARCHAR,TEXT, 或BLOB字段)的話，那么就采用固定大小的記錄格式。這樣速度更快，不過可能會(huì)浪費(fèi)點(diǎn)空間。詳情請(qǐng)看“15.1.3MyISAMTable Storage Formats”。 - 表的主索引應(yīng)盡可能短。這樣的話會(huì)每條記錄都有名字標(biāo)識(shí)且更高效。
- 只創(chuàng)建確實(shí)需要的索引。索引有利于檢索記錄，但是不利于快速保存記錄。如果總是要在表的組合字段上做搜索，那么就在這些字段上創(chuàng)建索引。索引的第一部分必須是最常使用的字段.如果總是需要用到很多字段，首先就應(yīng)該多復(fù)制這些字段，使索引更好的壓縮。
- 一個(gè)字段很有可能在最開始的一些數(shù)量字符是各不相同的，因此在這些字符上做索引更合適。MySQL支持在一個(gè)字段的最左部分字符做索引。索引越短，速度越快，不僅是因?yàn)樗加酶俚拇疟P空間，也因?yàn)檫@提高了索引緩存的命中率，由此減少了磁盤搜索。詳情請(qǐng)看“7.5.2Tuning Server Parameters”。
- 在某些情況下，把一個(gè)頻繁掃描的表分割成兩個(gè)更有利。在對(duì)動(dòng)態(tài)格式表掃描以取得相關(guān)記錄時(shí)，它可能使用更小的靜態(tài)格式表的情況下更是如此。
7.4.3字段索引
所有的MySQL字段類型都能被索引。在相關(guān)字段上做索引對(duì)提高 SELECT 語句的性能最有效。
每個(gè)表的最大索引長度以及最多索引數(shù)量是由各自的存儲(chǔ)引擎定義好了的。詳情請(qǐng)看“15 MySQL Storage Engines and Table Types”。所有的存儲(chǔ)引擎對(duì)每個(gè)表都至少可以支持16個(gè)索引，索引長度最小是 256 字節(jié)。大部分存儲(chǔ)引擎的限制更高。
索引格式中使用 col_name(length) 語法，就能只對(duì) CHAR 或 VARCHAR 字段最前面的 length 個(gè)字符做索引。象類似這樣只對(duì)字段的前綴部分做索引能讓索引文件更小。
MyISAM 和 InnoDB (從MySQL 4.0.14開始)存儲(chǔ)引擎還支持在 BLOB 和 TEXT 字段上做索引，但是必須指定索引的前綴長度，例如：
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
前綴的長度可以多達(dá)255字節(jié)(從MySQL 4.1.2開始，MyISAM 和 InnoDB 表支持1000字節(jié))。注意，前綴長度限制是以字節(jié)數(shù)衡量的，然而 CREATE TABLE 語句中的前綴長度理解成為字符個(gè)數(shù)。因此在指定字段索引前綴長度時(shí)要考慮到使用多字節(jié)字符集字段的情況了。
從MySQL 3.23.23開始，就可以創(chuàng)建 FULLTEXT 索引了，它們使用全文搜索。只有 MyISAM 表支持對(duì) CHAR,VARCHAR 和 TEXT 字段做 FULLTEXT 索引。只對(duì)整個(gè)字段檢索有效，不支持部分(前綴)檢索。詳情請(qǐng)看“13.6 Full-Text Search Functions”。
從MySQL 4.1.0開始，還可以空間類型字段上做索引。目前，只有 MyISAM 存儲(chǔ)引擎支持空間類型?？臻g索引使用R樹索引。
MEMORY (HEAP) 存儲(chǔ)引擎支持哈希索引，從MySQL 4.1.0開始，它也支持B樹索引。
7.4.4 多字段索引
MySQL可以在多個(gè)字段上創(chuàng)建索引，可以由多達(dá)15個(gè)字段組成。對(duì)特定的字段類型，還可以使用前綴索引(詳情請(qǐng)看"7.4.3 Column Indexes”)。
多字段索引可以認(rèn)為是由索引字段的值連接在一起而成，且經(jīng)過排序之后的數(shù)組。
MySQL以如下方法使用多字段索引：在 WHERE 子句中指定了已知數(shù)量的索引的第一個(gè)字段，查詢就很快了，甚至無需指定其他字段的值。
假定一個(gè)表結(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 字段。這個(gè)索引在字段 last_name 上或 last_name 和 first_name 一起的指定范圍內(nèi)查詢時(shí)能起到作用。因此這個(gè)索引在以下幾個(gè)查詢中都會(huì)被用到：
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 在以下幾個(gè)查詢中不會(huì)被用到：
SELECT * FROM test WHERE first_name='Michael'; SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';
關(guān)于MySQL如何使用索引來改善查詢性能的方式在下個(gè)章節(jié)中具體討論。
