一、索引的含義和特點 索引是一個單獨的、存儲在磁碟上的資料庫結構，他們包含著對數據表裡所有記錄的引用指針。使用索引用於快速找出某個或多個列中有一特點值的行，所用MySQL列類型都可以被索引，對相關列使用索引是提高查詢操作速度的最佳途徑。 索引是在儲存引擎中實現的，因此，每種存儲引擎的索引都不一定完全 ...
一、索引的含義和特點 索引是一個單獨的、存儲在磁碟上的資料庫結構，他們包含著對數據表裡所有記錄的引用指針。使用索引用於快速找出某個或多個列中有一特點值的行，所用MySQL列類型都可以被索引，對相關列使用索引是提高查詢操作速度的最佳途徑。 索引是在儲存引擎中實現的，因此，每種存儲引擎的索引都不一定完全相同，並且每種存儲引擎也不一定支持所有索引類型。根據存儲引擎定義每個表的最大索引數和最大索引長度。所有存儲殷勤支持每個表至少16個索引，總索引長度至少為256位元組。大多數存儲引擎有更高的限制。MySQL中索引的存儲類型有兩種：BTREE和HASH，具體和表的存儲引擎相關：MyISAM和InnoDB存儲殷勤只支持BTREE索引；MEMORY/HEAP存儲引擎可以支持HASH和BTREE索引。 索引的優點主要有： （1）通過創建唯一索引，可以保證資料庫表中每一行數據的唯一性。 （2）可以大大加快數據的查詢速度。 （3）在實現數據的參考完整性方面，可以加速表和表之間的連接。 （4）在使用分組和排序子句進行數據查詢時，也可以顯著減少查詢中分組和排序的時間。 索引的缺點主要有： （1）創建索引和維護索引要耗費時間，並且隨著數據量的增加所耗費的時間也會增加。 （2）索引需要占磁碟空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果有大量的索引，索引文件可能比數據文件更快達到最大文件尺寸。 （3）當對錶中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。 二、索引的分類 MySQL的索引可以分為以下幾類： 1、普通索引和唯一索引 普通索引是MySQL中的基本索引類型，允許在定義索引的列中插入重覆值和空值。 唯一索引，索引列的值必須唯一，單允許有空值。如果是組合索引，則列值的組合必須唯一。主鍵索引是一種特殊的唯一索引，不允許有空值。 2、單列索引和組合索引 單列索引即一個索引只包含單個列，一個表可以有多個單個索引。 組合索引指在表的多個欄位組合上創建的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用。使用組合索引時遵循最左首碼集合。 3、全文索引 全文索引類型為FULLTEXT，在定義索引的列上支持值得全文查找，允許在這些索引列中插入重覆值和空值。全文索引可以在CHAR、VARCHAR或者TEXT類型的列上創建。MySQL中只有MyISAM存儲引擎支持全文索引。 4、空間索引 空間索引是對空間數據類型的欄位建立的索引，MySQL中的空間數據類型有4中，分別是：GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL關鍵字進行擴展，使用能夠用於創建正規索引類似的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲引擎為MyISAM的表中創建。 三、索引的設計原則 索引設計不合理或者缺少索引都會對資料庫和應用程式的性能造成障礙。高效的索引對於獲得良好的性能非常重要。 設計索引時，應該考慮的一些準則： （1）索引並非越多越好，一個表中如有大量的索引，不僅占用磁碟空間，而且會影響INSERT、DELETE、UPDATE等語句的性能，因為表中的數據更改的同時，索引也會進行調整和更新。 （2）避免對經常更新的表進行過多的索引，並且索引中的列儘可能少。而對經常用於查詢的欄位應該創建索引，單要避免添加不必要的欄位。 （3）數據量小的表最好不要使用索引，由於數據較少，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果。 （4）在條件表達式中經常用到的不同值較多的列建立索引，在不同值少的列上不要建立索引。比如在學生表的“性別”欄位上只有“男”和“女”兩個不同值，因此就無須建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重較低更新速度。 （5）當唯一性是某種數據本身的特征時，指定唯一索引。使用唯一索引需能確保定義的列的數據完整性，以提高查詢速度。 （6）在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引。 四、創建索引 1、創建表的時候創建索引 使用CREATE TABLE創建表時，除了可以定義列的數據類型，還可以定義主鍵約束、外鍵約束或者唯一約束，而不論創建那種約束，在定義約束的同時相當於在指定列上創建了一個索引。 （1）創建普通索引 最基本的索引類型，沒有唯一性之類的限制，其作用只是加快對數據的訪問速度。 eg： CREATE TABLE book ( bookid INT NOT NULL, bookname VARCHAR(255) NOT NULL, authors VARCHAR(255) NOT NULL, info VARCHAR(255), comment VARCHAR(255), year_publication YEAR NOT NULL, INDEX(year_publication) )； （2）創建唯一索引 創建索引的主要原因是減少查詢索引列操作的時間，尤其是對比較龐大的數據表。它與前面的普通索引類似，不同就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值得組合必須唯一。 eg： CREATE TABLE t1 ( id INT NOT NULL, name CHAR(30) NOT NULL, UNIQUE INDEX UniqIdx(id) )； （3）創建單列索引 單列索引是在數據表中的某一個欄位上創建的索引，一個表可以闖將多個單列索引。上面兩個例子中創建都是單列索引。 eg： CREATE TBALE t2 ( id INT NOT NULL, name CHAR(50), INDEX SingleIdx(name(20)) ----名為SingleIdx的單列索引，索引長度為20 )； （4）創建組合索引 組合索引是在多個欄位上創建一個索引。 eg: CREATE TABLE t3 ( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), INDEX MultiIdx(id, name, age(100)) )； 組合索引可起幾個索引的作用，但是使用時並不是隨便查詢那個欄位都可以使用索引，而是遵從“最左首碼”：利用索引中最左邊的列集類匹配行，這樣的列集稱為最左首碼。例如這裡由id、name和age3個欄位構成的索引，索引行中按id/name/age的順序存放，索引可以搜索下麵欄位組合：（id，name， age）、（id，name）或者id，如果列不構成索引最左面的首碼，MySQL不能使用局部索引，如（age）或者（name，age）組合不能使用索引查詢。 （5）創建全文索引 FULLTEXT全文索引可以用於全文搜索。只用MyISAM存儲引擎支持FULLTEXT索引，並且只為CHAR、VARCHAR和TEXT列。索引總是對整個列進行，不支持局部（首碼）索引。 eg： CREATE TABLE t4 ( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEX FullTxtIdx(info) ) ENGINE=MyISAM; （6）創建空間索引 空間索引必須在MyISAM類型的表中創建，且空間類型的欄位必須為非空。 eg： CREATE TABLE t5 ( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) ) ENGINE=MyISAM; 2、在已經存在的表上創建索引 在已經存在的表中創建索引，可以使用ALTER TABLE或者CREATE INDEX語句。 使用ALTER TABLE語句： ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name](col_name[length],...)[ASC|DESC] 使用CREATE INDEX語句： CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length,...)[ASC|DESC] 三、刪除索引 使用ALTER TABLE語句刪除索引： ALTER TABLE table_name DROP INDEX index_name; 使用DROP INDEX語句刪除索引： DROP INDEX index_name ON table_name; 四、查看索引 SHOW INDEX FROM table_name; SHOW KEYS FROM table_name;
