- 相關(guān)推薦
SQL優(yōu)化大全
SQL是最重要的關(guān)系數(shù)據(jù)庫(kù)操作語(yǔ)言,并且它的影響已經(jīng)超出數(shù)據(jù)庫(kù)領(lǐng)域,得到其他領(lǐng)域的重視和采用,如人工智能領(lǐng)域的數(shù)據(jù)檢索,第四代軟件開(kāi)發(fā)工具中嵌入SQL的語(yǔ)言等。那么SQL怎么優(yōu)化?一起來(lái)看看優(yōu)化步驟吧!
1. 優(yōu)化SQL步驟
1. 通過(guò) show status和應(yīng)用特點(diǎn)了解各種 SQL的執(zhí)行頻率
通過(guò) SHOW STATUS 可以提供服務(wù)器狀態(tài)信息,也可以使用 mysqladmin extende d-status 命令獲得。 SHOW STATUS 可以根據(jù)需要顯示 session 級(jí)別的統(tǒng)計(jì)結(jié)果和 global級(jí)別的統(tǒng)計(jì)結(jié)果。
如顯示當(dāng)前session: SHOW STATUS like "Com_%"; 全局級(jí)別:show global status;
以下幾個(gè)參數(shù)對(duì) Myisam 和 Innodb 存儲(chǔ)引擎都計(jì)數(shù):
1. Com_select 執(zhí)行 select 操作的次數(shù),一次查詢只累加 1 ;
2. Com_insert 執(zhí)行 insert 操作的次數(shù),對(duì)于批量插入的 insert 操作,只累加一次 ;
3. Com_update 執(zhí)行 update 操作的次數(shù);
4. Com_delete 執(zhí)行 delete 操作的次數(shù);
以下幾個(gè)參數(shù)是針對(duì) Innodb 存儲(chǔ)引擎計(jì)數(shù)的,累加的算法也略有不同:
1. Innodb_rows_read select 查詢返回的行數(shù);
2. Innodb_rows_inserted 執(zhí)行 Insert 操作插入的行數(shù);
3. Innodb_rows_updated 執(zhí)行 update 操作更新的行數(shù);
4. Innodb_rows_deleted 執(zhí)行 delete 操作刪除的行數(shù);
通過(guò)以上幾個(gè)參數(shù),可以很容易的了解當(dāng)前數(shù)據(jù)庫(kù)的應(yīng)用是以插入更新為主還 是以查詢操作為主,以及各種類型的 SQL大致的執(zhí)行比例是多少。對(duì)于更新操作的計(jì) 數(shù),是對(duì)執(zhí)行次數(shù)的計(jì)數(shù),不論提交還是回滾都會(huì)累加。
對(duì)于事務(wù)型的應(yīng)用,通過(guò) Com_commit 和 Com_rollback 可以了解事務(wù)提交和回 滾的情況,對(duì)于回滾操作非常頻繁的數(shù)據(jù)庫(kù),可能意味著應(yīng)用編寫(xiě)存在問(wèn)題。此外,以下幾個(gè)參數(shù)便于我們了解數(shù)據(jù)庫(kù)的基本情況:
1. Connections 試圖連接 Mysql 服務(wù)器的次數(shù)
2. Uptime 服務(wù)器工作時(shí)間
3. Slow_queries 慢查詢的次數(shù)
2. 定位執(zhí)行效率較低的SQL語(yǔ)句
可以通過(guò)以下兩種方式定位執(zhí)行效率較低的 SQL 語(yǔ)句:
1. 可以通過(guò)慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的 sql 語(yǔ)句,用 --log-slow-queries[=file_name] 選項(xiàng)啟動(dòng)時(shí), mysqld 寫(xiě)一個(gè)包含所有執(zhí)行時(shí)間超過(guò)long_query_time 秒的 SQL 語(yǔ)句的日志文件?梢枣溄拥焦芾砭S護(hù)中的相關(guān)章節(jié)。
2. 使用 show processlist查看當(dāng)前MYSQL的線程, 命令慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才紀(jì)錄,所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問(wèn)題的時(shí)候查 詢慢查詢?nèi)罩静⒉荒芏ㄎ粏?wèn)題,可以使用 show processlist 命令查看當(dāng)前 MySQL 在進(jìn)行的線程,包括線程的狀態(tài),是否鎖表等等,可以實(shí)時(shí)的查看 SQL 執(zhí)行情況, 同時(shí)對(duì)一些鎖表操作進(jìn)行優(yōu)化。
3. 通過(guò)EXPLAIN 分析低效 SQL的執(zhí)行計(jì)劃:
通過(guò)以上步驟查詢到效率低的 SQL 后,我們可以通過(guò) explain 或者 desc 獲取MySQL 如何執(zhí)行 SELECT 語(yǔ)句的信息,包括 select 語(yǔ)句執(zhí)行過(guò)程表如何連接和連接 的次序。
2. MySQL索引
1. mysql如何使用索引
索引用于快速找出在某個(gè)列中有一特定值的行。對(duì)相關(guān)列使用索引是提高SELECT 操作性能的最佳途徑。
查詢要使用索引最主要的條件是查詢條件中需要使用索引關(guān)鍵字,如果是多列 索引,那么只有查詢條件使用了多列關(guān)鍵字最左邊的前綴時(shí)(前綴索引),才可以使用索引,否則 將不能使用索引。
下列情況下, Mysql 不會(huì)使用已有的索引:
1、如果 mysql 估計(jì)使用索引比全表掃描更慢,則不使用索引。例如:如果 key_part 1均勻分布在 1 和 100 之間,下列查詢中使用索引就不是很好:
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
2、如果使用 heap 表并且 where 條件中不用=索引列,其他 > 、 < 、 >= 、 <= 均不使 用索引(MyISAM和innodb表使用索引);
3、使用or分割的條件,如果or前的條件中的列有索引,后面的列中沒(méi)有索引,那么涉及到的索引都不會(huì)使用。
4、如果創(chuàng)建復(fù)合索引,如果條件中使用的列不是索引列的第一部分;(不是前綴索引)
4、如果 like 是以%開(kāi)始;
5、對(duì) where 后邊條件為字符串的一定要加引號(hào),字符串如果為數(shù)字 mysql 會(huì)自動(dòng)轉(zhuǎn) 為字符串,但是不使用索引。
2. 查看索引使用情況
如果索引正在工作, Handler_read_key 的值將很高,這個(gè)值代表了一個(gè)行被索引值讀的次數(shù),很低的值表明增加索引得到的性能改善不高,因?yàn)樗饕⒉唤?jīng)常使 用。
Handler_read_rnd_next 的值高則意味著查詢運(yùn)行低效,并且應(yīng)該建立索引補(bǔ)救。這個(gè)值的含義是在數(shù)據(jù)文件中讀下一行的請(qǐng)求數(shù)。如果你正進(jìn)行大量的表掃描,
該值較高。通常說(shuō)明表索引不正確或?qū)懭氲牟樵儧](méi)有利用索引。
語(yǔ)法:
mysql> show status like 'Handler_read%';
3. 具體優(yōu)化查詢語(yǔ)句
1. 查詢進(jìn)行優(yōu)化,應(yīng)盡量避免全表掃描
對(duì)查詢進(jìn)行優(yōu)化,應(yīng)盡量避免全表掃描,首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引
. 嘗試下面的技巧以避免優(yōu)化器錯(cuò)選了表掃描:
· 使用ANALYZE TABLEtbl_name為掃描的表更新關(guān)鍵字分布。
· 對(duì)掃描的表使用FORCEINDEX告知MySQL,相對(duì)于使用給定的索引表掃描將非常耗時(shí)。
SELECT * FROM t1, t2 FORCE INDEX (index_for_column) WHERE t1.col_name=t2.col_name;
· 用--max-seeks-for-key=1000選項(xiàng)啟動(dòng)mysqld或使用SET max_seeks_for_key=1000告知優(yōu)化器假設(shè)關(guān)鍵字掃描不會(huì)超過(guò)1,000次關(guān)鍵字搜索。
1). 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷
否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,如:
select id from t where num is null
NULL對(duì)于大多數(shù)數(shù)據(jù)庫(kù)都需要特殊處理,MySQL也不例外,它需要更多的代碼,更多的檢查和特殊的索引邏輯,有些開(kāi)發(fā)人員完全沒(méi)有意識(shí)到,創(chuàng)建表時(shí)NULL是默認(rèn)值,但大多數(shù)時(shí)候應(yīng)該使用NOT NULL,或者使用一個(gè)特殊的值,如0,-1作為默 認(rèn)值。
不能用null作索引,任何包含null值的列都將不會(huì)被包含在索引中。即使索引有多列這樣的情況下,只要這些列中有一列含有null,該列 就會(huì)從索引中排除。也就是說(shuō)如果某列存在空值,即使對(duì)該列建索引也不會(huì)提高性能。 任何在where子句中使用is null或is not null的語(yǔ)句優(yōu)化器是不允許使用索引的。
此例可以在num上設(shè)置默認(rèn)值0,確保表中num列沒(méi)有null值,然后這樣查詢:
select id from t where num=0
2). 應(yīng)盡量避免在 where 子句中使用!=或<>操作符
否則將引擎放棄使用索引而進(jìn)行全表掃描。
MySQL只有對(duì)以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些時(shí)候的LIKE。
可以在LIKE操作中使用索引的情形是指另一個(gè)操作數(shù)不是以通配符(%或者_(dá))開(kāi)頭的情形。例如:
SELECT id FROM t WHERE col LIKE 'Mich%'; # 這個(gè)查詢將使用索引,
SELECT id FROM t WHERE col LIKE '%ike'; #這個(gè)查詢不會(huì)使用索引。
3). 應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件
否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,如:
select id from t where num=10 or num=20
可以 使用UNION合并查詢: select id from t where num=10 union all select id from t where num=20
在某些情況下,or條件可以避免全表掃描的。
1 .where 語(yǔ)句里面如果帶有or條件, myisam表能用到索引, innodb不行。
2 .必須所有的or條件都必須是獨(dú)立索引
mysql or條件可以使用索引而避免全表
4) .in 和 not in 也要慎用,否則會(huì)導(dǎo)致全表掃描,
如:
select id from t where num in(1,2,3)
對(duì)于連續(xù)的數(shù)值,能用 between 就不要用 in 了:
Select id from t where num between 1 and 3
5).下面的查詢也將導(dǎo)致全表掃描:
select id from t where name like '%abc%' 或者
select id from t where name like '%abc' 或者
若要提高效率,可以考慮全文檢索。
而select id from t where name like 'abc%' 才用到索引
7). 如果在 where 子句中使用參數(shù),也會(huì)導(dǎo)致全表掃描。
因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量,但優(yōu)化程序不能將訪問(wèn)計(jì)劃的選擇推 遲到運(yùn)行時(shí);它必須在編譯時(shí)進(jìn)行選擇。然而,如果在編譯時(shí)建立訪問(wèn)計(jì)劃,變量的值還是未知的,因而無(wú)法作為索引選擇的輸入項(xiàng)。如下面語(yǔ)句將進(jìn)行全表掃描:
select id from t where num=@num
可以改為強(qiáng)制查詢使用索引: select id from t with(index(索引名)) where num=@num
8). 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作,
這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如:
select id from t where num/2=100
應(yīng)改為: select id from t where num=100*2
9). 應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作,
這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如:
select id from t where substring(name,1,3)='abc' --name
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’
生成的id 應(yīng)改為:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10).不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算,
否則系統(tǒng)將可能無(wú)法正確使用索引。
11). 索引字段不是復(fù)合索引的前綴索引
例如 在使用索引字段作為條件時(shí),如果該索引是復(fù)合索引,那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引,否則該索引將不會(huì)被使用,并且應(yīng)盡可能的讓字段順序與索引順序相一致。
2 .其他一些注意優(yōu)化:
12). 不要寫(xiě)一些沒(méi)有意義的查詢,
如需要生成一個(gè)空表結(jié)構(gòu):
select col1,col2 into #t from t where 1=0
這類代碼不會(huì)返回任何結(jié)果集,但是會(huì)消耗系統(tǒng)資源的,應(yīng)改成這樣: create table #t(...)
13). 很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇:
select num from a where num in(select num from b)
用下面的語(yǔ)句替換:
select num from a where exists(select 1 from b where num=a.num)
14). 并不是所有索引對(duì)查詢都有效,
SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢優(yōu)化的,當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí),SQL查詢可能不會(huì)去利用索引,如一表中有字段sex,male、female幾乎各一半,那么即使在sex上建了索引也對(duì)查詢效率起不了作用。
15). 索引并不是越多越好,
索引固然可以提高相應(yīng)的 select 的效率,但同時(shí)也降低了 insert 及 update 的效率,因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引,所以怎樣建索引需要慎重考慮,視具體情況而定。一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè),若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
16).應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列,
因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序,一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整,會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列,那么需要考慮是否應(yīng)將該索引建為 clustered 索引。
17).盡量使用數(shù)字型字段,
若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型,這會(huì)降低查詢和連接的性能,并會(huì)增加存儲(chǔ)開(kāi)銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符,而對(duì)于數(shù)字型而言只需要比較一次就夠了。
18).盡可能的使用 varchar/nvarchar 代替 char/nchar ,
因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小,可以節(jié)省存儲(chǔ)空間,其次對(duì)于查詢來(lái)說(shuō),在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。
19).最好不要使用"*"返回所有: select * from t ,
用具體的字段列表代替“*”,不要返回用不到的任何字段。
3. 臨時(shí)表的問(wèn)題:
20). 盡量使用表變量來(lái)代替臨時(shí)表。
如果表變量包含大量數(shù)據(jù),請(qǐng)注意索引非常有限(只有主鍵索引)。
21).避免頻繁創(chuàng)建和刪除臨時(shí)表,以減少系統(tǒng)表資源的消耗。
22).臨時(shí)表并不是不可使用,
適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行,例如,?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是,對(duì)于一次性事件,最好使用導(dǎo)出表。
23).在新建臨時(shí)表時(shí),如果一次性插入數(shù)據(jù)量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;
如果數(shù)據(jù)量不大,為了緩和系統(tǒng)表的資源,應(yīng)先create table,然后insert。
24). 如果使用到了臨時(shí)表,在存儲(chǔ)過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除,先 truncate table ,然后 drop table ,這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。
4. 游標(biāo)的問(wèn)題:
25).盡量避免使用游標(biāo),
因?yàn)橛螛?biāo)的效率較差,如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行,那么就應(yīng)該考慮改寫(xiě)。
26).使用基于游標(biāo)的方法或臨時(shí)表方法之前,
應(yīng)先尋找基于集的解決方案來(lái)解決問(wèn)題,基于集的方法通常更有效。
27).與臨時(shí)表一樣,游標(biāo)并不是不可使用。
對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法,尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開(kāi)發(fā)時(shí)間允許,基于游標(biāo)的方法和基于集的方法都可以嘗試一下,看哪一種方法的效果更好。
28).在所有的存儲(chǔ)過(guò)程和觸發(fā)器的開(kāi)始處設(shè)置 SET NOCOUNT ON ,在結(jié)束時(shí)設(shè)置 SET NOCOUNT OFF 。
無(wú)需在執(zhí)行存儲(chǔ)過(guò)程和觸發(fā)器的每個(gè)語(yǔ)句后向客戶端發(fā)送 DONE_IN_PROC 消息。
5. 事務(wù)的問(wèn)題:
29).盡量避免大事務(wù)操作,提高系統(tǒng)并發(fā)能力。
6. 數(shù)據(jù)量的問(wèn)題
30).盡量避免向客戶端返回大數(shù)據(jù)量,若數(shù)據(jù)量過(guò)大,應(yīng)該考慮相應(yīng)需求是否合理。
7. COUNT優(yōu)化:
31) count(*) 優(yōu)于count(1)和count(primary_key)
很多人為了統(tǒng)計(jì)記錄條數(shù),就使用 count(1) 和 count(primary_key) 而不是 count(*) ,他們認(rèn)為這樣性能更好,其實(shí)這是一個(gè)誤區(qū)。對(duì)于有些場(chǎng)景,這樣做可能性能會(huì)更差,應(yīng)為數(shù)據(jù)庫(kù)對(duì) count(*) 計(jì)數(shù)操作做了一些特別的優(yōu)化。
32)count(column) 和 count(*) 是不一樣的
這個(gè)誤區(qū)甚至在很多的資深工程師或者是 DBA 中都普遍存在,很多人都會(huì)認(rèn)為這是理所當(dāng)然的。實(shí)際上,count(column) 和 count(*) 是一個(gè)完全不一樣的操作,所代表的意義也完全不一樣。
count(column) 是表示結(jié)果集中有多少個(gè)column字段不為空的記錄
count(*) 是表示整個(gè)結(jié)果集有多少條記錄
1)innodb引擎在統(tǒng)計(jì)方面和myisam是不同的,Myisam內(nèi)置了一個(gè)計(jì)數(shù)器,
Count(*)在沒(méi)有查詢條件的情況下使用 select count(*) from table 的時(shí)候,Myisam直接可以從計(jì)數(shù)器中取出數(shù)據(jù)。而innodb必須全表掃描一次方能得到總的數(shù)量
2. 但是當(dāng)有查詢條件的時(shí)候,兩者的查詢效率一致。
4. 主鍵索引count(*)的時(shí)候之所以慢
InnoDB引擎:
[1] 數(shù)據(jù)文件和索引文件存儲(chǔ)在一個(gè)文件中,主鍵索引默認(rèn)直接指向數(shù)據(jù)存儲(chǔ)位置。
[2] 二級(jí)索引存儲(chǔ)指定字段的索引,實(shí)際的指向位置是主鍵索引。當(dāng)我們通過(guò)二級(jí)索引統(tǒng)計(jì)數(shù)據(jù)的時(shí)候,無(wú)需掃描數(shù)據(jù)文件;而通過(guò)主鍵索引統(tǒng)計(jì)數(shù)據(jù)時(shí),由于主鍵索引與數(shù)據(jù)文件存放在一起,所以每次都會(huì)掃描數(shù)據(jù)文件,所以主鍵索引統(tǒng)計(jì)沒(méi)有二級(jí)索引效率高。
[3] 由于主鍵索引直接指向?qū)嶋H數(shù)據(jù),所以當(dāng)我們通過(guò)主鍵id查詢數(shù)據(jù)時(shí)要比通過(guò)二級(jí)索引查詢數(shù)據(jù)要快。
l MyAsm引擎
[1] 該引擎把每個(gè)表都分為幾部分存儲(chǔ),比如用戶表,包含user.frm,user.MYD和user.MYI。
[2] User.frm負(fù)責(zé)存儲(chǔ)表結(jié)構(gòu)
[3] User.MYD負(fù)責(zé)存儲(chǔ)實(shí)際的數(shù)據(jù)記錄,所有的用戶記錄都存儲(chǔ)在這個(gè)文件中
[4] User.MYI負(fù)責(zé)存儲(chǔ)用戶表的所有索引,這里也包括主鍵索引。
8. 優(yōu)化order by語(yǔ)句
基于索引的排序
MySQL的弱點(diǎn)之一是它的排序。雖然MySQL可以在1秒中查詢大約15,000條記錄,但由于MySQL在查詢時(shí)最多只能使用一個(gè)索引。因此,如果WHERE條件已經(jīng)占用了索引,那么在排序中就不使用索引了,這將大大降低查詢的速度。我們可以看看如下的SQL語(yǔ)句:
SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
在以上的SQL的WHERE子句中已經(jīng)使用了NAME字段上的索引,因此,在對(duì)SALE_DATE進(jìn)行排序時(shí)將不再使用索引。為了解決這個(gè)問(wèn)題,我們可以對(duì)SALES表建立復(fù)合索引:
ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME,SALE_DATE)
這樣再使用上述的SELECT語(yǔ)句進(jìn)行查詢時(shí)速度就會(huì)大副提升。但要注意,在使用這個(gè)方法時(shí),要確保WHERE子句中沒(méi)有排序字段,在上例中就是不能用SALE_DATE進(jìn)行查詢,否則雖然排序快了,但是SALE_DATE字段上沒(méi)有單獨(dú)的索引,因此查詢又會(huì)慢下來(lái)。
在某些情況中, MySQL可以使用一個(gè)索引來(lái)滿足 ORDER BY子句,而不需要額外的排序。 where條件和order by使用相同的索引,并且order by 的順序和索引順序相 同,并且order by的字段都是升序或者都是降序。例如:下列sql可以使用索引。
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下情況不使用索引:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ; --order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ;-- 用于查詢行的關(guān)鍵字與 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2 ;-- 對(duì)不同的關(guān)鍵字使用 ORDER BY :
9. 優(yōu)化GROUP BY
默認(rèn)情況下, MySQL 排序所有 GROUP BY col1 , col2 , .... 。查詢的方法如同在查詢中指定 ORDER BY col1 , col2 , ... 。如果顯式包括一個(gè)包含相同的列的 ORDER BY
子句, MySQL 可以毫不減速地對(duì)它進(jìn)行優(yōu)化,盡管仍然進(jìn)行排序。如果查詢包括 GROUP BY 但你想要避免排序結(jié)果的消耗,你可以指定 ORDER BY NULL禁止排序。
例如 :
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
10. 優(yōu)化 OR
具體詳解看:mysql or條件可以使用索引而避免全表
4. Explain解釋說(shuō)明
explain顯示了mysql如何使用索引來(lái)處理select語(yǔ)句以及連接表?梢詭椭x擇更好的索引和寫(xiě)出更優(yōu)化的查詢語(yǔ)句。
使用方法,在select語(yǔ)句前加上explain就可以了:
如:
explain select surname,first_name form a,b where a.id=b.id
分析結(jié)果形式如下:
table | type | possible_keys | key | key_len | ref | rows | Extra
EXPLAIN列的解釋:
1 table:
顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
2 type:
這是重要的列,顯示連接使用了何種類型。從最好到最差的連接類型為:system、const、eg_reg、ref、ref_or_null、 range、indexhe、 ALL。
system:表僅有一行(=系統(tǒng)表)。這是const聯(lián)接類型的一個(gè)特例
const:(PRIMARY KEY或UNIQUE)
表最多有一個(gè)匹配行,它將在查詢開(kāi)始時(shí)被讀取。因?yàn)閮H有一行,在這行的列值可被優(yōu)化器剩余部分認(rèn)為是常數(shù)。
const表很快,因?yàn)樗鼈冎蛔x取一次!
const用于用常數(shù)值比較PRIMARY KEY或UNIQUE索引的所有部分時(shí)。
在下面的查詢中,tbl_name可以用于const表:
SELECT * from tbl_name WHERE primary_key=1;
eq_reg:key
對(duì)于每個(gè)來(lái)自于前面的表的行組合,從該表中讀取一行。這可能是最好的聯(lián)接類型,除了const類型。
它用在一個(gè)索引的所有部分被聯(lián)接使用并且索引是UNIQUE或PRIMARY KEY。
eq_ref可以用于使用= 操作符比較的帶索引的列。比較值可以為常量或一個(gè)使用在該表前面所讀取的表的列的表達(dá)式。
在下面的例子中,MySQL可以使用eq_ref聯(lián)接來(lái)處理ref_tables:
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref:key
對(duì)于每個(gè)來(lái)自于前面的表的行組合,所有有匹配索引值的行將從這張表中讀取。如果聯(lián)接只使用鍵的最左邊的前綴,
或如果鍵不是UNIQUE或PRIMARY KEY(換句話說(shuō),如果聯(lián)接不能基于關(guān)鍵字選擇單個(gè)行的話),則使用ref。
如果使用的鍵僅僅匹配少量行,該聯(lián)接類型是不錯(cuò)的。
ref可以用于使用=或<=>操作符的帶索引的列。
在下面的例子中,MySQL可以使用ref聯(lián)接來(lái)處理ref_tables:
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref_or_null:Or Is null
該聯(lián)接類型如同ref,但是添加了MySQL可以專門(mén)搜索包含NULL值的行。在解決子查詢中經(jīng)常使用該聯(lián)接類型的優(yōu)化。
在下面的例子中,MySQL可以使用ref_or_null聯(lián)接來(lái)處理ref_tables:
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
range:=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN
只檢索給定范圍的行,使用一個(gè)索引來(lái)選擇行。key列顯示使用了哪個(gè)索引。
key_len包含所使用索引的最長(zhǎng)關(guān)鍵元素。在該類型中ref列為NULL。
當(dāng)使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比較關(guān)鍵字列時(shí),可以使用range:
SELECT * FROM tbl_name WHERE key_column = 10;
SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);
indexhe:
該聯(lián)接類型與ALL相同,除了只有索引樹(shù)被掃描。這通常比ALL快,因?yàn)樗饕募ǔ1葦?shù)據(jù)文件小。
當(dāng)查詢只使用作為單索引一部分的列時(shí),MySQL可以使用該聯(lián)接類型。
ALL:
對(duì)于每個(gè)來(lái)自于先前的表的行組合,進(jìn)行完整的表掃描。如果表是第一個(gè)沒(méi)標(biāo)記const的表,
這通常不好,并且通常在它情況下很差。通?梢栽黾痈嗟乃饕灰褂肁LL,
使得行能基于前面的表中的常數(shù)值或列值被檢索出。
3 possible_keys :
顯示可能應(yīng)用在這張表中的索引。如果為空,沒(méi)有可能的索引?梢詾橄嚓P(guān)的域從WHERE語(yǔ)句中
選擇一個(gè)合適的語(yǔ)句
4 key :
實(shí)際使用的索引。如果為NULL,則沒(méi)有使用索引。很少的情況下,MYSQL會(huì)選擇優(yōu)化不足的索引 。
這種情況下,可以在SELECT語(yǔ)句中使用USEINDEX(indexname)來(lái)強(qiáng)制使用一個(gè)索引或者用IGNORE INDEX(indexname)來(lái)強(qiáng)制MYSQL忽略索引
5key_len:
使用的索引的長(zhǎng)度。在不損失精確性的情況下,長(zhǎng)度越短越好
6 ref
顯示索引的哪一列被使用了,如果可能的話,是一個(gè)常數(shù)
7 rows
MYSQL認(rèn)為必須檢查的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù) (掃描行的數(shù)量)
8 Extra
該列包含MySQL解決查詢的詳細(xì)信息
關(guān)于MYSQL如何解析查詢的額外信息。將在表4.3中討論,但這里可以看到的壞的例子是Using temporary和Using filesort,
意思MYSQL根本不能使用索引,結(jié)果是檢索會(huì)很慢
extra列返回的描述的意義
Distinct:
一旦MYSQL找到了與行相聯(lián)合匹配的行,就不再搜索了
Not exists :
MYSQL優(yōu)化了LEFT JOIN,一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行, 就不再搜索了
面是一個(gè)可以這樣優(yōu)化的查詢類型的例子:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
假定t2.id定義為NOT NULL。在這種情況下,MySQL使用t1.id的值掃描t1并查找t2中的行。
如果MySQL在t2中發(fā)現(xiàn)一個(gè)匹配的行,它知道t2.id絕不會(huì)為NULL,并且不再掃描t2內(nèi)有相同的id值的行。
換句話說(shuō),對(duì)于t1的每個(gè)行,MySQL只需要在t2中查找一次,無(wú)論t2內(nèi)實(shí)際有多少匹配的行。
Range checked for each Record(index map:#)
沒(méi)有找到理想的索引,因此對(duì)于從前面表中來(lái)的每一個(gè)行組合,MYSQL檢查使用哪個(gè)索引,并用它來(lái)從表中返回行。
這是使用索引的最慢的連接之一
MySQL沒(méi)有發(fā)現(xiàn)好的可以使用的索引,但發(fā)現(xiàn)如果來(lái)自前面的表的列值已知,可能部分索引可以使用。
對(duì)前面的表的每個(gè)行組合,MySQL檢查是否可以使用range或index_merge訪問(wèn)方法來(lái)索取行。
關(guān)于適用性標(biāo)準(zhǔn)的描述參見(jiàn)7.2.5節(jié),“范圍優(yōu)化”和7.2.6節(jié),“索引合并優(yōu)化”,
不同的是前面表的所有列值已知并且認(rèn)為是常量。這并不很快,但比執(zhí)行沒(méi)有索引的聯(lián)接要快得多。
Using filesort
看到這個(gè)的時(shí)候,查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。
它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行
Using index
列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的,
這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候
Using temporary
看到這個(gè)的時(shí)候,查詢需要優(yōu)化了。這里,MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果,這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上,而不是GROUP BY上
Using where
使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行,
并且連接類型ALL或index,這就會(huì)發(fā)生,或者是查詢有問(wèn)題
Impossible WHERE noticed after reading const table...
【SQL優(yōu)化】相關(guān)文章:
oracle的sql語(yǔ)句01-21
SQL語(yǔ)句的理解原則03-30
SQL查詢語(yǔ)句大全04-25
sql語(yǔ)句的各種模糊查詢03-30
mysql SQL語(yǔ)句積累參考03-30
執(zhí)行sql原理l分析03-30
SQL中的單記錄函數(shù)03-30
PHP防止SQL注入的例子03-15
SQL語(yǔ)句中的正則表達(dá)示03-18