MySQL中索引的创建及删除方法

目录

• 1.1 索引的分类
• 1.2 创建索引
• 1、创建表的时候创建索引
• 1、创建普通索引
• 2、创建唯一索引       
• 3、主键索引
• 4、创建单列索引
• 5、创建组合索引
• 6、 创建全文索引
• 2、在已经存在的表上创建索引
• 1. 使用 ALTER TABLE 语句创建索引
• 2. 使用 CREATE INDEX 创建索引
• 1.3 删除索引
• 1. 使用 ALTER TABLE 删除索引：
• 2. 使用 DROP INDEX 语句删除索引：
• 总结 

# 1.隐式的添加索引(在添加有主键约束、唯一性约束或者外键约束的字段会自动的创建索引)
CREATE TABLE dept(
    dept_id INT PRIMARY KEY AUTO_INCREMENT,# 创建主键索引
    dept_name VARCHAR(20)
);
CREATE TABLE emp(
    emp_id INT PRIMARY KEY AUTO_INCREMENT,# 主键索引
    emp_name VARCHAR(20) UNIQUE,# 唯一索引
    dept_id INT,
    CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
); # 外键索引

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

# ①创建普通的索引
CREATE TABLE book(
    book_id INT ,
    book_name VARCHAR(100),
    AUTHORS VARCHAR(100),
    info VARCHAR(100) ,
    COMMENT VARCHAR(100),
    year_publication YEAR,
    # 声明索引
    INDEX idx_bname(book_name)
);

# 方式1：
SHOW CREATE TABLE book; # Linux下添加\G参数

# 方式2：
SHOW INDEX FROM book;

#性能分析工具：EXPLAIN，查看索引是否正在使用
EXPLAIN SELECT * from book where book_name = 'mysql高级';

# ②创建唯一索引
CREATE TABLE book1 (
  book_id INT,
  book_name VARCHAR (100),
  AUTHORS VARCHAR (100),
  info VARCHAR (100),
  COMMENT VARCHAR (100),
  year_publication YEAR,
  #声明索引
  UNIQUE INDEX uk_idx_cmt (COMMENT)
);

show INDEX from book1;# 查看索引

# ③主键索引
# 通过定义主键约束的方式定义主键索引
create table book2(
    book_id int primary key,
    book_name varchar(100),
    AUTHORS VARCHAR (100),
    info VARCHAR (100),
    COMMENT VARCHAR (100),
    year_publication YEAR
) ;

# 通过删除主键约束的方式删除主键索引
alter table book2
drop primary key;

#④ 创建单列索引
CREATE TABLE book3(
    book_id INT,
    book_name VARCHAR(100),
    AUTHORS VARCHAR (100),
    info VARCHAR (100),
    COMMENT VARCHAR (100),
    year_publication YEAR,
    UNIQUE INDEX idx_bname(book_name)
);

show index from book3;

# ⑤ 创建联合索引
create table book4(
    book_id INT,
    book_name VARCHAR(100),
    AUTHORS VARCHAR (100),
    info VARCHAR (100),
    COMMENT VARCHAR (100),
    year_publication YEAR,
    index mul_bid_bname_info(book_id,book_name,info)    
)

SHOW INDEX FROM book4;

# 分析
explain select * from book4 where book_id = 1001 and book_name = 'mysql'; # 会使用到mul_bid_bname_info索引

explain select * from book4 where book_name = 'mysql';# 不会使用到mul_bid_bname_info索引

CREATE TABLE test4(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(255),
    FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;

SHOW INDEX FROM test4\G;

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR (200),
    body TEXT,
    FULLTEXT index (title, body)
) ENGINE = INNODB ;

CREATE TABLE `papers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 SELECT * FROM papers WHERE content LIKE ‘%查询字符串%';

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串');

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

create index idx_cmt on book(comment);

CREATE UNIQUE INDEX uk_idx_bid ON book(book_id);

CREATE INDEX mul_bid_bname_info ON book(book_id,book_name,info);

ALTER TABLE table_name DROP INDEX index_name;

SHOW INDEX FROM book\G;

ALTER TABLE book DROP INDEX idx_bk_id;

DROP INDEX index_name ON table_name;

DROP INDEX idx_aut_info ON book;

SHOW CREATE TABLE book\G;