震驚风秤,竟然不能命中索引?

前言

數(shù)據(jù)庫查詢不走索引會導(dǎo)致全表掃描暂题,效率低下。

舉例

  1. “列類型”與“where值類型”不符贡未,不能命中索引,會導(dǎo)致全表掃描(full table scan)。
  • 數(shù)據(jù)準(zhǔn)備
create table t1 (
cell varchar(3) primary key
)engine=innodb default charset=utf8;

insert into t1(cell) values ('111'),('222'),('333');
cell屬性為varchar類型会喝;
cell為主鍵陡叠,即聚簇索引(clustered index);
  • 測試語句
explain select * from t1 where cell=111;
explain select * from t1 where cell='111';
第一個語句肢执,where后的值類型是整數(shù)(與表cell類型不符)枉阵;
第二個語句,where后的值類型是字符串(與表cell類型一致)预茄;
  • 測試結(jié)果


    列類型與where后值類型不符.png

    列類型與where后值類型相同.png

    強(qiáng)制類型轉(zhuǎn)換兴溜,不能命中索引,需要全表掃描耻陕,即3條記錄拙徽;
    類型相同,命中索引诗宣,1條記錄膘怕;

  1. 相join的兩個表的字符編碼不同,不能命中索引召庞,會導(dǎo)致笛卡爾積的循環(huán)計算(nested loop)
create table t2 (
cell varchar(3) primary key
)engine=innodb default charset=latin1;

insert into t2(cell) values ('111'),('222'),('333'),('444'),('555'),('666');

create table t3 (
cell varchar(3) primary key
)engine=innodb default charset=utf8;

insert into t3(cell) values ('111'),('222'),('333'),('444'),('555'),('666');
t2和t1字符集不同岛心,插入6條測試數(shù)據(jù);
t3和t1字符集相同裁眯,也插入6條測試數(shù)據(jù)鹉梨;
  • 測試語句
explain select * from t1,t2 where t1.cell=t2.cell;
explain select * from t1,t3 where t1.cell=t3.cell;
第一個join,連表t1和t2(字符集不同)穿稳,關(guān)聯(lián)屬性是cell存皂;
第一個join,連表t1和t3(字符集相同)逢艘,關(guān)聯(lián)屬性是cell旦袋;
  • 測試結(jié)果


    字符編碼不同.png

    字符編碼相同.png

    t1和t2字符集不同,存儲空間不同它改;
    t1和t2相join時疤孕,遍歷了t1的所有記錄3條,t1的每一條記錄又要遍歷t2的所有記錄6條央拖,實際進(jìn)行了笛卡爾積循環(huán)計算(nested loop)祭阀,索引無效;
    t1和t3相join時鲜戒,遍歷了t1的所有記錄3條专控,t1的每一條記錄使用t3索引,即掃描1行記錄遏餐;

