MySQL效能優化實戰

zxiaofan發表於2016-02-05
MySql優化
一、MySQL優化原則：
where子句哪些操作將導致MySQL引擎放棄索引而進行全表掃描？
  • 對欄位進行where num is null判斷。【給num設定預設值0】
  • 使用!=或<>。
  • 使用or連線條件，如：where num=10 or num=20【select……union
    all select……替代】
  • 使用in或not in，如：where num in(1,2,3)【連續數值可用where num between 1 and 3替代】
  • like‘李%’【考慮全文檢索】
  • 使用引數

1、【只要一行資料】或【只需要確認是否包含該資料】： LIMIT 1
        查詢時，你已經知道結果只會有一條，但因為可能需要去fetch遊標，或是你會去檢查返回的記錄數。在這種情況下，加上 LIMIT 1 可以增加效能。這樣，MySQL資料庫引擎會在找到一條資料後停止搜尋，而不是繼續往後查少下一條符合記錄的資料。
測試資料：
    MySQL自帶資料庫：sakila庫rental表（資料量16W+），待查詢資料在10467行。

SELECT * FROM rental WHERE rental_date =`2005-08-01
09:45:58` # 耗時0.018s

# LIMIT 1 # 耗時0.002s

;

2、為搜尋欄位建索引

        索引也有消耗，性別無需索引。
3、避免select * 
        讀出資料越多，查詢就越慢。如果資料庫和web伺服器獨立，還會增加網路傳輸的負載。請求所有列再丟掉不需要的列？減少表結構的影響。

4、不要 ORDER BY RAND() 
    【隨機挑選資料】【打亂返回資料】
看著很方便，但會讓你的資料庫效能指數級下降（MySQL不得不執行RAND()函式，耗CPU時間）。

SELECT rental_id FROM rental ORDER BY RAND() LIMIT 1;

這條要求似乎是針對就資料庫，在MySQL 5.7.10測試時，僅第一次耗時達到幾百毫秒，隨後都是幾毫秒。


5、用 ENUM 而不是 VARCHAR 
        ENUM 型別是非常快和緊湊的。在實際上，其儲存的是 TINYINT，但其外表上顯示為字串。如果你有一個欄位，比如“性別”，“國家”，“民族”，“狀態”或“部門”，你知道這些欄位的取值是有限而且固定的，那麼，你應該使用
ENUM 而不是 VARCHAR。
測試資料：
    MySQL自帶資料庫：world庫country表（暫略）

6、儘可能使用not null
①null是空值嗎？不是，空值不佔用空間，而資料庫裡的null佔用空間，資料庫裡任何數跟NULL進行運算都是NULL, 判斷值是否等於NULL，不能簡單用=，而要用IS NULL關鍵字。

7、快速刪除表
    先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

8、拒絕大SQL
        一條SQL叧能在一個CPU運算，5000+ QPS（每秒查詢率Query Per Second）的高幵發中，1秒大SQL可能一條大SQL就把整個資料庫堵死。拆解成多條簡單SQL，簡單SQL快取命中率更高，減少鎖表時間，特別是MyISAM，用上多CPU。

9、範圍查詢
同一欄位：in代替or
or效率：O(n)；in效率：O(Log n)，建議n小於200。

select*FROM rental WHERE return_date=`2005-08-04
10:49:24`or return_date=`2005-08-08
06:46:39`;  # 8ms

select*FROM rental WHERE return_date IN(`2005-08-04
10:49:24`,`2005-08-08 06:46:39`);  #
7ms

不同欄位：union代替or

select*FROM rental WHERE return_date=`2005-08-04
10:49:24` or inventory_id=3157; 

select*FROM rental WHERE return_date=`2005-08-04
10:49:24` UNION SELECT * FROM rental WHERE inventory_id=3157; 


10、limit高效分頁
Select * from table limit 10000,10;
改為：
Select * from table WHERE id>=23423 limit 11; #10+1 (每頁10條) 

11、union all而不是union
        若無需對結果進行去重，則用UNION ALL，UNION 要去重，有開銷。【此處效果不是很明顯，或許資料量不夠16W+】

Select SQL_NO_CACHE *from study.rentalUNION SELECT * FROM sakila.rental; #0.74s

Select SQL_NO_CACHE *from study.rental UNION ALL SELECT * FROM sakila.rental; #0.72s

12、同型別比較

        數字比數字，字元比字元。
數值比字元：同時轉為雙精度再比對；字元比數值：字元整列轉數值，無法使用索引。



相關文章