MySQL數據庫優化技術之索引使用技巧總結

2024-07-24 13:10:07

字體：大中小

來源：轉載

供稿：網友

本文實例總結了MySQL數據庫優化技術的索引用法。分享給大家供大家參考，具體如下：

這里緊接上一篇《MySQL數據庫優化技術之配置技巧總結》，進一步分析索引優化的技巧：

(七)表的優化

1. 選擇合適的數據引擎

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

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

2.選擇合適的列類型

使用 SELECT * FROM TB_TEST PROCEDURE ANALYSE()可以對這個表的每一個字段進行分析，給出優化列類型建議

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

4.建立合適的索引

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

(八)建立索引原則

1.合理使用索引

一個Table在一次query中只能使用一個索引,使用EXPLAIN語句來檢驗優化程序的操作情況

使用analyze幫助優化程序對索引的使用效果做出更準確的預測

2.索引應該創建在搜索、排序、歸組等操作所涉及的數據列上

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

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

4.盡量對比較短的值進行索引

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

如果對一個長的字符串建立索引，可以指定一個前綴長度

5.合理使用多列索引

如果多個條件經常需要組合起來查詢,則要使用多列索引(因為一個表一次查詢只能使用一個索引,建立多個單列索引也只能使用一個)

6.充分利用最左前綴

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

7.不要建立過多的索引

只有經常應用于where,order by,group by中的字段需要建立索引.

8.利用慢查詢日志查找出慢查詢(log-slow-queries, long_query_time)

(九)充分利用索引

1.盡量比較數據類型相同的數據列

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

3.盡可能不對查詢字段加函數,

如：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可以強制優化器按照FROM子句的次序來進行聯結,可以select straight join,強制所有聯結,也可以select * from a straight join b強制兩個表的順序.

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

7.盡量避免使用MySQL自動類型轉換,否則將不能使用索引.如將int型的num_col用where num_col='5'

(十)SQL語句的優化

1.創建合適的統計中間結果表，降低從大表查詢數據的幾率

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

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

可以改成：

SELECT a.id, MAX(p.created) AS latest_postFROM authors AS aINNER 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_libwhere first_char='A') limit 2000

改成:

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

3.插入判斷重復鍵時,使用ON DUPLICATE KEY UPDATE :

復制代碼代碼如下:

	insert into db_action.action_today(user_id,song_id,action_count) values(1,1,1) ON DUPLICATE KEY UPDATE action_count=action_count+1;

4.避免使用游標

游標的運行效率極低，可以通過增加臨時表，運用多表查詢，多表更新等方式完成任務,不要使用游標.

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

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

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

EXPLAIN的分析結果參數詳解:

1.table：這是表的名字。

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

system:表中僅有一條記錄（實際應用很少只有一條資料的表)

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

如：