SHOW

描述

该语句用于展示数据库对象的信息。

语法

SHOW {
    [EXTENDED] [FULL] TABLES [{FROM | IN} database_name] [like_or_where_clause]
   | {DATABASES | SCHEMAS} [STATUS] [like_or_where_clause]
   | [EXTENDED] [FULL] {COLUMNS | FIELDS} {FROM | IN} rel_name [{FROM | IN} database_name] [like_or_where_clause]
   | TABLE STATUS [{FROM | IN} database_name] [like_or_where_clause]
   | PROCEDURE STATUS [{FROM | IN} database_name] [like_or_where_clause]
   | FUNCTION STATUS [{FROM | IN} database_name] [like_or_where_clause]
   | {GLOBAL | SESSION | LOCAL} VARIABLES [like_or_where_clause]
   | {CHARSET | CHARACTER SET} [like_or_where_clause]
   | TRACE [FORMAT='JSON']
   | COLLATION [like_or_where_clause]
   | PARAMETERS [like_or_where_clause]
   | [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} table_name [{FROM | IN} database_name] WHERE opt_hint_value expr
   | [FULL] PROCESSLIST
   | {GLOBAL | SESSION | LOCAL} STATUS [like_or_where_clause]
   | CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name
   | CREATE TABLE table_name
   | CREATE VIEW view_name
   | CREATE PROCEDURE proc_name
   | CREATE FUNCTION func_name
   | WARNINGS [LIMIT N | LIMIT N,M]
   | ERRORS [LIMIT N | LIMIT N,M]
   | COUNT(*) WARNINGS
   | COUNT(*) ERRORS
   | GRANTS opt_for_grant_user
   | [STORAGE] ENGINES
   | PRIVILEGES
   | RECYCLEBIN
   | SEQUENCE [like_or_where_clause]
   | FUNCTION CODE func_name
   | PROCEDURE CODE proc_name
   | ENGIN engine_name {STATUS | MUTEX}
   | OPEN TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
   | CREATE CATALOG external_catalog_name
   | CATALOGS
};

like_or_where_clause:
    LIKE like_str [ESCAPE] esc_str
  | WHERE expr

opt_for_grant_user:
    FOR CURRENT USER
  | FOR user_name

参数解释

