mysql梳理复习内容--附思维导图

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
#联合索引
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

explain select * from staffs where name='z3'
explain select * from staffs where name='z3'and age=22 and pos='manager';
explain select * from staffs where name='z3' and pos='manager' and age=22;
explain select * from staffs where pos='manager' and age=22;

7、回表

走了两遍索引----举例：有一个主键索引，一个普通索引，当在普通索引字段上使用select*。先根据普通索引找到主键id，再根据主键id去主键索引中找到对应行的全量数据。普通索引，没有全量数据。

避免回表：使用覆盖索引

8、覆盖索引

不使用Select* 使用Select ID ，然后ID对应的是索引字段。覆盖索引可以减少树的搜索次数，提升性能。

9、MVCC

MVCC 是多版本并发控制，在很多情况下避免加锁，⼤都实现了⾮阻塞的读操作，写操作也只锁定必要的⾏。

通过在每⾏记录后⾯保存两个隐藏的列来实现。创建时间版本号，过期(删除)时间版本号，每开始一个新的事务，版本号都会自动递增。

MVCC 只能在已提交读和可重复读两个隔离级别下⼯作，因为未提交读总是读取最新的数据⾏，⽽不是符合当前事务版本的数据⾏。⽽ SERIALIZABLE 则会对所有读取的⾏都加锁

MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为空或大于当前事务版本

假设表里面两条数据如下：

name

创建版本号

删除版本号

一一

二二

三个事务：

A：查询id小于3的数据，创建版本号3，删除版本号空

B：修改id为1的数据name--->王八,创建版本号4，删除版本号空

C：删除id为2的数据，删除版本号5。

A查询到的数据还是上表中的数据。

name

创建版本号

删除版本号

一一

二二

王八

二二

三、事务

1、事务的四大特性（ACID）以及实现原理

（1）原子性：（undo log）

事务被视为不可分割的最小单元，事务的所有操作要么全部成功，要么全部失败回滚。

实现原理：利用innodb的undo log，undo log 记录的反向sql逻辑。

（2）一致性：

数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的

实现原理：

数据库层面：通过原子性、隔离性、持久性来保证一致性。一致性是目的，其他是手段。
应用层面：通过代码判断数据库数据是否有效，然后决定回滚还是提交。

（3）隔离性：（锁和MVCC机制）

事务之间互不影响，事务所做的修改在提交前，对其他事务是不可见的。

实现原理：利用的是锁和MVCC机制

（4）持久性：（redo log）

事务一旦提交，其所做的修改就是永久有效，即使是数据库宕机，事务执行的结果也不能丢。

实现原理：

利用innodb的redo log 日志文件，这文件记录了数据库表结构和表数据的物理变化。

数据进行修改时，不仅会在内存进行修改，还会在redo log 中记录这次操作。redo log ，也会

刷到磁盘上，但是是顺序IO速度快，且体积小。当数据库宕机时，会将redo log中的内容恢复到数

据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

2、MySQL常见log文件

undo log、 binlog、 redo log

（1）binlog

binlog记录了数据库表数据和表结构的变更的日志文件。

存储着每条变更的SQL语句等。用来复制和恢复数据。

（2）redo log

redo log 也是记录了数据库表数据和表结构的变更的日志文件。

不过记录的是物理变化，文件小，恢复速度快。

redo log 要写磁盘，但是redo log是顺序IO（比随机IO快很多）写入速度快。

（3）binlog与redo log 的区别

存储的内容不同：binlog 存储的是逻辑变化（sql语句），redo log 记载的是物理变化（xx页修改了xx）；
功能不同：binlog的作用是复制和恢复；redo log 为持久化而生。
写完内存，如果数据库挂了，那我们可以通过redo log来恢复内存还没来得及刷到磁盘的数据(redo log不会存储着历史所有数据的变更，文件的内容会被覆盖的)
写入顺序不同：redo log--事务开始的时候，就开始记录每次的变更信息。binlog--事务提交的时候才记录。

（4）undo log

主要有两个作用：回滚和多版本控制(MVCC)

主要存储的也是逻辑日志，反向逻辑。

bin log

redo log

undo log

存储内容

存sql逻辑

物理变化

反向sql

作用

复制+恢复（原子性）

持久化（持久性）

回滚+多版本控制（原子性）

3、隔离级别

隔离级别

脏读

不可重复读

幻影读

未提交读

√

提交读

√

可重复读

√

可串行化

（1）未提交读

事务中的修改，即使没有提交，对其他事务也是可见的。

会存在脏读，不可重复度，幻读问题。

（2）已提交读

事务只能读取已经提交的事务所做的修改。也就是未提交前，事务做的修改对其他事务不可见。

会存在不可重复度，幻读问题。

（3）可重复读

保证在同一个事务中多次读取同样数据的结果是一样的。

会存在幻读问题。

（4）串行化读

强制事务串行执行。需要加锁实现。

实际运用中，按业务场景需求来，隔离级别越低，事务并发性能越高。mysql默认可重复读。

