MySQL笔记

Anchovy
97 阅读43分钟

散装知识点

  • 不看表中的数据，只看表的结构，有一个命令：desc 表名;

  • 数据库中的字符串都是采用单引号括起来。这是标准的。双引号不标准。

  • 在数据库当中null不能使用等号进行衡量。需要使用is null因为数据库中的null代表什么也没有，它不是一个值，所以不能使用等号衡量。

    ​ 查询哪些员工的津贴/补助为null？

    ​ select empno,ename,sal,comm from emp where comm is null;

  • in 包含，相当于多个 or （not in 不在这个范围中）in不是一个区间。in后面跟的是具体的值。

  • not in 在使用的时候,后面的小括号中,如果有null,记得排除

  • like

    称为模糊查询，支持%或下划线匹配 %匹配任意多个字符,下划线匹配任意一个字符。（%是一个特殊的符号，_ 也是一个特殊符号）

  • 指定降序：desc 指定升序？asc

  • order by要写在where 之后

单行处理函数

  • lower 转换小写

select lower(ename) as ename from emp;

  • Upper 转换大写

select upper(name) as name from t_student;

  • substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）

select substr(ename, 1, 1) as ename from emp 注意：起始下标从1开始，没有0.

  • concat 函数进行字符串的拼接

select concat(empno,ename) from emp;

  • length 取长度

select length(ename) enamelength from emp;

  • trim 去空格

select * from emp where ename = trim(' KING');

  • round 四舍五入

select round(1236.567, 1) as result from emp; //保留1个小数

select round(1236.567, 2) as result from emp; //保留2个小数

select round(1236.567, -1) as result from emp; // 保留到十位

  • rand() 生成随机数

mysql> select round(rand()*100,0) from emp; // 100以内的随机数

  • ifnull 可以将 null 转换成一个具体值

ifnull是空处理函数。专门处理空的。在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。

ifnull函数用法：ifnull(数据, 被当做哪个值) 如果“数据”为NULL的时候，把这个数据结构当做哪个值。

补助为NULL的时候，将补助当做0

select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;

分组函数

  • 第一点：分组函数自动忽略NULL，你不需要提前对NULL进行处理。

  • count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。count(*)：统计表当中的总行数。

  • 第三点：分组函数不能够直接使用在where子句中。

  • 为什么分组函数不能直接使用在where后面？

    • 因为分组函数在使用的时候必须先分组之后才能使用。
    • where执行的时候，还没有分组。所以where后面不能出现分组函数。

  • 在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。

  • 使用having可以对分完组之后的数据进一步过滤。

    having不能单独使用，having不能代替where，having必须和group by联合使用。

去重 distinct

  • dinstinct只能出现在所有字段的最前方
  • dinstinct出现在两个字段之前,表示两个字段的联合起来去重
  • distinct可以和count这样的分组函数一起用

内连接

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno

完全匹配上on后面的条件的数据,才会被查询出来

inner写在join前面,一般是省略的

A和B连接,这两张表没有主次关系,是平等的

外连接

select e.ename,d.dname 
from emp e right join dept d 
on e.deptno = d.deptno

表示right join 的右边这张表为主表,主表的数据会全部查询出来,顺带关联查询左边的表

join前面有个outer,省略掉了

在外连接中,两张表有了主次关系

多张表的连接

select 字段 
from a 
join b on a和b的连接条件 
join c on a和c的连接条件

内连接和外连接可以出现在同一sql语句中

子查询

select语句中嵌套select,被嵌套的那个就是子查询

where中的案例

找出工资比最低工资高的员工的姓名和工资

1.查询最低工资

select min(sal) from emp

2.查出工资大于800的员工姓名和工资

select ename,sal from emp where sal > 800

3.合并

select ename,sal from emp where sal > (select min(sal) from emp)
from中的案例

找出每个岗位的平均工资的薪资等级

1.找出每个岗位的平均工资

select job,avg(sal) from group by job

image-20230823145223055.png

2.把以上的查询结果当成一张表,假设是t表,

select t.* , s.grade
from t 
join salgrade s 
on t.sal between s.losal and s.hisal

3.合并,注意要给t表里的平均薪资取别名

