網路技術趨勢觀測站

2014年1月4日星期六

IDE v.s. 程式語言

如果你問 C# 開發者：「你使用哪一套 IDE？」，我想答案應該都是 Visual Studio；如果你問 Objective-C 的開發者同樣的問題，答案應該都是 Xcode。

可是，如果你問 Java 的開發者，那得到的答案有可能是 Eclipse、NetBeans、IntelliJ IDEA，甚至是 JBuilder...等等。

IDE 對程式語言的發展與社群具有決定性的影響。我甚至覺得像 .Net 或 Objective-C 這樣只有一種 IDE 可以選擇反而是比較好的，對於該程式語言的發展、普及、社群也比較會有正面幫助。為什麼？我的答案是「一致」。

試想在一個 Java 開發團隊裡，如果沒有強制規定大家只能使用某種 IDE，不僅開發者之間無法共享許多使用 IDE 的知識、經驗、技巧之外，開發工具的安裝設定組態以及 plugins 也都不能通用，如果成員要進行 pair programming 更是痛苦，導致整個團隊裡的每個人浪費了過多時間在熟悉、適應、比較甚至爭論各種 IDE 的好壞。

所以，有時候選擇太多也不一定是件好事....

2013年8月17日星期六

Clean Code 讀後感想

重點如下：

1. Clean Code 追求的層次與境界比 Coding Rule 和 Coding Style 高，特別是程式碼的可讀性與易理解性，所以有講到程式碼的整體結構與佈局。

2. PG 的英文一定要很好，因為在這個指導原則下，寫 code 其實就好像是在用英文寫文章了（語境、語法、格式、段落、措辭、編排...等等都要考慮到）。

3. 要懂什麼是 S.O.L.I.D 五大原則，也要知道一些 Design Patterns（所以你應該也已經懂 UML 了）。

4. 也要懂 Refactoring、TDD、AOP...等等概念，所以建議搭配 Kent Beck 和 Martin Fowler 和其他一些大師的理念一起服用，效果會更佳。

5. 本書的舉例和引用都是 Java，所以比較適合 Java Programmer 閱讀，其他語言未必完全適用（除了像一些通則以外，如命名、註解、編排...等）。

6. 這本書至少要看過兩次以上，也要不停地練習、自我檢討才能內化為直覺反應。（也可能是我資質比較駑鈍吧，呵⋯）

以上都只是「專業知識技能」的部分，基本上還可以籍由後天的培訓來補足。這樣才可以說「慬」Clean Code。

但是真正要「做到」Clean Code，我覺得主要還是取決於個人的道德良知與自我嚴格要求，而這部分會比較偏人格特質的方面（沒錯，最好是有潔癖）。

若要 enforce 至公司內部 PG 團隊，則團隊裡的每個人都必須符合上述條件。否則，總是有人會一再地把 code 弄髒。此外，平時彼此之間要互相監督、提醒才行。

那如果要 enforce 至外包 PG 團隊呢？我覺得這就滿困難的了。因為在這種情況下，公司不太可能會對外包人員施以教育訓練，也無從了解其人格特質。

若公司派一個具備 Clean Code 能力的人全時間去督導呢？這不僅不符合成本效益，也是比較消極被動的控管方式。況且業界的驗收標準也尚未有所謂的 Clean Code 標準可茲依循遵守。

（外包：我不是只要功能、效能、資安都達到要求就好了嗎？怎麼現在還要要求我寫的 Code 是夠 Clean 呀!?）

2013年8月3日星期六

將 DocBook 轉換成 PDF 格式

這是上一篇文章「DocBook 的 Line Numbering 和 Code Highlighting」的續集，今天我們要將 DocBook 輸出為 PDF 格式，但在這邊要先提醒一下：Apache FOP 似乎不認得 xslthl 轉出來的 tags，所以如果你想將 DocBook 輸出成 PDF，可能就要先放棄 Code HighLighting 的功能。小弟自己在嘗試過多次以後，已經宣告放棄了...XD

我本身使用的 OS 是 Windows 7 Pro 64bit，JVM 版本則是 Oracle JDK 1.6.0_43，建議您盡量採用和我一樣的環境與軟體版本，以免遭遇到其他問題。

接下來進入正題，開始說明轉換的步驟：

一、FOP 的取得、安裝及設定

請到「http://archive.apache.org/dist/xmlgraphics/fop/binaries/」下載 fop-1.0-bin.zip，因為我只試過這個版本，而且沒遇到什麼問題，所以建議您跟我採用一樣的版本吧！

跟上一篇文章一樣，也請解壓縮在「D:\docbook」下方，所以這個資料夾下方應該要長的像下面這樣才對：

docbook-xsl-1.78.1\

fop-1.0\
output\

saxon6-5-5\

tools\

xerces-2_11_0\

xslthl-2.1.0\

my_article.xml

my_article.xsl

my_code.java

接下來，請修改「D:\docbook\fop-1.0\conf\fop.xconf」的內容，在「renders/render/fonts」區段加入如下內容（ START 到 END 之間），讓 FOP 可以使用你系統內的字型（包含中文字型），這部分我是參考「Docbook开发手记」的：

<fop version="1.0">
    ...
  <renderers>
    <renderer mime="application/pdf">
        ...
      <fonts>
        ...
        
        <!-- START -->
         <!-- register all the fonts found in a directory and all of its sub directories (use with care) -->
         <directory recursive="true">file:///c:/windows/fonts/</directory>

         <!-- automatically detect operating system installed fonts -->
         <auto-detect/>
        <!-- END -->
      </fonts>

二、修改 my_article.xsl：

請將 my_article.xsl 替換成如下的內容：

<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:saxon="http://icl.com/saxon"
                extension-element-prefixes="saxon"
                version='1.0'>
 
<xsl:import href="file:///D:/docbook/docbook-xsl-1.78.1/fo/docbook.xsl"/>
 
<!--============================================================================
line numbering
=============================================================================-->
<xsl:param name="linenumbering.everyNth">1</xsl:param>
<xsl:param name="linenumbering.width">4</xsl:param>
<xsl:param name="linenumbering.separator"><xsl:text> </xsl:text></xsl:param>

<!--============================================================================
font setting
=============================================================================-->
<xsl:param name="title.font.family">Microsoft JhengHei</xsl:param>
<xsl:param name="body.font.family">Microsoft JhengHei</xsl:param>
<xsl:param name="monospace.font.family">MingLiU</xsl:param>
<xsl:param name="symbol.font.family">Cambria Math</xsl:param>

<!-- add bookmark and support more type of figure -->
<xsl:param name="fop1.extensions">1</xsl:param>

<!-- no indent for body content -->
<xsl:param name="body.start.indent">0pt</xsl:param>

</xsl:stylesheet>

三、使用以下指令進行轉換（在「D:\docbook」下面）：

1. 先使用 Saxon 或 Xalan 來產生 fo 檔：

Saxon：

