正兒八經mysql優化！
mysql數據量少，優化沒必要，數據量大，優化少不了，不優化一個查詢10秒，優化得當，同樣查詢10毫秒。
這是多么痛的領悟！
mysql優化，說程序員的話就是：索引優化和where條件優化。
實驗環境：MacBook Pro MJLQ2CH/A，mysql5.7，數據量：212萬+
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);
咋一看，大佬肯定會想殺了我，沒事做啥自關聯，還是inner join。XX樓的，把我的殺豬刀拿來，我要宰了博主！！！
說實話，早上出門我的腦袋沒被門擠，我也不想這樣的。
1.數據量大了，你要做offset很大的分頁查詢，還真的這樣提速，原因 ---> 用join子表中的id覆蓋到全表，避免全表掃描。
看我的order by（細語：不就是個order by，TM誰不會寫），你把這個order by換成你自己的表中的字段desc or explain看看。Extra ---> filesort ! shit !
2.針對這種多個條件的order by，通常我們會直接給兩個字段分別加index，然而還是會Extra ---> filesort。另辟蹊徑，給order by后面的所有條件加一個聯合索引，注意順序一定要和你的order by順序一致。這樣Extra就只剩下where了。
再看看where，(select status from source where id = article.source_id)=1 and ...又啥JB寫法！
3.想過用join+index的方式，最后測試出來，和這種方式幾乎無差別。生產環境是這樣寫的，那就這樣吧，還能少兩個索引（source_id，category_id），懶病犯了誰都阻擋不了，以后吃虧了又回來繼續優化唄。
4.這個點是我昨晚才get到的，where條件的滿足順序是優先滿足最后一個條件，從右到左，經過刪除index測試，確實有效果，能從6秒降到4秒，優化了index之后再次測試發現順序對耗時影響幾乎可以忽略不計，0.X毫秒。
TWO：
select * from article
INNER JOIN (
SELECT id FROM article WHERE INSTR(ifnull(title,''),'戰狼') > 0 and status != 9
order by pub_time desc
limit 100,10
) AS t USING(id);
嗯——又是inner join.......
INSTR(ifnull(title,''),'戰狼') > 0，為啥不用like......
1.考慮到這是管理平臺的搜索，沒有去搜索引擎上搜，搜索引擎是一個小時才同步一次數據，數據不全。管理人員搜索時只管他要的結果，like %XX%不能走索引，效率比instr低了5倍，又測試了regexp '.*XX*.'，還是比instr耗時多一點，索性.....
desc or explain看看，filesort.....給pub_time加個index看看，還是filesort.....
2.這種情況有另外一種方案，SELECT id FROM article force index(pub_time)，指定使用這個索引。但是這種寫法太缺靈活性了，OUT！百度一下，有高人指點迷津：把status和pub_time建個聯合索引（pub_time_status，order的條件在前），讓where查詢的時候，把這個index自動force上。
THREE：
select * from article where status != 9 order by pub_time desc limit 100000,25;
desc or explain，還是filesort.....前面不是給status和pub_time建了聯合索引了嗎，tell me why......
好吧，我也不知道，把status和pub_time再建個聯合索引status_pub_time，這次where條件在前，explain沒filesort了，但是這個index卻沒有被使用，它勾搭出了pub_time_status。搞不懂啊
同時我又explain了TWO的SQL，都是如下圖：
這二者中刪除任何一個都不行，刪除一個，就有sql會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，是不是！
為了公平起見，我已經優化了索引，user_id_sort(user_id,sort)，讓where在用user_id判斷時force上這個索引。
第一句：0.48ms
第二句：0.42ms
第三句：6ms，導致時間長那么多的原因是union(查詢兩次表，合并成子表)后不能用index覆蓋到order by的sort上
有的時候union不一定比or快。
總結
以上所述是小編給大家分享的mysql大數據查詢優化經驗，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網站的支持！
您可能感興趣的文章:- MySQL 自定義變量的概念及特點
- MySQL中使用自定義變量 編寫偷懶的UNION示例
- 理解MySQL查詢優化處理過程
- mysql查詢優化之100萬條數據的一張表優化方案
- 詳解MySQL 聯合查詢優化機制
- MySQL查詢優化之查詢慢原因和解決技巧
- MySQL之select in 子查詢優化的實現
- Mysql慢查詢優化方法及優化原則
- 30個mysql千萬級大數據SQL查詢優化技巧詳解
- MySQL百萬級數據分頁查詢優化方案
- MySQL 使用自定義變量進行查詢優化
