第六章索引本章主要內(nèi)容索引簡(jiǎn)介創(chuàng)建索引使用圖形界面操作索引1234主要內(nèi)容刪除索引6.1索引簡(jiǎn)介

6.1.1索引的概念

如何在字典中查找指定偏旁的漢字？如何在一本書中查找某內(nèi)容？對(duì)于這兩個(gè)問題大家都不陌生：在字典中查找指定偏旁的漢字時(shí)，首先查詢目錄中指定的偏旁位置，再查詢指定筆畫的漢字，最后目錄中提供的頁(yè)碼找到這個(gè)漢字；在書中查詢某內(nèi)容時(shí)，首先在目錄中查詢?cè)搩?nèi)容所屬的知識(shí)點(diǎn)，然后根據(jù)該知識(shí)點(diǎn)所對(duì)應(yīng)的頁(yè)碼快速找到要查詢的內(nèi)容。在數(shù)據(jù)庫(kù)中也可以建立類似目錄的數(shù)據(jù)庫(kù)對(duì)象，實(shí)現(xiàn)數(shù)據(jù)的快速查詢，這就是索引。索引是將表中的一個(gè)或者多個(gè)字段的值按照特定的結(jié)構(gòu)進(jìn)行排序然后存儲(chǔ)。6.1索引簡(jiǎn)介

6.1.2為什么使用索引

那使用索引到底有什么好處呢？如果沒有索引，在查找某條記錄時(shí)，MySQL必須從表的第一條記錄開始，然后通讀整個(gè)表直到找到相關(guān)的記錄。如果表越大，那么查找記錄所耗費(fèi)的時(shí)間就越多。如果有索引，那么MySQL可以快速定位目標(biāo)記錄所在的位置，而不必去瀏覽表中的每一條記錄，效率遠(yuǎn)遠(yuǎn)超過沒有索引時(shí)的搜索效率。索引有自己專門的存儲(chǔ)空間，與表獨(dú)立存放。MySQL中的索引主要支持以下三種存儲(chǔ)方式：6.1索引簡(jiǎn)介

6.1.2為什么使用索引

(1)B-Tree存儲(chǔ)結(jié)構(gòu)：使用最多的一種存儲(chǔ)結(jié)構(gòu)，使用B-Tree存儲(chǔ)結(jié)構(gòu)的索引的所有結(jié)點(diǎn)都按照BalanceTree的數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)，索引數(shù)據(jù)結(jié)點(diǎn)都在葉子結(jié)點(diǎn)。B-Tree的基本思想是：所有值（被索引的字段）都是排過序的，每個(gè)葉結(jié)點(diǎn)到根結(jié)點(diǎn)距離相等。所以B-Tree適合用來查找某一范圍內(nèi)的數(shù)據(jù)，而且可以直接支持?jǐn)?shù)據(jù)排序（ORDERBY）。但是當(dāng)索引為多字段時(shí)，字段的順序特別重要。6.1索引簡(jiǎn)介

6.1.2為什么使用索引6.1索引簡(jiǎn)介

6.1.2為什么使用索引

(2)R-Tree存儲(chǔ)結(jié)構(gòu)：R-Tree存儲(chǔ)結(jié)構(gòu)主要用于空間索引（設(shè)置為空間索引字段的數(shù)據(jù)類型必須是空間數(shù)據(jù)類型，如GEOMETRY、POINT、LINESTRING、POLYGON）。(3)Hash存儲(chǔ)結(jié)構(gòu)：基于hash表的一種存儲(chǔ)結(jié)構(gòu)，所以這種存儲(chǔ)結(jié)構(gòu)的索引只支持精確查找，不支持范圍查找，也不支持排序。這意味著范圍查找或ORDERBY都要依賴server層的額外工作。6.1索引簡(jiǎn)介

6.1.2為什么使用索引不同的存儲(chǔ)引擎支持的存儲(chǔ)結(jié)構(gòu)不同：

(1)InnoDB存儲(chǔ)引擎（MySQL5.5版本之后默認(rèn)的存儲(chǔ)引擎）支持B-Tree和R-Tree（MySQL5.7新增功能），但默認(rèn)使用的是B-Tree。(2)MyISAM存儲(chǔ)引擎（MySQL5.5版本之前默認(rèn)的存儲(chǔ)引擎）支持B-Tree和R-Tree，但默認(rèn)使用的是B-Tree。(3)MEMORY存儲(chǔ)引擎支持B-Tree和Hash，但默認(rèn)是Hash。6.1索引簡(jiǎn)介

