Skip to content

Zeroplex 生活隨筆

軟體開發、伺服器和生活瑣事

小 縮小字型大小。 中 重設字型大小。 大 放大字型大小。

MySQL索引分析和優化

Posted on 2006 年 9 月 12 日2021 年 3 月 12 日 By 日落 在〈MySQL索引分析和優化〉中有 1 則留言

轉載 From 松下客 @ 賽迪網
一、什麼是索引?

索引用來快速地尋找那些具有特定值的記錄,所有MySQL索引都以B-樹的形式保存。如果沒有索引,執行查詢時MySQL必須從第一個記錄開始掃瞄整個表 的所有記錄,直至找到符合要求的記錄。表裡面的記錄數量越多,這個操作的代價就越高。如果作為搜索條件的列上已經創建了索引,MySQL無需掃瞄任何記錄 即可迅速得到目標記錄所在的位置。如果表有1000個記錄,通過索引查找記錄至少要比順序掃瞄記錄快100倍。

對於索引中的每一項,MySQL在內部為它保存一個數據文件中實際記錄所在位置的「指針」。因此,如果我們要查找name等於「Mike」記錄的 peopleid(SQL命令為「SELECT peopleid FROM people WHERE name=’Mike’;」),MySQL能夠在name的索引中查找「Mike」值,然後直接轉到數據文件中相應的 行,準確地返回該行的peopleid(999)。在這個過程中,MySQL只需處理一個行就可以返回結果。如果沒有「name」列的索引,MySQL要 掃瞄數據文件中的所有記錄,即1000個記錄!顯然,需要MySQL處理的記錄數量越少,則它完成任務的速度就越快。

二、索引的類型

MySQL提供多種索引類型供選擇:

普通索引
這是最基本的索引類型,而且它沒有唯一性之類的限制。普通索引可以通過以下幾種方式創建:
創建索引,例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
創建表的時候指定索引,例如CREATE TABLE tablename ( […], INDEX [索引的名字] (列的列表) );

唯一性索引
這種索引和前面的「普通索引」基本相同,但有一個區別:索引列的所有值都只能出現一次,即必須唯一。唯一性索引可以用以下幾種方式創建:
創建索引,例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
創建表的時候指定索引,例如CREATE TABLE tablename ( […], UNIQUE [索引的名字] (列的列表) );

主鍵
主鍵是一種唯一性索引,但它必須指定為「PRIMARY KEY」。如果你曾經用過AUTO_INCREMENT類型的列,你可能已經熟悉主鍵之類的概念了。主鍵一般在創建表的時候指定,例如「CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); 」。但是,我們也可以通過修改表的方式加入主鍵,例如「ALTER TABLE tablename ADD PRIMARY KEY (列的列表); 」。每個表只能有一個主鍵。

全文索引
MySQL從3.23.23版開始支持全文索引和全文檢索。在MySQL中,全文索引的索引類型為FULLTEXT。全文索引可以在VARCHAR或者 TEXT類型的列上創建。它可以通過CREATE TABLE命令創建,也可以通過ALTER TABLE或CREATE INDEX命令創建。對於大規模的數據集,通過ALTER TABLE(或者CREATE INDEX)命令創建全文索引要比把記錄插入帶有全文索引的空表更快。本文下面的討論不再涉及全文索引,要瞭解更多信息,請參見MySQL documentation。

三、單列索引與多列索引

索引可以是單列索引,也可以是多列索引。

四、最左前綴

多列索引還有另外一個優點,它通過稱為最左前綴(Leftmost Prefixing)的概念體現出來。

五、選擇索引列

在性能優化過程中,選擇在哪些列上創建索引是最重要的步驟之一。

六、分析索引效率

現在我們已經知道了一些如何選擇索引列的知識,但還無法判斷哪一個最有效。MySQL提供了一個內建的SQL命令幫助我們完成這個任務,這就是EXPLAIN命令。EXPLAIN命令的一般語法是:EXPLAIN 。

