MySQL性能優化實戰 提高mysql千萬級大資料SQL查詢優化30條經驗（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

10:49:24' or inventory_id=3157; 

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、同類型比較

        數字比數字，字元比字元。

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

參考資料：