《Mysql實例MySQL索引的缺點以及MySQL索引在實際操作中有哪些事項》要點：
本文介紹了Mysql實例MySQL索引的缺點以及MySQL索引在實際操作中有哪些事項，希望對您有用。如果有疑問，可以聯(lián)系我們。

以下的文章主要介紹的是MySQL索引的缺點以及MySQL索引在實際操作中有哪些事項是值得我們大家注意的,我們大家可能不知道過多的對索引進行使用將會造成濫用.因此MySQL索引也會有它的缺點：
雖然索引大大提高了查詢速度,同時卻會降低更新表的速度,如對表進行INSERT、UPDATE和DELETE.因為更新表時,MySQL不僅要保存數(shù)據(jù),還要保存一下索引文件.
建立索引會占用磁盤空間的索引文件.一般情況這個問題不太嚴重,但如果你在一個大表上創(chuàng)建了多種組合索引,索引文件的會膨脹很快.
索引只是提高效率的一個因素,如果你的MySQL有大數(shù)據(jù)量的表,就需要花時間研究建立最優(yōu)秀的MySQL索引,或優(yōu)化查詢語句.
使用索引的注意事項
使用索引時,有以下一些技巧和注意事項：
1、索引不會包含有NULL值的列
只要列中包含有NULL值都將不會被包含在MySQL索引中,復合索引中只要有一列含有NULL值,那么這一列對于此復合索引就是無效的.所以我們在數(shù)據(jù)庫設計時不要讓字段的默認值為NULL.
2、使用短索引
對串列進行索引,如果可能應該指定一個前綴長度.例如,如果有一個CHAR(255)的列,如果在前10個或20個字符內,多數(shù)值是惟一的,那么就不要對整個列進行索引.短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作.
3、索引列排序
MySQL查詢只使用一個索引,因此如果where子句中已經(jīng)使用了索引的話,那么order
by中的列是不會使用索引的.因此數(shù)據(jù)庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序,如果需要最好給這些列創(chuàng)建復合索引.
4、like語句操作
一般情況下不鼓勵使用like操作,如果非使用不可,如何使用也是一個問題.like “%aaa%” 不會使用MySQL索引而like “aaa%”可以使用索引.
5、不要在列上進行運算
select * from users where YEAR(adddate)<2007; 將在每個行上進行運算,這將導致索引失效而進行全表掃描,因此我們可以改成
select * from users where adddate<‘2007-01-01'; 不使用NOT IN和<>操作
以上,就對其中MySQL索引類型進行了介紹.


