【Mysql】給你100萬條數據的一張表，你將如何查詢優化？

author：咔咔

wechat：fangkangfk

1.兩種查詢引擎查詢速度（myIsam 引擎 ）

InnoDB 中不保存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行。

MyISAM只要簡單的讀出保存好的行數即可。

注意的是，當count(*)語句包含 where條件時，兩種表的操作有些不同，InnoDB類型的表用count(*)或者count(主鍵)，加上where col 條件。其中col列是表的主鍵之外的其他具有唯一約束索引的列。這樣查詢時速度會很快。就是可以避免全表掃描。

總結：

mysql 在300萬條數據（myisam引擎）情況下使用 count(*) 進行數據總數查詢包含條件（正確設置索引）運行時間正常。對于經常進行讀取的數據我們建議使用myIsam引擎。

2.百萬數據下mysql分頁問題

在開發過程中我們經常會使用分頁，核心技術是使用limit進行數據的讀取，在使用limit進行分頁的測試過程中，得到以下數據：

select * from news order by id desc limit 0,10
耗時0.003秒
select * from news order by id desc limit 10000,10
耗時0.058秒
select * from news order by id desc limit 100000,10 
耗時0.575秒
select * from news order by id desc limit 1000000,10
耗時7.28秒

我們驚訝的發現mysql在數據量大的情況下分頁起點越大查詢速度越慢，100萬條起的查詢速度已經需要7秒鐘。這是一個我們無法接受的數值！

改進方案 1

select * from news 
where id >  (select id from news order by id desc  limit 1000000, 1)
order by id desc 
limit 0,10

查詢時間 0.365秒，提升效率是非常明顯的！！原理是什么呢？？？

我們使用條件對id進行了篩選，在子查詢 (select id from news order by id desc limit 1000000, 1) 中我們只查詢了id這一個字段比起select * 或 select 多個字段 節省了大量的查詢開銷！

改進方案2

適合id連續的系統，速度極快！

select * from news 
where id  between 1000000 and 1000010 
order by id desc

不適合帶有條件的、id不連續的查詢。速度非常快！

3. 百萬數據下mysql條件查詢、分頁查詢的注意事項

接上一節，我們加上查詢條件：

select id from news 
where cate = 1
order by id desc 
limit 500000 ,10 

查詢時間 20 秒

好恐怖的速度！！利用上面方案進行優化：

select * from news
where cate = 1 and id > (select id from news where cate = 1 order by id desc limit 500000,1 ) 
order by id desc 
limit 0,10 

查詢時間 15 秒

優化效果不明顯，條件帶來的影響還是很大！在這樣的情況下無論我們怎么去優化sql語句就無法解決運行效率問題。那么換個思路：建立一個索引表，只記錄文章的id、分類信息，我們將文章內容這個大字段分割出去。

表 news2 [ 文章表 引擎 myisam 字符集 utf-8 ]

-------------------------------------------------

id int 11 主鍵自動增加

cate int 11 索引

在寫入數據時將2張表同步，查詢是則可以使用news2 來進行條件查詢：

select * from news
where cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) 
order by id desc 
limit 0,10

注意條件 id > 后面使用了news2 這張表！

運行時間 1.23秒，我們可以看到運行時間縮減了近20倍！！數據在10萬左右是查詢時間可以保持在0.5秒左右，是一個逐步接近我們能夠容忍的值！

但是1秒對于服務器來說依然是一個不能接受的值！！還有什么可以優化的辦法嗎？？我們嘗試了一個偉大的變化：

將 news2 的存儲引擎改變為innodb，執行結果是驚人的！

select * from news
where cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) 
order by id desc 
limit 0,10

只需要 0.2秒，非常棒的速度。

MySQL性能優化的一些經驗

a.為查詢優化你的查詢

大多數的MySQL服務器都開啟了查詢緩存。這是提高性能最有效的方法之一，而且這是被MySQL的數據庫引擎處理的。當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放到一個緩存中，這樣，后續的相同的查詢就不用操作表而直接訪問緩存結果了。

這里最主要的問題是，對于程序員來說，這個事情是很容易被忽略的。因為，我們某些查詢語句會讓MySQL不使用緩存。

請看下面的示例：

// 查詢緩存不開啟
$r = mysql_query("SELECT username FROM user WHERE     signup_date >= CURDATE()");
// 開啟查詢緩存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面兩條SQL語句的差別就是 CURDATE() ，MySQL的查詢緩存對這個函數不起作用。所以，像 NOW() 和 RAND() 或是其它的諸如此類的SQL函數都不會開啟查詢緩存，因為這些函數的返回是會不定的易變的。所以，你所需要的就是用一個變量來代替MySQL的函數，從而開啟緩存。

b.學會使用EXPLAIN

使用EXPLAIN關鍵字可以讓你知道MySQL是如何處理你的SQL語句的。

select id, title, cate from news where cate = 1

發現查詢緩慢，然后在cate字段上增加索引，則會加快查詢

c.當只要一行數據時使用LIMIT 1

當你查詢表的有些時候只需要一條數據，請使用 limit 1。

d.正確的使用索引

索引并不一定就是給主鍵或是唯一的字段。如果在你的表中，有某個字段你總要會經常用來做搜索、拍下、條件，那么，請為其建立索引吧。

e.不要ORDER BY RAND()

效率很低的一種隨機查詢。

f.避免SELECT *

從數據庫里讀出越多的數據，那么查詢就會變得越慢。并且，如果你的數據庫服務器和WEB服務器是兩臺獨立的服務器的話，這還會增加網絡傳輸的負載。必須應該養成一個需要什么就取什么的好的習慣。

g.使用 ENUM 而不是 VARCHAR

ENUM 類型是非常快和緊湊的。在實際上，其保存的是 TINYINT，但其外表上顯示為字符串。這樣一來，用這個字段來做一些選項列表變得相當的完美。

如果你有一個字段，比如“性別”，“國家”，“民族”，“狀態”或“部門”，你知道這些字段的取值是有限而且固定的，那么，你應該使用 ENUM 而不是 VARCHAR。

h.使用 NOT NULL

除非你有一個很特別的原因去使用 NULL 值，你應該總是讓你的字段保持 NOT NULL。這看起來好像有點爭議，請往下看。

首先，問問你自己“Empty”和“NULL”有多大的區別（如果是INT，那就是0和NULL）？如果你覺得它們之間沒有什么區別，那么你就不要使用NULL。（你知道嗎？在 Oracle 里，NULL 和 Empty 的字符串是一樣的！)

不要以為 NULL 不需要空間，其需要額外的空間，并且，在你進行比較的時候，你的程序會更復雜。 當然，這里并不是說你就不能使用NULL了，現實情況是很復雜的，依然會有些情況下，你需要使用NULL值。