MySQL(五)-索引一、索引介绍 1.1 索引的优缺点优点：所有字段都能加，还能为多列添加索引（组合索引）提高查

写在前面：部分内容摘抄于冰河的MySQL运维大全

一、索引介绍

1.1 索引的优缺点

优点：

所有字段都能加，还能为多列添加索引（组合索引）
提高查询性能
提高数据分组与排序的型能

缺点：

索引本身占用存储空间，如果大量使用，会占用大量磁盘空间
索引的创建与维护需要耗费时间，数据量的不断增长，耗费时间越来越长

1.2 索引的创建原则

1.尽量存储空间小的数据类型，这样占用空间小，如能用tinyint就不用int
2.尽量使用简单的数据类型的列创建索引，如int类型比varchar类型的开销小
3.创建索引的字段不要允许为null，这样会使计算变得复杂

二、索引的使用场景

1.3.1 适合创建索引的场景

主键和外键必须添加索引
与其他表关联的字段，并且经常关联查询，为连接字段创建索引
作为where子句的条件判断字段，并且经常操作的字段
作为order by语句的字段，并且经常用来执行排序的字段，应当添加索引

PS：只是列举了几个例子，其他的场景自行总结

1.3.2 不适合创建索引的场景

查询使用很少的字段
字段包含的内容很少，如性别字段
大数据类型的字段，如text，blob，bit等
查询数据时不会作为where、order by、group by语句条件的字段

三、创建表时创建索引

3.1 创建表时创建索引的语法格式

CREATE TABLE table_name 
column_name1 data_type1 [, column_name2 data_type2, …, column_namen data_typen]
[PRIMARY | UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (column_name [length])
[ASC | DESC]

语法格式说明如下：

create table table_name : 创建表，表名为table_name  
column data_type：字段，字段类型  
[primary | unique | fulltext | spatial]：索引的类型，分别表示为主键索引、唯一索引、全文索引和空间索引，可省略  
[index | key]：index和key基本相同，指定在数据表中创建索引，可省略  
column_name: 需要创建索引的列，可为多个列  
length: 指定索引的长度，只有字符串类型的字段才能指定  
[asc | desc]：指定升序还是降序存储索引值，可省略

3.2 创建普通索引

create table t1(
    id int not null,
    t_name varchar(30) not null default '',
    t_department_id int not null default 0,
    index department_id_index (t_department_id) //创建索引并指定字段
);

3.3 创建唯一索引、全文索引、空间索引

create table t1(
    id int not null,
    t_name varchar(30) not null default '',
    t_department_id int not null default 0,
    [unique | fulltext | spatial] index department_id_index (t_department_id)
);

3.4 创建主键索引

create table t1(
    id int not null primary key,
    t_name varchar(30) not null default ''
);

create table t1(
    id int not null,
    t_name varchar(30) not null default '',
    primary key(id)
);

3.5 创建多列索引

create table t(
    id int not null,
    t_no varchar(30) not null default '',
    t_name varchar(30) not null default '',
    t_department_id int not null default 0,
    index no_name_department_index (t_no,t_name,t_department_id)
);

注意：多列索引遵循最左匹配原则
查询索引有没有生效可以用show create table table_name

四、已有数据表添加索引

4.1 添加索引

ALTER TABLE 语法格式

ALTER TABLE table_name 
ADD [PRIMARY | UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (column_name [length])
[ASC | DESC]

CREATE INDEX 语法格式

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

示例：

alter table t add index t_no_index (t_no)  //alter table 语法
create index t_no_index on t (t_no)    //create index 语法

4.2 删除索引

alter table table_name drop index index_name //alter table 语法格式
drop index index_name on table_name //drop index 语法格式

五、隐藏索引

注意： MySQL 8.x开始支持，将要待删除的索引设置为隐藏索引，查询优化器就不再使用这个索引，就可以彻底删除索引。通过先隐藏索引，再删除索引的方式就是软删除。如果创建索引时先设置为隐藏索引，然后再将其设置为可见索引，这种方式就是灰度发布

5.1 语法格式

创建隐藏索引

alter table table_name add index [index_name] (column_name [length]) invisible
create index index_name on table_name (column_name [length]) invisible

注意：是否对优化器隐藏，跟 optimizer_switch="use_invisible_indexes=on | off" 这里的on|off相关

,可以通过select @@optimizer_switch查询

修改为可见索引

alter table table_name alter index index_name visible

六、其他

还有降序索引、函数索引，此内容为MySQL8.x之后版本