mysql 思维导图模板_ProcessOn思维导图、流程图

常用SQL语句

group by

关于group by的用法原理

分组规定

MySQL查询中group by语句的使用场景和用法

select

select “常数”as 字段名, 字段名 from 表名

删除重复行：select distinct 字段名 from 表名

select 字段可以直接运行算术运算符；select 字段名*2 from 表名

聚合函数

count：使用count(*)会包含null的数据行数。使用count(字段)则不会

使用聚合函数会把null列排除

使用聚合函数删除重复值：select count(distinct 字段名)

having和where

where：指定行所对应的条件

having：指定组所对应的条件

数据更新

从其他表复制数据：insert into 表名(字段) select 字段 from 表名

删除数据：delet from 表名 where 条件

插入数据：insert into 表名(字段) values(值)

更新数据：upate 表名 set 字段 = 值

集合关系运算

并集

union 相同行只显示一行

union all 相同的行全部都像显示

差集

待解

交集

待解

子查询：一次性视图

exits：检测行是否存在返回 true 或者false 不返回实际数据

使用exist要记得加条件，条件要跟外查询有关联

case 用法

搜索

case when 条件 then 表达式 when 条件 then 表达式 else 表达式 end

简单

case 列 when 条件 then 表达式 else 表达式 end

with rollup

解析：在group by分组之后，再对不是group by字段进行统计

语法：select 列名,... from 表名 group by 列名 with rollup

注意事项

对于group by 的列不做任何处理

不是group by 的列都会进行 rollup 处理

check：待解

连接查询

内连接

inner join

没有约束条件，会产生笛卡尔积

外(左/右)连接

事务

特性

原子性(atomicity)：通过undo log

隔离性(isolation)

一致性(consistency)

持久性(durability)：通过redo log

隔离级别

未提交读：可以读取到未提交的事务

读已提交：读取已提交的事务数据

可重复读：同一个事务执行过程中，看到的数据跟事务开始时看到的数据是一样的

可串行化

并发带来的问题

脏读

不可以重复读：针对update、delete

丢失修改

幻读：针对insert

锁：很多知识没搞明白

锁粒度

表锁

特点

级别

表锁

元数据锁(MDL)

行锁

record lock

gap lock

next-key lock

两阶段锁

级别

共享行级锁(Share Lock)：读锁

排他行级锁(Exclusive Lock)：写锁

全局锁：全局逻辑备份

命令：flush tables with read lock

作用：数据库备份时，让整个库处于只读状态

锁分类

意向共享锁

共享锁

排他锁

意向排他锁

MVCC：多版本并发控制

select

系统版本号小于或等于当前事务版本号

行的删除版本号要么未定义，要么大于当前版本号

insert

delete

update

MyISAM和InnoDB

MyISAM

InnoDB

注意事项

事务提交后，才会释放锁

死锁

日志

redo log(重做日志，存储引擎模块)：

作用：确保事务的持久化

事务开始的时候记录

bin log(归档日志，server层面)：

作用：复制和恢复数据(主从复制)

事务提交的时候记录

模式

statement：基于SQL语句的复制

row：基于行的复制，保留改动前后的

mixed：混合模式

undo log(回滚日志，存储引擎)

作用：回滚和多版本控制(MVCC)

通过两段提交保证redo log 和 bin log 数据的一致性

事务开始，redo log 写盘，事务进入prepare 状态

bin log 写盘，事务进入commit 状态

每个事务的bin log末尾记录一个XID event，标志事务是否成功

bin log 和 redo log 的区别

redo log

存储引擎层面

物理日志，记录该数据页更新的内容

循环写，空间大小固定，内容被覆盖

作为异常宕机或者介质故障后的数据恢复使用

bin log

server 层面

逻辑日志，记录更新语句的原始逻辑

内容是追加写，不会被覆盖

作为恢复数据使用，主从复制搭建

注意事项

redo log 刷到磁盘后，就无效了。文件内容会被覆盖

bin log 内容是叠加的

常用命令

查看存储引擎：show engines

查看默认存储引擎：show variables like '%storage_engine%'

查看表的存储引擎：show table status like "表名"

执行顺序

from

where

group by

huaving

select

order by

建表优化

选择正确存储的简单的最小的数据类型

尽量避免NULL

扩展

脏页

内存页和磁盘页数据不一样

刷脏页

从内存更新到磁盘

基础

三大范式

第一范式：表中字段不可再拆分

第二范式：每条记录都依赖行

第三范式：所有属性依赖主键

数据库操作

创建:create database 数据名称

数据表操作

创建表:craeate table 表名

删除表：drop table 表名

清空表数据：truncate 表名

基本概念

实体：客观存在并可相互区别的事物

属性：表达字段

元组：表的每一行数据都是一个元组

码：关键字。可以唯一标识一个实体

关系模式：待解

域：字段类型

外模式：待解

逻辑模式：待解

内模式：待解

笛卡尔积：两个集合相乘的结果(表连接)。可以使用等值连接消除笛卡尔积(即on)

投影：select的字段

选择：where筛选

除运算：待解

连接运算：join

自然连接：join

视图

