MySQL索引之创建

一、创建索引

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

● UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
● INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；
● index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
● col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
● length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
● ASC或DESC指定升序或者降序的索引值存储。

1.在创建表的同时创建索引

1. 创建普通索引

CREATE TABLE book( 
    book_id INT , 
    book_name VARCHAR(100), 
    authors VARCHAR(100), 
    info VARCHAR(100) , 
    comment VARCHAR(100), 
    year_publication YEAR, 
    INDEX(year_publication) 
);

2. 创建唯一索引

如果主键索引添加了自增 那么删除主键索引时 是无法删除的

CREATE TABLE test1( 
    id INT NOT NULL, 
    name varchar(30) NOT NULL, 
    UNIQUE INDEX uk_idx_id(id) 
);

查询test1表中的索引 \G 换行

SHOW INDEX FROM test1 \G 

3. 主键索引

CREATE TABLE student ( 
    id INT(10) UNSIGNED AUTO_INCREMENT, 
    student_no VARCHAR(200),
    student_name VARCHAR(200), 
    PRIMARY KEY(id) 
);

删除主键索引

ALTER TABLE student drop PRIMARY KEY ;

4. 创建单列索引

CREATE TABLE test2( 
    id INT NOT NULL, 
    name CHAR(50) NULL, 
    INDEX single_idx_name(name(20)) 
);

5. 创建组合索引

CREATE TABLE test3( 
    id INT(11) NOT NULL, 
    name CHAR(30) NOT NULL, 
    age INT(11) NOT NULL, 
    info VARCHAR(255), 
    INDEX multi_idx(id,name,age) 
);

最左前缀原则 创建了3个索引 条件有前两个 就可以命中索引

6. 创建全文索引

FULLTEXT可以用于全文搜索 并且只为char varchar text列创建索引 索引总是对整个列进行 不支持局部前缀

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 MATCH(title,content) AGAINST (‘查询字符串’);

7. 创建空间索引

CREATE TABLE test5( 
    geo GEOMETRY NOT NULL, 
    SPATIAL INDEX spa_idx_geo(geo) 
) ENGINE=MyISAM;

2.在已经存在的表上创建索引

1. 使用ALTER TABLE语句创建索引

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

2. 使用CREATE INDEX创建索引

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

二、删除索引

1. 使用ALTER TABLE删除索引

ALTER   ...   ...   DROP   ...    ... 
ALTER TABLE table_name DROP INDEX index_name;

Tips：添加AUTO_INCREMENT约束字段的唯一索引不能被删除

2. 使用DROP INDEX语句删除索引

DROP  ...   ...   ON   ...  
DROP INDEX index_name ON table_name;

三、MySQL8.0索引新特性

1 支持降序索引

在8.0版本之前创建的仍然是升序索引，使用时及时进行反向扫描，这大大降低了数据库的效率 。

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

2 隐藏索引

从MySQL 8.x开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。