如何查詢優化100萬條資料的一張表？

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秒，非常棒的速度。

4.mysql存儲引擎 myIsam和innodb的差別

MySQL有多種存儲引擎，MyISAM和InnoDB是其中常用的兩種。這裡介紹關于這兩種引擎的一些基本概念（非深入介紹）。

MyISAM存儲引擎，基于傳統的ISAM類型，支援全文搜尋，但不是事務安全的，而且不支援外鍵。每張MyISAM表存放在三個檔案中：frm 檔案存放表格定義；資料檔案是MYD (MYData)；索引檔案是MYI (MYIndex)。

InnoDB是事務型引擎，支援復原、崩潰恢複能力、多版本并發控制、ACID事務，支援行級鎖定（InnoDB表的行鎖不是絕對的，如果在執行一個SQL語句時MySQL不能确定要掃描的範圍，InnoDB表同樣會鎖全表，如like操作時的SQL語句），以及提供與Oracle類型一緻的不加鎖讀取方式。InnoDB存儲它的表和索引在一個表空間中，表空間可以包含數個檔案。

核心差別

MyISAM是非事務安全型的，而InnoDB是事務安全型的。

MyISAM鎖的粒度是表級，而InnoDB支援行級鎖定。

MyISAM支援全文類型索引，而InnoDB不支援全文索引。

MyISAM相對簡單，是以在效率上要優于InnoDB，小型應用可以考慮使用MyISAM。

MyISAM表是儲存成檔案的形式，在跨平台的資料轉移中使用MyISAM存儲會省去不少的麻煩。

InnoDB表比MyISAM表更安全，可以在保證資料不會丢失的情況下，切換非事務表到事務表（alter table tablename type=innodb）。

應用場景

MyISAM管理非事務表。它提供高速存儲和檢索，以及全文搜尋能力。如果應用中需要執行大量的SELECT查詢，那麼MyISAM是更好的選擇。

InnoDB用于事務處理應用程式，具有衆多特性，包括ACID事務支援。如果應用中需要執行大量的INSERT或UPDATE操作，則應該使用InnoDB，這樣可以提高多使用者并發操作的性能。

Mysql的存儲引擎和索引

資料庫必須有索引，沒有索引則檢索過程變成了順序查找，O(n)的時間複雜度幾乎是不能忍受的。我們非常容易想象出一個隻有單關鍵字組成的表如何使用B+樹進行索引，隻要将關鍵字存儲到樹的節點即可。當資料庫一條記錄裡包含多個字段時，一棵B+樹就隻能存儲主鍵，如果檢索的是非主鍵字段，則主鍵索引失去作用，又變成順序查找了。這時應該在第二個要檢索的列上建立第二套索引。 這個索引由獨立的B+樹來組織。有兩種常見的方法可以解決多個B+樹通路同一套表資料的問題，一種叫做聚簇索引（clustered index ），一種叫做非聚簇索引（secondary index）。這兩個名字雖然都叫做索引，但這并不是一種單獨的索引類型，而是一種資料存儲方式。對于聚簇索引存儲來說，行資料和主鍵B+樹存儲在一起，輔助鍵B+樹隻存儲輔助鍵和主鍵，主鍵和非主鍵B+樹幾乎是兩種類型的樹。對于非聚簇索引存儲來說，主鍵B+樹在葉子節點存儲指向真正資料行的指針，而非主鍵。

InnoDB使用的是聚簇索引，将主鍵組織到一棵B+樹中，而行資料就儲存在葉子節點上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對應的葉節點，之後獲得行資料。若對Name列進行條件搜尋，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節點擷取對應的主鍵。第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作，最終到達葉子節點即可擷取整行資料。

MyISM使用的是非聚簇索引，非聚簇索引的兩棵B+樹看上去沒什麼不同，節點的結構完全一緻隻是存儲的内容不同而已，主鍵索引B+樹的節點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表資料存儲在獨立的地方，這兩顆B+樹的葉子節點都使用一個位址指向真正的表資料，對于表資料來說，這兩個鍵沒有任何差别。由于索引樹是獨立的，通過輔助鍵檢索無需通路主鍵的索引樹。

為了更形象說明這兩種索引的差別，我們假想一個表如下圖存儲了4行資料。其中Id作為主索引，Name作為輔助索引。圖示清晰的顯示了聚簇索引和非聚簇索引的差異。