select t.* , s.grade
from (select job,avg(sal) as avgsal from group by job) t 
join salgrade s 
on t.avgsal between s.losal and s.hisal

Union

用于合并两个或者更多SELECT语句的结果集

select ename , job from emp where job = 'MANAGER'
union
select ename , job from emp where job = 'SALESMAN'

union的效率会更高

  • 对于表连接来说,每连一次新表,匹配的次数是满足笛卡尔积的
  • 对于union来说,可以减少匹配的次数,而且可以完成结果集的拼接
  • 假设a,b,c三张表各有10条记录,此时我们要用a连接b,再连接c,表连接的次数是10 x 10 x 10,就是1000次,union则是100 + 100 次
注意事项

1.union连接的查询语句,查询的列数必须相同

2.union默认去重,保留重复行请用union all

3.不能对单个查询语句使用order by

4.order by只能有一个,而且只能写在整个union语句的最后

Limit

limit startIndex length

limit 5 : 取前5条数据

两个参数分别是起始下标和长度,其中下标从0开始

注:limit 会在 order by 之后执行

标准分页

第 pageNo 页 : limit (pageNo -1) * pageSize, pageSize

例 : 查第11页,每一页显示3条数据

limit 30,3

数据类型相关

varchar(255)可变长度字符串 可以根据数据长度动态分配空间,但是速度会慢

char (255)定长字符串,不需要分配空间,速度快,可能会造成空间的浪费

clob 字符大对象 超过255个字符的都要用它

blob 二进制大对象 专门用来存储图片 声音 视频等. 插入数据需要使用IO流

date是短日期,只有年月日

datetime是长日期,包括年月日时分秒

删除表中数据的两种方法

1.delete

表中的数据被删除,但是硬盘上的数据不会被释放

删除效率低,但是支持事务的回滚

2.turance

表被一次截断,物理删除

效率高,但是不支持回滚

属于DDL操作

删除表中所有数据,表还在

删表用drop

约束

散装知识点

唯一约束字段可以为null

mysql中,如果一个字段同时是非空和唯一约束,这个字段自动被指定成主键

如何指定多个字段的联合约束?

约束没有写在列的后面,是为表级约束

create table t_vip{
	id int,
	name varchar(255),
	email varchar(255),
	unique (name,email)
}

注:not null不可被设定为表级约束

联合主键是可以的,但是不建议使用

引入外键的格式

以下是将t_class表的classno字段指定为外键的格式

create table t_student{
	foreign key(cno) references t_class(classno)
}

被引用作为外键的字段,在父表中,至少需要是unique约束

事务

事务是如何实现的?

InnoDB引擎 : 提供一组用来记录事务性活动的日志文件,事务执行过程中,每一条DML的操作都会记录到"事务性活动的日志文件中"

提交事务:清空事务性活动的日志文件,并把数据全部持久化到数据库表里

回滚事务:将之前的DML操作全部撤销,并且清空事务性活动的日志文件

回滚和提交都标志着事务的结束,前者全部失败,后者全部成功

默认情况下的事务行为

mysql在默认情况下是自动提交事务的

如何关闭自动提交? : start transaction

事物特性 ACID
  • Atomic 原子性

要么全部执行成功,要么全部执行失败

  • Consistency 一致性

事务保证数据库从一个一致性状态变换到另一个一致性状态

  • Isolation 隔离性

不同事务中的操作应该相互独立,并发的事务之间不会互相影响

  • Durability 持久性

已提交的事务,对数据库的修改,应该永久保存在数据库里,即使系统故障.这是事务最终结束的一个保障

事务的隔离级别?
  • 读未提交（Read Uncommitted）
    • 事务a可以读取到事务b未提交的数据,这种现象叫做脏独
  • 读已提交（Read Committed）
    • 事务A只能读取事务B已经提交的数据
    • 解决了脏读问题,但是有不可重复读问题
    • 不可重复读:第二次读取同一记录的时候,被其他事务修改了,这就导致两次读的数据不一样
    • 数据比较真实
  • 可重复读（Repeatable Read）
    • 保证事务A多次读取到的数据都是一致的,即使事务B把数据给改了
    • 解决了不可重复读的问题,但是有幻读
    • 幻读:每次读的数据都是幻影,不够真实
    • MySQL的默认隔离级别
  • 串行化（Serializable）
    • 最高隔离级别,强制事务顺序执行
    • 完全避免各种问题,但是效率最低

