as mysql with 嵌套_MySQL总结

最新推荐文章于 2023-10-29 23:59:44 发布
最新推荐文章于 2023-10-29 23:59:44 发布
阅读量177 收藏
点赞数
文章标签： as mysql with 嵌套
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39854730/article/details/111645203
版权

MySQL基础回顾

1、group by 查询

select 字段名，分组名
from 表名
where 筛选条件  // 分组前筛选
group by 分组名
having 分组后筛选条件  //分组后筛选
order by 排序字段

2、sq99连接查询

select 字段名
from 表名 as 别名
【连接类型】join 表名 as 别名
on 连接条件
where 筛选条件
order by 排序字段

其中【连接类型包括】

  • inner //内连接，等值连接
  • left //外连接，主表有、从表没有；左表是主表
  • right //右边是主表
  • full
  • cross

3、子查询与联合查询

select * 
from 表名
where id in/any/all (子查询)

union [all]：联合查询把不同的查询结果进行并集。

4、delete与truncate区别

delete可以删除部分行，通过where进行筛选。

truncate则不可以，是清楚整张表，相当于格式化。

5、表的复制

create table my_copy like 目标表   //复制结构
create table my_copy 
select * from 目标表               //复制结构和

6、约束

  • 非空
  • 主键 //主键默认非空且唯一，自动会创建索引，可以组合
  • 唯一 //可以为空，可以组合
  • 检查
  • 默认
  • 外键 //作用于表上，而不是列上。会自动创建索引。
【CONSTRAINT 约束名】  约束类型（字段名）

7、视图

  • 多个地方用到同样的查询且SQL相对复杂。
  • 不保存结果，只保存SQL。

优点：重用SQL，保护和封装数据。

可以进行插入或者修改，不建议这样，视图最好只有‘只读’权限。

create view 视图名
as
查询语句；

8、级联

  • 级联删除
  • 级联置空

9、变量

  • 系统变量：全局变量、会话变量
  • 自定义变量：用户变量、局部变量
//查看系统变量
SHOW [GLOBAL|SESSION] VARIABLES;  //不填写默认是会话变量

//查看指定系统变量
SELECT @@GOLOBAL|SESSION.系统变量名；   //不填写默认是会话变量

//设置指定系统变量
SET @@GOLOBAL|SESSION.系统变量名 = 值；   //不填写默认是会话变量

变量修改后重启服务则恢复默认初始值，除非修改配置文件。

10、存储过程和函数

可以理解为一组方法

delimeter $
create procedure 存储过程名（IN|OUT 变量名 变量类型）

begin
    SQL语句
end $

//调用
CALL 存储过程名（参数列表）

函数：有且只有一个返回

create function 函数名（参数类型） returns 返回类型

begin
    函数体
end

第一章 MySQL架构与历史

1、MySQL逻辑架构图

6f8440d5979e6d32027b4514fb3ad27e.png
  • 最上层：比较通用的一个设计。
  • 第二层：核心功能处在这里，包括查询解析、分析、优化、缓存和所有内置函数。跨存储引擎的功能也在这一层实现，包括存储过程、视图、触发器等。
  • 最下层：存储引擎，负责数据的存出和提取。不同存储引擎各有自己的优劣势。通过提供存储引擎API，来屏蔽不同存储引擎的差异。

连接与鉴权

每个客户端都会在服务器进程中拥有一个线程，较高版本的MySQL版本提供了线程池插件，实现用较少的线程来支持大量的连接。

连接后，执行某操作还会进行鉴权。

优化与执行

第二层会解析并分析查询语句、优化查询语句、看是否命中缓存等。

2、并发控制

主要讲服务器层面 与 存储引擎层面 的并发控制。

读写锁

  • 共享锁，又称作 读锁。
  • 排它锁，又称作 写锁。

锁粒度

  • 行级锁：开销大、但是并发量高。大部分数据库采用这种形式。在存储引擎层面实现。
  • 表锁：开销小、但是并发量低。在服务器层面实现，需要手动加锁来锁定整张表。

