mysql索引的创建删除

01.索引创建、删除与使用：

1.1 create方式创建索引：

CREATE 
  [UNIQUE -- 唯一索引
  | FULLTEXT -- 全文索引
  ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
  (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引  

1.2 alter方式创建索引：

ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)

1.3 drop方式删除索引：

DROP INDEX <索引名> ON <表名>

1.4 alter方式删除索引：

ALTER TABLE <表名> DROP INDEX <索引名>

1.5 索引的使用：

• 索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
• 索引不包含有NULL值的列
• 一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
• like做字段比较时只有前缀确定时才会使用索引
• 在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引

02.SQL实现

2.1 创建模拟表

drop table if exists examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
	id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
	exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
	tag varchar(32) COMMENT '类别标签',
	difficulty varchar(8) COMMENT '难度',
	duration int NOT NULL COMMENT '时长',
	release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
describe examination_info ;

​ 输出结果(表的描述)：

2.2 查看表的索引

SHOW INDEX FROM examination_info ;

​ 输出结果：

2.3 创建普通索引并查看结果：

CREATE index idx_duration on examination_info(duration);
show index from examination_info;

​ 输出结果：

2.4 创建唯一性索引并查看结果：

CREATE unique index  uniq_idx_exam_id on examination_info(exam_id);
show index from examination_info;

​ 输出结果：

2.5 创建全局性索引并查看结果：

CREATE fulltext index full_idx_tag on examination_info(tag);
show index from examination_info ;

​ 输出结果：

2.6 上述也可以采用如下的方式：

alter table examination_info
ADD INDEX `idx_duration` (`duration`),
ADD UNIQUE INDEX `uniq_idx_exam_id` (`exam_id`),
ADD FULLTEXT INDEX `full_idx_tag` (`tag`);

03.删除表的索引

3.1删除全局索引

drop index full_idx_tag on examination_info ;
show index from examination_info;

​ 输出结果：

3.2 使用第二种方式删除索引

CREATE fulltext index full_idx_tag on examination_info(tag);
alter table examination_info drop index full_idx_tag ;
show index from examination_info;

​ 输出结果：

3.2 删除唯一索引与前面类似

drop index uniq_idx_exam_id  on examination_info ;
show index from examination_info;

​ 也可以使用如下方式

CREATE unique  index uniq_idx_exam_id  on examination_info(exam_id);
alter table examination_info drop index uniq_idx_exam_id  ;
show index from examination_info;

​ 输出结果：

r table examination_info drop index uniq_idx_exam_id ;
show index from examination_info;

​			输出结果：

[外链图片转存中...(img-UvI1XvZQ-1647106792634)]