文章目录
获取对象的定义SQL语句列出库中的表和视图表的DDL语句索引的DDL语句视图的DDL语句物化视图的DDL语句
获取统计信息的SQL语句表级统计信息索引统计信息列级统计信息
获取执行计划的Explain语句ExplainExplain JsonExplain Tree (8.0.16及以上)Explain Analyze (8.0.18及以上)
关于PawSQL联系我们
获取对象的定义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_ROWStpchcustomerBASE TABLEInnoDB9,935tpchcustomer_vVIEWNULLNULLtpchlineitemBASE TABLEInnoDB148,390tpchnationBASE TABLEInnoDB543tpchordersBASE TABLEInnoDB200,128tpchpartBASE TABLEInnoDB721,764tpchpartsuppBASE TABLEInnoDB248,270tpchregionBASE TABLEInnoDB98,545
索引统计信息
收集索引统计信息
analyze table customer;
analyze table 会统计索引分布信息。支持 InnoDB、NDB、MyISAM 等存储引擎对于 MyISAM 表，相当于执行了一次 myisamchk --analyze执行 analyze table 时，会对表加上读锁该操作会记录binlog不支持视图
查询语句
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_descriptioncustomerkey_idxn_diff_pfx019,935C_CUSTKEYcustomerkey_idxn_leaf_pages133Number of leaf pages in the indexcustomerkey_idxsize161Number of pages in the indexlineitemGEN_CLUST_INDEXn_diff_pfx01148,390DB_ROW_IDlineitemGEN_CLUST_INDEXn_leaf_pages1,562Number of leaf pages in the indexlineitemGEN_CLUST_INDEXsize1,571Number of pages in the indexlineiteml_partkey_idxn_diff_pfx0118,356L_PARTKEYlineiteml_partkey_idxn_diff_pfx02149,721L_PARTKEY,DB_ROW_IDlineiteml_partkey_idxn_leaf_pages143Number of leaf pages in the indexlineiteml_partkey_idxsize225Number of pages in the indexlineiteml_shipdate_idxn_diff_pfx0115,745L_SHIPDATElineiteml_shipdate_idxn_diff_pfx02149,946L_SHIPDATE,DB_ROW_IDlineiteml_shipdate_idxn_leaf_pages134Number of leaf pages in the indexlineiteml_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_NAMEhtypeHISTOGRAMtpchordersO_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公众号
好文推荐
您阅读本篇文章共花了：
发表评论