索引

索引是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树（如B+树）,全文索引和 R 树索引等数据结构。

作用：

• 加快数据检索速度：通过索引，数据库系统可以不必扫描整个表，而是直接定位到数据的存储位置。

• 加快表与表之间的连接：在实现数据的参考完整性时，通过在表中创建外键索引，可以加快表与表之间的连接速度。

• 减少分组和排序时间：通过索引，可以加快分组和排序的速度，因为索引本身就是有序的。

索引类型：

MySQL支持多种索引类型，包括普通索引（INDEX）、唯一索引（UNIQUE）、主键索引（PRIMARY KEY）、全文索引（FULLTEXT）和空间索引（SPATIAL）等。其中，主键索引是一种特殊的唯一索引，它不允许有空值。

普通索引（Normal Index）

• 特点：最基本的索引类型，没有唯一性约束，允许索引列中存在重复值和空值。

• 用途：主要用于提高查询速度，尤其适用于那些经常出现在查询条件（WHERE）或排序条件（ORDER BY）中的列。

• 使用场景：假设有一个用户表（users），其中包含用户ID（user_id）、用户名（username）和邮箱（email）等字段。如果经常需要根据用户名查询用户信息，那么可以在username字段上创建普通索引。

唯一索引（Unique Index）

• 特点：索引列中的值必须是唯一的，但允许存在空值（NULL）。

• 用途：确保表中每一行数据的唯一性，防止数据重复插入。

• 使用场景：在注册用户时，可以使用唯一索引来确保用户名的唯一性，避免用户名的重复注册。

主键索引（Primary Key Index）

• 特点：一种特殊的唯一索引，不允许存在空值（NULL），且一个表只能有一个主键索引。

• 用途：唯一标识数据库表中的每条记录，常用于表的主键列。

• 使用场景：在用户表（users）中，通常将用户ID（user_id）设置为主键，并创建主键索引，以确保每个用户都有一个唯一的标识符。

组合索引（Composite Index）

• 特点：在表中的多个字段组合上创建的索引，使用时需要遵循最左前缀原则。

• 用途：提高基于多个列查询的性能，特别是当查询条件中经常同时出现这些列时。

• 使用场景：假设有一个订单表（orders），其中包含订单ID（order_id）、用户ID（user_id）和订单日期（order_date）等字段。如果经常需要根据用户ID和订单日期查询订单信息，那么可以在user_id和order_date字段上创建组合索引。

全文索引（Full-text Index）

• 特点：主要用于在大量文本数据中搜索关键字，而不是直接与索引中的值进行比较。

• 用途：在文本字段上创建，用于全文搜索。

• 使用场景：在文章管理系统、博客平台等应用中，经常需要根据文章标题或内容搜索相关文章。此时，可以在文章标题或内容字段上创建全文索引，以提高搜索效率。

不同类型的索引在数据库管理系统中扮演着不同的角色，它们各有特点并适用于不同的使用场景。在选择索引类型时，需要根据实际的数据表结构和查询需求来决定。同时，还需要注意索引的创建和维护成本，避免过度索引导致的性能问题。

索引的使用情况

在执行数据查询过程中，以下情况会用到索引：

• 查询条件中使用了索引列：当查询条件（如WHERE子句中的条件）中包含了索引列时，MySQL会尝试使用索引来加速查询。

• 排序和分组操作中使用了索引列：如果查询中的排序（ORDER BY）或分组（GROUP BY）操作是基于索引列的，那么MySQL也可以利用索引来提高效率。

• 覆盖索引：如果查询的列都包含在索引中（即索引覆盖），那么MySQL可以直接通过索引来获取数据，而无需回表查询。

索引失效的情况

• 查询条件中未使用索引列：如果查询条件中没有使用到任何索引列，那么MySQL将无法使用索引进行优化查询。

• 数据类型不匹配：如果查询中使用了索引列，但数据类型与索引列的数据类型不匹配（如将字符串类型的列与数值类型进行比较），那么MySQL将无法使用索引。