3、事务

  • 原子性：一起成功 or 一起失败。
  • 一致性：数据库会从一个一致性状态 到达 另外一个一致性状态。
  • 隔离性：在一个事务结束之前，其他事务对其修改是看不到的。
  • 持久性：一旦事务提交，其数据会持久的存在数据库中。

事务需要额外的很大开销，所以说如果不需要事务，可以选择不支持事务的存储引擎。当前不支持事物的存储引擎，也可以通过LOCK TABLES为程序提供一定的保护（服务器层面）。

隔离级别

SQL中定义了四种隔离级别，隔离级别越低，开销越低，并发越高。

9232387fc57082c55d127a6c42e2a3b0.png

大多数数据库的隔离级别是 读已提交，但是MySQL采用 可重复读。可重复读 会带来幻读这一问题，MySQL采用多版本并发（MVCC）来解决这一问题。

死锁与日志

两个事务互相等待对方释放资源。InnoDB处理方式，持有最少行级排它锁的事务进行回滚。

事务日志可以提高事务性能，修改数据只需要修改内存中数据，并且把修改记录行为记录在日志中，持久化在硬盘中即可。

4、多版本并发控制MVCC

通过增加两个隐藏列用于解决幻读情况。两列都是全局自增系统版本号。

5、存储引擎

InnoDB: 支持事务。聚簇索引。

MyISAM: 不支持事务（行级锁），可以在服务器层面锁定整张表。

存储引擎选择：

  • 1、是否支持事务。不需要的情况下可以选择MyISAM,比如日志系统。
  • 2、备份
  • 3、崩溃恢复
  • 4、特有特性

常用SQL语句：

ALTER TABLE mytable ENGINE = InnoDB;  //修改表的存储引擎

mysqldump //备份数据库

参考文章：

数据库事务隔离级别 - 分析脏读 & 不可重复读 & 幻读​www.cnblogs.com
8bac036e759b4517731005b51c803299.png

第二章 MySQL基准测试

1、测试目标

  • 吞吐量 ：单位时间内数据库处理的事务量。
  • 响应时间或延迟 : 可以拿95%的相应时间来做参考。
  • 可扩展性：

2、设计基准测试

首先是提出问题并且明确目标

3、基准测试工具

集成测试工具：测试整个Web服务接口

  • ab
  • http_load
  • JMeter

单组键测试工具：直接测试MySQL

  • mysqlslap ：
  • super smack ：一款用于MySQL和PostgreSQL的基准测试工具。
  • sysbench : 一款多线程系统压测工具。

第三章 服务器性能分析

1、问题：

  • 如何确认服务器达到了最佳性能状态。
  • 找出某条语句为什么执行的不够快。
  • 如何解决这些疑难杂症。

常用指令：

SHOW VARIABLES LIKE 'PROFILING';

SET PROFILING = 1;

SHOW PROFILES;

SHOW PROFILE FOR QUERY 1;

SHOW STATUS;

SHOW PROCESSLIST;

第四章 Schema与数据类型优化

1、Schema涉及原则

  • 更小的通常更好。
  • 简单就好： 整形比字符串的性能好，所以日期、时间和IP等，最好用整形存储。
  • 尽量避免NULL： 对建立索引不利，索引列最好不要有NULL。

类型：

整形：tinyint、smallint、mediumint、int、bigint
实数：decimal(精度高、需要存储空间大)、float、double
字符串：varchar(变长、字符串越短，占用空间越小；适合用在修改少、最大长度远远大于平均长度场景下)
       char（定长，适用于经常修改场景，密码这种定长场景、效率高）
BLOB与TEXT: 尽量少使用。
ENUM：效率比较高。
时间与日期类型：DateTime-效率低点，存储时间跨度长；TimeStamp:1970-01-01以来的，不同时区看到值不同
Bit：

