mysql 語句優化
從句子中可以看到,選取的字段比較多,另外連接條件也比較多,另外還包括了子查詢。
就此語句給出幾個需要注意的問題:
1、請先使用explain,對這個語句進行分析,EXPLAIN解釋SELECT命令如何被處理。這不僅對決定是否應該增加一個索引,而且對決定一個復雜的Join如何被MySQL處理都是有幫助的。
2、盡量在連接條件多的時候,把數據提取量少的條件放在前面,這樣會減少后一個條件的查詢時間。對了,這些經常用的連接條件最好建上索引。我不清楚
INNER JOIN table_user_profile AS up ON * = *
INNER JOIN table_user_count AS uc ON * = *
INNER JOIN table_user_daren AS ud ON * = *
這些那個先內連接數據比較少,自己排列一下試一試。
3、避免使用!=或、IS NULL或IS NOT NULL、IN ,NOT IN等這樣的操作符,因為這會使系統無法使用索引,而只能直接搜索表中的數據。像in和not in這樣的關鍵字用exists和not exists比較好。* not in(SELECT uid FROM table_user_follow where f_uid=100)改成* not exists(SELECT uid FROM table_user_follow where f_uid=100),效率會有提高。
4、mysql使用函數的時候會增加負擔,完全可以交給腳本程序去解決。比如此子查詢:
SELECT MAX(share_id) FROM table_share 完全可以不寫在這個sql語句中,交給腳本程序可以了。
MySQL語句優化技巧
1、應盡量避免在 where 子句中使用!=或操作符,否則將引擎放棄使用索引而進行全表掃描。
2、對查詢進行優化,應盡量避免全表掃描,首先應考慮在 where 及 order by 涉及的列上建立索引。
3、應盡量避免在 where 子句中對字段進行 null 值判斷,否則將導致引擎放棄使用索引而進行全表掃描,如:
select id from t where num is null
可以在num上設置默認值0,確保表中num列沒有null值,然后這樣查詢:
select id from t where num=0
4、盡量避免在 where 子句中使用 or 來連接條件,否則將導致引擎放棄使用索引而進行全表掃描,如:
select id from t where num=10 or num=20
可以這樣查詢:
select id from t where num=10
union all
select id from t where num=20
5、下面的查詢也將導致全表掃描:(不能前置百分號)
select id from t where name like '?c%'
若要提高效率,可以考慮全文檢索。
6、in 和 not in 也要慎用,否則會導致全表掃描,如:
select id from t where num in(1,2,3)
對于連續的數值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
7、
如果在 where
子句中使用參數,也會導致全表掃描。因為SQL只有在運行時才會解析局部變量,但優化程序不能將訪問計劃的選擇推遲到運行時;它必須在編譯時進行選擇。然
而,如果在編譯時建立訪問計劃,變量的值還是未知的,因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描:
select id from t where num=@num
可以改為強制查詢使用索引:
select id from t with(index(索引名)) where num=@num
8、應盡量避免在 where 子句中對字段進行表達式操作,這將導致引擎放棄使用索引而進行全表掃描。如:
select id from t where num/2=100
應改為:
select id from t where num=100*2
9、應盡量避免在where子句中對字段進行函數操作,這將導致引擎放棄使用索引而進行全表掃描。如:
select id from t where substring(name,1,3)='abc'–name以abc開頭的id
select id from t where datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′生成的id
應改為:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30′ and createdate
怎么代替mysql的in函數優化速度
你這涉及到union all,or 和in 及索引字段的,,,,1、對于索引列來最好使用union all,因復雜的查詢【包含運算等】將使or、in放棄索引而全表掃描,除非你能確定or、in會使用索引。
2、對于只有非索引字段來說你就老老實實的用or 或者in,因為 非索引字段本來要全表掃描而union all 只成倍增加表掃描的次數。3、對于及有索引字段【索引字段有效】又包含非索引字段來時,按理你也使用or 、in或者union all 都可以,但是我推薦使用or、in。
上面都說的是單表的情況,所以你這個問題你給出語句信息,并不能簡單的說誰比in快的,,,,要看索引字段情況的。
MYSQL NOT IN優化
算法上存在很大問題。我們先來分析該算法的執行次數。
按照你的方法,record表中的id字段要全部查詢一遍,也就是2W次查詢,而每次查詢,最壞
情況下需要與offline_record中的*進行4W次比較,這又導致offline_record表的4W次
查詢(取rec_id )。假設滿足
a.* from record a where * not in(select *_id from offline_record);
條件的記錄一共有N條,那么,最壞情況下,該算法所做的查詢次數為:
2W(取*)+2W*4W(每取一次*就要取一次offline_*_id且offline_record的最后一條數據滿足條件)+N(每
條滿足條件的記錄需要再在record中取該記錄全部數據)
所做的比較次數為:
2W*4w
考慮最好情況下的效率,該算法所做的查詢次數為:
2W(取*)+2W*1(每取一次*就要取一次offline_*_id且offline_record的第一條數據滿足條件)+N(N(每
條滿足條件的記錄需要再在record中取該記錄全部數據)
所做的比較次數為:
2W*1
因此,該算法平均查詢次數為:
2W+(4w*2w+1)*2w/2+N ->8*10^12
天文數字!這還不考慮將近4億次的平均比較次數,所以你的執行效率當然低了
下面,我們對該算法來進行優化:
算法主要解決的問題是,取表record中id不等于offline_*_id的數據。現假定id為record的主鍵(你的問題沒有指明,但是你會看到無論id是否主鍵都不影響分析),設計算法如下:
1、取offline_*_id的結果為集合,并對該集合進行排序,設最終生成的集合為A 。則,查詢數據庫4w次,生成集合的算法按照O(N*ln N)的效率來算平均情況下比較O(4W*ln 4w),約等于64W次,排序次數按照O(N*ln N)的效率來算平均情況下比較O(4W*ln 4w),約等于64W次。
2、順序取record中的id與第一步生成的集合A進行比較,從而得出最終結果。該過程中由于*與A均為有序表,所以比較次數為2w次,查詢次數為2w+N次。
如上算法,查詢次數為 4W+2W+N=6W+N次,平均比較次數為 64W+64W+2W=130w次。
顯而易見,該算法對原算法進行了最大的優化,大概將速度提高了10*8倍。
考慮到對數據庫的查詢時間遠遠大于排序比較時間,改進厚的算法在實際操作中還會有更好的表現。
至于你對mysql查詢語句的優化,則是治標不治本之舉,雖然有用,但畢竟是微小量變,不足與影響全局,在一個壞的算法下,幾乎不能提升性能。
mysql查詢語句優化方法
not in 在大數據表的情況下效率極低(小數據表則沒問題),此時應避免使用它。
另外join連接的效率會比不指定連接類型的連接效率稍微低一點,建議修改如下: SELECT count(1) from famous_video f1,famous_teacher f2, basic_school f3 where * = * and f2.f_school_id = * and (*=4 and *_id<>38467 and *<>38468 and *<>701517) and f2.f_status =1 and f2.f_sys_type =0 and *=1;。
轉載請注明出處華閱文章網 » mysqlin語句優化