MySQL

MySQL-08-优化查询性能

优化查询性能

目标：

  • 识别适用于优化的查询
  • 检验查询索引耗用
  • 创建查询以提高服务性能
  • 维护索引统计信息
  • 使用MySQL查询分析器

识别慢查询

  • 识别服务端执行的查询
    • 通过通用查询日志和慢查询日志
  • 识别耗时长的查询
    • 使用慢查询日志
    • 优先处理非异常查询或不常执行的查询之外的
  • 查找执行频率高的查询
    • 定期执行SHOW PROCESSLIST命令，或查询 Performance Schema 的threads表，查看当前正在执行的语句及其持续时间，并识别新出现的模式。
    • 使用sys.statement_analysis查看标准化语句及其聚合统计信息。
    • 将慢查询日志的阈值设置得较低，以记录大多数语句。

选择待优化的查询

  • 不要假定只需要优化最慢的查询语句
    • 尽管这样的查询执行已经相对较快，通过优化频率最高的查询语句来提升系统的整体性能
      • 优化频繁执行的查询（即使只是小幅优化）也能释放资源、减少锁定，并提高服务器的响应时间和吞吐量。
      • 而优化不常执行的查询，整体收益会小得多
  • 考虑以下示例
    • 单次优化幅度看似微小：查询耗时从 20 毫秒减少到 15 毫秒，单次仅节省 5 毫秒。
    • 但累计收益巨大：假设每分钟执行 5000 次，那么每分钟累计节省的时间为 5000×5 毫秒 = 25000 毫秒（25 秒），每小时累计节省 1500 秒（25 分钟），每天累计节省 600 分钟（10 小时）。这意味着服务器每天可额外释放 10 小时的资源用于处理其他任务，对整体吞吐量和响应速度的提升非常明显。

如果服务器在一个每分钟执行数千次的查询上多花费 5 毫秒，那么这可能会转化为该服务器上累计 15 到 20 秒的语句执行延迟。对频繁执行的查询进行微小优化，可能会带来更显著的整体性能提升。

EXPLAIN语句

使用 EXPLAIN语句来查看优化器选择的索引

  • EXPLAIN命令的核心功能是生成查询执行计划，展示优化器打算如何执行给定的 SQL 语句，其中包含 MySQL 服务器优化的相关信息。
  • 该命令适用于多种 SQL 语句，包括SELECT、INSERT、REPLACE、UPDATE和DELETE。
  • 使用EXPLAIN时，不会从数据集中返回任何数据，也不会对语句中的数据进行任何修改（仅用于分析执行计划，不实际执行操作）。
  • 优化器选择最佳操作的依据包括：查询本身的内容、查询中涉及的表的结构，以及所有相关的索引。

EXPLAIN:example

（上图输出存在一个警告，当使用 EXPLAIN去查看 SELECT语句的执行计划时，它会产生一个 note级别的事件用来说明查询重写和优化动作，当 sql_notes系统变量设置为 1 (默认值) 时note级别的事件被视作警告）

EXPLAIN语句输出详解：

  • id

    • 标识每个 SELECT (或子查询/DML语句)的执行顺序
    • id值相同表示同一执行层级（从上到下顺序执行），值越大优先级越高

  • select_type

    • 核心作用：描述查询的复杂度类型。
    • 常见值
      • SIMPLE：简单查询（不含UNION/子查询）
      • PRIMARY：最外层查询（含子查询时出现）。
      • UNION：UNION中第二个及之后的查询。
      • SUBQUERY：子查询中的第一个SELECT。
      • INSERT/REPLACE/UPDATE/DELETE：DML语句的查询类型标识。

  • table

    • 作用：显示当前行数据访问的表名或子查询别名（如<subquery2>）。

  • partitions

    • 作用：查询匹配的分区名（未分区时为NULL）。
    • 示例：若按月份分区，值可能为p_2023_jan。

  • type（关键性能指标）

    • 定义：
      • 表访问方式（性能从优到劣排序）：
      • system > const > eq_ref > ref > range > index > ALL
    • 说明：
      • system：表只有一行数据(如系统表或衍生表)
      • const：通过主键/唯一索引定位单行（最高效）。
      • eq_ref：多表关联时，主键或唯一索引作为关联条件(每轮匹配仅返回一行)。
      • ref：使用非唯一索引单值查找（可能返回多行）。
      • range：索引范围扫描（如 BETWEEN、IN、>等）。
      • index：全索引扫描（遍历索引树，但比ALL稍快）。
      • ALL：全表扫描（无索引可用，性能最差）。

  • possible_keys

    • 作用：理论上可用的索引（可能为空）。

  • key

    • 作用：实际使用的索引（优化器选择的最优索引）。
    • 注意：可能不在possible_keys中（优化器根据成本选择）。

  • key_len

    • 作用：表示查询中实际使用的索引部分的字节长度（从左到右计算）。

    • 关键点：

      • 复合索引中，若仅使用前两列，则key_len只计算这两列的总字节数。
      • 可判断索引是否被充分利用（例如：varchar(255)+NOT NULL的key_len为255 * 3+2=767字节，UTF-8编码）。

    • 示例：

      1