explain

  • type訪問類型伦腐,即找到所需數(shù)據(jù)使用的遍歷方式,潛在的方式有:
    ALL(Full Table Scan):全表掃描失都;
    index:走索引的全表掃描柏蘑;
    range:命中where子句的范圍索引掃描幸冻;
    ref/eq_ref:非唯一索引/唯一索引單值掃描;
    const/system:常量掃描咳焚;
    NULL:不用訪問表洽损;
    ALL最慢,逐步變快黔攒,NULL最快趁啸。
  • Extra字段,對分析與優(yōu)化SQL有很大的幫助
  1. Using where
    Extra為Using where說明督惰,SQL使用了where條件過濾數(shù)據(jù)
    需要注意的是:
    (1)返回所有記錄的SQL不傅,不使用where條件過濾數(shù)據(jù),大概率不符合預(yù)期赏胚,對于這類SQL往往需要進(jìn)行優(yōu)化访娶;
    (2)使用了where條件的SQL,并不代表不需要優(yōu)化觉阅,往往需要配合explain結(jié)果中的type(連接類型)來綜合判斷崖疤;
    (3)常見的優(yōu)化方法為,在where過濾屬性上添加索引典勇。
  2. Using index
    Extra為Using index說明劫哼,SQL所需要返回的所有列數(shù)據(jù)均在一棵索引樹上,而無需訪問實際的行記錄割笙。
    這類SQL語句往往性能較好权烧。
  3. Using index condition
    Extra為Using index condition說明,確實命中了索引伤溉,但不是所有的列數(shù)據(jù)都在索引樹上般码,還需要訪問實際的行記錄。
  4. Using filesort
    Extra為Using filesort說明乱顾,得到所需結(jié)果集板祝,需要對所有記錄進(jìn)行文件排序。
    這類SQL語句性能極差走净,需要進(jìn)行優(yōu)化券时。
    在一個沒有建立索引的列上進(jìn)行了order by,就會觸發(fā)filesort伏伯,常見的優(yōu)化方案是革为,在order by的列上添加索引,避免每次查詢都全量排序舵鳞。
  5. Using temporary
    Extra為Using temporary說明,需要建立臨時表(temporary table)來暫存中間結(jié)果琢蛤。
    這類SQL語句性能較低蜓堕,往往也需要進(jìn)行優(yōu)化抛虏。
    group by和order by同時存在,且作用于不同的字段時套才,就會建立臨時表迂猴,以便計算出最終的結(jié)果集。
  6. Using join buffer (Block Nested Loop)
    Extra為Using join buffer (Block Nested Loop)說明背伴,需要進(jìn)行嵌套循環(huán)計算沸毁。
    這類SQL語句性能往往也較低,需要進(jìn)行優(yōu)化傻寂。
    兩個關(guān)聯(lián)表join息尺,關(guān)聯(lián)字段均未建立索引,就會出現(xiàn)這種情況疾掰。常見的優(yōu)化方案是搂誉,在關(guān)聯(lián)字段上添加索引,避免每次嵌套循環(huán)計算静檬。
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末炭懊,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子拂檩,更是在濱河造成了極大的恐慌侮腹,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,681評論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件稻励,死亡現(xiàn)場離奇詭異父阻,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī)钉迷,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,205評論 3 399
  • 文/潘曉璐 我一進(jìn)店門至非,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人糠聪,你說我怎么就攤上這事荒椭。” “怎么了舰蟆?”我有些...
    開封第一講書人閱讀 169,421評論 0 362
  • 文/不壞的土叔 我叫張陵趣惠,是天一觀的道長。 經(jīng)常有香客問我身害,道長味悄,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 60,114評論 1 300
  • 正文 為了忘掉前任塌鸯,我火速辦了婚禮侍瑟,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘。我一直安慰自己涨颜,他們只是感情好费韭,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,116評論 6 398
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著庭瑰,像睡著了一般星持。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上弹灭,一...
    開封第一講書人閱讀 52,713評論 1 312
  • 那天督暂,我揣著相機(jī)與錄音,去河邊找鬼穷吮。 笑死逻翁,一個胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的酒来。 我是一名探鬼主播卢未,決...
    沈念sama閱讀 41,170評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼堰汉!你這毒婦竟也來了辽社?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 40,116評論 0 277
  • 序言:老撾萬榮一對情侶失蹤翘鸭,失蹤者是張志新(化名)和其女友劉穎滴铅,沒想到半個月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體就乓,經(jīng)...
    沈念sama閱讀 46,651評論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡汉匙,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,714評論 3 342
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了生蚁。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片噩翠。...
    茶點(diǎn)故事閱讀 40,865評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖邦投,靈堂內(nèi)的尸體忽然破棺而出伤锚,到底是詐尸還是另有隱情,我是刑警寧澤志衣,帶...
    沈念sama閱讀 36,527評論 5 351
  • 正文 年R本政府宣布屯援,位于F島的核電站,受9級特大地震影響念脯,放射性物質(zhì)發(fā)生泄漏狞洋。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,211評論 3 336
  • 文/蒙蒙 一绿店、第九天 我趴在偏房一處隱蔽的房頂上張望吉懊。 院中可真熱鬧,春花似錦、人聲如沸借嗽。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,699評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽淹魄。三九已至,卻和暖如春堡距,著一層夾襖步出監(jiān)牢的瞬間甲锡,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,814評論 1 274
  • 我被黑心中介騙來泰國打工羽戒, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留缤沦,地道東北人。 一個月前我還...
    沈念sama閱讀 49,299評論 3 379
  • 正文 我出身青樓易稠,卻偏偏與公主長得像缸废,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子驶社,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,870評論 2 361

推薦閱讀更多精彩內(nèi)容

  • --- layout: post title: "如果有人問你關(guān)系型數(shù)據(jù)庫的原理企量,叫他看這篇文章(轉(zhuǎn))" date...
    藍(lán)墜星閱讀 796評論 0 3
  • MySQL技術(shù)內(nèi)幕:SQL編程 姜承堯 第1章 SQL編程 >> B是由MySQL創(chuàng)始人之一Monty分支的一個版...
    沉默劍士閱讀 2,434評論 0 3
  • 常用語句: sql/plus sqlplus 'amdocs/Amdocs.Jx.China.110#@ysdb1...
    好好學(xué)習(xí)的蝸牛閱讀 3,025評論 0 0
  • 原文《MySQL實戰(zhàn)45講》 前言 ? 在實際生產(chǎn)中,關(guān)于 join 語句使用的問題亡电,一般會集中在以下兩類: ...
    灰氣球閱讀 1,647評論 0 0
  • 牛頓届巩, 卡塞格林,消除球差份乒,鏡片是非球面恕汇,倒像 格雷戈里,主鏡拋物面或辖,副鏡面橢球型瘾英,正像! 內(nèi)史密斯颂暇,是卡塞格林加...
    張寶興閱讀 301評論 0 0