2、范式与反范式涉及

  • 平衡存储与查询性能的杠杆
  • 是否引入缓存表与汇总表
  • ALTER TABLE是非常耗时的，要注意

第五章 创建高性能索引

1、索引基础

  • BTree索引
  • Hash索引
  • full-text全文索引
  • R-Tree索引

2、是否创建索引情况

  • 常用查询和排序的字段需要创建。
  • 数据量小，区分度低，常修改的则不要建索引。

3、执行报告的字段类型

  • id 相同则按序加载，不同则id越大，优先级越高。
  • select_type 查询类型，主要用于区别普通查询，联合查询，子查询等复杂查询。
    • SIMPLE
    • PRIMARY //带有子查询的最外层
    • SUBQUERY
    • DERIVED //派生出来的
    • UNION
    • UNION RESULT
  • table 哪张表
  • type 访问类型
    • system // 一张表且只有一条数据
    • const // 唯一索引，查询一条数据
    • eq_ref // 外键
    • ref // 非唯一性索引扫描，返回匹配某个单独值的所有行。
    • range // 一个索引的范围 30-60之间等
    • index // 索引读全部
    • all //直接全表，没有走索引
  • possible_keys //显示可能应用在这张表上的索引，一个或多个。
  • key // 实际用到的索引。覆盖索引（查询的col1,col2...与联合索引的大小和顺序相同）
  • key_len // 索引字段的最大可能长度。越短越好。
  • ref // 索引中用于筛选的值
  • rows // 每张表有多少行被优化器查询
  • extra // 额外重要的信息
    • using filesort // 排序时，索引没用上，需要内存内单独排序。
    • using temporary // 使用了临时表保存中间结果，对查询结果排序时常用到和group by。
    • using index // 表明使用到了覆盖索引。若同时出现using where,代表索引被用来执行值的查找；若没有出现，代表索引用来读取数据而非执行查找动作（即不去数据文件中）。
    • using where //表示使用到了where筛选
    • using join buffer //使用了连接缓存
    • impossible where // 筛选条件总是false
    • select tables optimized away //
    • distinct // 匹配一个值后，不再查找同样的值

4、索引案例分析

最左匹配原则
索引列不要设计计算、类型转换（手动或自动）等操作
范围之后的索引全部失效
尽量使用覆盖索引进行查询
如果左边是通配符，最好采用覆盖索引查，type级别是index。
字符串不加单引号索引失效（相当于在索引字段上进行类型转换）
少用or，用它链接时，索引失效。
不等于、IS NULL、IS NOT NULL会使索引失效，尽量避免。

左连接给右表加索引
小结果集驱动大结果集
优先优化嵌套循环的内层循环


小表驱动大表，使用in
大表驱动小表，使用exist     //要深刻理解in与exist的区别

5、Order By的优化

排序方式：

  • 文件排序 using filesort
  • 索引排序 using index

文件排序两种算法：

  • 双路算法、两次I/O
  • 单路算法，一次I/O
单路算法，一次I/O，但是耗内存，是把所有查询的字段加载到buffer，按照排序列进行排序。
所以要把握好buffer的大小，避免多次I/O加载。

调优步骤

  • select * 是大忌讳，需要哪些字段就查哪些字段。
    • Query的字段大小总和小于max_length_for_sort_data时，且不是TEXT|BLOB类型时，会采用优化后的排序方法，即单路排序算法。
    • 两种算法都可能超过sort_buffer的值，导致产生合并排序。
  • 尝试提高sort_buffer_size的值。
  • 尝试提高max_length_for_sort_data的值。但是设置的太高，又可能触发sort_buffer。。。

Group By的优化与Order By基本相同。不同在于

  • 先排序再分组
  • 用不上索引时，适当调大系统参数
  • where是分组前筛选，having是分组后筛选

慢查询日志

  • 开启慢查询日志
  • 设置阈值、慢查询日志文件位置
  • 查看相关信息
  • 工具：mysqldumpslow

6、show profile

show profiles;

show profile cpu, block io for query id;

