创建索引

examination_info	0	PRIMARY	1	id	A		BTREE
examination_info	0	uniq_idx_exam_id	1	exam_id	A	YES	BTREE
examination_info	1	idx_duration	1	duration	A		BTREE
examination_info	1	full_idx_tag	1	tag		YES	FULLTEXT

备注：后台会通过 SHOW INDEX FROM examination_info 语句来对比输出结果

示例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;

输出:

examination_info|0|PRIMARY|1|id|A|0|None|None||BTREE|||YES|None
examination_info|0|exam_id|1|exam_id|A|0|None|None||BTREE|||YES|None
examination_info|0|uniq_idx_exam_id|1|exam_id|A|0|None|None||BTREE|||YES|None
examination_info|1|idx_duration|1|duration|A|0|None|None||BTREE|||YES|None
examination_info|1|full_idx_tag|1|tag|None|0|None|None|YES|FULLTEXT|||YES|None

原站题解

Mysql 解法, 执行用时: 36ms, 内存消耗: 6220KB, 提交时间: 2022-01-23

-- 唯一索引
ALTER TABLE examination_info
ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);

-- 全文索引
ALTER TABLE examination_info
ADD FULLTEXT INDEX full_idx_tag(tag);
 
-- 普通索引
ALTER TABLE examination_info
ADD INDEX idx_duration(duration);

Mysql 解法, 执行用时: 36ms, 内存消耗: 6244KB, 提交时间: 2022-01-22

create INDEX idx_duration ON examination_info(duration);
create UNIQUE INDEX  uniq_idx_exam_id ON examination_info(exam_id);
create FULLTEXT INDEX  full_idx_tag ON examination_info(tag);

Mysql 解法, 执行用时: 36ms, 内存消耗: 6260KB, 提交时间: 2021-11-30

CREATE INDEX idx_duration ON examination_info(duration);
create UNIQUE index uniq_idx_exam_id ON examination_info(exam_id);
create FULLTEXT index full_idx_tag ON examination_info(tag);

Mysql 解法, 执行用时: 36ms, 内存消耗: 6276KB, 提交时间: 2021-12-03

alter table examination_info add KEY idx_duration(`duration`) using btree;
alter table examination_info add UNIQUE KEY uniq_idx_exam_id(`exam_id`) using btree;
alter table examination_info add FULLTEXT KEY full_idx_tag(`tag`);

Mysql 解法, 执行用时: 36ms, 内存消耗: 6292KB, 提交时间: 2022-01-22

alter table  examination_info add index  idx_duration(duration);
alter table  examination_info add unique index uniq_idx_exam_id(exam_id);
alter table  examination_info add fulltext index full_idx_tag(tag);

详情