轉(zhuǎn)自：https://blog.csdn.net/aa1215018028/article/details/80982208

關(guān)于MySQL索引的好處，如果正確合理設(shè)計(jì)并且使用索引的MySQL是一輛蘭博基尼的話，那么沒有設(shè)計(jì)和使用索引的MySQL就是一個(gè)人力三輪車。對(duì)于沒有索引的表，單表查詢可能幾十萬數(shù)據(jù)就是瓶頸，而通常大型網(wǎng)站單日就可能會(huì)產(chǎn)生幾十萬甚至幾百萬的數(shù)據(jù)，沒有索引查詢會(huì)變的非常緩慢。還是以WordPress來說，其多個(gè)數(shù)據(jù)表都會(huì)對(duì)經(jīng)常被查詢的字段添加索引，比如wp_comments表中針對(duì)5個(gè)字段設(shè)計(jì)了BTREE（二叉樹）索引。

?? 合理的設(shè)計(jì)自己的數(shù)據(jù)庫表和索引可以大大提高數(shù)據(jù)的檢索速度，如果在大表中濫用索引反而會(huì)影響你的數(shù)據(jù)庫性能，下邊數(shù)據(jù)庫優(yōu)化有詳細(xì)提到。

執(zhí)行下面的SQL語句：

1 mysql>?SELECT?id,FROM_UNIXTIME(time)?FROM?article?WHERE?a.title='測(cè)試標(biāo)題'

如果有20W條數(shù)據(jù)不加索引查詢需要的時(shí)間非?？植赖?，如果加上聯(lián)合查詢和其他一些約束條件，數(shù)據(jù)庫會(huì)瘋狂的消耗內(nèi)存，并且會(huì)影響前端程序的執(zhí)行。這時(shí)給title字段添加一個(gè)BTREE索引：

1 mysql>?ALTER?TABLE?article?ADD?INDEX?index_article_title?ON?title(200);

加上索引再次執(zhí)行上述查詢語句，其對(duì)比非常明顯。

MySQL索引的概念

??? 索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)，它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針。更通俗的說，數(shù)據(jù)庫索引好比是一本書前面的目錄，能加快數(shù)據(jù)庫的查詢速度。上述SQL語句，在沒有索引的情況下，數(shù)據(jù)庫會(huì)遍歷全部200萬條數(shù)據(jù)后選擇符合條件的；而有了相應(yīng)的索引之后，數(shù)據(jù)庫會(huì)直接在索引中查找符合條件的選項(xiàng)。如果我們把SQL語句換成“SELECT * FROM article WHERE id=2000000”，那么你是希望數(shù)據(jù)庫按照順序讀取完200萬行數(shù)據(jù)以后給你結(jié)果還是直接在索引中定位呢？加上索引后查詢數(shù)據(jù)會(huì)直接在索引中定位。（注：一般數(shù)據(jù)庫默認(rèn)都會(huì)為主鍵生成索引）。

??? 索引分為聚簇索引和非聚簇索引兩種，聚簇索引是按照數(shù)據(jù)存放的物理位置為順序的，而非聚簇索引就不一樣了；聚簇索引能提高多行檢索的速度，而非聚簇索引對(duì)于單行的檢索很快。

1. 普通索引

???? 這是最基本的索引，它沒有任何限制，比如上文中為title字段創(chuàng)建的索引就是一個(gè)普通索引，MyIASM中默認(rèn)的BTREE類型的索引，也是我們大多數(shù)情況下用到的索引。

01 –直接創(chuàng)建索引

CREATE?INDEX?index_name?ON?table(column(length))

02 –修改表結(jié)構(gòu)的方式添加索引

?ALTER?TABLE?table_name?ADD?INDEX?index_name?ON?(column(length))

03 –創(chuàng)建表的時(shí)候同時(shí)創(chuàng)建索引

?CREATE?TABLE?`table` (

?`id`?int(11)?NOT?NULL?AUTO_INCREMENT ,

?`title`?char(255)?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NOT?NULL?,

?`content` text?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NULL?,

?`time`?int(10)?NULL?DEFAULT?NULL?,

?PRIMARY?KEY?(`id`),

?INDEX?index_name (title(length))

?)

04 –刪除索引

DROP?INDEX?index_name?ON?table