java -cp "D:\docbook\saxon6-5-5\saxon.jar;D:\docbook\docbook-xsl-1.78.1\extensions\saxon65.jar" com.icl.saxon.StyleSheet -o output\my_article.fo my_article.xml my_article.xsl use.extensions=1

Xalan：

java -cp "D:\docbook\tools\xalan.jar;D:\docbook\xerces-2_11_0\xml-apis.jar;D:\docbook\xerces-2_11_0\xercesImpl.jar;D:\docbook\docbook-xsl-1.78.1\extensions\xalan27.jar" org.apache.xalan.xslt.Process -OUT output\my_article.fo -IN my_article.xml -XSL my_article.xsl -L -PARAM use.extensions 1

產生的 fo 檔案位於「D:\docbook\output\my_article.fo」。

2. 再以 fop.bat 指令將 fo 檔轉成 PDF 檔：

fop-1.0\fop.bat -c d:\docbook\fop-1.0\conf\fop.xconf -fo output\my_article.fo -pdf my_article.pdf

產生的 PDF 檔案在「D:\docbook\my_article.pdf」。在執行的過程中，我是有看到一些錯誤訊息（請參考本文最下方），但是最終產生的 PDF 看起來是正常的，所以應該沒什麼關係。

我這邊轉出來的檔案就像下面這個樣子，希望你也成功了。

my_article.pdf

和上一篇文章遭遇的問題一樣，Xalan 在轉換外部檔案「my_code.java」時，即使有在 <textdata> 標籤加上「encoding="UTF-8"」屬性，中文（在註解的位置）仍會變成亂碼，但 Saxon 就不會。

如果您還想更深入瞭解 DocBook ，建議再參考以下這些網站：

總結來說，DocBook 確實是一套很不錯的文件格式標準（可以轉換成不同的文件格式），個人認為比較可惜的是：(1) 許多相關的文件與資料都已經很舊、過時了，(2) 相關的社群似乎也不再熱絡了。所以每次遇到問題時、要找答案或解決方式，都很困難...

附錄、執行 FOP 時看到的錯誤訊息

D:\docbook>fop-1.0\fop.bat -c d:\docbook\fop-1.0\conf\fop.xconf -fo output\my_ar
ticle.fo -pdf my_article.pdf
2013/8/3 下午 04:53:55 org.apache.fop.apps.FopFactoryConfigurator configure
資訊: Default page-height set to: 11in
2013/8/3 下午 04:53:55 org.apache.fop.apps.FopFactoryConfigurator configure
資訊: Default page-width set to: 8.26in
2013/8/3 下午 04:53:56 org.apache.fop.events.LoggingEventListener processEvent
警告: Font "Cambria Math,normal,700" not found. Substituting with "Cambria Math,
normal,400".
2013/8/3 下午 04:53:57 org.apache.fop.hyphenation.Hyphenator getHyphenationTree
嚴重的: Couldn't find hyphenation pattern zh_tw
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: This is a TrueType collection file with 3 fonts
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: Containing the following fonts:
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: MingLiU <-- selected
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: PMingLiU
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: MingLiU_HKSCS
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: This is a TrueType collection file with 3 fonts
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: Containing the following fonts:
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: MingLiU <-- selected
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: PMingLiU
2013/8/3 下午 04:53:57 org.apache.fop.fonts.truetype.TTFFile checkTTC
資訊: MingLiU_HKSCS

2013年7月31日星期三

DocBook 的 Line Numbering 和 Code Highlighting

最近在製作 DocBook 文件時，遇到了「Line Numbering」和「Code Highlighting」問題，花了三天的時間終於解決，特別筆記在此紀錄一下解決的步驟。

首先，說一下前置作業：

取得「docbook-xsl」。從 SourceForge下載「docbook-xsl-1.?.?.zip」，請特別留意版本號！不要太新、也不要太舊，網址在：http://sourceforge.net/projects/docbook/files/docbook-xsl/ ，我自己下載的是「docbook-xsl-1.78.1.zip」。
取得 XSLT 1.0 proccessor —— Saxon 或 Xalan，也一樣請留意版本號：

Saxon：請從 http://saxon.sourceforge.net/#F6.5.5 下載，取得 Saxon 6.5.5 就好了，我自己下載的是「saxon6-5-5.zip」。
Xalan：它被包在 Xerces2 Java 裡了，請從 http://xerces.apache.org/mirrors.cgi#binary
下載 Binary 和 Tools，我自己下載的是「Xerces-J-bin.2.11.0.zip」和「Xerces-J-tools.2.11.0.zip」。

取得 XSLT Syntax Highlight —— xslthl，請從 http://sourceforge.net/projects/xslthl/ 下載，我自己下載的是「xslthl-2.1.0-dist.zip」。
下載完上述檔案後，把它們解開在「D:\docbook」下方，也就是像這樣：

docbook-xsl-1.78.1\
saxon6-5-5\
tools\
xerces-2_11_0\
xslthl-2.1.0\

閱讀「DocBook XSL: The Complete Guide 4th Edition」的這兩篇：

此外，要提醒的是：只有「Saxon」和「Xalan」才支援「Line Numbering」和「Code Highlighting」這兩項功能， xmltproc 並不支援喔！

接著，請準備好以下三個檔案（其內容列於本文底部），並盡量都使用一樣的編碼（如 UTF-8），也放到 D:\docbook 下面：

my_article.xml：DocBook 文件。
my_article.xsl：DocBook XSL 樣式檔。
my_code.java：一段 Java 程式碼。

然後，在 D:\docbook 下執行以下指令，就可將 DocBook 格式轉換為 HTML 格式了：

如果你是用 Saxon 的話：

java -cp "D:\docbook\xslthl-2.1.0\xslthl-2.1.0.jar;D:\docbook\saxon6-5-5\saxon.jar;D:\docbook\docbook-xsl-1.78.1\extensions\saxon65.jar" -Dxslthl.config="file:///D:/docbook/docbook-xsl-1.78.1/highlighting/xslthl-config.xml" com.icl.saxon.StyleSheet -o output\my_aritcle.html my_article.xml my_article.xsl use.extensions=1

如果你是用 Xalan 的話：

java -cp "D:\docbook\xslthl-2.1.0\xslthl-2.1.0.jar;D:\docbook\tools\xalan.jar;D:\docbook\xerces-2_11_0\xml-apis.jar;D:\docbook\xerces-2_11_0\xercesImpl.jar;D:\docbook\docbook-xsl-1.78.1\extensions\xalan27.jar" -Dxslthl.config="file:///D:/docbook/docbook-xsl-1.78.1/highlighting/xslthl-config.xml" org.apache.xalan.xslt.Process -OUT output\my_article.html -IN my_article.xml -XSL my_article.xsl -L -PARAM use.extensions 1

轉換完的 my_article.html 內容就像下面這個樣子：

加上行號與程式碼 highlight 的 my_article.html