本文介紹了數(shù)據(jù)庫索引,及其優(yōu)、缺點.針對MySQL索引的特點、應用進行了詳細的描述.分析了如何避免MySQL無法使用,如何使用EXPLAIN分析查詢語句,如何優(yōu)化MySQL索引的應用.
　　索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分),它們包含著對數(shù)據(jù)表里所有記錄的引用指針.
　　注：[1]索引不是萬能的!索引可以加快數(shù)據(jù)檢索操作,但會使數(shù)據(jù)修改操作變慢.每修改數(shù)據(jù)記錄,索引就必須刷新一次.為了在某種程序上彌補這一缺陷,許多SQL命令都有一個DELAY_KEY_WRITE項.這個選項的作用是暫時制止MySQL在該命令每插入一條新記錄和每修改一條現(xiàn)有之后立刻對索引進行刷新,對索引的刷新將等到全部記錄插入/修改完畢之后再進行.在需要把許多新記錄插入某個數(shù)據(jù)表的場合,DELAY_KEY_WRITE選項的作用將非常明顯.[2]另外,索引還會在硬盤上占用相當大的空間.因此應該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引.注意,如果某個數(shù)據(jù)列包含許多重復的內容,為它建立索引就沒有太大的實際效果.
　　從理論上講,完全可以為數(shù)據(jù)表里的每個字段分別建一個索引,但MySQL把同一個數(shù)據(jù)表里的索引總數(shù)限制為16個.
　　1. InnoDB數(shù)據(jù)表的索引
　　與MyISAM數(shù)據(jù)表相比,索引對InnoDB數(shù)據(jù)的重要性要大得多.在InnoDB數(shù)據(jù)表上,索引對InnoDB數(shù)據(jù)表的重要性要在得多.在InnoDB數(shù)據(jù)表上,索引不僅會在搜索數(shù)據(jù)記錄時發(fā)揮作用,還是數(shù)據(jù)行級鎖定機制的苊、基礎."數(shù)據(jù)行級鎖定"的意思是指在事務操作的執(zhí)行過程中鎖定正在被處理的個別記錄,不讓其他用戶進行訪問.這種鎖定將影響到(但不限于)SELECT...LOCK IN SHARE MODE、SELECT...FOR UPDATE命令以及INSERT、UPDATE和DELETE命令.
　　出于效率方面的考慮,InnoDB數(shù)據(jù)表的數(shù)據(jù)行級鎖定實際發(fā)生在它們的索引上,而不是數(shù)據(jù)表自身上.顯然,數(shù)據(jù)行級鎖定機制只有在有關的數(shù)據(jù)表有一個合適的索引可供鎖定的時候才能發(fā)揮效力.
　　2. 限制
　　如果WEHERE子句的查詢條件里有不等號(WHERE coloum != ...),MySQL將無法使用索引.
　　類似地,如果WHERE子句的查詢條件里使用了函數(shù)(WHERE DAY(column) = ...),MySQL也將無法使用索引.
　　在JOIN操作中(需要從多個數(shù)據(jù)表提取數(shù)據(jù)時),MySQL只有在主鍵和外鍵的數(shù)據(jù)類型相同時才能使用索引.
　　如果WHERE子句的查詢條件里使用比較操作符LIKE和REGEXP,MySQL只有在搜索模板的第一個字符不是通配符的情況下才能使用索引.比如說,如果查詢條件是LIKE 'abc%',MySQL將使用索引；如果查詢條件是LIKE '%abc',MySQL將不使用索引.
　　在ORDER BY操作中,MySQL只有在排序條件不是一個查詢條件表達式的情況下才使用索引.(雖然如此,在涉及多個數(shù)據(jù)表查詢里,即使有索引可用,那些索引在加快ORDER BY方面也沒什么作用)
　　如果某個數(shù)據(jù)列里包含許多重復的值,就算為它建立了索引也不會有很好的效果.比如說,如果某個數(shù)據(jù)列里包含的凈是些諸如"0/1"或"Y/N"等值,就沒有必要為它創(chuàng)建一個索引.
　　
　　普通索引、唯一索引和主索引
　　1.普通索引
　　普通索引(由關鍵字KEY或INDEX定義的索引)的唯一任務是加快對數(shù)據(jù)的訪問速度.因此,應該只為那些最經(jīng)常出現(xiàn)在查詢條件(WHERE column = ...)或排序條件(ORDER BY column)中的數(shù)據(jù)列創(chuàng)建索引.只要有可能,就應該選擇一個數(shù)據(jù)最整齊、最緊湊的數(shù)據(jù)列(如一個整數(shù)類型的數(shù)據(jù)列)來創(chuàng)建索引.
　　2.唯一索引
　　普通索引允許被索引的數(shù)據(jù)列包含重復的值.比如說,因為人有可能同名,所以同一個姓名在同一個"員工個人資料"數(shù)據(jù)表里可能出現(xiàn)兩次或更多次.
　　如果能確定某個數(shù)據(jù)列將只包含彼此各不相同的值,在為這個數(shù)據(jù)列創(chuàng)建索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引.這么做的好處：一是簡化了MySQL對這個索引的管理工作,這個索引也因此而變得更有效率；二是MySQL會在有新記錄插入數(shù)據(jù)表時,自動檢查新記錄的這個字段的值是否已經(jīng)在某個記錄的這個字段里出現(xiàn)過了；如果是,MySQL將拒絕插入那條新記錄.也就是說,唯一索引可以保證數(shù)據(jù)記錄的唯一性.事實上,在許多場合,人們創(chuàng)建唯一索引的目的往往不是為了提高訪問速度,而只是為了避免數(shù)據(jù)出現(xiàn)重復.
　　3.主索引
　　在前面已經(jīng)反復多次強調過：必須為主鍵字段創(chuàng)建一個索引,這個索引就是所謂的"主索引".主索引與唯一索引的唯一區(qū)別是：前者在定義時使用的關鍵字是PRIMARY而不是UNIQUE.
　　4.外鍵索引
　　如果為某個外鍵字段定義了一個外鍵約束條件,MySQL就會定義一個內部索引來幫助自己以最有效率的方式去管理和使用外鍵約束條件.
　　5.復合索引
　　索引可以覆蓋多個數(shù)據(jù)列,如像INDEX(columnA, columnB)索引.這種索引的特點是MySQL可以有選擇地使用一個這樣的索引.如果查詢操作只需要用到columnA數(shù)據(jù)列上的一個索引,就可以使用復合索引INDEX(columnA, columnB).不過,這種用法僅適用于在復合索引中排列在前的數(shù)據(jù)列組合.比如說,INDEX(A, B, C)可以當做A或(A, B)的索引來使用,但不能當做B、C或(B, C)的索引來使用.
　　6.索引的長度
　　在為CHAR和VARCHAR類型的數(shù)據(jù)列定義索引時,可以把索引的長度限制為一個給定的字符個數(shù)(這個數(shù)字必須小于這個字段所允許的最大字符個數(shù)).這么做的好處是可以生成一個尺寸比較小、檢索速度卻比較快的索引文件.在絕大多數(shù)應用里,數(shù)據(jù)庫中的字符串數(shù)據(jù)大都以各種各樣的名字為主,把索引的長度設置為10~15個字符已經(jīng)足以把搜索范圍縮小到很少的幾條數(shù)據(jù)記錄了.
　　在為BLOB和TEXT類型的數(shù)據(jù)列創(chuàng)建索引時,必須對索引的長度做出限制；MySQL所允許的最大索引長度是255個字符.

