1. 索引的声明与使用
1.1 索引的分类
MySQL 的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
从 功能逻辑 按照上说，索引主要有 4 种：普通索引、唯一索引、主键索引、全文索引。
按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照 作用字段个数 进行划分，分成单列索引和联合索引。
小结：不同的存储引擎支持的索引类型也不一样
InnoDB：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory：支持 B-tree、Hash 等 索引，不支持 Full-text 索引；
NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive：不支 持 B-tree、Hash、Full-text 等索引;
1.2 创建索引
1、创建表的时候创建索引
隐式的索引创建：
隐式的索引创建：
# 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]
UNIQUE、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引;
INDEX与KEY 为同义词，两者的作用相同，用来指定创建索引;
index_name 指定索引的名称，为可选参数，如果不指定，那么 MySQL 默认 col_name 为索引名;
col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择;
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度;
ASC 或 DESC 指定升序或者降序的索引值存储。
特例：主键索引使用主键约束的方式来创建。
1、创建普通索引
在book表中的year_publication字段上建立普通索引，SQL语句如下:
# ①创建普通的索引 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语句输出结果的各个行我们在下一章讲解，这里主要关注两个字段
possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引
key行时MySQL实际选用的索引
可以看到，possible_keys和key值都为idx_bname，查询时使用了索引
2、创建唯一索引
# ②创建唯一索引 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;# 查看索引
3、主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法:
- 随表一起建索引:
# ③主键索引 # 通过定义主键约束的方式定义主键索引 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;
- 修改主键索引：必须先删除掉（drop）原索引，再新建（add）索引
4、创建单列索引
#④ 创建单列索引 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;
5、创建组合索引
举例：创建表 book4，在表中的 book_id、book_name和 info字段上建立组合索引，SQL 语句如下
# ⑤ 创建联合索引 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;
注意上面三行依次是book_id,book_name,info，与我们创建索引时指定的顺序是严格对应的。在查询时会遵守最左索引原则，先进行book_id条件的比较，然后再进行book_name比较，最后才是info。因此注意把最常用的查询字段放在索引的最左边。
# 分析 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索引
6、 创建全文索引
FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。
举例1：创建表test4，在表中的 info 字段上建立全文索引，SQL 语句如下:
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;
在 MySQL 5.7 及之后版本中可以不指定最后的 ENGINE 了，因为在此版本中 InnoDB 支持全文索引。
语句执行完毕后，用SHOW CREATE TABLE查看表结构：
SHOW INDEX FROM test4\G;
由结果可以看到，info字段上已经成功建立了一个名为futxt_idx_info的FULLTEXT索引。
举例2：
CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR (200), body TEXT, FULLTEXT index (title, body) ) ENGINE = INNODB ;
创建了一个给 title 和 body 字段添加全文索引的表。
举例3：
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;
不同于 like 方式的的查询:
SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;
全文索引用 match+against 方式查询:
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);
明显的提高查询效率
注意点
使用全文索引前，搞清楚版本支持情况；
全文索引比like + % 快 N倍，但是可能存在精度问题
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
7、创建空间索引
空间索引创建中，要求空间类型的字段必须为 非空 。
举例：创建表 test5，在空间类型为 GEOMETRY 的字段上创建空间索引，SQL 语句如下
CREATE TABLE test5( geo GEOMETRY NOT NULL, SPATIAL INDEX spa_idx_geo(geo) ) ENGINE=MyISAM;
该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：
SHOW INDEX FROM test5\G;
可以看到，test5的geo字段上创建了名称为spa_idx_geo的空间索引。注意创建时指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM
2、在已经存在的表上创建索引
在已经存在的表中创建索引可以使用 ALTER TABLE 语句或者 CREATE INDEX 语句。
1. 使用 ALTER TABLE 语句创建索引
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
