詳解mysql索引使用技巧及注意事項

本篇文章主要介紹了索引使用技巧及,小編覺得挺不錯的,現在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧

一.索引的作用

一般的應用系統,讀寫比例在10:1左右,而且插入操作和一般的更新操作很少出現性能問題,遇到最多的,也是最容易出問題的,還是一些復雜的操作,所以查詢語句的優化顯然是重中之重。

在數據量和訪問量不大的情況下,mysql訪問是非常快的,是否加索引對訪問影響不大。但是當數據量和訪問量劇增的時候,就會發現mysql變慢,甚至down掉,這就必須要考慮優化sql了,給數據庫建立正確合理的索引,是mysql優化的一個重要手段。?

索引的目的在于提高查詢效率,可以類比字典,如果要查“mysql”這個單詞,我們肯定需要定位到m字母,然后從下往下找到y字母,再找到剩下的sql。如果沒有索引,那么你可能需要把所有單詞看一遍才能找到你想要的。除了詞典,生活中隨處可見索引的例子,如火車站的車次表、圖書的目錄等。它們的原理都是一樣的,通過不斷的縮小想要獲得數據的范圍來篩選出最終想要的結果,同時把隨機的事件變成順序的事件,也就是我們總是通過同一種查找方式來鎖定數據。

在創建索引時,需要考慮哪些列會用于 SQL 查詢,然后為這些列創建一個或多個索引。事實上,索引也是一種表,保存著主鍵或索引字段,以及一個能將每個記錄指向實際表的指針。數據庫用戶是看不到索引的,它們只是用來加速查詢的。數據庫搜索引擎使用索引來快速定位記錄。

INSERT 與 UPDATE 語句在擁有索引的表中執行會花費更多的時間,而SELECT 語句卻會執行得更快。這是因為,在進行插入或更新時,數據庫也需要插入或更新索引值。

二.索引的創建、刪除

索引的類型:

  1. UNIQUE(唯一索引):不可以出現相同的值,可以有

  2. INDEX(普通索引):允許出現相同的索引內容

  3. PROMARY KEY(主鍵索引):不允許出現相同的值

  4. fulltext index(全文索引):可以針對值中的某個單詞,但效率確實不敢恭維

  5. 組合索引:實質上是將多個字段建到一個索引里,列值的組合必須唯一

(1)使用ALTER TABLE語句創建索性

應用于表創建完畢之后再添加。

ALTER?TABLE?表名?ADD?索引類型?(unique,primary?key,fulltext,index)[索引名](字段名)
//普通索引  alter?table?table_name?add?index?index_name?(column_list)?;  //唯一索引  alter?table?table_name?add?unique?(column_list)?;  //主鍵索引  alter?table?table_name?add?primary?key?(column_list)?;

ALTER TABLE可用于創建普通索引、UNIQUE索引和PRIMARY KEY索引3種索引格式,table_name是要增加索引的表名,column_list指出對哪些列進行索引,多列時各列之間用逗號分隔。索引名index_name可選,缺省時,MySQL將根據第一個索引列賦一個名稱。另外,ALTER TABLE允許在單個語句中更改多個表,因此可以同時創建多個索引。

(2)使用CREATE INDEX語句對表增加索引

CREATE INDEX可用于對表增加普通索引或UNIQUE索引,可用于建表時創建索引。

CREATE?INDEX?index_name?ON?table_name(username(length));

如果是CHAR,VARCHAR類型,length可以小于字段實際長度;如果是BLOB和TEXT類型,必須指定 length。

//create只能添加這兩種索引;  CREATE?INDEX?index_name?ON?table_name?(column_list)  CREATE?UNIQUE?INDEX?index_name?ON?table_name?(column_list)

table_name、index_name和column_list具有與ALTER TABLE語句中相同的含義,索引名不可選。另外,不能用CREATE INDEX語句創建PRIMARY KEY索引。

(3)刪除索引

刪除索引可以使用ALTER TABLE或DROP INDEX語句來實現。DROP INDEX可以在ALTER TABLE內部作為一條語句處理,其格式如下:

drop?index?index_name?on?table_name?;    alter?table?table_name?drop?index?index_name?;    alter?table?table_name?drop?primary?key?;

其中,在前面的兩條語句中,都刪除了table_name中的索引index_name。而在最后一條語句中,只在刪除PRIMARY KEY索引中使用,因為一個表只可能有一個PRIMARY KEY索引,因此不需要指定索引名。如果沒有創建PRIMARY KEY索引,但表具有一個或多個UNIQUE索引,則MySQL將刪除第一個UNIQUE索引。

如果從表中刪除某列,則索引會受影響。對于多列組合的索引,如果刪除其中的某列,則該列也會從索引中刪除。如果刪除組成索引的所有列,則整個索引將被刪除。

(4) 組合索引與前綴索引

在這里要指出,組合索引和前綴索引是對建立索引技巧的一種稱呼,并不是索引的類型。為了更好的表述清楚,建立一個demo表如下。

create?table?USER_DEMO  (  ??ID??????????int?not?null?auto_increment?comment?'主鍵',  ??LOGIN_NAME??????varchar(100)?not?null?comment?'登錄名',  ??PASSWORD???????varchar(100)?not?null?comment?'密碼',  ??CITY?????????varchar(30)?not?null?comment?'城市',  ??AGE?????????int?not?null?comment?'年齡',  ??SEX?????????int?not?null?comment?'性別(0:女?1:男)',  ??primary?key?(ID)  );

