本篇文章主要介紹了索引使用技巧及，小編覺得挺不錯的，現在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
一.索引的作用
一般的應用系統，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現性能問題，遇到最多的，也是最容易出問題的，還是一些復雜的操作，所以查詢語句的優化顯然是重中之重。
在數據量和訪問量不大的情況下，mysql訪問是非常快的，是否加索引對訪問影響不大。但是當數據量和訪問量劇增的時候，就會發現mysql變慢，甚至down掉，這就必須要考慮優化sql了，給數據庫建立正確合理的索引，是mysql優化的一個重要手段。?
索引的目的在于提高查詢效率，可以類比字典，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y字母，再找到剩下的sql。如果沒有索引，那么你可能需要把所有單詞看一遍才能找到你想要的。除了詞典，生活中隨處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過不斷的縮小想要獲得數據的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種查找方式來鎖定數據。
在創建索引時，需要考慮哪些列會用于 SQL 查詢，然后為這些列創建一個或多個索引。事實上，索引也是一種表，保存著主鍵或索引字段，以及一個能將每個記錄指向實際表的指針。數據庫用戶是看不到索引的，它們只是用來加速查詢的。數據庫搜索引擎使用索引來快速定位記錄。
INSERT 與 UPDATE 語句在擁有索引的表中執行會花費更多的時間，而SELECT 語句卻會執行得更快。這是因為，在進行插入或更新時，數據庫也需要插入或更新索引值。
二.索引的創建、刪除
索引的類型：
-
INDEX(普通索引)：允許出現相同的索引內容
-
PROMARY KEY(主鍵索引)：不允許出現相同的值
-
fulltext index(全文索引)：可以針對值中的某個單詞，但效率確實不敢恭維
-
組合索引：實質上是將多個字段建到一個索引里，列值的組合必須唯一
(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 、、！=操作，但,>=,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>
