本文實(shí)例總結(jié)了MySQL數(shù)據(jù)庫優(yōu)化技術(shù)的索引用法。分享給大家供大家參考，具體如下：

這里緊接上一篇《MySQL數(shù)據(jù)庫優(yōu)化技術(shù)之配置技巧總結(jié)》，進(jìn)一步分析索引優(yōu)化的技巧：

(七)表的優(yōu)化

1. 選擇合適的數(shù)據(jù)引擎

MyISAM:適用于大量的讀操作的表

InnoDB:適用于大量的寫讀作的表

2.選擇合適的列類型

使用 SELECT * FROM TB_TEST PROCEDURE ANALYSE()可以對(duì)這個(gè)表的每一個(gè)字段進(jìn)行分析，給出優(yōu)化列類型建議

3.對(duì)于不保存NULL值的列使用NOT NULL，這對(duì)你想索引的列尤其重要

4.建立合適的索引

5.使用定長字段,速度比變長要快

(八)建立索引原則

1.合理使用索引

一個(gè)Table在一次query中只能使用一個(gè)索引,使用EXPLAIN語句來檢驗(yàn)優(yōu)化程序的操作情況

使用analyze幫助優(yōu)化程序?qū)λ饕氖褂眯Ч龀龈鼫?zhǔn)確的預(yù)測

2.索引應(yīng)該創(chuàng)建在搜索、排序、歸組等操作所涉及的數(shù)據(jù)列上

3.盡量將索引建立在重復(fù)數(shù)據(jù)少的數(shù)據(jù)列中，唯一所以最好

例如：生日列，可以建立索引，但性別列不要建立索引

4.盡量對(duì)比較短的值進(jìn)行索引

降低磁盤IO操作，索引緩沖區(qū)中可以容納更多的鍵值，提高命中率

如果對(duì)一個(gè)長的字符串建立索引，可以指定一個(gè)前綴長度

5.合理使用多列索引

如果多個(gè)條件經(jīng)常需要組合起來查詢,則要使用多列索引(因?yàn)橐粋€(gè)表一次查詢只能使用一個(gè)索引,建立多個(gè)單列索引也只能使用一個(gè))

6.充分利用最左前綴

也就是要合理安排多列索引中各列的順序，將最常用的排在前面

7.不要建立過多的索引

只有經(jīng)常應(yīng)用于where,order by,group by中的字段需要建立索引.

8.利用慢查詢?nèi)罩静檎页雎樵?log-slow-queries, long_query_time)

(九)充分利用索引

1.盡量比較數(shù)據(jù)類型相同的數(shù)據(jù)列

2.盡可能地讓索引列在比較表達(dá)式中獨(dú)立, WHERE mycol < 4 / 2 使用索引,而WHERE mycol * 2 < 4不使用

3.盡可能不對(duì)查詢字段加函數(shù),

如：WHERE YEAR(date_col) < 1990改造成WHERE date_col < '1990-01-01'

WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff 改造成WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

4.在LIKE模式的開頭不要使用通配符

5.使用straight join可以強(qiáng)制優(yōu)化器按照FROM子句的次序來進(jìn)行聯(lián)結(jié),可以select straight join,強(qiáng)制所有聯(lián)結(jié),也可以select * from a straight join b強(qiáng)制兩個(gè)表的順序.

6.使用force index強(qiáng)制使用指定的索引.如 select * from song_lib force index(song_name) order by song_name比不用force index效率高

7.盡量避免使用MySQL自動(dòng)類型轉(zhuǎn)換,否則將不能使用索引.如將int型的num_col用where num_col='5'

(十)SQL語句的優(yōu)化

1.創(chuàng)建合適的統(tǒng)計(jì)中間結(jié)果表，降低從大表查詢數(shù)據(jù)的幾率

2.盡量避免使用子查詢，而改用連接的方式.例如：

SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id = a.id) AS latest_post
FROM authors a

可以改成：

SELECT a.id, MAX(p.created) AS latest_post
FROM authors AS a
INNER JOIN posts p ON (a.id = p.author_id)
GROUP BY a.id

select song_id from song_lib where singer_id in
(select singer_id from singer_lib
where first_char='A'
) limit 2000

改成:

select song_id from song_lib a
inner join singer_lib b on a.singer_id=b.singer_id and first_char='A' limit 2000

3.插入判斷重復(fù)鍵時(shí),使用ON DUPLICATE KEY UPDATE :

4.避免使用游標(biāo)

游標(biāo)的運(yùn)行效率極低，可以通過增加臨時(shí)表，運(yùn)用多表查詢，多表更新等方式完成任務(wù),不要使用游標(biāo).

(十一)使用Explain分析SQL語句使用索引的情況

當(dāng)你在一條SELECT語句前放上關(guān)鍵詞EXPLAIN，MySQL解釋它將如何處理SELECT，提供有關(guān)表如何聯(lián)結(jié)和以什么次序聯(lián)結(jié)的信息,借助于EXPLAIN，可以知道什么時(shí)候必須為表加入索引以得到一個(gè)使用索引來尋找記錄的更快的SELECT,你也能知道優(yōu)化器是否以一個(gè)最佳次序聯(lián)結(jié)表。為了強(qiáng)制優(yōu)化器對(duì)一個(gè)SELECT語句使用一個(gè)特定聯(lián)結(jié)次序，增加一個(gè)STRAIGHT_JOIN子句。 。

EXPLAIN命令的一般語法是：EXPLAIN <SQL命令> 如：explain select * from a inner join b on a.id=b.id

EXPLAIN的分析結(jié)果參數(shù)詳解:

1.table：這是表的名字。

2.type：連接操作的類型。

system:表中僅有一條記錄（實(shí)際應(yīng)用很少只有一條資料的表)

const:表最多有一個(gè)匹配行,用于用常數(shù)值比較PRIMARY KEY或UNIQUE索引的所有部分時(shí),

如：

select * from song_lib where song_id=2

(song_id為表的primary key)

eq_ref:對(duì)于每個(gè)來自于前面的表的行組合，從該表中用UNIQUE或PRIMARY KEY的索引讀取一行,

如：

ref:對(duì)于每個(gè)來自于前面的表的行組合，從該表中用非UNIQUE或PRIMARY KEY的索引讀取一行

如：

ref_or_null:該聯(lián)接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行,

如：

index_merge:該聯(lián)接類型表示使用了索引合并優(yōu)化方法

Key： 它顯示了MySQL實(shí)際使用的索引的名字。如果它為空（或NULL），則MySQL不使用索引。

key_len： 索引中被使用部分的長度，以字節(jié)計(jì)。

3.ref：ref列顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行

4.rows： MySQL所認(rèn)為的它在找到正確的結(jié)果之前必須掃描的記錄數(shù)。顯然，這里最理想的數(shù)字就是1。

5.Extra：這里可能出現(xiàn)許多不同的選項(xiàng)，其中大多數(shù)將對(duì)查詢產(chǎn)生負(fù)面影響。一般有：

using where：表示使用了where條件

using filesort: 表示使用了文件排序,也就是使用了order by子句，并且沒有用到order by 里字段的索引，從而需要額外的排序開銷，所以如果出現(xiàn)using filesort就表示排序的效率很低，需要進(jìn)行優(yōu)化,比如采用強(qiáng)制索引的方法(force index)

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過程技巧大全》、《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫計(jì)有所幫助。

最新評(píng)論