一、索引分类
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;
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;
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(‘查询字符串’);
- 注意点
- 使用全文索引前，搞清楚版本支持情况；
- 全文索引比 like + % 快 N 倍，但是可能存在精度问题；
- 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
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;
- 删除
name列
ALTER TABLE test3
DROP name;
- 查看索引
SHOW INDEXES FROM test3;
- 删除
age列
ALTER TABLE test3
DROP age;
- 查看索引
SHOW INDEXES FROM test3;
- 删除
id列
ALTER TABLE test3
DROP id;
- 查看索引
SHOW INDEXES FROM test3;
