(七)表的最佳化
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_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.插入判斷重複鍵時,使用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索引的所有部分時,
如：select * from song_lib where song_id=2(song_id為表的primary key)
eq_ref:對於每個來自於前面的表的行組合，從該表中用UNIQUE或PRIMARY KEY的索引讀取一行,
如：select * from song_lib a inner join singer_lib b on a.singer_id=b.singer_id（b的type值為eq_ref)
ref:對於每個來自於前面的表的行組合，從該表中用非UNIQUE或PRIMARY KEY的索引讀取一行
如：select * from song_lib a inner join singer_lib b on a.singer_name=b.singer_name和
select * from singer_lib b where singer_name=‘ccc’ （b的type值為ref,因為b.singer_name是普通索引)
ref_or_null:該聯結類型如同ref，但是添加了MySQL可以專門搜尋包含NULL值的行,
如：select * from singer_lib where singer_name=‘ccc’ or singer_name is null
index_merge:該聯結類型表示使用了索引合并最佳化方法
Key： 它顯示了MySQL實際使用的索引的名字。如果它為空白（或NULL），則MySQL不使用索引。
key_len： 索引中被使用部分的長度，以位元組計。 3.ref：ref列顯示使用哪個列或常數與key一起從表中選擇行
4.rows： MySQL所認為的它在找到正確的結果之前必須掃描的記錄數。顯然，這裡最理想的數字就是1。
5.Extra：這裡可能出現許多不同的選項，其中大多數將對查詢產生負面影響。一般有：
using where：表示使用了where條件
using filesort: 表示使用了檔案排序,也就是使用了order by子句，並且沒有用到order by 裡欄位的索引，從而需要
額外的排序開銷，所以如果出現using filesort就表示排序的效率很低，需要進行最佳化,比如採用強制索引
的方法(force index)
|