事务的原理

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

  • 有了redolog之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。
  • 在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。
  • 一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性
  • 而如果脏页成功刷新到磁盘 或 或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了
undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和 支持MVCC(多版本并发控制) 。

  • undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
  • Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
  • Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment。

索引

概述

  • 索引是帮助数据库快速获取数据的一个数据结构,索引存储在表里,但是独立于表
  • 索引里包含对数据的引用指针,而不是真数据
  • 索引通过B-Tree等进行排序,能够对数据进行预排序,从而实现快速查找

常用种类

  • 普通索引
  • 唯一索引 : 对unique约束的字段自动创建索引
  • 主键索引 : 主键自动创建索引
  • 组合索引 : 多个字段创建的单个索引,提高查询多个字段时的效率

结构

1.改进型B+树

image-20230909225428239.png

  • 所有的数据都会出现在叶子节点。
  • 叶子节点形成一个单向链表。
  • 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
  • 原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序

2.Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中

特点

  • Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
  • 无法利用索引完成排序操作
  • 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

为什么InnoDB使用B+树?

  • 相比于二叉树,层级更少,搜索效率高
  • 而对于B-Tree,由于其叶子节点和非叶子节点都存储数据,而页的物理大小是固定的,导致一页可以存储的键数量减少,指针也减少
  • 指针减少导致,存放同样多的数据,B树的高度比B+树要大
  • 相比于Hash索引的话,B+树支持范围匹配和排序,Hash只能支持对等比较

InnoDB中的聚集索引和二级索引

  • 聚集索引的叶子节点存放了行数据,必须有且只有一个
    • 如果存在主键,那主键就是聚集索引
    • 如果没主键,就用第一个唯一索引当
    • 如果都没有,InnoDB会生成一个隐藏的rowid作为聚集索引
  • 二级索引的叶子节点存放了主键
    • 回表查询:先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据

索引的适用

1.数据量过大,要加快查询效率的时候

2.经常被查询的字段应该建立索引

3.增删改查太频繁的字段不适合建立索引,因为DML操作以后,索引要重排

优势和劣势

  • 提高检索效率,降低排序和IO的成本
  • 降低更新效率,需要占用空间

语法

创建索引:给emp表的ename字段添加常规索引

create index emp_ename_index on  emp(ename)

添加唯一索引这样的,就在index前面加unique

删除索引

drop index emp_ename_index on emp

查看索引

show index from emp

查询中不使用索引的情况

  • 索引列参加了运算或者使用了函数
  • 索引列是字符串类型,但是没加单引号
  • 使用%或_作为前缀通配符搜索
  • 使用or,连接的列都有索引,那才会生效
  • 使用复合索引,但是查询时没有使用索引左侧的列找,如果跳过了某一列,则这列后面的字段也不会用索引
  • 复合索引中,使用< >来范围查询的字段,这个字段后面的其他字段不会走索引
  • 如果MySQL评估使用索引比全表更慢，则不使用索引

关于索引的sql提示

1). use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

2). ignore index ： 忽略指定的索引。

3). force index ： 强制使用索引。

explain select * from tb_user use/ignore/force index(idx_user_pro) where profession = '软件工程';

覆盖索引

尽量使用覆盖索引，减少select *。覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

为什么？

一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案?:

select id,username,password from tb_user where username='itcast';

分析:i对username和password创建联合索引,这样符合覆盖索引

如果只对username建立索引,搜索时在username二级索引里只能找到username数据和id,还需要通过id进行回表查询,去查询聚集索引,才能把password找出来,性能稍差

前缀索引

如果要创建索引的字段的属性太长,可以对字段的一部分前缀建立索引

为tb_user表的email字段，建立长度为5的前缀索引。

create index idx_email_5 on tb_user(email(5));

怎么确定前缀长度?

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

