北京赛车 pk10开奖记录_Toyou热购彩票官网
香港高速VIP雲機房火爆上線啦!無需備案郴,開通即用!配套《巔雲自助建站系統3.0》將帶給您飛一般的可視化拖拽建站體驗僻,歡迎免費體驗湃咳副。

項目中常用的19條MySQL優化

一佰互聯網站制作(www.yinxi.net) 發布日期 2019-03-26 12:18:43 瀏覽數: 60

簡介陡卿︰作者彈︰zhangqh 來源舞︰https://segmentfault.com/a/1190000012155267 一阮風、EXPLAIN做MySQL優化躬會,我們要善用 EXPLAIN 查看SQL執行計劃苗親。下面來個簡單的示例沁幌信,標注(1,2,3,4,5)我們要重點關注的數據type列泊城,連接類型董粗涕。一 ...

作者館河︰zhangqh來源疇︰https://segmentfault.com/a/1190000012155267

一娜、EXPLAIN

做MySQL優化蜜,我們要善用 EXPLAIN 查看SQL執行計劃懦群。

下面來個簡單的示例洗,標注(1,2,3,4,5)我們要重點關注的數據

項目中常用的19條MySQL優化

  • type列刪憐,連接類型碑叉嘛。一個好的sql語句至少要達到range級別蛙屯染。杜絕出現all級別
  • key列搪,使用到的索引名里。如果沒有選擇索引律,值是NULL享轟裸。可以采取強制索引方式
  • key_len列汕,索引長度
  • rows列耙舶,掃描行數犀。該值是個預估值
  • extra列茫甦砂,詳細說明梧相沃。注意常見的不太友好的值有略新︰Using filesort, Using temporary


二概高苦、SQL語句中IN包含的值不應過多

MySQL對于IN做了相應的優化臣耗,即將IN中的常量全部存儲在一個數組里面奉,而且這個數組是排好序的京習謙。但是如果數值較多掏朵,產生的消耗也是比較大的備攫佳。再例如擰福棵︰select id from table_name where num in(1,2,3) 對于連續的數值扣廓胸,能用 between 就不要用 in 了;再或者使用連接來替換筷脫媳。


三貫綏、SELECT語句務必指明字段名稱

SELECT *增加很多不必要的消耗(cpu礙昂存、io鄲箔、內存乳坤逃、網絡帶寬);增加了使用覆蓋索引的可能性;當表結構發生改變時戲娠,前斷也需要更新瞪稻騾。所以要求直接在select後面接上字段名炭冕砂。


四懾狹、當只需要一條數據的時候壟淬,使用limit 1

這是為了使EXPLAIN中type列達到const類型


五唾憾勒、如果排序字段沒有用到索引仁偏困,就盡量少排序


六蓬講軟、如果限制條件中其他字段沒有索引郎,盡量少用or

or兩邊的字段中暑下,如果有一個不是索引字段橡涉,而其他條件也不是索引字段廬,會造成該查詢不走索引的情況滑橇餡。很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果


七劑、盡量用union all代替union

union和union all的差異主要是前者需要將結果集合並後再進行唯一性過濾操作雄,這就會涉及到排序拍,增加大量的CPU運算部病,加大資源消耗及延遲岡。當然淮疏榔,union all的前提條件是兩個結果集沒有重復數據訛。


八橫、不使用ORDER BY RAND()
select id from `table_name` order by rand() limit 1000;

上面的sql語句芭歸,可優化為
select id from `table_name` t1 join (select rand() * (select max(id) from `table_name`) as nid) t2 on t1.id > t2.nid limit 1000;


九捷吶梅、區分in和exists臀唉, not in和not exists
select * from 表A where id in (select id from 表B)

上面sql語句相當于
select * from 表A where exists(select * from 表B where 表B.id=表A.id)

區分in和exists主要是造成了驅動順序的改變(這是性能變化的關鍵)殿斧寸,如果是exists掏,那麼以外層表為驅動表太羚鈕,先被訪問傘厘,如果是IN頗扇,那麼先執行子查詢蕉嫩菩。所以IN適合于外表大而內表小的情況;EXISTS適合于外表小而內表大的情況潮。

關于not in和not exists鴕,推薦使用not exists侈揣,不僅僅是效率問題狹祿,not in可能存在邏輯問題敬伸。如何高效的寫出一個替代not exists的sql語句?

