Knightdreams'Blog

Appearance

如何进行SQL调优 ​
  1. 了解业务需求，确定SQL执行的目标和期望结果；
  2. 分析SQL的执行计划，找出慢查询和高频查询；
  3. 根据执行计划，优化SQL语句的写法，比如减少子查询、避免使用全表扫描、合理使用索引等；
  4. 检查表结构是否合理，比如是否需要增加或删除索引、调整字段类型等；
  5. 针对数据库参数进行调整，比如修改缓存大小、调整线程池等；
  6. 进行测试和验证，确保优化后的 SQL 没有产生错误或不符合预期的结果；
  7. 定期监控和维护优化后的 SQL，保证其持续有效性。

在进行 SQL 调优时，需要具备一定的数据库基础知识和经验，并且需要使用一些常用的工具，如 Explain、Slow Query Log 等来辅助分析和优化 SQL 语句。另外，SQL 调优是一个持续的过程，需要不断地观察和改进，以保证数据库系统的高效运行。

Explain ​

Explain 是一个数据库工具，用于分析 SQL 查询语句的执行计划。通过 Explain，可以查看查询语句在执行时的详细信息，包括表的访问方式、索引使用情况、连接方式等，以及每个操作的耗时和数据处理行数等指标。

Explain 的输出结果通常是一张表格，其中包含了查询语句的执行计划。具体的字段和含义可能会因数据库系统而异，但通常会包括以下关键信息：

  1. id

    select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，该字段通常与table字段搭配来分析。

    id相同，执行顺序从上到下。

    id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高

  2. select_type

    查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。

    SIMPLE: 简单的select查询，查询中不包含子查询或union查询

    PRIMARY: 查询中若包含任何复杂的子部分，最外层查询也就是最后加载的为PRIMARY

    SUBQUERY: 在select或where列表中包含了子查询，就被标记为SUBQUERY

    DERIVED: 在from列表中包含的子查询会被标记为DERIVED（衍生），MySQL会递归执行这些子查询，将结果放在临时表中

    UNION RESULT: 从union表获取结果的select

  3. table

    显示sql操作属于哪个表的

  4. partitions

    官方定义为The matching partitions（匹配的分区），该字段应该是看table所在的分区吧（不晓得理解错误没）。值为NULL表示表未被分区。

  5. type

    表示查询所使用的访问类型，type的值主要有八种，该值表示查询的sql语句好坏，从最好到最差依次为：system>const>eq_ref>ref>range>index>ALL。

    一般来说，需保证查询至少达到range级别，最好能达到ref。

  6. possible_keys和key、key_len

    possible_keys：显示可能应用在表中的索引，可能一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

    key：实际中使用的索引，如为NULL，则表示未使用索引。若查询中使用了覆盖索引，则该索引和查询的select字段重叠。

    key_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

    注：在使用索引查询时，当条件越精确，key_len的长度可能会越长，所以在不影响结果的情况下，key_len的值越短越好。

  7. ref

    显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。

  8. rows

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

  9. filtered

    百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。

  10. Extra

    显示十分重要的额外信息。其取值有以下几个：

    Using filesort：表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。出现Using filesort就非常危险了，在数据量非常大的时候几乎“九死一生”。出现Using filesort尽快优化sql语句。

    Using temporary：使用了临时表保存中间结果，常见于排序order by和分组查询group by。非常危险，“十死无生”，急需优化。

    Using index：表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。如果同时出现了Using where，表明索引被用来执行索引键值的查找

    使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。也就说出现后面两项表明sql语句是非常烂的，急需优化！！！

Slow Query Log ​

慢查询日志（Slow Query Log）是数据库系统中的一种功能，用于记录执行时间超过特定阈值的查询语句。它可以帮助开发人员和数据库管理员识别潜在的性能问题，并进行性能优化和调优。

慢查询日志通常会记录以下信息：

  1. 查询语句：记录执行时间超过阈值的查询语句的具体内容。
  2. 执行时间：记录查询语句的执行时间，以便确定哪些查询需要进行性能优化。
  3. 锁等待：如果查询涉及到锁定资源或者存在锁等待，慢查询日志可能还会记录相关的锁等待信息。
  4. 扫描行数：记录查询语句扫描的行数，以便评估查询效率。

通过分析慢查询日志，我们可以识别出执行时间较长的查询语句，并了解其执行计划、索引使用情况、可能存在的性能瓶颈等。这有助于我们找出导致性能问题的具体原因，并采取相应的优化措施。

要启用慢查询日志，通常需要对数据库的配置文件进行相应的修改，并设置一个执行时间阈值。一旦慢查询日志被启用，数据库会自动记录执行时间超过阈值的查询语句到指定的日志文件中。

需要注意的是，慢查询日志可能会对数据库的性能产生一定的影响，因为记录查询执行时间需要额外的系统资源。因此，在生产环境中，我们需要谨慎地选择合适的执行时间阈值和日志记录级别，以避免不必要的性能开销。