mysql創建索引的三種辦法

一、CREATE INDEX方法

成都創新互聯服務項目包括原平網站建設、原平網站制作、原平網頁制作以及原平網絡營銷策劃等。多年來，我們專注于互聯網行業，利用自身積累的技術優勢、行業經驗、深度合作伙伴關系等，向廣大中小型企業、政府機構等提供互聯網行業的解決方案，原平網站推廣取得了明顯的社會效益與經濟效益。目前，我們服務的客戶以成都為中心已經輻射到原平省份的部分城市，未來相信會繼續擴大服務區域并繼續獲得客戶的支持與信任！

CREATE INDEX 索引名 ON 表名 (列名 [長度] [ ASC | DESC])

限制：只能增加普通索引INDEX和UNIQUE INDEX索引這兩種；不能創建PRIMARY KEY索引

二、ALTER TABLE方法

ALTER TABLE 表名 ADD INDEX [索引名] (列名,…)

ALTER TABLE 表名 ADD UNIQUE [ INDEX | KEY] [索引名] (列名,…)

ALTER TABLE 表名 ADD PRIMARY KEY (列名,…)

ALTER TABLE 表名 ADD FOREIGN KEY [索引名] (列名,…)

三、CREATE TABLE時候指定

創建一般索引

CREATE TABLE tb_stu_info

(

id INT NOT NULL,

name CHAR(45) DEFAULT NULL,

dept_id INT DEFAULT NULL,

age INT DEFAULT NULL,

height INT DEFAULT NULL,

INDEX(height)

);

創建唯一索引

mysql CREATE TABLE tb_stu_info2

(

id INT NOT NULL,

name CHAR(45) DEFAULT NULL,

dept_id INT DEFAULT NULL,

age INT DEFAULT NULL,

height INT DEFAULT NULL,

UNIQUE INDEX(height)

);

創建主鍵（雖然ALTER TABLE也能創建，但主鍵一般都在創表時建立）

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

PRIMARY KEY(ID)

);

如何查看MySQL索引

查看索引

mysql show index from tblname;

mysql show keys from tblname;

· Table

表的名稱。

· Non_unique

如果索引不能包括重復詞，則為0。如果可以，則為1。

· Key_name

索引的名稱。

· Seq_in_index

索引中的列序列號，從1開始。

· Column_name

列名稱。

· Collation

列以什么方式存儲在索引中。在MySQL中，有值‘A’（升序）或NULL（無分類）。

· Cardinality

索引中唯一值的數目的估計值。通過運行ANALYZE TABLE或myisamchk -a可以更新。基數根據被存儲為整數的統計數據來計數，所以即使對于小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL使用該索引的機 會就越大。

· Sub_part

如果列只是被部分地編入索引，則為被編入索引的字符的數目。如果整列被編入索引，則為NULL。

· Packed

指示關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。

· Null

如果列含有NULL，則含有YES。如果沒有，則該列含有NO。

· Index_type

用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

· Comment

MySQL索引

MySQL的Innodb存儲引擎的索引分為聚集索引和非聚集索引兩大類

特點：B+樹葉子節點存儲行數據

一個表中，必須有一個聚集索引，只能有一個聚集索引，Innodb通常把一個表的主鍵索引作為聚集索引，如果沒有主鍵InnoDB會選擇一個唯一索引代替。如果沒有這樣的索引，InnoDB會隱式的定義一個主鍵來作為聚集索引，這個字段為6個字節，類型為長整形。

利用主鍵索引查找行數據是最快的，建議使用自增主鍵原因是利于索引樹的構建（主鍵自增寫入時新插入的數據不會影響到原有頁，插入效率高；但是如果主鍵是無序的或者隨機的，那每次的插入可能會導致原有頁頻繁的分裂，影響插入效率）

特點：B+樹葉子節點存儲主鍵ID

一個表中可以有多個非聚集索引，每個非聚集索引即是一棵B+樹

通過非聚集索引查找數據時，需要先在非聚集索引上找到主鍵ID，再從聚集索引獲取行數據，這個過程就稱之為回表

B樹索引中的B樹實際上是B+樹，至于為什么使用B+樹而不使用B樹或者紅黑樹的原因在另外的文章中有提及。

特點：

特點：類似JDK中的HashMap，但無法支持范圍查詢

特點：使用的算法仍然是B樹索引，不同的就是索引列的值必須唯一

對于普通索引來說，查找到滿足條件的第一個記錄后，需要查找下一個記錄，直到碰到第一個不滿足條件的記錄。

對于唯一索引來說，由于索引定義了唯一性，查找到第一個滿足條件的記錄后，就會停止繼續檢索，提升索引性能

另外插入行時會構建該唯一索引，假如索引值重復將插入失敗，適合業務上做唯一性檢驗

通過建立倒排索引，可以極大的提升檢索效率,解決判斷字段是否包含的問題，但是業務上一般都不采用這種索引，而是使用ES處理全文搜索需求

僅對某個特定字段建立的索引，如(biz_id)

對多個字段建立的索引，如(biz_id,type)