MYSQL 基础

关系型数据库,存储和管理数据

执行图

8.0版本里面直接删除了连接器，查询缓存本身消耗性能，没有缓存的时候需要新建缓存，表级锁失效慢

数据写入流程

索引

B+树，树节点存页的地址，叶子节点存的是具体数据页，数据页之间也会有一个双向链表，方便查找

聚簇索引：非页子节点记录的是行的key ，页子节点记录的是行的信息，聚簇索引不用用户创建，mysql自动创建，一张表有且只有一个聚簇索引（主键或者第一个唯一索引unique列为key）都没有则创建一个6字节递增的隐藏列DB_ROW_ID ==> GEN_CLUST_index的索引

非聚簇索引，（辅助索引）页面节点保留聚簇索引的key值，在通过聚簇索引去查，也就是回表

SQL优化

通过explain看执行计划

type：

值（从高到低）	含义

extra：

值（从高到低）	含义
useIndex	覆盖索引
useIndexCondition	索引下推（需要大量回表，回表之前）

ref：

值（从高到低）	含义

SQL事务

事务：多个数据库操作打包成一个不可分割的整体来执行，且需要保证数据的一致性、可靠性

Innodb：ACID模型，即数据库四大特性，A(Atomicity) 原子性、C(Consistency)一致性、I(Isolation)隔离性、D(Durability)持久性, 最终是为了一致性

A(Atomicity) 原子性： undo log
I(Isolation)隔离性：事务不能查看彼此未提交的数据，分为写与写的隔离（锁去解决），读与写的隔离（mvcc）
A事务读取到B事务未提交的数据 –脏读
A事务先后2次读取到数据不一致（侧重内容） – 不可重复读
A事务两次读取的数据条数不一致（侧重数据量）– 幻读
D(Durability)持久性： 1. 断电等导致刷盘故障（内存到磁盘） redo log 2.数据页部分数据每刷过去，双写缓冲区 Double write buffer

隔离级别

隔离级别	脏读	不可重复读	幻读
READ UNCOMITTED（读未提交 RU）（当前读）（读未提交）	√	√	√
READ COMMITTED (读提交RC)（读已提交）	×	√	√
REPEATABLE READ (可重复读 PR)（提交之后的也读取不到，读取刚开启事务时候数据）	×	×	√
SERIALIZABLE 串行化（事务排队，不能并发）	×	×	×

MYISAM

MYISAM就没有事务概览，由于都是非聚簇索引，所以不需要回表，而且没有行锁所以快，此外MYISAM还使用了一种称为“延迟键写入”的技术，这意味着它在插入数据时不会立即更新索引，而是将索引写入磁盘缓存，稍后在需要时再进行更新。这使得插入数据的速度更快，因为MYISAM不需要等待索引的更新完成。

mysql数据库引擎的区别

Undo log

执行器执行之前就写undo log，通过rollpointer关联

新增Undo 更新Undo 如果有临时表，还涉及到临时表的undo log

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

保证事务的原子性

著作权归所有原文链接：https://javaguide.cn/database/mysql/mysql-logs.html

Redo log

InnoDB存储引擎为redo log的刷盘策略提供了innodb_flush_log_at_trx_commit参数，它支持三种策略保证事务的持久性

设置为0的时候，表示每次事务提交时不进行刷盘操作
设置为1的时候，表示每次事务提交时都将进行刷盘操作（默认值）
设置为2的时候，表示每次事务提交时都只把redo log buffer内容写入page cache

Binlog

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

事务锁

锁信息包含：事务信息事务ID、被锁的索引信息、锁类型模式

锁分类（lock_mode）

LOCK_S: 共享锁 share ，也称为读锁
LOCK_X：独占锁 exclusive，也称为写锁
LOCK_IS: 共享意向锁
LOCK_IX：独占意向锁
LOCK_AUTO_INC：自增锁事务回滚，自增不会滚，可能导致自增不连续

1 2	SELECT age FROM `big_data_500w` LOCK IN SHARE MODE ; -- 共享锁 SELECT age FROM `big_data_500w` WHERE age=20 FOR UPDATE ; -- 排他锁

锁分类（lock_type）

表锁
行锁
- rec_lock_type: 精准行锁、行GAP锁、NEXT-KEY锁、插入gap锁
页锁（BDB引擎才有的）

MVCC