最後，我發現一個問題，就是 Xalan 在轉換外部檔案「my_code.java」時，即使有在<textdata> 標籤加上「encoding="UTF-8"」屬性，中文（在註解的位置）會變成亂碼，但 Saxon 就不會。

至於其原因，我暫時沒時間去探究。因此，如果你也遇到同樣的問題，建議就先改用 Saxon 吧！（或者幫忙查一下原因，並回饋給小弟，:-p）

前面所提到的三個檔案內容如下：

1. my_article.xml：

<?xml version='1.0' encoding="UTF-8"?>
<!DOCTYPE article PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN" "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd">

<article lang="zh_tw">
  <title>快來測試 Line Numbering 和 Code Highlighting 的功能吧！</title>
  <sect1>
    <title>my_code.java</title>
      <programlisting linenumbering="numbered" startinglinenumber="1" language="java"><?dbhtml linenumbering.everyNth="1" linenumbering.separator=" " linenumbering.width="4"?><?dbfo linenumbering.everyNth="1" linenumbering.separator=" " linenumbering.width="4"?><textobject><textdata fileref="file:///D:/docbook/my_code.java" encoding="UTF-8" /></textobject></programlisting>        
  </sect1>
</article>

2. my_article.xsl：

<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:saxon="http://icl.com/saxon"
                extension-element-prefixes="saxon" 
                version='1.0'>

<xsl:import href="file:///D:/docbook/docbook-xsl-1.78.1/html/docbook.xsl"/>
<xsl:import href="file:///D:/docbook/docbook-xsl-1.78.1/html/highlight.xsl"/>

<xsl:output method="html" 
            encoding="UTF-8"
            indent="yes"
            saxon:character-representation="native;decimal"/>
<!--============================================================================
Code Line Numbering & Highlighting Global Settings
=============================================================================-->
<xsl:param name="linenumbering.extension" select="1"></xsl:param>
<xsl:param name="linenumbering.everyNth">1</xsl:param>
<xsl:param name="linenumbering.width">4</xsl:param>
<xsl:param name="linenumbering.separator"><xsl:text> </xsl:text></xsl:param>
<xsl:param name="highlight.source" select="1"></xsl:param>
</xsl:stylesheet>

3. my_code.java：

/**
 * 使用「Adapter」Design Pattern 
 */
abstract class HumanWorkerAdpater implements IWorker {
   public void work();
   public void eat();
}
abstract class RobotWorker implements IWorker {
   public void work();
}
IWoker HumanWorker = new HumanWorkerAdpater() {
   public work() {
      // working....
   }
   public eat() {
      // eating....
   }
}
IWoker RobotWorker = new RobotWorkerAdpater() {
   public work() {
      // working....
   }
}

2013年7月28日星期日

SQLite 學習筆記之六 - Query Planner

※ 本文主要是 SQLite 官方網頁「The SQLite Query Planner」的翻譯整理，希望能對大家有幫助！

依據 SQL statement 與 DB schema 的複雜度，同一個 SQL statement 的處理方式可能會有很多種。而 Query Planner 的任務便是在多個方案之中找出一個 Disk I/O 與 CPU 開銷最小的方案。

一、WHERE 子句分析

如果查詢中的「WHERE」子句包含了「AND」運算元，SQLite 會以「AND」為分隔，將其拆解為更細的條件式（term）。如果 WHERE 包含了「OR 」運算元，則整個子句都會被視為一個條件式。
SQLite 會分析 WHERE 字句裡的條件式是否可利用「索引」來輔助查詢？

如果完全沒有索引可資利用，則 SQLite 就會對表格進行逐列檢驗（table scan）。
如果完全依賴索引就可完成查詢，則 SQLite 就不會對表格做任何檢驗（如：覆蓋式索引 / covering index）。
有時候，雖然有一個或多個條件式可利用索引來輔助查詢，但 SQLite 仍須對表格進行逐列檢驗。

SQLite 對條件式進行分析以後，可能會在 WHERE 子句中又產生新的「虛擬條件」（virtual term）。虛擬條件可和索引一起使用，而且虛擬條件絕不會對原始表格進行表格掃瞄。

若要讓 SQLite 使用索引，則條件式必須符合以下形式之一（※ 注意：沒有「<>」！）：

column = expression
column > expression
column >= expression
column < expression
column <= expression
expression = column
expression > column

expression >= column
expression < column
expression <= column
column IN (expression-list)
column IN (subquery)
column IS NULL

請先建立如下的表格與「多欄位索引」（此索引也算「覆蓋式索引」）：

CREATE TABLE ex1(a, b, c, d);

CREATE INDEX idx_ex1 ON ex1(a, b, c, d);
如果多欄位索引的起始欄位有出現在 WHERE 子句之中，那麼 SQLite 就可能會使用這個索引，如以下查詢就是從起始欄位 a 開始、然後接著是欄位 b：

sqlite> EXPLAIN QUERY PLAN SELECT * FROM ex1 WHERE a=1 AND b=1;

