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;