MVCC（Multi-Version Concurrency Control）多版本并发控制机制，解决脏读和幻读，保证隔离基础上提高了并发效率

基于undo_log以及readView读视图实现，读视图里面包含了 m_ids、min_trx_id、max_trx_id、creater_trx_id

需要理解MVCC执行顺序，为啥解决

解决不可重读：是因为第一次创建了读视图，后续都是直接按读视图规则去读

练习

网上找题目练习

-- 1、取得每个部门最高薪水的人员名称 
SELECT 
	MAX(e.SAL) as sal,e.DEPTNO,d.DNAME,e.ENAME
	from emp e
	JOIN dept d
	ON e.DEPTNO=d.DEPTNO
	GROUP BY e.DEPTNO



-- 哪些人的薪水在部门的平均薪水之上

SELECT  e1.ename, e1.sal,e1.DEPTNO,eavg.avg from emp e1  JOIN

(SELECT AVG(e.SAL) as avg,DEPTNO from emp e GROUP BY e.DEPTNO) eavg

ON eavg.DEPTNO = e1.DEPTNO and   e1.SAL > eavg.avg



-- 取得部门中（所有人的）平均的薪水等级

select salgrade.GRADE, eavg.avg,eavg.DEPTNO 
from salgrade 
RIGHT JOIN (SELECT AVG(e.SAL) as avg,DEPTNO from emp e GROUP BY e.DEPTNO) eavg
ON eavg.avg between salgrade.LOSAL and salgrade.HISAL

select 
		e.DEPTNO , AVG(s.grade)
	from
		emp e
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal
 GROUP BY 
		e.DEPTNO
ORDER BY DEPTNO


-- 不准用组函数（Max ），取得最高薪水

SELECT  e.ENAME,e.SAL  from emp e ORDER BY e.SAL DESC limit 1


-- 取得平均薪水最高的部门的部门编号

SELECT deptno, AVG( emp.SAL) as avg from emp GROUP BY deptno ORDER BY avg desc LIMIT 1

SELECT vg.deptno,  MAX(vg.avg) 
from 
(SELECT deptno, AVG( emp.SAL) as avg from emp GROUP BY deptno) vg


SELECT * from emp e WHERE e.SAL = (SELECT MAX(emp.SAL) from emp) ;


-- 求平均薪水的等级最低的部门的部门名称
	select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal;

-- 取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
-- 普通员工
SELECT  MAX(emp.SAL) as maxSal  from emp WHERE emp.EMPNO  not in (SELECT DISTINCT emp.MGR from emp WHERE emp.mgr is not NULL)
-- 领导人

SELECT  ename,sal  from emp WHERE emp.EMPNO in (SELECT DISTINCT emp.MGR from emp WHERE emp.mgr is not NULL)
  and emp.SAL > (SELECT  MAX(emp.SAL) as maxSal  from emp WHERE emp.EMPNO  not in (SELECT DISTINCT emp.MGR from emp WHERE emp.mgr is not NULL)
)

-- 取得薪水最高的前五名员工

select ename,sal from emp order by sal desc limit 5;


-- 取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5,5;

-- 取得最后入职的 5 名员工

	select ename,hiredate from emp order by hiredate desc limit 5;

-- 取得每个薪水等级有多少员工分组count

   SELECT GRADE, COUNT(*) from emp e join salgrade on e.SAL BETWEEN salgrade.LOSAL and salgrade.HISAL GROUP BY salgrade.GRADE ORDER BY GRADE

select 
	s.grade ,count(*)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
	s.grade;

-- 列出所有员工及领导的姓名 
select 
	a.ename '员工', b.ename '领导'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;

-- 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

SELECT a.ENAME '员工', a.HIREDATE, b.ENAME '领导', b.HIREDATE ,d.DNAME
from emp a 
join emp b
on a.mgr = b.EMPNO and  a.HIREDATE<b.HIREDATE and b.ENAME is not null
join
	dept d
on
	a.deptno = d.deptno


-- 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门


SELECT * from emp e 
RIGHT JOIN dept 
on dept.deptno=e.deptno

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



-- 列出至少有 5 个员工的所有部门按照部门编号分组，计数，筛选出 >= 5
SELECT tt.ddname,count(tt.ddname) as num 
from 
(
select
	e.DEPTNO, d.dname as ddname 
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno) tt
GROUP BY tt.ddname 
HAVING  num >=5