0|0|0|SEARCH TABLE ex1 USING COVERING INDEX idx_ex1 (a=? AND b=?) (~9 rows
但如果條件式使用的欄位之間有縫隙（gap），SQLite 就只會使用縫隙之前的欄位。如下查詢的條件式中使用了 a、b、d 欄位，但卻未使用「c」欄位：

sqlite> EXPLAIN QUERY PLAN SELECT * FROM ex1 WHERE a=1 AND b=1 AND d=1;

0|0|0|SEARCH TABLE ex1 USING COVERING INDEX idx_ex1 (a=? AND b=?) (~2 rows)
承上，索引起始欄位只能和「=」或「IN」的運算元一起使用，否則 SQLite 是不會使用到右方欄位（b, c, d）的，例如：

sqlite> EXPLAIN QUERY PLAN SELECT * FROM ex1 WHERE a>1 AND b=1;

0|0|0|SEARCH TABLE ex1 USING COVERING INDEX idx_ex1 (a>?) (~25000 rows)
承上，但有一個例外狀況，亦即起始欄位使用「=」或「IN」，而靠右方欄位是使用不相等條件式（>, >=, <, <=），例如：

sqlite> EXPLAIN QUERY PLAN SELECT * FROM ex1 WHERE a=1 AND b>1;

0|0|0|SEARCH TABLE ex1 USING COVERING INDEX idx_ex1 (a=? AND b>?) (~2 rows)

sqlite> EXPLAIN QUERY PLAN SELECT * FROM ex1 WHERE a=1 AND b=1 AND c>1;

0|0|0|SEARCH TABLE ex1 USING COVERING INDEX idx_ex1 (a=? AND b=? AND c>?) (~2 rows)

sqlite> EXPLAIN QUERY PLAN SELECT * FROM ex1 WHERE a=1 AND b=1 AND c=1 AND d>1;

0|0|0|SEARCH TABLE ex1 USING COVERING INDEX idx_ex1 (a=? AND b=? AND c=? AND d>?) (~2 rows)
承上，但是靠右方欄位最多只能使用到兩個不等條件式：

sqlite> EXPLAIN QUERY PLAN SELECT * FROM ex1 WHERE a=1 AND b>1 AND b<5;

0|0|0|SEARCH TABLE ex1 USING COVERING INDEX idx_ex1 (a=? AND b>? AND b<?) (~1 rows)

以下是「索引條件式」的一些例子：

... WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'
多欄位索引中的 4 個欄位（a, b, c, d）皆會被 SQLite 使用，因為這些條件式是從索引的起始欄位開始、中間沒有縫隙，而且都是相等條件式（equality constraints ）。
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'
多欄位索引中只有 a, b 和 c 欄位會被 SQLite 使用、d 欄位不會被使用，因為 d 在索引中是在 c 的右邊、而且 c 又是使用不相等條件式。
... WHERE a=5 AND b IN (1,2,3) AND d='hello'
多欄位索引中只有 a 和 b 兩個欄位會被 SQLite 使用，d 欄位不會被使用，因為少了 c 欄位的條件式，所以產生了縫隙。
... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'
SQLite 完全不會使用這個多欄位索引，因為沒有 a 欄位的條件式，而 a 欄位是索引中最左邊的欄位，此查詢將造成「全表掃瞄」（full table scan）。
... WHERE a=5 OR b IN (1,2,3) OR c NOT NULL OR d='hello'
SQLite 完全不會使用這個多欄位索引，因為條件式都是以「OR」連接、而非以「AND」連接。此查詢將造成「全表掃瞄」。但是如果 b, c, d 這三個欄位自己也有自己個別的索引，則 SQLite 可能會採用「OR 條件式優化」的方式（或 OR-by-UNION）。

二、BETWEEN 優化

假設某 WHERE 子句的條件式如下：

expr1 BETWEEN expr2 AND expr3
則 SQLite 可能會自動加入如下的「虛擬條件式」（virtual terms）（由一個 BETWEEN 轉換為 >= 和 <=）：

expr1 >= expr2 AND expr1 <= expr3
如果這兩個虛擬條件式最後有都被成功轉為「索引條件式」（如：BETWEEN 1 AND 3 轉換為 a >= 1 AND a <= 3），那麼 SQLite 就會忽略原來的 BETWEEN 條件式，而且也不需要對原表格進行相關的逐列檢驗了（table scan）。
如果 BETWEEN 條件式沒辦法被轉換為索引條件式，而必須要對原表格進行逐行掃瞄時，而 expr1 也只會被評估一次。

三、OR 優化

SQLite 共有兩種 OR 優化方法：「將多個 OR 轉換為一個 IN」與「OR-by-UNION」。

1. 將多個 OR 轉換為一個 IN

如果 WHERE 子句中以「OR」連接的條件式像下面這樣包含了多個子條件式（subterm），而這些子條件式都是同一個欄位並以多個 OR 連接：

column = expr1 OR column = expr2 OR column = expr3 OR ...

那麼該條件式就會重寫成像下面這個樣子（將多個 OR 轉換為一個 IN）：

column IN (expr1,expr2,expr3,expr4,...)
重寫過後的條件式如果轉為「索引條件式」，那 SQLite 就能利用索引來提升查詢效率。注意在每個以 OR 連接的子條件式裡的欄位都必須是同一個欄位，而欄位可以在「=」運算子的左邊或右邊。

2. OR-by-UNION

子條件式有可能是像「a=5」或「x>y」這樣的單一運算式，也有可能是 LIKE 或 BETWEEN 運算式，或是在一對刮號內又包含了以 AND 連接的子條件式…等等。為了要確定這些子條件式是否都能被轉換成「索引條件式」，SQLite 會把每一個子條件都視為一個完整的 WHERE 子句進行分析。
如果分析的結果是每個 OR 子條件式是對應到不同的索引上，SQLite 就會像下面這樣來處理查詢（OR-by-UNION）：

rowid IN (SELECT rowid FROM table WHERE expr1

UNION SELECT rowid FROM table WHERE expr2

UNION SELECT rowid FROM table WHERE expr3)
上一頁重寫過後的表示式只是概念性的表示式。實際上包含 OR 限制式的 WHERE 子句並不是真的會被重寫成這個樣子。SQLite 內部的實作方式會使用比子查詢更有效率的機制（即使原表格中的「ROWID」欄位名稱因其他用途被重載，而不再指向真正的 ROWID了）。
簡言之，就是先用多個索引找出每個 OR 條件式的中間結果列（candidate result）以後，再將這些列使用 UNION 操作求出最終結果列。

3. 小結

在大部分的狀況下，SQLite 對 FROM 子句中的一個表格只會使用單一個索引，但上述的第二種 OR 優化方法「OR-by-UNION」則是其中的例外。在此情況下，SQLite 可能會針對個別 OR 子條件式採用不同的索引。
SQLite 並不一定會對任何查詢都採用上述的 OR 優化方法。SQLite 的 query planner 是以成本為基礎的（cost-based），所以會在評估各計畫可能消耗的 CPU 和磁碟 I/O 成本之後，再從中選擇最快的計畫。
如果 WHERE 子句中的 OR 子條件式很多、或者個別 OR 子條件式中的索引並不是那麼有利用價值，那 SQLite 可能會決定採用不同的演算法，甚至進行全表掃瞄。
應用程式開發人員可以在 SQL 前面加上「EXPLAIN QUERY PLAN」，以對 SQLite 選定的查詢計畫取得比較高層次的瞭解。

四、LIKE 優化

LIKE 或 GLOB 運算子有時候也可以用來限縮索引，但使用上有如下諸多限制：

LIKE 或 GLOB 左邊必須是具備「TEXT 親和性」（affinity）的索引欄位。
LIKE 或 GLOB 右邊必須是「字串」或「字串參數」（parameter），但開頭不能是萬用字元。
LIKE 運算子不能出現 ESCAPE 運算子。
用來實作 LIKE 和 GLOB 的內建函式必須未被 sqlite3_create_function() API 重載過（overload）。
如果是 GLOB 運算子，索引欄位必須使用內建的「BINARY」整理序列（collation sequence）。
如果是 LIKE 運算子：

在「case_sensitive_like」模式開啟的狀況下，索引欄位就必須是「BINARY」整理序列（collation sequence），此時對於 latin1 字元（ASCII 編碼 127 以下）是區分大小寫的，例如「'a' LIKE 'A'」的結果為 true。
在「case_sensitive_like」模式關閉的狀況下，索引欄位就必須是「 NOCASE」整理序列，此時對於 latin1 字元是忽略大小寫的，例如「‘a’ LIKE ‘A’」的結果為 false。
國際性編碼在 SQLite 中都是 case-sensitive 的，除非 AP 自行定義了整理序列或者在 like() 函式使用了非 ASCII 字元。

更多詳細資訊請參考 SQLite 官方網頁「The SQLite Query Planner – 4.0 The Like optimization」一節。

五、JOIN

SQLite 在進行「WHERE 子句分析」之前，INNER JOIN 裡的 ON 和 USING 子句都會被轉換成 WHERE 子句中的限制式。因此，使用較新的 SQL92 JOIN 語法並不會比使用 SQL89 以逗號分隔的 JOIN 語法得到較多好處。兩者最終都能達到同樣的目的。

如果是 LEFT OUTER JOIN，狀況會比較複雜，以下兩個查詢是不相等的；如果是 INNER JOIN，以下兩個查詢會是相等的：

SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.y;

SELECT * FROM tab1 LEFT JOIN tab2 WHERE tab1.x=tab2.y;

SQLite 會對 OUTER JOIN 中的 ON 和 USING 子句進行特殊處理：如果 JOIN 右方的表格是在空列上（null row），則 ON 和 USING 子句裡的限制式就不適用；但如果是在 WHERE 子句中就適用。

SQLite 會把 LEFT JOIN 的 ON 或 USING 子句放入 WHERE 子句之中，最後的結果就是被其轉換為一般的 INNER JOIN — 儘管 INNER JOIN 的執行速度會比較慢。

1. 表格 JOIN 順序

目前 SQLite 只使用迴圈式 JOIN。也就是說，在 SQLite 內部，JOIN 其實是以「巢狀迴圈」（nested loops）實作的。
預設的巢狀迴圈 JOIN 順序是：FROM 子句最左邊的表格會成為外層迴圈（outer loop）、而最右邊的表格式則會成為內層迴圈（inner loop）。
改變巢狀迴圈的順序如果能幫助 SQLite 選擇更好的索引，那 SQLite 可能就會這麼做。

INNER JOIN 的迴圈順序可能會被 SQLite 改變。
LEFT OUTER JOIN 的迴圈順序絕不會被 SQLite 改變（因為改變以後，意義就不同，結果也會不一樣了）。
OUTER JOIN 左右兩邊的 INNER JOIN 的迴圈順序也可能會被 SQLite 改變（如果 SQLite 認為這是有利的），但 OUTER JOIN 還是會依照其出現的先後順序來運算。

當選擇 JOIN 子句中的表格順序時，SQLite 會使用貪婪的演算法（greedy algorithm） ，其時間複雜度為 O(N²) 。正因如此，SQLite 可以對 50 ~ 60 種連接方式的 JOIN 進行有效的規劃。
改變 JOIN 順序是自動進行的，而且通常運作良好，所以程式設計人員不須為此費心，特別是當有用「ANALYZE」指令來蒐集索引的統計資訊時。但偶爾還是需要程式設計師提供一些線索。
考慮以下的 schema：

CREATE TABLE node(
     id INTEGER PRIMARY KEY,
     name TEXT
);
CREATE INDEX node_idx ON node(name);
CREATE TABLE edge(
     orig INTEGER REFERENCES node,
     dest INTEGER REFERENCES node,
     PRIMARY KEY(orig, dest)
);
CREATE INDEX edge_idx ON edge(dest,orig);
以上的 schema 定義了一個有向圖（directed graph）的資料結構，並儲存了每一個點（node）的名稱（name）。現在再考慮以下的 INNER JOIN 查詢：

SELECT *
    FROM edge AS e,
         node AS n1,
         node AS n2
   WHERE n1.name = 'alice'
     AND n2.name = 'bob'
     AND e.orig = n1.id
     AND e.dest = n2.id;
以上查詢希望找出所有從節點「alice」到節點「bob」的邊。對此，SQLite 可能會有兩個方案（事實上會有 6 種不同的方案，但是在這邊我們只要考慮這兩個就好了）。虛擬碼（pseudocode）如下：

方案 1：
foreach n1 where n1.name='alice' do:
    foreach n2 where n2.name='bob' do:
      foreach e where e.orig=n1.id and e.dest=n2.id
        return n1.*, n2.*, e.*
      end
    end
end
方案 2：

foreach n1 where n1.name='alice' do:
    foreach e where e.orig=n1.id do:
      foreach n2 where n2.id=e.dest and n2.name='bob' do:
        return n1.*, n2.*, e.*
      end
    end
end

這兩個選擇都會使用同樣的索引來加速每一個迴圈。唯一差別在於這些迴圈出現於巢狀的順序。
所以哪一個查詢計畫比較好呢？答案取決於在 node 和 edge 表格內會找到什麼樣的資料。假設「alice」節點的數量是 M、而「bob」節點的數量是 N。考慮下列兩種情況：

第一個情況：如果 M 和 N 都是 2，但在這 4 個點上都有上千個邊。在此狀況下，選擇「方案一」較好。使用「方案一」，內層迴圈會尋找兩點之間的邊。但因為「alice」和「bob」都只有 2 個而已，因此內層迴圈總共只要執行 4 次即可，比「方案二」快很多。方案二的最外層迴圈雖然只執行 2 次，但是因為來自 alice 的邊數量很多，所以第二層迴圈可能就會執行數千次，相當之慢。因此在此情況下，我們偏好「方案一」。
第二個情況：如果 M 和 N 都是 3500。此時 Alice 的數量很多，但是這些節點之間只有 1 ~ 2 個邊相連。在此狀況下，選擇「方案二」較好。使用方案二，外層迴圈仍然要執行 3500 次，但是中間迴圈卻只要執行 1~2 次、內層迴圈只要執行 1 次。所以內層迴圈執行總次數約為 7000 次。另一方面，方案一外層與中間迴圈都要執行 3500 次，結果中間迴圈就要執行 1200 萬次。因此，在第二個情況下，方案二比方案一快了 2000 倍！

由上例可以看出「方案一」或「方案二」比較好，主要是取決於表格內的資料結構。但是 SQLite 預設會選擇哪一個方案？從 3.6.18 版以後，若未執行 ANALYZE，SQLite 會選擇「方案二」。但若執行過 ANALYZE，SQLite 就會依據統計資訊選擇較快的方案。

2. 查詢計畫的手動控制

有兩種方式可對查詢計畫進行手動控制，一種是捏造 ANALYZE 的統計數據（儲存於 sqlite_stat1 和 sqlite_stat2 表格中），另一種是使用 CROSS JOIN。

捏造 ANALYZE 的統計數據：

一般不建議採用此方法，除非是像下面這樣的情境。
在開發時期，先建立一個包含特定資料的雛形 DB，然後執行 ANALYZE 指令以針對該 DB 蒐集統計資訊，然後再把這些統計資訊與應用程式儲存在一起。
在部署時期，建立一個新的 DB 檔案，執行 ANALYZE 指令以建立 sqlite_stat1 和 sqlite_stat2 表格，然後再把之前從雛形 DB 取得的統計資訊複製進這兩個表格。如此，應用程式就可以使用這些統計資訊了。

使用 CROSS JOIN：

使用 CROSS JOIN，以這個語法連接的兩張表格就不會被 SQLite 改變順序。
假設原查詢如下：

SELECT *
    FROM node AS n1,
         edge AS e,
         node AS n2
   WHERE n1.name = 'alice'
     AND n2.name = 'bob'
     AND e.orig = n1.id
     AND e.dest = n2.id;
用「CROSS JOIN」取代逗號「,」，原來邏輯仍然不變，但可將表格的順序固定成： n1, e, n2。

SELECT *
    FROM node AS n1 CROSS JOIN
         edge AS e CROSS JOIN
         node AS n2
   WHERE n1.name = 'alice'
     AND n2.name = 'bob'
     AND e.orig = n1.id
     AND e.dest = n2.id;
如果改成上面這個樣子，SQLite 就會改採「方案二」了。
注意，一定要使用關鍵字「CROSS JOIN」才能關掉表格 JOIN 順序的最佳化
INNER JOIN, NATURAL JOIN, JOIN 以及其他組合的運作方式其實也跟以逗號連接的 JOIN 一樣，SQLite 都會視狀況進行表格 JOIN 順序的調整，所以也可使用這個方法
注意， SQLite 本來就不會對「OUTER JOIN」進行表格 JOIN 順序的調整（因為改變表格 JOIN 順序會改變查詢的結果）。

六、在多個索引之間做選擇

SQLite 針對在 FROM 子句中的每個表格最多只會使用一個索引（「OR 優化」除外），也會盡可能對每張表格至少使用一個索引。有時候，一個表格可能會有兩個或更多的索引可供 SQLite 選擇。例如：

CREATE TABLE ex2(x,y,z);

CREATE INDEX ex2i1 ON ex2(x);

CREATE INDEX ex2i2 ON ex2(y);

SELECT z FROM ex2 WHERE x=5 AND y=6;
以上例的 SELECT 來說，SQLite 有可能先使用 ex2i1 去尋找表格 ex2 中包含「x=5」的資料列，然後再對 ex2 進行「y=6」的逐列檢驗；也有可能先使用 ex2i2 去尋找表格 ex2 中包含「y=6」的資料列，然後再對 ex2 進行「x=5」的逐列檢驗。
當遇到有多個索引可以選擇的問題時，SQLite 試著估計每個查詢計畫的總工作量。然後從中選出總工作量最小的查詢計畫。
為了幫助 SQLite 能估計出更精確的工作量，使用者可執行「ANALYZE」指令來掃瞄 DB 中所有索引並且蒐集相關統計資料。蒐集到的統計資料會儲存在以「sqlite_stat」開頭的表格之中。因為這些表格不會在 DB 內容改變之後自動更新，所以最好再重新執行一次 ANALYZE。
「sqlite_statN」表格包含了這些索引的可用性。例如，表格「sqlite_stat1」可能指出在欄位 x 的相等條件式會平均減少10 列的搜尋空間、而在欄位 y 的相等條件式可能會平均減少 3 列的搜尋空間。在這種狀況下，SQLite 可能就會選擇「ex2i2」這個索引（有問題!?）。
如想讓 SQLite 對 WHERE 子句裡的條件式不使用索引，可在欄位名稱前加上「+」這個一元運算子。「+」是一個 no-op，不會讓該條件式的檢驗變慢。如果將上例查詢改寫成像下面這樣，這會強制 SQLite 不使用「ex2i1」索引，所以會改用「ex2i2」索引：

SELECT z FROM ex2 WHERE +x=5 AND y=6;
注意「+」也會使表示式中的「型態親和性」（Type Affinity）失效，而某些狀況下也會改變表示式的意義。例如，假設上例的 x 欄位原先具備「TEXT 親和性」，那表示式「x=5」將會以文字處理。但因為「+」運算子移除了這個親和性。所以「+x=5」表示式將會把 x 欄位中的文字拿來跟數值 5 做比較，其結果就會變成 false。

七、範圍查詢

假設上一個情境微調成下面這樣：

CREATE TABLE ex2(x,y,z);

CREATE INDEX ex2i1 ON ex2(x);

CREATE INDEX ex2i2 ON ex2(y);

SELECT z FROM ex2 WHERE x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 100;
接著我們假設欄位 x 的值介於 0~1,000,000 之間，而欄位 y 的值介於 0~1,000 之間。在這個情況下，在 x 欄位上的範圍限制將會用 10,000 的因數（factor）來減少搜尋空間、在 y 欄位上的範圍限制只會用 10 的因數來減少搜尋空間。因此 SQLite 應該會選擇「ex2i2」索引。
但是只有在編譯時期，有把「SQLITE_ENABLE_STAT3」選項打開，SQLite 才會採取這樣的查詢計畫。「SQLITE_ENABLE_STAT3」選項會使「ANALYZE」蒐集欄位內容的統計資料（並儲存於「sqlite_stat3」表格內），以便 SQLite 在「範圍查詢」時做出更好的策略。
「sqlite_stat3」表格裡的統計資料只有在條件右手邊是常數或參數，而非運算式的時候有用。另外一個限制是：這些統計資料只用於索引最左邊的欄位。例如以下的狀況：

CREATE TABLE ex2(w,x,y,z);

CREATE INDEX ex2i1 ON ex2(w, x);

CREATE INDEX ex2i2 ON ex2(w, y);

SELECT z FROM ex3 WHERE w=5 AND x BETWEEN 1 AND 100 AND y BETWEEN 1 AND 100;
因為此時欄位 x 和 y 都不是索引的最左方欄位。所以蒐集到的統計資料並無法幫助此查詢在欄位 x 和 y 找出合適的範圍限制。

八、避免表格掃瞄

當 SQLite 使用索引來加速查詢時，一般流程都是先在索引上進行 binary search，然後從索引取出 ROWID，最後再在原始表格上進行一次 binary search。
因此索引查詢通常包含了兩次 binary search。但如果查詢所需要的欄位都已經可以從索引中取得的話，SQLite 就會直接使用索引中的值，而不會對原始表格進行資料查找。所以會少掉一次對每一資料列的 binary search（表格掃瞄），而且會讓許多查詢變快兩倍，請參考「涵蓋式索引」（covering index）。

九、ORDER BY 優化

SQLite 會盡可能地嘗試利用索引來滿足查詢中的 ORDER BY 子句，其分析的步驟和「WHERE 子句分析」類似，SQLite 會從中選出最快的方案。
詳細可參考「WHERE 子句分析」和「QUERY PLANNING – 排序」。

十、子查詢扁平化（Subquery flattening）

當 FROM 裡頭出現子查詢時，最簡單的方式就是先將該子查詢轉成暫存表格（ transient table），然後再對該表格進行 SELECT 查詢。但因為這個暫存表格上面並沒有任何索引，而且外層 SELECT 查詢（很可能是 JOIN）可能對這個表格進行「全表掃瞄」，所以這個方法並不是那麼好。
要解決這個問題，SQLite 會嘗試將 FROM 子句中的子查詢扁平化（subquery flattening） 。大致的步驟是，先將子查詢的 FROM 子句插入外圍的查詢，然後重寫外層的查詢以取得原子查詢的結果集。例如：

SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5;
經過「子查詢扁平化」可能會變成下面這個樣子：

SELECT x+y AS a FROM t1 WHERE z<100 AND a>5;
必須符合下列所有條件，SQLite 才會對查詢進行扁平化：

子查詢和外層查詢都不能使用聚合函式（Aggregate Functions ，如：AVG()、COUNT()、MAX()、MIN()、SUM()、TOTAL()…等）。
子查詢不是一個聚合函式或外層查詢不是 JOIN。
子查詢不是 LEFT OUTER JOIN 的右方運算元。
子查詢不是 DISTINCT 或外層查詢不是 JOIN。
子查詢不是 DISTINCT 或外層查詢沒有使用聚合函式。
子查詢未使用聚合函式或者外層查詢不是 DISTINCT。
子查詢必須有 FROM 子句。
子查詢沒有使用 LIMIT 或外層查詢不是 JOIN。
子查詢沒有使用 LIMIT 或外層查詢沒有使用聚合函式。
子查詢未使用聚合函式或外層查詢沒有使用 LIMIT。
子查詢和外層查詢都沒有 ORDER BY 子句。
子查詢和外層查詢都沒有使用 LIMIT。
子查詢沒有使用 OFFSET。
外層查詢不是複合 SELECT （compound select）的一部份或子查詢內沒有 ORDER BY 和 LIMIT 子句。
外層查詢不是聚合函式或子查詢沒有 ORDER BY。
子查詢不是複合 SELECT ，或者是完全以非聚合函式組成的 UNION ALL 複合子句，而且：

父查詢不是複合 SELECT 的一部份。
父查詢不是聚合函式或 DISTINCT 查詢
父查詢的 FROM 子句中沒有其他表格或者 sub-select。
父查詢和子查詢可能包含 WHERE 子句。並遵守規則 11、12 以及 13，他們可能也包含 ORDER BY 、LIMIT 或 OFFSET 子句。

如果子查詢是複合式 SELECT 那父查詢 ORDER BY 子句的所有條件式必須是子查詢的欄位簡單參照。
子查詢不能使用 LIMIT 或外層查詢沒有 WHERE 子句。
如果子查詢是複合式 SELECT，那必須沒有使用 ORDER BY 子句。

讀者不需瞭解或記得以上的規則。此份規則只是在強調 SQLite 內部決定要對查詢進行扁平化工作是相當複雜的。
因為 VIEW 會被轉換為子查詢，所以就 VIEW 來說，「查詢扁平化」是一項重要的優化技巧。

十一、MIN/MAX 優化

假設有合適索引可資使用、而查詢又符合下列形式的話，那執行此類查詢的時間複雜度就會是對數時間（ logarithmic time）：

SELECT MIN(x) FROM table;

SELECT MAX(x) FROM table;
查詢必須完全符合以上的形式，SQLite 才會對此進行優化（亦即只有欄位和表格的名稱可以改變而已）。加上 WHERE 子句或者對結果進行任何算術運算都是不允許的。而且結果集只能是單一欄位。而 MIN 或 MAX 函式中的欄位必須是索引過的欄位。

十二、自動索引（Automatic Indices）

當如果沒有索引可以用來加速查詢時，SQLite 可能會在單一 SQL 執行期間建立一個自動索引，並使用該索引來提升查詢效能。但因為建立自動索引的時間複雜度是 O(NlogN)（N 是表格中的資料列數）、而執行「全表掃瞄」只有 O(N)，所以只有當 SQLite 預期「全表掃瞄」的次數會大於 logN 時，SQLite 才會採用自動索引。假設以下範例：

CREATE TABLE t1(a,b);

CREATE TABLE t2(c,d);

-- Insert many rows into both t1 and t2

SELECT * FROM t1, t2 WHERE a=c;
在上例中，如果 t1 和 t2 都約有 N 列，如果沒有任何索引的話，該查詢約需要 O(N*N) 的時間。另一方面，先為 t2 表格建立索引需要 O(NlogN) 的時間、再利用該索引去進行查詢需要再一個 O(NlogN) 的時間。如果沒有執行過 ANALYZE，SQLite 會猜測 N 是 100 萬，因此它會認為建立自動索引會是比較便宜的方案。
自動索引或許能用在如下的子查詢：

CREATE TABLE t1(a,b);
CREATE TABLE t2(c,d);
-- Insert many rows into both t1 and t2
SELECT a, (SELECT d FROM t2 WHERE c=b) FROM t1;
在這個例子裡表格，表格 t2 被用在子查詢之中，並以欄位 c 參照到 t1 表格的 b 欄位。如果兩張表都包含 N 列，SQLite 會認為子查詢會執行 N 次，因此它會認為在表格 t2 上面建立一個自動的、暫時的索引，然後用這個索引來加速子查詢。
此功能可在執行時期以「PRAGMA automatic_index;」指令來關閉，也可在編譯時期用「SQLITE_OMIT_AUTOMATIC_INDEX」選項來

2013年7月27日星期六

SQLite 學習筆記之五 - Explain Query Plan

※ 本文主要是 SQLite 官方網頁「EXPLAIN QUERY PLAN」的翻譯整理，希望能對大家有幫助！

我們可在 SQL 前加上「EXPLAIN QUERY PLAN」，以瞭解 SQLite 內部的實際運作流程與如何使用索引。除了 SELECT，也適用於：UPDATE、DELETE、INSERT INTO ... SELECT 等指令。更可用此指令來調校 SQL 與 DB schema，並進而改善效能。

一、輸出結果

此指令輸出的結果共有 4 個欄位：selectid、order、 from、detail ，其中以「detail」欄位提供的資訊最為有用。

欄位	說明
selectid	通常是 0（代表是最頂層的 SELECT）。只有當查詢中包含了「子查詢」（subquery，sub-select）時，此欄位才會出現非 0 的數值，代表這是第幾個子查詢。
order	因為 SQLite 的 join 動作都是使用「巢狀迴圈掃瞄」（nested scans），所以這個欄位代表此掃瞄動作是在第幾層迴圈，由 0 開始，0 代表最外層迴圈、1 代表第二層迴圈…依此類推。
from	代表此掃瞄/搜尋動作是發生在 FROM 子句中的第幾個 table，由 0 開始，0 代表第一個 table，1 則代表第二個 table…依此類推。
detail	開頭通常是「SCAN」或「SEARCH」。「SCAN」代表是 full-table scan，也包含了依據某 index 定義之順序對 table 內所有資料進行遍訪；「SEARCH」代表只有對 table 內的某子集合（subset）進行遍訪。而每一個「SCAN」或「SEARCH」還包含了以下資訊：讀取資料的 table 名稱。是否使用 index（或 automatic index）？是否使用「涵蓋式索引」（covering index）來最佳化？ WHERE 中的哪一個子句被用於 index？ SQLite 可能拜訪的資料列數（估計值）。

二、表格與索引掃瞄

例一、表示 SQLite 會對 t1 進行全表掃瞄，並估計可能會巡訪 100,000 列的資料。若您覺得這個估計值不準，可以再執行「ANALYZE」指令以更新表格與索引的統計資訊：

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
0|0|0|SCAN TABLE t1 (~100000 rows)
例二、如果 SQLite 發現有索引可用，則 SCAN / SEARCH 就會顯示此計畫所使用的索引名稱。若是「SEARCH」的話，還會顯示 SQLite 是依據哪些限制條件來過濾的。例如下面這個例子就表示 SQLite 是利用索引「i1」來最佳化 WHERE 中的「a=?」子句，而且 SQLite 預估約有 10 列會符合「a=1」子句：

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
例三、這個例子則表示 SQLite 利用了「涵蓋式索引」（covering index ）：

sqlite> CREATE INDEX i2 ON t1(a, b);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;

0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
例四、這個例子說明第二個欄位（order）與第三個欄位（from）之意義。第一列 SEARCH TABLE 的「order=0」表示這是外層迴圈的掃瞄，第二列 SCAN TABLE 的「order=1」表示這是內層迴圈的掃瞄。至於第一列的「from=0」表示 t1 是在 FROM 子句中的第一個表格，而第二列的「from=1」表示 t2 是在 FROM 子句中的第二個表格。

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)
0|1|1|SCAN TABLE t2 (~1000000 rows)
例五、若對調上例 SQL「FROM」子句中的表格順序，則可發現 SQLite 仍使用相同的查詢策略，唯 from 欄位的值會跟著改變：

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)
0|1|0|SCAN TABLE t2 (~1000000 rows)
例六、若 WHERE 子句包含了「OR」限制式，則 SQLite 可能會採用所謂的「OR by union」策略。在此狀況下，可能會有兩筆「SEARCH」紀錄（一筆對一個索引），而且「order」與「from」欄位的值都會一樣。

