SQL ⑦-索引

索引

索引是一种特殊的数据结构，它帮助数据库系统高效地找到数据。

索引通过一定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。

索引好比书籍的目录，能帮助你快速找到相应的章节。

B+树

B+树是一种经常用于数据库和文件系统等场合的平衡查找树，为MySQL索引采用的数据结构。

非叶子节点仅具有索引作用，不存储数据，所有叶子节点保存真实的数据。

所有叶子节点构成一个有序链表，可以按照 key 排序的次序依此遍历全部数据。

页

记录是保存在 InnoDB 页中的，InnoDB 存储引擎将数据划分为若干个页，以页作为磁盘和内存之间交互的最小单位。

InnoDB 中页的大小默认为 16KB。也就是默认情况下，一次最少从磁盘中读取16KB的数据到内存中，一次最少把内存中 16KB 的内容刷新到磁盘上。

存储用户记录的页我们统一叫做数据页。

B+树在 MySQL 索引中的应用

• 表中有主键，则会自动为主键建立索引，称为主键索引。
  
  

• 非叶子节点记录的是索引信息，称为索引页。索引页保存的是主键的值和子节点的引用。

• 叶子节点记录的是真实的数据，称为数据页。数据页保存的是真实的数据。

• 页于页之间通过页号建立关联关系。

索引分类

主键索引

当在一个表上定义一个主键 PRIMAY KEY 时，InnoDB 使用它作为聚集索引。

所以推荐为每个表定义一个主键。如果没有逻辑上唯一且非空的列或列集可以使用主键，则推荐添加一个自增列。

普通索引

主键索引是在搜索条件为主键时才会发挥作用，但如果搜索条件不是主键，则需要使用普通索引。为那个单独的列添加索引，可以提高查询效率。

对于需要经常搜索的列，可以添加索引。

组合索引

组合索引是指两个或多个列组合在一起创建的索引。

组合索引可以提高查询效率，因为组合索引可以减少索引的数量，减少磁盘 IO，提高查询性能。

使用组合索引时，例如：index(name, phone)，会优先对 name 进行排序，如果 name 相同时，然后再对 phone 进行排序。所以 phone 并非总是有序的。

唯一索引

当在一个表上定义一个唯一键 UNIQUE 时，自动创建唯一索引。

与普通索引类似，但区别在于唯一索引的列值必须唯一。

全文索引

全文索引是一种特殊的索引，它对文本字段进行索引，可以快速查找包含指定关键词的记录。

全文索引需要 MySQL 的插件支持，如 MyISAM、InnoDB 等。

聚集索引

如果没有表定义主键，InnoDB 使用第一个 UNIQUE 和 NOT NULL 的列作为聚集索引。

如果表中没有 PRIMARY KEY 或者 UNIQUE 索引，InnoDB 会创建一个隐藏的聚集索引。为 6 字节的 ROW_ID 字段记录，ROW_ID 单调递增，并使用 ROW_ID 作为索引。

非聚集索引

聚集索引之外的索引，称为非聚集索引或者二级索引。

二级索引中的每条记录都包含该行的主键列，以及二级索引指定的列。

索引操作

创建索引

CREATE INDEX index_name ON table_name (column1[, column2,...]);CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype,INDEX (column3 [, column2])
);

删除索引

ALTER table_name DROP PRIMARY KEY; # 删除主键索引alter table table_name drop index index_name; # 删除其他索引

查看是否使用索引

EXPLAIN SELECT_STATEMENT;

使用后，会出现 type、possible_keys 和 key 字段。

possible_keys 字段表示可能用到的索引，key 字段表示实际用到的索引。

type 字段表示索引类型，有 ALL、index、range、ref、eq_ref、const、system、NULL 等。

• ALL：全表扫描，没有任何索引可以使用。
• index：索引扫描，扫描全部索引树。
• range：索引范围扫描，查询语句中使用了索引范围。
• ref：索引的一种查找方式，索引匹配某个值，但是不一定是唯一的。
• eq_ref：索引的一种查找方式，索引匹配某个值，而且唯一。
• const、system：单表中最多有一个匹配的行，查询起来非常迅速。
• NULL：不用访问表或者索引，直接就能得到结果。
• Extra 字段表示额外信息。
  • Using index 表示查询时使用了索引，表示没有查询主键索引表或者数据表，而是直接使用当前索引就能得到结果，也叫做索引覆盖。
  • Using where 表示查询时使用了 WHERE 条件。