• 在索引列上进行计算或函数操作：在查询条件中对索引列进行函数操作或计算（如SELECT * FROM table WHERE YEAR(date_column) = 2023），会导致索引失效。

• 索引列的顺序不正确：对于复合索引（包含多个列的索引），如果查询条件中的列的顺序与复合索引的列的顺序不一致，那么MySQL可能无法充分利用索引。

• 使用LIKE进行模糊匹配：如果LIKE查询以通配符（%或_）开头，那么索引可能会失效。因为MySQL无法利用索引来快速定位到数据的起始位置。

• OR条件中的非索引列：如果OR条件中的某个字段不是索引列，那么MySQL可能无法利用索引来优化查询。

• 索引列上的范围查询：虽然范围查询（如<、>、BETWEEN等）可以使用索引，但如果范围查询后还有其他非索引列的查询条件，那么这些条件可能无法利用索引。

• 数据更新频繁：在数据更新频繁的情况下，索引的维护成本会增加，可能导致索引的查询性能下降。

• 索引过多或过少：索引过多会占用额外的存储空间，并在数据更新时增加维护成本；索引过少则可能无法充分利用索引来提高查询性能。

EXPLAIN

在MySQL中，EXPLAIN语句是一个强大的工具，用于获取MySQL如何执行SELECT语句的详细信息。它可以帮助开发人员和分析师理解查询的执行计划，包括如何连接表、是否使用了索引、是否进行了全表扫描等。这对于优化查询性能至关重要。

EXPLAIN的输出包含多个列，这些列提供了关于查询执行计划的详细信息。以下是一些常见的列及其含义：

• id: SELECT的标识符。如果你的查询包含子查询，MySQL会为每个子查询分配一个唯一的ID。

• select_type: SELECT的类型，如SIMPLE（简单SELECT，不使用UNION或子查询等）、PRIMARY（查询中最外层的SELECT）、UNION等。

  • SIMPLE：简单 SELECT，不需要使用 UNION 操作或子查询。
  • PRIMARY：如果查询包含子查询，最外层的 SELECT 被标记为 PRIMARY。
  • UNION：UNION 操作中第二个或后面的 SELECT 语句。
  • SUBQUERY：子查询中的第一个 SELECT。
  • DERIVED：派生表的 SELECT 子查询。

• table: 输出行所引用的表。

• partitions: 匹配的分区。

• type: 连接类型，如ALL（全表扫描）、index（索引扫描）、range（索引范围扫描）等。

  以下是链接类型判断性能从高到低的类型：

  • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计。
  • const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
  • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。
  • index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）。
  • ALL：Full Table Scan，遍历全表以找到匹配的行。

• possible_keys: 显示可能应用在这张表上的索引，但这不意味着实际查询中会用到它们。

• key: 实际使用的索引。如果为NULL，则没有使用索引。

• key_len: 使用的索引的长度。在某些情况下，不是索引的全部部分都会被使用，这个值越小，索引越高效。

• ref: 显示索引的哪一列或常数被用于查找值。

• rows: MySQL认为必须检查的用来返回请求数据的行数，这个值越小越好。

• filtered: 表示返回结果的行占开始找到符合表连接条件的行的百分比，百分比越高越好。

• Extra: 包含不适合在其他列中显示但非常重要的额外信息，如是否使用了文件排序（Using filesort）、是否使用了临时表（Using temporary）等。

  • Using filesort：MySQL 无法利用索引完成排序操作。
  • Using index：只使用索引的信息而不需要进一步查表来获取更多的信息。
  • Using temporary：MySQL 需要使用临时表来存储结果集，常用于分组和排序。
  • Impossible where：where子句会导致没有符合条件的行。
  • Distinct：MySQL 发现第一个匹配行后，停止为当前的行组合搜索更多的行。
  • Using where：查询的列未被索引覆盖，筛选条件并不是索引的前导列。