sqlite> CREATE INDEX i3 ON t1(b);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)

三、暫時用來排序的 B-Tree

如果在 SELECT 查詢中包含了 ORDER BY、GROUP BY 或 DISTINCT 子句，則 SQLite 可能會使用「暫時性的 B-Tree 結構」來對輸出資料列進行排序。或者利用索引，而使用索引會比使用 B-Tree 來得有效率。
如果 SQLite 決定採用 B-Tree 排序的話，則在「detail」欄位會出現類似「USE TEMP B-TREE FOR xxx」的訊息，其中「xxx」可能是「ORDER BY」、「GROUP BY」或「DISTINCT」。

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;

0|0|0|SCAN TABLE t2 (~1000000 rows)

0|0|0|USE TEMP B-TREE FOR ORDER BY
若希望 SQLite 避免採用「暫時性的 B-Tree 結構」來排序的話，可參考下例在要排序的欄位上建立一個索引：

sqlite> CREATE INDEX i4 ON t2(c);

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;

0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows)

四、子查詢

在前面的例子中，第一個欄位（selectid）都是 0。但如果查詢中包含了子查詢（subquery、sub-select），不管是在 FROM 子句或者是 SQL 的一部份，那麼該欄位就會出現非 0 值。而最頂層的 SELECT 的 selectid 總是 0。
下例中，我們看到有兩個子查詢的 selectid 分別是「1」和「2」。另外，還有一列 SCAN 以及兩列 EXECUTE。這兩個 EXECUTE 代表這兩個子查詢都是由最頂層的查詢所執行的。

