【MySQL】学习笔记

已于 2023-03-22 18:34:01 修改
阅读量410 收藏
点赞数
文章标签： mysql 学习 数据库
于 2022-09-27 00:46:34 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jams3368/article/details/127062668
版权

SQL语句执行顺序

  • 案例

    首先，我们先看下如上SQL的执行顺序，如下：

1.首先执行 FROM 子句, 从学生成绩表中组装数据源的数据。
2.执行 WHERE 子句, 筛选学生成绩表中所有学生的数学成绩不为 NULL 的数据 。
3.执行 GROUP BY 子句, 把学生成绩表按 “班级” 字段进行分组。

4.计算 avg 聚合函数, 按找每个班级分组求出 数学平均成绩。
5.执行HAVING子句, 筛选出班级 数学平均成绩大于 75 分的。

6.执行SELECT语句，返回数据，但别着急，还需要执行后面几个步骤。
7.执行 ORDER BY 子句, 把最后的结果按 “数学平均成绩” 进行排序。
8.执行LIMIT ，限制仅返回3条数据。结合ORDER BY 子句，即返回所有班级中数学平均成绩的前三的班级及其数学平均成绩。

SQL一些容易遇到的问题

  1. 可以先通过where 过滤，再进行分组
  2. 聚合函数 出现在select 或者 having
  3. group by 可以有多个字段
  4. on 是首要筛选的条件，where对此进行虚表过滤。on优先级最高。
  5. having过滤条件在聚合函数之后。也就意味着可以过滤聚合函数
  6. 遇到一些不正常走索引的可以使用 force index() 来强制走索引
  7. explain 查看执行计划，
  8. show profiles 查慢查询日志(set profiling = 1;set long_query_time = 0; )

一条SQL是如何执行的


MyIsam 与 Innodb 区别

  • MyIsam 不支持事务，不支持行锁
  • MyIsam 表的行数都会自己做存储（查MyIsam 表的行数的时候，会比较快）
  • innodb 主键索引存储整行的数据；其他二级索引存储【索引数据与主键ID】
  • Innodb 的二级索引 存的是【索引数据与主键ID】，通过主键索引来查询到这个行数据

更新操作详解

1.更新之前会先进行查询
2.拿到行数据之后，用执行器调用存储引擎的接口，进行更新操作。
3.更新的操作会设计一些日志WAL

Write Ahead Log简称WAL，在分布式存储系统中的元数据更新中应用得十分广泛。WAL的主要意思是说在将元数据的变更操作写入到持久稳定的db之前，先预先写入到一个log中，然后再由另外的操作将log apply到外部的持久db里去。这种模式会减少掉每次的db写入操作，尤其当系统要处理大量的transaction操作的时候，WAL的方式相比较于实时同步db的方式有着更高的效率。

日志分类

redo log

  • 空间有限，只有4G
    在这里插入图片描述

在这里插入图片描述

bin log

  • 不是环形的
  • 磁盘上的全量日志
  • 只要磁盘够大，你只管追加操作就行了

MySQL如何做到崩溃恢复

  • 奔溃提交涉及到：两阶段提交
  • 顺序：
    1.首先记录一个redo log
    2.in-> redo prepare -> out 其中一个状态崩溃了都无法恢复
  • bin log 可日志回放
    在这里插入图片描述

MySQL执行计划

  • Extra：Using index 表示走了覆盖索引，不需要回表
    在这里插入图片描述

主键索引存储结构

在这里插入图片描述

二级索引存储结构

在这里插入图片描述

联合索引存储结构

  • 比较占用空间
    在这里插入图片描述

优化案例

视频

  1. 强制走索引
  2. 走联合索引
  3. 拒绝回表，小表驱动大表（翻页）

数据库的优化，大多数都在优化IO

索引下推（ICP）

https://www.bilibili.com/video/BV12V4y157yi/?spm_id_from=333.999.0.0&vd_source=0722208728783f1453516ee9e0d6ca3f

  • 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后回表，判断数据是否符合条件 。
  • 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
  • ICP特点：先判断索引列是否满足条件，不满足则忽略。

在开始之前先先准备一张用户表(user)
其中主要几个字段有：id、name、age、address。
建立联合索引（name，age）。
假设有一个需求，要求匹配姓名第一个为陈的所有用户，sql语句如下：