参数	描述
[EXTENDED] [FULL] TABLES {FROM \| IN} database_name	展示 `database_name` 库中所有的表。 `EXTENDED` 关键字用于隐藏的表。由于 seekdb 当前版本不会产生隐藏表，该参数无实际作用。 `FULL` 关键字用于展示表类型。
{DATABASES \| SCHEMAS} [STATUS]	展示所有数据库。`STATUS` 关键字用于展示数据库的读写属性。
[EXTENDED] [FULL] {COLUMNS \| FIELDS} {FROM \| IN} rel_name	展示关系 `rel_name` 的列。 `EXTENDED` 关键字用于展示数据库内部使用的隐藏列的信息。 `FULL` 关键字用于展示列的字符序、权限与注释。
TABLE STATUS [{FROM \| IN} database_name]	展示 `database_name` 库中所有表的详细信息。
PROCEDURE STATUS [{FROM \| IN} database_name]	展示 `database_name` 库中所有存储过程的详细信息。
FUNCTION STATUS [{FROM \| IN} database_name]	展示 `database_name` 库中所有函数的详细信息。
{GLOBAL \| SESSION \| LOCAL} VARIABLES	展示系统变量。
{CHARSET \| CHARACTER SET}	展示支持的字符集。
TRACE [FORMAT='JSON']	展示 SQL 语句的执行情况。可以选择以 JSON 的格式输出。
COLLATION	展示支持的字符序。
PARAMETERS [like_or_where_clause]	展示系统配置项。
[EXTENDED] {INDEX \| INDEXES \| KEYS} {FROM \| IN} rel_name [{FROM \| IN} database_name]	展示关系 `rel_name` 上的索引或键。 `EXTENDED` 关键字用于展示数据库内部使用的隐藏索引的信息。
[FULL] PROCESSLIST	查看进程列表。具体如下： `SHOW PROCESSLIST` 显示简要的进程列表，具体如下： `Id`：进程的 ID，即当前会话的 Client Session ID，该 ID 是会话在客户端中的唯一标识。 `User`：连接的用户名。 `Host`：Client 端 IP 和端口。 `db`：访问的 Database 名称。 `Command`：当前执行的命令类型，取值 `Query`、`Sleep` 等。 `Time`：当前命令执行时间，单位是秒。如果命令发生重试，会清零后重新计算。 `State`：当前会话状态，取值 `SLEEP`、`ACTIVE` 等。 `Info`：展示当前正在执行的命令，长度限制为 100 个字符，超出部分截断。 `SHOW FULL PROCESSLIST`：显示完整的进程列表，包括每个进程的详细信息，具体如下： `Id`：进程的 ID，即当前会话的 Client Session ID，该 ID 是会话在客户端中的唯一标识。 `User`：连接的用户名。 `Tenant`：连接的租户。 `Host`：Client 端 IP 和端口。 `db`：访问的 Database 名称。 `Command`：当前执行的命令类型，取值 `Query`、`Sleep` 等。 `Time`：当前命令执行时间，单位是秒。如果命令发生重试，会清零后重新计算。 `State`：当前会话状态，取值 `SLEEP`、`ACTIVE` 等。 `Info`：展示当前正在执行的命令。 `Ip`：服务器 IP 地址。 `Port`：SQL 端口号。
{GLOBAL \| SESSION \| LOCAL} STATUS	展示会话的状态。
CREATE {DATABASE \| SCHEMA} [IF NOT EXISTS] database_name	查看创建数据库的语句。`IF NOT EXISTS` 关键字用于在创建语句中添加 `IF NOT EXISTS`。
CREATE TABLE table_name	查看创建表的语句。
CREATE VIEW view_name	查看创建视图的语句。
CREATE PROCEDURE proc_name	查看创建存储过程的语句。
CREATE FUNCTION func_name	查看创建函数的语句。
WARNINGS [LIMIT N \| LIMIT N,M]	查看告警。
COUNT(*) WARNINGS	查看告警数量。
ERRORS [LIMIT N \| LIMIT N,M]	查看错误。
COUNT(*) ERRORS	查看错误数量。
GRANTS opt_for_grant_user	查看指定用户的权限。如果不指定用户名，则显示当前用户的权限。当前用户可以查看自己的权限。如果需要查看其他指定用户的权限，必须拥有对 `mysql.user` 的 `SELECT` 权限。
[STORAGE] ENGINES	查看存储引擎，seekdb 的存储引擎为 `OceanBase`。
PRIVILEGES	查看各项权限的描述。
RECYCLEBIN	查看回收站。
SEQUENCE	查看所创建的序列。
PROCEDURE CODE proc_name	查看存储过程 `proc_name` 的内部实现。当前版本仅支持该关键字语法，功能不生效。
FUNCTION CODE func_name	查看存储函数 `func_name` 的内部实现。当前版本仅支持该关键字语法，功能不生效。
ENGIN engine_name	查看有关存储引擎的操作信息。当前版本仅支持该关键字语法，功能不生效。
OPEN TABLES	用于查看服务器当前已经打开的表及其状态信息。当前版本仅支持该关键字语法，功能不生效。
CREATE CATALOG external_catalog_name	用于查看创建 Catalog 的语句。
CATALOGS	用于查看当前租户的 Catalog。

示例

查看所有数据库。

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db                 |
| information_schema |
| latin1_db          |
| mysql              |
| my_test            |
| oceanbase          |
| ocs                |
| pd_test            |
| sys_external_tbs   |
| test               |
| test_db            |
| test_dba           |
| test_recycle       |
| test_ro_db         |
| test_rw_db         |
| test_table         |
| wl                 |
+--------------------+
17 rows in set (0.129 sec)

查看支持的字符集。

