持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第28天，点击查看活动详情

前言

上篇我们学习了MySQL中的索引创建。有兴趣的小伙伴可以阅读(MySQL学习-索引创建（一）)。
下面我们继续学习MySQL中的索引创建。

创建单列索引

CREATE TABLE test(
    id INT NOT NULL,
    name CHAR(50) NULL,
    INDEX single_idx_name(name(20))
);

创建组合索引

创建表test1，在表中的id，name字段上建立组合索引，SQL语句如下：

CREATE TABLE test1(
    id INT NOT NULL,
    name CHAR(50) NULL,
    INDEX multi_idx(id, name)
);

创建全文索引

创建表test2，在表中的name字段上建立全文索引，SQL语句如下：

CREATE TABLE test2(
    id INT NOT NULL,
    name CHAR(50) NULL,
    FULLTEXT INDEX futxt_idx_name(name)
) ENGINE=MyISAM;

在MySQL5.7以后，可以不指定最后的ENGINE了，因为此版本中的InnoDB支持全文索引。

CREATE TABLE test2(
    id INT NOT NULL,
    name CHAR(50) NULL,
    FULLTEXT INDEX futxt_idx_name(name)
) ENGINE=InnoDB;

举例一

创建表papers，创建title与content的全文索引。

CREATE TABLE papers(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) DEFAULT NULL,
    content text,
    PRIMARY KEY(id),
    FULLTEXT INDEX title(natitle, content)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

一般查询字符串时使用LIKE关键字。全文索引使用match+against方式查询。

SELECT *
FROM papers
WHERE MATCH(title, content) AGAINST('查询字符串');

注意点：

使用全文索引前，需要弄清楚版本支持情况。
全文索引比LIKE快N倍，但是可能存在精度问题。
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

创建空间索引

在空间索引创建中，要求空间类型的字段必须为非空。

创建表test3，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：

CREATE TABLE test3(
    geo GEOMETRY NOT NULL,
    SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;

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

在已经创建好的表上创建索引可以使用ALTER TABLE或者CREATE INDEX语句。

使用ALTER TABLE语句创建索引语法如下：

ALTER TABLE table_name
ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name]

使用CREATE INDEX语句创建索引，它可以在已经存在的表上添加索引，在MySQL中，CREATE INDEX语句被映射到一个ALTER TABLE语句上，基本语法结果为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name]
ON table_name (col_name[length],...) [ASC | DESC]

删除索引

使用ALTER TABLE删除索引，语法如下：

ALTER TABLE table_name DROP INDEX index_name;

使用DROP INDEX删除索引，语法如下：

DROP INDEX index_name ON table_name;

提示：
删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列被删除，则整个索引被删除。

今天先学习到这里，明天继续。

MySQL学习-索引创建（二）

前言