当前位置：首页 > article >正文

mysql笔记——索引

article 2025/1/8 3:20:57

索引

InnoDB采用了B+树索引结构。

相比于二叉树，层级更少，搜索效率高。

B树中叶子节点和非叶节点都会存储数据，导致段页式存储中一页存储的键值减少，指针也会减少，要同样保存大量数据，只能增加树的高度，导致性能降低。

相比于Hash索引，指出范围匹配和排序。

索引语法

唯一索引，保证索引列中的值都唯一：CREATE UNIQUE INDEX index_name ON table_name (column_name);
全文索引，支持全文搜索：CREATE FULLTEXT INDEX index_name ON table_name (column_name);
组合索引，多条件查询:CREATE INDEX index_name ON table_name (column1, column2);
主键会自动创建索引。

通过查看一个数据库中历史增删改查的频次，可以决定是否需要对其使用索引优化的策略。如果数据库访问以查询为主，就需要进行优化。如果以增删改为主，则不需要。

SQL性能分析

命令查看最近执行的语句的性能信息：

SHOW PROFILES;

使用具体的 Query_ID 查询每个阶段的时间消耗：

SHOW PROFILE FOR QUERY Query_ID;

根据消耗的时间分析和优化sql。

explain命令分析sql命令。

EXPLAIN 的输出包括以下字段：

id
表示查询中执行步骤的标识符，查询按照 id 值从上到下执行。
- 单表查询通常只有一个步骤，id 为 1。
- 多表查询时，id 越大优先级越高，表示优先被执行。
select_type
表示查询的类型，常见值包括：
- SIMPLE：简单查询，无子查询或联合。
- PRIMARY：主查询（存在子查询时）。
- SUBQUERY：子查询。
- DERIVED：派生表（子查询中的临时表）。
- UNION：联合查询的第二个及后续部分。
table
查询涉及的表名称。
type
表示表的访问类型，性能由好到差排序为：
- system > const > eq_ref > ref > range > index > ALL
  - ALL 表示全表扫描，性能最差。
  - 优化目标是尽量使用更高效的访问类型，如 ref 或 range。
possible_keys
查询中可能用到的索引。
key
查询实际使用的索引。如果未使用索引会显示 NULL。
key_len
使用索引的长度，表示查询中使用的索引字段的字节数。
ref
显示索引列的比较条件，例如常量或某个列。
rows
估算需要扫描的行数。行数越多，查询越慢。
Extra
附加信息，描述优化器的其他决策和行为。常见值包括：
- Using where：需要通过条件过滤数据。
- Using index：全索引覆盖扫描，无需访问表。
- Using filesort：需要额外的排序操作，性能可能较差。
- Using temporary：需要创建临时表，通常发生在 GROUP BY 或 ORDER BY 操作中。