SHOW CHARSET;
+--------------+---------------------------+-------------------------+--------+
| Charset      | Description               | Default collation       | Maxlen |
+--------------+---------------------------+-------------------------+--------+
| binary       | Binary pseudo charset     | binary                  |      1 |
| utf8mb4      | UTF-8 Unicode             | utf8mb4_general_ci      |      4 |
| gbk          | GBK charset               | gbk_chinese_ci          |      2 |
| utf16        | UTF-16 Unicode            | utf16_general_ci        |      4 |
| gb18030      | GB18030 charset           | gb18030_chinese_ci      |      4 |
| latin1       | cp1252 West European      | latin1_swedish_ci       |      1 |
| gb2312       | GB2312 Simplified Chinese | gb2312_chinese_ci       |      2 |
| gb18030_2022 | GB18030-2022 charset      | gb18030_2022_chinese_ci |      4 |
| ascii        | US ASCII                  | ascii_general_ci        |      1 |
| tis620       | TIS620 Thai               | tis620_thai_ci          |      1 |
| ujis         | EUC-JP Japanese           | ujis_japanese_ci        |      3 |
| euckr        | EUC-KR Korean             | euckr_korean_ci         |      2 |
| eucjpms      | UJIS for Windows Japanese | eucjpms_japanese_ci     |      3 |
| cp932        | SJIS for Windows Japanese | cp932_japanese_ci       |      2 |
| utf16le      | UTF-16LE Unicode          | utf16le_general_ci      |      4 |
| sjis         | SJIS                      | sjis_japanese_ci        |      2 |
| big5         | BIG5                      | big5_chinese_ci         |      2 |
| hkscs        | HKSCS                     | hkscs_bin               |      2 |
| hkscs31      | HKSCS-ISO UNICODE 31      | hkscs31_bin             |      2 |
| dec8         | DEC West European         | dec8_swedish_ci         |      1 |
| cp850        | DOS West European         | cp850_general_ci        |      1 |
| hp8          | HP West European          | hp8_english_ci          |      1 |
| macroman     | Mac West European         | macroman_general_ci     |      1 |
| swe7         | 7bit West European        | swe7_swedish_ci         |      1 |
+--------------+---------------------------+-------------------------+--------+
24 rows in set (0.210 sec)

查看支持的字符序。

SHOW COLLATION;
+--------------------+---------+-----+---------+----------+---------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin        | utf8mb4 |  46 |         | Yes      |       1 |
| binary             | binary  |  63 | Yes     | Yes      |       1 |
| gbk_chinese_ci     | gbk     |  28 | Yes     | Yes      |       1 |
| gbk_bin            | gbk     |  87 |         | Yes      |       1 |
| utf16_general_ci   | utf16   |  54 | Yes     | Yes      |       1 |
| utf16_bin          | utf16   |  55 |         | Yes      |       1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 |         | Yes      |       1 |
| utf16_unicode_ci   | utf16   | 101 |         | Yes      |       1 |
| gb18030_chinese_ci | gb18030 | 248 | Yes     | Yes      |       1 |
| gb18030_bin        | gb18030 | 249 |         | Yes      |       1 |
| latin1_swedish_ci  | latin1  |   8 | Yes     | Yes      |       1 |
| latin1_bin         | latin1  |  47 |         | Yes      |       1 |
+--------------------+---------+-----+---------+----------+---------+
13 rows in set (0.146 sec)

查看当前的会话状态。

SHOW SESSION STATUS;
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_connected | 16     |
| Uptime            | 945571 |
+-------------------+--------+

查看各权限的含义。