2. 唯一索引

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

01 –創(chuàng)建唯一索引

?CREATE?UNIQUE?INDEX?indexName?ON?table(column(length))

02 –修改表結(jié)構(gòu)

?ALTER?TABLE?table_name?ADD?UNIQUE?indexName?ON?(column(length))

03 –創(chuàng)建表的時(shí)候直接指定

?CREATE?TABLE?`table` (

?`id`?int(11)?NOT?NULL?AUTO_INCREMENT ,

?`title`?char(255)?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NOT?NULL?,

?`content` text?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NULL?,

?`time`?int(10)?NULL?DEFAULT?NULL?,

?PRIMARY?KEY?(`id`),

?UNIQUE?indexName (title(length))

?);

3. 全文索引（FULLTEXT）

????? MySQL從3.23.23版開始支持全文索引和全文檢索，在mysql5.6版本以前FULLTEXT索引僅可用于 MyISAM 表，在5.6之后innodb引擎也支持FULLTEXT索引；他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創(chuàng)建，或是隨后使用ALTER TABLE 或CREATE INDEX被添加。

?????? //對(duì)于較大的數(shù)據(jù)集，將你的資料輸入一個(gè)沒有FULLTEXT索引的表中，然后創(chuàng)建索引，其速度比把資料輸入現(xiàn)有FULLTEXT索引的速度更為快。不過切記對(duì)于大容量的數(shù)據(jù)表，生成全文索引是一個(gè)非常消耗時(shí)間非常消耗硬盤空間的做法。

01 –創(chuàng)建表的適合添加全文索引

?CREATE?TABLE?`table` (

?`id`?int(11)?NOT?NULL?AUTO_INCREMENT ,

?`title`?char(255)?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NOT?NULL?,

?`content` text?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NULL?,

?`time`?int(10)?NULL?DEFAULT?NULL?,

?PRIMARY?KEY?(`id`),

?FULLTEXT (content)

?);

02 –修改表結(jié)構(gòu)添加全文索引

?ALTER?TABLE?article?ADD?FULLTEXT index_content(content)

03 –直接創(chuàng)建索引

?CREATE?FULLTEXT?INDEX?index_content?ON?article(content)

4. 單列索引、多列索引

多個(gè)單列索引與單個(gè)多列索引的查詢效果不同，因?yàn)閳?zhí)行查詢時(shí)，MySQL只能使用一個(gè)索引，會(huì)從多個(gè)索引中選擇一個(gè)限制最為嚴(yán)格的索引。

5. 組合索引（最左前綴）

平時(shí)用的SQL查詢語句一般都有比較多的限制條件，所以為了進(jìn)一步榨取MySQL的效率，就要考慮建立組合索引。例如上表中針對(duì)title和time建立一個(gè)組合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面兩組組合索引：

–title,time

–title

為什么沒有time這樣的組合索引呢？這是因?yàn)镸ySQL組合索引“最左前綴”的結(jié)果。簡(jiǎn)單的理解就是只從最左面的開始組合。并不是只要包含這兩列的查詢都會(huì)用到該組合索引，如下面的幾個(gè)SQL所示：

1 –使用到上面的索引

?SELECT?*?FROM?article WHREE title='測(cè)試'?AND?time=1234567890;

?SELECT?*?FROM?article WHREE title='測(cè)試';

2 –不使用上面的索引

?SELECT?*?FROM?article WHREE?time=1234567890;

MySQL索引的優(yōu)化

?? 上面都在說使用索引的好處，但過多的使用索引將會(huì)造成濫用。因此索引也會(huì)有它的缺點(diǎn)：雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。建立索引會(huì)占用磁盤空間的索引文件。一般情況這個(gè)問題不太嚴(yán)重，但如果你在一個(gè)大表上創(chuàng)建了多種組合索引，索引文件的會(huì)膨脹很快。索引只是提高效率的一個(gè)因素，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時(shí)間研究建立最優(yōu)秀的索引，或優(yōu)化查詢語句。下面是一些總結(jié)以及收藏的MySQL索引的注意事項(xiàng)和優(yōu)化方法。

1. 何時(shí)使用聚集索引或非聚集索引？

聚集索引:

??一種索引，該索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序。

