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

文章目录

    • 获取对象的定义SQL语句
      • 列出库中的表和视图
      • 表的DDL语句
      • 索引的DDL语句
      • 视图的DDL语句
      • 物化视图的DDL语句
    • 获取统计信息的SQL语句
      • 表级统计信息
      • 索引统计信息
      • 列级统计信息
    • 获取执行计划的Explain语句
      • Explain
      • Explain Json
      • Explain 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_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

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/400503.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Java语言实现五子棋

Java语言实现五子棋

目录 内容 题目 解题 代码 实现 内容 题目 五子棋 使用二维数组,实现五子棋功能. 1.使用二维数组存储五子棋棋盘 如下图 2.在控制台通过Scanner输入黑白棋坐标(例如:1,2 2,1格式 表示二维数组坐标),使用实心五角星和空心五角星表示黑白棋子. 如下图: 输入后重新输出…
阅读更多...
解决方案——文本生成图像DF-GAN配置Oxford-102 Flower 花数据集全流程

解决方案——文本生成图像DF-GAN配置Oxford-102 Flower 花数据集全流程

目录 一、Oxford-102 Flower简介二、DF-GAN配置Oxford-102 Flower 数据集2.1、下载数据集2.2、配置数据集 三、修改代码四、资源下载 一、Oxford-102 Flower简介 Oxford-102 Flower是牛津工程大学于2008年发布的用于图像分类的花卉数据集&#xff0c;原论文链接&#xff1a;Au…
阅读更多...
适配器模式：转换接口，无缝对接不同系统

适配器模式：转换接口，无缝对接不同系统

文章目录 **一、技术背景与应用场景****为什么使用适配器模式&#xff1f;****典型应用场景包括但不限于&#xff1a;** **二、适配器模式定义与结构****三、使用步骤举例****四、优缺点分析****总结** 一、技术背景与应用场景 适配器模式在软件设计中扮演着桥梁角色&#xff…
阅读更多...
.zip文件如何在centos7解压

.zip文件如何在centos7解压

在CentOS 7中解压.zip文件&#xff0c;您可以使用unzip命令。如果您的系统上没有安装unzip工具&#xff0c;您首先需要安装它。以下是步骤指南&#xff1a; 安装unzip工具 打开终端并运行以下命令来安装unzip&#xff1a; sudo yum install unzip解压.zip文件 安装unzip之后&am…
阅读更多...
2023年12月 Python（六级）真题解析#中国电子学会#全国青少年软件编程等级考试

2023年12月 Python（六级）真题解析#中国电子学会#全国青少年软件编程等级考试

Python等级考试（1~6级）全部真题・点这里 一、单选题（共25题，共50分） 第1题 运行以下程序，输出的结果是？（ ） class A():def __init__(self,x):self.x=x
阅读更多...
leetcode hot100组合综合四

leetcode hot100组合综合四

本题中&#xff0c;是要求nums中求的总和为target的排列数&#xff0c;因为题中说了&#xff0c;元素顺序不同&#xff0c;则可以视为不同的结果之一。 所以&#xff0c;根据对背包问题的总结&#xff0c;本题中元素可以重复使用&#xff0c;是完全背包并且需要求排列数&#…
阅读更多...
多线程——threading和queue模块的理解。加实例+详解+思路

多线程——threading和queue模块的理解。加实例+详解+思路

并发&#xff1a;假的多任务 并行&#xff1a;真的多任务 实现多线程用——threading模块 import threading import timedef shuru():for i in range(1,4):print("正在输入")time.sleep(1) def shuchu():for i in range(1,4):print("正在输出")time.sle…
阅读更多...
通俗易懂的双亲委派机制

通俗易懂的双亲委派机制

当你超过别人一点点&#xff0c;别人会嫉妒你&#xff1b;当你超过别人一大截&#xff0c;别人就会羡慕你 据说给我点关注的都成了大佬&#xff0c;点关注的我都会私发一份好东西 ​​​​你得先知道 在介绍双亲委派机制的时候&#xff0c;不得不提ClassLoader&#xff08;类…
阅读更多...
Windows / Linux dir 命令

Windows / Linux dir 命令

Windows / Linux dir 命令 1. dir2. dir *.* > data.txt3. dir - list directory contentsReferences 1. dir 显示目录的文件和子目录的列表。 Microsoft Windows [版本 10.0.18363.900] (c) 2019 Microsoft Corporation。保留所有权利。C:\Users\cheng>dir驱动器 C 中…
阅读更多...
xxl-job架构原理讲解

xxl-job架构原理讲解