SHOW PRIVILEGES;
+----------------+---------------------------------------+-------------------------------------------------------+
| Privilege      | Context                               | Comment                                               |
+----------------+---------------------------------------+-------------------------------------------------------+
| Alter          | Tables                                | To alter the table                                    |
| Create         | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create user    | Server Admin                          | To create new users                                   |
| Create view    | Tables                                | To create new views                                   |
| Delete         | Tables                                | To delete existing rows                               |
| Drop           | Databases,Tables                      | To drop databases, tables, and views                  |
| Grant option   | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index          | Tables                                | To create or drop indexes                             |
| Insert         | Tables                                | To insert data into tables                            |
| Process        | Server Admin                          | To view the plain text of currently executing queries |
| Select         | Tables                                | To retrieve rows from table                           |
| Show databases | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view      | Tables                                | To see views with SHOW CREATE VIEW                    |
| Super          | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Update         | Tables                                | To update existing rows                               |
| Usage          | Server Admin                          | No privileges - allow connect only                    |
+----------------+---------------------------------------+-------------------------------------------------------+

查看回收站。

SHOW RECYCLEBIN;
+---------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME                     | ORIGINAL_NAME | TYPE  | CREATETIME                 |
+---------------------------------+---------------+-------+----------------------------+
| __recycle_$_10_1629093637659648 | t1            | TABLE | 2021-08-16 14:00:37.660550 |
| __recycle_$_10_1629093721748992 | t1            | TABLE | 2021-08-16 14:02:01.748691 |
+---------------------------------+---------------+-------+----------------------------+

查看所创建的序列。

CREATE SEQUENCE IF NOT EXISTS s3 ORDER CACHE 100 INCREMENT BY 3 MAXVALUE 99999999999;
Query OK, 0 rows affected

SHOW SEQUENCE LIKE 's%';
+------------------------+
| Sequences_in_test (s%) |
+------------------------+
| s1                     |
| s2                     |
| s3                     |
+------------------------+
3 rows in set (0.002 sec)

SHOW SEQUENCE WHERE sequence_name='s3';
+-------------------+
| Sequences_in_test |
+-------------------+
| s3                |
+-------------------+
1 row in set (0.001 sec)

SHOW SEQUENCE;
+-------------------+
| Sequences_in_test |
+-------------------+
| a2                |
| s1                |
| s2                |
| s3                |
+-------------------+
4 rows in set (0.002 sec)

使用 SHOW TRACE 查看某一 SQL 的执行信息，并以 JSON 的格式输出。

SET ob_enable_show_trace = 1;
Query OK, 0 rows affected (0.001 sec)

CREATE TABLE t1(c1 INT,c2 INT,c3 INT);
Query OK, 0 rows affected (0.001 sec)

INSERT INTO t1 VALUES(1,1,1);
Query OK, 1 rows affected (0.001 sec)

INSERT INTO t1 VALUES(2,2,2);
Query OK, 1 rows affected (0.001 sec)

SELECT/*+PARALLEL(2)*/ COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.001 sec)

