产品概述
应用场景
产品架构
实例类型
兼容性说明
使用规范建议
tdsql> explain select * from t1, t2 where t1.a = t2.a;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL || 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
tdsql> explain format=tree select * from t1, t2 where t1.a = t2.a;+---------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+---------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Inner hash join (t2.a = t1.a) (cost=6.28 rows=3)-> Table scan on t2 (cost=0.88 rows=3)-> Hash-> Table scan on t1 (cost=2.84 rows=3)|+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
tdsql> explain format=json select * from t1, t2 where t1.a = t2.a;+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| {"query_block": {"select_id": 1,"cost_info": {"query_cost": "6.27"},"nested_loop": [{"table": {"table_name": "t1","access_type": "ALL","rows_examined_per_scan": 3,"rows_produced_per_join": 3,"filtered": "100.00","cost_info": {"read_cost": "2.54","eval_cost": "0.30","prefix_cost": "2.84","data_read_per_join": "48"},"used_columns": ["a","b"]}},{"table": {"table_name": "t2","access_type": "ALL","rows_examined_per_scan": 3,"rows_produced_per_join": 3,"filtered": "33.33","using_join_buffer": "hash join","cost_info": {"read_cost": "2.54","eval_cost": "0.30","prefix_cost": "6.28","data_read_per_join": "48"},"used_columns": ["a","b"],"attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"}}]}} |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
tdsql> select connection_id();+-----------------+| connection_id() |+-----------------+| 1048611 |+-----------------+tdsql> show processlist;+---------+------------+-------+-------------------+---------------------+--------------------+---------+------+------------------------+------------------+---------+-----------+---------------+| Id | Tid | Mem | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |+---------+------------+-------+-------------------+---------------------+--------------------+---------+------+------------------------+------------------+---------+-----------+---------------+| 1048611 | 4294970988 | 16384 | test | 127.0.0.1:40318 | test | Query | 0 | init | show processlist | 0 | 0 | 0 |+---------+------------+-------+-------------------+---------------------+--------------------+---------+------+------------------------+------------------+---------+-----------+---------------+tdsql> explain for connection 1048611;ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE. txid: 0. sql-node: node-1-001. error-store-node: nil
tdsql> explain analyze select * from t1, t2 where t1.a = t2.a;+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Inner hash join (t2.a = t1.a) (cost=6.28 rows=3) (actual time=1.459..1.481 rows=3 loops=1)-> Table scan on t2 (cost=0.88 rows=3) (actual time=0.528..0.548 rows=3 loops=1)-> Hash-> Table scan on t1 (cost=2.84 rows=3) (actual time=0.841..0.863 rows=3 loops=1)|+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
tdsql> explain analyze verbose select * from t1, t2 where t1.a = t2.a;+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Inner hash join (t2.a = t1.a) (cost=6.28 rows=3) (actual time=0.511..0.533 rows=3 loops=1)Chunk pair files: 0, memory usage: 16kB-> Table scan on t2 (cost=0.88 rows=3) (actual time=0.167..0.185 rows=3 loops=1)-> Hash-> Table scan on t1 (cost=2.84 rows=3) (actual time=0.262..0.283 rows=3 loops=1)RPC statistics: leader-> LocalScanRecord=latency(ms): 2,0.266323,0.081208...0.185115, retry_count: 0, retry_interval_all(ms): 0.000000, failure_count: 0 |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
tdsql> set profiling = 1;Query OK, 0 rows affected, 1 warning (0.00 sec)tdsql> select * from t1 where a > 0;+------+------+| a | b |+------+------+| 1 | 1 || 2 | 2 || 3 | 3 |+------+------+3 rows in set (0.00 sec)tdsql> show profiles;+----------+------------+------------------------------+| Query_ID | Duration | Query |+----------+------------+------------------------------+| 1 | 0.00195300 | select * from t1 where a > 0 |+----------+------------+------------------------------+1 row in set, 1 warning (0.00 sec)tdsql> show profile for query 1;+--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| starting | 0.000204 || Executing hook on transaction | 0.000004 || starting | 0.000031 || checking permissions | 0.000014 || Opening tables | 0.000095 || init | 0.000009 || System lock | 0.000035 || optimizing | 0.000020 || statistics | 0.000076 || Wait gts rsp | 0.000354 || preparing | 0.000082 || executing | 0.000849 || end | 0.000007 || query end | 0.000005 || waiting for handler commit | 0.000028 || closing tables | 0.000061 || freeing items | 0.000079 || cleaning up | 0.000004 |+--------------------------------+----------+18 rows in set, 1 warning (0.01 sec)
tdsql> set optimizer_trace='enabled=on';Query OK, 0 rows affected (0.00 sec)tdsql> explain select * from t1 where a > 0;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)tdsql> select * from information_schema.optimizer_trace;+--------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+| QUERY | TRACE | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |+--------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+| explain select * from t1 where a > 0 | {"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` > 0)"}]}},{"join_optimization": {"select#": 1,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`t1`.`a` > 0)","steps": [{"transformation": "equality_propagation","resulting_condition": "(`t1`.`a` > 0)"},{"transformation": "constant_propagation","resulting_condition": "(`t1`.`a` > 0)"},{"transformation": "trivial_condition_removal","resulting_condition": "(`t1`.`a` > 0)"}]}},{"substitute_generated_columns": {}},{"table_dependencies": [{"table": "`t1`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"ref_optimizer_key_uses": []},{"rows_estimation": [{"table": "`t1`","table_scan": {"rows": 3,"cost": 2.5375}}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`t1`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 3,"filtering_effect": [],"final_filtering_effect": 0.333333,"access_type": "scan","resulting_rows": 1,"cost": 2.8375,"chosen": true}]},"condition_filtering_pct": 100,"rows_for_plan": 1,"cost_for_plan": 2.8375,"chosen": true}]},{"attaching_conditions_to_tables": {"original_condition": "(`t1`.`a` > 0)","attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`t1`","attached": "(`t1`.`a` > 0)"}]}},{"force_batched_key_access": [{"table": "`t1`","batched_key_access": true}]},{"finalizing_table_conditions": [{"table": "`t1`","original_table_condition": "(`t1`.`a` > 0)","final_table_condition ": "(`t1`.`a` > 0)"}]},{"refine_plan": [{"table": "`t1`"}]},{"engine_push_conditions": [{"table": "`t1`","total_rows": 3,"index": "hidden pk","condition_push": {},"single_table_push": {"projection_push": {"enabled": false,"cause": "read field pct is less than tdsql_max_projection_pct"}},"condition_pushed": false,"cause": "scan_rows < tdsql_push_down_threshold_rows"}]}]}},{"parallel_plan": {"select#": 1,"steps": [{"considering": {"chosen": false,"cause": "plan_cost_less_than_threshold"}}]}},{"local_access_optimize": {"check_local": true,"tables": [{"table": "`t1`","type": "scan","strategy": "condidate_for_local","info": "not support direct local"}]}},{"join_explain": {"select#": 1,"steps": []}}]} | 0 | 0 |+--------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+1 row in set (0.01 sec)tdsql> set optimizer_trace='enabled=off';Query OK, 0 rows affected (0.00 sec)
文档反馈