MySQL笔记2.6索引

2.6索引

2.6.1索引的概念

数据库的索引好比新华字典的音序表，它是对数据库表中一列或多列的值进行排序后的一种结构，其作用就是提高表中数据的查询速度

• 1.普通索引

  普遍索引是由KEY或INDEX定义的索引，它是MySQL中的基本索引类型,可以创建在任何数据类型中，其值是否唯一和非空 由字段本身的约束条件所决定。

• 2.唯一性索引

  唯一性索引是由UNIQUE定义的索引,该索引所在字段的值必须是唯一的。

• 3.全文索引

  全文索引是由FULLTEXT定义的索引，它只能创建在CHAR、VARCHAR或TEXT类型的字段上,而且,现在只有MyISAM存储引擎支持全文索引。

• 4.单列索引

  单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一索引或者全文索引,只要保证该索引只对应表中一个字段即可。

• 5.多列索引

  多列索引指的是在表中多个字段.上创建索引，只有在查询条件中使用了这些字段中的第一个字段时,该索引才会被使用。

• 6.空间索引

  空间索引是由SPATIAL定义的索引，它只能创建在空间数据类型的字段上。创建时必须声明为not null,并且只有MyISAM存储引擎支持

---

2.6.2 创建索引

> 1.创建表的时候创建索引

语法:CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
  					  字段名 数据类型 [完整性约束条件],
  					  ...
  					  		字段名 数据类型
  					  		[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
  					  			[别名] (字段名1 [(长度)] [ASC|DESC])
  					  			);

• UNIQUE:可选参数，表示唯一索引
• FULLTEXT:可选参数，表示全文索引
• SPATIAL:可选参数，表示空间索引
• INDEX和KEY:用来表示字段的索引，二者选一即可
• ASC和DESC可选参数，ASC表示升序排列，DESC表示降序排列
• 别名:可选参数，表示创建的索引的名称
• 字段名1:指定索引对应字段的名称，
• 长度:可选参数，用于表示索引的长度

>1.创建普通索引

mysql> create table t2(
    -> id int(11),
    -> name varchar(20),
    -> grade float,
    -> index index(id asc)/*创建名为index的索引,升序排列*/
    -> );
建立后,使用show create table查看表的结构
	

==show create table t1\G; t1\G 使排列有序
为了查看索引是否被使用可以使用explain语句进行查看,SQL代码如下:

explain select * from t1 while id=1 \G

>2.创建唯一性索引

mysql> create table t2(
    -> id int(11),
    -> name varchar(20),
    -> grade float,
    -> unique index unique_id(id asc)/*在表中id字段上建立索引名为unique_id的唯一性索引,并且按照升序排序*/
    -> );

>3.创建全文索引

mysql> create table t1(
    -> id int(11),
    -> name varchar(20),
    -> grade float,
    -> fulltext index fulltext_name(name)
    -> )engine=myisam;/*因为目前只有MyISAM存储引擎支持全文索引*/

>4.创建单列索引

mysql> create table t4(
    ->name varchar(20) not null,
    -> score float,
    -> index single_name(name(20))/*在name字段上创建一个名叫single_name的单列索引索引长度为20*/
    -> )

>5.创建多列索引

mysql> create table t5(
	->id int not null,
    ->name varchar(20) not null,
    -> score float,
    -> index multi(id,name(20))/*在id和name字段上创建一个名叫multi的多列索引*/
    -> )

注意:在多列索引中,只有查询条件中使用了这些字段的第一个字段时多列索引才会被使用

>6.创建空间索引

mysql> create table t6(
	->id int,
    ->space geometry not null,
    ->spatial index sp(space)/*在space字段上建立一个名为sp的空间索引*/
    ->)engine=myisam;

注意:创建空间索引时,所在字段不能为空值,并且表的储存引擎为myisam

---

2.使用CREATE INDEX语句在已经存在的表上创建索引

语法CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 
	ON 表名 (字段名 [(长度)] [ASC|DESC]);

mysql> create table book(
    -> bookid int not null,
    -> bookname varchar(255) not null,
    -> authors varchar(255) not null
    -> info varchar(255) null,
    -> comment varchar(255) null,
    -> publicyear year not null
    -> );

>1.创建普通索引

mysql> create index index_id on book(bookid);/*在book表中的bookid字段上建立一个名为index_id的普通索引*/
mysql> show create table book\g;/*查看数据表结构*/

>2.创建唯一性索引

create unique index uniqueidx on book(bookid);
/*在bookid字段上创建一个名为uniqueidx的唯一性索引*/

>3.创建单列索引

create index singleidx on book(comment);
/*在comment字段上创建一个名称为singleidx的单列索引*/

>4.创建多列索引

create index mulitidx on book(authors(20)),info(20));
/*在authors和info字段上创建一个名称为mulitidx的多列索引*/

>5.创建全文索引

create fulltext index fulltextidx on book(info);
/*在info字段上创建名为fulltextidx的全文索引*/

>6.创建空间索引

create table t7(
				g geometry not null
				)engine=myisam;
create spatail index spatidx on t7(g);/*在g字段上创建名称为spatidx的空间索引*/

---

3.使用ALTER TABLE 语句在已经存在的表上创建索引

语法:ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX  
					索引名 (字段名[(长度)] [ASC|DESC] )

create table book(
				  bookid int not null,
				  bookname varchar(255) not null,
				  authors varchar(255) null,
				  comment varchar(255) null,
				  pubblicyear year not null
				  );

>1.创建普通索引

alter table book add index id(bookid);

>2.创建唯一性索引

alter table book add unique uniqueidx(bookid);

>3.创建单列索引

alter table book add index singleidx(comment(50));

>4.创建多列索引

alter table book add index multidx(authors(20),info(50));

>5.创建全文索引

alter table book add fulltext index fulltextidx(info);

>6.创建空间索引

create table t8(
				space geometry not null
				)engine=myisam;
alter table t8 add spatial index spatidx(space);

---

2.6.3删除索引

1.使用alter table删除索引

语法:alter table 表名 drop index 索引名;

2.使用 drop index删除索引

语法:drop index 索引名 on 表名;