索引的创建和删除

创建普通索引

 

语法

create table t1 (

   idint primary key,

  name varchar(10),

  sex ENUM(‘F’,’M’,’UN’)

  index(name)

)engine=myisam character set utf8;

 

创建索引

mysql> create table t_1 ( id int, namevarchar(10),index(name) );

Query OK, 0 rows affected (0.06 sec)

 

查看语句

mysql> show create table t_1\G;

*************************** 1. row***************************

      Table: t_1

Create Table: CREATE TABLE `t_1` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

  KEY`name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

 

创建唯一索引

 

语法

create table t1 (

   idint primary key,

  name varchar(10),

  sex ENUM(‘F’,’M’,’UN’)

   uniqueindex id_in (id)

)engine=myisam character set utf8;

 

创建索引

mysql> create table t_2 (

   -> id int,

   -> name varchar(10),

   -> unique index idInx (id)

   -> );

Query OK, 0 rows affected (0.24 sec)

 

查看语句

mysql> show create table t_2\G;

*************************** 1. row***************************

      Table: t_2

Create Table: CREATE TABLE `t_2` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

 UNIQUE KEY `idInx` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

创建单列索引

 

语法

create table t1 (

   idint primary key,

  name varchar(10),

  sex ENUM(‘F’,’M’,’UN’)

  index name_in (name(10))

)engine=myisam character set utf8;

 

创建索引

mysql> create table t_3 (

   -> id int,

   -> name varchar(10),

   -> index idinx (name(10))

   -> );

Query OK, 0 rows affected (0.06 sec)

 

查看语句

mysql> show create table t_3\G;

*************************** 1. row***************************

      Table: t_3

Create Table: CREATE TABLE `t_3` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

  KEY`idinx` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

创建组合索引

 

语法

create table t1 (

   idint not null,

  name varchar(20),

  age int not null,

  index multi_in (id,name,age(100))

)engine=myisam character set utf8;

 

创建索引

mysql> create table t_4 (

   -> id int,

   -> name varchar(10),

   -> age int,

   -> index MutiIdx (id,name,age)

   -> );

Query OK, 0 rows affected (0.07 sec)

 

插入数据

mysql> insert into t_4 values

   -> (1,'AAA',10),

   -> (2,'BBB',20),

   -> (3,'CCC',30),

   -> (4,'DDD',40),

   -> (5,'EEE',50);

Query OK, 5 rows affected (0.05 sec)

Records: 5 Duplicates: 0  Warnings: 0

 

mysql> select * from t_4;

+------+------+------+

| id  | name | age  |

+------+------+------+

|   1 | AAA  |   10 |

|   2 | BBB  |   20 |

|   3 | CCC  |   30 |

|   4 | DDD  |   40 |

|   5 | EEE  |   50 |

+------+------+------+

5 rows in set (0.00 sec)

 

 

mysql> explain select name,age from t_4where id<3\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: t_4

  partitions: NULL

        type: range

possible_keys: MutiIdx

         key: MutiIdx

     key_len: 5

         ref: NULL

        rows: 2

    filtered: 100.00

       Extra: Using where; Using index

1 row in set, 1 warning (0.00 sec)

 

ERROR:

No query specified

 

mysql> explain select name,age from t_4where id<3 and age<50\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: t_4

  partitions: NULL

         type: range

possible_keys: MutiIdx

         key: MutiIdx

     key_len: 5

         ref: NULL

        rows: 2

    filtered: 33.33

        Extra: Using where; Using index

1 row in set, 1 warning (0.00 sec)

 

ERROR:

No query specified

 

 

mysql> explain select name,age from t_4where age<50\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: t_4

  partitions: NULL

         type: index

possible_keys: NULL

         key: MutiIdx

     key_len: 23

         ref: NULL

        rows: 5

    filtered: 33.33

       Extra: Using where; Using index

1 row in set, 1 warning (0.00 sec)

 

ERROR:

No query specified

注：组合索引查询的时候需要包含最左面列的关键字，才会使用到索引，否则不会使用到索引。

 

 

创建全文索引

 

语法

create table t1 (

   idint not null,

  name varchar(20),

  age int not null,

   infoTEXT,

  fulltext index info_in (info)

)engine=myisam character set utf8;

 

创建索引

 

 

 

 

用alter语句修改和删除索引

 

语法

Alter table t1 add name_in (name);

Alter table t1 drop index name_in;

 

查看语句

mysql> show create table t_5\G;

*************************** 1. row***************************

      Table: t_5

Create Table: CREATE TABLE `t_5` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

增加索引

mysql> alter table t_5 add index nameIdx(name);

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0  Warnings: 0

 

查看语句

mysql> show create table t_5\G;

*************************** 1. row***************************

      Table: t_5

Create Table: CREATE TABLE `t_5` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

  KEY`nameIdx` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

 

增加唯一性索引

 

mysql> alter table t_5 add unique indexnameIdx1 (name);

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0  Warnings: 0

 

mysql> show create table t_5\G;

*************************** 1. row***************************

      Table: t_5

Create Table: CREATE TABLE `t_5` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

 UNIQUE KEY `nameIdx1` (`name`),

  KEY`nameIdx` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

删除索引

 

mysql> alter table t_5 drop indexnameIdx;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0  Warnings: 0

 

查看

mysql> show create table t_5\G;

*************************** 1. row***************************

      Table: t_5

Create Table: CREATE TABLE `t_5` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

 UNIQUE KEY `nameIdx1` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

 

 

用create语句修改索引

 

语法

Create index name_in on t1(name);

 

查看

mysql> show create table t_5\G;

*************************** 1. row***************************

      Table: t_5

Create Table: CREATE TABLE `t_5` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

 UNIQUE KEY `nameIdx1` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

增加索引

mysql> create index idIdx on t_5(id);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0  Warnings: 0

 

 

查看

mysql> show create table t_5\G;

*************************** 1. row***************************

      Table: t_5

Create Table: CREATE TABLE `t_5` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

 UNIQUE KEY `nameIdx1` (`name`),

  KEY`idIdx` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

用drop语句删除索引

 

语法

Drop index name_in on t1;

 

查看

mysql> show create table t_5\G;

*************************** 1. row***************************

      Table: t_5

Create Table: CREATE TABLE `t_5` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

 UNIQUE KEY `nameIdx1` (`name`),

  KEY`idIdx` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

删除

mysql> drop index idIdx on t_5;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0  Warnings: 0

 

查看

mysql> show create table t_5\G;

*************************** 1. row***************************

      Table: t_5

Create Table: CREATE TABLE `t_5` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

 UNIQUE KEY `nameIdx1` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

ERROR:

No query specified