三范式

  1. 第一范式（1NF）：关系中的每个属性都是不可分割的原子值，即没有重复的列值。

    字段不可再分

  2. 第二范式（2NF）：关系模式满足第一范式，且每个非主属性完全函数依赖于主键。

    这要求关系模式中不存在部分依赖

    非主键属性完全依赖于主键

  3. 第三范式（3NF）：关系模式满足第二范式，且每个非主属性都不传递函数依赖于主键。

    这要求关系模式中不存在传递依赖

    非主键属性之间没有依赖关系

三范式只是理论上的,实际开发中,为了满足用户的需求,有可能会用冗余字段来减少表的连接次数

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的

存储引擎

  • 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。
  • 存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
  • 我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎（InnoDB）。

InnoDB

DML操作遵循ACID模型,支持事务,支持行级锁,支持外键

每张表对应一个ibd文件,用来存放表结构,数据和索引

逻辑存储结构

image-20230922091842656.png 1.表空间是InnoDB存储引擎逻辑结构的最高层，每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

2.段分为数据段,索引段等,数据段就是B+数的叶子节点,存放数据,索引段是B+数的非叶子节点,存放索引.段用来管理多个区

3.区，表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。

4.页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

5.行，InnoDB 存储引擎数据是按行进行存放的。

物理结构

物理结构分为内存结构和硬盘结构

image-20230922092801087.png
  • 缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
  • Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中
  • Adaptive Hash Index 自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
  • Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。
磁盘结构
image-20230922094535671.png

1). System Tablespace

系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。

2). File-Per-Table Tablespaces

如果开启了innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索引 ，并存储在文件系统上的单个数据文件中。

3). General Tablespaces

通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。

4). Undo Tablespaces

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志

5). Temporary Tablespaces

InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

6). Doublewrite Buffer Files

双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据

7). Redo Log

重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用。

线程

在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。

  • 1). Master Thread 核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收 。

  • 2). IO Thread 在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IOThread主要负责这些IO请求的回调。

  • image-20230922095221202.png

  • 3). Purge Thread

  • 主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

  • 4). Page Cleaner Thread

  • 协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞

MyISAM

不支持事务和外键,支持表锁,不支持行锁,访问速度快

一张表有三个文件,分别存放表结构(sdi),数据(MYD)和索引(MYI)

Memory

存储在内存中,只能当临时表或缓存用 只有一个sdi文件

面试:InnoDB和MyISAM的区别

InnoDB支持事务和外键,MyISAM不支持

InnoDB支持行级锁和表级锁,MyISAM只支持表锁

如何选择存储引擎?

MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。(MongoDB)

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。(Redis)

优化

SQL性能分析的方法

优化之前的第一件事,自然是分析效率

查询sql语句的执行频次
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志,用来查找执行时间不符合要求的语句

打开MySQL配置文件（通常是my.cnf或my.ini）。

找到[mysqld]部分，并添加或修改以下行：

slow_query_log = 1  --启用慢查询日志
slow_query_log_file = /path/to/your/slow-query.log  --指定日志文件路径
long_query_time = 2	--指定超过2秒的语句为慢查询

慢查询日志文件会记录慢查询语句

查询sql语句执行的信息

在MySQL中，SHOW PROFILES命令用于显示与每个已执行的语句关联的配置文件信息。配置文件是MySQL服务器用来优化查询性能的机制。

使用SHOW PROFILES命令可以获取有关查询执行的详细信息，包括执行时间、CPU时间、块读取次数等。这对于分析和优化查询性能非常有用。

请注意，要使用SHOW PROFILES命令，必须启用配置文件功能。可以通过设置profiling系统变量来启用配置文件功能，例如：

SET profiling = 1;

启用配置文件功能后，执行的每个查询都将关联一个配置文件。可以使用SHOW PROFILES命令来检索和显示这些配置文件的信息。

以下是SHOW PROFILES命令的基本语法：

sql复制代码

SHOW PROFILES [type] [FOR QUERY n] [LIMIT [offset,] row_count] [sort_by]

