mysql查詢優(yōu)化之100萬(wàn)條數(shù)據(jù)的一張表優(yōu)化方案

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

InnoDB 中不保存表的具體行數(shù)，也就是說(shuō)，執(zhí)行select count(*) from table時(shí)，InnoDB要掃描一遍整個(gè)表來(lái)計(jì)算有多少行。

MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。

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

總結(jié)：

mysql 在300萬(wàn)條數(shù)據(jù)（myisam引擎）情況下使用 count(*) 進(jìn)行數(shù)據(jù)總數(shù)查詢包含條件（正確設(shè)置索引）運(yùn)行時(shí)間正常。對(duì)于經(jīng)常進(jìn)行讀取的數(shù)據(jù)我們建議使用myIsam引擎。

2.百萬(wàn)數(shù)據(jù)下mysql分頁(yè)問(wèn)題

在開(kāi)發(fā)過(guò)程中我們經(jīng)常會(huì)使用分頁(yè)，核心技術(shù)是使用limit進(jìn)行數(shù)據(jù)的讀取，在使用limit進(jìn)行分頁(yè)的測(cè)試過(guò)程中，得到以下數(shù)據(jù)：

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

我們驚訝的發(fā)現(xiàn)mysql在數(shù)據(jù)量大的情況下分頁(yè)起點(diǎn)越大查詢速度越慢，100萬(wàn)條起的查詢速度已經(jīng)需要7秒鐘。這是一個(gè)我們無(wú)法接受的數(shù)值！

改進(jìn)方案 1

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

查詢時(shí)間 0.365秒，提升效率是非常明顯的?。≡硎鞘裁茨?？？？

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

改進(jìn)方案2

適合id連續(xù)的系統(tǒng)，速度極快！

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

不適合帶有條件的、id不連續(xù)的查詢。速度非?？欤?/p>

3. 百萬(wàn)數(shù)據(jù)下mysql條件查詢、分頁(yè)查詢的注意事項(xiàng)

接上一節(jié)，我們加上查詢條件：

select id from news 
where cate = 1
order by id desc 
limit 500000 ,10 
查詢時(shí)間 20 秒

好恐怖的速度??！利用第一節(jié)知識(shí)進(jìn)行優(yōu)化：

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 
查詢時(shí)間 15 秒

優(yōu)化效果不明顯，條件帶來(lái)的影響還是很大！在這樣的情況下無(wú)論我們?cè)趺慈?yōu)化sql語(yǔ)句就無(wú)法解決運(yùn)行效率問(wèn)題。那么換個(gè)思路：建立一個(gè)索引表，只記錄文章的id、分類信息，我們將文章內(nèi)容這個(gè)大字段分割出去。

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

id int 11 主鍵自動(dòng)增加

cate int 11 索引

在寫入數(shù)據(jù)時(shí)將2張表同步，查詢是則可以使用news2 來(lái)進(jìn)行條件查詢：

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 這張表！

運(yùn)行時(shí)間 1.23秒，我們可以看到運(yùn)行時(shí)間縮減了近20倍?。?shù)據(jù)在10萬(wàn)左右是查詢時(shí)間可以保持在0.5秒左右，是一個(gè)逐步接近我們能夠容忍的值！

但是1秒對(duì)于服務(wù)器來(lái)說(shuō)依然是一個(gè)不能接受的值?。∵€有什么可以優(yōu)化的辦法嗎？？我們嘗試了一個(gè)偉大的變化：

將 news2 的存儲(chǔ)引擎改變?yōu)閕nnodb，執(zhí)行結(jié)果是驚人的！

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存儲(chǔ)引擎 myIsam和innodb的區(qū)別

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

MyISAM存儲(chǔ)引擎，基于傳統(tǒng)的ISAM類型，支持全文搜索，但不是事務(wù)安全的，而且不支持外鍵。每張MyISAM表存放在三個(gè)文件中：frm 文件存放表格定義；數(shù)據(jù)文件是MYD (MYData)；索引文件是MYI (MYIndex)。

