mysql sql語句優化建議

1.select * from table_name where;

建議将*改為需要的列。影響網絡傳輸和記憶體。

2.like語句

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

3.不要在列上進行運算，無法運用索引

select * from users where YEAR(adddate)<2007;      

将在每個行上進行運算，這将導緻索引失效而進行全表掃描，是以我們可以改成

select * from users where adddate<‘2007-01-01’;      

4.不使用NOT IN和<>操作

NOT IN和<>操作都不會使用索引将進行全表掃描。NOT IN可以NOT EXISTS代替，id<>3則可使用id>3 or id<3來代替。

5.limit huge_num,offset

在分頁的時候，當huge_num比較大的時候，這樣分頁有一個很大的性能問題。我用‘limit 1000,20;’去1000-1020行的記錄，會周遊1020行，然後前1000條記錄都會被抛棄。

SELECT * FROM payment ORDER BY rental_id LIMIT 100,10;      

思路一：使用子查詢,按照索引分頁後回表方式改寫sql

SELECT * FROM payment a INNER JOIN (SELECT payment_id FROM payment ORDER BY rental_id LIMIT 100,10)b ON      

思路二：使用between … and…

#不建議，用處不大
SELECT * FROM payment WHERE rental_id BETWEEN 100 AND 110 ORDER BY      

思路三：和開發人員協商，翻頁過程通過增加一個參數last_page_record，來記錄上一頁最後一行排序編号，然後通過該參數範圍查找下一頁的記錄。

6、 當隻要一行資料時使用 LIMIT 1

當你查詢表的有些時候，你已經知道結果隻會有一條結果，但因為你可能需要去fetch遊标，或是你也許會去檢查傳回的記錄數。

在這種情況下，加上 LIMIT 1 可以增加性能。這樣一樣，MySQL資料庫引擎會在找到一條資料後停止搜尋，而不是繼續往後查少下一條符合記錄的資料。

7、通路頻率較低的大字段拆分出資料表。

有些大字段占用空間多，通路頻率較其他字段明顯要少很多，這種情況進行拆分，頻繁的查詢中就不需要讀取大字段，造成IO資源的浪費。

8、資料庫字段類型定義

1、經常需要計算和排序等消耗CPU的字段,應該盡量選擇更為迅速的字段，如用TIMESTAMP(4個位元組，最小值1970-01-01 00:00:00)代替Datetime（8個位元組，最小值1001-01-01 00:00:00）,

2、字元型變長字段使用varchar，不要使用char

3、對于二進制多媒體資料，流水隊列資料(如日志)，超大文本資料不要放在資料庫字段中。

9、索引字段上進行運算會使索引失效

盡量避免在WHERE子句中對字段進行函數或表達式操作，這将導緻引擎放棄使用索引而進行全表掃描。如：

SELECT * FROM T1 WHERE F1/2=100 應改為: SELECT * FROM T1 WHERE F1=100*2      

9、避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等這樣的操作符

因為這會使系統無法使用索引,而隻能直接搜尋表中的資料。例如: SELECT id FROM employee WHERE id != “B%” 優化器将無法通過索引來确定将要命中的行數,是以需要搜尋該表的所有行。在in語句中能用exists語句代替的就用exists.

10、把列設定為NOT NULL

允許NULL的列不僅占用更多磁盤空間，而且會影響查詢分析器對SQL語句的優化，在業務場景允許的情況下應優先設定列為NOT NULL，并賦予預設值如空白字元串、-1等

11、優先把索引設定為唯一

如果已知一個列的内容不會出現重複的行且有必要建立索引，則應建立唯一索引，除了防止誤操作，還可以明确的告知查詢分析器，某些查詢隻要找到一條符合條件的記錄就可以結束掃描了。

12、索引不會包含有NULL值的列

隻要列中包含有NULL值都将不會被包含在索引中，複合索引中隻要有一列含有NULL值，那麼這一列對于此複合索引就是無效的。是以我們在資料庫設計時不要讓字段的預設值為NULL。

13、使用哈希索引

如果在一個很長的字元串列上做精确查找，直接建立索引可能不是最好的辦法，這會導緻占用更多磁盤空間和索引效率的降低，例如這個查詢

select url from myurls where url='      

可以考慮從應用層面上優化，對myurls表增加一個int列hashurl，在插入記錄時通過一定雜湊演算法計算url的哈希值，記入hashurl列，并對該列建立索引

查詢語句修改為：

select url from myurls where hashurl=3346369 and url=      

通過hashurl的索引會過濾掉大部分不符合條件的行數，後面的精确比對解決了哈希沖突問題。

14、避免類型轉換

　　這裡所說的“類型轉換”是指 where 子句中出現 column 字段的類型和傳入的參數類型不一緻的時候發生的類型轉換：

　　人為在column_name 上通過轉換函數進行轉換

　　直接導緻 MySQL(實際上其他資料庫也會有同樣的問題)無法使用索引，如果非要轉換，應該在傳入的參數上進行轉換

　　由資料庫自己進行轉換

　　如果我們傳入的資料類型和字段類型不一緻，同時我們又沒有做任何類型轉換處理，MySQL 可能會自己對我們的資料進行類型轉換操作，也可能不進行處理而交由存儲引擎去處理，這樣一來，就會出現索引無法使用的情況而造成執行計劃問題。

15、盡量用 union all 代替 union

　　union 和 union all 的差異主要是前者需要将兩個(或者多個)結果集合并後再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。是以當我們可以确認不可能出現重複結果集或者不在乎重複結果集的時候，盡量使用 union all 而不是 union。

16、盡量少 or

　　當 where 子句中存在多個條件以“或”并存的時候，MySQL 的優化器并沒有很好的解決其執行計劃優化問題，再加上 MySQL 特有的 SQL 與 Storage 分層架構方式，造成了其性能比較低下，很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果。

17、使用連接配接（JOIN）來代替子查詢(Sub-Queries)

MySQL從4.1開始支援SQL的子查詢。這個技術可以使用SELECT語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。例如，我們要将客戶基本資訊表中沒有任何訂單的客戶删除掉，就可以利用子查詢先從銷售資訊表中将所有發出訂單的客戶ID取出來，然後将結果傳遞給主查詢，如下所示：

DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM      

使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，并且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接配接（JOIN）.. 替代。例如，假設我們要将所有沒有訂單記錄的使用者取出來，可以用下面這個查詢完成：

SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM      

如果使用連接配接（JOIN）.. 來完成這個查詢工作，速度将會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，性能将會更好，查詢如下：

SELECT * FROM customerinfo
    LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID
    WHERE salesinfo.CustomerID IS NULL      

update user_score as us LEFT JOIN t_athlete_score as tas
on us.user_id=tas.pk_id
SET