如何用好索引

索引的代价

创建索引可以提高查询效率，那我可以为每个字段都创建索引吗？

这当然不合适~~~

空间代价

创建索引后，就相当于创建了一颗 B+树。

每一颗 B+树 的节点都是一个数据页，每一个数据页都占用 16KB 空间。

创建大量索引，会占用大量的磁盘空间。

时间代价

空间代价还可以使用 “钞能力” 来解决，但是时间代价却是无法回避的。

B+树 的叶子节点之间是通过相互连接的引用来组织的，也就是链表，且是有序的，当我们新增或者删除一个节点时，势必要对其他节点进行调整，这势必会影响查询效率。（删除不会真的删除，只是标记为删除，等到合适的时候才会真的删除）。

执行查询语句之前，MySQL 查询优化器会基于 cost 成本对一条查询语句进行优化，并生成一个执行计划。如果创建的索引太多，优化器会计算每个索引的搜索成本，导致在分析过程中耗时太多，最终影响查询语句的执行效率。

回表的代价

在使用二级索引时，我们可以找到一些信息，但如果二级索引的信息不全，只有二级索引的列的值和主键值。

那么我们就需要拿着主键值通过主键索引再去查询一次，得到完整的记录。这个过程称为回表查询。

回表查询的还是不小的，容易出现随机 IO 的情况。

能不回表查询就不回表查询。

要回表查询就尽量减少回表查询的次数。

索引覆盖

索引覆盖：索引覆盖是指索引包含了查询语句的所有列，不需要回表查询。

比如，我们查询 id，name，age 三个字段。

SELECT id, name, age FROM table_name WHERE name = 'zs';

如果我们给 name 和 age 创建了联合索引，那么就可以直接使用该二级索引，而不需要回表查询了。

索引下推

索引下推 (Index Condition Pushdown，ICP) 是数据库优化技术的一种，它允许数据库引擎在索引扫描阶段，而不是在表扫描阶段，应用部分查询条件。这样可以减少从磁盘读取的行数，从而提高查询效率。

在没有 ICP 的情况下，数据库引擎会首先根据索引找到满足条件的行，然后将这些行的数据从磁盘读取到内存中，再应用查询条件进行过滤。这个过程可能会读取大量的行，即使只有一部分行满足查询条件。

而 ICP 技术则优化了这一过程。它使得数据库引擎可以在索引扫描的过程中直接应用查询条件，对于不满足条件的行，直接跳过，不再读取这些行的详细数据到内存中。这样就大大减少了 I/O 操作，提高了查询性能。

ICP技术主要在 MySQL 数据库中得到广泛应用，在其他一些数据库管理系统中也有类似的优化技术。

详情请看：索引下推详细

索引失效

索引失效是指索引对查询的优化没有帮助，或者索引没有完全起作用。

违反最左前缀原则

最左前缀原则 (Leftmost Prefix Principle，LPP) 指的是索引字段的选择，索引字段的选择应该遵循从左到右的顺序。

例如：我们创建了一个联合索引：(name, phone)，我们直接使用 phone 作为条件进行查询。

EXPLAIN SELECT * FROM table_name WHERE phone = '123456';

可以看到 possible_keys 和 key 字段都为 NULL，表示没有使用索引。

如果我们加上 name 字段作为搜索条件

EXPLAIN SELECT * FROM table_name WHERE phone = '123456' AND name = 'zs';

这时 possible_keys 和 key 字段都不为 NULL，表示使用了索引。且我们不需要关系是先写 name 还是先写 phone，因为查询优化器会自动优化查询计划。

使用反向查询

MySQL 在使用反向查询（!=、<>、NOT LIKE）的时候无法使用索引，会导致全表扫描，覆盖索引除外。

LIKE以通配符开头

当使用 name LIKE '%xxx' 或者 name LIKE '%xxx%' 这两种都会导致索引失效。

因为联合索引的 B+树 数据页内的记录首先按照 name 字段排序，这两种搜索方式不在意 name 字段的开头是什么，自然就无法使用到索引，只能通过全表扫描的方式进行查询。

对索引列做任何操作

如果不是单纯的使用索引列，而是使用索引列进行了某些操作，例如数值计算，使用函数，手动或自动类型转换等操作，都会导致索引失效。