為了進一步榨取mysql的效率,就可以考慮建立組合索引,即將LOGIN_NAME,CITY,AGE建到一個索引里:

代碼如下:

ALTER?TABLE?USER_DEMO?ADD?INDEX?name_city_age?(LOGIN_NAME(16),CITY,AGE);

建表時,LOGIN_NAME長度為100,這里用16,是因為一般情況下名字的長度不會超過16,這樣會加快索引查詢速度,還會減少索引文件的大小,提高INSERT,UPDATE的更新速度。

如果分別給LOGIN_NAME,CITY,AGE建立單列索引,讓該表有3個單列索引,查詢時和組合索引的效率是大不一樣的,甚至遠遠低于我們的組合索引。雖然此時有三個索引,但mysql只能用到其中的那個它認為似乎是最有效率的單列索引,另外兩個是用不到的,也就是說還是一個全表掃描的過程。

建立這樣的組合索引,就相當于分別建立如下三種組合索引:

LOGIN_NAME,CITY,AGE  LOGIN_NAME,CITY  LOGIN_NAME

為什么沒有CITY,AGE等這樣的組合索引呢?這是因為mysql組合索引“最左前綴”的結果。簡單的理解就是只從最左邊的開始組合,并不是只要包含這三列的查詢都會用到該組合索引。也就是說name_city_age(LOGIN_NAME(16),CITY,AGE)從左到右進行索引,如果沒有左前索引,mysql不會執行索引查詢。

如果索引列長度過長,這種列索引時將會產生很大的索引文件,不便于操作,可以使用前綴索引方式進行索引,前綴索引應該控制在一個合適的點,控制在0.31黃金值即可(大于這個值就可以創建)。

SELECT?COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*)?FROM?Arctic;?--?這個值大于0.31就可以創建前綴索引,Distinct去重復    ALTER?TABLE?`user`?ADD?INDEX?`uname`(title(10));?--?增加前綴索引SQL,將人名的索引建立在10,這樣可以減少索引文件大小,加快索引查詢速度

三.索引的使用及注意事項???

EXPLAIN可以幫助開發人員分析SQL問題,explain顯示了mysql如何使用索引來處理select語句以及連接表,可以幫助選擇更好的索引和寫出更優化的查詢語句。

使用方法,在select語句前加上Explain就可以了:

Explain?select?*?from?user?where?id=1;

盡量避免這些不走索引的sql:

SELECT?`sname`?FROM?`stu`?WHERE?`age`+10=30;--?不會使用索引,因為所有索引列參與了計算    SELECT?`sname`?FROM?`stu`?WHERE?LEFT(`date`,4)?<p>-- 如果mysql估計使用全表掃描要比使用索引快,則不使用索引</p><p>索引雖然好處很多,但過多的使用索引可能帶來相反的問題,索引也是有缺點的:</p><ol class=" list-paddingleft-2"> <li><p>雖然索引大大提高了查詢速度,同時卻會降低更新表的速度,如對表進行INSERT,UPDATE和<a href="http://www.php.cn/code/4705.html" target="_blank"></a>。因為更新表時,mysql不僅要保存數據,還要保存一下索引文件</p></li> <li><p>建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重,但如果你在要給大表上建了多種組合索引,索引文件會膨脹很寬</p></li> </ol><p>索引只是提高效率的一個方式,如果mysql有大數據量的表,就要花時間研究建立最優的索引,或優化查詢語句。</p><p><strong>使用索引時,有一些技巧:</strong></p><p>1.索引不會包含有NULL的列</p><p>只要列中包含有NULL值,都將不會被包含在索引中,復合索引中只要有一列含有NULL值,那么這一列對于此符合索引就是無效的。</p><p>?2.使用短索引</p><p>對串列進行索引,如果可以就應該指定一個前綴長度。例如,如果有一個char(255)的列,如果在前10個或20個字符內,多數值是唯一的,那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。</p><p>3.索引列排序</p><p>mysql查詢只使用一個索引,因此如果where子句中已經使用了索引的話,那么order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作,盡量不要包含多個列的排序,如果需要最好給這些列建復合索引。</p><p>4.like語句操作</p><p>一般情況下不鼓勵使用like操作,如果非使用不可,注意正確的使用方式。like ‘%aaa%'不會使用索引,而like ‘aaa%'可以使用索引。</p><p>5.不要在列上進行運算</p><p>6.不使用NOT IN 、、!=操作,但,&gt;=,BETWEEN,IN是可以用到索引的</p><p>7.索引要建立在經常進行select操作的字段上。</p><p>這是因為,如果這些列很少用到,那么有無索引并不能明顯改變查詢速度。相反,由于增加了索引,反而降低了系統的維護速度和增大了空間需求。</p><p>8.索引要建立在值比較唯一的字段上。</p><p>9.對于那些定義為text、image和bit<a href="http://www.php.cn/code/4705.html" target="_blank"></a>的列不應該增加索引。因為這些列的數據量要么相當大,要么取值很少。</p><p>10.在where和join中出現的列需要建立索引。</p><p>11.where的查詢條件里有不等號(where column != …),mysql將無法使用索引。</p><p>12.如果where字句的查詢條件里使用了函數(如:where DAY(column)=…),mysql將無法使用索引。</p><p>13.在join操作中(需要從多個數據表提取數據時),mysql只有在主鍵和外鍵的數據類型相同時才能使用索引,否則及時建立了索引也不會使用。<br></p>

? 版權聲明
THE END
喜歡就支持一下吧
點贊12 分享