SQL-索引index

索引创建、删除与使用：

• create方式创建索引：
  建表时创建索引

  CREATE TABLE
  IF
  	NOT EXISTS user_info_vip (
  		id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
  		uid INT UNIQUE NOT NULL COMMENT '用户ID',
  		nick_name VARCHAR(64) COMMENT '昵称',
  		achievement INT DEFAULT 0 COMMENT '成就值',
  		level INt COMMENT '用户等级',
  		job VARCHAR(32) COMMENT '职业方向' ,
  		register_time datetime COMMENT '注册时间' DEFAULT CURRENT_TIMESTAMP
  	) CHARACTER 
  	SET utf8 COLLATE utf8_general_ci;
  

  单独创建索引

  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 );  -- 全文索引
  

  alter 创建索引

  	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` );
  	```
  

• 删除索引
  drop方式删除索引：DROP INDEX <索引名> ON <表名>
  index前面不需要再说明是否是unique，或者full等。

  DROP INDEX idx_duration on examination_info;
  DROP INDEX uniq_idx_exam_id on examination_info;
  DROP INDEX full_idx_tag on examination_info;
  

  alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

  ALTER TABLE examination_info DROP INDEX `idx_duration`;
  ALTER TABLE examination_info DROP INDEX `uniq_idx_exam_id`;
  ALTER TABLE examination_info DROP INDEX `full_idx_tag`;
  

• 查看索引
  SHOW INDEX FROM <表名> [ FROM <数据库名>]

  SHOW INDEX FROM examination_info FROM mytest;