6.1.2為什么使用索引索引可以提升數(shù)據(jù)的查詢效率，但在使用索引時(shí)要注意以下幾點(diǎn)：(1)索引數(shù)據(jù)會(huì)占用大量的存儲(chǔ)空間。(2)索引改善檢索操作的性能，但降低數(shù)據(jù)插入、修改和刪除的性能。在執(zhí)行這些操作時(shí)，DBMS必須動(dòng)態(tài)地更新索引。(3)限制表中索引的數(shù)目。索引越多，在修改表時(shí)對(duì)索引做出修改的工作量越大。(4)并非所有數(shù)據(jù)都適合于索引。唯一性不好的數(shù)據(jù)從索引得到的好處并不多。6.1索引簡(jiǎn)介

6.1.2為什么使用索引索引可以提升數(shù)據(jù)的查詢效率，但在使用索引時(shí)要注意以下幾點(diǎn)：(5)索引用于數(shù)據(jù)過濾和數(shù)據(jù)排序。如果你經(jīng)常以某種特定的順序排序數(shù)據(jù)，則該數(shù)據(jù)可能是索引的備選。(6)可以在索引中定義多個(gè)字段（如省+城市），這樣的索引只在以“省+城市”的順序排序時(shí)有用。如果只想按城市排序，則這種索引沒有用處。6.1索引簡(jiǎn)介

6.1.3索引的分類1.普通索引

普通索引是最基本的索引，它沒有任何限制。創(chuàng)建索引的字段可以是任意數(shù)據(jù)類型，字段的值可以為空，也可以重復(fù)。比如說，創(chuàng)建索引的字段為員工的姓名，但是姓名有重名的可能，所以同一個(gè)姓名在同一個(gè)“員工個(gè)人資料”數(shù)據(jù)表里可能出現(xiàn)兩次或更多次。6.1索引簡(jiǎn)介

6.1.3索引的分類2.唯一索引

如果能確定某個(gè)字段的值唯一，那么在為這個(gè)字段創(chuàng)建索引的時(shí)候就可以使用關(guān)鍵字UNIQUE把它定義為一個(gè)唯一索引。創(chuàng)建唯一索引的好處：簡(jiǎn)化了MySQL對(duì)索引的管理工作，唯一索引也因此而變得更有效率；MySQL會(huì)在有新記錄插入數(shù)據(jù)表時(shí)，自動(dòng)檢查新記錄中該字段的值是否已經(jīng)在某個(gè)記錄的該字段中出現(xiàn)過了，如果已經(jīng)出現(xiàn)，MySQL將拒絕插入這條新記錄。也就是說，唯一索引可以保證數(shù)據(jù)記錄的唯一性。6.1索引簡(jiǎn)介

6.1.3索引的分類3.主鍵索引

主鍵索引是為主鍵字段設(shè)置的索引，是一種特殊的唯一索引。主鍵索引與唯一索引的區(qū)別是在于：前者在定義時(shí)使用的關(guān)鍵字是PRIMARYKEY，而后者使的是UNIQUE；前者定義索引的字段值不允許有空值，而后者允許。6.1索引簡(jiǎn)介

6.1.3索引的分類4.全文索引

全文索引適用于在一大串文本中進(jìn)行查找，并且創(chuàng)建該類型索引的字段的數(shù)據(jù)類型必須是CHAR、VARCHAR或者TEXT。在MySQL5.7之前，全文索引只支持英文檢索，因?yàn)樗鞘褂每崭駚碜鳛榉衷~的分隔符，對(duì)于中文而言，使用空格是不合適的；從MySQL5.7開始，內(nèi)置了支持中文分詞的ngram全文檢索插件，并且InnoDB和MyISAM存儲(chǔ)引擎均支持全文檢索。6.1索引簡(jiǎn)介

6.1.3索引的分類5.空間索引

設(shè)置為空間索引字段的數(shù)據(jù)類型必須是空間數(shù)據(jù)類型，如GEOMETRY、POINT、LINESTRING、POLYGON，并且該字段必須設(shè)置為NOTNULL。目前InnoDB和MyISAM存儲(chǔ)引擎均支持空間檢索。6.1索引簡(jiǎn)介

6.1.3索引的分類6.復(fù)合索引

復(fù)合索引指在多個(gè)字段上創(chuàng)建的索引，這種索引只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段，該索引才會(huì)被觸發(fā)，這是因?yàn)槭褂脧?fù)合索引時(shí)遵循“最左前綴”的原因。例如：當(dāng)索引字段為(id,name)時(shí)，只有查詢條件中適用了id字段，該索引才會(huì)被使用；如果查詢條件中只有name字段是不會(huì)使用該索引的。6.2創(chuàng)建索引

