MySQL使用教程【六】索引的操作

一、创建和查看索引

1、创建和查看普通索引

普通索引：创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。

（1）创建表时创建普通索引
【语法形式】

create table table_name(
 
        属性名1 数据类型，
 
        属性名2 数据类型，
 
        属性名3 数据类型，
 
        index|key 索引名(属性名1 【(长度)】 【ASC|DESC】) 
);

【实例】

#选择数据库
mysql> use test;

#创建索引
mysql> create table t_deptest(
    -> deptno int,
    -> dname varchar(20),
    -> index index_deptno(deptno)
    -> );
    
#校验索引是否创建成功
mysql> show create table t_deptest \G

#校验索引是否被使用
mysql> explain select * from t_deptest where deptno=1\G

注：\G后边不跟分号。否则会有no query specified错误

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

【语法形式】

create index 索引名 on 表名(属性名 【(长度)】 【ASC|DESC】);

【实例】

mysql> create index index_deptno on t_dept(deptno);

（3）通过SQL语句ALTER TABLE 创建普通索引

【语法形式】

alter table table_name add index 索引名(属性名 【(长度)】 【ASC|DESC】) 

【实例】

mysql> alter table t_dept add index index_deptno(deptno);

2、创建和查看唯一索引

所谓唯一索引，就是在创建索引时，限制索引的值必须是唯一的，通过该类型的索引可以更快速的查询某条记录。根据创建索引方式，可以分为自动索引和手动索引两种。

• 自动索引：在数据库中设置完整性约束时，该表会被自动创建索引；
• 手动索引：手动在表上创建索引；

（1）创建表时创建唯一索引

【语法形式】

create table table_name(
 
    属性名 数据类型，
 
    属性名 数据类型，
 
    unique index 索引名(属性名 【(长度)】 【ASC|DESC】) 
 
);

【实例】

mysql> create table t_dept1(
    -> deptno int unique,
    -> dname varchar(20),
    -> unique index index_deptno(deptno)
    -> );

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

【语法形式】

create unique index 索引名 on 表名(属性名 【(长度)】 【ASC|DESC】);

【实例】

mysql> create unique index index_deptno on t_dept1 (deptno) ;

（3）通过SQL语句ALTER TABLE 创建唯一索引

【语法形式】

alter table table_name add unique index|key 索引名(属性名 【(长度)】 【ASC|DESC】);

【实例】

mysql> alter table t_dept add unique index index_deptno(deptno);

3、创建和查看全文索引

全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上，以便能够快速查询数据量较大的字符串类型的字段。只能在存储引擎为MyISAM的数据库表上创建全文索引。

（1）创建表时创建全文索引

【语法形式】

create table table_name(
 
        属性名1 数据类型，
 
        属性名2 数据类型，
 
        属性名3 数据类型，
 
        fulltext index|key 索引名(属性名 【(长度)】 【ASC|DESC】) 
);

【实例】

mysql> create table t_dept3(
    -> deptno int,
    -> dname varchar(20),
    -> fulltext index index_dname(dname)
    -> ) ENGINE=MyISAM;

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

【语法形式】

create fulltext index 索引名 on 表名 (属性名 【(长度)】 【ASC|DESC】);

【实例】

mysql> create fulltext index index_name on t_dept3(dname);

（3）通过SQL语句ALTER TABLE 创建全文索引

【语法形式】

alter table table_name add fulltext index 索引名(属性名 【(长度)】 【ASC|DESC】);

【实例】

mysql> alter table t_dept3 add fulltext index index_doname(dname);

4、创建和查看多列索引

所谓多列索引，是指在创建索引时，所关联的字段不是一个字段，而是多个字段。虽然可以通过所关联的字段查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。

（1）创建表时创建多列索

【语法形式】

create table table_name(
 
    属性名 数据类型，
 
    属性名 数据类型，
 
    index|key 索引名 (属性名1 【(长度)】 【ASC|DESC】,
                      …………
                      属性名m 【(长度)】 【ASC|DESC】)
 
);

【实例】

mysql> create table t_dept4(
    -> deptno int,
    -> dname varchar(20),
    -> loc varchar(20),
    -> key index_dname_loc(dname,loc)
    -> );

（2）在已经存在的表上创建多列索引

【语法形式】

 create index 索引名 on 表名(属性名1 【(长度)】 【ASC|DESC】,
                             …………
                             属性名m 【(长度)】 【ASC|DESC】)
                             );

【实例】

mysql> create index index_dname_loc on t_dept4(dname,loc);

（3）通过SQL语句ALTER TABLE 创建全文索引

【语法形式】

alter table table_name add index|key 索引名(属性名1 【(长度)】 【ASC|DESC】,
                             …………
                             属性名m 【(长度)】 【ASC|DESC】)
);

【实例】

mysql> alter table t_dept4 add index index_dname_loc(dname,loc);

5、删除索引

【语法】

drop index index_name on table_name

【实例】

mysql> drop index index_dname_loc on t_dept;