2

      ALTER TABLE users ADD INDEX idx_name_age (name, age);
EXPLAIN SELECT * FROM users WHERE name = 'Alice'; -- 假设name是varchar(255), key_len=767

    • . ref

      • 作用：显示与索引比较的列名或常量（即索引的匹配条件）。

      • 常见值：

        • const：常量值（如WHERE id = 1）。
        • func：函数计算结果（如WHERE UPPER(name) = 'ALICE'）。
        • 列名：多表关联时显示关联列（如users.id）。

      • 示例：

        1
2

        EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id;
-- ref字段可能显示：`test_db.users.id`（数据库名.表名.列名）

    • rows

      • 作用：优化器预估需要扫描的行数（非精确值，基于统计信息）。

      • 重要性：

        • 值越大，查询成本越高。
        • 若实际行数与预估差异大，可能需更新统计信息（ANALYZE TABLE）。

      • 示例：

        1

        EXPLAIN SELECT * FROM logs WHERE date > '2023-01-01'; -- rows=10000（预估扫描1万行）

    • filtered

      • 作用：表示存储引擎层过滤后，剩余数据满足其他条件的百分比（范围：0~100）。

      • 应用场景：

        • 低百分比（如10%）可能提示索引效率低或条件过于宽松。
        • 与type=ALL结合时，可判断全表扫描后的过滤代价。

      • 示例：

        1
2

        EXPLAIN SELECT * FROM products WHERE price > 100 AND category = 'electronics';
-- filtered=30%（价格>100的记录中，30%属于电子产品类别）

    • Extra（关键诊断字段）

      • 作用：

        • 提供优化器或存储引擎的额外执行细节，常见值及含义

          • Using index：仅通过索引即可获取数据（覆盖索引，高效）。

          • Using where：需在存储引擎层后过滤数据（可能未用索引）。

          • Using temporary：使用临时表（排序/分组时常见，需优化）。

          • Using filesort：额外排序操作（可能需添加索引优化）。

重写与优化操作

  • 使用 SHOW WARNINGS 来展示更多关于优化的细节
  • 每条展示的信息提供了该查询关于优化器计划的更多扩展信息
  • 会以伪SQL形式展示查询优化后的操作序列，例如： image-20250710173519950

EXPLAIN 会产生错误代码为 1003 的提示信息（Note-Level Messages）。该错误代码对应的内部符号为 ER_YES，关联的错误名称为 YES。当 sql_notes 设置为 1 时，这些提示信息会被视为警告（Warnings）。虽然它们并非传统意义上的警告或错误，但可以通过客户端接口查看这些额外信息。某些存储引擎（如 NDB）还会在 SHOW WARNINGS 的输出中添加存储引擎特定的优化提示。

EXPLAIN示例：全表扫描

  • possible_keys, key和 key_len 列显示为 NULL
    • 查询不能用任何索引来提升查询性能
  • type列显示的值为 ALL说明是全表扫描
  • rows列显示数字为 299246
    • 这是 InnoDB预估的表的行数（实际行数可能有差异，但代表要扫描的数据量级 ）

EXPLAIN示例：主键

explain-primarykey

  • 操作类型（type）为 const。 const 类型的查询会将字面量（常量）值与主键或唯一键进行匹配。
  • 所选索引在 key 列中显示为 PRIMARY，长度为 4 字节。 该索引会与一个引用的常量值进行等值比较，由 ref: const 体现。 将主键与指定常量值比较，最多只会产生一行结果。
  • 此外，优化器估计只需检查一行数据，这由 rows 列中的值体现。

EXPLAIN示例：不唯一索引

image-20250711112945293

  • 操作类型（type）为 ref
    • 将列值与一个引用值（常量）进行比较。
    • 把引用值与非唯一列进行匹配。
    • 可能匹配到多条记录。
  • 优化器估算匹配的行数为 2 行。
    • 实际数值取决于数据情况。
  • “Extra” 中的 “Using index” 表示：查询无需额外回表去读取实际行的数据（即利用了覆盖索引 ）

EXPLAIN和复杂查询

  • 当分析一个复杂查询语句时，EXPLAIN产生多个输出行
    • 复杂查询语句博爱错子查询 UNION子句或者 JOIN子句
  • 每行使用一个 id来唯一标识与它相关的语句
    • 如果一个 SELECT语句含有的一个子查询或者 UNION子句 中包含了 SELECT语句，将会得到两行、每行有不同的id
      • 每行相关的表操作在独立的语句中
    • 对于连接两个表的单个 SELECT语句，会得到有着相同 id的两行
      • 两行都是在相同语句下相关的表操作