使用函数

EXPLAIN SELECT * FROM table_name WHERE LEFT(name, 3) = 'abc';

MySQL8.0 新增了函数索引的功能，我们可以给函数作用之后的结果创建索引。

ALTER TABLE table_name ADD KEY INDEX idx_name_left (LEFT(name, 3));

使用表达式

EXPLAIN SELECT age FROM table_name WHERE age + 1 = 18;

上述sql语句不会走索引，但是改成 age = 18 - 1 后就可以了。

EXPLAIN SELECT age FROM table_name WHERE age = 18 - 1;

使用类型转换

例子1 {#example1}

假设 phone 为 varchar 类型。我们创建 phone 列的索引。

EXPLAIN SELECT * FROM table_name WHERE phone = 123456;

通过结果得知上述查询没有走索引。

例子2

我们再假设 phone 列的类型为 bigint。同样创建索引。

EXPLAIN SELECT * FROM table_name WHERE phone = '123456';

通过结果得知，上述查询走了索引。

通过上述两个例子得知，当索引字段的数据类型为字符串，使用数字类型进行查询不会走索引；当索引字段数据类型为数字类型时，使用字符串类型进行查询会走索引。
我们需要知道 MySQL 的数据类型转换规则是什么。简单地就是 MySQL 会自动将数字转化为字符串，还是自动将字符串转化为数字。

我们只需执行一个 SELECT '10' > 9 即可知道 MySQL 的转换规则。

• 如果结果为 1，说明 MySQL 自动将数字 '10' 转化为字符串 10。所以 '10 > 9' 为 1。
• 如果结果为 0，说明 MySQL 自动将字符串 9 转化为数字 '9'。所以 '10' > '9' 为 0。
  执行我们发现，结果为 1，说明 MySQL 自动会将字符串类型转换为数字类型。

这也就解释了为什么 例子1 中 phone 列的索引失效了。

OR 连接

使用 OR 连接的查询语句，如果 OR 之前的条件列是索引列，但是 OR 之后的条件列不是索引列，则不会使用索引。

索引创建原则

不为离散度低的列创建索引

举个例子，gender（性别）列只有0、1两个值，列的离散度非常低，假如我们为该列创建索引，我们会在二级索引中搜索到大量的重复数据，然后进行大量回表操作。

只为用于搜索、排序或分组的列创建索引

我们只为出现在WHERE子句中的列或者出现在ORDER BY和GROUP BY子句中的列创建索引即可。仅出现在查询列表中的列不需要创建索引。

用好联合索引

不要为联合索引的第一个索引列单独创建索引

建立联合索引的时候，一定要把最常用的列放在最左边

对过长的字段，建立前缀索引

如果一个字符串格式的列占用的空间比较大（就是说允许存储比较长的字符串数据），为该列创建索引，就意味着该列的数据会被完整地记录在每个数据页的每条记录中，会占用相当大的存储空间。

对此，我们可以为该列的前几个字符创建索引，也就是在二级索引的记录中只会保留字符串的前几个字符。比如我们可以为 phone 列创建索引，索引只保留手机号的前3位。

频繁更新的值，不要作为主键或索引

因为可能涉及到数据页分裂的情况，会影响性能。

随机无序的值，不建议作为索引，例如身份证、UUID

索引结构（如B+树）对有序数据有更好的维护和查询效率。

使用随机无序的数据会导致：

• 页分裂问题：每次插入新数据时可能需要重新调整B+树结构，造成频繁的页分裂（Page Split）
• 缓存不友好：随机值破坏了局部性原理（Locality），相邻数据在物理存储上不连续。
  们可以为 phone 列创建索引，索引只保留手机号的前3位。

频繁更新的值，不要作为主键或索引

因为可能涉及到数据页分裂的情况，会影响性能。

随机无序的值，不建议作为索引，例如身份证、UUID

索引结构（如B+树）对有序数据有更好的维护和查询效率。

使用随机无序的数据会导致：

• 页分裂问题：每次插入新数据时可能需要重新调整B+树结构，造成频繁的页分裂（Page Split）
• 缓存不友好：随机值破坏了局部性原理（Locality），相邻数据在物理存储上不连续。
• 查询范围效率低：例如身份证号 110101199003071234 和 310104198502151122 这类无序数据，当进行范围查询时，即使使用索引，仍需扫描大量不连续的叶子节点。