view作用 - mysql超新手入門11




MySQL索引如何工作? (4)

你必須知道的第一件事是索引是一種避免掃描整個表來獲得你要找的結果的方法。

有不同種類的索引,它們在存儲層中實現,因此它們之間沒有標準,它們也依賴於您正在使用的存儲引擎。

InnoDB和B +樹索引

對於InnoDB,最常見的索引類型是基於B +樹的索引,它按排序順序存儲元素。 此外,您不必訪問真正的表來獲取索引值,這使您的查詢更快地返回。

有關此索引類型的“問題”是,您必須查詢最左邊的值以使用索引。 所以,如果你的索引有兩列,比如last_name和first_name,那麼你查詢這些字段的順序就很重要

所以,給出下表:

CREATE TABLE person (
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    INDEX (last_name, first_name)
);

該查詢將利用索引:

SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"

但下面的一個不會

SELECT last_name, first_name FROM person WHERE first_name = "Constantine"

因為您首先查詢first_name列,而不是索引中最左邊的列。

最後一個例子更糟糕:

SELECT last_name, first_name FROM person WHERE first_name LIKE "%Constantine"

因為現在,您正在比較索引中最右邊字段的最右邊部分。

散列索引

這是一個不同的索引類型,不幸的是,只有內存後端支持。 它閃電般​​快速,但僅對完整查找有用,這意味著您不能將它用於><LIKE

由於它只適用於內存後端,因此您可能不會經常使用它。 我現在能想到的主要情況是,你在內存中創建一個臨時表,並使用另一個select的一組結果,並使用散列索引在此臨時表中執行很多其他選擇。

如果你有一個大的VARCHAR字段,你可以在使用B-Tree時“模擬”使用散列索引,方法是創建另一列並保存大值的哈希值。 假設你在一個字段中存儲一個url,並且這些值非常大。 您也可以創建一個名為url_hash的整數字段,並在插入時使用散列函數(如CRC32或任何其他散列函數)對網址進行散列處理。 然後,當你需要查詢這個值時,你可以這樣做:

SELECT url FROM url_table WHERE url_hash=CRC32("http://gnu.org");

上面的例子的問題是,由於CRC32函數產生一個非常小的散列,你最終會在散列值中產生很多衝突。 如果您需要確切的值,可以通過執行以下操作來解決此問題:

SELECT url FROM url_table 
WHERE url_hash=CRC32("http://gnu.org") AND url="http://gnu.org";

即使碰撞數很高,仍然值得對其進行哈希處理,因為您只會對重複哈希執行第二次比較(字符串一)。

不幸的是,使用這種技術,你仍然需要點擊表格來比較url字段。

包起來

您每次想談論優化時都會考慮一些事實:

  1. 整數比較比字符串比較方式快。 可以用InnoDB中的哈希索引模擬來舉例說明。

  2. 也許,在一個過程中增加額外的步驟使其更快,而不是更慢。 這可以通過以下事實來說明:可以通過將SELECT分解為兩個步驟來優化SELECT ,使第一個將值存儲在新創建的內存表中,然後在第二個表上執行較重的查詢。

MySQL也有其他的索引,但我認為B + Tree是有史以來使用最多的,哈希是一個很好的事情,但你可以在dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html找到其他索引。

我強烈建議你閱讀“高性能MySQL”一書,上面的答案絕對是基於它關於索引的章節。

我對MySQL索引的工作方式非常感興趣,更具體地說,他們如何在不掃描整個表的情況下返回請求的數據?

我知道這是無關緊要的,但是如果有人能夠詳細解釋這一點,我會非常非常感激。


基本上,索引是按順序排序的所有鍵的映射。 按照順序排列列表,而不是檢查每個鍵,它可以做這樣的事情:

1:轉到列表的中間 - 比我要找的更高還是更低?

2:如果較高,則進入中間和底部之間的中間點,如果較低,中間和頂部

3:更高還是更低? 再次跳到中間點等。

使用該邏輯,您可以按大約7個步驟在排序列表中查找元素,而不是檢查每個項目。

顯然有復雜性,但是這給了你基本的想法。


數據庫索引或僅索引有助於加速從表中檢索數據。 當你從表中查詢數據時,首先MySQL檢查索引是否存在,然後MySQL使用索引來選擇表的確切物理對應行,而不是掃描整個表。

數據庫索引類似於書籍的索引。 如果您想查找主題,請先查看索引,然後打開包含主題的頁面,而不掃描整本書。

強烈建議您應該在您經常查詢數據的表的列上創建索引。 請注意,所有主鍵列都自動位於表格的主索引中。

如果索引有助於加快查詢數據,為什麼我們不使用索引來查看所有列? 如果您為每個列創建索引,則MySQL必須構建和維護索引表。 無論何時對錶的記錄進行更改,MySQL都必須重建索引,這會花費時間並降低數據庫服務器的性能。 創建MySQL索引

您在創建表格時經常創建索引。 MySQL自動將任何聲明為PRIMARY KEY,KEY,UNIQUE或INDEX的列添加到索引。 另外,您可以將索引添加到已有數據的表中。

為了創建索引,可以使用CREATE INDEX語句。 以下說明了CREATE INDEX語句的語法:1 2 3

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name USING [BTREE | HASH | RTREE] ON table_name (column_name [(length)] [ASC | DESC],...)

首先,根據表類型或存儲引擎指定索引:

UNIQUE意味著MySQL將創建一個約束,索引中的所有值必須是唯一的。 在除BDB以外的所有存儲引擎中允許重複的NULL值。 FULLTEXT索引僅由MyISAM存儲引擎支持,並且僅在數據類型為CHAR,VARCHAR或TEXT的列上接受。 SPATIAL索引支持空間列,並可在MyISAM存儲引擎上使用。 另外,列值不能為NULL。

然後,您可以在USING關鍵字(如BTREE,HASH或RTREE)之後將索引及其類型命名為基於表的存儲引擎。

以下是具有相應允許索引類型的表的存儲引擎:存儲引擎允許索引類型MyISAM BTREE,RTREE InnoDB BTREE存儲器/堆磁盤,BTREE NDB哈希

第三,你聲明你想添加到索引的表名和列表列。 在MySQL中創建索引的示例

在示例數據庫中,可以使用CREATE INDEX語句將employees表的employeeCode列添加到索引,如下所示:1

CREATE INDEX officeCode ON employees(officeCode)

刪除索引

除了創建索引外,還可以使用DROP INDEX語句刪除索引。 有趣的是,DROP INDEX語句也映射到ALTER TABLE語句。 以下是刪除索引的語法:1

DROP INDEX index_name ON table_name

例如,如果您要刪除我們上面創建的僱員表的索引officeCode,則可以執行以下查詢:1

DROP INDEX officeCode ON employees






indexing