如何用SQL EXPLAIN识别和调整有问题的查询

作为一名开发人员，你经常面临这样的问题：**为什么我的应用程序速度慢？**你可能已经将数据库确定为瓶颈（即使这个过程也需要一些故障排除），然后你发现自己在问。

• 哪些查询是慢的？
• 我应该对哪些查询进行故障排除？

查询故障排除的过程首先是隔离由你的应用程序发出的违规查询。

最好的做法是在连接到数据库时用标签或名称来标记你的应用程序，使你能够跟踪传入查询的来源。大多数数据库都支持这一功能，你可以利用这一优势来快速过滤和隔离含有问题查询的特定应用工作负载。

在CockroachDB中，当连接到你的数据库时，你可以按照这些说明来设置应用名称。然后你可以使用DB控制台通过这个应用名称进行过滤，并检索出可能遇到性能问题并影响你的应用的一组查询。从CockroachDB 21.1开始，你也可以通过语句类型进行过滤，以确定应用程序中的具体操作。

下一步是确定从你的应用中发出的特定的慢速查询。数据库通常会提供正在运行和之前运行的查询的系统视图，包括运行时的统计数据，以帮助你找到麻烦的查询。

第1a步：如何使用DB控制台来识别缓慢的查询

CockroachDB提供了一个开箱即用的DB控制台，使你能够监控和优化集群性能。你可以使用控制台对特定的慢速查询进行过滤和排序，包括他们在报表页面的运行时间统计。

第1b步：如何用 "慢速查询日志 "识别慢速查询

为了快速欺骗，CockroachDB支持慢速查询日志，通过自定义持续时间阈值的查询将被自动捕获并写入一个单独的视图中。

SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '100ms';

Slow Query Log:
   "Timestamp":1626455175848119000,
   "EventType":"slow_query",
   "Statement":"‹SELECT l_returnflag, l_linestatus, sum(l_quantity) AS sum_qty, sum(l_extendedprice) AS sum_base_price, sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, sum((l_extendedprice * (1 - l_discount)) * (1 + l_tax)) AS sum_charge, avg(l_quantity) AS avg_qty, avg(l_extendedprice) AS avg_price, avg(l_discount) AS avg_disc, count(*) AS count_order FROM \"\".\"\".lineitem WHERE l_shipdate <= ('1998-12-01' - '95 days') GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus›",
   "Tag":"SELECT",
   "User":"root",
   "ApplicationName":"$ cockroach sql",
   "ExecMode":"exec",
   "NumRows":4,
   "Age":7681.835,
   "FullTableScan":true,
   "TxnCounter":8

第1c步：如何根据计划属性来识别慢速查询

最常见的查询计划问题之一是当优化器选择的计划扫描的数据比需要的多，导致查询时间延长。这个问题可以通过查询计划中CockroachDB中的FULL扫描来表示。

你可以使用集群设置来禁止带有FULL扫描的查询，以使其预先失败，并提醒你注意这个问题--这在你开始使用初始基本数据库模式设计的应用时特别有帮助。你也可以在DB控制台中结合你的应用程序名称来过滤具有FULL扫描的查询。这些功能使你能够找到具有次优计划的查询，并开始调整过程。

SET CLUSTER SETTING sql.defaults.disallow_full_table_scans.enabled = 'true';

ERROR: query `SELECT * FROM region` contains a full table/index scan which is explicitly disallowed

