mysql优化sql：EXPLAIN各语法解释：

当我们谈论数据库性能优化时，EXPLAIN 是一个非常有用的工具，用于分析查询语句的执行计划。它能帮助我们理解数据库是如何执行查询的，以及是否能有效利用索引和其他优化策略。下面是一些关键的概念和术语，帮助你理解如何分析 EXPLAIN 的输出以优化查询性能：

1. 执行计划基础

执行 EXPLAIN 查询的基本语法是：

EXPLAIN SELECT ......  ;

这会返回一个描述查询执行计划的结果集。每一行都代表执行计划中的一步操作，通常是从最内层到最外层。

2. 关键字段解释

在分析 EXPLAIN 输出时，需要注意以下关键字段：

• id:  操作的唯一标识符，通常从 1 开始递增。多个操作可能共享相同的 id，表示它们在同一层级。
  •   id不同，在所有组中,id值越大,优先级越高,越先执行 。
  •   id相同，都是1，从上到下顺序执行。
• select_type:  操作的类型，例如 SIMPLE、PRIMARY、SUBQUERY 等。不同类型反映了不同的查询结构和优化方式。
  •   查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询结果值。
  •   SIMPLE： 简单select查询,查询中不包含子查询或者UNION。
  •   PRIMARY： 查询中若包含任何复杂的子查询,最外层查询则被标记为primary
  •   SUBQUERY： 在select或where中包含了子查询。
  •   DERIVED： 在from列表中包含的子查询被标记为derived(衍生)，把结果放在临时表。
  •   UNION： 若第二个select出现的union之后,则被标记为union ，若union包含在from子句的子查询中,外层select将被标记为deriver
  •   UNION RESULT： 从union表获取结果select,两个UNION合并的结果集在最后。
• table:  操作涉及的表。
• type:  访问类型，表示如何访问表，常见的类型有 ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。
  •     结果值:(最好到最差) system > const > eq_ref > ref > range > index > ALL
  •     system 表中有一行记录(系统表) 这是const类型的特例,平时不会出现。
  •    const 表示通过索引一次就找到了，const用于比较primary 或者 unique索引。
  •     eq_ref  唯一性索引扫描 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或唯一索引扫描
  •    ref 非唯一性索引扫描。
  •    range 只检索给定范围的行,使用一个索引来选择行 一般就是在你的where语句中出现between<>\ in等查询。
  •    index 与 All 区别为 index 类型只遍历索引树,通常比 All 要快,因为索引文件通常比数据文件要小，all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取。
  •  ALL 将全表进行扫描,从硬盘当中读取数据,如果出现了All 切数据量非常大, 一定要去做优化。
• possible_keys:  可能使用的索引列表。
• key:  实际使用的索引。
• key_len:  索引字段的长度。
• ref:  显示索引如何与表中的列值匹配。
  •     索引是否被引入到, 到底引用到了哪几个索引。
• rows:  预计要检索/扫描的行数。
  •   根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,每长表有多少行被优化器查询过。
• filtered：满足查询的记录数量的比例，注意是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不十分准确。 
• Extra:  额外信息，如 Using where（表示使用了 WHERE 条件过滤结果）、Using index（表示查询使用了覆盖索引）等。
  •   Using Filesort 和 Using Temporary：说明没有使用到索引。
  •        impossible where：说明条件永远不成立。
  •        use index：表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好
  •        using where：表明使用了where过滤。
  •        using join buffer：使用了连接缓存。

3. 分析常见问题和优化建议

在分析 EXPLAIN 输出时，可以根据以下常见问题和优化建议进行评估：

• 全表扫描（ALL）：如果 type 列显示为 ALL，表示查询执行全表扫描，这通常是性能问题的原因。可以考虑优化查询或添加合适的索引来避免全表扫描。

• 索引未使用（NULL key）：如果 key 列为 NULL，表示查询没有使用索引，可能需要调整查询或者添加新的索引。

• 索引选择不当：type 列显示 range 或 ref 时，可能表示索引选择不当，考虑是否需要调整索引以提高效率。

• 额外信息（Extra）：Extra 列提供了额外的执行信息，如 Using where、Using index、Using temporary 等，这些信息可以帮助理解查询的具体执行情况。

4. 注意优化常见的不走索引等慢sql：

•   -- 索引的最左匹配原则 * ： 指在使用复合索引（Composite Index）时，索引的多个列按顺序组成了索引键。当你查询时，可以利用索引中的最左边的列开始匹配，并且是连续的。而不能跳过。否则跳过中间一个，索引不生效。
    -- 范围条件右边的索引失效： 通常指的是当在查询中使用了索引，但索引列不在查询条件的最左侧，或者在范围条件右边的情况下，数据库优化器无法有效利用索引。
    -- 不再索引列上做任何操作 * ： 尽量避免对索引列进行任何形式的函数操作、类型转换或其他计算。如 UPPER(column)、DATE_FORMAT(column)等。这些函数可能会导致索引失效，因为数据库无法在索引上直接应用函数。
    -- 使用不等于(!=或者<>)索引失效 *
    -- is not null 无法使用索引 * ：        如果表中大多数行都不为 NULL，数据库优化器可能会认为使用索引并不会提高性能。  
•        -- 使用or连接索引失效 * ：
    -- like以通配符开头(%name)索引失效 * :

1. 1.  前缀匹配：如果可以将查询改为前缀匹配（例如 LIKE '鲁%'），索引就可以被使用。
   2.  对于文本搜索，考虑使用 MySQL 的全文索引。全文索引在处理文本匹配时更加高效：

      CREATE FULLTEXT INDEX ft_idx_name ON employee(name);
      SELECT * FROM employee WHERE MATCH(name) AGAINST('鲁' IN BOOLEAN MODE);

   3.  反向索引：如果确实需要后缀匹配，可以考虑创建一个存储反向字符串的列，并在该列上创建索引。