「對於每一種與另一個表中記錄的組合,MySQL將從當前的表讀取所有帶有匹配索引值的記錄。如果連接操作只使用鍵的最左前綴,或者如果鍵不是 UNIQUE或PRIMARY KEY類型(換句話說,如果連接操作不能根據鍵值選擇出唯一行),則MySQL使用ref連接類型。如果連接操作所用的鍵只匹配少量的記錄,則ref是一 種好的連接類型。」

如果EXPLAIN顯示連接類型是「ALL」,而且你並不想從表裡面選擇出大多數記錄,那麼MySQL的操作效率將非常低,因為它要掃瞄整個表。你可以加入更多的索引來解決這個問題。預知更多信息,請參見MySQL的手冊說明。

possible_keys:
可能可以利用的索引的名字。這裡的索引名字是創建索引時指定的索引暱稱;如果索引沒有暱稱,則默認顯示的是索引中第一個列的名字(在本例中,它是「firstname」)。默認索引名字的含義往往不是很明顯。

Key:
它顯示了MySQL實際使用的索引的名字。如果它為空(或NULL),則MySQL不使用索引。

key_len:
索引中被使用部分的長度,以字節計。在本例中,key_len是102,其中firstname占50字節,lastname占50字節,age占2字節。如果MySQL只使用索引中的firstname部分,則key_len將是50。

ref:
它顯示的是列的名字(或單詞「const」),MySQL將根據這些列來選擇行。在本例中,MySQL根據三個常量選擇行。

rows:
MySQL所認為的它在找到正確的結果之前必須掃瞄的記錄數。顯然,這裡最理想的數字就是1。

Extra:
這裡可能出現釵h不同的選項,其中大多數將對查詢產生負面影響。在本例中,MySQL只是提醒我們它將用WHERE子句限制搜索結果集。

七、索引的缺點

到目前為止,我們討論的都是索引的優點。事實上,索引也是有缺點的。

首先,索引要佔用磁盤空間。通常情況下,這個問題不是很突出。但是,如果你創建每一種可能列組合的索引,索引文件體積的增長速度將遠遠超過數據文件。如果你有一個很大的表,索引文件的大小可能達到操作系統允釭熙怳j文件限制。

第二,對於需要寫入數據的操作,比如DELETE、UPDATE以及INSERT操作,索引會降低它們的速度。這是因為MySQL不僅要把改動數據寫入數據文件,而且它還要把這些改動寫入索引文件。

【結語】在大型數據庫中,索引是提高速度的一個關鍵因素。不管表的結構是多麼簡單,一次500000行的表掃瞄操作無論如何不會快。如果你的網站上也有這 種大規模的表,那麼你確實應該花些時間去分析可以採用哪些索引,並考慮是否可以改寫查詢以優化應用。要瞭解更多信息,請參見MySQL manual。另外注意,本文假定你所使用的MySQL是3.23版,部分查詢不能在3.22版MySQL上執行。

文章導覽

Previous Post: Google Adsense
Next Post: 作弊歌

Comment (1) on “MySQL索引分析和優化”

  1. 凍仁.翔 (Chu-Siang Lai)表示:
    2012 年 6 月 21 日03:34

    看來之前 Zero 大大已經走過在下的現在的路了!

    回覆

發佈留言 取消回覆

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *


其他

關於我  (About me)

小額贊助

  文章 RSS Feed

  留言 RSS Feed

Apache AWS Bash C/C++ Docker FreeBSD GCP Git Google Java JavaScript Laravel Linux Microsoft MSSQL MySQL Nginx PHP PHPUnit PostgreSQL Python Qt Ubuntu Unix Vim Web Windows WordPress XD 作業系統 分享 好站推薦 專題 攝影 新奇搞笑 新聞 旅遊 生活雜記 程式設計 網路架站 網頁設計 資訊學習 資訊安全 遊戲 音樂


創用 CC 授權條款
本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權.