cockroach官方文档翻译--4.1 长query

4 管理

4.1 长query

**查询

查询正在执行的语句

root@:26260/bank>SHOW CLUSTER QUERIES;

+----------------------------------+---------+----------+----------------------------------+----------------------+-----------------+------------------+-------------+-----------+

| query_id | node_id | username | start | query | client_address |application_name | distributed | phase |

+----------------------------------+---------+----------+----------------------------------+----------------------+-----------------+------------------+-------------+-----------+

|150d3d83ab265bbc0000000000000001 | 1 | root | 2018-01-2603:11:25.215091+00:00 | SHOW CLUSTER QUERIES | 127.0.0.1:40206 |cockroach | NULL | preparing |

+----------------------------------+---------+----------+----------------------------------+----------------------+-----------------+------------------+-------------+-----------+

(1row)

按条件查询

root@:26260/>select * from [show cluster queries] where start < (now() -interval '3 hour');

+----------+---------+----------+-------+-------+----------------+------------------+-------------+-------+

|query_id | node_id | username | start | query | client_address |application_name | distributed | phase |

+----------+---------+----------+-------+-------+----------------+------------------+-------------+-------+

+----------+---------+----------+-------+-------+----------------+------------------+-------------+-------+

(0rows)

注意：

1.schema改变和backup/restore语句在内部不使用query执行，不再showqueries列出，想要监控，使用showjobs替代

2.查询不需要权限，非root用户只能查询到自己正在实行的活跃sql

3.

distributed：若为true，使用DistSQL引擎，若为false，使用标准的local引擎，若为NULL,query还在准备中，现在还不知道使用哪种引擎

phase：query执行解析，若为preparing，query正在解析和计划，若为executing,语句正在执行。

root@:26260/bank>show jobs;

+----+------+-------------+----------+--------+---------+---------+----------+----------+--------------------+-------+----------------+

|id | type | description | username | status | created | started |finished | modified | fraction_completed | error | coordinator_id |

+----+------+-------------+----------+--------+---------+---------+----------+----------+--------------------+-------+----------------+

+----+------+-------------+----------+--------+---------+---------+----------+----------+--------------------+-------+----------------+

(0rows)

Time:9.015767ms

kill长query

cancel query ‘<query_id>’

**查询执行计划

root@:26260/bank>explain select * from accounts;

+-------+------+-------+------------------+

|Level | Type | Field | Description |

+-------+------+-------+------------------+

|      0 |  scan |          |                                |

|      0 |           | table | accounts@primary |

|      0 |           | spans | ALL                       |

+-------+------+-------+------------------+

(3rows)

Time:48.298276ms

查看包含sql语句

root@:26260/bank>EXPLAIN (exprs) SELECT * FROM accounts where balance > 4000 ;

+-------+------+--------+------------------+

|Level | Type | Field | Description |

+-------+------+--------+------------------+

|      0 | scan |          |                    |

|      0 |          | table | accounts@primary |

|      0 |          | spans | ALL                 |

|      0 |          | filter | balance > 4000 |

+-------+------+--------+------------------+

(4rows)

root@:26260/bank>EXPLAIN (metadata) SELECT * FROM accounts where balance > 4000 ;

+-------+------+-------+------------------+---------------+----------+

|Level | Type | Field | Description | Columns | Ordering |

+-------+------+-------+------------------+---------------+----------+

|      0 | scan |            |                   | (id, balance) |           |

|      0 |          | table   | accounts@primary |        |            |

|      0 |          | spans |     ALL        |                     |            |

+-------+------+-------+------------------+---------------+----------+

Time:26.470243ms

metadata包含使用的列，也用于查看排序

root@:26260/bank>EXPLAIN (metadata) SELECT * FROM accounts where balance > 4000order by balance;

+-------+------+-------+------------------+---------------+----------+

|Level | Type | Field | Description | Columns | Ordering |

+-------+------+-------+------------------+---------------+----------+

