正兒八經(jīng)mysql優(yōu)化！

mysql數(shù)據(jù)量少，優(yōu)化沒(méi)必要，數(shù)據(jù)量大，優(yōu)化少不了，不優(yōu)化一個(gè)查詢(xún)10秒，優(yōu)化得當(dāng)，同樣查詢(xún)10毫秒。

這是多么痛的領(lǐng)悟！

mysql優(yōu)化，說(shuō)程序員的話(huà)就是：索引優(yōu)化和where條件優(yōu)化。

實(shí)驗(yàn)環(huán)境：MacBook Pro MJLQ2CH/A，mysql5.7，數(shù)據(jù)量：212萬(wàn)+

ONE：

 select * from article
 INNER JOIN (
 SELECT id
 FROM article
 WHERE
  length(content_url) > 0 and
  (select status from source where id = article.source_id)=1 and
  (select status from category where id = article.category_id)=1 and
  status = 1 and id  2164931
 order by stick desc,pub_time desc
 limit 240,15
 ) AS t
USING(id);

咋一看，大佬肯定會(huì)想殺了我，沒(méi)事做啥自關(guān)聯(lián)，還是inner join。XX樓的，把我的殺豬刀拿來(lái)，我要宰了博主！?。?/p>

說(shuō)實(shí)話(huà)，早上出門(mén)我的腦袋沒(méi)被門(mén)擠，我也不想這樣的。

1.數(shù)據(jù)量大了，你要做offset很大的分頁(yè)查詢(xún)，還真的這樣提速，原因 ---> 用join子表中的id覆蓋到全表，避免全表掃描。

看我的order by（細(xì)語(yǔ)：不就是個(gè)order by，TM誰(shuí)不會(huì)寫(xiě)），你把這個(gè)order by換成你自己的表中的字段desc or explain看看。Extra ---> filesort ! shit !

2.針對(duì)這種多個(gè)條件的order by，通常我們會(huì)直接給兩個(gè)字段分別加index，然而還是會(huì)Extra ---> filesort。另辟蹊徑，給order by后面的所有條件加一個(gè)聯(lián)合索引，注意順序一定要和你的order by順序一致。這樣Extra就只剩下where了。

再看看where，(select status from source where id = article.source_id)=1 and ...又啥JB寫(xiě)法！

3.想過(guò)用join+index的方式，最后測(cè)試出來(lái)，和這種方式幾乎無(wú)差別。生產(chǎn)環(huán)境是這樣寫(xiě)的，那就這樣吧，還能少兩個(gè)索引（source_id，category_id），懶病犯了誰(shuí)都阻擋不了，以后吃虧了又回來(lái)繼續(xù)優(yōu)化唄。

4.這個(gè)點(diǎn)是我昨晚才get到的，where條件的滿(mǎn)足順序是優(yōu)先滿(mǎn)足最后一個(gè)條件，從右到左，經(jīng)過(guò)刪除index測(cè)試，確實(shí)有效果，能從6秒降到4秒，優(yōu)化了index之后再次測(cè)試發(fā)現(xiàn)順序?qū)臅r(shí)影響幾乎可以忽略不計(jì)，0.X毫秒。

TWO：

 select * from article
 INNER JOIN (
 SELECT id FROM article WHERE INSTR(ifnull(title,''),'戰(zhàn)狼') > 0 and status != 9
 order by pub_time desc
 limit 100,10

 ) AS t USING(id);

嗯——又是inner join.......

INSTR(ifnull(title,''),'戰(zhàn)狼') > 0，為啥不用like......

1.考慮到這是管理平臺(tái)的搜索，沒(méi)有去搜索引擎上搜，搜索引擎是一個(gè)小時(shí)才同步一次數(shù)據(jù)，數(shù)據(jù)不全。管理人員搜索時(shí)只管他要的結(jié)果，like %XX%不能走索引，效率比instr低了5倍，又測(cè)試了regexp '.*XX*.'，還是比instr耗時(shí)多一點(diǎn)，索性.....

desc or explain看看，filesort.....給pub_time加個(gè)index看看，還是filesort.....

2.這種情況有另外一種方案，SELECT id FROM article force index(pub_time)，指定使用這個(gè)索引。但是這種寫(xiě)法太缺靈活性了，OUT！百度一下，有高人指點(diǎn)迷津：把status和pub_time建個(gè)聯(lián)合索引（pub_time_status，order的條件在前），讓where查詢(xún)的時(shí)候，把這個(gè)index自動(dòng)force上。

THREE：

select * from article where status != 9 order by pub_time desc limit 100000,25;
desc or explain，還是filesort.....前面不是給status和pub_time建了聯(lián)合索引了嗎，tell me why......

好吧，我也不知道，把status和pub_time再建個(gè)聯(lián)合索引status_pub_time，這次where條件在前，explain沒(méi)filesort了，但是這個(gè)index卻沒(méi)有被使用，它勾搭出了pub_time_status。搞不懂啊

同時(shí)我又explain了TWO的SQL，都是如下圖：

這二者中刪除任何一個(gè)都不行，刪除一個(gè)，就有sql會(huì)filesort！

FOUR：

SELECT * from follow
 where (((SELECT status FROM source WHERE id=follow.source_id)=1 and follow.type=1) or ((select status from topic WHERE id=follow.source_id)=1 and follow.type=2)) AND user_id=10054
 ORDER BY sort limit 15,15;
 SELECT * from follow inner join(
 SELECT id from follow
 where (((SELECT status FROM source WHERE id=follow.source_id)=1 and follow.type=1) or ((select status from topic WHERE id=follow.source_id)=1 and follow.type=2)) AND user_id=10054
 ORDER BY sort limit 15,15
 ) as t using(id);
 (SELECT id, source_id, user_id, temporary, sort, follow_time, read_time,type from follow where (SELECT status FROM source WHERE id=follow.source_id)=1 and follow.type=1 and user_id=10054)
 union all
 (SELECT id, source_id, user_id, temporary, sort, follow_time, read_time,type from follow where (select status from topic WHERE id=follow.source_id)=1 and follow.type=2 and user_id=10054)
 ORDER BY sort limit 15,15;

看看這三句sql，interesting，是不是！

為了公平起見(jiàn)，我已經(jīng)優(yōu)化了索引，user_id_sort(user_id,sort)，讓where在用user_id判斷時(shí)force上這個(gè)索引。

第一句：0.48ms

第二句：0.42ms

第三句：6ms，導(dǎo)致時(shí)間長(zhǎng)那么多的原因是union(查詢(xún)兩次表，合并成子表)后不能用index覆蓋到order by的sort上

有的時(shí)候union不一定比or快。

總結(jié)

以上所述是小編給大家分享的mysql大數(shù)據(jù)查詢(xún)優(yōu)化經(jīng)驗(yàn)，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！