参数说明：

  • type：指定要显示的配置文件类型。可以是ALL（所有类型）、BLOCK IO（块I/O操作）、CONTEXT SWITCHES（上下文切换）、CPU（CPU时间）、IPC（进程间通信）、MEMORY（内存使用）、PAGE FAULTS（页面错误）、SOURCE（源文件）或SWAPS（交换次数）。如果未指定类型，则默认为ALL。
  • FOR QUERY n：指定要显示配置文件的查询语句的编号。可以通过执行EXPLAIN命令来获取查询语句的编号。
  • LIMIT [offset,] row_count：指定要显示的配置文件的偏移量和行数。可以使用此参数来分页显示结果。
  • sort_by：指定按哪个字段对结果进行排序。可以是TIME（执行时间）、CPU（CPU时间）或BLOCKS（块读取次数）。如果未指定排序字段，则默认为按执行时间排序。

以下是一个示例：

SHOW PROFILES FOR QUERY 1;

上述命令将显示查询语句编号为1的配置文件信息。

show profile cpu for query 1;

上述命令会显查询语句编号为1的cpu使用信息

查询sql语句执行查询的计划

也就是获取 MySQL 如何执行 SELECT 语句,只会返回查询语句的信息,不会真正地执行查询

EXPLAIN SELECT * FROM customers WHERE age > 30;

返回这一条语句的查询计划

EXPLAIN返回的结果集通常包含以下字段：

  1. id: 查询中每个select语句都有一个独一无二的id，如果语句中有子查询，那么每个子查询也都有一个id。这个字段有助于区分各个查询，也用于表示查询执行的顺序。
  2. select_type: 这个字段用来表示select语句的类型。主要有以下几种类型：
    • SIMPLE: 不包含子查询的简单select语句。
    • PRIMARY: 最外层的select语句。
    • SUBQUERY: 在select语句之前执行的子查询。
    • UNION: 用UNION或UNION ALL连接的联合查询。
    • DEPENDENT UNION: 与UNION或UNION ALL一起使用的子查询中的第二个或后面的select语句。
    • UNION RESULT: 用UNION ALL连接的select语句的结果。
  3. table: 正在访问的表的名称。
  4. type: 表示查询中表之间的连接类型。主要有以下几种类型：
    • system: 当表只有一行数据或者是空表时，此类型适用，它是const类型的特例。
    • const: 当一个常量值与一个列相比较或者一个常量值与一个单列索引比较时，使用此类型。
    • eq_ref: 当一个列用等于操作符进行比较，并且该列或索引列只被访问一次时，使用此类型。
    • ref: 当一个索引的列被一个比较操作符引用时，使用此类型。
    • fulltext: 用于全文搜索。
    • ref_or_null: 当一个引用列在查询中被使用，或者索引列被一个比较操作符引用，并且该列包含NULL值时，使用此类型。
    • index_merge: 当查询使用了两个以上的索引时，使用此类型。
    • unique_subquery: 当子查询中返回的结果是唯一的，并且被用在比较操作符之前时，使用此类型。
    • index_subquery: 当子查询使用了索引并且被用在比较操作符之前时，使用此类型。
    • range: 当一个索引的列被一个范围操作符引用时，使用此类型。
    • index: 当查询只使用了索引列来检索数据而不使用表的数据列时，使用此类型。
    • ALL: 当表的所有数据都被用来检索数据时，使用此类型。除了ALL之外，其他的type都可以使用到索引，而index_merge类型不可以。
  5. possible_keys: 这个字段显示了可能使用的索引列表。尽管它可能包含几个索引，但是并不是每个索引都会被使用，具体使用的索引由key字段表示。
  6. key: 这个字段表示实际使用的索引。如果该字段为NULL，则表示没有使用任何索引。如果该字段非NULL，则表示使用了可能索引列表中的某一个索引。
  7. key_len: 这个字段表示使用的索引的长度。如果索引是复合的，这个长度表示使用了多少个字节的索引列。如果索引不是复合的，这个长度就是该列的长度。
  8. ref: 这个字段表示哪些列或常量被用作索引查找条件。
  9. rows: 这个字段表示MySQL估计要检查的行数。这个数字是根据表的大小和索引的使用情况得出的。
  10. Extra: 这个字段提供了其他关于查询执行的信息。比如是否使用了临时表、是否使用了文件排序等额外的信息。

对主键的优化

  • 满足业务需求的情况下，尽量降低主键的长度。主键过长,会导致二级索引所需要的页数增大
  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。乱序插入可能导致表分页,这个操作比较耗时间
  • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
  • 业务操作时，避免对主键的修改。

