（十二）MySQL中的索引

1、概述

??索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。可以將索引理解為一本書前面的目錄，能加快數(shù)據(jù)的查詢速度。
　　對于沒有索引的表，MySQL會遍歷全部數(shù)據(jù)后選擇符合條件的記錄，因此單表查詢可能幾十萬數(shù)據(jù)就是瓶頸，而通常大型網(wǎng)站單日就可能會產(chǎn)生幾十萬甚至幾百萬的數(shù)據(jù)，沒有索引查詢會變的非常緩慢，而有了相應(yīng)的索引之后，MySQL會直接在索引中查找符合條件的選項，效率會大大提升。

??索引分為聚簇索引和非聚簇索引兩種：

  1. 聚簇索引是按照數(shù)據(jù)存放的物理位置為順序的，對于數(shù)據(jù)而言，通常來說物理順序結(jié)構(gòu)只有一種，因此每張數(shù)據(jù)表也只能有一個聚簇索引。在設(shè)置主鍵時，系統(tǒng)會默認(rèn)為其加上聚簇索引，當(dāng)然也可以使用其他字段作為索引，此時需要在設(shè)置主鍵之前先手動為待選字段添加上唯一的聚簇索引，然后再設(shè)置主鍵，就可以解決這一問題。
  • 非聚簇索引記錄的物理順序與邏輯順序沒有必然的聯(lián)系，與數(shù)據(jù)的存儲物理位置也沒有關(guān)系；每張數(shù)據(jù)表對應(yīng)的非聚簇索引可以有多個，根據(jù)不同列的約束可以建立不同要求的非聚簇索引。

??簡單總結(jié)即，聚簇索引能提高多行檢索的效率，而非聚簇索引對于單行的檢索更有效。

2、索引的類型

普通索引

??普通索引是最基本的索引，它沒有任何限制，是大多數(shù)情況下用到的索引。

直接創(chuàng)建索引
CREATE INDEX index_name ON tbl_name(col_name(length));
修改數(shù)據(jù)表結(jié)構(gòu)時添加索引
ALTER TABLE tbl_name ADD INDEX index_name (col_name(length));
創(chuàng)建數(shù)據(jù)表時同時創(chuàng)建索引
CREATE TABLE tbl_name (
……
INDEX index_name (col_name(length))
)

唯一索引

??唯一索引與普通索引類似，不同之處是：索引列的值必須唯一，但允許有空值（注意和主鍵不同）。如果是組合索引，則列值的組合必須唯一，創(chuàng)建方法和普通索引類似。

直接創(chuàng)建索引
CREATE UNIQUE INDEX index_name ON tbl_name(col_name(length));
修改數(shù)據(jù)表結(jié)構(gòu)時添加索引
ALTER TABLE tbl_name ADD UNIQUE index_name (col_name(length));
創(chuàng)建數(shù)據(jù)表時同時創(chuàng)建索引
CREATE TABLE tbl_name (
……
UNIQUE index_name (col_name(length))
)

全文索引

??全文索引僅可用于MyISAM數(shù)據(jù)表，對于較大的數(shù)據(jù)集而言，將資料輸入進(jìn)一個沒有全文索引的數(shù)據(jù)表中，然后創(chuàng)建索引；其速度遠(yuǎn)比把資料輸入現(xiàn)有全文索引的數(shù)據(jù)表中更快。不過對于大容量的數(shù)據(jù)表來說，生成全文索引是一個非常消耗時間及硬盤空間的做法。

直接創(chuàng)建索引
CREATE FULLTEXT INDEX index_name ON tbl_name(col_name(length));
修改數(shù)據(jù)表結(jié)構(gòu)時添加索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_name(length));
創(chuàng)建數(shù)據(jù)表時同時創(chuàng)建索引
CREATE TABLE tbl_name (
……
FULLTEXT index_name (col_name(length))
)

多列索引、單列索引

??單個多列索引與多個單列索引的查詢效果不同。當(dāng)使用多個單列索引并執(zhí)行查詢時，MySQL只會從多個索引中選擇一個限制最為嚴(yán)格的索引，剩余的索引將起不到作用。在建立多列索引時，字段的順序也是需要注意的，應(yīng)該將嚴(yán)格的索引放在前面，這樣篩選的力度會更大，效率更高。


組合索引（最左前綴）