|      0 |  sort   |           |                   | (id, balance) | +balance |

|      0 |           |  order | +balance   |                      |                 |

|      1 | scan  |            |                   | (id, balance) |                 |

|      1 |           |   table | accounts@primary |         |                 |

|      1 |           | spans |      ALL       |                      |                 |

+-------+------+-------+------------------+---------------+----------+

(5rows)

Time:2.116552ms

root@:26260/bank>EXPLAIN (metadata) SELECT * FROM accounts where balance > 4000order by balance desc;

+-------+------+-------+------------------+---------------+----------+

|Level | Type | Field | Description | Columns | Ordering |

+-------+------+-------+------------------+---------------+----------+

|      0 |   sort   |           |                   | (id, balance) | -balance |

|      0 |            | order |    -balance  |                      |                |

|      1 | scan   |           |                   | (id, balance) |                 |

|      1 |           |   table | accounts@primary |         |                 |

|      1 |           | spans |      ALL      |                       |                 |

+-------+------+-------+------------------+---------------+----------+

(5rows)

Time:24.339574ms

qualify显示使用的表名

root@:26260/bank>explain (exprs,qualify) SELECT a.balance,b.balance FROM accounts asa,accounts as b;

+-------+--------+----------+------------------+

|Level | Type | Field | Description |

+-------+--------+----------+------------------+

| 0 | render | | |

| 0 | | render 0 | a.balance |

| 0 | | render 1 | b.balance |

| 1 | join | | |

| 1 | | type | cross |

| 2 | scan | | |

| 2 | | table | accounts@primary |

| 2 | | spans | ALL |

| 2 | scan | | |

| 2 | | table | accounts@primary |

| 2 | | spans | ALL |

+-------+--------+----------+------------------+

(11rows)

Time:5.376645ms

root@:26260/bank>explain (exprs) SELECT a.balance,b.balance FROM accounts asa,accounts as b;

+-------+--------+----------+------------------+

|Level | Type | Field | Description |

+-------+--------+----------+------------------+

| 0 | render | | |

| 0 | | render 0 | balance |

| 0 | | render 1 | balance |

| 1 | join | | |

| 1 | | type | cross |

| 2 | scan | | |

| 2 | | table | accounts@primary |

| 2 | | spans | ALL |

| 2 | scan | | |

| 2 | | table | accounts@primary |

| 2 | | spans | ALL |

+-------+--------+----------+------------------+

(11rows)

Time:5.531411ms

VERBOSE参数

verbose包含exprs，metadata,和qualify参数。

root@:26260/bank>explain (verbose) select * from accounts as a join accounts using(id) where a.balance > 4000 order by a.balance desc;

+-------+--------+----------------+-----------------------+----------------------------------------------------------------+----------+

|Level | Type | Field | Description | Columns | Ordering |

+-------+--------+----------------+-----------------------+----------------------------------------------------------------+----------+

| 0 | sort | | | (id,balance, balance) | -balance|

| 0 | | order | -balance | | |

| 1 | render | | | (id,balance, balance) | |

| 1 | | render 0 | id | | |

| 1 | | render 1 | a.balance | | |

| 1 | | render 2 | bank.accounts.balance | | |

| 2 | join | | | (id,id[hidden,omitted], balance, id[hidden,omitted], balance) | |

| 2 | | type | inner | | |

| 2 | | equality | (id) = (id) | | |

| 2 | | mergeJoinOrder | +"(id=id)" | | |

| 3 | scan | | | (id,balance) | +id,key |

| 3 | | table | accounts@primary | | |

| 3 | | spans | ALL | | |

| 3 | | filter | balance > 4000 | | |

| 3 | scan | | | (id,balance) | +id,key |

| 3 | | table | accounts@primary | | |

| 3 | | spans | ALL | | |

+-------+--------+----------------+-----------------------+----------------------------------------------------------------+----------+

(17rows)

Time:26.155144ms