前言
數(shù)據(jù)庫查詢不走索引會導(dǎo)致全表掃描暂题,效率低下。
舉例
- “列類型”與“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條記錄膘怕;
- 相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有很大的幫助
- 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過濾屬性上添加索引典勇。 - Using index
Extra為Using index說明劫哼,SQL所需要返回的所有列數(shù)據(jù)均在一棵索引樹上,而無需訪問實際的行記錄割笙。
這類SQL語句往往性能較好权烧。 - Using index condition
Extra為Using index condition說明,確實命中了索引伤溉,但不是所有的列數(shù)據(jù)都在索引樹上般码,還需要訪問實際的行記錄。 - Using filesort
Extra為Using filesort說明乱顾,得到所需結(jié)果集板祝,需要對所有記錄進(jìn)行文件排序。
這類SQL語句性能極差走净,需要進(jìn)行優(yōu)化券时。
在一個沒有建立索引的列上進(jìn)行了order by,就會觸發(fā)filesort伏伯,常見的優(yōu)化方案是革为,在order by的列上添加索引,避免每次查詢都全量排序舵鳞。 - Using temporary
Extra為Using temporary說明,需要建立臨時表(temporary table)來暫存中間結(jié)果琢蛤。
這類SQL語句性能較低蜓堕,往往也需要進(jìn)行優(yōu)化抛虏。
group by和order by同時存在,且作用于不同的字段時套才,就會建立臨時表迂猴,以便計算出最終的結(jié)果集。 - 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)計算静檬。