索引的介绍与创建

索引的介绍与创建

索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引、全文索引和空间索引等。

1. 从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
2. 按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
3. 按照 作用字段个数 进行划分，分成单列索引和联合索引。

其中主键索引就是聚簇索引，非主键索引可以称为非聚簇索引或是二级索引。

小结：不同的存储引擎支持的索引类型也不一样
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 等索引；

索引的创建

索引的两种创建方式。创建表的时候创建和已存在的表上创建。

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

比如：

-- 为emp_id 创建主键索引并且自增，emp_name 创建唯一索引
CREATE TABLE emp(
	emp_id INT PRIMARY KEY AUTO_INCREMENT,
	emp_name VARCHAR(20) UNIQUE,
	dept_id INT
);

基本语法格式如下：

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. 创建普通索引
在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)
);

查看表索引

SHOW INDEX FROM test1

2. 创建唯一索引

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

3. 主键索引

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

注意：
修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引

删除主键索引

ALTER TABLE student drop PRIMARY KEY ;

4. 创建单列索引

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

5. 创建组合索引
id、name和age字段上建立组合索引。

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)
);

6. 创建全文索引
给title和body字段添加全文索引

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

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

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

使用注意：

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

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

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

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

1、使用ALTER TABLE语句创建索引
语法：

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

案列：

alter table student add index ind_age(age);

2、使用CREATE INDEX创建索引
语法

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

案列

create index idx_age on student(age);

索引删除

1、使用ALTER TABLE删除索引。

ALTER TABLE table_name DROP INDEX index_name;

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

DROP INDEX index_name ON table_name;

MySQL8.0索引新特性

支持降序索引

创建降序索引

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

在MySQL 5.7版本中查看数据表ts1的结构，结果如下：

索引仍然是默认的升序

在MySQL 8.0版本中查看数据表ts1的结构，结果如下：

从结果可以看出，索引已经是降序了。

隐藏索引

从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，将索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引）。
删除索引可以先设置为隐藏索引 ，确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索
引，再删除索引的方式就是软删除 。

创建方式

1、 创建表时直接创建
通过SQL语句INVISIBLE来实现。

CREATE TABLE tablename(
	propname1 type1[CONSTRAINT1],
	propname2 type2[CONSTRAINT2],
	……
	propnamen typen,
	INDEX [indexname](propname1 [(length)]) INVISIBLE
);

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

CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

3、通过ALTER TABLE语句创建

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

切换索引可见状态

索引可通过如下语句切换可见状态。

-- 切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; 
-- 切换成非隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; 

注意： 当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

使隐藏索引对查询优化器可见

MySQL 8.x版本中，可以通过查询优化器的一个开关（use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes设置为off(默认)，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

1、 在MySQL命令行执行如下命令查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G

在输出的结果信息中，找到use_invisible_indexes，如果为on打开，off关闭。

2、 使隐藏索引对查询优化器可见命令

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)