建站
咨詢
售后
建站咨詢
新聞中心
- 《兩個(gè)工具分析SQL死鎖》
- 《SQL空值帶來(lái)的大坑》
兩個(gè)案例分析，展現(xiàn)了MySQL性能分析工具explain的強(qiáng)大。
創(chuàng)新互聯(lián)不只是一家網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司；我們對(duì)營(yíng)銷、技術(shù)、服務(wù)都有自己獨(dú)特見解，公司采取“創(chuàng)意+綜合+營(yíng)銷”一體化的方式為您提供更專業(yè)的服務(wù)！我們經(jīng)歷的每一步也許不一定是最完美的，但每一步都有值得深思的意義。我們珍視每一份信任，關(guān)注我們的成都網(wǎng)站制作、網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)質(zhì)量和服務(wù)品質(zhì)，在得到用戶滿意的同時(shí)，也能得到同行業(yè)的專業(yè)認(rèn)可，能夠?yàn)樾袠I(yè)創(chuàng)新發(fā)展助力。未來(lái)將繼續(xù)專注于技術(shù)創(chuàng)新，服務(wù)升級(jí)，滿足企業(yè)一站式營(yíng)銷型網(wǎng)站建設(shè)需求，讓再小的品牌網(wǎng)站設(shè)計(jì)也能產(chǎn)生價(jià)值！
《同一個(gè)SQL語(yǔ)句，為啥性能差異咋就這么大呢?》
詳細(xì)敘述了explain結(jié)果中最重要的type字段(連接類型)的含義。
其實(shí)，explain結(jié)果中還有一個(gè)Extra字段，對(duì)分析與優(yōu)化SQL有很大的幫助，今天花1分鐘簡(jiǎn)單和大家聊一聊。
數(shù)據(jù)準(zhǔn)備：
- create table user (
- id int primary key,
- name varchar(20),
- sex varchar(5),
- index(name)
- )engine=innodb;
- insert into user values(1, 'shenjian','no');
- insert into user values(2, 'zhangsan','no');
- insert into user values(3, 'lisi', 'yes');
- insert into user values(4, 'lisi', 'no');
數(shù)據(jù)說(shuō)明：
- 用戶表：id主鍵索引，name普通索引(非唯一)，sex無(wú)索引;
- 四行記錄：其中name普通索引存在重復(fù)記錄lisi;
實(shí)驗(yàn)?zāi)康模?/strong>
通過(guò)構(gòu)造各類SQL語(yǔ)句，對(duì)explain的Extra字段進(jìn)行說(shuō)明，啟發(fā)式定位待優(yōu)化低性能SQL語(yǔ)句。
一、【Using where】
實(shí)驗(yàn)語(yǔ)句：
- explain select * from user where sex='no';
結(jié)果說(shuō)明：
Extra為Using where說(shuō)明，SQL使用了where條件過(guò)濾數(shù)據(jù)。
需要注意的是：
- 返回所有記錄的SQL，不使用where條件過(guò)濾數(shù)據(jù)，大概率不符合預(yù)期，對(duì)于這類SQL往往需要進(jìn)行優(yōu)化;
- 使用了where條件的SQL，并不代表不需要優(yōu)化，往往需要配合explain結(jié)果中的type(連接類型)來(lái)綜合判斷;
畫外音：join type在《同一個(gè)SQL語(yǔ)句，為啥性能差異咋就這么大呢?》一文中有詳細(xì)敘述，本文不再展開。
本例雖然Extra字段說(shuō)明使用了where條件過(guò)濾，但type屬性是ALL，表示需要掃描全部數(shù)據(jù)，仍有優(yōu)化空間。
常見的優(yōu)化方法為，在where過(guò)濾屬性上添加索引。
畫外音：本例中，sex字段區(qū)分度不高，添加索引對(duì)性能提升有限。
二、【Using index】
實(shí)驗(yàn)語(yǔ)句：
- explain select id,name from user where name='shenjian';
結(jié)果說(shuō)明：
Extra為Using index說(shuō)明，SQL所需要返回的所有列數(shù)據(jù)均在一棵索引樹上，而無(wú)需訪問實(shí)際的行記錄。
畫外音：The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row.
這類SQL語(yǔ)句往往性能較好。
問題來(lái)了，什么樣的列數(shù)據(jù)，會(huì)包含在索引樹上呢?
三、【Using index condition】
實(shí)驗(yàn)語(yǔ)句：
- explain select id,name,sex from user
- where name='shenjian';
畫外音：該SQL語(yǔ)句與上一個(gè)SQL語(yǔ)句不同的地方在于，被查詢的列，多了一個(gè)sex字段。
結(jié)果說(shuō)明：
Extra為Using index condition說(shuō)明，確實(shí)命中了索引，但不是所有的列數(shù)據(jù)都在索引樹上，還需要訪問實(shí)際的行記錄。
畫外音：聚集索引，普通索引的底層實(shí)現(xiàn)差異，詳見《1分鐘了解MyISAM與InnoDB的索引差異》。
這類SQL語(yǔ)句性能也較高，但不如Using index。
問題來(lái)了，如何優(yōu)化為Using index呢?
四、【Using filesort】
實(shí)驗(yàn)語(yǔ)句：
- explain select * from user order by sex;
結(jié)果說(shuō)明：
Extra為Using filesort說(shuō)明，得到所需結(jié)果集，需要對(duì)所有記錄進(jìn)行文件排序。
這類SQL語(yǔ)句性能極差，需要進(jìn)行優(yōu)化。
典型的，在一個(gè)沒有建立索引的列上進(jìn)行了order by，就會(huì)觸發(fā)filesort，常見的優(yōu)化方案是，在order by的列上添加索引，避免每次查詢都全量排序。
五、【Using temporary】
實(shí)驗(yàn)語(yǔ)句：
- explain select * from user group by name order by sex;
結(jié)果說(shuō)明：
Extra為Using temporary說(shuō)明，需要建立臨時(shí)表(temporary table)來(lái)暫存中間結(jié)果。
這類SQL語(yǔ)句性能較低，往往也需要進(jìn)行優(yōu)化。
典型的，group by和order by同時(shí)存在，且作用于不同的字段時(shí)，就會(huì)建立臨時(shí)表，以便計(jì)算出最終的結(jié)果集。
六、【Using join buffer (Block Nested Loop)】
實(shí)驗(yàn)語(yǔ)句：
- explain select * from user where id in(select id from user where sex='no');
結(jié)果說(shuō)明：
Extra為Using join buffer (Block Nested Loop)說(shuō)明，需要進(jìn)行嵌套循環(huán)計(jì)算。
畫外音：內(nèi)層和外層的type均為ALL，rows均為4，需要循環(huán)進(jìn)行4*4次計(jì)算。
這類SQL語(yǔ)句性能往往也較低，需要進(jìn)行優(yōu)化。
典型的，兩個(gè)關(guān)聯(lián)表join，關(guān)聯(lián)字段均未建立索引，就會(huì)出現(xiàn)這種情況。常見的優(yōu)化方案是，在關(guān)聯(lián)字段上添加索引，避免每次嵌套循環(huán)計(jì)算。
結(jié)尾：
explain是SQL優(yōu)化中最常用的工具，搞定type和Extra，explain也就基本搞定了。
- 《MySQL explain，type分析》進(jìn)行了常見type分析
- 本文進(jìn)行了常見Extra分析
- 《兩個(gè)工具分析SQL死鎖》和《SQL空值帶來(lái)的大坑》是兩篇典型案例分析
- 《MyISAM與InnoDB的索引差異》是InnoDB和MyISAM索引差異分析
- 《數(shù)據(jù)庫(kù)索引，到底是什么做的?》是索引底層實(shí)現(xiàn)分析
以上幾篇文章，強(qiáng)烈建議大家讀透。
【本文為專欄作者“58沈劍”原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)聯(lián)系原作者】
戳這里，看該作者更多好文
分享標(biāo)題：如何利用工具，迅猛定位低效SQL？ | 1分鐘系列
當(dāng)前鏈接：http://m.fisionsoft.com.cn/article/ccdghhg.html
