MySQL----主键、唯一、普通索引的创建与删除

创建索引

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• CREATE INDEX: 用于创建普通索引的关键字。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• table_name: 指定要在哪个表上创建索引。
• (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

创建索引的条件

• 查询频繁的字段应该作为索引。索引的目的就是提高检索效率，如果某个字段被频繁使用，使用字段作为检索条件时就有必要提高检索效率。

• 更新频繁的字段不适合作为索引。索引的高效是以增删改的效率为代价的。

• 不作为检索条件的，不适合作为索引。如果该字段都不会作为条件用于检索，只会出现在结果中，那该字段不适合作为索引。

主键索引

在创建的时候直接指定

create table user(
    id int primary key,    
    name varchar(30)
);
//或者
create table user(
    id int,
    name varchar(30),
    primary key(id)       
);

在已有的表添加索引

ALTER TABLE `table_name` ADD PRIMARY KEY index_name  ( `column`  [ASC|DESC]);

//例如：
create table user(
    id int,
    name varchar(30)
);
alter table user3 add primary key(id);    -- 创建表以后再添加主键

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• ADD PRIMARY KEY: 添加索引的子句。ADD PRIMARY KEY用于创建普通索引。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• column1： 指定要索引的表列名。唯一的，列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

删除索引

如果一个主键是自增长的，不能直接删除该列的主键索引，应当先取消自增长，再删除主键特性

alter table 表名 drop primary key; 
//例如：
alter table user modify id int ;【重新定义列类型】

alter table user drop primary key;

普通索引

在已有的表添加索引

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• ADD INDEX: 添加索引的子句。ADD INDEX用于创建普通索引。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

在创建表的时候添加索引

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

• CREATE TABLE: 用于创建新表的关键字。
• table_name: 指定要创建的表的名称。
• (column1, column2, …): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• INDEX: 用于创建普通索引的关键字。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

删除索引

DROP INDEX

DROP INDEX index_name ON table_name;

• DROP INDEX: 用于删除索引的关键字。
• index_name: 指定要删除的索引的名称。
• ON table_name: 指定要在哪个表上删除索引。

ALTER TABLE

ALTER TABLE table_name
DROP INDEX index_name;

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• DROP INDEX: 用于删除索引的子句。
• index_name: 指定要删除的索引的名称。

唯一索引

创建索引CREATE UNIQUE INDEX

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• CREATE UNIQUE INDEX: 用于创建唯一索引的关键字组合。
• index_name: 指定要创建的唯一索引的名称。索引名称在表中必须是唯一的。
• table_name: 指定要在哪个表上创建唯一索引。
• (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

在已有的表上创建索引 ALTER TABLE

ALTER table mytable 
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• ADD CONSTRAINT: 这是用于添加约束（包括唯一索引）的关键字。
• unique_constraint_name : 指定要创建的唯一索引的名称，约束名称在表中必须是唯一的。
• UNIQUE (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。

创建表的时候直接指定

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

• CREATE TABLE: 用于创建新表的关键字。
• table_name: 指定要创建的表的名称。
• (column1, column2, …): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• CONSTRAINT: 用于添加约束的关键字。
• index_name: 指定要创建的唯一索引的名称。约束名称在表中必须是唯一的。
• UNIQUE (column1, column2, …): 指定要索引的表列名。

显示索引信息

SHOW INDEX FROM table_name\G

注意

1. 不是加了索引就会使用到，如果mysql发现通过索引查找的效率和整表查找差不多，mysql会自动优化为整表索引，因为使用索引要先读索引文件还需要扫码索引树。
2. 如果索引字段涉及到类型强转或者mysql函数调用以及表达式计算等，就不能使用到索引了。
3. 对于字符串类型来说，通常取前几个字符来区分字符串就足够了，否则过长的字符串索引会导致索引文件也过长，磁盘I/O也使用过多。