1. 首頁 > 生活百科 > >

4 MySQL學習---MySQL索引( 四 )

生活百科


按表列屬性分類，有以下幾種索引類型：
普通索引（Key）作為MySQL中的基本索引類型，允許在定義索引的列中插入重復值和空值 。
唯一索引（Unique）索引列的值必須有值且不能重復 ， 但允許空值 。語法如下：
# 隨表一起建立唯一索引# 唯一索引時必須保證所有的值是唯一的（除了null），若有重復數據，會報錯create table user (uid int(10) unsigned auto_increment,name varchar(50) not null,gender int(2) not null,primary key(uid),KEY(name),unique(gender));# 單獨建立唯一索引Ccreate unique index idx_gender on user(gender);# 刪除唯一索引drop index idx_gender on user;主鍵索引主鍵索引是唯一的，通常以表的ID設置為主鍵索引，一個表只能有一個主鍵索引 ， 這是它跟唯一索引的區別 。語法如下：
# 隨表一起建立主鍵索引create table user (uid int(10) unsigned auto_increment,name varchar(50) not null,gender int(2) not null,primary key(uid));create table user2 (uid int(10) unsigned,name varchar(50) not null,gender int(2) not null,primary key(uid));# 單獨建立主鍵索引alter table user add primary key user(name);# 刪除主鍵索引alter table user drop primary key;# 修改主鍵索引# 必須先刪除掉（drop）原索引，再新建（add）索引全文索引（FULLTEXT）全文索引類型為FULLTEXT，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值 。全文索引可以在char、varchar或者text類型的列上創建 。
空間索引（SPATIAL）空間索引是對空間數據類型的字段建立的索引，MySQL中的空間數據類型有4種，分別是geometry、point、linestring和polygon 。MySQL使用SPATIAL關鍵字進行擴展 ， 使得能夠用于創建正規索引類似的語法創建空間索引 。創建空間索引的列必須聲明為NOT NULL 。
按索引列的數量分類，有以下幾種索引類型：
單列索引單列索引即一個索引只包含單個列 ， 一個表中可以有多個單列索引 。語法如下：
# 隨表一起建立單列索引 。索引名同列名（name）create table user (uid int(10) unsigned auto_increment,name varchar(50) not null,gender int(2) not null,primary key(uid),key(name));# 單獨建立單列索引create index idx_name on user(name);# 刪除單列索引drop index idx_name;聯合索引/復合索引聯合索引即一個索引中包含多個列，在數據庫操作期間，聯合索引所需要的開銷更?。ㄏ嘍雜諳嗤畝喔雋薪⒌ブ鄧饕?。語法如下：
# 隨表一起建立聯合索引create table user (uid int(10) unsigned auto_increment,name varchar(50) not null,gender int(2) not null,primary key(uid),key(name),unique(gender),key(name,gender));# 單獨建立聯合索引create index idx_name_gender on user(name,gender);# 刪除聯合索引drop index idx_name_gender on user;按存儲結構分類，有以下幾種索引類型：
聚簇索引/聚集索引（Primary Key）InnoDB引擎中的聚簇索引通常由主鍵或非空唯一索引來實現的 ， 實際上是在葉子節點中同時存儲了索引和整行數據，通過該索引查詢可以直接獲取查詢數據行 。
與MYISAM引擎不同，InnoDB的數據文件本身就是索引文件 ， 表數據文件本身就是按照B+組織的一個索引結構，其葉子節點的鍵值就是表的主鍵 ， 這種數據存儲方式也被稱為聚簇索引 。由此可見，聚簇索引并不是一種單獨的索引類型，而是一種數據存儲方式 。
聚簇索引的葉子節點都包含主鍵值、事務ID、用于事務MVCC的回滾指針以及所有的剩余列 。
注：InnoDB存儲引擎的表會存在主鍵（唯一且非空），如果建表的時候沒有指定主鍵，則會使用第一個非空的唯一索引作為聚集索引，否則InnoDB會自動幫你創建一個不可見的、長度為6字節的row_id用來作為聚集索引 。
非聚簇索引/輔助索引/二級索引非聚簇索引也叫輔助索引、二級索引等，葉子節點存儲索引值和主鍵值 。當需要訪問數據時，根據主鍵從聚簇索引中查詢 。
與MYISAM引擎的輔助索引實現不同，InnoDB的輔助索引 ， 其葉子節點存儲的不是行指針而是主鍵值 ， 得到主鍵值再要查詢具體行數的話，要去聚簇索引中再查找一次，也叫回表 。這樣的策略優勢是減少了當出現行移動或者數據頁分裂時二級索引的維護工作 。
索引的創建原則