1. 创建表时添加索引

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]

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

1.2 普通索引

创建普通索引举例

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) 普通索引
);

1.3 唯一索引

创建唯一索引举例

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

1.4 主键

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 ;

修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引

1.5 单列索引

创建单列索引

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

1.6 组合索引

创建组合索引

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

1.7 全文索引

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

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(可以加长度，表示只比较前xxx长度)) 全文索引
) ENGINE=MyISAM;

在MySQL5.7及之后版本中可以不指定最后的ENGINE了，因为在此版本中InnoDB支持全文索引。

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;

全文索引和like的语法规则

like
SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;FULLTEXT KEY
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

注意点：

1. 使用全文索引前，搞清楚版本支持情况；
2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题；
3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

1.8 空间索引

创建空间索引
空间索引创建中，要求空间类型的字段必须为 非空 。

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

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

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

1. 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：

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

2. 使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，
   CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

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

3. 删除索引

使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成
索引的所有列都被删除，则整个索引将被删除。

4. MySQL8.0索引新特性

4.1支持降序索引

举例：分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1

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

从结果可以看出，索引仍然是默认的升序

在MySQL 8.0版本中查看数据表ts1的结构，结果如下：
从结果可以看出，索引已经是降序了。下面继续测试降序索引在执行计划中的表现。
分别在MySQL 5.7版本和MySQL 8.0版本的数据表ts1中插入800条随机数据，执行语句如下：

从结果可以看出，索引已经是降序了。下面继续测试降序索引在执行计划中的表现。分别在MySQL 5.7版本和MySQL 8.0版本的数据表ts1中插入800条随机数据，执行语句如下：

DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 800
DO
insert into ts1 select rand()*80000,rand()*80000;
SET i = i + 1;
END WHILE;
commit;
END //
DELIMITER ;CALL ts_insert()

mysql5.7执行查询计划

从结果可以看出，执行计划中扫描数为799，而且使用了Using filesort。

Using filesort是MySQL中一种速度比较慢的外部排序，能避免是最好的。多数情况下，管理员可以通过优化索引来尽量避免出现Using filesort，从而提高数据库执行速度。

mysql8.0执行查询计划

在MySQL 8.0版本中查看数据表ts1的执行计划。从结果可以看出，执行计划中扫描数为5，而且没有使用Using filesort。

即mysql8比5.7多了倒叙索引，虽然5.7的语法支持，但还是升序索引。

4.2 隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

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

5. 索引的设计原则

数据准备：表结构

5.1 字段的数值有唯一性的限制 UNIQUE

索引本身可以起到约束作用，比如唯一索引，主键索引都是可以有唯一约束，因此在数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引或主键索引，这样可以更快的通过该索引来确定某条记录

例如上表student_info的id，student_id

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

5.2 频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率

5.3 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多个，那么可以在这些列上建立 组合索引 。

5.4 UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.5 DISTINCT 字段需要创建索引

因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多

5.6 多表 JOIN 连接操作

创建索引注意事项：

1. 首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

2. 其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

3. 最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。虽然都能查到，但是是由于mysql做了隐式转换，使用了函数，一旦使用函数，索引失效

5.7 使用列的类型小的创建索引

1. 数据类型小，查询操作快
2. 数据类型小，占用空间小，一个页中能存储的数据更多，一次IO能读取更多数据，减少IO带来的损耗
3. 这对于主键更适用，因为不仅聚簇索引包含主键，非聚簇索引也会包含主键的值，能够节省空间，减少IO带来的损耗

5.8 使用字符串前缀创建索引

假设字符串很长，那么存储一个字符串就需要占用很大的存储空间，在我们为字符串建立索引时，对应的B+树就有两个问题：

1. B+树索引中的记录需要把该列的完整记录存储起来，而且更费时间（比较时）和占用空间
2. 如果B+树索引中存储的字符串很长，那么做字符串比较时，会占用更多的时间

如果字符串截取少了，重复内容太多，散列度低，截取多了，达不到节省存储空间的效果，那么问题来了，应该截取多少字符串长度作为索引呢？

扩展：在varchar字段建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际的文本区分度决定索引长度，索引的长度和区分度是一对矛盾体，一般对字符串类型的数据，长度达到20，区分度高达90%以上，可以使用
count/(distinct left(列名,索引长度))/count(*)
的区分度来确定，这个值越接近1，则对应的索引长度越合适

关于前缀索引的问题：
groupby，orderby时，会有不准的情况，这一点很好理解

5.9 区分度（散列度）高的列创建索引

这一点很好理解，散列度高了，进行索引查找时获取值更少

5.10 使用最频繁的列放到组合索引的左侧

最左原则

5.11 在多个字段都需要创建索引的情况下，联合索引优于单列索引

5.12 限制索引的个数

建议单表不超过6个，

1. 索引越多，需要的磁盘空间越大
2. 索引会影响增删改的效率
3. 索引越多，优化器在对索引进行成本分析时耗时越大（explain中的possible_keys和key的选择）

6. 不适合创建索引的情况

6.1 where中使用不到的字段

6.2 数据量小的表不建议使用索引

数据量小，不使用索引的情况下查询速度本来就很快，如果创建索引，不仅占用磁盘空间，而且查询的时候，还得有回表操作，以及对增删改操作的性能有影响，所以数据量小的表不建议使用索引

6.3 重复值大的列不适合创建索引

例如性别

100万行数据（男女一样的情况下）如果查找性别为男的数据，如果为性别创建了索引，则会需要去二级索引中获取到50万条数据，再回表对聚簇索引进行查找，而且还降低增删改的效率，实际的查询速度也没有多大提升，这样子的开销有可能比不创建索引的开销还大

当数据量重复度大，高于10%时，就不需要对这个字段添加索引

6.4 避免对经常更新的表创建过多的索引

6.5 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

6.6 删除不再使用或者很少使用的索引

6.7 不要定义冗余或重复的索引