??平時用的SQL查詢語句一般都有比較多的限制條件，因此為了進(jìn)一步提高M(jìn)ySQL的效率，就要考慮建立組合索引。例如上圖中針對“l(fā)ast_name”和“first_name”建立一個組合索引：

INDEX name (last_name,first_name)

??建立這樣的組合索引，其實是相當(dāng)于分別建立了下面兩組組合索引：

(last_name,first_name)
(last_name)

??之所以沒有(first_name)這樣的組合索引，是因為MySQL組合索引采取“最左前綴”的結(jié)果。簡單的理解就是只從最左面的開始組合，組合索引的最左列一定選擇好，否則無法起到索引的效果。如果查詢時最左列不在查詢條件中則該組合索引不會被使用。
??最左列一定是使用最頻繁的列，然而并不是只要包含在組合索引中的列的查詢都會用到該組合索引，例如以下形式的查詢語句能夠使用組合索引：

SELECT * FROM tbl_name WHERE last_name='Widenius';  
  
SELECT * FROM tbl_name WHERE last_name='Widenius' AND first_name='Michael';  
  
SELECT * FROM tbl_name WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');

??以下形式的查詢語句不能使用組合索引：

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

查看索引

SHOW INDEX FROM tbl_name;
SHOW KEYS FROM tbl_name;

刪除索引

DORP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name;

3、索引的缺點(diǎn)

??雖然索引極大提高了數(shù)據(jù)查詢的速度，但同時卻會降低更新表的速度，如對表進(jìn)行INSERT、UPDATE和DELET時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件，這樣就會降低數(shù)據(jù)的維護(hù)速度。
　　建立索引會占用磁盤空間，一般而言這個問題不太嚴(yán)重，但如果在一個擁有大量數(shù)據(jù)的表上創(chuàng)建了多種組合索引，索引文件的會膨脹很快。
　　索引只是提高效率的一個因素，如果有大數(shù)據(jù)量的表，就需要花費(fèi)時間研究建立最優(yōu)秀的索引組合，或優(yōu)化查詢語句，且隨著數(shù)據(jù)量的增加，維護(hù)索引的成本也會增加。

4、使用索引的注意事項

如何選擇聚簇索引或非聚簇索引

動作描述 使用聚簇索引 使用非聚簇索引
列經(jīng)常被分組排序 使用 使用
返回某范圍內(nèi)的數(shù)據(jù) 使用 不使用
一個或極少不同值 不使用 不使用
少量的不同值 使用 不使用
大量的不同值 不使用 使用
頻繁更新的列 不使用 使用
外鍵列 使用 使用
主鍵列 使用 使用
頻繁修改索引列 不使用 使用

使用前綴長度

??對于CHAR和VARCHAR列，只用一列的一部分就可創(chuàng)建索引。創(chuàng)建索引時，使用col_name(length)語法，對前綴編制索引。前綴包括每列值的前l(fā)ength個字符。BLOB和TEXT列也可以編制索引，但是必須給出前綴長度。
　　例如，有一個CHAR(255)的列，如果在前10個或20個字符內(nèi)，多數(shù)值是唯一的，那么就不要對整個列進(jìn)行索引，而是CREATE INDEX index_name ON tbl_name(col_name(10))。
　　使用短索引不僅可以提高查詢速度，而且可以節(jié)省磁盤空間和I/O操作。

LIKE語句

??若LIKE語句是不以通配符開頭的常量串，MySQL會使用索引。比如：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'
或
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'

??而以下情況無法使用索引：

//LIKE語句以通配符開頭
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'
//LIKE語句不是常量串
SELECT * FROM tbl_name WHERE key_col LIKE other_col

WHERE子句

  1. 在WHERE子句的查詢條件中進(jìn)行運(yùn)算會導(dǎo)致索引失效；
  2. 在WHERE子句的查詢條件中使用了函數(shù)會導(dǎo)致索引失效；
  3. 在WHERE子句的查詢條件中使用“or”來連接條件會導(dǎo)致索引失效；
  4. 在WHERE子句的查詢條件中使用“!=”或“<>”操作符會導(dǎo)致索引失效。

多表連接

??如果數(shù)據(jù)表需要很多連接查詢，首先應(yīng)該確認(rèn)兩張數(shù)據(jù)表中連接的字段已經(jīng)創(chuàng)建索引。這樣，MySQL內(nèi)部會啟動優(yōu)化連接的SQL語句的機(jī)制。
　　除此之外，這些被用來連接的字段，應(yīng)該是屬于同一類型。例如，如果要把DECIMAL類型的字段和一個INT類型的字段連接在一起，MySQL就無法使用它們的索引。另外對于STRING類型，還需要有相同的字符集才行(兩張數(shù)據(jù)表的字符集有可能不一樣)。

