2011年12月24日 星期六

[MySQL]執行順序與效率概論

MySQL執行順序如下
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE or WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.TOP / LIMIT

如果想要知道自己SELECT語句的效率如何,可以在SELECT之前加上EXPLAIN 關鍵字
即可得知相關的執行順序及效率分析<此篇不對此做討論>

如上所列查詢語句有其執行的順序,所以要讓自己的查詢語句提昇效率就必須從這上述各點來下手,在此以例子直接說明
SELECT
         *
FROM
         user
         JOIN
                  userProfile AS pro
         ON
                  user.id = pro.userId
         JOIN
                  userAgreement AS agr
        ON
                  agr.userId = user.id
WHERE
         user.birthday = '1984-06-25'
         AND
         pro.local = '員林'

 這句SQL執行的順序是
1.FROM    user
2.ON         user.id = pro.userId
3.JOIN      userProfile AS pro
4.ON         agr.userId = user.id
5.JOIN      userAgreement AS agr
6.WHERE user.birthday = '1984-06-25'
                  AND
                  pro.local = '員林'
7.SELECT *

查詢顧名思義如同在電話簿中找尋某人的名字一樣,資料量越大、所知的條件越少查詢所需時間就越長,相對的資料量越少、知道的條件越明確查詢所需的時間就越短。

那麼在這樣的執行順序下我們要怎麼讓資料量縮小以加快速度呢?
照執行順序來走
1.先碰到的就是user這個table,這個table相對於其他兩個table是否資料量最少?以此例討論的確是如此,因為user對userProfile、userAgreement都是一對多的關係,當然以user當開頭是一個好開頭。

2.接下來限制條件,以明確的條件來限制、以及少量的資料來比對大量的資料,自然就能保持較佳的查詢效率了。

3.有了明確條件以及少量資料比對的限制,JOIN table效率自然會比較好。

4.5.如上兩點所言,越明確的條件以及少量資料的比對,效率會更好,所以這裡值得注意的是ON         agr.userId = user.id  以大量的資料去比對少量的資料,這是反其道而行會適得起反降慢速度,ON   user.id = agr.userId 如果改成這樣就會變成以少量資料去比對大量資料,則又可獲得較佳的效率了。

6.當上述的TABLE都JOIN完成之後,再用WHERE來做整體的條件限制,一直以來都是這麼做的沒錯吧~那就錯大了!!如果條件可以在上面ON的時候就限制,資料量便可再更縮小,JOIN出來的TABLE就會很小甚至精準到需求的資料,有時候根本連WHERE都不用寫,那該怎麼寫呢?稍後將會把改完的SQL寫在下方給大家參考。

7.JOIN完TABLE,限制條件也結束了,才開始SELECT,此時會將使用者所需要的資料一一列出,大家都會用*來簡便的將一切列出,這也是降低效率的兇手之一,SELECT越明確,越少的欄位會讓整體速度稍微加快,因此在SELECT部份也盡量少濫用*,可獲得較佳的效率

透過上述幾點,將修改後的SQL寫出如下︰
SELECT
         user.id,
         user.username,
         pro.phone,
         agr.agreementEndDate
FROM
         user
         JOIN
                  userProfile AS pro
         ON
                  user.birthday = '1984-06-25'
                  AND
                  pro.local = '員林'
                  AND
                  user.id = pro.userId
         JOIN
                  userAgreement AS agr
         ON
                  agr.userId = user.id

如上所示,在第一次ON的條件限制下就將資料量降到最少,甚至精準,如此往後JOIN就會讓資料量大為減少,最後連WHERE都不需要額外限制其他條件,而SELECT部份也只挑出所需的資料就好, 達到最佳的效率

總結︰資料量越小、條件越明確就能達到最好的查詢效率















其它文章

標籤

Oracle (149) Oracle DB (144) Oracle_DB (143) Oracle SQL (135) JAVA (82) css-基本類 (65) CSS Selector (58) MySQL (58) jQuery (49) JavaScript-基本類 (39) JavaScript (37) JavaScript HTML DOM (37) JavaScript-HTML_DOM (36) CSS3 (30) JAVA-基本類 (28) jQuery UI (27) Apache (23) Oracle GROUP BY (20) datepicker (20) Android (17) Oracle Date (17) c (17) JAVA-lang套件 (16) Linux (16) Oracle Sub Query (16) Spring-基本類 (16) jQuery-基本類 (16) MySQL-進階系列教學 (15) Android基本類 (14) Grails (14) Oracle join (14) SQLite (13) Spring (13) WIN7-基本類 (13) grails-基本類 (13) CKEditor (12) JAVA-流程控制類 (12) JAVA_Spring (12) PHP (11) linux cent os (11) MySQL-基本系列教學 (10) SQLite for java (10) Windows (10) c/c++ (10) eclipse (9) jQuery-Selector (9) sqldeveloper (9) DB_Toad (8) JAVA_IDE_Eclipse (8) JavaScript-String類 (8) MySQL DB Toad (8) MySQL-DATE相關 (8) MySQL-函式相關 (8) Spring Bean (8) Android Studio (7) Hibernate (7) JAVA-OCWCD (7) JavaScript-陣列類 (7) JAVA-程式分享 (6) JAVA.util套件 (6) JavaScript-數學類 (6) MinGw (6) MySQL-其它類 (6) Spring MVC (6) Apache_Tomcat (5) Apache套件_POI (5) CSS (5) JavaScript-Date物件 (5) JavaScript-其它類 (5) PostgreSQL (5) httpd (5) log4j (5) 基本資訊 (5) CSS Properties (4) Dev-C++ (4) Oracle DDL (4) Servlet (4) apache_Maven (4) Android NDK (3) Eclipse IDE for C/C++ (3) HTML5 (3) Hibernate-基本類 (3) JAVA-問題 (3) JAVA-綀習分享 (3) Linux 指令 (3) Proxy Server (3) Spring Mobile (3) Squid (3) maven (3) zk (3) 生活其它 (3) JAVA_IO (2) JAVA_其它_itext套件 (2) JBoss-問題 (2) Jboss (2) MySQL-語法快速查詢 (2) Spring AOP (2) Spring Batch (2) Spring Boot (2) Spring i18n (2) Subversive (2) Tomcat 8 (2) UML (2) c++ (2) c語言綀習題 (2) jQuery Mobile (2) jQuery-事件處理 (2) jQuery-套件類 (2) svn (2) weblogic (2) Apache_JMeter (1) Apache套件_BeanUtils (1) Apache套件_StringUtils (1) HTML5-基本類 (1) JAVA 7 (1) JAVA 日期 (1) JAVA-OCJP (1) JAVA-WEB (1) JAVA_IDE (1) JAVA其它 (1) JBoss Server (1) JMX (1) JSP (1) Java RMI (1) Java String (1) Joda Time (1) Linux_其它 (1) MySQL教學 (1) Oracle_VirtualBox (1) SQL Server (1) SWT (1) Session (1) Struts 2 (1) Tool (1) TortoiseSVN (1) ZK Studio (1) csv (1) grails-其它類 (1) jQuery-進階 (1) java mail (1) java web (1) jsoup (1) modules (1) tomcat (1) 其它類 (1) 圖片工具 (1) 開發工具 (1) 開發資訊 (1)

精選文章

初學 Java 的 HelloWorld 程式

撰寫一個JAVA程式 public class HelloWorld{ public static void main(String[ ] args){ System.out.println("我第一支Java程式!!"); } } ...