第2步：如何用`EXPLAIN'反省查询计划和调整性能

在确定一个有问题的查询后，使用EXPLAIN 。在内省和调整查询时，EXPLAIN 是你的工具库中最关键的命令之一。运行EXPLAIN 将返回优化器选择的逻辑查询计划。在这个例子中，我们对CockroachDB中发现的一个麻烦的查询运行了EXPLAIN 。

                                                  info

--------------------------------------------------------------------------------------------------------

  distribution: full

  vectorized: true


  • sort

  │ estimated row count: 6

  │ order: +l_returnflag,+l_linestatus

  │

  └── • group

      │ estimated row count: 6

      │ group by: l_returnflag, l_linestatus

      │

      └── • render

          │ estimated row count: 5,917,362

          │

          └── • filter

              │ estimated row count: 5,917,362

              │ filter: l_shipdate <= '1998-08-28'

              │

              └── • scan

                    estimated row count: 6,002,293 (100% of the table; stats collected 39 minutes ago)

                    table: lineitem@primary

                    spans: FULL SCAN

(22 rows)


Time: 2ms total (execution 2ms / network 0ms)

在上面的逻辑查询计划中，注意到哪里写着spans: FULL SCAN 。如前所述，查询计划中的FULL SCAN 是次优的，特别是在扫描相对大量的行时。这个计划树是一个典型的指标，表明该查询将从二级索引中受益。二级索引根据索引列复制和排序行，这使你能够有效地访问数据库中的行。二级索引应该被仔细地创建，因为你要付出额外的写惩罚来维护索引，以获得更好的读取性能。你可以在我们的文档中了解更多关于专门针对CockroachDB的二级索引。

如果你使用的是CockroachDB，DB控制台也会以图形的方式浮现出查询计划，帮助你快速检测FULL扫描。

第3步：如何用 "EXPLAIN ANALYZE "找到性能瓶颈

为了进一步了解你的查询将如何执行，大多数数据库支持运行EXPLAIN 与查询执行。这使你能够执行查询并捕获实际的物理计划，包括运行时统计。然后你可以使用运行时统计来了解瓶颈所在，这对分布式SQL数据库特别有帮助。

这是在CockroachDB中使用EXPLAIN ANALYZE ，在21.1中，CockroachDB发布了对EXPLAIN ANALYZE 。你现在可以在SQL CLI中直接看到物理计划，了解查询的执行是如何分布在集群中的，包括运行时的统计数据，如争用、每个节点的持续时间等。

​​                                           info
--------------------------------------------------------------------------------------------
  planning time: 353µs
  execution time: 3ms
  distribution: local
  vectorized: true
  rows read from KV: 52 (5.8 KiB)
  cumulative time spent in KV: 2ms
  maximum memory usage: 60 KiB
  network usage: 0 B (0 messages)
  cluster regions: us-east1

  • update
  │ cluster nodes: n1
  │ cluster regions: us-east1
  │ actual row count: 1
  │ table: users
  │ set: name
  │ auto commit
  │
  └── • render
      │ cluster nodes: n1
      │ cluster regions: us-east1
      │ actual row count: 1
      │ estimated row count: 0
      │
      └── • filter
          │ cluster nodes: n1
          │ cluster regions: us-east1
          │ actual row count: 1
          │ estimated row count: 0
          │ filter: id = '32a962b7-8440-4b31-97cd-a7d7757d6eac'
          │
          └── • scan
                cluster nodes: n1
                cluster regions: us-east1
                actual row count: 52
                KV time: 2ms
                KV contention time: 0µs
                KV rows read: 52
                KV bytes read: 5.8 KiB
                estimated row count: 50 (100% of the table; stats collected 3 minutes ago)
                table: users@primary
                spans: FULL SCAN
(42 rows)

Time: 4ms total (execution 4ms / network 0ms)

在上面的物理查询计划中，你可以看到统计数据是最新的。争用时间等信息浮出水面，以帮助你解决由于事务争用造成的性能缓慢问题。在执行过程中，我们还看到一个FULL SCAN，返回52条记录，这有可能通过一个二级索引来减少。

通过 "EXPLAIN "检查查询计划，可以为设计和更新你的模式提供许多其他见解。CockroachDB提供了一个全面的指南，在设计数据库模式时使用`EXPLAIN'。

关于CockroachDB查询性能的一般信息，请注册我们的Cockroach大学课程--开发人员的查询性能。如果你有问题或反馈，我们鼓励你加入我们CockroachDB社区Slack的#cockroach-university频道。