写在前面：
知识的主体都来自于《MySQL是怎样运行的》
索引的分类
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照作用字段个数进行划分，分成单列索引和联合索引。
索引的创建和删除
语法
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 指定升序或者降序的索引值存储。
eg：创建组合索引
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)
);
补：
全文索引用match+against方式查询：
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);
全文索引比 like + % 快 N 倍，但是可能存在精度问题；
空间索引创建中，要求空间类型的字段必须为 非空 。
在已经存在的表上创建索引
使用ALTER TABLE语句创建索引
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
使用CREATE INDEX创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
删除索引
使用ALTER TABLE删除索引
ALTER TABLE table_name DROP INDEX index_name;
使用DROP INDEX语句删除索引
DROP INDEX index_name ON table_name;
提示：删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
MySQL8.0索引新特性
1、支持降序索引
eg：CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));
2、隐藏索引
在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。
从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除 。
语法
通过ALTER TABLE语句创建
ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;
切换索引可见状态
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引
在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开（use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes设置为off(默认)，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。
set session optimizer_switch="use_invisible_indexes=on";
索引适用的条件
前言
举一个创建索引的例子：
CREATE TABLE person_info(
id INT NOT NULL auto_increment,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);
主键是 id 列，它存储一个自动递增的整数。所以 InnoDB 存储引擎会自动为 id 列建立聚簇索引。
额外定义了一个二级索引 idx_name_birthday_phone_number ，它是由3个列组成的联合索引。所以在这个索引对应的 B+ 树的叶子节点处存储的用户记录只保留 name 、 birthday 、 phone_number 这三个列的值以及主键 id 的值，并不会保存 country 列的值。一个表中有多少索引就会建立多少棵B+树，person_info表会为聚簇索引idx_name_birthday_phone_number索引建立2棵 B+ 树。
索引idx_name_birthday_phone_number示意如图：
idx_name_birthday_phone_number 索引对应的 B+ 树中页面和记录的排序方式就是：
先按照 name 列的值进行排序。
如果 name 列的值相同，则按照 birthday 列的值进行排序。
如果 birthday 列的值也相同，则按照 phone_number 的值进行排序。
1、字段的数值有唯一性的限制
业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。
2、全值匹配（频繁作为 WHERE 查询条件的字段；UPDATE、DELETE 的 WHERE 条件列）
如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配
eg:
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND
phone_number = '15123983239';
因为 B+ 树的数据页和记录先是按照 name 列的值进行排序的，所以先可以很快定位 name 列的值是 Ashburn
的记录位置。
在 name 列相同的记录里又是按照 birthday 列的值进行排序的，所以在 name 列的值是 Ashburn 的记录里又
可以快速定位 birthday 列的值是 '1990-09-27' 的记录。
如果很不幸， name 和 birthday 列的值都是相同的，那记录是按照 phone_number 列的值排序的，所以联合
索引中的三个列都可能被用到。
ps：WHERE 子句中的几个搜索条件的顺序对查询结果没有影响
eg：
SELECT * FROM person_info WHERE birthday = '1990-09-27' AND phone_number = '15123983239' A
ND name = 'Ashburn';
MySQL 有一个叫查询优化器的东东，会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定
先使用哪个搜索条件，后使用哪个搜索条件。
对数据按照某个条件进行查询后再进行UPDATE或DELETE的操作，如果对 WHERE 字段创建了索引，能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
3、用于排序；分组（ 经常 GROUP BY 和 ORDER BY 的列）
GROUP BY 和 ORDER BY 其实就是将查出来的数据进行排序（有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。在 MySQL 中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名： filesort ）），而添加了索引的数据，本身就是有序了的，所以直接从索引中拿就行，然后进行 回表 操作取出该索引中不包含的列就好了。
排序eg：
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
分组eg：
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, ph
one_number
这个查询语句相当于做了3次分组操作：
- 先把记录按照 name 值进行分组，所有 name 值相同的记录划分为一组。
- 将每个 name 值相同的分组里的记录再按照 birthday 的值进行分组，将 birthday 值相同的记录放到一个小
分组里，所以看起来就像在一个大分组里又化分了好多小分组。 - 再将上一步中产生的小分组按照 phone_number 的值分成更小的分组，所以整体上看起来就像是先把记录分
成一个大分组，然后把 大分组 分成若干个 小分组 ，然后把若干个 小分组 再细分成更多的 小小分组 。
ps：对于 联合索引 有个问题需要注意， ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY phone_number, birthday, name 的顺序，那也是用不了 B+ 树索引，遵循最左匹配原则
4、DISTINCT 字段需要创建索引
5、多表 JOIN 连接操作时，创建索引
对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。
JOIN course
ON student_info.course_id = course.course_id
6、匹配左边的列；匹配列前缀；匹配范围值；精确匹配某一列并范围匹配另外一列
前言：遵循最左匹配原则，即条件的顺序要和索引的顺序一样，不能跳过某一个条件。
先前创建的索引：idx_name_birthday_phone_number (name, birthday, phone_number)
1、匹配左边的列：
搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行：
SELECT * FROM person_info WHERE name = 'Ashburn';
//这样也可以
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';
//这样不行
SELECT * FROM person_info WHERE birthday = '1990-09-27';
//这样只会用到name列的索引
SELECT * FROM person_info WHERE name = 'Ashburn' AND phone_number = '15123983239';
如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。
2、匹配列前缀
索引中存在是字符串类型的列，而字符串的排序其实是:
先比较字符串的第一个字符，第一个字符小的那个字符串就比较小。
如果两个字符串的第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小。
如果两个字符串的第二个字符也相同，那就接着比较第三个字符，依此类推。
也就是说这些字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的，比方说我们想查询名字以 ‘As’ 开头的记录，那就可以这么写查询语句：
SELECT * FROM person_info WHERE name LIKE 'As%';
但是需要注意的是，如果只给出后缀或者中间的某个字符串，比如这样：
SELECT * FROM person_info WHERE name LIKE '%As%';
MySQL 就无法快速定位记录位置了，因为字符串中间有 ‘As’ 的字符串并没有排好序，所以只能全表扫描了。
如果确实要为字符串建立索引，会有两个问题
B+ 树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。
如果 B+ 树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。
解决方式是：只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。
比方说我们在建表语句中只对 name 列的前10个字符进行索引可以这么写：
CREATE TABLE person_info(
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
name(10) 就表示在建立的 B+ 树索引中只保留记录的前 10 个字符的编码，这种只索引字符串值的前缀的策略是
我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。
但使用部分前缀的时候会出现另一个问题
比如说在上面已经为name所在的列创建了索引，取前10个字符编码，此时执行以下查询就不太可行
SELECT * FROM person_info ORDER BY name LIMIT 10;
因为二级索引中不包含完整的 name 列信息，所以无法对前十个字符相同，后边的字符不同的记录进行排序，也
就是使用索引列前缀的方式无法支持使用索引排序，只好用文件排序。
3、匹配范围值
在构建好的索引中，所有记录都是按照索引列的值从小到大的顺序排好序的。 所以在我们进行范围查询的时候也能起很好的作用。
eg：
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
其对应的实际操作：
找到 name 值为 Asa 的记录。
找到 name 值为 Barlow 的记录。
由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记
录都可以很容易的取出来
找到这些记录的主键值，再到 聚簇索引 中 回表 查找完整的记录。
注意：如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+ 树索引
eg：
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-0
1';
上面查询的操作步骤;
1. 通过条件 name > 'Asa' AND name < 'Barlow' 来对 name 进行范围，查找的结果可能有多条 name 值不同的
记录，
2. 对这些 name 值不同的记录继续通过 birthday > '1980-01-01' 条件继续过滤。
但是：
对于联合索引 idx_name_birthday_phone_number 来说，只能用到 name 列的部分，而用不到 birthday 列的部分，因为只有 name 值相同的情况下才能用 birthday 列的值进行排序，而这个查询中通过 name 进行范围查找的记录中可能并不是按照 birthday 列进行排序的，所以在搜索条件中继续以 birthday 列进行查找时是用不到这个 B+ 树索引的。
4、精确匹配某一列并范围匹配另外一列
虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找，比方说这样：
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday
< '2000-12-31' AND phone_number > '15100000000';
创建索引要注意的事项
1、使用列的类型小的创建索引
以整数类型为例，有 TINYINT 、 MEDIUMINT 、 INT 、 BIGINT
这么几种，它们占用的存储空间依次递增，我们这里所说的 类型大小 指的就是该类型表示的数据范围的大小。
能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况
下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用 BIGINT ，能使用 MEDIUMINT 就不要使用
INT ～ 这是因为：
数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东东）
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带
来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
2、使用字符串前缀创建索引
在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本
区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达
90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;
选择合适的长度。
3、为区分度高(散列性高)的列适合作为索引（考虑列的基数，有大量重复数据的列上不要建立索引）
列的基数 指的是某一列中不重复数据的个数，比方说某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8 ，虽然有 9 条
记录，但该列的基数却是 3 。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基
数越小，该列中的值越集中。
4、在多个字段都要创建索引的情况下，联合索引优于单值索引
5、让索引列在比较表达式中单独出现
以下两个 WHERE 子句虽然语义是一致的，但是在效率上却有差别：
WHERE my_col * 2 < 4
WHERE my_col < 4/2
第1个 WHERE 子句中 my_col 列并不是以单独列的形式出现的，而是以 my_col * 2 这样的表达式的形式出现的，
存储引擎会依次遍历所有的记录，计算这个表达式的值是不是小于 4 ，所以这种情况下是使用不到为 my_col 列建立的 B+ 树索引的。而第2个 WHERE 子句中 my_col 列并是以单独列的形式出现的，这样的情况可以直接使用B+ 树索引。
所以结论就是：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。
6、注意主键插入顺序
对于一个使用 InnoDB 存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在 聚簇索引 的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插
如果我们插入的主键值忽大忽小的话，这就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在 1~100 之间
如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：
此时我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入。
7、冗余和重复索引
冗余索引eg：
CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
KEY idx_name (name(10))
);
idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对
name 列的索引就算是一个 冗余 索引，维护这个索引只会增加维护的成本
重复索引eg:
CREATE TABLE repeat_index_demo (
c1 INT PRIMARY KEY,
c2 INT,
UNIQUE uidx_c1 (c1),
INDEX idx_c1 (c1)
);
c1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚
簇索引，所以定义的唯一索引和普通索引是重复的
另外一些注意：
数据量小的表最好不要使用索引
避免对经常更新的表创建过多的索引
索引列的匹配类型要相同：比如varchar的索引在使用时： key = 123 。那么久会形成类型转换，应该直接
key = ‘123’，避免类型转换，无论是自动还是手动的
不建议用无序的值作为索引：例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、
要限制索引的数目
MD5、HASH、无序长字符串等。
回表的代价
在使用二级索引的时候，为了获取某些数据，有可能需要进行回表操作：
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
从索引 idx_name_birthday_phone_number 对应的 B+ 树中取出 name 值在 Asa ～ Barlow 之间的用户记录。
由于索引 idx_name_birthday_phone_number 对应的 B+ 树用户记录中只包含 name 、 birthday 、
phone_number 、 id 这4个字段，而查询列表是 * ，意味着要查询表中所有字段，也就是还要包括 country
字段。这时需要把从上一步中获取到的每一条记录的 id 字段都到聚簇索引对应的 B+ 树中找到完整的用户记
录，也就是我们通常所说的 回表 ，然后把完整的用户记录返回给查询用户。
由于索引 idx_name_birthday_phone_number 对应的 B+ 树中的记录首先会按照 name 列的值进行排序，所以值
在 Asa ～ Barlow 之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这
些连着的记录从磁盘中读出来，这种读取方式我们也可以称为顺序I/O。根据第1步中获取到的记录的 id 字段
的值可能并不相连，而在聚簇索引中记录是根据 id （也就是主键）的顺序排列的，所以根据这些并不连续的 id
值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数
据页，这种读取方式我们也可以称为 随机I/O 。一般情况下，顺序I/O比随机I/O的性能高很多，所以步骤1的执行可能很快，而步骤2就慢一些。所以这个使用索引 idx_name_birthday_phone_number 的查询有这么两个特点：
会使用到两个 B+ 树索引，一个二级索引，一个聚簇索引。
访问二级索引使用 顺序I/O ，访问聚簇索引使用 随机I/O 。
需要回表的记录越多，使用二级索引的性能就越低
至于什么时候使用采用二级索引 + 回表的方式去执行查询呢？这个是查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索引 + 回表 的方式。一般情况下，限制
查询获取较少的记录数会让优化器更倾向于选择使用 二级索引 + 回表 的方式进行查询。
eg：
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' LIMIT 10;
有排序需求的查询，上边讨论的采用 全表扫描 还是 二级索引 + 回表 的方式进行查询的条件也是成立的，比方说下边这个查询：
SELECT * FROM person_info ORDER BY name, birthday, phone_number;
由于查询列表是 * ，所以如果使用二级索引进行排序的话，需要把排序完的二级索引记录全部进行回表操作，这
样操作的成本还不如直接遍历聚簇索引然后再进行文件排序（ filesort ）低，所以优化器会倾向于使用 全表扫描 的方式执行查询。如果我们加了 LIMIT 子句，比如这样：
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
这样需要回表的记录特别少，优化器就会倾向于使用 二级索引 + 回表 的方式执行查询。
覆盖索引
为了告别 回表 操作带来的性能损耗，最好在查询列表里只包含索引列（需要什么字段就查什么字段，不要用）*
SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlo
w'
因为我们只查询 name , birthday , phone_number 这三个索引列的值，所以在通过
idx_name_birthday_phone_number 索引得到结果后就不必到 聚簇索引 中再查找记录的剩余列，也就是country 列的值了，这样就省去了 回表 操作带来的性能损耗。我们把这种只需要用到索引的查询方式称为 索引覆盖 。排序操作也优先使用 覆盖索引 的方式进行查询，比方说这个查询：
SELECT name, birthday, phone_number FROM person_info ORDER BY name, birthday, phone_numbe
r;
虽然这个查询中没有 LIMIT 子句，但是采用了 覆盖索引 ，所以查询优化器就会直接使用
idx_name_birthday_phone_number 索引进行排序而不需要回表操作了。