InnoDB是事務(wù)型引擎，支持回滾、崩潰恢復(fù)能力、多版本并發(fā)控制、ACID事務(wù)，支持行級(jí)鎖定（InnoDB表的行鎖不是絕對(duì)的，如果在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表，如like操作時(shí)的SQL語(yǔ)句），以及提供與Oracle類型一致的不加鎖讀取方式。InnoDB存儲(chǔ)它的表和索引在一個(gè)表空間中，表空間可以包含數(shù)個(gè)文件。

核心區(qū)別

MyISAM是非事務(wù)安全型的，而InnoDB是事務(wù)安全型的。

MyISAM鎖的粒度是表級(jí)，而InnoDB支持行級(jí)鎖定。

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

MyISAM相對(duì)簡(jiǎn)單，所以在效率上要優(yōu)于InnoDB，小型應(yīng)用可以考慮使用MyISAM。

MyISAM表是保存成文件的形式，在跨平臺(tái)的數(shù)據(jù)轉(zhuǎn)移中使用MyISAM存儲(chǔ)會(huì)省去不少的麻煩。

InnoDB表比MyISAM表更安全，可以在保證數(shù)據(jù)不會(huì)丟失的情況下，切換非事務(wù)表到事務(wù)表（alter table tablename type=innodb）。

應(yīng)用場(chǎng)景

MyISAM管理非事務(wù)表。它提供高速存儲(chǔ)和檢索，以及全文搜索能力。如果應(yīng)用中需要執(zhí)行大量的SELECT查詢，那么MyISAM是更好的選擇。

InnoDB用于事務(wù)處理應(yīng)用程序，具有眾多特性，包括ACID事務(wù)支持。如果應(yīng)用中需要執(zhí)行大量的INSERT或UPDATE操作，則應(yīng)該使用InnoDB，這樣可以提高多用戶并發(fā)操作的性能。

Mysql的存儲(chǔ)引擎和索引

數(shù)據(jù)庫(kù)必須有索引，沒(méi)有索引則檢索過(guò)程變成了順序查找，O(n)的時(shí)間復(fù)雜度幾乎是不能忍受的。我們非常容易想象出一個(gè)只有單關(guān)鍵字組成的表如何使用B+樹進(jìn)行索引，只要將關(guān)鍵字存儲(chǔ)到樹的節(jié)點(diǎn)即可。當(dāng)數(shù)據(jù)庫(kù)一條記錄里包含多個(gè)字段時(shí)，一棵B+樹就只能存儲(chǔ)主鍵，如果檢索的是非主鍵字段，則主鍵索引失去作用，又變成順序查找了。這時(shí)應(yīng)該在第二個(gè)要檢索的列上建立第二套索引。 這個(gè)索引由獨(dú)立的B+樹來(lái)組織。有兩種常見(jiàn)的方法可以解決多個(gè)B+樹訪問(wèn)同一套表數(shù)據(jù)的問(wèn)題，一種叫做聚簇索引（clustered index ），一種叫做非聚簇索引（secondary index）。這兩個(gè)名字雖然都叫做索引，但這并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式。對(duì)于聚簇索引存儲(chǔ)來(lái)說(shuō)，行數(shù)據(jù)和主鍵B+樹存儲(chǔ)在一起，輔助鍵B+樹只存儲(chǔ)輔助鍵和主鍵，主鍵和非主鍵B+樹幾乎是兩種類型的樹。對(duì)于非聚簇索引存儲(chǔ)來(lái)說(shuō)，主鍵B+樹在葉子節(jié)點(diǎn)存儲(chǔ)指向真正數(shù)據(jù)行的指針，而非主鍵。

InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹中，而行數(shù)據(jù)就儲(chǔ)存在葉子節(jié)點(diǎn)上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對(duì)應(yīng)的葉節(jié)點(diǎn)，之后獲得行數(shù)據(jù)。若對(duì)Name列進(jìn)行條件搜索，則需要兩個(gè)步驟：第一步在輔助索引B+樹中檢索Name，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵。第二步使用主鍵在主索引B+樹種再執(zhí)行一次B+樹檢索操作，最終到達(dá)葉子節(jié)點(diǎn)即可獲取整行數(shù)據(jù)。

