如何通过SQL语句获取表/视图的DDL，表/列/索引的统计信息，查询的执行计划（MySQL）_sql 语出获取创建表的ddl

获取对象的定义SQL语句

列出库中的表和视图

• 查询语句

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

table_type标识是表还是视图，

• ‘base_type’ - 表
• ‘view’ - 视图

表的DDL语句

• 查询语句

SHOW CREATE TABLE tpch.customer
1

• 查询结果

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;
1
2
3
4
5
6
7
8
9
10
11

索引的DDL语句

对于MySQL数据库，索引信息可以从建表语句中获取，无需单独获取。

视图的DDL语句

• 查询语句

SHOW CREATE TABLE tpch.customer_v
1

• 查询结果

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

物化视图的DDL语句

MySQL不支持物化视图

获取统计信息的SQL语句

表级统计信息

• 查询语句

select
	table_schema,
	table_name,
	table_type,
	engine,
	table_rows
from
	information_schema.tables
where
	table_schema = $dbname
1
2
3
4
5
6
7
8
9
10

• 查询结果

  TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	TABLE_ROWS
  tpch	customer	BASE TABLE	InnoDB	9,935
  tpch	customer_v	VIEW	NULL	NULL
  tpch	lineitem	BASE TABLE	InnoDB	148,390
  tpch	nation	BASE TABLE	InnoDB	543
  tpch	orders	BASE TABLE	InnoDB	200,128
  tpch	part	BASE TABLE	InnoDB	721,764
  tpch	partsupp	BASE TABLE	InnoDB	248,270
  tpch	region	BASE TABLE	InnoDB	98,545

索引统计信息

• 收集索引统计信息

analyze table customer;
1

1. analyze table 会统计索引分布信息。
2. 支持 InnoDB、NDB、MyISAM 等存储引擎
3. 对于 MyISAM 表，相当于执行了一次 myisamchk --analyze
4. 执行 analyze table 时，会对表加上读锁
5. 该操作会记录binlog
6. 不支持视图

• 查询语句

select
	table_name,
	index_name,
	stat_name,
	stat_value,
	stat_description
from
	mysql.innodb_index_stats
where
	database_name = 'tpch'
1
2
3
4
5
6
7
8
9
10

• 查询结果

  table_name	index_name	stat_name	stat_value	stat_description
  customer	key_idx	n_diff_pfx01	9,935	C_CUSTKEY
  customer	key_idx	n_leaf_pages	133	Number of leaf pages in the index
  customer	key_idx	size	161	Number of pages in the index
  lineitem	GEN_CLUST_INDEX	n_diff_pfx01	148,390	DB_ROW_ID
  lineitem	GEN_CLUST_INDEX	n_leaf_pages	1,562	Number of leaf pages in the index
  lineitem	GEN_CLUST_INDEX	size	1,571	Number of pages in the index
  lineitem	l_partkey_idx	n_diff_pfx01	18,356	L_PARTKEY
  lineitem	l_partkey_idx	n_diff_pfx02	149,721	L_PARTKEY,DB_ROW_ID
  lineitem	l_partkey_idx	n_leaf_pages	143	Number of leaf pages in the index
  lineitem	l_partkey_idx	size	225	Number of pages in the index
  lineitem	l_shipdate_idx	n_diff_pfx01	15,745	L_SHIPDATE
  lineitem	l_shipdate_idx	n_diff_pfx02	149,946	L_SHIPDATE,DB_ROW_ID
  lineitem	l_shipdate_idx	n_leaf_pages	134	Number of leaf pages in the index
  lineitem	l_shipdate_idx	size	161	Number of pages in the index

列级统计信息

• 收集列上的统计信息

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

• 查询语句

select
	schema_name,
	table_name,
	column_name,
	histogram->>'$."histogram-type"' htype,
	histogram
from
	information_schema.column_statistics
where
	schema_name = 'tpch'
1
2
3
4
5
6
7
8
9
10

• 查询结果

获取执行计划的Explain语句

Explain

explain select C_NAME, C_ADDRESS from customer c where c.C_CUSTKEY < 100
1

1	SIMPLE	c		range	key_idx	key_idx	4		100	100.0	Using where
1

Explain Json

explain format = json select C_NAME, C_ADDRESS 
from customer c 
where c.C_CUSTKEY < 100
1
2
3

{
  "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)"
    }
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

Explain Tree (8.0.16及以上)

explain format = tree select C_NAME, C_ADDRESS 
from customer c 
where c.C_CUSTKEY < 100
1
2
3

-> 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)
1
2

Explain Analyze (8.0.18及以上)

explain analyze select C_NAME, C_ADDRESS 
from customer c 
where c.C_CUSTKEY < 100
1
2
3

-> 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)
1
2

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化，支持MySQL，PostgreSQL，Opengauss，Oracle等，提供的SQL优化产品包括

• PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员，
• PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
• PawSQL Engine, 是PawSQL系列产品的后端优化引擎，可以以docker镜像的方式独立安装部署，并通过http/json的接口提供SQL优化服务。

联系我们

网址: https://app.pawsql.com

邮件：service@pawsql.com

Twitter: https://twitter.com/pawsql

扫描关注PawSQL公众号