select 
	emp.deptno,dept.dname
from
	emp
JOIN dept 
on dept.DEPTNO = emp.deptno
group by
	emp.deptno
having
	count(*) >= 5;

-- 列出薪金比"SMITH" 多的所有员工信息

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');


-- 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
select ename,job from emp where job = 'CLERK';




-- 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数按照工作岗位分组求最小值。

SELECT job,MIN(emp.SAL) as minsal,COUNT(*)  from emp  GROUP BY job HAVING minsal>1500



-- 列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号

select ename from emp where deptno = (select deptno from dept where dname = 'SALES');


-- 列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.


-- 列出与"SCOTT" 从事相同工作的所有员工及部门名称

SELECT job from emp WHERE emp.ENAME='SCOTT'

SELECT ENAME,d.dname,e.mgr,salgrade.GRADE from emp e 
join dept d
on d.deptno = e.deptno 
join salgrade
on e.sal BETWEEN salgrade.LOSAL and salgrade.HISAL
WHERE e.job= (SELECT job from emp WHERE emp.ENAME='SCOTT') and e.ENAME!='SCOTT'


-- 列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.

select distinct sal from emp where deptno = 30;

SELECT emp.ENAME,emp.SAL from emp
WHERE emp.SAL in (select distinct sal from emp where deptno = 30
)
and 
	deptno <> 30;


-- 列出在每个部门工作的员工数量, 平均工资和平均服务期限 没有员工的部门，部门人数是0


SELECT dept.DEPTNO, COUNT(emp.ename) '员工数', ifnull(AVG( emp.SAL),0) '平均薪水', ifnull(AVG(TIMESTAMPDIFF(YEAR,emp.HIREDATE,NOW())),0) as '平均服务年限' from emp 
RIGHT JOIN dept
on emp.DEPTNO= dept.DEPTNO
GROUP BY emp.DEPTNO


-- 列出所有员工的姓名、部门名称和工资。

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



-- 列出所有部门的详细信息和人数

SELECT dept.LOC,dept.dname, dept.DEPTNO,dept.LOC, count(emp.ename) from emp  
RIGHT JOIN dept
on dept.DEPTNO =emp.DEPTNO
GROUP BY dept.LOC, dept.DEPTNO,dept.LOC


-- 列出各种工作的最低工资及从事此工作的雇员姓名

select 
	e.ename,t.*
from
	emp e
join
	(select 
		job,min(sal) as minsal
	from
		emp
	group by
		job) t
on
	e.job = t.job and e.sal = t.minsal;


-- 列出各个部门的 MANAGER( 领导) 的最低薪金

select 
	deptno, min(sal)
from
	emp
where
	job = 'MANAGER'
group by
	deptno;

-- 列出所有员工的 年工资, 按 年薪从低到高排序

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


-- 求出员工领导的薪水超过3000的员工名称与领导
	SELECT a.ename "员工", b.ename "领导" from emp a
	join emp b 
  on a.MGR= b.EMPNO
  WHERE b.SAL >3000

-- 求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
		
 SELECT dept.DNAME,SUM(e.sal) '工资合计',COUNT(e.ename) '人数'  from emp e  
 right join  dept
 on dept.DEPTNO=e.deptno 
GROUP BY e.deptno
HAVING DNAME like '%s%'

 SELECT SUM(e.sal) '工资',COUNT(e.ename) '人数' from emp e  
 join  dept
 on dept.DEPTNO=e.deptno 
GROUP BY e.deptno
HAVING dept.DNAME like '%s%'

-- 给任职日期超过 30 年的员工加薪 10%.

update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

总结

注意sql的执行顺序

select 字段

from 表名

where …….

group by ……..

having …….

order by ……..

where => group by=> having=>select =>order by

分组后只有分组的字段才有意义，其余字段没有意义，可以组合分组
设计范式
- 第一范式(确保每列保持原子性)
- 第二范式(确保表中的每列都和主键相关)
- 第三范式(确保每列都和主键列直接相关,而不是间接相关)
  
  但是数据范式不是必须的，视具体业务而定，因为过多的join 会指数级增加检索

技术

#mysql

mysql 基础

https://godbuttton.github.io/2023/04/20/mysql-基础/

作者

godbutton

发布于

2023年4月20日

许可协议

股票-通达信版本上一篇

leetcode 解题下一篇