SHOW TRACE FORMAT='JSON'\G
*************************** 1. row ***************************
ShowTraceJSON: [{"logs": null, "tags": [[{"sess_id": 3221487676}, {"action_name": ""}, {"module_name": ""}, {"client_info": ""}, {"receive_ts": 1686734801498147}, {"log_trace_id": "YB42AC1E87DE-0005FDE675EF77C4-0-0"}]], "elapse": 4716, "end_ts": "2023-06-14 17:26:41.502925", "parent": "0005fe13-8cac-6fd6-8035-4c299e621239", "span_id": "0005fe13-8cac-7061-6648-1148424d99fa", "start_ts": "2023-06-14 17:26:41.498209", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "com_query_process", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 4698, "end_ts": "2023-06-14 17:26:41.502914", "parent": "0005fe13-8cac-7061-6648-1148424d99fa", "span_id": "0005fe13-8cac-7068-d79c-9a1e3df2f09f", "start_ts": "2023-06-14 17:26:41.498216", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "mpquery_single_stmt", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": [[{"sql_text": "SHOW TRACE FORMAT='JSON'"}], [{"sql_id": "D2A6E68D54F4B888F9443FD4EABB490C"}, {"database_id": 500001}, {"plan_hash": 13465692160314901852}], [{"hit_plan": false}]], "elapse": 2623, "end_ts": "2023-06-14 17:26:41.500858", "parent": "0005fe13-8cac-7068-d79c-9a1e3df2f09f", "span_id": "0005fe13-8cac-707b-84fc-7259f7a5afa4", "start_ts": "2023-06-14 17:26:41.498235", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "sql_compile", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 5, "end_ts": "2023-06-14 17:26:41.498244", "parent": "0005fe13-8cac-707b-84fc-7259f7a5afa4", "span_id": "0005fe13-8cac-707f-c009-4663739d39ed", "start_ts": "2023-06-14 17:26:41.498239", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "pc_get_plan", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 2543, "end_ts": "2023-06-14 17:26:41.500841", "parent": "0005fe13-8cac-707b-84fc-7259f7a5afa4", "span_id": "0005fe13-8cac-70ba-5c5c-1dbf433776e1", "start_ts": "2023-06-14 17:26:41.498298", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "hard_parse", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 27, "end_ts": "2023-06-14 17:26:41.498327", "parent": "0005fe13-8cac-70ba-5c5c-1dbf433776e1", "span_id": "0005fe13-8cac-70bc-91c6-d074e4c11eb8", "start_ts": "2023-06-14 17:26:41.498300", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "parse", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 777, "end_ts": "2023-06-14 17:26:41.499126", "parent": "0005fe13-8cac-70ba-5c5c-1dbf433776e1", "span_id": "0005fe13-8cac-70ed-8482-917a5e0c16e7", "start_ts": "2023-06-14 17:26:41.498349", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "resolve", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 855, "end_ts": "2023-06-14 17:26:41.500028", "parent": "0005fe13-8cac-70ba-5c5c-1dbf433776e1", "span_id": "0005fe13-8cac-7425-2af1-497b7573f962", "start_ts": "2023-06-14 17:26:41.499173", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "rewrite", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 477, "end_ts": "2023-06-14 17:26:41.500522", "parent": "0005fe13-8cac-70ba-5c5c-1dbf433776e1", "span_id": "0005fe13-8cac-778d-5e60-51648fcad206", "start_ts": "2023-06-14 17:26:41.500045", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "optimize", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 176, "end_ts": "2023-06-14 17:26:41.500712", "parent": "0005fe13-8cac-70ba-5c5c-1dbf433776e1", "span_id": "0005fe13-8cac-7978-2ba6-2d656301eaef", "start_ts": "2023-06-14 17:26:41.500536", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "code_generate", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 1980, "end_ts": "2023-06-14 17:26:41.502845", "parent": "0005fe13-8cac-7068-d79c-9a1e3df2f09f", "span_id": "0005fe13-8cac-7ac1-2183-d9f0749c518d", "start_ts": "2023-06-14 17:26:41.500865", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "sql_execute", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 21, "end_ts": "2023-06-14 17:26:41.500887", "parent": "0005fe13-8cac-7ac1-2183-d9f0749c518d", "span_id": "0005fe13-8cac-7ac2-cc2b-a9dcea52ac30", "start_ts": "2023-06-14 17:26:41.500866", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "open", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 1874, "end_ts": "2023-06-14 17:26:41.502770", "parent": "0005fe13-8cac-7ac1-2183-d9f0749c518d", "span_id": "0005fe13-8cac-7ae0-063a-65a2b4aefdd6", "start_ts": "2023-06-14 17:26:41.500896", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "response_result", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 44, "end_ts": "2023-06-14 17:26:41.500947", "parent": "0005fe13-8cac-7ae0-063a-65a2b4aefdd6", "span_id": "0005fe13-8cac-7ae7-2357-70f9191525f2", "start_ts": "2023-06-14 17:26:41.500903", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "do_local_das_task", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": [[{"sql_id": "7F33FD22651F99E8AB2BAC5428623BCD"}, {"database_id": 201001}], [{"sql_text": "START TRANSACTION WITH CONSISTENT SNAPSHOT"}], [{"hit_plan": false}]], "elapse": 95, "end_ts": "2023-06-14 17:26:41.501466", "parent": "0005fe13-8cac-7ae0-063a-65a2b4aefdd6", "span_id": "0005fe13-8cac-7cbb-24eb-6fd68edef4b3", "start_ts": "2023-06-14 17:26:41.501371", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "sql_compile", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 5, "end_ts": "2023-06-14 17:26:41.501378", "parent": "0005fe13-8cac-7cbb-24eb-6fd68edef4b3", "span_id": "0005fe13-8cac-7cbd-d28e-243bd51c8f52", "start_ts": "2023-06-14 17:26:41.501373", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "pc_get_plan", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 53, "end_ts": "2023-06-14 17:26:41.501456", "parent": "0005fe13-8cac-7cbb-24eb-6fd68edef4b3", "span_id": "0005fe13-8cac-7cdb-6744-4e35d589f69e", "start_ts": "2023-06-14 17:26:41.501403", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "hard_parse", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 14, "end_ts": "2023-06-14 17:26:41.501418", "parent": "0005fe13-8cac-7cdb-6744-4e35d589f69e", "span_id": "0005fe13-8cac-7cdc-d22e-b917f1f800ac", "start_ts": "2023-06-14 17:26:41.501404", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "parse", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 10, "end_ts": "2023-06-14 17:26:41.501446", "parent": "0005fe13-8cac-7cdb-6744-4e35d589f69e", "span_id": "0005fe13-8cac-7cfc-a422-4419ea2a8f67", "start_ts": "2023-06-14 17:26:41.501436", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "resolve", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 32, "end_ts": "2023-06-14 17:26:41.501531", "parent": "0005fe13-8cac-7ae0-063a-65a2b4aefdd6", "span_id": "0005fe13-8cac-7d3b-4877-b72cd0df1355", "start_ts": "2023-06-14 17:26:41.501499", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "open", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 22, "end_ts": "2023-06-14 17:26:41.501522", "parent": "0005fe13-8cac-7d3b-4877-b72cd0df1355", "span_id": "0005fe13-8cac-7d3c-4373-3698da80bc1d", "start_ts": "2023-06-14 17:26:41.501500", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "cmd_open", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": [[{"trans_id": 0}]], "elapse": 1, "end_ts": "2023-06-14 17:26:41.501502", "parent": "0005fe13-8cac-7d3c-4373-3698da80bc1d", "span_id": "0005fe13-8cac-7d3d-8ba4-00096f64fb96", "start_ts": "2023-06-14 17:26:41.501501", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "end_transaction", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 2, "end_ts": "2023-06-14 17:26:41.501552", "parent": "0005fe13-8cac-7ae0-063a-65a2b4aefdd6", "span_id": "0005fe13-8cac-7d6e-608e-1f9e19e7615d", "start_ts": "2023-06-14 17:26:41.501550", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "close", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 536, "end_ts": "2023-06-14 17:26:41.502144", "parent": "0005fe13-8cac-7ae0-063a-65a2b4aefdd6", "span_id": "0005fe13-8cac-7da8-8fe7-73d23fcb23bf", "start_ts": "2023-06-14 17:26:41.501608", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "inner_execute_read", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": [[{"sql_text": "SELECT svr_ip, svr_port, tenant_id, trace_id, request_id, span_id, parent_span_id, span_name, ref_type, start_ts, end_ts, tags, logs FROM __all_virtual_trace_span_info WHERE tenant_id = 1002 AND trace_id = '0005fe13-8bf2-47d5-2cdd-5d819739c997'"}], [{"sql_id": "9B307250A34F95FE531FDC05F9F87300"}, {"database_id": 201001}, {"plan_hash": 13345609059733987708}, {"hit_plan": true}]], "elapse": 96, "end_ts": "2023-06-14 17:26:41.501714", "parent": "0005fe13-8cac-7da8-8fe7-73d23fcb23bf", "span_id": "0005fe13-8cac-7db2-b9c2-ef1bdb1e916b", "start_ts": "2023-06-14 17:26:41.501618", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "sql_compile", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 68, "end_ts": "2023-06-14 17:26:41.501687", "parent": "0005fe13-8cac-7db2-b9c2-ef1bdb1e916b", "span_id": "0005fe13-8cac-7db3-0fc7-6c76f9923e90", "start_ts": "2023-06-14 17:26:41.501619", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "pc_get_plan", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 5, "end_ts": "2023-06-14 17:26:41.501730", "parent": "0005fe13-8cac-7da8-8fe7-73d23fcb23bf", "span_id": "0005fe13-8cac-7e1d-fffd-bb0c14004d0b", "start_ts": "2023-06-14 17:26:41.501725", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "open", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 35, "end_ts": "2023-06-14 17:26:41.501788", "parent": "0005fe13-8cac-7da8-8fe7-73d23fcb23bf", "span_id": "0005fe13-8cac-7e39-e47b-68b94d34b9f2", "start_ts": "2023-06-14 17:26:41.501753", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "do_local_das_task", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 32, "end_ts": "2023-06-14 17:26:41.502270", "parent": "0005fe13-8cac-7ae0-063a-65a2b4aefdd6", "span_id": "0005fe13-8cac-801e-95e9-f013f18e58ae", "start_ts": "2023-06-14 17:26:41.502238", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "close", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 12, "end_ts": "2023-06-14 17:26:41.502253", "parent": "0005fe13-8cac-801e-95e9-f013f18e58ae", "span_id": "0005fe13-8cac-8021-a2b8-efb5ea82362a", "start_ts": "2023-06-14 17:26:41.502241", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "close_das_task", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 131, "end_ts": "2023-06-14 17:26:41.502419", "parent": "0005fe13-8cac-7ae0-063a-65a2b4aefdd6", "span_id": "0005fe13-8cac-8050-4e15-2c08b254c1a5", "start_ts": "2023-06-14 17:26:41.502288", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "inner_commit", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": [[{"sql_text": "COMMIT"}], [{"sql_id": "1D0BA376E273B9D622641124D8C59264"}, {"database_id": 201001}]], "elapse": 50, "end_ts": "2023-06-14 17:26:41.502344", "parent": "0005fe13-8cac-8050-4e15-2c08b254c1a5", "span_id": "0005fe13-8cac-8056-1325-2808b1b2c771", "start_ts": "2023-06-14 17:26:41.502294", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "sql_compile", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 43, "end_ts": "2023-06-14 17:26:41.502338", "parent": "0005fe13-8cac-8056-1325-2808b1b2c771", "span_id": "0005fe13-8cac-8057-68cd-3b71b27d0efc", "start_ts": "2023-06-14 17:26:41.502295", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "hard_parse", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 6, "end_ts": "2023-06-14 17:26:41.502302", "parent": "0005fe13-8cac-8057-68cd-3b71b27d0efc", "span_id": "0005fe13-8cac-8058-f520-2ec9b2347039", "start_ts": "2023-06-14 17:26:41.502296", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "parse", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 9, "end_ts": "2023-06-14 17:26:41.502328", "parent": "0005fe13-8cac-8057-68cd-3b71b27d0efc", "span_id": "0005fe13-8cac-806f-120c-2d223c5cafed", "start_ts": "2023-06-14 17:26:41.502319", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "resolve", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 31, "end_ts": "2023-06-14 17:26:41.502381", "parent": "0005fe13-8cac-8050-4e15-2c08b254c1a5", "span_id": "0005fe13-8cac-808e-48e0-16311abf387d", "start_ts": "2023-06-14 17:26:41.502350", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "open", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 26, "end_ts": "2023-06-14 17:26:41.502376", "parent": "0005fe13-8cac-808e-48e0-16311abf387d", "span_id": "0005fe13-8cac-808e-d038-2dd226a767c8", "start_ts": "2023-06-14 17:26:41.502350", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "cmd_open", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": [[{"trans_id": 638124}]], "elapse": 15, "end_ts": "2023-06-14 17:26:41.502366", "parent": "0005fe13-8cac-808e-d038-2dd226a767c8", "span_id": "0005fe13-8cac-808f-c5be-0aea9943a94e", "start_ts": "2023-06-14 17:26:41.502351", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "end_transaction", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 1, "end_ts": "2023-06-14 17:26:41.502394", "parent": "0005fe13-8cac-8050-4e15-2c08b254c1a5", "span_id": "0005fe13-8cac-80b9-b0dc-197d3c4b2ffd", "start_ts": "2023-06-14 17:26:41.502393", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "close", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 55, "end_ts": "2023-06-14 17:26:41.502838", "parent": "0005fe13-8cac-7ac1-2183-d9f0749c518d", "span_id": "0005fe13-8cac-823f-9393-a4c4d9ef623d", "start_ts": "2023-06-14 17:26:41.502783", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "close", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": null, "elapse": 5, "end_ts": "2023-06-14 17:26:41.502789", "parent": "0005fe13-8cac-823f-9393-a4c4d9ef623d", "span_id": "0005fe13-8cac-8240-73a1-98f019c5455a", "start_ts": "2023-06-14 17:26:41.502784", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "close_das_task", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}, {"logs": null, "tags": [[{"trans_id": 0}]], "elapse": 3, "end_ts": "2023-06-14 17:26:41.502828", "parent": "0005fe13-8cac-823f-9393-a4c4d9ef623d", "span_id": "0005fe13-8cac-8269-cc9b-b89fe9a1cda3", "start_ts": "2023-06-14 17:26:41.502825", "trace_id": "0005fe13-8cac-6fd6-0b2a-658fb95ee88f", "span_name": "end_transaction", "tenant_id": 1002, "rec_svr_ip": "172.xx.xxx.xxx", "rec_svr_port": 2882}]
1 row in set (0.001 sec)

通过 SHOW COLUMNS 查看表 tbl2 的列信息。

SHOW COLUMNS FROM tbl2;

查询结果如下：

  +-------+---------+------+-----+---------+-------+
  | Field | Type    | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | a     | int(11) | YES  |     | NULL    |       |
  | b     | int(11) | YES  |     | NULL    |       |
  +-------+---------+------+-----+---------+-------+
  2 rows in set (0.009 sec)

通过 SHOW EXTENDED COLUMNS 查看表 tbl2 隐藏的列信息。

SHOW EXTENDED COLUMNS FROM tbl2;

查询结果如下：

  +----------------+-----------------+------+-----+---------+-------+
  | Field          | Type            | Null | Key | Default | Extra |
  +----------------+-----------------+------+-----+---------+-------+
  | a              | int(11)         | YES  |     | NULL    |       |
  | b              | int(11)         | YES  |     | NULL    |       |
  | __pk_increment | bigint unsigned | NO   | PRI | NULL    |       |
  +----------------+-----------------+------+-----+---------+-------+
  3 rows in set (0.006 sec)

通过 SHOW INDEX 查看表 tbl2 的索引信息。

SHOW INDEX FROM tbl2;

查询结果如下：

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| tbl2  |          1 | tbl2_index |            1 | a           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
| tbl2  |          1 | tbl2_index |            2 | b           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
2 rows in set (0.005 sec)

通过 SHOW EXTENDED INDEX 查看表 tbl2 隐藏的索引信息。

  SHOW EXTENDED INDEX FROM tbl2;

查询结果如下：

  +-------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name   | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl2  |          0 | PRIMARY    |            1 | __pk_increment | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
  | tbl2  |          0 | PRIMARY    |            2 | a              | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
  | tbl2  |          0 | PRIMARY    |            3 | b              | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
  | tbl2  |          1 | tbl2_index |            1 | a              | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  | tbl2  |          1 | tbl2_index |            2 | b              | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  | tbl2  |          1 | tbl2_index |            3 | __pk_increment | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
  +-------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  6 rows in set (0.004 sec)

描述​

语法​

参数解释​

示例​

Contents

描述

语法

参数解释

示例