mysql索引總結

標籤：

062 建立索引

　　對於查詢佔主要的應用來說，索引顯得尤為重要。很多時候效能問題很簡單的就是因為我們忘了添加索引而造成的，或者說沒有添加更為有效索引導致。如果不加索引的話，那麼尋找任何哪怕只是一條特定的資料都會進行一次全表掃描，如果一張表的資料量很大而合格結果又很少，那麼不加索引會引起致命的效能下降。但是也不是什麼情 況都非得建索引不可，比如性別可能就只有兩個值，建索引不僅沒什麼優勢，還會影響到更新速度，這被稱為過度索引。

063 複合索引

　　比如有一條語句是這樣的：select * from users where area=’beijing’ and age=22; 
　　如果我們是在area和age上分別建立單個索引的話，由於MySQL查詢每次只能使用一個索引，所以雖然這樣已經相對不做索引時全表掃描提高了很多效率，但是如果在area、age兩列上建立複合索引的話將帶來更高的效率。如果我們建立了(area, age, salary)的複合索引，那麼其實相當於建立了(area,age,salary)、 (area,age)、(area)三個索引，這被稱為最佳左首碼特性。因此我們在建立複合索引時應該將最常用作限制條件的列放在最左邊，依次遞減。

064 索引不會包含有NULL值的列

　　只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL。

065 使用短索引

　　對串列進行索引，如果可能應該指定一個前置長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。

066 排序的索引問題

　　mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

067 like語句操作

　　一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

068 MYSQL資料庫設計資料類型選擇需要注意哪些地方?

　　VARCHAR和CHAR類型，varchar是變長的，需要額外的1-2個位元組儲存，能節約空間，可能會對效能有協助。但由於是變長，可能發生片段，如更新資料； 
　　使用ENUM（MySQL的枚舉類）代替字串類型，資料實際儲存為整型。 
　　字串類型 
　　要儘可能地避免使用字串來做標識符，因為它們佔用了很多空間並且通常比整數類型要慢。特別注意不要在MYISAM表上使用字串標識符。MYISAM預設情況下為字串使用了壓縮索引（Packed Index），這使尋找更為緩慢。據測試，使用了壓縮索引的MYISAM表效能要慢6倍。 
　　還要特別注意完全‘隨機’的字串，例如由MD5（）、SHA1（）、UUID（）產生的。它們產生的每一個新值都會被任意地儲存在很大的空間範圍內，這會減慢INSERT及一些SELECT查詢。1）它們會減慢INSERT查詢，因為插入的值會被隨機地放入索引中。這會導致分頁、隨機磁碟訪問及聚集儲存引擎上的叢集索引片段。2)它們會減慢SELECT查詢，因為邏輯上相鄰的行會分布在磁碟和記憶體中的各個地方。3）隨機值導致緩衝對所有類型的查詢效能都很差，因為它們會使緩衝賴以工作的訪問局部性失效。如果整個資料集都變得同樣“熱”的時候，那麼把特定部分的資料緩衝到記憶體中就沒有任何的優勢了。並且如果工作集不能被裝入記憶體中，緩衝就會進行很多刷寫的工作，並且會導致很多緩衝未命中。 
　　如果儲存UUID值，就應該移除其中的虛線，更好的辦法是使用UHEX（）把UUID值轉化為16位元組的數字，並把它儲存在BINARY（16）列中。

069 不要在列上進行運算

　　select * from users where YEAR(adddate)<2007; 
　　將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成 
　　select * from users where adddate<‘2007-01-01’; 
　　不使用NOT IN和操作 
　　NOT IN和操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替，id != 3則可使用id>3 or id<3來代替。

070 IS NULL 與 IS NOT NULL

　　不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高效能。 
　　任何在where子句中使用is null或is not null的語句最佳化器是不允許使用索引的

075 MySQL添加索引

　　普通索引 添加INDEX 
　　　　ALTER TABLE ‘table_name’ ADD INDEX index_name (‘column’); 
　　主鍵索引 添加PRIMARY KEY 
　　　　ALTER TABLE ‘table_name’ ADD PRIMARY KEY (‘column’); 
　　唯一索引 添加UNIQUE 
　　　　ALTER TABLE ‘table_name’ ADD UNIQUE (‘column’); 
　　全文索引 添加FULLTEXT 
　　　　ALTER TABLE ‘table_name’ ADD FULLTEXT (‘column’); 
　　多列索引 
　　　　ALTER TABLE ‘table_name’ ADD INDEX index_name (‘column1’, ‘column2’, ‘column3’)

076 什麼情況下使用索引？