原sql語句
select colname … from A表 where a.id not in (select b.id from B表)

高效的sql語句
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

取出的結果集如下圖表示涵寡,A表不在B表中的數據



項目中常用的19條MySQL優化




十蝕固、使用合理的分頁方式以提高分頁的效率
select id,name from table_name limit 866613, 20

使用上述sql語句做分頁的時候費蓮,可能有人會發現餡效距,隨著表數據量的增加退悲,直接使用limit分頁查詢會越來越慢鄙廊。

優化的方法如下副猩︰可以取前一頁的最大行數的id肯肯,然後根據這個最大的id來限制下一頁的起點眯。比如此列中粕倡冊,上一頁最大的id是866612徽。sql可以采用如下的寫法全︰
select id,name from table_name where id> 866612 limit 20


十一酵激權、分段查詢

在一些用戶選擇頁面中龐哪,可能一些用戶選擇的時間範圍過大摩喂汲,造成查詢緩慢俗鞘摔。主要的原因是掃描行數過多慷伴緞。這個時候可以通過程序揉放搪,分段進行查詢甜,循環遍歷扁,將結果合並處理進行展示蛻窪。

如下圖這個sql語句逝潮,掃描的行數成百萬級以上的時候就可以使用分段查詢

項目中常用的19條MySQL優化



十二肚乃、避免在 where 子句中對字段進行 null 值判斷

對于null的判斷會導致引擎放棄使用索引而進行全表掃描記。


十三俺簍、不建議使用%前綴奈校糊查詢

例如LIKE “%name”或者LIKE “%name%”弛舵釜,這種查詢會導致索引失效而進行全表掃描麓凶。但是可以使用LIKE “name%”尖全。

那如何查詢%name%?

如下圖所示墩勸緩,雖然給secret字段添加了索引販辦陷,但在explain結果果並沒有使用

項目中常用的19條MySQL優化


那麼如何解決這個問題呢乾城,答案渮︰使用全文索引

在我們查詢中經常會用到select id,fnum,fdst from table_name where user_name like "%zhangsan%";坷。這樣的語句券抵,普通索引是無法滿足查詢需求的茅畏。慶幸的是在MySQL中唯貶娟,有全文索引來幫助我們陋悉。

創建全文索引的sql語法是 矢︰
ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

使用全文索引的sql語句是緝巧橡︰
select id,fnum,fdst from table_name where match(user_name) against("zhangsan" in boolean mode);

注意朝技砷︰在需要創建全文索引之前絆輯,請聯系DBA確定能否創建味。同時需要注意的是查詢語句的寫法與普通索引的區別


十四喪凱翹、避免在where子句中對字段進行表達式操作

比如
select user_id,user_project from table_name where age*2=36;

中對字段就行了算術運算垃,這會造成引擎放棄使用索引搏,建議改成
select user_id,user_project from table_name where age=36/2;


十五贍宏、避免隱式類型轉換

where 子句中出現 column 字段的類型和傳入的參數類型不一致的時候發生的類型轉換館海,建議先確定where中的參數類型

項目中常用的19條MySQL優化



十六匡茬、對于聯合索引來說頭雙使,要遵守最左前綴法則

舉列來說索引含有字段id,name,school八,可以直接用id字段四幾廄,也可以id,name這樣的順序頭菩,但是name懾,school都無法使用這個索引恃喚。所以在創建聯合索引的時候一定要注意索引字段順序繡,常用的查詢字段放在最前面


十七授傷噶、必要時可以使用force index來強制查詢走某個索引

有的時候MySQL優化器采取它認為合適的索引來檢索sql語句鋇舍廄,但是可能它所采用的索引並不是我們想要的鹼。這時就可以采用force index來強制優化器使用我們制定的索引河駭輕。


十八辭娩瓷、注意範圍查詢語句

對于聯合索引來說敬淋,如果存在範圍查詢兄,比如between,>,<等條件時倆衫率,會造成後面的索引字段失效燦稜。


十九呢屜淒、關于JOIN優化



項目中常用的19條MySQL優化


  • LEFT JOIN A表為驅動表
  • INNER JOIN MySQL會自動找出那個數據少的表作用驅動表
  • RIGHT JOIN B表為驅動表

注意嘎溜︰MySQL中沒有full join比,可以用以下方式來解決
select * from A left join B on B.name = A.name where B.name is null union allselect * from B;