order by优化

  1. Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
  2. Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

  • A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

  • B. 尽量使用覆盖索引。

  • C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。

    • 创建联合索引时,默认都是升序,如果查询时是一个升一个降低,那就会是Using filesort的情况.可以再次创建指定升降的联合索引

    • create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

  • D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

group by 优化

A. 在分组操作时，可以通过索引来提高效率。

B. 分组操作时，索引的使用也是满足最左前缀法则的。

update优化

我们主要需要注意一下update语句执行时的注意事项。尽量根据主键/索引字段进行数据更新

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下SQL时。

update course set name = 'SpringBoot' where name = 'PHP' ;

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

视图

介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法

-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;
-- 查询视图
show create view stu_v_1; 	-- 查询建视图的语句
select * from stu_v_1;
select * from stu_v_1 where id < 3;
-- 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;
alter view stu_v_1 as select id,name from student where id <= 10;
-- 删除视图
drop view if exists stu_v_1;

检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。

MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。

1). CASCADED

级联。

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

2). LOCAL

本地。

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1。

更新规则

4.1.4 视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）

B. DISTINCT

C. GROUP BY

D. HAVING

E. UNION 或者 UNION ALL

作用

1). 简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2). 安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

3). 数据独立

视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合

  • 封装，复用 -----------------------> 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
  • 可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回值。
  • 减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

相当于程序设计语言中的函数

基本语法

注意:

在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

-- 存储过程基本语法
-- 创建
create procedure p1()
begin
	select count(*) from student;
end;
-- 调用
call p1();
-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = '数据库名';
show create procedure p1; -- 查看建表语句
-- 删除
drop procedure if exists p1;

游标

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。

create procedure p11(in uage int)
begin
	declare uname varchar(100);
	declare upro varchar(100);
	-- 声明游标u_cursor
	declare u_cursor cursor for select name,profession from
		tb_user where age <= uage;
		
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
		id int primary key auto_increment,
		name varchar(100),
		profession varchar(100)
	);
	--开启游标
	open u_cursor;
	while true do
		--遍历游标,把u_cursor里的两个字段赋给uname和upro两个变量
		fetch u_cursor into uname,upro;
		-- 把uname和upro两个变量插入表
		insert into tb_user_pro values (null, uname, upro);
	end while;
	-- 关闭游标
	close u_cursor;
end;
call p11(30);

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤

类似Java中的异常

DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;
handler_action 的取值：
	CONTINUE: 继续执行当前程序
	EXIT: 终止执行当前程序
condition_value 的取值：
	SQLSTATE sqlstate_value: 状态码，如 02000
	SQLWARNING: 所有以01开头的SQLSTATE代码的简写
	NOT FOUND: 所有以02开头的SQLSTATE代码的简写
	SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

当异常状态码为02000时,关闭游标

declare exit handler for SQLSTATE '02000' close u_cursor

变量

系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

-- 查看系统变量
show session variables ;	--会话变量
show session variables like 'auto%'; --模糊查询
show global variables like 'auto%';  --全局变量的模糊查询
select @@global.autocommit;   --详细查询
select @@session.autocommit;
-- 设置系统变量
set session autocommit = 1;
注意事项

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。

A. 全局变量(GLOBAL): 全局变量针对于所有的会话。

B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。

用户定义变量

用户定义变量:是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名" 使用就可以。

其作用域为当前连接。

-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;	--把查询的结果作为变量初始化
-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;

局部变量

局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。

-- 声明局部变量 - declare
-- 赋值
create procedure p2()
begin
	declare stu_count int default 0;
	select count(*) into stu_count from student;
	select stu_count;
end;
call p2();

触发器

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器,影响每一行
BEGIN
	trigger_stmt ;
END;

SHOW TRIGGERS ; 

DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定schema_name，默认为当前数据库 。

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁，按照锁的粒度分，分为以下三类：

  • 全局锁：锁定数据库中的所有表。
  • 表级锁：每次操作锁住整张表。
  • 行级锁：每次操作锁住对应的行数据。

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态