sqlite> EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
0|0|0|SCAN TABLE t2 (~1000000 rows)
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
而 EXECUTE 紀錄中的「CORRELATED」代表第二個子查詢與最頂層的查詢是相關的，也就是說從頂層找出一個資料列以後會再執行一次第二個子查詢。至於第一個子查詢中沒有出現此修飾詞，則代表此查詢只會執行一次，而且執行結果會被 cache。換句話說，因為第二個子查詢會執行多次，所以是提升效能的關鍵。
除非是 SQLite 對子查詢採用了「扁平優化」（flattening optimization），否則在 FROM 子句裡的子查詢，其查詢結果會被先儲存在暫存表格之內，後續 SQLite 會再用這個暫存表格裡的資料來取代子查詢並提供給上層的父查詢（parent query）使用。而在 EXPLAIN QUERY PLAN 的結果中，我們就可以看到「SCAN TABLE」會變成「SCAN SUBQUERY」：

sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;

1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)

0|0|0|SCAN SUBQUERY 1 (~1000000 rows)

0|0|0|USE TEMP B-TREE FOR GROUP BY
如果 SQLite 對子查詢採用了「扁平優化」，就看不到「SCAN SUBQUERY」出現。反之，我們會看到 SQLite 對表格 t1 與 t2 執行了「巢狀迴圈 join」（nested loop join）：

sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;

0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)

0|1|1|SCAN TABLE t1 (~1000000 rows)

五、複合查詢

如果是 UNION、UNION ALL 、EXCEPT 或 INTERSECT 這類的「複合式查詢」（compound queries），則 SQLite 會個別指派一個專屬的 selectid 給每一個子查詢、然後報告其查詢計畫。至於父查詢（複合式查詢）的報告，則會獨立顯示在另外一行，如下例中的「COMPOUND SUBQUERIES……」：

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;

1|0|0|SCAN TABLE t1 (~1000000 rows)

2|0|0|SCAN TABLE t2 (~1000000 rows)

0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
上例中的「USING TEMP B-TREE」還說明了 SQLite 使用「暫存的 B-Tree結構」來進行兩個子查詢的結果集的 UNION 操作。
如果 SQLite 沒有使用到「暫存的 B-Tree結構」，則此訊息就不會出現，如下所示：

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;

1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)

2|0|0|SCAN TABLE t2 (~1000000 rows)

2|0|0|USE TEMP B-TREE FOR ORDER BY

0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)

訂閱：文章 (Atom)

網路技術趨勢觀測站

2014年1月4日星期六

IDE v.s. 程式語言

2013年8月17日星期六

Clean Code 讀後感想

2013年8月3日星期六

將 DocBook 轉換成 PDF 格式

2013年7月31日星期三

DocBook 的 Line Numbering 和 Code Highlighting

2013年7月28日星期日

SQLite 學習筆記之六 - Query Planner

2013年7月27日星期六

SQLite 學習筆記之五 - Explain Query Plan

總網頁瀏覽量

標籤

追蹤者

2014年1月4日 星期六

IDE v.s. 程式語言

2013年8月17日 星期六

Clean Code 讀後感想

2013年8月3日 星期六

將 DocBook 轉換成 PDF 格式

2013年7月31日 星期三

DocBook 的 Line Numbering 和 Code Highlighting

2013年7月28日 星期日

SQLite 學習筆記之六 - Query Planner

2013年7月27日 星期六

SQLite 學習筆記之五 - Explain Query Plan

2014年1月4日星期六

2013年8月17日星期六

2013年8月3日星期六

2013年7月31日星期三

2013年7月28日星期日

2013年7月27日星期六