MySQL 的索引型別及如何建立維護

索引在MySQL中特別重要，用好了可以很大提升MySQL的效能。

索引的定義

是一種可以幫助MySQL高效獲取資料的資料結構，也可以說是一種排好序的快速查詢資料結構。
主要有以下幾種資料結構：
1. B+TREE 索引（也叫BTREE或B-TREE，預設及最常用的）
2. HASH 索引
3. RTREE 索引
4. FULLTEXT（全文索引）

索引的種類

1. 主鍵索引
2. 唯一索引
3. 普通索引
4. 組合索引
5. 全文索引

語句

常用以下兩種方式：
1. CREATE [UNIQUE|FULLTEXT] INDEX [indexName] ON tableName(columnname(length))
2. ALTER TABLE tableName ADD [UNIQUE|FULLTEXT] INDEX [indexName] (colummnname(length))
備註：
也可以在建立表的同時建索引
* unique|fulltext為可選引數，分別表示唯一索引、全文索引
* columnname為需要建立索引的欄位列，該列必須從資料表中該定義的多個列中選擇
* indexName指定索引的名稱，為可選引數，如果不指定，預設colummnname為索引值
* length為可選引數，表示索引的長度，只有字串型別的欄位才能指定索引長度

不同型別的索引建立維護

主鍵索引

1、建立表同時設定主鍵

create table teacher(
-> id int(10) auto_increment,
-> name varchar(20),
-> age int(10),
-> phone varchar(11),
-> primary key (id));//主鍵設定

2、單獨設定主鍵

alter table teacher add primary key (id);

desc tableName;//顯示錶的結構
show index from tableName \G //檢視錶的索引情況

唯一索引

1、建立表同時建唯一索引

create table teacher(
    -> id int(10) auto_increment,
    -> name varchar(20),
    -> age int(10),
    -> phone varchar(11),
    -> primary key (id),
    -> unique index idx_phone(phone(11)));//唯一索引

2、單獨建唯一索引

create unique index idx_phone on teacher(phone(11));

drop index idexName on tableName;//刪除索引

3、修改建唯一索引

alter table teacher add unique idx_phone (phone(11));

普通索引

1、建立表同時建普通索引

create table teacher(
    -> id int(10) auto_increment,
    -> name varchar(20),
    -> age int(10),
    -> phone varchar(11),
    -> primary key (id),
    -> index idx_phone (phone(11)));//普通索引

2、單獨建普通索引

create index idx_phone on teacher(phone(11));

3、修改普通索引

alter table teacher add index idx_phone (phone(11));

組合索引

1、建立表同時建組合索引

create table teacher(
    -> id int(10) auto_increment,
    -> name varchar(20),
    -> phone varchar(11),
    -> primary key (id),
    -> index idx_name_phone (name(20),phone(11)));//組合索引

2、單獨建組合索引

create index idx_name_phone on teacher (name(20),phone(11));

3、修改組合索引

alter table teacher add index idx_name_phone (name(20),phone(11));

全文索引

1、建立表同時建全文索引

create table teacher(
    -> id int(10) auto_increment,
    -> name varchar(20),
    -> age int(10),
    -> phone varchar(11),
    -> primary key (id),
    -> fulltext index idx_phone(phone(11)));//全文索引

2、單獨建全文索引

create fulltext index idx_phone on teacher(phone(11));

3、修改全文索引

alter table teacher add fulltext index idx_phone (phone(11));