　　全文索引
　　文本字段上的普通索引只能加快對出現(xiàn)在字段內容最前面的字符串(也就是字段內容開頭的字符)進行檢索操作.如果字段里存放的是由幾個、甚至是多個單詞構成的較大段文字,普通索引就沒什么作用了.這種檢索往往以LIKE %word%的形式出現(xiàn),這對MySQL來說很復雜,如果需要處理的數(shù)據(jù)量很大,響應時間就會很長.
　　這類場合正是全文索引(full-text index)可以大顯身手的地方.在生成這種類型的索引時,MySQL將把在文本中出現(xiàn)的所有單詞創(chuàng)建為一份清單,查詢操作將根據(jù)這份清單去檢索有關的數(shù)據(jù)記錄.全文索引即可以隨數(shù)據(jù)表一同創(chuàng)建,也可以等日后有必要時再使用下面這條命令添加：
　　ALTER TABLE tablename ADD FULLTEXT(column1, column2)
　　有了全文索引,就可以用SELECT查詢命令去檢索那些包含著一個或多個給定單詞的數(shù)據(jù)記錄了.下面是這類查詢命令的基本語法：
　　SELECT * FROM tablename
　　WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')
　　上面這條命令將把column1和column2字段里有word1、word2和word3的數(shù)據(jù)記錄全部查詢出來.
　　注解：InnoDB數(shù)據(jù)表不支持全文索引.

　　查詢和索引的優(yōu)化
　　只有當數(shù)據(jù)庫里已經(jīng)有了足夠多的測試數(shù)據(jù)時,它的性能測試結果才有實際參考價值.如果在測試數(shù)據(jù)庫里只有幾百條數(shù)據(jù)記錄,它們往往在執(zhí)行完第一條查詢命令之后就被全部加載到內存里,這將使后續(xù)的查詢命令都執(zhí)行得非常快--不管有沒有使用索引.只有當數(shù)據(jù)庫里的記錄超過了1000條、數(shù)據(jù)總量也超過了MySQL服務器上的內存總量時,數(shù)據(jù)庫的性能測試結果才有意義.
　　在不確定應該在哪些數(shù)據(jù)列上創(chuàng)建索引的時候,人們從EXPLAIN SELECT命令那里往往可以獲得一些幫助.這其實只是簡單地給一條普通的SELECT命令加一個EXPLAIN關鍵字作為前綴而已.有了這個關鍵字,MySQL將不是去執(zhí)行那條SELECT命令,而是去對它進行分析.MySQL將以表格的形式把查詢的執(zhí)行過程和用到的索引(如果有的話)等信息列出來.
　　在EXPLAIN命令的輸出結果里,第1列是從數(shù)據(jù)庫讀取的數(shù)據(jù)表的名字,它們按被讀取的先后順序排列.type列指定了本數(shù)據(jù)表與其它數(shù)據(jù)表之間的關聯(lián)關系(JOIN).在各種類型的關聯(lián)關系當中,效率最高的是system,然后依次是const、eq_ref、ref、range、index和All(All的意思是：對應于上一級數(shù)據(jù)表里的每一條記錄,這個數(shù)據(jù)表里的所有記錄都必須被讀取一遍--這種情況往往可以用一索引來避免).
　　possible_keys數(shù)據(jù)列給出了MySQL在搜索數(shù)據(jù)記錄時可選用的各個索引.key數(shù)據(jù)列是MySQL實際選用的索引,這個索引按字節(jié)計算的長度在key_len數(shù)據(jù)列里給出.比如說,對于一個INTEGER數(shù)據(jù)列的索引,這個字節(jié)長度將是4.如果用到了復合索引,在key_len數(shù)據(jù)列里還可以看到MySQL具體使用了它的哪些部分.作為一般規(guī)律,key_len數(shù)據(jù)列里的值越小越好(意思是更快).
　　ref數(shù)據(jù)列給出了關聯(lián)關系中另一個數(shù)據(jù)表里的數(shù)據(jù)列的名字.row數(shù)據(jù)列是MySQL在執(zhí)行這個查詢時預計會從這個數(shù)據(jù)表里讀出的數(shù)據(jù)行的個數(shù).row數(shù)據(jù)列里的所有數(shù)字的乘積可以讓我們大致了解這個查詢需要處理多少組合.
　　最后,extra數(shù)據(jù)列提供了與JOIN操作有關的更多信息,比如說,如果MySQL在執(zhí)行這個查詢時必須創(chuàng)建一個臨時數(shù)據(jù)表,就會在extra列看到using temporary字樣. MYSQL應用

轉載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/6402.html