7、读写锁

MyISAM（偏读）：给表加读锁后； show open tables ：查看表的锁状态。

  • 当前session可以读。
  • 当前session写自己表出错。
  • 当前session读其他表出错。
  • 其他session可以读。
  • 其他session若修改，则会一直阻塞。

给表加写锁

  • 当前session可以写。
  • 当前session可以读。
  • 当前session读其他表出错。
  • 其他session读则阻塞。
  • 其他session写则阻塞。

InnoDB：支持事务、行级锁。

间隙锁：修改字段的条件是一个范围，那么会对这个范围的所有行加锁，包括不存在的行。若这时有其他session进行增加数据，则会阻塞。

// 锁定一行
select * from table where xxx  for update;

8、主从复制

weixin_39854730
关注
as mysql with 嵌套_使用WITH AS提高性能简化嵌套SQL
weixin_29707757的博客
03-03 1345
一．WITH AS的含义WITH AS短语，也叫做子查询部分(subquery factoring)，可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则...
java mysql事务嵌套_事务之六：spring 嵌套事务
weixin_35063366的博客
01-27 384
一、基本概念二、 嵌套事务示例2.1、Propagation.REQUIRED+Propagation.REQUIRES_NEWpackagedxz.demo1;@Servicepublic class ServiceAImpl implementsServiceA {@AutowiredprivateServiceB serviceB;@AutowiredprivateVcSettleMainM...
as mysql with 嵌套_Mysql在嵌套循环中选择
weixin_30253461的博客
01-17 167
I have a table named as People:id | name |parent_id---+------+---------1 | John | 02 | Jane | 13 | James| 14 | Jack | 05 | Jim | 46 | Jenny| 4So john is parent of Jane and James. Tree goes like...
使用WITH AS提高性能简化嵌套SQL
weixin_30339457的博客
12-23 71
http://www.cnblogs.com/fygh/archive/2011/08/31/2160266.html 特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高， 所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将 WITH AS...
With...as用法(mysql)
Joieeee的博客
10-28 5483
WITH AS短语也叫子查询部分，用来定义一个SQL片段，且该SQL片段会被整个SQL语句用到，这个语句算是公用表表达式(CTE)。 eg： with A as (select *from product) select *from A 先执行select *from product得到一个结果，将这个结果记录为A，再执行select *from A语句。 ...
mysql中with...as用法
drhrht的博客
03-22 3909
最近无意中接触到了一篇文章，里面写了一个SQL的用法，是with…as，中午抽空记录一下 用MySQL试了一下，发现并不支持该语法（版本:5.7） 于是换作以下（版本:8.0）不会报错： 总结一下with的用法，类似生成一个临时的表，和如下红框里的操作并无异样 感觉是个鸡肋语法…翻遍了mysql文档也没找到该语法到底是哪个版本开始支持的 更新大佬的原理链接—> ??? MariaDB表表达式(2)：CTE - 骏马金龙 - 博客园 ...
mysql with rollup_MySQL-with rollup函数运用
weixin_29149577的博客
01-18 735
如果想在下面这个表下面添加一行 总计 数据行SQL代码怎么实现 并且根据9月金额进行城市降序 总计置于底部呢MySQL提供了 group by with rollup 函数进行group by 字段的汇总但是order by 互斥的不能同时用第一步还是是先计算各城市每个月的金额SELECT b.城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年...
mysql 嵌套集合_分层数据 Hierarchical Data 探索 (3.嵌套集合模型) 无限极分类
weixin_34765290的博客
02-01 328
分层数据Hierarchical Data探索(例如：无限级分类、多级菜单、省份城市)引言第一篇 分层数据Hierarchical Data探索(1.递归) 已经介绍了分层数据以及使用递归算法实现了无限极分类，但是递归即浪费时间，又浪费空间(内存),尤其是在数据量大的情况下效率显著下降。第二篇 分层数据Hierarchical Data探索(2.邻接表模型) 介绍了一种数据模型邻接表模型来实现，但...
mysql 存储过程 嵌套if_mysql存储过程if嵌套if的写法
weixin_30131185的博客
01-18 2132
类似语法上的问题，其实可以参考一下手册中的说明和例子。MySQL 5.4 Reference Manual12.8.6.1. IF StatementIF search_condition THEN statement_list[ELSEIF search_condition THEN statement_list] ...[ELSE statement_list]END IFIF impleme...
MySQL----with as用法
Mac_qq的博客
03-22 2872
3.如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询），注意：只要后面有引用的就可以，不一定非要在主查询中引用，比如后面的with查询也引用了，也是可以的。1.使用with子句可以让子查询重用相同的with查询块,通过select调用（with子句只能被select查询块引用），一般在with查询用到多次情况下。但同一with子句内部不能嵌套with子句。
MySQL - WITH...AS 创建临时表复用子查询
陆氪和他的那些代码
05-21 4532
当我们遇到复杂 SQL 子查询的时候，发现表经常被复用的情况，按照传统的写法，就需要子查询 SQL 重复写，这样 SQL 很不优雅，今天我们用 WITH AS 来解决这个问题！（MySQL 8.0+ 开始支持） WITH t2 AS( WITH t1 AS( SELECT id, visit_date, people FROM Stadium WHERE people >= 100 ) SELECT id, visit_date, people, @gi.
MySQL WITH AS及递归查询
假女吖的博客
10-29 781
WITH是 SQL 中的一个关键字，用于创建临时表达式（也称为 Common Table Expression，CTE），它允许你在一个查询中临时定义一个表达式，然后在后续的查询中引用它。理解：当我们使用WITH AS他会帮我们创建一个临时的表，。当我们使用这个临时表二次以上时，他的效率是会比我们原本写两个一模一样的子查询是要搞的。
mysql with as 递归_SQL递归查询(with as)
weixin_33894125的博客
01-18 960
Id Pid DeptName lvl----------- ----------- -------------------------------------------------- -----------1 研发部...
MySQL with、union all语句
Gan_1314的博客
06-20 1111
MySQL with、union all语句
MySQL8.0+问题（WITH查询，UNION ALL以后执行结果出错）
weixin_40649680的博客
08-16 1726
MySQL8.0+问题（WITH查询，UNION ALL以后执行结果出错）
Mysql子查询
银的博客
10-14 753
子查询分类 按照子查询结果集的行列数的不分为4种 标量子查询（结果集中只有1行1列） 列子查询（结果集中只有1列多行） 行子查询（结果集中只有1行多列） 表子查询（结果集一般为多行多列） 按子查询出现在主查询中的不同位置分为4中 select后面：仅支持标量子查询 from后面：支持表子查询 where/having后面：支持标量、列、行子查询 exists后面（相关子查询）：支持表子查询 exists子查询 先执行主查询，然后将主查询的结果根据子查询进行过滤，子查询中涉及到主查询中用到的字
MySQL 查询与高级查询（多表、嵌套和正则表达式）
03-02
Re： MySQL 查询与高级查询（多表、嵌套和正则表达式） （查询的能力，显示DBA的功力）============================================= 数据库管理系统的个最重要的功能就是数据查询，数据查询不应只是简单查询数据库中存储的数据，还应该根据需要对数据进行筛选，以及确定数据以什么样的格式显示。 MySQL提供了功能强大、灵活的语句来实现这些操作，本章将介绍如何使用 SELECT 语句查询数据表中的一列或多列数据、使用集合函数显示查询结果、连接查询、子查询以及使用正则表达式进行查询等。------------------------------------  
mysql with as 用法
最新发布
10-09
MySQL 中的 `WITH` 关键字，也称为 Common Table Expression (CTE)，是一种强大的临时表创建工具，用于组织复杂的查询逻辑。它允许你在主查询之前定义一系列中间结果集，这在处理递归查询、子查询嵌套过深，或者需要...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值