一、索引分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等
①：从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引
②：按照物理实现方式，索引可以分为 2 种，聚簇索引和非聚簇索引
③：按照作用字段个数进行划分，分成单列索引和联合索引

1、普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。

2、唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引

3、主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL + UNIQUE，表里最多只有一个主键索引。这是由于主键索引的物理实现方式决定的，因为数据存储在文件中（叶子节点）只能按照一种顺序进行存储

4、单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引

5、多列（组合、联合）索引

多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。使用组合索引时遵循最左前缀集合

6、全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。他能够利用[分词技术]等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地赛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。
使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列种插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

6.1、自然语言的全文索引

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语

• 随着大数据时代的到来，关系性数据库应对全文索引的需求已力不从心，逐渐被Solr、ElasticSearch等专门的搜索引擎所替代。

7、空间索引

使用参数SPATIAL可以设置索引为空间索引 。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且检索的字段不能为空值

小结：不同的存储引擎支持的索引类型也不一样

• InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash索引；
• MyISAM ： 支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
• NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
• Archive ：不支持 B-tree、Hash、Full-text 等索引

二、创建索引

MySQL支持多种方法在单个或多个列上创建索引。
方式1：在创建表的定义语句CREATE TABLE中指定索引列
方式2：使用ALTER TABLE语句在存在的表上创建索引
方式3：使用CREATE INDEX语句在存在的表上创建索引

1、创建表的时候创建索引

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

1.1、使用主键标识时，自动会创建对应索引

• 创建表时指定主键

CREATE TABLE dept
(
    dept_id   INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(20)
);

• 查看 dept 表的索引

SHOW INDEXES FROM dept;

dept表中的索引.png

1.2、使用主键、外键和UNIQUE等标识时，自动会创建对应索引

• 创建表代码

CREATE TABLE emp
(
    emp_id   INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20) UNIQUE,
    dept_id  INT,
    CONSTRAINT emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (dept_id)
);

• 查看索引

SHOW INDEXES FROM emp;

emp表索引.png

1.3、显示创建表时创建索引，基本语法格式

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 指定升序或者降序的索引值存储。

1.4、创建普通索引

• 在book表中的year_publication字段上建立普通索引，SQL语句如下

CREATE TABLE book
(
    book_id          INT,
    book_name        VARCHAR(100),
    authors          VARCHAR(100),
    info             VARCHAR(100),
    comment          VARCHAR(100),
    year_publication YEAR,
    INDEX (year_publication)
);

1.5、 创建唯一索引

CREATE TABLE test1
(
    id   INT         NOT NULL,
    name VARCHAR(30) NOT NULL,
    UNIQUE INDEX uk_idx_id (id)
);

1.6、 主键索引

• 设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法：

CREATE TABLE student
(
    id           INT(10) UNSIGNED AUTO_INCREMENT,
    student_no   VARCHAR(200),
    student_name VARCHAR(200),
    PRIMARY KEY (id)
);

1.7、创建单列索引

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

1.8、创建组合索引

• 创建表test3，在表中的id、name和age字段上建立组合索引，SQL语句如下：

CREATE TABLE test3
(
    id   INT(11)  NOT NULL,
    name CHAR(30) NOT NULL,
    age  INT(11)  NOT NULL,
    info VARCHAR(255),
    INDEX multi_idx (id, name, age)
);

1.9、创建全文索引

FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引

• 创建表test4，在表中的info字段上建立全文索引，SQL语句如下

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

• 创建一个给 title 和 body 字段添加全文索引的表

CREATE TABLE articles
(
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body  TEXT,
    FULLTEXT INDEX (title, body)
) ENGINE = INNODB;

• FULLTEXT

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

• like方式的的查询

SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;

• 全文索引用match+against方式查询

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

• 注意点
  • 1. 使用全文索引前，搞清楚版本支持情况；
  • 2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题；
  • 3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

1.10、 创建空间索引

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

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

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

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

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

2.1、使用ALTER TABLE语句创建索引

• ALTER TABLE语句创建索引的基本语法如下

ALTER TABLE table_name
    ADD [UNIQUE | FULLTEXT | SPATIAL] 
    [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

2.2、使用CREATE INDEX创建索引

• CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为

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

三、删除索引

1、使用ALTER TABLE删除索引

• ALTER TABLE删除索引的基本语法格式如下

ALTER TABLE table_name DROP INDEX index_name;

2、使用DROP INDEX语句删除索引

• DROP INDEX删除索引的基本语法格式如下

DROP INDEX index_name ON table_name;

3、小结

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

• 创建表语句

CREATE TABLE test3
(
    id   INT(11)  NOT NULL,
    name CHAR(30) NOT NULL,
    age  INT(11)  NOT NULL,
    info VARCHAR(255),
    INDEX multi_idx (id, name, age)
);

• 查看索引

SHOW INDEXES FROM test3;

联合索引.png

• 删除 name 列

ALTER TABLE test3
DROP name;

• 查看索引

SHOW INDEXES FROM test3;

删除 name 列后的联合索引.png

• 删除 age 列

ALTER TABLE test3
DROP age;

• 查看索引

SHOW INDEXES FROM test3;

删除 age 列后的联合索引.png

• 删除 id 列

ALTER TABLE test3
DROP id;

• 查看索引

SHOW INDEXES FROM test3;

删除 所有 列后的联合索引也被删除.png