经验分享 | 如何通过SQL获取MySQL对象的DDL、统计信息、查询的执行计划

select table_name, table_type from information_schema.tables 
where table_schema = '$dbname'

SHOW CREATE TABLE tpch.customer

CREATE TABLE `customer` (
`C_CUSTKEY` int NOT NULL,
`C_NAME` varchar(25) NOT NULL,
`C_ADDRESS` varchar(40) NOT NULL,
`C_NATIONKEY` int NOT NULL,
`C_PHONE` char(15) NOT NULL,
`C_ACCTBAL` decimal(15,2) NOT NULL,
`C_MKTSEGMENT` char(10) NOT NULL,
`C_COMMENT` varchar(117) NOT NULL,
 PRIMARY KEY `PK_IDX1614428511` (`C_CUSTKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

SHOW CREATE TABLE tpch.customer_v

create view `customer_v` as select
`customer`.`C_CUSTKEY` as `C_CUSTKEY`,
`customer`.`C_NAME` as `C_NAME`,
`customer`.`C_ADDRESS` as `C_ADDRESS`,
`customer`.`C_NATIONKEY` as `C_NATIONKEY`,
`customer`.`C_PHONE` as `C_PHONE`,
`customer`.`C_ACCTBAL` as `C_ACCTBAL`,
`customer`.`C_MKTSEGMENT` as `C_MKTSEGMENT`,
`customer`.`C_COMMENT` as `C_COMMENT`
from `customer`
where (`customer`.`C_CUSTKEY` < 100)

select
table_schema,
table_name,
table_type,
engine,
table_rows
from information_schema.tables
where table_schema = $dbname

analyze table customer;

select
table_name,
index_name,
stat_name,
stat_value,
stat_description
from mysql.innodb_index_stats
where database_name = 'tpch'

analyze table orders update histogram on o_custkey, o_orderdate 
with 100 buckets;

select schema_name, table_name, column_name,
histogram->>'$."histogram-type"' htype, histogram
from information_schema.column_statistics
where schema_name = 'tpch'

explain select c_name, c_address 
from customer c 
where c.c_custkey < 100

explain format = json select c_name, c_address 
from customer c 
where c.c_custkey < 100

 {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "20.30"
    },
    "table": {
      "table_name": "c",
      "access_type": "range",
      "possible_keys": [
        "key_idx"
      ],
      "key": "key_idx",
      "used_key_parts": [
        "C_CUSTKEY"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 100,
      "rows_produced_per_join": 100,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "10.30",
        "eval_cost": "10.00",
        "prefix_cost": "20.30",
        "data_read_per_join": "89K"
      },
      "used_columns": [
        "C_CUSTKEY",
        "C_NAME",
        "C_ADDRESS"
      ],
      "attached_condition": "(`tpch`.`c`.`C_CUSTKEY` < 100)"
    }
  }
}

explain format = tree select c_name, c_address 
from customer c 
where c.c_custkey < 100

-> Filter: (c.C_CUSTKEY < 100)  (cost=20.30 rows=100)
    -> Index range scan on c using key_idx over (C_CUSTKEY < 100)  (cost=20.30 rows=100)

explain format = json select c_name, c_address 
from customer c 
where c.c_custkey < 100

-> Filter: (c.C_CUSTKEY < 100)  (cost=20.30 rows=100) (actual time=0.254..0.312 rows=100 loops=1)
    -> Index range scan on c using key_idx over (C_CUSTKEY < 100)  (cost=20.30 rows=100) (actual time=0.017..0.069 rows=100 loops=1)

       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>8.0.22</version>
       </dependency>