4、脏读、不可重复读、幻读

（1）脏读：（读取到的数据与数据库数据值不一致）事务A读取了事务B未提交的修改数据，事务B随时可能回滚。

（2）不可重复读取：同一个事务，多次读取同一个数据，值都不同。

事务A 读取数据值为2，此时事务B修改数据值为5并提交，事务A再一次读取数据，值为5。事务A前后两次读取的值并不相同。

（3）幻读：事务A获取全表数据为10条，此时事务B新增/删除一条数据，事务A再一次获取全表数据为11/9条，就好像出现幻觉一样。

不可重复读重点在于其他事务修改数据并提交了：值不同
幻读的重点在于其他事务新增或者删除数据并提交了：数据量不同了

5、丢失更新

多个事务同时更新同一个数据，后更新的事务把前面事务更新的结果覆盖了，导致丢失更新。

现在数据库当一个事务更新一条记录时，就会加排他锁，另外一个的更新就会阻塞住。所以数据库本身并没有丢失更新的问题。通常都是由于程序产生的。可以通过乐观锁的方式来解决这个问题。

四、mysql优化

对于开发者，优化在于【开发规范】【数据库索引】【慢查询（慢sql）处理】。

从查询来说：

--走索引，理论上来说，数据达到一定数据量，就应该建索引。
能否使用覆盖索引，减少回表。
是否建立联合索引，区分度高的放在左边，并考虑最左匹配原则
尽量避免索引失效的操作，比如使用函数操作或者表达式计算
通过explain命令查看，sql是否走索引，走什么索引。

走了索引仍然查询缓慢：

1、考虑删除旧数据，能删数据的业务很少很少，所以一般都不是删除数据。
2、加缓存，能接受非真正实时数据，查询条件简单，如果查询条件相对复杂且多变不建议使用
3、如果有字符串检索的场景导致查询低效，考虑将数据导入es类搜索引擎
Mysql-->es需要对应的同步程序（监听binlog，解析binlog导入es）

五、慢sql优化

参考博客https://blog.csdn.net/ByteDanceTech/article/details/125795764?spm=1000.2115.3001.5927

1、影响执行效率的主要因素:

数据量：数据量越大，IO次数越多
取数据的方式：

数据在缓存还是磁盘，是否通过全局索引快速寻址

数据加工的方式：

排序、子查询、聚合、关联等，一般要先把数据取到临时表中，再对数据进行加工。

对于数据量比较多的计算，会消耗大量计算节点的 CPU 资源，让数据加工变得更加缓慢。

是否选择了合适的 join 方式

2、优化思路：

减少数据量扫描。尽量在查询中加入一些可以提前过滤数据的条件。

减少交互次数（减少网络传输）：将数据存放在更快的地方（redis,es等）

减少服务器 CPU 开销（减少 CPU 及内存开销）

避免大事务操作

增加资源

3、数据库结构优化

尽量使用数字型字段

只含数值信息的字段，尽量不设置为字符串。引擎在处理查询和连接时逐个比较字符串中的每个字符，数字型只需比对一次。

尽可能使用vachar代替char

vachar是可变长度的，char是固定大小的。

当索引列大量重复数据时，去掉该列索引。可以认为是建立合适的索引
范式优化：表的设计合理化（符合 3NF），比如消除冗余（节省空间）；
反范式优化：比如适当加冗余等（减少 join）

4、SQL 语句优化

优化 GROUP BY:

提高 GROUP BY 语句的效率, 将不需要的记录在 GROUP BY 之前过滤掉
（原sql）select class , avg(age) from student group by class having job = 'class';
（优化）select class , avg(age) from student where job = 'class' group by class;

尽量避免使用子查询
用 IN 来替换 OR（原因）
区分 in 和 exists（具体）
读取适当的记录 LIMIT M,N，而不要读多余的记录
优化 Join 语句
使用具体字段代替select*，使用覆盖索引，避免回表（看需要来）
尽量避免索引失效的操作：比如使用函数操作或者表达式计算等
尽量减少多表连接，可以考虑从业务上优化

5、大表优化

分库分表（水平、垂直）
读写分离（主从架构）

六、explain

查看查询语句的执行计划，是查询性能优化的重要工具

详情见下面文章

数据库--explain的使用_上房揭瓦ing的博客-CSDN博客

七、MySQL死锁

现象：接口响应时间超长，耗时几十秒才返回错误提示，后台日志中出现Lock wait.....

原因：锁等待超过了innodb_lock_wait_timeout（默认是50s）设置的时间,所以报错

场景：
（1）同一个事务内先后对同一数据进行插入和更新。
（2）多台服务器操作同一数据库
（3）短时间内出现高并发现象，spring事务造成数据库死锁，后续操作超时抛出异常
（4）两个事务同时更新、删除，事务等待另一个事务释放锁。

排查：