EXPLAIN示例：简单连接

image-20250711113045394

当在一个查询中连接多个表时，优化器必须对每个表进行一次操作

  • 当面的连接有一个 WHERE 子句，它引用了一个未索引的字段。
  • 第一个操作是对 titles 表的 emp_no 索引中的所有行进行索引扫描。
    • 它读取所有索引条目，并根据 WHERE 子句针对主键值对其进行过滤。InnoDB 表中的所有二级索引也包含主键值。・
  • 第二个操作引用第一个操作：
    • 第二个操作的类型是 eq_ref，表明它将每行与比较值进行相等匹配。
    • 所涉及的值是限定列名 employees.titles.emp_no，这表明要比较的值不是语句中提供的常量，而是来自另一个列

EXPLAIN 格式

  • TRADITIONAL：（默认）
    • 以默认的表格格式呈现输出
  • JSON：
    • 以 JSON 格式呈现输出
    • 包含估算的成本和行数
  • TREE：
    • 以树形格式呈现输出
    • 包含估算的成本和行数
    • 自 MySQL 8.0.16 起可用

image-20250711134650878

EXPLAIN的JSON格式：

image-20250711135023962

EXPLAIN ANALYZE

  • 自 MySQL 8.0.18 版本起可用。 它允许你为查询生成执行计划、监测查询执行过程，并执行查询但不返回查询结果。
  • 输出类似于 EXPLAIN FORMAT=TREE，但包含额外的实际统计信息：
    • 返回首行的时间（以毫秒为单位）
    • 返回所有行的时间（以毫秒为单位）
    • 迭代器返回的行数
    • 循环次数

哈希连接优化（Hash Join Optimization）

  • 自 MySQL 8.0.18 版本起可用
  • 适用于满足以下条件的查询：每个连接都有等值连接条件，且未使用索引
  • 在上述场景中，比块嵌套循环算法更快
  • 在第一个表上构建哈希表，然后扫描第二个表中的每一行，利用哈希表查找匹配的行
  • 仅可通过 EXPLAIN FORMAT=TREE 和 EXPLAIN ANALYZE 查看相关执行信息

image-20250711135316626

索引的使用

索引类型

  • 非唯一（Nonunique）：值在索引中可以多次出现。—— 这是默认的索引类型。
  • 唯一（Unique）：值必须唯一（或为 NULL ）。
  • 主键（Primary key）：值是唯一的，且不能包含 NULL 。—— 每个表最多有一个主键。
  • 全文（Fulltext）：值是字符串数据，且该索引支持全文搜索。
  • 空间（Spatial）：值是空间数据类型，如 GEOMETRY、POINT 或 POLYGON 。
  • 函数式（Functional）：值是基于表中值的表达式或函数的结果

上述索引类型在 InnoDB中时支持使用的，其他存储引擎可能不支持

创建索引来提升查询性能

  • 能够高效的获取数据行
    • 在索引字段中搜索某个值的查询可以立即找到包含该值的行。
      • 一种高效的查找操作
    • 在非索引字段中搜索某个值的查询必须读取所有行才能找到包含该值的行。
      • 一种低效的扫描操作
  • 支持以下操作：
    • 直接值匹配
    • 存在性检查
    • 范围扫描

在已存在的表上创建索引

image-20250711141341868

注意： ALTER TABLE操作可能会锁表（尤其是达标），另其他数据库的函数式索引语法可能不尽相同

删除索引

image-20250711141838808

展示索引源数据

  • SHOW CREATE TABLE tablename 语句：
    • 结果会显示表中的所有索引，包括索引名称、索引类型以及被索引的列。
  • SHOW INDEXES FROM tablename 语句：
    • 它会返回表的索引信息，包括一些索引统计数据。
    • 通过查询 INFORMATION_SCHEMA 的 STATISTICS 视图，或者使用带 -k 选项的 mysqlshow 客户端程序，也可以获得类似信息 。

不可见索引（Invisible Indexes）

  • 允许你将索引对优化器 “隐藏” 起来

  • 测试移除索引对查询性能的影响

    • 无需执行删除索引这样具有破坏性的操作
    • 若发现索引仍需使用，可避免重新创建索引的高昂开销

  • 当通过数据操作语言（DML）语句修改数据时，服务器仍会更新这些索引

  • 不能应用于主键

  • 应在CREATE TABLE、ALTER TABLE

    或CREATE INDEX语句中标记为INVISIBLE

    • 可使用 VISIBLE 关键字 “取消隐藏”

索引统计信息

