MySQL高级应用 - 性能分析

IllTamer
78 阅读6分钟

本文已参与「新人创作礼」活动， 一起开启掘金创作之路。

性能分析

MySQL Query Optimizer

  1. Mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的,这部分最耗费时间)
  2. 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQuery Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的Hint信息（如果有)，看显示Hint信息是否可以完全确定该Query 的执行计划。如果没有Hint 或Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MYSQL 常见瓶颈

  • CPU：CPU饱和，一般发生在数据装入内存 / 从磁盘读取数据的时候
  • IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件：top，free，iostat和vmstat来查看系统性能状态

Explain

  • 是什么？

    ​ 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

  • 干什么？

    • 表的读取顺序
    • 数据读取操作的操作类型
    • 哪写索引可以使用
    • 哪写索引实际被使用
    • 表之间的引用
    • 每张表有多少行被优化器查询

  • 怎么用？

    • EXPLAIN + SQL语句

    • 执行计划包含的信息

image-20211022170352250.png

  • 各字段解释

    • id

      SELECT查询的序列号，包含一串数字，表示查询中执行SEKECT子句 / 操作表的顺序

      1. id相同：执行顺序由上至下
      2. id不同：如果是子查询，id序号会递增，id值越大优先级越高，越先被执行。
      3. id相同不同同时存在：id如果相同认为是同一组，顺序执行。所有组中id越大优先级越高。

    • select_type

      • 有哪些？

        IDSELECT_TYPE
        1SIMPLE
        2PRIMARY
        3SUBQUERY
        4DERIVED
        5UNION
        6UNION RESULT

      • 查询的类型

        ​ 主要用于区别普通查询、联合查询、子查询等复杂查询

        1. SIMPLE

          ​ 简单的SELECT查询，查询中不包含子查询和联合查询。

        2. PRIMARY

          ​ 查询中若包含任何复杂的字部份，最外层查询则被标记为PRIMARY

        3. SUBQUERY

          ​ 在SELECT / WHERE列表中包含了子查询

        4. DERIVED

          derived+序号 表示第 i 个id被加载的衍生i号表

          ​ 在FROM列表中包含的子查询被标记为衍生，MySQL会递归执行这些子查询，把结果放在临时表里。

        5. UNION

          ​ 若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED

        6. UNION RESULT

          ​ 从UNION表获取结果的SELECT

    • table

      ​ 显示该行数据是关于哪张表的 (操作的表对象)

    • type

      ​ 访问类型排列，从最好到最差依次是：

      system > const > eq_ref > ref > range > index > ALL

      • system

        表只有一行记录 (等于系统表)，这是const类型的特例。

      • const

        表示通过一次索引就找到了，const用于比较primary key / unique索引，因为只匹配一行数据所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量。

      • eq_ref

        唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 / 唯一索引扫描

      • ref (建议)

        非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

      • range (底线)

        只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引比全表扫描要好。

      • index

        Full Index Scan，index与ALL的区别为index只遍历索引树，这通常比ALL快。虽然ALL和index都是读全表，但index从索引(内存)中读，ALL是从硬盘中读取的。

      • ALL

        Full Table Scan，遍历全表。

    • possible_keys

      显示可能应用在这张表上的索引，一个或多个。查询涉及到的字段上如果存在索引则将被列出，但不一定被查询实际使用。

    • key

      实际使用的索引，如果为NULL则没有使用索引，查询中若使用了覆盖索引[^1]，则该索引仅出现在key索引中。

    • key_len

      表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度 (自然是越小越好)。

      注：此长度为索引字段最大可能的长度，并非实际使用长度。即key_len是根据表定义计算而得，不是通过表内检索得出。

    • ref

      显示哪个索引被使用，哪些列(库.表.列) / 常量(const)被用于查找索引列上的值。

    • rows

      根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数。

    • Extra

      包含不适合在其他列中显示但十分重要的信息。

      1. Using filesort (危)

        说明mysql会对数据使用一个内部的**(执行时新建的)**索引排序，而不是按照表内的索引顺序进行排序、MySQL中无法利用索引完成的排序操作称为“文件排序”。

        诱因可能为 未按多重索引顺序 order by，如idx_col1_col2，但group by col2

      2. Using temporary (危)

        使用了临时表保存中间结果，MySQL在对查询结构排序时使用了临时表。常见于排序order by 和分组查询group by。

      3. Using index

        注意：如果要使用覆盖索引，一定不能使用select *。因为如果将所有字段一起索引会导致索引文件过大查询性能下降。

        表示相应 SELECT 操作使用了覆盖索引[^1]，避免了访问表的数据行，效率不错！如果同时出现Using where，表明索引被用来执行索引键值的查找；如果没有同时出现则表明索引用来读取数据而非执行查找动作。

      4. Using where

        表明使用了where过滤。

      5. Using join buffer

        表示使用了链接缓存。

      6. Impossible where

        where子句的值总是false，不能用来获取任何元素。(如 where sex='男' and sex='女')

      7. Select tables optimized away

        在没有group by子句的情况下，基于索引优化MIN / MAX操作或者对MyISAM储存引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

      8. Distinct

        优化distinct操作，再找到第一个匹配的元素后即停止查找直接收工。

avatar
文章
阅读
粉丝