#	查看当前被锁的表
show OPEN TABLES where In_use > 0;
#查看数据库当前进程，看有无正在执行的慢SQL记录线程。
show processlist;
#查看当前运行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#或
select trx_state,trx_started,trx_mysql_thread_id,trx_query 
from information_schema.innodb_trx;

#查询全局的自动提交是否开启：1表示开启
show global variables like 'autocommit';	
select @@autocommit;
#查询当前会话等待事务锁超时时间：
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
#修改当前会话等待事务锁超时时间：
SET innodb_lock_wait_timeout=10000;

解决
（1）治标
改大锁定等待时间，找到事务对应的线程kill掉
（2）治本
找到锁表的事务，分析锁表原因，进行优化

八、MySQL数据库cpu飙升如何处理

来源百度的一个提问下的答案

先用操作系统命令top观察是不是mysqld占用导致的
如果是show processlist 查看当前进程，是不是有消耗资源的sql在运行，找出消耗高的sql，看看执行计划是否准确，index是否缺失，或者实在是数据量太大造成
一般来说，是要kill掉这些线程，等进行相应的调整（比如说加索引、改sql、改内存参数)之后，再重新跑这些SQl
(连接数激增)也有可能是每个sql消耗资源并不多，但是突然之间，有大量的session连进来导致cpu飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。
如果不是，找出占用高的进程，并进行相关处理

九、存储过程、视图、触发器

1、存储过程

是什么

存储过程就是一组预先编译好的sql语句集

存储过程的优缺点

优点：

提高重用性，减少冗余；
减少了sql的编译次数，减少了与服务器连接交互的次数；
生产环境下，可通过直接修改存储过程的方式修改业务逻辑或bug，而不用重启服务器。
执行速度快，存储过程经过编译之后会比单独一条一条编译执行要快很多。

缺点：

过程化编程，复杂业务处理的维护成本高。
调试不便。
因为不同数据库语法不一致，不同数据库之间可移植性差。

如何使用

create procedure 存储过程名(in/out/inout参数列表)
begin
//....存储过程体
end;

（1）空参数列表的存储过程

#空参数列表的存储过程
DELIMITER $$
CREATE PROCEDURE allEmpList()
BEGIN
	SELECT * from emp;
END $$
#调用
call allEmpList()

（2）带 in的存储过程

#示例
DELIMITER $$
CREATE PROCEDURE getEmpByEmpName (IN empName VARCHAR(255))
BEGIN
	     SELECT *  FROM  emp t    LEFT JOIN dept t2 ON t.dept_id = t2.dept_id WHEREt.emp_name = empName ;
END$$

#调用
call getEmpByEmpName('张三'

（3）带out的存储过程

DELIMITER $$
CREATE PROCEDURE getEmpNameByEmpId (
	IN empId INT,
	OUT empName VARCHAR (255)
)
BEGIN
	      SELECT t.emp_name INTO empName  FROM emp t   WHERE t.emp_id = empId ; 
END$$ 

#使用
call getEmpNameByEmpId(1,@empName);
select @empName as empName;

（4）带INOUT的存储过程

DELIMITER $$
CREATE PROCEDURE customSum (INOUT a INT, INOUT b INT)
BEGIN
SET a = a * 12 ;
SET b = b * 12 ; 
END$$

#使用
set @a = 10;
set @b = 11;
call customSum(@a,@b);
select @a,@b;

2、视图

是什么

视图是虚拟表，不存储数据，存储的是sq

优点

查询简单化，提高复杂SQL语句的复用性和表操作的安全性

使用场景（什么情况下使用）

重用SQL语句

简化复杂的SQL操作

使用表的组成部分而不是整个表

为了保护数据

视图的操作

创建：create view视图名 as select语句;
   删除：drop view视图名;
   修改：replace view 视图名 as select语句;
   使用：select * from 视图名 [where 条件];

什么情况不可以执行insert、update和delete操作？

多表连接，包含distinct、聚合函数，group by、order by、union、union all、包含子查询等。

3、存储过程和视图的区别

（1）存储过程是程序化的sql可以实现一般sql不能实现的功能。
如：先检索一个表得到一些数据，经过一定的编辑后更新到另外一个表中、这就可以用不带参数的存储过程实现。
（2）视图是虚拟表，不存储数据，存储的是sql，检索他的时候，实际上是执行定义它的sql语句。

4、触发器

触发器是用来响应激活或者触发数据库行为事件的存储程序。通常，触发器用来作为数据库操作语言的响应而被调用，触发器可以被用来作为数据校验和自动反向格式化。

十、其他

1、数据库常用函数

1、数字函数：
ABS(x)：绝对值
AVG(expression)：平均值
MAX（expression）：最大值
MIN（最小值）
2、字符串：
CONCAT(s1,s2…sn)：合并成一个字符串
SUBSTR（）字符串截取
REVERSE（）颠倒顺序
3、日期函数：
DATE()：提取日期值
DAY(d)：提取日期部分
4、高级点的函数
IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2
left（V1, V2）：取字符串V1 前V2个字符