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 (150) 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 (18) 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) Notepad++ (9) 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) Bootstrap (2) 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) Base64 (1) Google API (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) 泰山 (1) 聊天 (1) 聚餐 (1) 開發工具 (1) 開發資訊 (1) 霸王櫻桃鴨 (1)

精選文章

初學 Java 的 HelloWorld 程式

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