mysql 索引的使用

1、创建索引

CREATE INDEX index_name ON table_name(column);

2、查看索引

SHOW INDEX FROM table_name;

3、删除索引

DROP INDEX index_name ON table_name;

4、sql语句执行分析

explain SELECT m.id  FROM oms_order_master m LEFT JOIN oms_order_sub s ON m.id = s.order_master_id  WHERE m.enterprise_id = 60  and m.STATUS = 23     ORDER BY m.id desc  LIMIT 0, 10 ; 

执行计划输出的核心字段及含义：

id：查询子步骤的序号，相同 id 表示同级执行，id 越大优先级越高。

select_type：查询类型（如 SIMPLE 简单查询，SUBQUERY 子查询）。

table：涉及的表名。

partitions： 匹配的分区（若表已分区）。 p0, p1（使用的分区名）

type：数据访问方式（性能从优到差排序）：

1. system/const：通过主键或唯一索引查找。

2. ref：非唯一索引扫描。

3. range：索引范围扫描。

4. index：全索引扫描。

5. ALL：全表扫描（需优化）。

possible_keys：可能使用的索引。

key：实际使用的索引。

key_len： 使用的索引长度（字节数），反映索引中使用的字段数。

ref： 与索引比较的列或常量。

filtered： 查询条件过滤后剩余行的百分比（仅限WHERE子句）。

rows：预估扫描的行数（越少越好）。

Extra：附加信息（如 Using where 表示过滤数据，Using temporary 使用临时表）。

以下是 SQL 执行计划中 Extra 字段的常见信息及其解释（以 MySQL 为主，其他数据库类似）：

一、索引相关

Using index
含义：查询仅通过索引即可完成（无需回表查数据）。

场景：查询的列全部包含在索引中（覆盖索引）。

优化建议：保持现有索引，或扩展索引字段以覆盖更多查询需求。

Using index condition

含义：使用索引条件下推（Index Condition Pushdown, ICP），在存储引擎层过滤数据。

场景：WHERE 条件部分字段可用索引，但需进一步筛选。

优化建议：无需额外优化，这是高效利用索引的表现。

Using where

含义：在存储引擎返回数据后，服务器层（Server）再次过滤数据。

场景：WHERE 条件中的字段未被索引完全覆盖。

优化建议：检查 WHERE 条件字段是否可添加索引。

二、排序与临时表

Using filesort

含义：需在内存或磁盘中对结果排序。

场景：ORDER BY 或 GROUP BY 的字段无索引。

优化建议：为排序字段添加索引，或减少排序数据量（如加 LIMIT）。

Using temporary

含义：使用临时表存储中间结果。

场景：复杂 GROUP BY、DISTINCT 或 UNION 操作。

优化建议：简化查询，或为 GROUP BY 字段添加索引。

三、连接与扫描

Using join buffer

含义：使用连接缓冲区（Join Buffer）加速表连接。

场景：JOIN 时未命中索引，需缓存驱动表数据。

优化建议：为连接字段添加索引，或调大 join_buffer_size 参数。

Full scan on NULL key

含义：子查询中的某个列可能为 NULL，导致全表扫描。

场景：如 col IN (SELECT …) 且 col 可为 NULL。

优化建议：避免 NULL 值，或优化子查询。

四、其他关键信息

Impossible WHERE

含义：WHERE 条件永远为假，未扫描任何数据（如 WHERE 1=0）。

场景：逻辑错误或动态生成的 SQL。

优化建议：检查查询条件逻辑。

Select tables optimized away

含义：查询已被优化，无需访问表（如使用聚合函数访问索引的最小值）。

场景：如 SELECT MIN(id) FROM table，且 id 为主键。

优化建议：无需优化。

Range checked for each record

含义：对每一行数据动态选择索引范围扫描。

场景：JOIN 时索引选择不稳定（如涉及不同列的对比）。

优化建议：统一 WHERE 条件的数据类型或重构查询。

五、需要警惕的负面信号

Using filesort 内存/磁盘排序效率低 → 为排序字段添加索引。
Using temporary 临时表可能拖慢性能 → 优化 GROUP BY/DISTINCT，或增加内存参数。
Using where; Using index 索引覆盖但需过滤 → 检查是否可扩展索引字段。
Using join buffer 未命中索引导致缓存 → 为 JOIN 字段添加索引。
Using where; Backward index scan 数据库正在反向扫描索引（从索引末尾向开头扫描），通常用于优化 ORDER BY DESC 或逆序范围查询。在反向扫描索引后，仍需在服务器层对数据进行过滤（索引未完全覆盖查询条件）。