运用场合
  • 数据库备份：全局锁可以防止数据库在备份期间被修改，从而确保备份数据的一致性和完整性。
  • 数据恢复：全局锁可以避免在数据库恢复过程中其他线程对数据库进行修改，使得恢复数据的一致性得到保障。
  • 数据复制：全局锁可以锁定整个数据库实例，保证在进行数据复制或迁移时，数据的一致性得到保障。
  • 数据完整性保障：在一些特定的操作中，全局锁可以防止多个线程同时对数据库进行修改，从而保障数据的完整性。
语法
flush tables with read lock ;  //加全局锁

unlock tables ; //释放锁
特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

  • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
  • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中

表锁

语法：加锁：lock tables 表名... read/write。 释放锁：unlock tables / 客户端断开连接 。

  • 表共享读锁

    • 所有的事物都只能读（当前加锁的客户端也只能读，不能写）

  • 表独占写锁

    • 对于当前加锁的客户端，可读可写，对于其他的客户端，不可读也不可写。
元数据锁

元数据 : 描述数据的数据

在MySQL中元数据是描述数据库、表、列、索引和其他数据库对象的结构、属性和关系的数据。简单理解成表结构

作用:避免DML和DDL冲突,保证读写的正确性

  • 当执行DML或DDL语句时，会获取相应的元数据锁。
  • DML操作需要元数据读锁，而DDL操作需要元数据写锁。
  • 读锁和写锁之间是相互阻塞的，即在一个表上的DML和DDL操作之间会发生互相阻塞。
  • 写锁和写锁之间也是互相阻塞的，即两个会话不能同时对表进行定义变更操作，需要串行执行。
  • 然而，读锁和读锁之间不会产生阻塞，这意味着并发执行DML操作时不会因元数据锁而相互阻塞。
意向锁

使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

  • 意向共享锁(IS): 由语句select ... lock in share mode添加。
    • 与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
  • 意向排他锁(IX): 由insert、update、delete、select...for update添加 。
    • 与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
流程
  1. 我们要给一张表加表级锁
  2. 先获取这张表的意向锁
  3. 如果是IS，那可以给这张表加表级读锁，不能加表写锁
  4. 如果是IX，那表写锁和表读锁都不能加

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。与MyISAM相比:事务外键行级锁

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

行锁

锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。(多事务都能读取,都不能写)
  • 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。(单事务读/写,其他事务不能再加S/X)

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  • 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
  • InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。
间隙锁 & 临键锁

锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。间隙锁可以共存

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

特点

  • 使用唯一索引进行等值查询，给不存在的记录加锁时, 优化为间隙锁 。
  • 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
  • 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

MVCC

基本概念

1). 当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ...for update、update、insert、delete(排他锁)都是一种当前读。

2). 快照读

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

• Read Committed：每次select，都生成一个快照读。

• Repeatable Read：开启事务后第一个select语句才是快照读的地方。

• Serializable：快照读会退化为当前读。

3). MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

1.隐藏字段

当我们创建了一张表，我们在查看表结构的时候，就可以看到自己创建的字段。 实际上除了自己建立的字段以外，InnoDB还会自动的给我们添加三个隐藏字段

  • DB_TRX_ID 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
  • DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
  • DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

2.undo log 版本链

image-20230922153015211.png image-20230922153036729.png

执行了上述三个操作后,undo log 和 表中的记录分别如上,具体视频看黑马MySQL P.144

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

3.Read View

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

ReadView中有四个核心的字段

  • m_ids 当前活跃的事务ID集合
  • min_trx_id 最小活跃事务ID
  • max_trx_id 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
  • creator_trx_id ReadView创建者的事务ID

ReadView规定了版本链的访问规则

trx_id代表当前undo log版本链对应事务的id

  • trx_id ==creator_trx_id 成立，说明数据是当前这个事务更改的。可以访问该版本
  • trx_id < min_trx_id 成立，说明数据已经提交了。可以访问该版本
  • trx_id > max_trx_id 成立，说明该事务是在ReadView生成后才开启。不可以访问该版本
  • min_trx_id <= trx_id <= max_trx_id 如果trx_id不在m_ids中，是可以访问该版本的。成立，说明数据已经提交。

不同隔离级别下,ReadView的生成时机不同

  • READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
  • REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
avatar
文章
阅读
粉丝