《MySQL索引使用》

查看表结构

SHOW CREATE TABLE 表名;

查看表中创建的索引

SHOW INDEX FROM 表名;

索引介绍

使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一约束，

而不论创建那种约束，在定义约束的同时相当于在指定列上创建了一个索引，创建表时创建索引的基本

语法如下：

CREATE TABLE table_name [col_name data_type]

[UNIQUE | FULLTEXT | SPATIAL]

[INDEX | KEY]

[index_name]

(col_name[length])

[ASC | DESC]

UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引、空间索引；

INDEX、KEY为同义词，两者作用相同，用来指定创建索引；

index_name指定索引的名称，为可选参数，如果不指定MySQL默认col_name为索引值；

col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；

length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

ASC、DESC指定升序或者降序的索引值存储；

 

使用EXPLAIN查看索引是否正在使用

explain select * from 表名 where 条件;

select_type行指定所使用的SELECT查询类型，这里的值是SIMPLE，表示简单的SELECT，

不使用UNION或者子查询。可能有的取值有：PRIMARY、UNION、SUBQUERY等。

table行指定数据库读取的数据表名字，他们按被读取的先后顺序排列。

type行指定了本数据表与其他数据表之间的关联关系，可能的取值有：system、const、

eq_ref、ref、range、index、All。

possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。

key行是MySQL实际选用的索引。

key_len行给出索引按字节计算的长度，key_len数值越小，表示越快。

ref行给出了关联关系中另一个数据表里的数据列的名字

rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行个数。

extra行提供了与关联操作有关的信息

创建唯一索引

索引列的值必须唯一，但允许有空值

CREATE TABLE 表名

(

id    INT NOT NULL,

name CHAR(30) NOT NULL,

UNIQUE INDEX 索引名(id)

);

创建单列索引

单列索引是在数据表中某一个字段上创建的，一个表中可以创建多个单列索引

CREATE TABLE 表名

(

id   INT NOT NULL,

name CHAR(50) NULL,

INDEX 索引名(name(20))

);

创建组合索引

组合索引是在多个字段上创建一个索引

CREATE TABLE 表名

(

id    INT,

name CHAR(30),

age  INT,

info VARCHAR(255),

INDEX 索引名(id, name, age(100))

);

创建全文索引

只为CHAR、VARCHAR、TEXT列创建索引，索引总是对整个列进行，不支持局部前缀索引

并且MySQL只有MyISAM引擎支持FULLTEXT索引

CREATE TABLE 表名

(

id    INT NOT NULL,

name CHAR(30) NOT NULL,

age  INT NOT NULL,

info VARCHAR(255),

FULLTEXT INDEX 索引名(info)

) ENGINE=MyISAM;

创建空间索引

空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空

CREATE TABLE 表名

( g GEOMETRY NOT NULL, SPATIAL INDEX 索引名(g) )ENGINE=MyISAM;

在已经存在的表上创建索引

ALTER TABLE 或者CREATE INDEX创建索引

Table表示创建索引的表

Non_unique表示索引非唯一，1代表是非唯一索引，0代表唯一索引

Key_name表示索引的名称

Seq_in_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引

定义中的顺序

Column_name表示定义索引的列字段

Sub_part表示索引的长度

Null表示该字段是否能为空值

Index_type表示索引类型

在已经存在的表上创建普通索引

ALTER TABLE 表名 ADD INDEX索引名 (字段名);

在已经存在的表上创建唯一索引

ALTER TABLE 表名 ADD UNIQUE INDEX索引名 (字段名 );

在已经存在的表上创建组合索引

ALTER TABLE 表名 ADD INDEX索引名 (字段1,字段2 );

在已经存在的表上创建全文索引

ALTER TABLE 表名 ADD FULLTEXT INDEX索引名 (字段 );

在已经存在的表上创建空间索引

ALTER TABLE 表名 ADD SPATIAL INDEX索引名(字段);

使用ALTER TABLE删除索引

基本格式

ALTER TABLE 表名 DROP INDEX索引名;

使用DROP INDEX删除索引

基本格式

DROP INDEX 索引名 ON表名;