MySQL索引相关命令

一、索引

1、概念

2、索引的作用

3、创建索引的原则依据

二、创建索引

1、创建一个新的数据库和表

mysql -u root -p

create database school;
use school;
create table class( id int(10) not null, name varchar(20) not null, sex char(2) not null, cardid varchar(20) not null, phone varchar(11), address varchar(50));
desc class;

insert into class values ('1','srs1','男','00001','111111','中国');
insert into class values ('2','srs2','男','00002','222222','安徽');
insert into class values ('3','srs3','男','00003','333333','滁州');
insert into class values ('4','srs4','男','00004','444444','凤阳');
insert into class values ('5','srs5','男','00005','555555','府城');
select * from class;

2、索引的分类和创建

1、 普通索引

最基本的索引类型，没有唯一性之类的限制。
①直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);
#(列名(length))：length是可选项，下同。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾。

例：
create index phone_index on class (phone);
show keys from class\G;

②修改表方式创建

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

例：
alter table class add index id_index (id);
show keys from class\G;

③创建表的时候指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));

例：
create table test(id int(10) not null,name varchar(20) not null,index id_index (id));

show keys from test\G;

2、唯一索引

与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。
①直接创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

例：
create unique index address_index on class(address);
create unique index id_index on class(id);

show keys from class\G;

②修改表方式创建

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

例：
alter table class add unique phone_index (phone);
show keys from class\G;

③创建表的时候指定

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

例：
create table test2(id int(10) not null,name varchar(20) not null,unique id_index (id));
show keys from test2\G;

3、主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。
①创建表的时候指定

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

例：
create table test3(id int(10) not null,name varchar(20) not null,primary key (id));
或
create table test3(id int(10) not null primary key,name varchar(20) not null);

show keys from test3\G;

②修改表方式创建

ALTER TABLE 表名 ADD PRIMARY KEY (列名); 

例：
alter table class add primary key(id);
show keys from class\G;

4、组合索引（单列索引与多列索引）

可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

例:
create table test4(id int(10) not null,name varchar(20) not null,sex char(2) not null,index index_amd (id,name));

show keys from test4\G;
insert into test4 values(1,'srs','男');
select * from test4 where id=1 and name='srs';

5、全文索引（FULLTEXT）

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。
在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎;
在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。
全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。
①直接创建索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

例：
create fulltext index address_index on class (address);

②修改表方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

例：
alter table class add fulltext address_index (address);

③创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT

例：
create table test6(id int(10) not null,name varchar(20) not null,address varchar(50),fulltext address_full(address));

④使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

例：
select * from test6 where match(address) against('anhui');

3、查看索引

三、删除索引

1、直接删除索引

DROP INDEX 索引名 ON 表名；
例：
drop index address_index on class;

2、修改表方式删除索引

ALTER TABLE 表名 DROP INDEX 索引名；
例：
alter table class drop index id_index;

3、删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;
例：
alter table class drop primary key;