如何通过SQL语句获取表/视图的DDL,表/列/索引的统计信息,查询的执行计划(MySQL)

获取对象的定义SQL语句

列出库中的表和视图

  • 查询语句
select table_name, table_type from information_schema.tables 
where table_schema = '$dbname'

table_type标识是表还是视图,

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

表的DDL语句

  • 查询语句
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;

索引的DDL语句

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

视图的DDL语句

  • 查询语句
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)

物化视图的DDL语句

MySQL不支持物化视图

获取统计信息的SQL语句

表级统计信息

  • 查询语句
select
	table_schema,
	table_name,
	table_type,
	engine,
	table_rows
from
	information_schema.tables
where
	table_schema = $dbname
  • 查询结果

    TABLE_SCHEMATABLE_NAMETABLE_TYPEENGINETABLE_ROWS
    tpchcustomerBASE TABLEInnoDB9,935
    tpchcustomer_vVIEWNULLNULL
    tpchlineitemBASE TABLEInnoDB148,390
    tpchnationBASE TABLEInnoDB543
    tpchordersBASE TABLEInnoDB200,128
    tpchpartBASE TABLEInnoDB721,764
    tpchpartsuppBASE TABLEInnoDB248,270
    tpchregionBASE TABLEInnoDB98,545

索引统计信息

  • 收集索引统计信息
analyze table customer;
  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'
  • 查询结果

    table_nameindex_namestat_namestat_valuestat_description
    customerkey_idxn_diff_pfx019,935C_CUSTKEY
    customerkey_idxn_leaf_pages133Number of leaf pages in the index
    customerkey_idxsize161Number of pages in the index
    lineitemGEN_CLUST_INDEXn_diff_pfx01148,390DB_ROW_ID
    lineitemGEN_CLUST_INDEXn_leaf_pages1,562Number of leaf pages in the index
    lineitemGEN_CLUST_INDEXsize1,571Number of pages in the index
    lineiteml_partkey_idxn_diff_pfx0118,356L_PARTKEY
    lineiteml_partkey_idxn_diff_pfx02149,721L_PARTKEY,DB_ROW_ID
    lineiteml_partkey_idxn_leaf_pages143Number of leaf pages in the index
    lineiteml_partkey_idxsize225Number of pages in the index
    lineiteml_shipdate_idxn_diff_pfx0115,745L_SHIPDATE
    lineiteml_shipdate_idxn_diff_pfx02149,946L_SHIPDATE,DB_ROW_ID
    lineiteml_shipdate_idxn_leaf_pages134Number of leaf pages in the index
    lineiteml_shipdate_idxsize161Number of pages in the index

列级统计信息

  • 收集列上的统计信息
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'
  • 查询结果
SCHEMA_NAMETABLE_NAMECOLUMN_NAMEhtypeHISTOGRAM
tpchordersO_CUSTKEYequi-height{“buckets”: [[0, 803, 0.09997181005099819, 804], [804, 1682, 0.20001195937230382, 879], [1683, 3685, 0.30000939664966725, 2004], [3686, 6331, 0.3999897491094539, 2647], [6332, 8964, 0.4999957287956058, 2634], [8965, 284782258, 0.6000102508905462, 4304], [284876800, 743350400, 0.7000076881679096, 5371], [743377234, 1205176678, 0.8000136678540615, 5442], [1205354704, 1662703498, 0.8999940203138481, 5380], [1662881524, 2147483647, 1.0, 5502]], “data-type”: “int”, “null-values”: 0.0, “collation-id”: 8, “last-updated”: “2023-05-11 08:12:50.964396”, “sampling-rate”: 0.5678184143966043, “histogram-type”: “equi-height”, “number-of-buckets-specified”: 10}
tpchordersO_ORDERDATEequi-height{“buckets”: [[“1900-01-01”, “1924-11-27”, 0.09999743727736347, 4533], [“1924-11-30”, “1950-01-21”, 0.20000341696351537, 4483], [“1950-01-22”, “1975-04-21”, 0.2999666846057251, 4562], [“1975-04-22”, “2000-06-27”, 0.3999982915182423, 4533], [“2000-07-01”, “2020-03-05”, 0.5000469832483364, 3249], [“2020-03-06”, “2020-08-07”, 0.599907741985085, 155], [“2020-08-08”, “2021-01-09”, 0.7000418578030633, 155], [“2021-01-10”, “2021-06-12”, 0.8002528553001376, 154], [“2021-06-13”, “2021-11-14”, 0.9002759198038663, 155], [“2021-11-15”, “2022-09-01”, 1.0, 179]], “data-type”: “date”, “null-values”: 0.0, “collation-id”: 8, “last-updated”: “2023-05-11 08:12:50.965784”, “sampling-rate”: 0.5678184143966043, “histogram-type”: “equi-height”, “number-of-buckets-specified”: 10}

获取执行计划的Explain语句

Explain

explain select C_NAME, C_ADDRESS from customer c where c.C_CUSTKEY < 100
1	SIMPLE	c		range	key_idx	key_idx	4		100	100.0	Using where

Explain Json

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 Tree (8.0.16及以上)

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 Analyze (8.0.18及以上)

explain analyze 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)

关于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公众号PawSQL

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值