mysql索引

索引是数据库中用于加速数据检索的一种数据结构。它类似于书籍的目录，通过索引可以快速定位到数据的位置，而不需要扫描整个表。

索引的作用

• 加速查询：索引可以显著提高查询速度，尤其是在大数据量的表中。
• 优化排序和分组：索引可以帮助数据库快速完成排序和分组操作。
• 保证数据唯一性：唯一索引可以确保某一列或多列的值唯一。

索引的代价

• 存储空间：索引需要占用额外的存储空间。
• 写操作性能：索引会降低插入、更新和删除操作的性能，因为每次写操作都需要更新索引。

索引结构

B+树索引

• B+树是MySQL中最常用的索引结构，适用于范围查询和排序操作。
• 特点
  • 所有数据都存储在叶子节点中，内部节点仅用于索引。
  • 叶子节点通过指针连接，形成一个有序链表，便于范围查询。

哈希索引

• 哈希索引基于哈希表实现，适用于等值查询。
• 特点：
  • 查询速度极快，但不支持范围查询和排序。
  • 只有Memory存储引擎支持哈希索引。

全文索引

• 全文索引用于全文搜索，支持自然语言查询。
• 特点:
  • 适用于文本数据的搜索。
  • 只有MyISAM和InnoDB存储引擎支持全文索引。

R-Tree索引

• R-Tree索引用于空间数据查询，如地理坐标。
• 特点：
  • 适用于地理信息系统（GIS）等场景。
  • 只有MyISAM存储引擎支持R-Tree索引。

索引分类

MySQL可以按照四个角度来分类索引。

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

按数据结构分类

从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

这两个区别在前面也提到了：

• 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

• 主键索引

  主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

• 唯一索引

  唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

• 普通索引

  普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

• 前缀索引

  前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

  使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引；

通过将多个字段组合成一个索引，该索引就被称为联合索引。

最左匹配原则

最左匹配原则的定义‌

最左匹配原则是指在联合索引中，查询条件必须从索引的最左端开始，按照索引列的顺序依次匹配。如果查询条件没有从最左端开始，或者中间有列未匹配，索引将无法完全生效或部分失效。

联合索引的结构与匹配过程

联合索引的底层是B+树，其节点按照索引列的顺序存储数据。例如，联合索引(a, b, c)的B+树会先按a排序，a相同的情况下按b排序，b相同的情况下再按c排序。查询时，数据库会依次匹配a、b、c，如果某列未匹配，后续列的索引将无法使用。

最左匹配原则的应用场景

完全匹配‌：查询条件包含联合索引的所有列，且顺序一致。例如，索引(a, b, c)的查询WHERE a=1 AND b=2 AND c=3会完全利用索引。 顺序可以不一致, 存储引擎会先优化sql.

部分匹配‌：查询条件从最左端开始，但未包含所有列。例如，WHERE a=1 AND b=2会利用索引的前两列，而WHERE a=1 AND c=3只能利用a列，c列无法通过索引加速。

范围查询‌：如果查询条件中包含范围查询（如>、<、BETWEEN），范围查询之后的列将无法使用索引。例如，WHERE a=1 AND b>2 AND c=3只能利用a和b列，c列无法通过索引加速。

EXPLAIN命令

用于分析SQL查询的执行计划。

EXPLAIN select * from actor WHERE last_name = 'GUINESS'

EXPLAIN返回的结果包含多个字段，重点关注以下几个：

id

• 查询的标识符，数值越大优先级越高，相同数值按顺序执行。

type

• 数据访问类型，从最优到最差排序
  • system/const：单条记录查询（主键或唯一索引）。
  • eq_ref：唯一索引扫描（如JOIN操作）。
  • ref：非唯一索引扫描。
  • range：范围扫描（如WHERE age > 18）。
  • index：全索引扫描（仅扫描索引树）。
  • ALL：全表扫描（性能最差）。

possible_keys

• 可能使用的索引，但不一定实际使用。

key

• 实际使用的索引，若为NULL则未使用索引。

key_len

• 索引使用的字节数，用于评估索引的选择性。

rows

• MySQL 估算的扫描行数，值越小越好。

Extra

• 额外信息，常见值：
  • **Using filesort**：需额外排序（性能开销大）。
  • **Using temporary**：使用临时表（如GROUP BY或ORDER BY）。
  • **Using index**：覆盖索引（仅通过索引即可获取所有数据）。