创建表的时候创建索引
1.创建普通索引http://www.cndba.cn/hbhe0316/article/4838

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,
    -> year_publication YEAR NOT NULL,
    -> INDEX(year_publication)
    -> );
Query OK, 0 rows affected (0.11 sec)

2.创建唯一索引http://www.cndba.cn/hbhe0316/article/4838

mysql> CREATE TABLE t1
    -> (
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> UNIQUE INDEX UniqIdx(id)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW CREATE TABLE t1 /G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) COLLATE utf8mb4_general_ci NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

可以看到创建了一个名叫UniqIdx的唯一索引http://www.cndba.cn/hbhe0316/article/4838

3.创建单列索引

mysql> CREATE TABLE t2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(50) NULL,
    -> INDEX SingleIdx(name(20))
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW CREATE TABLE t2 /G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

创建了一个名叫SingleIdx的单列索引

4.创建组合索引

CREATE TABLE t3
(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(255),
    INDEX MultiIdx(id, name, age)
);
mysql> SHOW CREATE TABLE t3 /G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30) COLLATE utf8mb4_general_ci NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  KEY `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

创建了MultiIdx的组合索引

5.创建全文索引http://www.cndba.cn/hbhe0316/article/4838

CREATE TABLE t4
(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(255),
    FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;

只有MyISAM才支持全文索引，CHAR，VARCHAR，TEXT列才可以创建索引。

6.创建空间索引

http://www.cndba.cn/hbhe0316/article/4838

mysql> CREATE TABLE t5(g GEOMETRY NOT NULL, SPATIAL INDEX apatId(g)) ENGINE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> SHOW CREATE TABLE t5 /G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `apatId` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

只有在MyISAM上可以创建空间索引

9.2.2在已经创建的表上增加索引

9.3删除索引

ALTER TABLE table_name DROP INDEX index_name;

版权声明：本文为博主原创文章，未经博主允许不得转载。

MySQL