sqlite3學習之索引（Index）的使用&Indexed By子句&Alter 指令

索引（Index）是一種特殊的查找表，資料庫搜尋引擎用來加快資料檢索。簡單地說，索引是一個指向表中資料的指針。一個資料庫中的索引與一本書後邊的索引是非常相似的。例如，如果我們想在一本讨論某個話題的書中引用所有頁面，我們首先需要指向索引，索引按字母順序列出了所有主題，然後指向一個或多個特定的頁碼。索引有助于加快 SELECT 查詢和 WHERE 子句，但它會減慢使用 UPDATE 和 INSERT 語句時的資料輸入。索引可以建立或删除，但不會影響資料。使用 CREATE INDEX 語句建立索引，它允許命名索引，指定表及要索引的一列或多列，并訓示索引是升序排列還是降序排列。索引也可以是唯一的，與 UNIQUE 限制類似，在列上或列組合上防止重複條目，來看下建立索引的文法格式：

CREATE INDEX index_name ON table_name;
           

單列索引是一個隻基于表的一個列上建立的索引，基本文法如下：

CREATE INDEX index_name ON table_name (column_name);
           

使用唯一索引不僅是為了性能，同時也為了資料的完整性。唯一索引不允許任何重複的值插入到表中，基本文法如下：

CREATE UNIQUE INDEX index_name on table_name (column_name);
           

組合索引是基于一個表的兩個或多個列上建立的索引，基本文法如下：

CREATE INDEX index_name on table_name (column1, column2);
           

至于我們是否要建立一個單列索引還是組合索引，要考慮到我們在作為查詢過濾條件的 WHERE 子句中使用非常頻繁的列，如果值使用到一個列，則選擇使用單列索引，如果在作為過濾的 WHERE 子句中有兩個或多個列經常使用，則選擇使用組合索引。

隐式索引是在建立對象時，由資料庫伺服器自動建立的索引，索引自動建立為主鍵限制和唯一限制。

接下來，我們在 COMPANY 表的 salary 列上建立一個索引：

CREATE INDEX salary_index ON COMPANY (salary);
           

之後，我們就可以使用 .indices 或 .indexes 指令列出 COMPANY 表上所有可用的索引，如下：

.indices COMPANY
           

結果如下，其中 sqlite_autoindex_COMPANY_1 是建立表時建立的隐式索引：

salary_index
sqlite_autoindex_COMPANY_1
           

我們來嘗試列出資料庫範圍的所有索引，如下：

SELECT * FROM sqlite_master WHERE type = 'index';
           

一個索引可以使用 SQLite 的 DROP 指令删除。當删除索引時應特别注意，因為性能可能會下降或提高，基本文法如下：

DROP INDEX index_name;
           

雖然索引的目的在于提高資料庫的性能，但這裡有幾個情況需要避免使用索引。使用索引時，應重新考慮下列準則：

• 索引不應該使用在較小的表上。
• 索引不應該使用在有頻繁的大批量的更新或插入操作的表上。
• 索引不應該使用在含有大量的 NULL 值的列上。
• 索引不應該使用在頻繁操作的列上。

"INDEXED BY index-name" 子句規定必須需要命名的索引來查找前面表中值。如果索引名 index-name 不存在或不能用于查詢，然後 SQLite 語句的準備失敗。"NOT INDEXED" 子句規定當通路前面的表（包括由 UNIQUE 和 PRIMARY KEY 限制建立的隐式索引）時，沒有使用索引。然而，即使指定了 "NOT INDEXED"，INTEGER PRIMARY KEY 仍然可以被用于查找條目。

我們來看下 INDEXED BY 子句的文法，它可以與 DELETE、UPDATE 或 SELECT 語句一起使用：

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);
           

 假設有表 COMPANY，我們将建立一個索引，并用它進行 INDEXED BY 操作，如下：

CREATE INDEX salary_index ON COMPANY(salary);
           

接下來，可以使用 INDEXED BY 子句從表 COMPANY 中選擇資料，如下：

SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;
           

SQLite 的 ALTER TABLE 指令不通過執行一個完整的轉儲和資料的重載來修改已有的表。您可以使用 ALTER TABLE 語句重命名表，使用 ALTER TABLE 語句還可以在已有的表中添加額外的列。在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 指令不支援其他操作，我們來看下用來重命名已有的表的 ALTER TABLE 的基本文法：

ALTER TABLE database_name.table_name RENAME TO new_table_name;
           

用來在已有的表中添加一個新的列的 ALTER TABLE 的基本文法：

ALTER TABLE database_name.table_name ADD COLUMN column_def...;
           

我們來假設 COMPANY 表有如下記錄：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
           

我們來嘗試使用 ALTER TABLE 語句重命名該表，如下：

ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
           

我們再來嘗試在 OLD_COMPANY 表中添加一個新的列，如下：

ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
           

這裡提個醒，sqlite3 并不支援直接添加帶有 unique 限制的列，來看下面的例子：

sqlite> alter table company add department text unique;
Error: Cannot add a UNIQUE column
           

可以先直接添加一列資料，然後再添加 unique 索引。其實在建表的時候如果列有 unique 限制，通過查詢系統表 SQLITE_MASTER 可以看到，會自動建立相應的索引。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 是 sqlite 的系統表，SQLITE_TEMP_MASTER 表存儲臨時表有關的所有内容。

好啦，本次記錄就到這裡了。

如果感覺不錯的話，請多多點贊支援哦。。。