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