建立索引如何優(yōu)化SQL？相信很多沒有經(jīng)驗(yàn)的人對此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

創(chuàng)新互聯(lián)公司長期為上1000家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為覃塘企業(yè)提供專業(yè)的成都做網(wǎng)站、網(wǎng)站制作，覃塘網(wǎng)站改版等技術(shù)服務(wù)。擁有10年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。

1、建立普通索引：對經(jīng)常出現(xiàn)在 where關(guān)鍵字后面的表字段建立對應(yīng)的索引。。

2、建立復(fù)合索引：如果 where關(guān)鍵字后面常出現(xiàn)的有幾個字段，可以建立對應(yīng)的 復(fù)合索引。要注意可以優(yōu)化的一點(diǎn)是，將單獨(dú)出現(xiàn)最多的字段放在前面。例如現(xiàn)在我們有兩個字段 a和 b經(jīng)常會同時出現(xiàn)在 where關(guān)鍵字后面：

select * from t where a = 1 and b = 2;   \* Q1 *\

也有很多 SQL會單獨(dú)使用字段 a作為查詢條件：

select * from t where a = 2;   \* Q2 *\

此時，我們可以建立復(fù)合索引 index(a,b)。因?yàn)椴坏?Q1可以利用復(fù)合索引，Q2也可以利用復(fù)合索引。

3、最左前綴匹配原則

如果我們使用的是復(fù)合索引，應(yīng)該盡量遵循 最左前綴匹配原則。MySQL會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配。假如此時我們有一條SQL：

select * from t where a = 1 and b = 2 and c > 3 and d = 4;

那么我們應(yīng)該建立的復(fù)合索引是：index(a,b,d,c)而不是 index(a,b,c,d)。因?yàn)樽侄?c是范圍查詢，當(dāng) MySQL遇到范圍查詢就停止索引的匹配了。大家也注意到了，其實(shí) a,b,d在 SQL的位置是可以任意調(diào)整的，優(yōu)化器會找到對應(yīng)的復(fù)合索引。還要注意一點(diǎn)的是，最左前綴匹配原則不但是復(fù)合索引的最左 N個字段；也可以是單列（字符串類型）索引的最左 M個字符。例如我們常說的 like關(guān)鍵字，盡量不要使用全模糊查詢，因?yàn)檫@樣用不到索引；所以建議是使用右模糊查詢：select * from t where name like '李%'（查詢所有姓李的同學(xué)的信息）。

4、索引下推：很多時候，我們還可以復(fù)合索引的 索引下推 來優(yōu)化 SQL。例如此時我們有一個復(fù)合索引：index(name,age)，然后有一條 SQL如下：

select * from user where name like '張%' and age = 10 and sex = 'm';

根據(jù)復(fù)合索引的最左前綴匹配原則，MySQL匹配到復(fù)合索引 index(name,age)的 name時，就停止匹配了；然后接下來的流程就是根據(jù)主鍵回表，判斷 age和 sex的條件是否同時滿足，滿足則返回給客戶端。

但是由于有索引下推的優(yōu)化，匹配到 name時，不會立刻回表；而是先判斷復(fù)合索引 index(name,age)中的 age是否符合條件；符合條件才進(jìn)行回表接著判斷 sex是否滿足，否則會被過濾掉。那么借著 MySQL 5.6引入的索引下推優(yōu)化 ，可以做到減少回表的次數(shù)。

5、覆蓋索引：很多時候，我們還可以覆蓋索引來優(yōu)化SQL。

情況一：SQL只查詢主鍵作為返回值。主鍵索引（聚簇索引）的葉子節(jié)點(diǎn)是整行數(shù)據(jù)，而普通索引（二級索引）的葉子節(jié)點(diǎn)是主鍵的值。所以當(dāng)我們的 SQL只查詢主鍵值，可以直接獲取對應(yīng)葉子節(jié)點(diǎn)的內(nèi)容，而避免回表。

情況二：SQL的查詢字段就在索引里。復(fù)合索引：假如此時我們有一個復(fù)合索引 index(name,age)，有一條 SQL如下：

select name,age from t where name like '張%';

由于是字段 name是右模糊查詢所以可以走復(fù)合索引，然后匹配到 name時，不需要回表，因?yàn)?SQL只是查詢字段 name和 age，所以直接返回索引值就 ok了。

6、普通索引

盡量 使用普通索引 而不是唯一索引。首先，普通索引和唯一索引的查詢性能其實(shí)不會相差很多；當(dāng)然了，前提是要查詢的記錄都在同一個數(shù)據(jù)頁中，否則普通索引的性能會慢很多。但是，普通索引的更新操作性能比唯一索引更好；其實(shí)很簡單，因?yàn)槠胀ㄋ饕芾?change buffer來做更新操作；而唯一索引因?yàn)橐袛喔碌闹凳欠袷俏ㄒ坏模悦看味夹枰獙⒋疟P中的數(shù)據(jù)讀取到 buffer pool中。

7、前綴索引

我們要學(xué)會巧妙的使用 前綴索引，避免索引值過大。例如有一個字段是 addr varchar(255)，但是如果一整個建立索引 [ index(addr) ]，會很浪費(fèi)磁盤空間，所以會選擇建立前綴索引 [ index(addr(64)) ]。建立前綴索引，一定要關(guān)注字段的區(qū)分度。例如像身份證號碼這種字段的區(qū)分度很低，只要出生地一樣，前面好多個字符都是一樣的；這樣的話，最不理想時，可能會掃描全表。前綴索引避免不了回表，即無法使用覆蓋索引這個優(yōu)化點(diǎn)，因?yàn)樗饕抵皇亲侄蔚那?n個字符，需要回表才能判斷查詢值是否和字段值是一致的。

怎么解決？倒序存儲：像身份證這種，后面的幾位區(qū)分度就非常的高了；我們可以這么查詢：

select field_list from t where id_card = reverse('input_id_card_string'

增加 hash字段并為 hash字段添加索引。

8、干凈的索引列：索引列不能參與計算，要保持索引列“干凈”。假設(shè)我們給表 student的字段 birthday建立了普通索引。下面的 SQL語句不能利用到索引來提升執(zhí)行效率：

select * from student where DATE_FORMAT(birthday,'%Y-%m-%d') = '2020-02-02';

我們應(yīng)該改成下面這樣：

select * from student where birthday = STR_TO_DATE('2020-02-02', '%Y-%m-%d');

9、擴(kuò)展索引

我們應(yīng)該盡量擴(kuò)展索引，而不是新增索引，一個表最好不要超過5個索引；一個表的索引越多，會導(dǎo)致更新操作更加耗費(fèi)性能。

看完上述內(nèi)容，你們掌握建立索引如何優(yōu)化SQL的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！