mysql 索引怎么使用

CREATE [UNIQUE] INDEX index_name ON table_name(字段 [ASC|DESC]);

成都創(chuàng)新互聯(lián)主要從事網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)英吉沙,十載網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):028-86922220

UNIQUE --確保所有的索引列中的值都是可以區(qū)分的。

[ASC|DESC] --在列上按指定排序創(chuàng)建索引。

(創(chuàng)建索引的準則：

1.如果表里有幾百行記錄則可以對其創(chuàng)建索引(表里的記錄行數(shù)越多索引的效果就越明顯)。

2.不要試圖對表創(chuàng)建兩個或三個以上的索引。

3.為頻繁使用的行創(chuàng)建索引。

)

示例

create index i_1 on emp(empno asc);

如何高效地利用MySQL索引

1、要想高效利用索引，我們首先要考慮如何正確建立索引。

（1）在經(jīng)常做搜索的列上，也就是WHERE子句里經(jīng)常出現(xiàn)的列，考慮加上索引，加快搜索速度。

（2）唯一標識記錄的列，應(yīng)該加上唯一索引，強制該列的唯一性并且加快按該列查找記錄的速度。

（3）在內(nèi)連接使用的列上加上索引，最好是在內(nèi)連接用到字段都加上，因為MySQL優(yōu)化器會自動地選擇連接順序，然后觀察索引的使用情況，將沒用的索引刪除即可。

（4）在需要排序的列上加上索引，因為索引本身是按順序的組織的，它可以避免 filesort，要知道，Server層在進行排序時是在內(nèi)存中進行的，非常消耗資源。

（5）可以考慮實現(xiàn)覆蓋索引，即根據(jù) SELECT 的所有字段上創(chuàng)建聯(lián)合索引，這樣存儲引擎只用讀取索引而不用去回表查詢，極大地減少了對數(shù)據(jù)表的訪問，大大地提高了性能。

（6）對于那些選擇性很小的列，比如性別列，增加索引并不能明顯加快查詢速度，反而該索引會成為表的累贅。

（7）對于那些定義為text, image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因為，這些列的要么數(shù)據(jù)量相當(dāng)大，要么取值很少。

（8）當(dāng)對寫性能的要求遠遠大于讀性能時，不應(yīng)該創(chuàng)建索引。寫性能和讀性能是互相矛盾的。這是因為，維護一個 B+Tree 成本是非常大的，對索引的寫會涉及到頁的分裂等。

（9）復(fù)合索引的幾個字段是否經(jīng)常同時以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引，否則考慮單字段索引。這還是說明，滿足查詢性能的前提下，索引越少越好。

（10）如果復(fù)合索引所包含的字段超過3個，那么仔細考慮其必要性，考慮減少復(fù)合的字段。

（11）在用于GROUP BY的列上加上索引，避免使用臨時表。

（12）對于較長的字符列，如 char、varchar等，由于字符串的比較相對來說非常耗時，因此考慮使用前綴索引減少索引長度，或者創(chuàng)建自定義哈希索引，將字符串映射成整數(shù)，然后以該整數(shù)作為索引，同時以字符串的值作為過濾條件。

我們在創(chuàng)建索引時，可以根據(jù)下面原則進行簡單判斷：索引是否將相關(guān)記錄集合到了一起，從未減少了磁盤I/O，加快搜索速度？索引中數(shù)據(jù)的排列順序是否和查找的數(shù)據(jù)的排列順序一致，從而避免了Server層的排序？索引中的列是否包含了查詢中需要的全部列從而實現(xiàn)了覆蓋索引？ 這幾個條件層層遞進，滿足得越多越好。

2、索引正確地建立了，我們還需要正確地使用它們：

（1）使用了運算符 !=，以及關(guān)鍵字not in，not exist，，等，總之產(chǎn)生的結(jié)果集很大時（也在where條件進行大范圍的選擇時），往往導(dǎo)致引擎不使用索引而是走全盤掃描。因為如果使用索引會造成大量的隨機I/O，得不償失。

（2）如果對索引列進行運算，如 WHERE substr(name, 1, 3)=‘mark’，存儲引擎并不能聰明地判斷哪些索引滿足等式，因此不能使用到索引。

（3）使用到了LIKE，并且通配符在最前面時，不能使用索引。

（4）對于聯(lián)合索引 (a, b, c)，如果沒用到最左列，那么一般情況下都使用不到索引。但是，比如統(tǒng)計操作 count(*) where a xxx，是可以使用到該聯(lián)合索引的。畢竟統(tǒng)計這類操作，它不是檢索，并不需要索引完全有序。

（5）對于聯(lián)合索引，如果某個列使用了范圍查找，那么其右邊的列都無法作為索引優(yōu)化查詢，但是由于 ICP（Index Condition Pushdown），這些列能作為過濾條件在存儲引擎中對數(shù)據(jù)進行過濾。

（6）如果條件中有 OR，則必須每個OR用到的字段都有索引，否則不能使用任何索引。

（7）想在聯(lián)合查詢中使用索引來避免 filesort，則關(guān)聯(lián)查詢中的ORDER BY用到的字段必須全部是第一張表（驅(qū)動表）上的。

MySQL索引

MySQL的Innodb存儲引擎的索引分為聚集索引和非聚集索引兩大類

特點：B+樹葉子節(jié)點存儲行數(shù)據(jù)