　　表的主關鍵字 
　　自動建立唯一索引 
　　　　如zl_yhjbqk（使用者基本情況）中的hbs_bh（戶標識編號） 
　　表的欄位唯一約束 
　　　　ORACLE利用索引來保證資料的完整性 
　　　　如lc_hj（流程環節）中的lc_bh+hj_sx（流程編號+環節順序） 
　　直接條件查詢的欄位 
　　在SQL中用於條件約束的欄位 
　　　　如zl_yhjbqk（使用者基本情況）中的qc_bh（區冊編號） 
　　　　select * from zl_yhjbqk where qc_bh=’7001’ 
　　查詢中與其它表關聯的欄位 
　　欄位常常建立了外鍵關係 
　　　　如zl_ydcf（用電成份）中的jldb_bh（計量點表編號） 
　　　　select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’ 
　　查詢中排序的欄位 
　　排序的欄位如果通過索引去訪問那將大大提高排序速度 
　　　　select * from zl_yhjbqk order by qc_bh（建立qc_bh索引） 
　　　　select * from zl_yhjbqk where qc_bh=’7001’ order by cb_sx（建立qc_bh+cb_sx索引，註：只是一個索引，其中包括qc_bh和cb_sx欄位） 
　　查詢中統計或分組統計的欄位 
　　　　select max(hbs_bh) from zl_yhjbqk 
　　　　select qc_bh,count(*) from zl_yhjbqk group by qc_bh

077 什麼情況下應不建或少建索引

　　表記錄太少 
　　如果一個表只有5條記錄，採用索引去訪問記錄的話，那首先需訪問索引表，再通過索引表訪問資料表，一般索引表與資料表不在同一個資料區塊，這種情況下ORACLE至少要往返讀取資料區塊兩次。而不用索引的情況下ORACLE會將所有的資料一次讀出，處理速度顯然會比用索引快。 
　　如表zl_sybm（使用部門）一般只有幾條記錄，除了主關鍵字外對任何一個欄位建索引都不會產生效能最佳化，實際上如果對這個表進行了統計分析後ORACLE也不會用你建的索引，而是自動執行全表訪問。如：select * from zl_sybm where sydw_bh=’5401’（對sydw_bh建立索引不會產生效能最佳化） 
　　經常插入、刪除、修改的表 
　　對一些經常處理的業務表應在查詢允許的情況下盡量減少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等業務表。 
　　資料重複且分布平均的表欄位 
　　假如一個表有10萬行記錄，有一個欄位A只有T和F兩種值，且每個值的分布機率大約為50%，那麼對這種表A欄位建索引一般不會提高資料庫的查詢速度。 
　　經常和主欄位一塊查詢但主欄位索引值比較多的表欄位 
　　如gc_dfss（電費實收）表經常按收費序號、戶標識編號、抄表日期、電費發生年月、操作 標誌來具體查詢某一筆收款的情況，如果將所有的欄位都建在一個索引裡那將會增加資料的修改、插入、刪除時間，從實際上分析一筆收款如果按收費序號索引就已 經將記錄減少到只有幾條，如果再按後面的幾個欄位索引查詢將對效能不產生太大的影 響。

078 千萬級MySQL資料庫建立索引的事項及提高效能的手段

