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));