MyISM使用的是非聚簇索引，非聚簇索引的兩棵B+樹看上去沒(méi)什么不同，節(jié)點(diǎn)的結(jié)構(gòu)完全一致只是存儲(chǔ)的內(nèi)容不同而已，主鍵索引B+樹的節(jié)點(diǎn)存儲(chǔ)了主鍵，輔助鍵索引B+樹存儲(chǔ)了輔助鍵。表數(shù)據(jù)存儲(chǔ)在獨(dú)立的地方，這兩顆B+樹的葉子節(jié)點(diǎn)都使用一個(gè)地址指向真正的表數(shù)據(jù)，對(duì)于表數(shù)據(jù)來(lái)說(shuō)，這兩個(gè)鍵沒(méi)有任何差別。由于索引樹是獨(dú)立的，通過(guò)輔助鍵檢索無(wú)需訪問(wèn)主鍵的索引樹。

為了更形象說(shuō)明這兩種索引的區(qū)別，我們假想一個(gè)表如下圖存儲(chǔ)了4行數(shù)據(jù)。其中Id作為主索引，Name作為輔助索引。圖示清晰的顯示了聚簇索引和非聚簇索引的差異。

我們重點(diǎn)關(guān)注聚簇索引，看上去聚簇索引的效率明顯要低于非聚簇索引，因?yàn)槊看问褂幂o助索引檢索都要經(jīng)過(guò)兩次B+樹查找，這不是多此一舉嗎？聚簇索引的優(yōu)勢(shì)在哪？

1 由于行數(shù)據(jù)和葉子節(jié)點(diǎn)存儲(chǔ)在一起，這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的，找到葉子節(jié)點(diǎn)就可以立刻將行數(shù)據(jù)返回了，如果按照主鍵Id來(lái)組織數(shù)據(jù)，獲得數(shù)據(jù)更快。

2 輔助索引使用主鍵作為"指針" 而不是使用地址值作為指針的好處是，減少了當(dāng)出現(xiàn)行移動(dòng)或者數(shù)據(jù)頁(yè)分裂時(shí)輔助索引的維護(hù)工作，使用主鍵值當(dāng)作指針會(huì)讓輔助索引占用更多的空間，換來(lái)的好處是InnoDB在移動(dòng)行時(shí)無(wú)須更新輔助索引中的這個(gè)"指針"。也就是說(shuō)行的位置（實(shí)現(xiàn)中通過(guò)16K的Page來(lái)定位，后面會(huì)涉及）會(huì)隨著數(shù)據(jù)庫(kù)里數(shù)據(jù)的修改而發(fā)生變化（前面的B+樹節(jié)點(diǎn)分裂以及Page的分裂），使用聚簇索引就可以保證不管這個(gè)主鍵B+樹的節(jié)點(diǎn)如何變化，輔助索引樹都不受影響。

所以在百萬(wàn)級(jí)數(shù)據(jù)及更大數(shù)據(jù)情況下，mysql innoDB 的索引表現(xiàn)更加優(yōu)秀！

5、MySQL性能優(yōu)化的一些經(jīng)驗(yàn)

a.為查詢優(yōu)化你的查詢

大多數(shù)的MySQL服務(wù)器都開(kāi)啟了查詢緩存。這是提高性能最有效的方法之一，而且這是被MySQL的數(shù)據(jù)庫(kù)引擎處理的。當(dāng)有很多相同的查詢被執(zhí)行了多次的時(shí)候，這些查詢結(jié)果會(huì)被放到一個(gè)緩存中，這樣，后續(xù)的相同的查詢就不用操作表而直接訪問(wèn)緩存結(jié)果了。

這里最主要的問(wèn)題是，對(duì)于程序員來(lái)說(shuō)，這個(gè)事情是很容易被忽略的。因?yàn)?，我們某些查詢語(yǔ)句會(huì)讓MySQL不使用緩存。

請(qǐng)看下面的示例：

// 查詢緩存不開(kāi)啟

$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

// 開(kāi)啟查詢緩存

$today = date("Y-m-d");