避免使用“SELECT * ”

??從數(shù)據(jù)庫里讀出的數(shù)據(jù)越多，那么查詢就會變得越慢。而且如果數(shù)據(jù)庫服務(wù)器和WEB服務(wù)器是兩臺獨(dú)立的服務(wù)器的話，還會增加網(wǎng)絡(luò)傳輸?shù)呢?fù)擔(dān)。因此，應(yīng)該養(yǎng)成需要什么就查找什么的好習(xí)慣：

//不推薦使用
SELECT * FROM tbl_name WHERE id = 1;
//推薦使用
SELECT username FROM tbl_name WHERE id = 1;

為每張數(shù)據(jù)表設(shè)置一個ID字段

??應(yīng)該為數(shù)據(jù)庫里的每張表都設(shè)置一個ID做為其主鍵，且最好為INT類型的(推薦使用UNSIGNED)，并設(shè)置上AUTO_INCREMENT。
　　使用例如VARCHAR類型來當(dāng)主鍵會使MySQL性能下降。而且，還有一些操作，例如集群、分區(qū)等需要使用主鍵，在這些情況下，主鍵的性能和設(shè)置變得非常重要。

盡可能的使用“NOT NULL”

??要盡可能地把字段定義為“NOT NULL”。即使該數(shù)據(jù)表無須保存“NULL”（沒有值），也有許多表包含了可空列（Nullable Column），這僅僅因為它是默認(rèn)選項。除非真的要保存“NULL”，否則就把字段定義為“NOT NULL”。
　　MySQL難以優(yōu)化引用了可空列的查詢，它會使索引、索引統(tǒng)計和值更加復(fù)雜。可空列需要更多的儲存空間，還需要在MySQL內(nèi)部進(jìn)行特殊處理。當(dāng)可空列被索引的時候，每條記錄都需要一個額外的字節(jié)，還可能導(dǎo)致MyISAM中固定大小的索引(例如一個整數(shù)字段上的索引)變成可變大小的索引。
　　即使要在表中儲存“沒有值”的字段，還是有可能不使用“NULL”的。考慮使用0、特殊值或空字符串來代替它。

5、索引的SQL語句匯總

  • 普通索引
    直接創(chuàng)建索引
    CREATE INDEX index_name ON tbl_name(col_name(length));
    修改數(shù)據(jù)表結(jié)構(gòu)時添加索引
    ALTER TABLE tbl_name ADD INDEX index_name (col_name(length));
    創(chuàng)建數(shù)據(jù)表時同時創(chuàng)建索引
    CREATE TABLE tbl_name (
    ……
    INDEX index_name (col_name(length))
    )

  • 唯一索引
    直接創(chuàng)建索引
    CREATE UNIQUE INDEX index_name ON tbl_name(col_name(length));
    修改數(shù)據(jù)表結(jié)構(gòu)時添加索引
    ALTER TABLE tbl_name ADD UNIQUE index_name (col_name(length));
    創(chuàng)建數(shù)據(jù)表時同時創(chuàng)建索引
    CREATE TABLE tbl_name (
    ……
    UNIQUE index_name (col_name(length))
    )

  • 全文索引
    直接創(chuàng)建索引
    CREATE FULLTEXT INDEX index_name ON tbl_name(col_name(length));
    修改數(shù)據(jù)表結(jié)構(gòu)時添加索引
    ALTER TABLE tbl_name ADD FULLTEXT index_name (col_name(length));
    創(chuàng)建數(shù)據(jù)表時同時創(chuàng)建索引
    CREATE TABLE tbl_name (
    ……
    FULLTEXT index_name (col_name(length))
    )

  • 查看索引
    SHOW INDEX FROM tbl_name;
    SHOW KEYS FROM tbl_name;

  • 刪除索引
    DORP INDEX index_name ON tbl_name;
    ALTER TABLE tbl_name DROP INDEX index_name;

版權(quán)聲明：歡迎轉(zhuǎn)載，歡迎擴(kuò)散，但轉(zhuǎn)載時請標(biāo)明作者以及原文出處，謝謝合作！             ↓↓↓
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容