# 索引

MySQL 中的索引称为 index、key ，索引中的每条数据称为条目（entry）。
- 用户可以对一张数据表的任意个字段建立任意个单列索引、组合索引，然后在 WHERE 子句中，刻意使用已建立索引的字段作为查询条件，MySQL 便会自动使用索引加速查询，避免全表扫描。
- 创建索引之后，MySQL 会自动保存、更新索引，不需要用户手动编辑。

# 单列索引

可以对一张数据表的单个列字段建立一个索引，称为单列索引（column index）。
- 适合只考虑一个字段的查询语句，比如：WHERE name='one'

例：

CREATE TABLE tb1 (..., INDEX index1(name)); -- 创建表 tb1 时，对字段 name 建立索引，命名为 index1

CREATE INDEX index1 ON tb1(name);           -- 给表添加索引
ALTER TABLE tb1 ADD  INDEX index1(name);    -- 另一种添加索引的语法
ALTER TABLE tb1 DROP INDEX index1;          -- 删除表的索引

SHOW INDEX FROM tb1;                        -- 显示表的所有索引

# 组合索引

可以对一张数据表的多个列字段建立一个索引，称为组合索引（composite index）。
- 组合索引适合同时考虑多个字段的查询语句，比如：WHERE id>1 AND name='one'

例：

ALTER TABLE tb1 ADD INDEX index1(id, name(10), age);

WHERE 子句中同时查询多个字段时，比如 WHERE a=xx AND b=xx; ，MySQL 会将这些字段组合为一个 group ，比如 (a,b) ，然后检查能否使用 composite index ：
- 如果 group 中存在 composite index 的第一个字段，并且 group 中的所有字段都包含于 composite index ，则可以使用该 composite index 。

假设 tb1 表中只有组合索引 index1(a, b, c) ，分析以下查询语句：

-- 以下查询会使用 index1
EXPLAIN SELECT * FROM tb1 WHERE a=xx;
EXPLAIN SELECT * FROM tb1 WHERE a=xx AND b=xx;
EXPLAIN SELECT * FROM tb1 WHERE b=xx AND a=xx;  -- AND 两侧的字段顺序不重要， (a,b) 和 (b,a) 两种字段组合的效果一样
EXPLAIN SELECT * FROM tb1 WHERE a=xx AND c=xx;  -- a、c 不是 index1(a, b, c) 中的连续字段，也可以使用组合索引

-- 以下查询不会使用 index1
EXPLAIN SELECT * FROM tb1 WHERE b=xx;           -- 字段 b 不是 index1(a, b, c) 中的第一个字段
EXPLAIN SELECT * FROM tb1 WHERE b=xx AND c=xx;  -- 字段组合 (b, c) 不包含 index1(a, b, c) 中的第一个字段
EXPLAIN SELECT * FROM tb1 WHERE a=xx OR b=xx;   -- OR 两侧的字段不能组合为一个 group ，不能使用组合索引。虽然 WHERE a=xx 可使用组合索引，但这里 SELECT * 需要回表查询，因此不使用索引，查询类型为 all

# 前缀索引

对 char、varchar 等字符串型的字段建立索引时，可以限制只对左侧连续 n 个字符建立索引，称为前缀索引（prefix index）。
- text、blob 类型的字段可能长度很大，因此只允许使用前缀索引。

例：

ALTER TABLE tb1 ADD INDEX index1(name(5));

n 越小，索引体积越小。
- n 不能过小，避免查询结果的精度过低，查询一条数据却返回多条数据。
- 一般的 InnoDB 表，index prefix 最大存储长度为 767 bytes 。存储 utf8mb4 字符时每个字符占 4 bytes ，因此 n 最大取值为 191 。
- 如果查询语句中的值，长度超过前缀索引的 n ，比如 WHERE name LIKE 'one%' 。则先查询索引，找到一批前缀匹配的数据，再从原表读取这些数据的完整值，进行查询。

# 唯一索引

创建索引时，可以加上 unique 关键字，保证索引中每条数据的取值都不同，称为唯一索引（unique index）。
- 如果用户向数据表写入一条索引值重复的数据，则会报错：Duplicate entry
- 支持将前缀索引声明为 unique 。

例：

ALTER TABLE tb1 ADD UNIQUE INDEX index1(name(5));

# 全文索引

创建索引时，可加上 FULLTEXT 关键字，对 char、varchar、text 类型的字段建立全文索引（fulltext index），从而提供全文搜索功能。
- 全文索引属于倒排索引，而不是 B+ tree 等正排索引。
- 不支持将前缀索引声明为全文索引。
- MySQL 的全文搜索功能比较简单，比 ElasticSearch 的功能少、性能低。
例：创建全文索引
```
ALTER TABLE books ADD FULLTEXT INDEX index1(title, content);
```
然后进行全文搜索：
```
SELECT * FROM books WHERE match(title, content) against('test')
```
如果有某行数据的 title 或 content 字段包含 test 单词，则返回这行数据。