(1)自動(dòng)創(chuàng)建索引(2)手動(dòng)創(chuàng)建索引

當(dāng)在表中定義一個(gè)PRIMARYKEY或者UNIQUE約束條件，MySQL數(shù)據(jù)庫(kù)會(huì)自動(dòng)創(chuàng)建一個(gè)對(duì)應(yīng)的主鍵索引或者唯一索引。

用戶可以在創(chuàng)建表時(shí)創(chuàng)建索引，也可以為已存在的表添加索引。6.2創(chuàng)建索引

1.自動(dòng)創(chuàng)建，創(chuàng)建帶主鍵和唯一約束的表createtablestudent1( stu_idint(10)primarykey, stu_namevarchar(3)unique, stu_sexvarchar(1));6.2創(chuàng)建索引

查看student1表的索引showindexfromstudent1;6.2創(chuàng)建索引

2.手動(dòng)創(chuàng)建

手動(dòng)創(chuàng)建索引可以在創(chuàng)建表的時(shí)候使用“CREATETABLE”語句為指定表中的指定字段創(chuàng)建索引，也可以使用“CREATEINDEX”或者“ALTERTABLE”語句為已存在的表創(chuàng)建索引。我們將會(huì)在接下來的兩個(gè)小的章節(jié)中詳細(xì)講解這兩種創(chuàng)建索引的方式。6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引1.普通索引的創(chuàng)建，語法格式如下所示：createtabletable_name( column_name1date_type, column_name2date_type, ......, index|key[index_name][index_type](column_name[(length)][asc|desc]));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引創(chuàng)建表時(shí)創(chuàng)建普通索引createtablestudent2( stu_idint(10), stu_namevarchar(3), index(stu_id));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引2.唯一索引的創(chuàng)建，語法格式如下所示：createtabletable_name( column_name1date_type, column_name2date_type, ......, unique[index|key][index_name][index_type](column_name[(length)][asc|desc]));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引創(chuàng)建表時(shí)創(chuàng)建唯一索引createtablestudent3( stu_idint(10), stu_namevarchar(3), uniqueindex(stu_id));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引3.主鍵索引的創(chuàng)建，語法格式如下所示：createtabletable_name( column_name1date_type, column_name2date_type, ......, primarykey[index|key][index_name][index_type](column_name[(length)][asc|desc]));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引創(chuàng)建表時(shí)創(chuàng)建主鍵索引createtablestudent4( stu_idint(10), stu_namevarchar(3), primarykeyusinghash(stu_iddesc));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引4.全文索引的創(chuàng)建，語法格式如下所示：createtabletable_name( column_name1date_type, column_name2date_type, ......, fulltext[index|key][index_name][index_type](column_name[(length)][asc|desc]));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引創(chuàng)建表時(shí)創(chuàng)建全文索引createtablestudent5( stu_idint(10), stu_infovarchar(100), fulltextindex(stu_info));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引5.空間索引的創(chuàng)建，語法格式如下所示：createtabletable_name( column_name1date_type, column_name2date_type, ......, spatial[index|key][index_name][index_type](column_name[(length)][asc|desc]));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引創(chuàng)建表時(shí)創(chuàng)建空間索引createtablestudent6( stu_idint(10), stu_locpointnotnull, spatialindex(stu_loc));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引6.復(fù)合索引的創(chuàng)建，語法格式如下所示：createtabletable_name( column_name1date_type, column_name2date_type, ......, index|key[index_name][index_type](column_name1[(length)][asc|desc],column_name2[(length)][asc|desc],···));6.2創(chuàng)建索引