$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面兩條SQL語(yǔ)句的差別就是 CURDATE() ，MySQL的查詢緩存對(duì)這個(gè)函數(shù)不起作用。所以，像 NOW() 和 RAND() 或是其它的諸如此類的SQL函數(shù)都不會(huì)開(kāi)啟查詢緩存，因?yàn)檫@些函數(shù)的返回是會(huì)不定的易變的。所以，你所需要的就是用一個(gè)變量來(lái)代替MySQL的函數(shù)，從而開(kāi)啟緩存。

b.學(xué)會(huì)使用EXPLAIN

使用EXPLAIN關(guān)鍵字可以讓你知道MySQL是如何處理你的SQL語(yǔ)句的。

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

發(fā)現(xiàn)查詢緩慢，然后在cate字段上增加索引，則會(huì)加快查詢

c.當(dāng)只要一行數(shù)據(jù)時(shí)使用LIMIT 1

當(dāng)你查詢表的有些時(shí)候只需要一條數(shù)據(jù)，請(qǐng)使用 limit 1。

d.正確的使用索引

索引并不一定就是給主鍵或是唯一的字段。如果在你的表中，有某個(gè)字段你總要會(huì)經(jīng)常用來(lái)做搜索、拍下、條件，那么，請(qǐng)為其建立索引吧。

e.不要ORDER BY RAND()

效率很低的一種隨機(jī)查詢。

f.避免SELECT *

從數(shù)據(jù)庫(kù)里讀出越多的數(shù)據(jù)，那么查詢就會(huì)變得越慢。并且，如果你的數(shù)據(jù)庫(kù)服務(wù)器和WEB服務(wù)器是兩臺(tái)獨(dú)立的服務(wù)器的話，這還會(huì)增加網(wǎng)絡(luò)傳輸?shù)呢?fù)載。必須應(yīng)該養(yǎng)成一個(gè)需要什么就取什么的好的習(xí)慣。

g.使用 ENUM 而不是 VARCHAR

ENUM 類型是非常快和緊湊的。在實(shí)際上，其保存的是 TINYINT，但其外表上顯示為字符串。這樣一來(lái)，用這個(gè)字段來(lái)做一些選項(xiàng)列表變得相當(dāng)?shù)耐昝馈?/p>

如果你有一個(gè)字段，比如“性別”，“國(guó)家”，“民族”，“狀態(tài)”或“部門”，你知道這些字段的取值是有限而且固定的，那么，你應(yīng)該使用 ENUM 而不是 VARCHAR。

h.使用 NOT NULL

除非你有一個(gè)很特別的原因去使用 NULL 值，你應(yīng)該總是讓你的字段保持 NOT NULL。這看起來(lái)好像有點(diǎn)爭(zhēng)議，請(qǐng)往下看。

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

不要以為 NULL 不需要空間，其需要額外的空間，并且，在你進(jìn)行比較的時(shí)候，你的程序會(huì)更復(fù)雜。 當(dāng)然，這里并不是說(shuō)你就不能使用NULL了，現(xiàn)實(shí)情況是很復(fù)雜的，依然會(huì)有些情況下，你需要使用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

很多程序員都會(huì)創(chuàng)建一個(gè) VARCHAR(15) 字段來(lái)存放字符串形式的IP而不是整形的IP。如果你用整形來(lái)存放，只需要4個(gè)字節(jié)，并且你可以有定長(zhǎng)的字段。而且，這會(huì)為你帶來(lái)查詢上的優(yōu)勢(shì)，尤其是當(dāng)你需要使用這樣的WHERE條件：IP between ip1 and ip2。

我們必需要使用UNSIGNED INT，因?yàn)?IP地址會(huì)使用整個(gè)32位的無(wú)符號(hào)整形

j.固定長(zhǎng)度的表會(huì)更快

如果表中的所有字段都是“固定長(zhǎng)度”的，整個(gè)表會(huì)被認(rèn)為是 “static” 或 “fixed-length”。 例如，表中沒(méi)有如下類型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一個(gè)這些字段，那么這個(gè)表就不是“固定長(zhǎng)度靜態(tài)表”了，這樣，MySQL 引擎會(huì)用另一種方法來(lái)處理。

