# 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; ```