6.2.1創(chuàng)建表的時(shí)候創(chuàng)建索引創(chuàng)建表時(shí)創(chuàng)建復(fù)合索引createtablestudent7( stu_idint(10), stu_namevarchar(3), index(stu_id,stu_name));6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引1.使用CREATEINDEX創(chuàng)建索引，語法格式如下所示：create[unique|fulltext|spatial]indexindex_name[index_type]ontable_name(column_name1[(length)][asc|desc],column_name2[(length)][asc|desc],···);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(1)創(chuàng)建普通索引，使用CREATEINDEX為已存在的表創(chuàng)建普通索引createtablestudent8( stu_idint(10), stu_namevarchar(3));createindexindex_idonstudent8(stu_id);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引使用“SHOWINDEXFROM”語句查看表中的索引，執(zhí)行結(jié)果6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(2)創(chuàng)建唯一索引，使用CREATEINDEX為已存在的表創(chuàng)建唯一索引createuniqueindexindex_idonstudent9(stu_id);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(3)創(chuàng)建全文索引，使用CREATEINDEX為已存在的表創(chuàng)建全文索引createtablestudent10( stu_idint(10), stu_infovarchar(100));createuniqueindexindex_idonstudent9(stu_id);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引使用“SHOWINDEXFROM”語句查看表中的索引，執(zhí)行結(jié)果6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(4)創(chuàng)建空間索引，使用CREATEINDEX為已存在的表創(chuàng)建空間索引createtablestudent11( stu_idint(10), stu_locpointnotnull);createspatialindexindex_loconstudent11(stu_loc);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(5)創(chuàng)建復(fù)合索引，使用CREATEINDEX為已存在的表創(chuàng)建復(fù)合索引createindexindex_idonstudent12(stu_id,stu_name);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引2.使用ALTERTABLE創(chuàng)建索引，語句的語法格式如下所示：altertabletable_nameaddindex|key[index_name][index_type](column_name1[(length)][asc|desc],column_name2[(length)][asc|desc],···);|addunique[index|key][index_name][index_type](column_name1[(length)][asc|desc],column_name2[(length)][asc|desc],···);|addprimarykey[index_type](column_name1[(length)][asc|desc],column_name2[(length)][asc|desc],···);|add[fulltext|spatial][index|key][index_name](column_name1[(length)][asc|desc],column_name2[(length)][asc|desc],···);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(1)創(chuàng)建普通索引，使用CREATEINDEX為已存在的表創(chuàng)建普通索引createtablestudent13( stu_idint(10), stu_namevarchar(3));altertablestudent13addindexindex_id(stu_id);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引使用“SHOWINDEXFROM”語句查看表中的索引，執(zhí)行結(jié)果6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(2)創(chuàng)建唯一索引，使用ALTERTABLE為已存在的表創(chuàng)建唯一索引altertablestudent14adduniqueindexindex_id(stu_id);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(3)創(chuàng)建主鍵索引，使用ALTERTABLE為已存在的表創(chuàng)建主鍵索引altertablestudent15addprimarykey(stu_id);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(4)創(chuàng)建全文索引，使用ALTERTABLE為已存在的表創(chuàng)建全文索引createtablestudent16( stu_idint(10), stu_infovarchar(100));altertablestudent16addfulltextindexindex_info(stu_info);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(5)創(chuàng)建空間索引，使用ALTERTABLE為已存在的表創(chuàng)建空間索引createtablestudent17( stu_idint(10), stu_locpointnotnull);altertablestudent17addspatialindexindex_loc(stu_loc);6.2創(chuàng)建索引

6.2.2為已存在的表創(chuàng)建索引(6)創(chuàng)建復(fù)合索引，使用ALTERTABLE為已存在的表創(chuàng)建復(fù)合索引altertablestudent18addindexindex_id(stu_id,stu_name);6.3刪除索引

6.3.1使用ALTERTABLE語句刪除索引使用ALTERTABLE語句刪除索引的SQL語句需要指定索引的名稱，其語法格式如下所示：altertabletable_namedropindex|keyindex_name;6.3刪除索引

6.3.1使用ALTERTABLE語句刪除索引【示例】使用ALTERTABLE刪除索引altertablestudent17dropindexindex_loc;6.3刪除索引

6.3.2使用DROPINDEX語句刪除索引DROPINDEX語句刪除索引的SQL語句同樣需要指定索引的名稱，其語法格式如下所示：dropindexindex_nameontable_name;6.4使用圖形界面操作索引

(1)首先，在圖形界面的左側(cè)列表中右鍵選中student18這個(gè)表，在彈出的下拉列表中選擇“DesignTable”選項(xiàng)。6.4使用圖形界面操作索引

(2)圖6-42是“DesignTable”選項(xiàng)的默認(rèn)界面（這個(gè)界面大家都不陌生，是設(shè)計(jì)表格字段相關(guān)信息的視圖界面“Fields”），此時(shí)需要選擇“Indexes”視圖。6.4使用圖形界面操作索引

(3)下面我們?yōu)楸碇械膕tu_id字段添加一個(gè)唯一索引。

首先點(diǎn)擊“AddIndex”選項(xiàng)；然后分別編輯該索引的“Name（索引名）”、“Fields（索引的字段，可以多選）”、“IndexType（索引類型，包括：FULLTEXT、NORMAL、SPATIAL、UNIQUE，其中“NORMAL”表示普通索引）”、“IndexMethod（索引方式，包括BT