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

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

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

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

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

總結(jié)：

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

2.百萬數(shù)據(jù)下mysql分頁問題

在開發(fā)過程中我們經(jīng)常會使用分頁，核心技術(shù)是使用limit進(jìn)行數(shù)據(jù)的讀取，在使用limit進(jìn)行分頁的測試過程中，得到以下數(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ù)量大的情況下分頁起點(diǎn)越大查詢速度越慢，100萬條起的查詢速度已經(jīng)需要7秒鐘。這是一個(gè)我們無法接受的數(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秒，提升效率是非常明顯的??！原理是什么呢？？？

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

改進(jìn)方案2

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

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

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

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

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

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

好恐怖的速度??！利用第一節(jié)知識進(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)化效果不明顯，條件帶來的影響還是很大！在這樣的情況下無論我們怎么去優(yōu)化sql語句就無法解決運(yùn)行效率問題。那么換個(gè)思路：建立一個(gè)索引表，只記錄文章的id、分類信息，我們將文章內(nèi)容這個(gè)大字段分割出去。

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

id int 11 主鍵自動增加

cate int 11 索引

在寫入數(shù)據(jù)時(shí)將2張表同步，查詢是則可以使用news2 來進(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萬左右是查詢時(shí)間可以保持在0.5秒左右，是一個(gè)逐步接近我們能夠容忍的值！

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

將 news2 的存儲引擎改變?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存儲引擎 myIsam和innodb的區(qū)別

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

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

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

核心區(qū)別

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

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

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

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

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

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

應(yīng)用場景

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

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

Mysql的存儲引擎和索引

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

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

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

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

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

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

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

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

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

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

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

這里最主要的問題是，對于程序員來說，這個(gè)事情是很容易被忽略的。因?yàn)?，我們某些查詢語句會讓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的查詢緩存對這個(gè)函數(shù)不起作用。所以，像 NOW() 和 RAND() 或是其它的諸如此類的SQL函數(shù)都不會開啟查詢緩存，因?yàn)檫@些函數(shù)的返回是會不定的易變的。所以，你所需要的就是用一個(gè)變量來代替MySQL的函數(shù)，從而開啟緩存。

b.學(xué)會使用EXPLAIN

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

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

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

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

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

d.正確的使用索引

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

e.不要ORDER BY RAND()

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

f.避免SELECT *

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

g.使用 ENUM 而不是 VARCHAR

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

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

h.使用 NOT NULL

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

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

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

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

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

j.固定長度的表會更快

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

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

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

k.垂直分割

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

l.拆分大的 DELETE 或 INSERT 語句

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

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

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

m.越小的列會越快

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

n.選擇正確的存儲引擎

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

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

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

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