维护 InnoDB索引统计信息

  • MySQL 的优化器使用索引键分布统计信息来决定：
    • 在查询中，MySQL 对特定表使用哪些索引
    • 当对非常量进行连接操作时的连接顺序
  • 更新索引统计信息：
    • 当表中 10% 的行发生变化后，自动更新
    • 使用 ANALYZE TABLE 语句手动更新

自动更新索引统计信息

  • InnoDB持久化存储统计信息
    • 通过 innodb_stats_persistent变量启用
    • 每个表通过表选项 STATS_PERSISTENT启用
    • 存储于 mysql.innodb_index_stats表中
  • 统计信息自动更新
    • 默认采样 20 个数据页
      • 可通过 innodb_stats_persistent_sample_pages 变量修改
    • 当 10% 的行发生变化后，由 innodb_stats_auto_recalc 变量默认启用更新
    • 可选地，在执行 SHOW TABLE STATUS 等元数据语句后，或查询 INFORMATION_SCHEMA.TABLES 时，由 innodb_stats_on_metadata 变量启用更新

如果为一个或多个表禁用持久化统计信息收集，InnoDB 会在内部以临时形式（重启后会丢失）存储这些表的统计信息，并使用 innodb_stats_transient_sample_pages 变量的值来决定读取多少个数据页。

使用 ANALYZE TABLE

  • 分析并存储表的键分布统计信息

  • 用于优化查询执行计划的选择

  • 适用于 InnoDB、NDB 和 MyISAM 存储引擎的表

  • 支持分区表

  • ANALYZE TABLE选项：

    • NO_WRITE_TO_BINLOG 或 LOCAL：禁止将操作写入二进制日志

  • ANALYZE TABLE 结果示例 image-20250711144057577

重建索引

  • 使用FORCE选项：

    • 即使语句未改变表结构，也强制重建索引
    • 重新组织数据，使数据在数据页上分布更均匀
    • 回收空页占用的文件系统空间

    表经过大量 DELETE 或 UPDATE 后出现性能下降（如查询变慢），可能是索引碎片过多导致，此时用 FORCE 重建可优化性能

  • 使用 OPTIMIZE TABLE重建全文索引：

    • 默认情况下，OPTIMIZE TABLE 会重建整个表
    • innodb_optimize_fulltext_only 选项确保 OPTIMIZE TABLE 仅重建全文索引

    全文索引（如文章内容的索引）因频繁更新导致查询效率下降时，单独重建全文索引比优化整个表更高效，尤其适合大表

注意事项

  • 锁表影响：FORCE 和 OPTIMIZE TABLE 执行时会对表加锁（InnoDB 中可能是短时表锁），期间会阻塞读写操作，建议在低峰期执行。
  • 性能开销：重建索引需扫描和重写数据，大表可能耗时较长，且会增加磁盘 I/O 和 CPU 负载

mysqlcheck 客户端程序

  • 相比执行 SQL 语句，使用它可能更便捷
  • 适用于 InnoDB、MyISAM 和 ARCHIVE 存储引擎的表
  • 支持三个级别的检查：
    • 特定表
    • 特定数据库
    • 所有数据库
  • mysqlcheck的一些维护选项：
    • --analyze：执行 ANALYZE TABLE 操作
    • --check：执行 CHECK TABLE 操作（默认选项）
    • --optimize：执行 OPTIMIZE TABLE 操作

直方图（Histograms）

  • 为优化器提供非索引列内数据分布的额外信息
    • 优化器了解索引列的数据分布，但不了解未构成索引的列的数据分布。
    • 倾斜的数据可能会影响查询性能。
  • 近似估算这些列内的数据分布
  • 使优化器能够做出更合理的决策

image-20250711160851876

MySQL查询分析器

  • 监控在MySQL服务器上执行的SQL语句
    • 查询的类型
    • 执行次数
    • 执行事件
  • 默认通过使用MySQL Enterprise Monitor Agent从性能模式中提取信息
    • 可以配置客户端应用程序，通过 MySQL 代理（MySQL Proxy）和 MySQL 聚合器（MySQL Aggregator）路由数据库请求
    • 可以安装连接器插件（Connector plugin），将这些信息直接发送到 MySQL 企业监控服务管理器（MySQL Enterprise Monitor Service Manager）
  • 对查询进行标准化处理，以便于报告生成
    • 将具有不同字面量值的相似查询合并
  • 允许深入查看每个查询的详细信息

查询响应时间指数（Query Response Time Index，QRTi）

MySQL 查询分析器会为每个查询生成一个查询响应时间指数（QRTi）。

  • 提供一个 “一目了然” 的查询性能指标
  • 由一种算法生成，该算法会考虑每个查询的执行次数和持续时间 image-20250711161848652

查询分析器用户界面

image-20250711161221001

© 2025 Penna_a
共 0 次浏览