mysql笔记——索引

索引

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

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

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

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

索引语法

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

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

SQL性能分析

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

SHOW PROFILES;

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

SHOW PROFILE FOR QUERY Query_ID;

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

explain命令分析sql命令。

EXPLAIN 的输出包括以下字段：

1. id
   表示查询中执行步骤的标识符，查询按照 id 值从上到下执行。

   • 单表查询通常只有一个步骤，id 为 1。
   • 多表查询时，id 越大优先级越高，表示优先被执行。

2. select_type
   表示查询的类型，常见值包括：

   • SIMPLE：简单查询，无子查询或联合。
   • PRIMARY：主查询（存在子查询时）。
   • SUBQUERY：子查询。
   • DERIVED：派生表（子查询中的临时表）。
   • UNION：联合查询的第二个及后续部分。

3. table
   查询涉及的表名称。

4. type
   表示表的访问类型，性能由好到差排序为：

   • system > const > eq_ref > ref > range > index > ALL
     • ALL 表示全表扫描，性能最差。
     • 优化目标是尽量使用更高效的访问类型，如 ref 或 range。

5. possible_keys
   查询中可能用到的索引。

6. key
   查询实际使用的索引。如果未使用索引会显示 NULL。

7. key_len
   使用索引的长度，表示查询中使用的索引字段的字节数。

8. ref
   显示索引列的比较条件，例如常量或某个列。

9. rows
   估算需要扫描的行数。行数越多，查询越慢。

10. Extra
    附加信息，描述优化器的其他决策和行为。常见值包括：

    • Using where：需要通过条件过滤数据。
    • Using index：全索引覆盖扫描，无需访问表。
    • Using filesort：需要额外的排序操作，性能可能较差。
    • Using temporary：需要创建临时表，通常发生在 GROUP BY 或 ORDER BY 操作中。

索引的使用规则——最左前缀法则

例如，联合索引（a,b,c）。查询需要从索引的最左列开始，并且不条约索引中的列。如果跳跃了某一列，后面的字段索引会失效。

索引失效的情况

• 在索引列上进行运算
• 字符串未加’'造成隐式类型转换
• 没有按照最左前缀法则使用
• like模糊匹配
• or连接的条件一侧有索引，一侧没有
• mysql自行评估。全表扫瞄速度比走索引块

主键设计原则

• 满足业务要求，尽量降低主键长度
• 顺序插入数据，主键自增
• 避免uuid做主键，或是其他自然主键
• 业务操作中避免主键修改

糊匹配

• or连接的条件一侧有索引，一侧没有
• mysql自行评估。全表扫瞄速度比走索引块

[外链图片转存中…(img-QYOrycU5-1733740027341)]

主键设计原则

• 满足业务要求，尽量降低主键长度
• 顺序插入数据，主键自增
• 避免uuid做主键，或是其他自然主键
• 业务操作中避免主键修改