盡量使用inner join痞,避免left join

參與聯合查詢的表至少為2張表告,一般都存在大小之分倪工。如果連接方式是inner join高,在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅動表呵記,但是left join在驅動表的選擇上遵循的是左邊驅動右邊的原則沫蒲,即left join左邊的表名為驅動表將猾。

合理利用索引

被驅動表的索引字段作為on的限制字段卵祿。

利用小表去驅動大表



項目中常用的19條MySQL優化



從原理圖能夠直觀的看出如果能夠減少驅動表的話仕扁,減少嵌套循環中的循環次數犁紳捻,以減少 IO總量及CPU運算的次數泊。

巧用STRAIGHT_JOIN

inner join是由mysql選擇驅動表搪盲朔,但是有些特殊情況需要選擇另個表作為驅動表妨,比如有group by巨搏聰、order by等「Using filesort」塑槍彪、「Using temporary」時杉床。STRAIGHT_JOIN來強制連接順序祈筒拖,在STRAIGHT_JOIN左邊的表名就是驅動表湘,右邊則是被驅動表舍實。在使用STRAIGHT_JOIN有個前提條件是該查詢是內連接詳饋,也就是inner join吵。其他鏈接不推薦使用STRAIGHT_JOIN晦刷,否則可能造成查詢結果不準確冠。

項目中常用的19條MySQL優化


這個方式有時可能減少3倍的時間度篡瓊。

這里只列舉了上述優化方案仙,當然還有其他的優化方式相,大家可以去摸索嘗試濃鴻矮,感謝關注娥。

34張架構史上最全技術知識圖譜

本文僅代表作者個人觀點掏,不代表巔雲官方發聲爐,對觀點有疑義請先聯系作者本人進行修改駱目謾,若內容非法請聯系平台管理員停碘瞎,郵箱qq2522407257冊先零。更多相關資訊鐮隊,請到巔雲www.yinxi.net學習互聯網營銷技術請到巔雲學院www.yx10011.com競炒。
TAG標簽檬劑剃︰ 項目中常用的19條MySQL優化  
一佰互聯是全國知名建站品牌服務商,我們有九年網站建設苟、網站制作喜、網頁設計敘揀、php開發和域名注冊及虛擬主機服務經驗緞理,提供的自助建站服務更是全國有名存墾。近年來還整合團隊優勢自主開發了可視化多用戶”巔雲建站系統“3.0平台版灸甘,拖拽排版網站制作設計幌船胃,輕松實現pc站度吐、手機微網站嘩、小程序煩搏燙、APP一體化全網營銷網站建設 拐鬧,已成功的為全國上百家網絡公司提供自助建站平台搭建服務編坍。

相關新聞more

05
04月
怎麼開發百度小程序

簡介鉀阜︰重點巢齊︰怎麼開發百度小程序?想百度智能小程序開發找誰? 巨推傳媒根據市場的需要也做了一些面對弛,不論是內容類還是電商類找巨推科技做開發!巨... >>詳情

26
04月
golang 調用 php7詳解及實例

執行php文件func Test_exec(t *testing.T) { engine.Initialize() ctx := &en... >>詳情

25
04月
PHP正則解析多重循環模板示例

本文實例講述了PHP正則解析多重循環模板奶箍。分享給大家供大家參考系刷鹵,具體如下奮未鄙︰$str = "客戶您好斑,為您推薦以下項目夏嘲噸︰(crm{項目2:項目... >>詳情

06
04月
2017流行的多款HTML5網站模板

2016年12月墳北澆,我國網民規模達7.31億姓炊,互聯網普及率為53.2%配如項。 專家預計2017年我國網民規模將達7.72億驕夯,互聯網普及率達55.... >>詳情

營業執照. cdn加速服務 備案系統認證 網絡安全協會 我們的支付方式AAA認證

7x24小時服務電話:18581389571 傳真:023-85725751 免費建站交流群鉸冕味︰236412099 139947842(自助建站交流) E-Mail:post@yinxi.net 網站投訴:
重慶楚捷科技有限公司 一佰互聯©版權所有 自助建站(www.yinxi.net,Inc.) 2001-2020 All Rights Reserved 本站程序受法律保護,網站法律顧問瓜藉布︰ITLAW-莊毅雄律師
中華人民共和國信息產業部網站備案號:渝ICP備12000592號
pk10开奖 pk10开奖 北京赛车