SQLite教程（五）：索引和資料分析/清理，sqlite資料分析

SQLite教程（五）：索引和資料分析/清理，sqlite資料分析

一、建立索引：

    在SQLite中，建立索引的SQL文法和其他大多數關係型資料庫基本相同，因為這裡也僅僅是給出樣本用法：
 複製代碼 代碼如下:
    sqlite> CREATE TABLE testtable (first_col integer,second_col integer);
    --建立最簡單的索引，該索引基於某個表的一個欄位。
    sqlite> CREATE INDEX testtable_idx ON testtable(first_col);
    --建立聯合索引，該索引基於某個表的多個欄位，同時可以指定每個欄位的定序(升序/降序)。
    sqlite> CREATE INDEX testtable_idx2 ON testtable(first_col ASC,second_col DESC);
    --建立唯一性索引，該索引規則和資料表的唯一性限制式的規則相同，即NULL和任何值都不同，包括NULL本身。
    sqlite> CREATE UNIQUE INDEX testtable_idx3 ON testtable(second_col DESC);
    sqlite> .indices testtable
    testtable_idx
    testtable_idx2   
    testtable_idx3
 
    從.indices命令的輸出可以看出，三個索引均已成功建立。
   
二、刪除索引：

    索引的刪除和視圖的刪除非常相似，含義也是如此，因此這裡也只是給出樣本： 複製代碼 代碼如下:  
    sqlite> DROP INDEX testtable_idx;
    --如果刪除不存在的索引將會導致操作失敗，如果在不確定的情況下又不希望錯誤被拋出，可以使用"IF EXISTS"從句。
    sqlite> DROP INDEX testtable_idx;
    Error: no such index: testtable_idx
    sqlite> DROP INDEX IF EXISTS testtable_idx;

三、重建索引：

    重建索引用於刪除已經存在的索引，同時基於其原有的規則重建該索引。這裡需要說明的是，如果在REINDEX語句後面沒有給出資料庫名，那麼當前串連下所有Attached資料庫中所有索引都會被重建。如果指定了資料庫名和表名，那麼該表中的所有索引都會被重建，如果只是指定索引名，那麼當前資料庫的指定索引被重建。
 複製代碼 代碼如下:
    --當前串連attached所有資料庫中的索引都被重建。
    sqlite> REINDEX;
    --重建當前主要資料庫中testtable表的所有索引。
    sqlite> REINDEX testtable;
    --重建當前主要資料庫中名稱為testtable_idx2的索引。
    sqlite> REINDEX testtable_idx2;
   
四、資料分析：

    和PostgreSQL非常相似，SQLite中的ANALYZE命令也同樣用於分析資料表和索引中的資料，並將統計結果存放於SQLite的內部系統資料表中，以便於查詢最佳化工具可以根據分析後的統計資料選擇最優的查詢執行路徑，從而提高整個查詢的效率。見如下樣本：
 複製代碼 代碼如下:
    --如果在ANALYZE命令之後沒有指定任何參數，則分析當前串連中所有Attached資料庫中的表和索引。
    sqlite> ANALYZE;
    --如果指定資料庫作為ANALYZE的參數，那麼該資料庫下的所有表和索引都將被分析並產生統計資料。
    sqlite> ANALYZE main;
    --如果指定了資料庫中的某個表或索引為ANALYZE的參數，那麼該表和其所有關聯的索引都將被分析。
    sqlite> ANALYZE main.testtable;
    sqlite> ANALYZE main.testtable_idx2;

五、資料清理：

    和PostgreSQL中的VACUUM命令相比，他們的功能以及實現方式非常相似，不同的是PostgreSQL提供了更細的粒度，而SQLite只能將該命令作用於資料庫，無法再精確到資料庫中指定的資料表或者索引，然而這一點恰恰是PostgreSQL可以做到的。
    當某個資料庫中的一個或多個資料表存在大量的插入、更新和刪除等操作時，將會有大量的磁碟空間被已刪除的資料所佔用，在沒有執行VACUUM命令之前，SQLite並沒有將它們歸還於作業系統。由於該類資料表中的資料存放區非常分散，因此在查詢時，無法得到更好的批量IO讀取效果，從而影響了查詢效率。
    在SQLite中，僅支援清理當前串連中的主要資料庫，而不能清理其它Attached資料庫。VACUUM命令在完成資料清理時採用了和PostgreSQL相同的策略，即建立一個和當前資料庫檔案相同大小的新資料庫檔案，之後再將該資料庫檔案中的資料有組織的匯入到新檔案中，其中已經刪除的資料區塊將不會被匯入，在完成匯入後，收縮新資料庫檔案的尺寸到適當的大小。該命令的執行非常簡單，如：
 複製代碼 代碼如下:
    sqlite> VACUUM;