MySQL筆記之索引的使用

索引是建立在表上的，對資料庫表中一列或多列的值進行排序的一種結構

其作用主要在於提高查詢的速度，降低資料庫系統的效能開銷

通過索引，查詢資料不必讀完記錄的全部資訊進行匹配，而是只查詢索引列

索引相當於字典中的音序表，要查詢某字時可以在音序表中找到

然後直接跳轉到那一音序所在位置，而不必從字典第一頁開始翻，逐字匹配

tips：索引雖能提高查詢速度，但在插入記錄時會按照索引進行排序，因此降低了插入速度

　 　 最好的操作方式是先刪除索引，插入大量記錄後再建立索引

索引分類

1.普通索引：不附加任何限制條件，可建立在任何資料類型中

2.唯一性索引：使用unique參數可以設定索引為唯一性索引，在建立索引時，限制該索引的值必須唯一，主鍵就是一種唯一性索引

3.全文索引：使用fulltext參數可以設定索引為全文索引。全文索引只能建立在char、varchar或text類型的欄位上。查詢資料量較大的字串類型欄位時，效果明顯。但只有MyISAM儲存引擎支援全文檢索索引

4.單列索引：在表中單個欄位上建立的索引，單列索引可以是任何類型，只要保證索引只對應一個一個欄位

5.多列索引：在表中多個欄位上建立的索引，該索引指向建立時對應的多個欄位

6.空間索引：使用spatial參數可以設定索引為空白間索引，空間索引只能建立在空間資料類型上比如geometry，並且不可為空，目前只有MyISAM儲存引擎支援

在建立表時建立索引
建立普通索引

複製代碼 代碼如下:mysql> create table index1(
-> id int,
-> name varchar(20),
-> sex boolean,
-> index(id)
-> );
Query OK, 0 rows affected (0.11 sec)

此處在id欄位上建立索引，show create table可查看

建立唯一性索引

複製代碼 代碼如下:mysql> create table index2(
-> id int unique,
-> name varchar(20),
-> unique index index2_id(id ASC)
-> );
Query OK, 0 rows affected (0.12 sec)

此處使用id欄位建立了一個名為index2_id的索引

這裡的id欄位可以不設定唯一性限制式，但這樣一來索引就沒有作用

建立全文索引

複製代碼 代碼如下:mysql> create table index3(
-> id int,
-> info varchar(20),
-> fulltext index index3_info(info)
-> )engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)

要注意建立全文索引時只能使用MyISAM儲存引擎

建立單列索引

複製代碼 代碼如下:mysql> create table index4(
-> id int,
-> subject varchar(30),
-> index index4_st(subject(10))
-> );
Query OK, 0 rows affected (0.12 sec)

此處subject欄位長度是30,而索引長度則是10

這麼做的目的在於提高查詢速度，對於字元型的資料不用查詢全部資訊

建立多列索引

複製代碼 代碼如下:mysql> create table index5(
-> id int,
-> name varchar(20),
-> sex char(4),
-> index index5_ns(name,sex)
-> );
Query OK, 0 rows affected (0.10 sec)

可以看出，這裡使用了name欄位和sex欄位建立索引列

建立空間索引

複製代碼 代碼如下:mysql> create table index6(
-> id int,
-> space geometry not null,
-> spatial index index6_sp(space)
-> )engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)

這裡需要注意空間space欄位不可為空，還有儲存引擎

在已經存在的表上建立索引
建立普通索引

複製代碼 代碼如下:mysql> create index index7_id on example0(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

這裡在現有表的id欄位上建立了一條名為index7_id的索引

建立唯一性索引

複製代碼 代碼如下:mysql> create unique index index8_id on example1(course_id);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

此處只需要在index關鍵字前面加上unique即可

至於表中的course_id欄位，最要也設定唯一性限制式條件

建立全文索引

複製代碼 代碼如下:mysql> create fulltext index index9_info on example2(info);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

fulltext關鍵字用來設定全文引擎，此處的表必須是MyISAM儲存引擎

建立單列索引

複製代碼 代碼如下:mysql> create index index10_addr on example3(address(4));
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

此表中address欄位的長度是20,這裡只查詢4位元組，不需要全部查詢

建立多列索引

複製代碼 代碼如下:mysql> create index index11_na on example4(name,address);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

索引建立好之後，查詢中必須有name欄位才能使用

建立空間索引

複製代碼 代碼如下:mysql> create spatial index index12_line on example5(space);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

這裡需要注意儲存引擎是MyISAM，還有空間資料類型

用alter table語句來建立索引
建立普通索引

複製代碼 代碼如下:mysql> alter table example6 add index index13_n(name(20));
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

建立唯一性索引
複製代碼 代碼如下:mysql> alter table example7 add unique index index14_id(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

建立全文索引
複製代碼 代碼如下:mysql> alter table example8 add fulltext index index15_info(info);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

建立單列索引
複製代碼 代碼如下:mysql> alter table example9 add index index16_addr(address(4));
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

建立多列索引
複製代碼 代碼如下:mysql> alter table example10 add index index17_in(id,name);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

建立空間索引
複製代碼 代碼如下:mysql> alter table example11 add spatial index index18_space(space);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

到此，三種操作方式，每種索引類別的建立就都列舉了

對於索引，重要的是理解索引的概念，明白索引的種類

更多的是自己的使用經驗

最後來看看索引的刪除

刪除索引

複製代碼 代碼如下:mysql> drop index index18_space on example11;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

這裡是剛剛建立的一條索引

其中index18_space是索引名，example11是表名