　　聚集索引確定表中數(shù)據(jù)的物理順序。聚集索引類似于電話簿，后者按姓氏排列數(shù)據(jù)。由于聚集索引規(guī)定數(shù)據(jù)在表中的物理存儲(chǔ)順序，因此一個(gè)表只能包含一個(gè)聚集索引。但該索引可以包含多個(gè)列（組合索引），就像電話簿按姓氏和名字進(jìn)行組織一樣。

? 聚集索引對(duì)于那些經(jīng)常要搜索范圍值的列特別有效。使用聚集索引找到包含第一個(gè)值的行后，便可以確保包含后續(xù)索引值的行在物理相鄰。例如，如果應(yīng)用程序執(zhí)行的一個(gè)查詢經(jīng)常檢索某一日期范圍內(nèi)的記錄，則使用聚集索引可以迅速找到包含開始日期的行，然后檢索表中所有相鄰的行，直到到達(dá)結(jié)束日期。這樣有助于提高此 類查詢的性能。同樣，如果對(duì)從表中檢索的數(shù)據(jù)進(jìn)行排序時(shí)經(jīng)常要用到某一列，則可以將該表在該列上聚集（物理排序），避免每次查詢?cè)摿袝r(shí)都進(jìn)行排序，從而節(jié) 省成本。

　 當(dāng)索引值唯一時(shí)，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇員 ID 列 emp_id 查找特定雇員的最快速的方法，是在 emp_id 列上創(chuàng)建聚集索引或 PRIMARY KEY 約束。

非聚集索引:

? 非聚集索引，必須先查到目錄中查到每一項(xiàng)數(shù)據(jù)對(duì)應(yīng)的頁碼，然后再根據(jù)頁碼查到具體內(nèi)容，該索引中索引的邏輯順序與磁盤上行的物理存儲(chǔ)順序不同。

? 索引是通過二叉樹的數(shù)據(jù)結(jié)構(gòu)來描述的，我們可以這么理解聚簇索引：索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)。而非聚簇索引的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只不過有一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊。

備注：聚集索引一張表只能創(chuàng)建一個(gè)，非聚集索引一張表可以創(chuàng)建多個(gè)，在mysql中InnoDB引擎是唯一支持聚集索引的存儲(chǔ)引擎。InnoDB按照主鍵（Primary Key）進(jìn)行聚集，如果沒有定義主鍵，InnoDB會(huì)試著使用唯一的非空索引來代替。如果沒有這種索引，InnoDB就會(huì)定義隱藏的主鍵然后在上面進(jìn)行聚集。

2. 索引不會(huì)包含有NULL值的列

??? 只要列中包含有NULL值都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有NULL值，那么這一列對(duì)于此復(fù)合索引就是無效的。所以我們?cè)跀?shù)據(jù)庫設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL。

3. 使用短索引

??? 對(duì)串列進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長度。例如，如果有一個(gè)CHAR(255)的列，如果在前10個(gè)或20個(gè)字符內(nèi)，多數(shù)值是惟一的，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

例：CREATE?INDEX?index_name?ON?table(column(10 or 20))；

4. 索引列排序

??? MySQL查詢只使用一個(gè)索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的。因此數(shù)據(jù)庫默認(rèn)排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個(gè)列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引。

5. like語句操作

???? 一般情況下不鼓勵(lì)使用like操作，如果非使用不可，如何使用也是一個(gè)問題。like “%aaa%” 不會(huì)使用索引而like “aaa%”可以使用索引。

6. 不要在列上進(jìn)行運(yùn)算

???? 例如：select * from users where YEAR(adddate)<2007，將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致索引失效而進(jìn)行全表掃描，因此我們可以改成：select * from users where adddate<’2007-01-01′。關(guān)于這一點(diǎn)可以圍觀：一個(gè)單引號(hào)引發(fā)的MYSQL性能損失。

總結(jié)：

????? 最后總結(jié)一下，MySQL只對(duì)一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時(shí)候的like(不以通配符%或_開頭的情形)。而理論上每張表里面最多可創(chuàng)建16個(gè)索引，不過除非是數(shù)據(jù)量真的很多，否則過多的使用索引也不是那么好玩的，比如我剛才針對(duì)text類型的字段創(chuàng)建索引的時(shí)候，系統(tǒng)差點(diǎn)就卡死了。