# 主索引

每个 InnoDB 数据表在创建时，都会自动建立一个聚集索引（clustered index），又称为主索引（primary index），用于将数据表的所有数据行按 B+ tree 等数据结构存储。
- 如果数据表定义了主键（PRIMARY KEY），则会根据主键建立主索引。
  - 假设主键为 id 字段，如果查询 WHERE id=xx ，则能很快地在主索引中定位这条数据。如果查询 WHERE name=xx ，则只能遍历全部数据，才能找到符合查询条件的数据。
  - 因此建议为每个数据表定义主键，以便建立主索引。
- 如果数据表未定义主键：
  - 如果存在一个唯一索引，其中所有字段都要求 NOT NULL ，则由第一个这样的唯一索引担任主索引。
  - 如果不存在这样的唯一索引，则在数据表中添加一个隐藏的列，取值为 AUTO_INCREMENT ，用于建立主索引。
每个数据表有且仅有一个主索引。用户可建立其它索引，统称为非聚集索引（non-clustered index）、二级索引（secondary index）。
- 执行 SHOW INDEX FROM <table>会显示主索引、所有二级索引。
- 假设数据表的主键为 id 字段，建立了一个单列索引 index1(name) ，则存储结构如下：
  - 主索引的 B+ tree 中，在叶子节点存储数据行的全部字段，可根据 id 值定位每条数据。
  - 二级索引的 B+ tree 中，在叶子节点存储每条数据的 id 字段和 name 字段。
    - 查询 WHERE name=xx 时，先从二级索引找到目标数据，然后根据 id 值，从主索引读取这条数据的完整字段，即回表查询，会先后查询两个索引树。
- 主索引中，如果几条数据的主键取值连续，则它们在磁盘的存储地址也是连续的，因此访问磁盘时属于顺序读写。
  - 二级索引中，每条数据在磁盘的存储地址是离散的，因此访问磁盘时属于随机读写。读取大量数据时，比主索引慢，此时建议不使用二级索引，直接读取主索引。

# EXPLAIN

如果在执行 SQL 命令时加上 EXPLAIN 关键字作为前缀，则会在执行 SQL 之后，分析查询过程。如下：

例：tb1 表中 id 字段为主键并建立了索引，分析查询过程如下

mysql> EXPLAIN SELECT * FROM tb1 WHERE id='214540bee2e1dde14eec8bdcae6d3f6d';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | SELECT_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb1   | NULL       | const | PRIMARY,id    | PRIMARY | 202     | const | 1    |   100.00 |       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

显示的各列含义如下：

id ：EXPLAIN 显示结果的第几行。
partitions ：使用了分区表中的哪个分区。

type ：查询类型。查询速度从高到低依次分为：

null        # 不必读取表或索引。比如类似 SELECT 1+1 的纯运算；数据表为空；索引字段会被 B+ tree 排序，因此类似 SELECT max(id) FROM tb1 能直接找到最大值、最小值
system      # 表中只包含一行数据
const       # 查询取值唯一的索引字段，找到一个匹配结果就立即返回，不必检查剩下的数据。比如 primary 或 unique 索引字段
eq_ref      # 联表查询时，比如 SELECT * FROM tb1,tb2 WHERE tb1.id=tb.id ，如果 id 是 primary 或 unique 索引字段，则属于 eq_ref 类型
ref         # 联表查询时，如果 id 不是 primary 或 unique 索引字段，使得 tb1 中的一条数据可能对应 tb2 中的多条数据，则属于 ref 类型
index_merge # 使用了两个或更多索引，最后合并它们的查询结果
range       # 查询了索引中某个取值范围的数据。比如 WHERE id>'214540bee2e1dde14eec8bdcae6d3f6d'; ，类似的运算符有 <、>、!=、between、in、is null
index       # 遍历了索引表，检查了其中所有条数据
all         # 遍历了原表，即全表扫描

possible_keys ：当前查询可能使用哪些索引。如果 WHERE 子句查询了多个字段，其中某些字段存在于已建立的索引中，则考虑是否使用这些索引。
key ：实际使用的索引名，可能有多个。
key_len ：索引表中，每条数据预计读取的长度。必须从头开始读取字节，可能不必读取到最后一个字节。
ref ：表示跟索引字段相比较的对象。比如一个常量、联表查询时另一张表的字段名。
rows ：预计要读取多少条数据，才能找到目标数据。

Extra 记录一些额外信息，例如：