创建视图：create view 视图名称 (列名....) as select

删除视图：drop view 视图名称

SQL语句

DML(数据操纵语言)

DDL(数据定义语言)

DCL(数据控制)

数据存储

数据存储在存储引擎

自带数据库

information_schema：保存服务端维护的所有其他数据库信息。例如表、视图、列、索引等元数据

mysql：保存服务端用户和权限信息，以及一些存储过程和日志信息

performance_schema：保存服务端运行过程中的一些状态信息，用于提供性能监控和问题排查

sys：通过视图（数据库视图，不是那种可视化视图）的形式把 information_schema 和 performance_schema 结合起来，方便了解 MySQL 服务器的一些性能信息

索引

索引类型

唯一索引

值是唯一的，但允许有空值

主键索引

值是唯一的，不允许有空值

普通索引

联合索引

遵循最左匹配原则：可以是联合索引的最左前几个字段，也可以是字符串最左的前几个字符

全文索引

文本类型 char varchar text

前缀索引

文本类型 char varchar text 可以指定索引列长度

索引结构

B+树

原理

有序性：查找、排序、分组、范围查找

hash

原理

等值查询

索引优化

独立的列：1、不能是表达式的一部分 2、不能是函数的参数

多列索引：要符合最左前缀原则

多列索引顺序：选择性强的放前面

前缀索引：只索引列开始的字符(blob、text、varchar)

覆盖索引(InnoDB)：select 的列在组合索引中都能找到或者从非主键索引上就可以获得需要的数据

索引下推

尽可能的扩展的索引

优点

减少服务器扫描数据的行数

将随机IO变成顺序IO

缺点

索引会占用磁盘空间

降低更新表的效率

MyISAM 和 InnoDB

MyISAM

辅助索引存行记录的磁盘地址

主键索引存行记录的磁盘地址

InnoDB

辅助索引存主键值，查询时，可能需要回表

主键索引存完整的行记录

回表：获得主键，再去主键索引树中获取数据

聚簇和非聚簇索引

索引维护

页分裂：插入新数据时，当前数据页满了，会申请一个新的数据页，然后挪动部分数据过去

页合并：由于删除数据，可能会将数据页合并

存储引擎

InnoDB和MyISAM的区别

InnoDB

1、支持事务

2、支持行锁

3、支持崩溃恢复：通过redo log 实现 InnoDB 存储引擎层

4、支持外键

5、支持MVCC

6、支持在线热备份(待解)

7、不保存表达行数，统计行数需要遍历

MyISAm

1、支持表锁

2、不支持事务

3、不支持崩溃恢复

4、保存表的行数

InnoDB

MyISAM

sql优化

存储过程和函数

查询优化：使用explain 分析select语句

select_type

simple：简单查询

union：联合查询

subquery：子查询

table：要查询的表

possible_keys：可选择索引

key：实际选择索引

rows：扫描的行数

type：索引查询类型(性能从上往下降序)

system

const

eq_ref

ref

range

index

all：全表扫描，不走索引

extra

using temporary：使用临时表

using filesort：需要执行排序操作

分层

server层：连接器、查询缓存、分析器、优化器、执行器、存储过程、视图、触发器、内置函数

存储引擎层：负责数据的存储和提取

MYSQL过程

执行逻辑

执行过程

连接器

跟客户端建立连接、获取权限、维护和管理链接

命令：mysql -u root -p

注意事项：连接成功后，修改权限，不会影响当次连接。下一次连接时才会生效

长连接内存涨的快解决方案

定期断开长连接

5.7之后，通过执行mysql_reset_connection来重新初始化连接资源

查询缓存：8.0之后没有了

分析器：词法分析，语法分析（要做什么）

解析语句，生成解析树

检查语句中的关键词、表、字段是否存在

优化器：执行计划生成，索引选择（怎么做）

原则：尽可能扫描少的数据行记录

执行器：操作引擎，返回结果。权限验证

rows_examined：表示这个语句执行过程中扫描了多少行

存储引擎：存储数据，提供读写接口

其他命令

查看连接状态

show processlist：sleep空闲连接

update执行过程

连接

图示

扩展

写盘IO成本

redo log

参考资料

SQL 基础教程

SQL进阶教程

https://blog.csdn.net/u010002184/article/details/88526708

https://mp.weixin.qq.com/s?__biz=MzI4Njg5MDA5NA==&mid=2247486392&idx=2&sn=9acbd3e958a40e559ea41b878bc1da64&chksm=ebd74ab9dca0c3af3aca763faf2072e709ba54a4bbd8c37b25ba29f0fb88ce56443750342443&token=1436027357&lang=zh_CN&scene=21#wechat_redirect

https://time.geekbang.org/column/article/68633?gk_activity=0

https://mp.weixin.qq.com/s?__biz=MzI4Njg5MDA5NA==&mid=2247484721&idx=1&sn=410dea1863ba823bec802769e1e6fe8a&chksm=ebd74430dca0cd265a9a91dcb2059e368f43a25f3de578c9dbb105e1fba0947e1fd0b9c2f4ef&token=1676899695&lang=zh_CN###rd

https://laravelacademy.org/post/22107