　　1.對查詢進行最佳化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。 
　　2.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：select id from t where num is null可以在num上設定預設值0，確保表中num列沒有null值，然後這樣查詢：select id from t where num=0 
　　3.應盡量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進行全表掃描。 
　　4.應盡量避免在 where 子句中使用or 來串連條件，否則將導致引擎放棄使用索引而進行全表掃描，如：select id from t where num=10 or num=20可以這樣查詢：select id from t where num=10 union all select id from t where num=20 
　　5.in 和 not in 也要慎用，否則會導致全表掃描，如：select id from t where num in(1,2,3) 對於連續的數值，能用 between 就不要用 in 了：select id from t where num between 1 and 3 
　　6.避免使用萬用字元。下面的查詢也將導致全表掃描：select id from t where name like ‘李%’若要提高效率，可以考慮全文檢索索引。 
　　7.如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但最佳化程式不能將訪問計劃的選擇延遲到運行時；它必須在編譯時間進行選擇。然而，如果在編譯時間建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：select id from t where [email protected]可以改為強制查詢使用索引：select id from t with(index(索引名)) where [email protected] 
　　8.應盡量避免在 where 子句中對欄位進行運算式操作，這將導致引擎放棄使用索引而進行全表掃描。如：select id from t where num/2=100應改為:select id from t where num=100*2 
　　9.應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：select id from t where substring(name,1,3)=’abc’ ，name以abc開頭的id應改為:select id from t where name like ‘abc%’ 
　　10.不要在 where 子句中的“=”左邊進行函數、算術運算或其他運算式運算，否則系統將可能無法正確使用索引。 
　　11.在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。 
　　12.不要寫一些沒有意義的查詢，如需要產生一個空表結構：select col1,col2 into #t from t where 1=0 這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：create table #t(…) 
　　13.很多時候用 exists 代替 in 是一個好的選擇：select num from a where num in(select num from b)用下面的語句替換：select num from a where exists(select 1 from b where num=a.num) 
　　14.並不是所有索引對查詢都有效，SQL是根據表中資料來進行查詢最佳化的，當索引列有大量資料重複時，SQL查詢可能不會去利用索引，如一表中有欄位sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。 
　　15.索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了insert 及 update 的 效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要謹慎考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有 必要。 
　　16.應儘可能的避免更新 clustered 索引資料列，因為 clustered 索引資料列的順序就是表記錄的實體儲存體 順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引資料列，那麼需要考慮是否應將該索引建為 clustered 索引。 
　　17.盡量使用數字型欄位，若只含數值資訊的欄位盡量不要設計為字元型，這會降低查詢和串連的效能，並會增加儲存開銷。這是因為引擎在處理查詢和串連時會逐個比較字串中每一個字元，而對於數字型而言只需要比較一次就夠了。 
　　18.儘可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位儲存空間小，可以節省儲存空間，其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。 
　　19.任何地方都不要使用 select * from t ，用具體的欄位列表代替“*”，不要返回用不到的任何欄位。 
　　20.盡量使用表變數來代替暫存資料表。如果表變數包含大量資料，請注意索引非常有限（只有主鍵索引）。 
　　21.避免頻繁建立和刪除暫存資料表，以減少系統資料表資源的消耗。 
　　22.暫存資料表並不是不可使用，適當地使用它們可以使某些常式更有效，例如，當需要重複引用大型表或常用表中的某個資料集時。但是，對於一次性事件，最好使用匯出表。 
　　23.在建立暫存資料表時，如果一次性插入資料量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果資料量不大，為了緩和系統資料表的資源，應先create table，然後insert。 
　　24.如果使用到了暫存資料表，在預存程序的最後務必將所有的暫存資料表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統資料表的較長時間鎖定。 
　　25.盡量避免使用遊標，因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該考慮改寫。 
　　26.使用基於遊標的方法或暫存資料表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。 
　　27.與暫存資料表一樣，遊標並不是不可使用。對小型資料集使用 FAST_FORWARD 遊標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的資料時。在結果集中包括“合計”的常式通常要比使用遊標執行的速度快。如果開發時間允許，基於遊標的方法和基於集的方法都可以嘗試一下，看哪一種方法的效果更好。 
　　28.在所有的預存程序和觸發器的開始處設定 SET NOCOUNT ON ，在結束時設定 SET NOCOUNT OFF。無需在執行預存程序和觸發器的每個語句後向用戶端發送DONE_IN_PROC 訊息。 
　　29.盡量避免大事務操作，提高系統並發能力。 
　　30.盡量避免向用戶端返回大資料量，若資料量過大，應該考慮相應需求是否合理。

079 MySql在建立索引最佳化時需要注意的問題

　　1，建立索引 
　　對於查詢佔主要的應用來說，索引顯得尤為重要。很多時候效能問題很簡單的就是因為我們忘了添加索引而造成的，或者說沒有添加更為有效索引導致。如果不加索引的話，那麼尋找任何哪怕只是一條特定的資料都會進行一次全表掃描，如果一張表的資料量很大而合格結果又很少，那麼不加索引會引起致命的效能下降。但是也不是什麼情況都非得建索引不可，比如性別可能就只有兩個值，建索引不僅沒什麼優勢，還會影響到更新速度，這被稱為過度索引。 
　　2，複合索引 
　　比如有一條語句是這樣的：select * from users where area=’beijing’ and age=22; 
　　如果我們是在area和age上分別建立單個索引的話，由於mysql查詢每次只能使用一個索引，所以雖然這樣已經相對不做索引時全表掃描提高了很多效率，但是如果在area、age兩列上建立複合索引的話將帶來更高的效率。如果我們建立了(area, age,salary)的複合索引，那麼其實相當於建立了(area,age,salary)、(area,age)、(area)三個索引，這被稱為最佳左首碼特性。因此我們在建立複合索引時應該將最常用作限制條件的列放在最左邊，依次遞減。 
　　3，索引不會包含有NULL值的列 
　　只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL。 
　　4，使用短索引 
　　對串列進行索引，如果可能應該指定一個前置長度。例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。 
　　5，排序的索引問題 
　　mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引。 
　　6，like語句操作 
　　一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。 
　　7，不要在列上進行運算 
　　select * from users where YEAR(adddate) 
　　8，不使用NOT IN和操作 
　　NOT IN和操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替，id != 3則可使用id>3 or id < 3

080 資料庫效能下降，想找到哪些sql耗時較長，應該如何操作? my.cnf裡如何配置?