Using index       # 只读取了索引表就返回查询结果，不必读取原表，即覆盖查询。这种情况下，查询效率最高
Using WHERE       # 查询到数据之后，需要经过 WHERE 子句过滤，才返回给用户。这说明查询到了额外的数据，可能需要优化 SQL
Using temporary   # 查询到数据之后，需要建立中间表来暂存。使用 group by 时通常会这样

describe 关键字等价于 EXPLAIN ，缩写为 desc 。

可以主动控制是否使用索引，便于测试索引的效果：

EXPLAIN SELECT * FROM tb1 USE    INDEX(index1) WHERE name='one';  -- 只使用某些索引
EXPLAIN SELECT * FROM tb1 IGNORE INDEX(index1) WHERE name='one';  -- 忽略某些索引
EXPLAIN SELECT * FROM tb1 FORCE  INDEX(index1) WHERE name='one';  -- 强制使用某些索引

# 使用索引

适合创建索引的情况：
- 经常在 WHERE 子句中查询的字段。
- 经常用 ORDER BY 排序的列。
  - 大部分类型的索引采用 B+ tree 存储结构，在存储时根据建立索引的字段的取值进行了排序，因此方便查找一个值、多个值、一个范围内的值。
不适合创建索引的情况：
- 字段数量不多的表。因为查询时加速效果不明显。
- 数据表经常增删数据，或者建立索引的字段的值经常修改。因为启用索引之后，读操作更快，写操作更慢。
- 取值容易重复的列。因为索引的查询结果可能包含很多条数据，需要回表查询这么多数据，耗时较久。
  - 通常字段的长度越大，取值重复率越低，但是索引体积也会越大，占用更多内存。
  - 例如字段取值为递增编号，或哈希值前几位时，长度小，并且取值几乎不会重复。但是取值缺乏意义，不方便人记忆。
用户执行查询语句时，MySQL 会自动判断是否使用索引，流程如下：
1. 找到与查询字段匹配的所有索引，记作 possible_keys 。
2. 比较 possible_keys 中的各个索引，考虑使用哪个索引。
  - 如果预期使用索引的效率比全表扫描更低，则不会使用索引。这是一条大致的原则，不一定准确，建议用 EXPLAIN 检查 SQL 是否使用了索引。
  - 如果一个字段同时匹配多个索引，比如一个单列索引、一个组合索引，则使用开销最小的那个索引。
  - 如果同时查询多个字段，这些字段匹配不同的索引，则可以分别查询这些索引，然后合并查询结果。

假设 tb1 表中 id 字段为主键，其它字段未建立索引，下面分析使用索引的常见情况：

例：

EXPLAIN SELECT id FROM tb1;   -- 没有 WHERE 查询条件，但 SELECT id 只需要读取主索引的索引键，不需要读取其它字段，因此查询类型为 index
EXPLAIN SELECT name FROM tb1; -- 查询类型为 all ，因为 SELECT 读取的字段不是索引键
EXPLAIN SELECT * FROM tb1;    -- 查询类型为 all ，与上一条同理

EXPLAIN SELECT * FROM tb1 WHERE id='214540bee2e1dde14eec8bdcae6d3f6d';  -- 查询类型为 const
EXPLAIN SELECT * FROM tb1 WHERE id>'214540bee2e1dde14eec8bdcae6d3f6d';  -- 查询类型为 range
EXPLAIN SELECT * FROM tb1 WHERE id>1; -- 查询类型为 all ，因为 1 与 id 的数据类型不兼容，相当于没有查询条件，预计匹配所有数据，而 SELECT 的字段不支持覆盖索引，此时使用索引不如全表扫描
EXPLAIN SELECT * FROM tb1 WHERE id is not null;   -- 查询类型为 all ，与上一条同理

查询语句像 WHERE id LIKE A 时，如果 A 是字符串常量、且不以 % 开头，才会使用索引。

查询语句像 WHERE ... AND ... 时，比如 WHERE a=1 AND b=2 ，如果只有字段 a 或 b 建立了索引，则 MySQL 会先查询该字段，筛选出数据之后再执行其它字段的查询条件。如下：

-- 这两条查询语句的效果一样，查询类型为 range
EXPLAIN SELECT id FROM tb1 WHERE id>'214540bee2e1dde14eec8bdcae6d3f6d' AND name='one';
EXPLAIN SELECT id FROM tb1 WHERE name='one' AND id>'214540bee2e1dde14eec8bdcae6d3f6d';

查询语句像 WHERE ... OR ... AND ... 时，如果 OR 左侧的查询没有使用索引，进行了一次全表扫描，则右侧的查询也不会使用索引。如果 OR 左右任一侧没有使用索引，则 AND 之后的查询都不会使用索引。