author：咔咔

wechat：fangkangfk

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類(lèi)型的表用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)銷(xiāo)！

改進(jìn)方案2

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

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

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

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 秒

好恐怖的速度??！利用上面方案進(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、分類(lèi)信息，我們將文章內(nèi)容這個(gè)大字段分割出去。

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

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

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

cate int 11 索引

在寫(xiě)入數(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秒，非常棒的速度。

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

如果你有一個(gè)字段，比如“性別”，“國(guó)家”，“民族”，“狀態(tài)”或“部門(mén)”，你知道這些字段的取值是有限而且固定的，那么，你應(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值。