　　1、show processlist; 
　　2、select * from information_schema.processlist ; 
　　3、可以在[mysqld]中添加如下： 
　　　　log =/var/log/mysql.log 
　　　　如果需要監控慢查詢可以添加如下內容： 
　　　　log-slow-queries = /var/log/slowquery.log 
　　　　long_query_time = 1

081 叢集索引

　　術語“聚集”指實際的資料行和相關的鍵值都儲存在一起。每個表只能有一個叢集索引。但是，覆蓋索引可以類比多個叢集索引。儲存引擎負責實現索引，因此不是所有的儲存索引都支援叢集索引。當前，SolidDB和InnoDB是唯一支援叢集索引的儲存引擎。 
　　優點： 
　　可以把相關資料儲存在一起。這樣從磁碟上提取幾個頁面的資料就能把某個使用者的資料全部抓取出來。如果沒有使用聚集，讀取每個資料都會訪問磁碟。 
　　資料訪問快。叢集索引把索引和資料都儲存到了同一棵B-TREE中，因此從叢集索引中取得資料通常比在非叢集索引進行尋找要快。 
　　缺點： 
　　聚集能最大限度地提升I/O密集負載的效能。如果資料能裝入記憶體，那麼其順序也就無所謂了。這樣聚集就沒有什麼用處。 
　　插入速度嚴重依賴於插入順序。更新叢集索引列是昂貴的，因為強制InnoDB把每個更新的行移到新的位置。 
　　建立在叢集索引上的表在插入新行，或者在行的主鍵被更新，該行必須被移動的時候會進行分頁。 
　　聚集表可會比全表掃描慢，尤其在表格儲存體得比較稀疏或因為分頁而沒有順序儲存的時候。 
　　第二（非聚集）索引可能會比預想的大，因為它們的葉子節點包含了被引用行的主鍵列。第二索引訪問需要兩次索引尋找，而不是一次。 InnoDB的第二索引葉子節點包含了主鍵值作為指向行的“指標”，而不是“行指標”。 這種策略減少了在移動行或資料分頁的時候索引的維護工作。使用行的主鍵值作為指標使得索引變得更大，但是這意味著InnoDB可以移動行，而無須更新指標。

082 索引類型

　　索引類型: B-TREE索引，雜湊索引 
　　B-TREE索引(預設的索引類型)加速了資料訪問，因為儲存引擎不會掃描整個表得到需要的資料。相反，它從根節點開始。根節點儲存了指向子節點的指標，並且儲存引擎會根據指標尋找資料。它通過尋找節點頁中的值找到正確的指標，節點頁包含子節點的指標，並且儲存引擎會根據指標尋找資料。它通過尋找節點頁中的值找到正確的指標，節點頁包含子節點中值的上界和下界。最後，儲存引擎可能無法找到需要的資料，也可能成功地找到包含資料的葉子頁面。 
　　例：B-TREE索引 對於以下類型查詢有用。匹配全名、匹配最左首碼、匹配列首碼、匹配範圍值、精確匹配一部分並且匹配某個範圍中的另一部分； 
　　B-TREE索引的局限：如果尋找沒有從索引列的最左邊開始，它就沒什麼用處。不能跳過索引中的列，儲存引擎不能優先訪問任何在第一個範圍條件右邊的列。例：如果查詢是where last_name=’Smith’ AND first_name LIKE ‘J%’ AND dob=’1976-12-23’;訪問就只能使用索引的頭兩列，因為LIKE是範圍條件。 
　　雜湊索引建立在雜湊表的基礎上，它只對使用了索引中的每一列的精確尋找有用。對於每一行，儲存引擎計算出了被索引列的雜湊碼，它是一個較小的值，並且有可能和其他行的雜湊碼不同。它把雜湊碼儲存在索引中，並且儲存了一個指向雜湊表中每一行的指標。
　　因為索引只包含了雜湊碼和行指標，而不是值自身，MYSQL不能使用索引中的值來避免讀取行。 
　　MYSQL不能使用雜湊索引進行排序，因為它們不會按序儲存行。 
　　雜湊索引不支援部分鍵匹配，因為它們是由被索引的全部值計算出來的。也就是說，如果在（A，B）兩列上有索引，並且WHERE子句中只使用了A，那麼索引就不會起作用。 
　　雜湊索引只支援使用了= IN（）和<=>的相等比較。它們不能加快範圍查詢。例如WHERE price > 100; 
　　訪問雜湊索引中的資料非常快，除非碰撞率很高。當發生碰撞的時候，儲存引擎必須訪問鏈表中的每一個行指標，然後逐行進行資料比較，以確定正確的資料。如果有很多碰撞，一些索引維護操作就有可能會變慢。

mysql索引總結