1、调度中心 调度中心是一个单独的Web服务&#xff0c;主要是用来触发定时任务的执行 它提供了一些页面操作&#xff0c;我们可以很方便地去管理这些定时任务的触发逻辑 调度中心依赖数据库&#xff0c;所以数据都是存在数据库中的 调度中心也支持集群模式&#xff0c;但是…
阅读更多...
mybatis数据操作语句

mybatis数据操作语句

//基于注解 Mapper public interface reboudapt {Select("select * from dept")List<dept> huoqudept();//删除语句Delete("delete from dept where id #{id}")void deletesc(Integer id);//增加语句Insert("insert into dept(name, create_t…
阅读更多...
【flutter】第一个flutter项目

【flutter】第一个flutter项目

前言 我们通过Android Studio来创建flutter项目。 安装dart和flutter插件 新版编译器需要先安装flutter插件才能构建flutter项目。 项目目录 我们基本就在lib中写代码 项目启动
阅读更多...
PiflowX-组件UnionAll

PiflowX-组件UnionAll

UnionAll组件 组件说明 Union多个输入源。输入源必须具有相同的字段类型。 计算引擎 flink 组件分组 common 端口 Inport&#xff1a;Any outport&#xff1a;默认端口 组件属性 名称展示名称默认值允许值是否必填描述例子inportsInports“”无否Inports string are…
阅读更多...
C# 使用onnxruntime部署夜间雾霾图像的可见度增强

C# 使用onnxruntime部署夜间雾霾图像的可见度增强

目录 介绍 模型信息 效果 项目 代码 下载 C# Onnx 使用onnxruntime部署夜间雾霾图像的可见度增强 介绍 github地址&#xff1a;GitHub - jinyeying/nighttime_dehaze: [ACMMM2023] "Enhancing Visibility in Nighttime Haze Images Using Guided APSF and Gradien…
阅读更多...
机器视觉系统选型-为什么还要选用工业光源控制器

机器视觉系统选型-为什么还要选用工业光源控制器

工业光源控制器最主要的用途是给光源供电&#xff0c;实现光源的正常工作。 1.开关电源启动时&#xff0c;电压是具有波浪的不稳定电压&#xff0c;其瞬间峰值电压超过了LED灯的耐压值&#xff0c;灯珠在多次高压冲击下严重降低了使用寿命&#xff1b; 2.使用专用的光源控制器&…
阅读更多...
day2：信号与槽

day2：信号与槽

思维导图 使用手动连接&#xff0c;将登录框中的取消按钮使用t4版本的连接到自定义的槽函数中&#xff0c;在自定义的槽函数中调用关闭函数 将登录按钮使用qt5版本的连接到自定义的槽函数中&#xff0c;在槽函数中判断u界面上输入的账号是否为"123",密码是否为"…
阅读更多...
【Java系列】JDK

【Java系列】JDK

目录 JDK介绍JDK版本系列文章版本记录JDK介绍 JDK是 Java 语言的软件开发工具包，主要用于移动设备、嵌入式设备上的java应用程序。JDK是整个java开发的核心，它包含了JAVA的运行环境（JVM+Java系统类库）和JAVA工具。 JDK版本 SE(JavaSE)，standard edition，标准版，是我们通…
阅读更多...
【MySQL】报错 Incorrect string value: ‘\xE5\xA4\xA9\xE5\x96\x9C‘ for column的解决方法

【MySQL】报错 Incorrect string value: ‘\xE5\xA4\xA9\xE5\x96\x9C‘ for column的解决方法

目录 解决方法如下&#xff1a;例如&#xff1a; 在向数据库中插入中文时遇到报错Incorrect string value: \xE5\xA4\xA9\xE5\x96\x9C for column &#xff0c;此时为数据库的编码格式有问题&#xff0c;可以按照如下方法修改 解决方法如下&#xff1a; 使用show create table…
阅读更多...
C++学习Day07之动态联编和静态联编

C++学习Day07之动态联编和静态联编

目录 一、程序及输出1.1 静态联编&#xff08;Static Binding&#xff09;1.2 动态联编&#xff08;Dynamic Binding&#xff09; 二、分析与总结 一、程序及输出 C 中的联编&#xff08;Binding&#xff09;分为动态联编&#xff08;Dynamic Binding&#xff09;和静态联编&a…
阅读更多...
关于本地docker启动xxl-job

关于本地docker启动xxl-job

之前通过github拉取xxl-job到本地启动&#xff0c;已经验证完了&#xff0c;主要要记住以下几个步骤: 1.拉取代码 GitHub地址&#xff1a;https://github.com/xuxueli/xxl-job Gitee地址&#xff1a;https://gitee.com/xuxueli0323/xxl-job 2.idea打开&#xff0c;找到tabl…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站