07 MySQL-SQL高级处理

# 1.视图-View

## 1.1.自定义视图

- 视图:
    - 虚拟的表,本身不保存数据,不占磁盘空间
    - 封装一条SELECT,数据来源1或多张实际的表
    - 结构和真是表相同,可在视图上进行增删改查
    - 不能添加索引
- 优点
    - 简单:用户一次定义后,无需关心表结构
    - 安全:屏蔽敏感数据,让用户仅看到需要看到的数据
- 缺点
    - 迁移维护麻烦
    - 底层逻辑简化,可能是负责嵌套方式,会引起业务波动
    - 让业务人员缺失业务敏锐性
    - 视图在MySQL优化器中,代码执行路径不同,对性能影响无法估计
    - 会带来额外开销(比如使用临时表算法)

```sql
#语法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
 VIEW view_name [(column_list)]
 AS select_statement
 [WITH [CASCADED | LOCAL] CHECK OPTION]

-- 制定查询某个客户的视图
CREATE view prdouctormers
as  select cust_name,cust_contact 
from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'TNT2';

-- 使用视图
select * from prdouctormers;

-- 丢失视图
drop view prdouctormers;
```

## 1.2.系统视图

- MySQL系统自带视图
- 获取表的数据文件、索引文件的大小、碎片情况、表行数、自增列增长情况
- MySQL版本变化
    - 5.6：59张表
    - 5.7：61张表,10个InnoDB存储引擎临时表(数据字典表).51个Memory引擎临时表
    - 8.0
        - 引入了基于InnoDB的本地数据字典表,消除创建临时表
        - I_S中表被设计为数据字典表上的VIEW(某些还是MEM表)
        - 数据库表的元数据都存储在事务数据字典表中
        - 改进
            - 不用创建临时表,减少内存暴涨的可能(大量访问tables),但是访问速度没有5.7的快
            - 不用扫描文件系统目录以查找FRM文件

