MySQL索引的使用

索引的使用

显式创建表时创建索引，基本语法格式如下：

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

直接创建索引

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

UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC 或 DESC 指定升序或者降序的索引值存储。

egs：

# 建立普通索引
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)
);

# 唯一索引
CREATE TABLE test1(
id INT NOT NULL,
name varchar(30) NOT NULL,
UNIQUE INDEX uk_idx_id(id)
);

# 主键索引
CREATE TABLE student (
id INT(10) UNSIGNED AUTO_INCREMENT ,
student_no VARCHAR(200),
student_name VARCHAR(200),
PRIMARY KEY(id)
);

# 降序索引
CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

# 建表后添加索引
ALTER TABLE test 
ADD UNIQUE INDEX dpmt_id(dpmt_id);

查看表的所有索引

SHOW INDEX FROM test1;

删除索引

ALTER TABLE table_name DROP INDEX index_name;

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

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，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

set session optimizer_switch="use_invisible_indexes=on"

索引提示
MySql支持索引提示（INDEX HINT）显式地告诉优化器使用那个索引。使用索引提示的sql语句有两种：

# 这个语句只是提醒优化器使用那个索引，具体的选择还是优化器说了算，不推荐使用。
SELECT * FROM t USE INDEX(a) WHERE a=1 AND b = 2;xxxxxxxxxx SELECT * FROM t USE INDEX(a) WHERE a=1 AND b = 2;USE INDEX(index_name)
# 这条语句强制优化器直接使用指定的索引进行查询，推荐使用。
SELECT * FROM t FORCE INDEX(a) WHERE a=1 AND b = 2;

关于降序索引
MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这样做的效率其实很低。