MySQL【index 】索引

索引本质为：数据结构，其用途是用于提升检索数据效率。

        索引是在存储引擎中实现的 ，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的【最大索引数】 和【最大索引长度】。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。有些存储引警支持更多的索引数和更大的索引长度。

        进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引査找相关数据，如果不符合则需要全表扫描 ，即需要一条一条地查找记录，直到找到与条件符合的记录。【减少磁盘的IO次数，加快查询速度】

索引的优点【面试题】

• 提高数据检索的效率，降低数据库的IO成本。
• 通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。
• 对于有依赖关系的子表和父表联合查询时， 可以提高查询速度。
• 在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间 ，降低了CPU的消耗。

索引的缺点

• 创建索引和维护索引要耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。
• 索引需要占用磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

选择索引的原则

1. 频繁查询的字段：索引列的选择应该基于查询模式。对经常在 SELECT 语句的 WHERE 子句、JOIN 子句、ORDER BY 子句和 GROUP BY 子句中出现的列创建索引。
2. 唯一性：对那些需要保持唯一值的列（如主键、唯一键）创建索引，以确保数据的完整性和一致性
3. 低选择性：唯一性太差的字段不适合作为索引，例如性别
4. 频繁增删改的字段：字段的变化`MySQL`是需要重新维护索引的，假设某个字段频繁修改，那就意味着需要频繁的重建索引，必然影响MySQL的性能
5. 不要建立太多的索引：MySQL维护索引是需要空间和耗费性能的。如果索引过多，这无疑是增加了MySQL的负担
6. 索引需要额外的磁盘空间，过小的表，索引的大小可能会变得相当大。对过小的表也没有添加索引的必要。

MySQL 索引的分类

普通索引（INDEX）：索引列值可重复

用于加速查询，不需要唯一。可以在列上创建索引以加速查询性能，没有唯一性要求。只是用于提高查询效率。这类索引可以创建在 任何数据类型 中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在name 上建立一个普通索引，查询记录时就可以根据该索引进行查询。

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(50),
    INDEX idx_name (name)
);

唯一索引（UNIQUE）：索引列值必须唯一，可以为NULL

在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空(NULL)值。在一张数据表里 可以有多个唯一索引。

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(20) UNIQUE
);

主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL，一个表只能有一个主键索引

索引列值必须唯一，不能为NULL。一个表只能有一个主键索引；不仅确保列中的所有值都是唯一的，还不允许包含 NULL 值。每个表只能有一个主键索引，它主要用于唯一标识表中的每一行，并作为表的主标识符。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

全文索引（FULL TEXT）：【了解】给每个字段创建索引

通过建立在文本列上的索引。允许对文本字段中的单词进行快速的搜索和匹配，通常用于处理大型文本数据，如文章内容、博客帖子和产品描述等。全文索引支持基于自然语言的查询，可以显著提高全文搜索的性能。

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body) -- 为 title 和 body 列创建全文索引
);

用途：

普通索引：常用于过滤数据。例如，以商品种类作为索引，检索种类为“手机”的商品。

唯一索引：主要用于标识一列数据不允许重复的特性，相比主键索引不常用于检索的场景。

主键索引：是行的唯一标识，因而其主要用途是检索特定数据。

全文索引：效率低，常用于文本中内容的检索。

show index from 表名; 
show index from test; 

1.创建索引：

普通索引 index：

允许索引列中有重复的值。

1.在创建表时指定 index：【常用】
create table test(
  id int not null, 
  name varchar(100) not null, 
  birthdy date, 
  sex char(5) not null, 
  index name_index (name(50))
);

2.基于表结构创建：为表中的一个或多个字段创建索引：
create index 索引名称 
on 表名 (字段);

create index name_index on test (name);
# 注意：当为多个字段设置索引，desc只会显示一个，show index则会显示全部
create index name_index on test (id,name,sex);

3.修改表结构创建 index :
alter table 表名 
add index 索引名称(字段);

alter table test add index id_index(id);

唯一索引：unique

1.创建表时指定唯一索引:
create table 表名 (
  字段 类型 unique,
  字段2 类型2......
  );
#第二种方式：
create table 表名 (
  字段1 类型1,
  字段2 类型2......
  unique index 索引名称(字段)
  );
#1.
create table test(
  id int not null unique, 
  name varchar(100) not null, 
  sex char(5) not null
  );
#2.
create table test(
  id int not null, 
  name varchar(100) not null, 
  sex char(1) not null, 
  unique index id_index (id));

2.创建 unique 索引的标准方法:可以为列添加唯一索引:
create unique index 索引名称 
on 索引名称(字段);
#
create unique index name_index on test(name);
# 注意：当为多个字段设置索引，desc只会显示一个，show index则会显示全部
create unique index id_index on test(id,sex);

3.使用 ALTER TABLE 语句:
alter table 表名 add unique (字段);
#
alter table test add unique (id);

主键索引：primary key

主键索引的特性:

1. 唯一性：主键索引的每个键值都是唯一的，不能有重复。
2. 非空：主键索引的列不能接受NULL值。
3. 表限制：每个表只能有一个主键索引。
4. 性能：主键索引有助于提高数据检索的速度，尤其是在处理大型数据集时。

1.创建表时指定主键:
create table 表名 (
字段 类型 primary key,
...
);
#1.
create table test(
  id int not null primary key, 
  name varchar(100) not null, 
  sex char(5) not null
  );

2.使用 ALTER TABLE 语句为现有表添加主键。
alter table 表名 add primary key (字段);
alter table test add primary key (id);

2.删除索引：

注意事项：

1. 在删除索引之前，请确保该索引不是由外键约束强制的，否则您可能需要先删除外键约束。
2. 删除索引操作是不可逆的，所以在执行之前请确保这是您想要的操作。
3. 如果表中的数据依赖于索引以提高查询性能，删除索引可能会影响性能。
4. 在生产环境中，删除索引之前最好先在测试环境中进行测试。

普通索引 index：

#直接删除
drop index 索引名称 
ON 表名;
#修改表结构删除
alter table 表名 
drop index 索引名称;

#
drop index name_index on test;
alter table test drop index name_index;

唯一索引：unique

#直接删除
drop index 索引名称 
ON 表名;
#修改表结构删除
alter table 表名 
drop index 索引名称;

drop index name_index on test;
alter table test drop index name_index;

主键索引：primary key

#主键不能采用直接删除的方式删除
alter table 表名 
drop primary key;

alter table test drop primary key;

3.查看索引：

show index from 表名;

show create table 表名;