mysql 创建索引和删除索引

目录

1、使用场景

2、代码实现

2.1、使用ALTER TABLE语句创建索引

2.2、使用CREATE INDEX语句对表增加索引

2.3、删除索引

4、创建索引之前先删除索引

5、参考文章

---

1、使用场景

        为了提高SQL语句查询速度，我们时常需要在各种表之中创建索引。索引的创建可以在CREATE TABLE语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。

2、代码实现

2.1、使用ALTER TABLE语句创建索引

语法如下：

alter table table_name add index index_name (column_list) ;

alter table table_name add unique (column_list) ;

alter table table_name add primary key (column_list) ;

其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

mysql> alter table table_name(表名) add index index_test1(name) ;

2.2、使用CREATE INDEX语句对表增加索引

能够增加普通索引和UNIQUE索引两种。其格式如下：

create index index_name on table_name (column_list) ;

create unique index index_name on table_name (column_list) ;

mysql>create index index_test2 on table_test(age);

2.3、删除索引

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
删除索引的操作，如下面的代码：

mysql> drop index name on table_test ;

4、创建索引之前先删除索引

MySQL没有直接类似建表语句常用的create xxx if exists yyy这样的语句来判断索引是否存在。
如果需要单独建索引，重复索引会导致脚本出错(Duplicate key)。
此时可以用存储过程来解决，看代码便知：

DROP PROCEDURE IF EXISTS delete_index;
create procedure delete_index(IN p_tablename varchar(200), IN p_idxname VARCHAR(200))
begin
DECLARE str VARCHAR(250);
  set @str=concat(' drop index ',p_idxname,' on ',p_tablename);

select count(*) into @cnt from information_schema.statistics where table_name=p_tablename and index_name=p_idxname ;
if @cnt >0 then
    PREPARE stmt FROM @str;
EXECUTE stmt ;
end if;
end;

使用方法：

-- 创建唯一索引
call delete_index('cos_assign_user','uindex_cos_assign_user');
create unique index uindex_cos_assign_user on cos_assign_user(course_id,origin_type,origin_id,user_id) COMMENT '唯一索引限定';


-- 在最后 在最后处可以删除存储过程

DROP PROCEDURE IF EXISTS delete_index;

5、参考文章

mysql 创建索引和删除索引

MySQL判断索引存在并删除索引的存储过程