一個表中，必須有一個聚集索引，只能有一個聚集索引，Innodb通常把一個表的主鍵索引作為聚集索引，如果沒有主鍵InnoDB會選擇一個唯一索引代替。如果沒有這樣的索引，InnoDB會隱式的定義一個主鍵來作為聚集索引，這個字段為6個字節(jié)，類型為長整形。

利用主鍵索引查找行數(shù)據(jù)是最快的，建議使用自增主鍵原因是利于索引樹的構(gòu)建（主鍵自增寫入時新插入的數(shù)據(jù)不會影響到原有頁，插入效率高；但是如果主鍵是無序的或者隨機的，那每次的插入可能會導(dǎo)致原有頁頻繁的分裂，影響插入效率）

特點：B+樹葉子節(jié)點存儲主鍵ID

一個表中可以有多個非聚集索引，每個非聚集索引即是一棵B+樹

通過非聚集索引查找數(shù)據(jù)時，需要先在非聚集索引上找到主鍵ID，再從聚集索引獲取行數(shù)據(jù)，這個過程就稱之為回表

B樹索引中的B樹實際上是B+樹，至于為什么使用B+樹而不使用B樹或者紅黑樹的原因在另外的文章中有提及。

特點：

特點：類似JDK中的HashMap，但無法支持范圍查詢

特點：使用的算法仍然是B樹索引，不同的就是索引列的值必須唯一

對于普通索引來說，查找到滿足條件的第一個記錄后，需要查找下一個記錄，直到碰到第一個不滿足條件的記錄。

對于唯一索引來說，由于索引定義了唯一性，查找到第一個滿足條件的記錄后，就會停止繼續(xù)檢索，提升索引性能

另外插入行時會構(gòu)建該唯一索引，假如索引值重復(fù)將插入失敗，適合業(yè)務(wù)上做唯一性檢驗

通過建立倒排索引，可以極大的提升檢索效率,解決判斷字段是否包含的問題，但是業(yè)務(wù)上一般都不采用這種索引，而是使用ES處理全文搜索需求

僅對某個特定字段建立的索引，如(biz_id)

對多個字段建立的索引，如(biz_id,type)

mysql之字符串字段添加索引

字符串創(chuàng)建索引方式：

1、直接創(chuàng)建完整索引，比較占用空間。

2、創(chuàng)建前綴索引，節(jié)省空間，但會增加查詢掃描次數(shù)，并且不能使用覆蓋索引。

3、倒序存儲，在創(chuàng)建前綴索引，用于繞過字符串本身前綴的卻分度不夠的問題。

4、創(chuàng)建hash字段索引，查詢性能穩(wěn)定，有額外的存儲和計算消耗。

倒序存儲和hash字段索引都不支持范圍查詢。倒序存儲的字段上創(chuàng)建的所有是按照倒序字符串的方式排序的。hash字段的方式也只能支持等值查詢。

mysql alter table SUser add index index1(email); :包含了每個記錄的整個字符串

或

mysql alter table SUser add index index2(email(6)); :-對于每個記錄只取前6個字節(jié)

全字段索引操作流程

使用的是 index1（即 email 整個字符串的索引結(jié)構(gòu)），執(zhí)行順序是這樣的：

1、從 index1 索引樹找到滿足索引值是’ zhangssxyz@xxx.com ’的這條記錄，取得 ID2 的值；

2、到主鍵上查到主鍵值是 ID2 的行，判斷 email 的值是正確的，將這行記錄加入結(jié)果集；

3、取 index1 索引樹上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)已經(jīng)不滿足 email=' zhangssxyz@xxx.com ’的條件了，循環(huán)結(jié)束。

前綴字段索引操作流程

如果使用的是 index2（即 email(6) 索引結(jié)構(gòu)），執(zhí)行順序是這樣的：

1、從 index2 索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個是 ID1；

2、到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值不是’ zhangssxyz@xxx.com ’，這行記錄丟棄；

3、取 index2 上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判斷，這次值對了，將這行記錄加入結(jié)果集；

4、重復(fù)上一步，直到在 idxe2 上取到的值不是’zhangs’時，循環(huán)結(jié)束。

倒序查詢和hash字段的區(qū)別

它們的區(qū)別，主要體現(xiàn)在以下三個方面：

1、從占用的額外空間來看，倒序存儲方式在主鍵索引上，不會消耗額外的存儲空間，而 hash 字段方法需要增加一個字段。當(dāng)然，倒序存儲方式使用 4 個字節(jié)的前綴長度應(yīng)該是不夠的，如果再長一點，這個消耗跟額外這個 hash 字段也差不多抵消了。

2、在 CPU 消耗方面，倒序方式每次寫和讀的時候，都需要額外調(diào)用一次 reverse 函數(shù)，而 hash 字段的方式需要額外調(diào)用一次 crc32() 函數(shù)。如果只從這兩個函數(shù)的計算復(fù)雜度來看的話，reverse 函數(shù)額外消耗的 CPU 資源會更小些。

3、從查詢效率上看，使用 hash 字段方式的查詢性能相對更穩(wěn)定一些。因為 crc32 算出來的值雖然有沖突的概率，但是概率非常小，可以認為每次查詢的平均掃描行數(shù)接近 1。而倒序存儲方式畢竟還是用的前綴索引的方式，也就是說還是會增加掃描行數(shù)。

網(wǎng)頁名稱：mysql索引字段怎么用,MySQL給字段加索引
網(wǎng)站網(wǎng)址：http://aaarwkj.com/article32/dsiiopc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、做網(wǎng)站、響應(yīng)式網(wǎng)站、電子商務(wù)、關(guān)鍵詞優(yōu)化、建站公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)