![22](https://img.sunrisenan.com/img/2024/03/19/125933566.png)

5.7与8.0

```sql
mysql5.7.22 
show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,

mysql8.0.15
  show create table information_schema.tables\G
*************************** 1. row ***************************
                View: TABLES
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `
```

```sql
-- 查询系统中所有视图对象
mysql> select table_schema,table_name,table_type from information_schema.tables where table_type like '%VIEW%';

--自带视图
(1)information_schema库,  系统视图，使用临时表算法
(2)sys库   使用合并表算法
```

### 1.2.1.I_S应用

```sql
(1)提供了访问系统“元数据”的视图。保存MySQL维护的其他数据库数据
(2)我们可以通过I_S和show更加方便的查询元数据信息。
(3)MySQL实例的百科全书,记录了数据库当中大部分我们需要了结的信息，比如字符集，权限相关，数据库实体对象信息，外检约束，分区，压缩表，表信息，索引信息，参数，优化，锁和事物等等。
```

### 1.2.2.I_S库表功能分类

![23](https://img.sunrisenan.com/img/2024/03/19/130126417.png)

1.字符集和排序规则相关的系统表

```sql
-- 
(1)CHARACTER_SETS ：存储数据库相关字符集信息
(2)COLLATIONS ：字符集对应的排序规则
(3)COLLATION_CHARACTER_SET_APPLICABILITY：字符集和排序规则对应关系.
```

2.权限相关表

```sql
SCHEMA_PRIVILEGES：提供了数据库的相关权限。mysql.db。
TABLE_PRIVILEGES:提供的是表权限相关信息，mysql.tables_priv 
COLUMN_PRIVILEGES:表可以清楚就能看到表授权的用户的对象
USER_PRIVILEGES:提供的是表权限相关信息，信息是从 mysql.user 表中加载的
```

3.存储数据库系统的实体对象表

```sql
COLUMNS：存储表的字段信息，所有的存储引擎

ENGINES：引擎类型，是否支持这个引擎，描述，是否支持事物，是否支持分布式事务，是否能够支持事物的回滚点

EVENTS ：记录MySQL中的事件，类似于定时作业

FILES ：这张表提供了有关在MySQL的表空间中的数据存储的文件的信息，文件存储的位置,内存表

PARAMETERS ：参数表存储了一些存储过程和方法的参数，以及存储过程的返回值信息。存储和方法在ROUTINES里面存储

PLUGINS ：基本上是MySQL的插件信息，是否是活动状态等信息。SHOW PLUGINS

ROUTINES：关于存储过程和方法function的一些信息，不过这个信息是不包括用户自定义的，只是系统的一些信息。

SCHEMATA：这个表提供了实例下有多少个数据库，而且还有数据库默认的字符集

TRIGGERS :这个表记录的就是触发器的信息，包括所有的相关的信息。系统的和自己用户创建的触发器。

VIEWS :视图的信息，也是系统的和用户的基本视图信息。
```

4.约束外键等相关表

```sql
REFERENTIAL_CONSTRAINTS：这个表提供的外键相关的信息，而且只提供外键相关信息

TABLE_CONSTRAINTS ：这个表提供的是 相关的约束信息

KEY_COLUMN_USAGE: 数据库中所有有约束的列都会存下下来
```

5.管理表

```sql
#系统重启的时候回重新加载的,也就是内存表.
#5.7版本在I_S,8.0在performance_schema
GLOBAL_STATUS      #全局系统状态
GLOBAL_VARIABLES   #全局系统变量
SESSION_STATUS     #会话状态
SESSION_VARIABLES  #会话变量
```

```sql
PARTITIONS:  MySQL分区表相关的信息
PROCESSLIST：show processlist其实就是从这个表拉取数据
INNODB_*:  INNODB存储引擎相关表
```

6.表信息和索引信息表

```sql
TABLES:系统数据库和用户创建的数据库
TABLESPACES:标注的活跃表空间,不提供关于innodb的表空间信息的
STATISTICS：这个表提供的是关于表的索引信息，所有索引的相关信息。
INNODB_INDEXES: innodb引擎索引信息
```

7.MySQL优化表

```sql
OPTIMIZER_TRACE ：提供的是优化跟踪功能产生的信息
PROFILING：深入的查看服务器执行语句的工作情况。
```

### 1.2.3.常用视图应用

- 一些表介绍对应show

![image-20240319130445723](https://img.sunrisenan.com/img/2024/03/19/130448102.png)

**1.processlist**

- 使用processlist表

```sql
-- 1.连接命令分类
select command, count(*)
from information_schema.processlist
group by command;

-- 2.连接IP排序
select ip, count(*) as num        from (                  select SUBSTR(host, 1, INSTR(host, ':') - 1) as ip, db , command, info                  from  information_schema.processlist                ) as a       group by ip order by num desc;

-- 3.查询Sleep连接
select ip, command, count(*) as num        from (                   select SUBSTR(host, 1, INSTR(host, ':') - 1) as ip, db , command, info                    from  information_schema.processlist                 ) as a        where command = 'Sleep'       group by ip, command order by num desc;

-- 4.kill连接,找出超过5分钟的线程
select concat("kill ",id,";") from information_schema.PROCESSLIST where host not in ('localhost','127.0.0.1') and Command != 'Sleep' and Time > 300 order by Time desc;

```

- show processlist

```sql
Id: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。
User: 就是指启动这个线程的用户。
Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
Command: 是指此刻该线程正在执行的命令。这个很复杂，下面单独解释
Time: 表示该线程处于当前状态的时间。
State: 线程的状态，和 Command 对应，下面单独解释。
Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

Commnad的值：
Binlog Dump: 主节点正在将二进制日志 ，同步到从节点
Change User: 正在执行一个 change-user 的操作
Close Stmt: 正在关闭一个Prepared Statement 对象
Connect: 一个从节点连上了主节点
Connect Out: 一个从节点正在连主节点
Create DB: 正在执行一个create-database 的操作
Daemon: 服务器内部线程，而不是来自客户端的链接
Debug: 线程正在生成调试信息
Delayed Insert: 该线程是一个延迟插入的处理程序
Drop DB: 正在执行一个 drop-database 的操作
Execute: 正在执行一个 Prepared Statement
Fetch: 正在从Prepared Statement 中获取执行结果
Field List: 正在获取表的列信息
Init DB: 该线程正在选取一个默认的数据库
Kill : 正在执行 kill 语句，杀死指定线程
Long Data: 正在从Prepared Statement 中检索 long data
Ping: 正在处理 server-ping 的请求
Prepare: 该线程正在准备一个 Prepared Statement
ProcessList: 该线程正在生成服务器线程相关信息
Query: 该线程正在执行一个语句
Quit: 该线程正在退出
Refresh：该线程正在刷表，日志或缓存；或者在重置状态变量，或者在复制服务器信息
Register Slave： 正在注册从节点
Reset Stmt: 正在重置 prepared statement
Set Option: 正在设置或重置客户端的 statement-execution 选项
Shutdown: 正在关闭服务器
Sleep: 正在等待客户端向它发送执行语句
Statistics: 该线程正在生成 server-status 信息
Table Dump: 正在发送表的内容到从服务器
Time: Unused
```

2.TABlES

```sql
#表结构
desc information_schema.tables; 
TABLE_SCHEMA       -- 表所在的库。
TABLE_NAME         -- 表名 
TABLE_TYPE         -- 表类型 
ENGINE             -- 存储引擎类型 
TABLE_ROWS         -- 数据行（粗略的） 
AVG_ROW_LENGTH     -- 平均行长度（字节） 
DATA_LENGTH        -- 数据长度 
INDEX_LENGTH       -- 索引长度 
DATA_FREE          -- 碎片量 
CREATE_TIME        -- 创建时间 
UPDATE_TIME        -- 更新时间 
TABLE_COMMENT      -- 注释

#应用
-- 1.统计前实例中业务相关的库和表的信息
select table_schema,group_concat(table_name),count(*) from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') group by table_schema;

-- 2.统计当前实例每个数据库的数据总量
select table_schema,sum(table_rows * avg_row_length + index_length)/1024/1024 as total_mb from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') group by table_schema;

-- 3.统计当前实例非innodb的表
select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine <> 'INNODB';

-- 4.查询有碎片的表信息
select table_schema,table_name ,data_free from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and data_free >0;

-- 5.mysql数据库表大小
select TABLE_NAME, concat(truncate(DATA_LENGTH/1024/1024/1024,2), 'GB') as data_size,concat(truncate(INDEX_LENGTH/1024/1024/1024,2), 'GB') as index_size from information_schema.TABLES  where table_schema not in ('sys','mysql','information_schema','performance_schema') order by data_length desc limit 20;

-- 6.查看数据库的数据
# test 为库名
SELECT
	CONCAT( table_schema, '.', table_name ) AS 'Table Name',
	table_rows AS 'Number of Rows',
	CONCAT( ROUND( data_length /( 1024 * 1024 * 1024 ), 4 ), 'G' ) AS 'Data Size',
	CONCAT( ROUND( index_length /( 1024 * 1024 * 1024 ), 4 ), 'G' ) AS 'Index Size',
	CONCAT( ROUND(( data_length + index_length )/( 1024 * 1024 * 1024 ), 4 ), 'G' ) AS 'Total' 
FROM
	information_schema.TABLES 
WHERE
	table_schema LIKE 'test';
```

3.TRIGGERS、views、ROUTINES、EVENTS应用

```sql
#5.7版本
use information_schema;   
select * from TRIGGERS;  
select * from EVENTS;
select * from VIEWS;
select * from mysql.proc; #存储过程
```

# 2.MySQL8.0 窗口函数

## 2.1.row_number()

## 2.2.rank()

## 2.3.dense_rank

## 2.4.lag和lead

## 2.5.CTE