SELECT * from user where  name like '陈%'

根据 “最佳左前缀” 的原则，这里使用了联合索引（name，age）进行了查询，性能要比全表扫描肯定要高。

问题来了，如果有其他的条件呢？
假设又有一个需求，要求匹配姓名第一个字为陈。
年龄为20岁的用户，此时的sql语句如下：

SELECT * from user where  name like '陈%' and age=20

这条sql语句应该如何执行呢？
下面对Mysql5.6之前版本和之后版本进行分析。
Mysql5.6之前的版本
5.6之前的版本是没有索引下推这个优化的，因此执行的过程如下图：

会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,1，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。

Mysql5.6及之后版本
5.6版本添加了索引下推这个优化，执行的过程如下图：

InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

explain

关于 in 的顺序问题 解决方案

在这里插入图片描述

MVCC

https://www.bilibili.com/video/BV1864y1976i/?spm_id_from=333.337.search-card.all.click&vd_source=0722208728783f1453516ee9e0d6ca3f

Mvcc的实现主要是由隐藏的三个字段，undolog, read view来实现的。

隐藏字段

在这里插入图片描述

undoLog

在这里插入图片描述

流程步骤：

  1. 事务1插入数据
    在这里插入图片描述
  2. 事务2更新name
    在这里插入图片描述
  3. 事务3更新age
    在这里插入图片描述

Read view

在这里插入图片描述
在这里插入图片描述
流程：
在这里插入图片描述
在这里插入图片描述
具体的比较规则：
在这里插入图片描述
在这里插入图片描述

总结
在这里插入图片描述

全表扫描

多次IO

在这里插入图片描述

索引

创建索引，相当于是给表中的字段创建了一个目录
在这里插入图片描述

索引文件

  • innodb:数据和索引存储在一个文件
  • MyIsam: 数据和索引分开存

索引的分类(5)

  • 主键索引
  • 普通索引
  • 唯一索引
  • 联合索引 （建议不要超过5个列）
  • 全文索引（MyIsam）

数据结构-树

在这里插入图片描述

在这里插入图片描述

  • 平衡二叉树（动态的二叉搜索树）

在这里插入图片描述
在这里插入图片描述

  • 红黑树
    在这里插入图片描述
    在这里插入图片描述

  • B树
    在这里插入图片描述

  • B+树
    在这里插入图片描述

Update 锁

  • 如果一个字段上是有索引的，那么在一个事务下会开启行锁
  • 没有索引则开启表锁
  • 所以在update的时候，尽量走索引的列
  • 它是根据索引加的锁，锁的是索引。