固定長(zhǎng)度的表會(huì)提高性能，因?yàn)镸ySQL搜尋得會(huì)更快一些，因?yàn)檫@些固定的長(zhǎng)度是很容易計(jì)算下一個(gè)數(shù)據(jù)的偏移量的，所以讀取的自然也會(huì)很快。而如果字段不是定長(zhǎng)的，那么，每一次要找下一條的話，需要程序找到主鍵。

并且，固定長(zhǎng)度的表也更容易被緩存和重建。不過(guò)，唯一的副作用是，固定長(zhǎng)度的字段會(huì)浪費(fèi)一些空間，因?yàn)槎ㄩL(zhǎng)的字段無(wú)論你用不用，他都是要分配那么多的空間。

k.垂直分割

“垂直分割”是一種把數(shù)據(jù)庫(kù)中的表按列變成幾張表的方法，這樣可以降低表的復(fù)雜度和字段的數(shù)目，從而達(dá)到優(yōu)化的目的。需要注意的是，這些被分出去的字段所形成的表，你不會(huì)經(jīng)常性地去Join他們，不然的話，這樣的性能會(huì)比不分割時(shí)還要差，而且，會(huì)是極數(shù)級(jí)的下降。

l.拆分大的 DELETE 或 INSERT 語(yǔ)句

如果在一個(gè)在線的網(wǎng)站上去執(zhí)行一個(gè)大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個(gè)網(wǎng)站停止相應(yīng)。因?yàn)檫@兩個(gè)操作是會(huì)鎖表的，表一鎖住了，別的操作都進(jìn)不來(lái)了。

Apache 會(huì)有很多的子進(jìn)程或線程。所以，其工作起來(lái)相當(dāng)有效率，而我們的服務(wù)器也不希望有太多的子進(jìn)程，線程和數(shù)據(jù)庫(kù)鏈接，這是極大的占服務(wù)器資源的事情，尤其是內(nèi)存。

如果你把你的表鎖上一段時(shí)間，比如30秒鐘，那么對(duì)于一個(gè)有很高訪問(wèn)量的站點(diǎn)來(lái)說(shuō)，這30秒所積累的訪問(wèn)進(jìn)程/線程，數(shù)據(jù)庫(kù)鏈接，打開(kāi)的文件數(shù)，可能不僅僅會(huì)讓你泊WEB服務(wù)Crash，還可能會(huì)讓你的整臺(tái)服務(wù)器馬上掛了。

m.越小的列會(huì)越快

對(duì)于大多數(shù)的數(shù)據(jù)庫(kù)引擎來(lái)說(shuō)，硬盤操作可能是最重大的瓶頸。所以，把你的數(shù)據(jù)變得緊湊會(huì)對(duì)這種情況非常有幫助，因?yàn)檫@減少了對(duì)硬盤的訪問(wèn)。

n.選擇正確的存儲(chǔ)引擎

在 MySQL 中有兩個(gè)存儲(chǔ)引擎 MyISAM 和 InnoDB，每個(gè)引擎都有利有弊。

MyISAM 適合于一些需要大量查詢的應(yīng)用，但其對(duì)于有大量寫操作并不是很好。甚至你只是需要update一個(gè)字段，整個(gè)表都會(huì)被鎖起來(lái)，而別的進(jìn)程，就算是讀進(jìn)程都無(wú)法操作直到讀操作完成。另外，MyISAM 對(duì)于 SELECT COUNT(*) 這類的計(jì)算是超快無(wú)比的。

InnoDB 的趨勢(shì)會(huì)是一個(gè)非常復(fù)雜的存儲(chǔ)引擎，對(duì)于一些小的應(yīng)用，它會(huì)比 MyISAM 還慢。他是它支持“行鎖” ，于是在寫操作比較多的時(shí)候，會(huì)更優(yōu)秀。并且，他還支持更多的高級(jí)應(yīng)用，比如：事務(wù)。

到此這篇關(guān)于mysql查詢優(yōu)化之100萬(wàn)條數(shù)據(jù)的一張表優(yōu)化方案的文章就介紹到這了,更多相關(guān)mysql查詢優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！