提高mysql千萬級大數據SQL查詢優化幾條經驗1

本文主要內容：

1：查詢語句where 子句使用時候優化或者需要注意的

2：like語句使用時候需要注意

3：in語句代替語句

4：索引使用或是創建需要注意

假設用戶表有一百萬用戶量。也就是1000000.num是主鍵

1：對查詢進行優化，應盡量避免全表掃描，首先應考慮在where及order by 涉及的列上創建索引。

因為：索引對查詢的速度有著至關重要的影響。

2：盡量避免在where字句中對欄位進行null值的判斷。否則將會導致引擎放棄使用索引而進行全表掃描。

例如：select id from user where num is null 。可以將num是這個欄位設置默認值0.確保表中沒有null值，然後在進行查詢。

sql如下：select id from user where num=0;

（考慮如下情況，假設資料庫中一個表有10^6條記錄，DBMS的頁面大小為4K，並存儲100條記錄。如果沒有索引，查詢將對整個表進行掃描，最壞的情況下，如果所有數據頁都不在內存，需要讀取10^4個頁面，如果這10^4個頁面在磁碟上隨機分布，需要進行10^4次I/O，假設磁碟每次I/O時間為10ms(忽略數據傳輸時間)，則總共需要100s(但實際上要好很多很多)。如果對之建立B-Tree索引，則只需要進行log100(10^6)=3次頁面讀取，最壞情況下耗時30ms。這就是索引帶來的效果，很多時候，當你的應用程序進行SQL查詢速度很慢時，應該想想是否可以建索引）

3：應盡量避免在where子句中使用!=或者是操作符號。否則引擎將放棄使用索引，進而進行全表掃描。

4：應盡量避免在where子句中使用or來連接條件，否則導致放棄使用索引而進行全表掃描。可以使用 union 或者是 union all代替。

例如： select id from user where num =10 or num =20 這個語句景導致引擎放棄num索引，而要全表掃描來進行處理的。

可以使用union 或者是 union all來代替。如下：

select id from user where num = 10;

union all

select id from user where num =20;

(union 和 nuion all 的區別這裡就不贅述了)

5：in 和 not in 也要慎用，否則將會導致全表掃描。

in 對於連續的數組，可以使用between ...and.來代替。

例如：

select id from user where num in (1,2,3);

像這樣連續的就可以使用between ...and...來代替了。如下：

select id from user where num between 1 and 3;

6：like使用需注意

下面這個查詢也將導致全表查詢：

select id from user where name like "%三"；

如果想提高效率，可以考慮到全文檢索。比如solr或是luncene

而下面這個查詢卻使用到了索引：

select id from user where name like "張%"；

7：where子句參數使用時候需注意

如果在where子句中使用參數，也會導致全表掃描。因為sql只會在運行時才會解析局部變數。但優化程序不能將訪問計劃的選擇推遲到運行時；必須在編譯時候進行選擇。然而，如果在編譯時建立訪問計劃，變數的值還是未知大，因而無法作為索引選擇輸入項。

如下面的語句將會進行全表掃描：

select id from user where num = @num

進行優化，我們知道num就是主鍵。是索引。

所以可以改為強制查詢使用索引：

select id from user where (index(索引名稱)) where num = @num;

8：盡量避免在where子句中對欄位進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。

例如：select id from user where num/2=100

應修改為：

select id from user where num = 100*2;

9：盡量避免愛where子句中對欄位進行函數操作，這將導致引擎放棄索引，而進行全表掃描。

例如：

select id from user substring(name,1,3) = "abc" ，這句sql的含義其實就是，查詢name以abc開頭的用戶id

(註：substring(欄位，start,end)這個是mysql的截取函數)

應修改為：

select id from user where name like "abc%";

10：不要在where子句中的"="左邊進行函數、算術運算或是使用其他表達式運算，否則系統可能無法正確使用索引

11：複合索引查詢注意

在使用索引欄位作為條件時候，如果該索引是複合索引，那麼必須使用該索引中的第一個欄位作為條件時候才能保證系統使用該所以，否則該索引將不會被使用，並且應儘可能的讓欄位順序和索引順序一致。

12：不要寫一些沒意義的查詢。

例如：需要生成一個空表結構和user表結構一樣(註：生成的新 new table的表結構和 老表 old table 結構一致)

select col1,col2,col3.....into newTable from user where 1=0

上面這行sql執行後不會返回任何的結果集，但是會消耗系統資源的。

應修改為：

create table newTable (....)這種語句。

13：很多時候用exists 代替 in是一個很好的選擇。

比如：

select num from user where num in(select num from newTable);

可以使用下面語句代替：

select num from user a where exists(select num from newTable b where b.num = a.num );

14：並不是所有索引對查詢都有效，sql是根據表中數據進行查詢優化的，當索引lie(索引欄位)有大量重複數據的時候，sql查詢可能不會去利用索引。如一表中欄位 sex、male、female 幾乎各一半。那麼即使在sex上創建了索引對查詢效率也起不了多大作用。

15：索引創建需注意

並非索引創建越多越好。索引固然可以提高相應的查詢效率，但是同樣會降低insert以及update的效率。因為在insert或是update的時候有可能會重建索引或是修改索引。所以索引怎樣創建需要慎重考慮，視情況而定。一個表中所以數量最好不要超過6個。若太多，則需要考慮一些不常用的列上創建索引是否有必要。

歡迎關注公眾號：凱哥Java

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！