小王的冬
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
学习笔记-MySQL
李唐敏民的博客
02-04 304
数据库 DataBase(DB) 用于存储和管理数据的仓库 特点: 持久化存储数据 方便存储和管理数据 使用了统一的方式操作数据库 MySQL的安装 卸载 配置 安装 卸载 配置 启动MySQL服务 MySQL登录 mysql -uroot -proot mysql -hip -proot mysql --host=ip --user=root --password=root MySQL退出 exit quit MySQL目录结构 MySQL安装目录 ​ 配置文件
MySQL学习笔记
fantasysolo的博客
07-23 229
MySQL学习笔记 概念 数据库系统 就是一个存储数据的仓库,为了方便数据的存储和管理,它根据特定的规律存储在磁 盘上 不必关心数据存储在磁盘的位置,可以直接通过数据库管理系统来查询表中的 数据   范式 合理的创建数据结构,使数据结构更加清晰 第一范式(1NF): 要求原子性(不存储重复列,且不可再分) 第二范式(2NF): 在满足 1NF 的前提下,字段不能部分依赖主...
sql分组查询
菜鸡的博客
10-11 5258
sql分组查询
【数据库】MySQL知识点总结（一）：基础操作
书山有路，学海无涯。记录成长，追逐梦想
06-07 1354
在学习 MySQL 的基础操作之前，我们应该首先了解一下 MySQL 中的语句执行顺序。
mysql 有一张学生成绩表，里面包含各个班级得学生成绩，我需要按照班级分组后，得到每个班级中分数最低得10名同学信息，
最新发布
qq_33192454的博客
07-05 172
mysql 有一张学生成绩表，里面包含各个班级得学生成绩，我需要按照班级分组后，得到每个班级中分数最低得10名同学信息，我的mysql 不支持 窗口函数和变量，这个查询将返回每个班级中分数最低的10名学生的信息，包括他们的班级、姓名和成绩。如果你的 MySQL 不支持窗口函数和变量，你可以通过子查询和连接来实现类似的功能。这个查询会返回每个班级中成绩最低的10名学生的信息，包括他们的班级、姓名和成绩。这个查询将返回每个班级中分数最低的10名学生的信息，包括他们的班级、姓名和成绩。语句来实现你的需求。
记录一下MySql的一些查询语句使用案例
xiaoyezhiei的博客
07-06 523
MySql简单查询案例
Mysql数据库查询之分组查询
weixin_69026046的博客
11-14 510
语法:SELECT 字段 1,字段 2 FROM 表名 where 条件 GROUP BY 分组字段 HAVING 条件 order by;
SQL SERVER查询学生表中 根据班级 分组，查询班级总人数，男生人数，女生人数
讯达网络科技的博客
04-29 8003
SQL SERVER查询学生表中 根据班级 分组，查询班级总人数，男生人数，女生人数
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
MySql学习笔记（详细）
05-18
### MySQL学习笔记（详细） #### 一、数据库操作 MySQL是一种关系型数据库管理系统，通过一系列命令来进行数据库的管理和数据的处理。以下是一些常见的数据库操作命令： - **连接MySQL服务器**： - `mysql -u ...
mysql学习笔记
04-20
个人学习mysql的一些笔记，包含一些基本的操作以及讲解，希望可以帮到一些初学者
《MySQL必知必会学习笔记》：数据分组
wojiushimogui的博客
03-28 973
数据分组根据上一篇博文中所看到的，SQL聚集函数可用来汇总数据。这使我们能够对行进行计数，计算和与平均数，获得最大和最小值而不用检索所有数据。目前为止的所有计算都是在表的所有数据或匹配特定的where子句的数据上进行的。例如，在一门公共选修课中，我们需要统计来自每个班的人数各有多少个？应该怎么做呢？ 可能有的人像下面这样做：select class,count(*) from student3 w
如何对班级表中的班级名称进行分组查询？
qq_44745905的博客
08-13 2885
@R星校长 分组查询的单独使用 分组查询的关键字是Group By，查询的是每个分组中 首次出现的一条记录。 语法规则为： SELECT 字段名 FROM 表名 ORDER BY 字段名 ; 。 例如： 现有user表数据： id name sex 1 Tom 男 2 Nancy 女 3 Allen Null 4 Jason 男 我们对表中数据的性别进行分组查询： 可以看出，返回了3条记录，分别是sex字段值为Null、女、男的记录，查询结果按照sex字段中不同的值进行.
Mysql语句执行顺序
weixin_41943486的博客
08-03 1902
Mysql语句执行顺序
每日10道mysql
liugg2016的博客
08-20 699
1、餐馆菜单输出最贵的五道菜     SELECT food_name FROM table ORDER BY food_price LIMIT 5; 2、学校中有一个年级表，有两个核心字段，student_id和class_id，把年级中大于30人的班级查出来 SELECT class_id FROM table GROUP BY class_id HAVING COUNT(*)>3...
MySQL教程（18）分组
.... 永远年轻，永远热泪盈眶
04-06 201
MySQL GROUP BY 语句 GROUP BY 语句根据一个或多个列对结果集进行分组。 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。 GROUP BY 语法 SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY co...
MySQL知识学习——第十三天：分组数据
qq_43569111的博客
06-07 554
前言 又是新的一周，继续努力呀~ 数据分组 怎么理解数据分组呢？举个简单的例子，一个学校有一个高三年级的学生名单，整个年级有三个班，这样我们就可以按照班级对数据进行分组。 个人理解的话，分组就是将具有某个相同值的数据分离开。 创建分组 那么如何进行分组呢？分组是在SELECT语句的GROUP BY子句中建立的。 看下面的例子，针对下面的表格（student） 我们对上面的表进行分组。 group by子句在后面要跟列名，它会把该列中具有相同的值的记录合成一组。 group by 子句可以包含任意数目的
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值