我們重點關注聚簇索引，看上去聚簇索引的效率明顯要低于非聚簇索引，因為每次使用輔助索引檢索都要經過兩次B+樹查找，這不是多此一舉嗎？聚簇索引的優勢在哪？

1 由于行資料和葉子節點存儲在一起，這樣主鍵和行資料是一起被載入記憶體的，找到葉子節點就可以立刻将行資料傳回了，如果按照主鍵Id來組織資料，獲得資料更快。

2 輔助索引使用主鍵作為"指針" 而不是使用位址值作為指針的好處是，減少了當出現行移動或者資料頁分裂時輔助索引的維護工作，使用主鍵值當作指針會讓輔助索引占用更多的空間，換來的好處是InnoDB在移動行時無須更新輔助索引中的這個"指針"。也就是說行的位置（實作中通過16K的Page來定位，後面會涉及）會随着資料庫裡資料的修改而發生變化（前面的B+樹節點分裂以及Page的分裂），使用聚簇索引就可以保證不管這個主鍵B+樹的節點如何變化，輔助索引樹都不受影響。

是以在百萬級資料及更大資料情況下，mysql innoDB 的索引表現更加優秀！

5、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值。

下面摘自MySQL自己的文檔

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

i.IP位址存成 UNSIGNED INT

很多程式員都會建立一個 VARCHAR(15) 字段來存放字元串形式的IP而不是整形的IP。如果你用整形來存放，隻需要4個位元組，并且你可以有定長的字段。而且，這會為你帶來查詢上的優勢，尤其是當你需要使用這樣的WHERE條件：IP between ip1 and ip2。

我們必需要使用UNSIGNED INT，因為 IP位址會使用整個32位的無符号整形

j.固定長度的表會更快

如果表中的所有字段都是“固定長度”的，整個表會被認為是 “static” 或 “fixed-length”。 例如，表中沒有如下類型的字段： VARCHAR，TEXT，BLOB。隻要你包括了其中一個這些字段，那麼這個表就不是“固定長度靜态表”了，這樣，MySQL 引擎會用另一種方法來處理。

固定長度的表會提高性能，因為MySQL搜尋得會更快一些，因為這些固定的長度是很容易計算下一個資料的偏移量的，是以讀取的自然也會很快。而如果字段不是定長的，那麼，每一次要找下一條的話，需要程式找到主鍵。

并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論你用不用，他都是要配置設定那麼多的空間。

k.垂直分割

“垂直分割”是一種把資料庫中的表按列變成幾張表的方法，這樣可以降低表的複雜度和字段的數目，進而達到優化的目的。需要注意的是，這些被分出去的字段所形成的表，你不會經常性地去Join他們，不然的話，這樣的性能會比不分割時還要差，而且，會是極數級的下降。

l.拆分大的 DELETE 或 INSERT 語句

如果在一個線上的網站上去執行一個大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，别的操作都進不來了。

Apache 會有很多的子程序或線程。是以，其工作起來相當有效率，而我們的伺服器也不希望有太多的子程序，線程和資料庫連結，這是極大的占伺服器資源的事情，尤其是記憶體。

如果你把你的表鎖上一段時間，比如30秒鐘，那麼對于一個有很高通路量的站點來說，這30秒所積累的通路程序/線程，資料庫連結，打開的檔案數，可能不僅僅會讓你泊WEB服務Crash，還可能會讓你的整台伺服器馬上掛了。

m.越小的列會越快

對于大多數的資料庫引擎來說，硬碟操作可能是最重大的瓶頸。是以，把你的資料變得緊湊會對這種情況非常有幫助，因為這減少了對硬碟的通路。

n.選擇正确的存儲引擎

在 MySQL 中有兩個存儲引擎 MyISAM 和 InnoDB，每個引擎都有利有弊。

MyISAM 适合于一些需要大量查詢的應用，但其對于有大量寫操作并不是很好。甚至你隻是需要update一個字段，整個表都會被鎖起來，而别的程序，就算是讀程序都無法操作直到讀操作完成。另外，MyISAM 對于 SELECT COUNT(*) 這類的計算是超快無比的。

InnoDB 的趨勢會是一個非常複雜的存儲引擎，對于一些小的應用，它會比 MyISAM 還慢。他是它支援“行鎖” ，于是在寫操作比較多的時候，會更優秀。并且，他還支援更多的進階應用，比如：事務。