MySQL数据库之索引

create table team (id int(10),name varchar(10),address varchar(10),phone int(12),hobby test,power int(3));

#为表添加以下数据
insert into team values(1,'小明','美国','1234567890','超级喜欢吃饭',66);
insert into team values(2,'小红','英国','1234566666','超级喜欢shopping',0);
insert into team values(3,'小刚','法国','1234566777','超级喜欢健身',888);
insert into team values(4,'小智','真兴镇','1234566777','超级喜欢皮卡丘',999);
insert into team values(5,'小豪','<孤儿>','334566777','超级狗',-999);

1、普通索引

最基本的索引类型，没有唯一性之类的限制

直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

列名(length)：length是可选项。如果忽略length的值，则使用整个列的值作为索引。如果指定使用列前的length个字符来创建索引，这样有利于减小索引文件的大小。

索引名建议以"_index"结尾。

2、修改表方式创建

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

alter table team add index name_index (name);
select name from team;
select name,power from team;
show create table team\G

3、创建表的适合指定索引

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

create table test2(id int(2),name varchar(10),age int(3),address varchar(20),index name_index (name));

show create table test2;

4、唯一索引

与普通索引类似，但区别是唯一索引列的每个值都唯一。

唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

直接创建唯一索引

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

select * from team;
create unique index address_index on team (address);
create unique index name_index on team (name);
show create table team\G

这里的操作和上述一样，只是创建索引的值是有唯一性的，包括建表时创建索引和alter去添加索引都是一样的。

5、主键索引

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

create table test3 (id int primary key,name varchar(20));

或（两种在创表时添加主键）

create table test3 (id int,name varchar(20),primary key (id));

当我们在创建表时忘记添加主键时，也可以使用alter去添加

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

例子：alter table test3 add primary key(name);

6、组合索引

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

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

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

还是以最开始的team表为例。（这里我删除了上面的单个索引）

我们以alter的方式添加索引，当然也可以在创建表的时候就添加

alter table team add index index_group (name,power);

7、全文索引（fulltext）

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

#直接创建全文索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);
例： create fulltext index suoyin onteam(name);

#修改表方式创建
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);
例：alter table team add fulltext index suoyin(name)

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

例：create table lcdb3(id int(4),name char(10),genter char(2), age int(2),address char(20
),fulltext index suoyin_index(address));

我们这里以alter的方式演示一下