- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

mysql总结

Studying-swz 发表于 2022/08/20 16:46:03 2022/08/20

【摘要】 @TOC -----------------------------------事务、锁部分---------------------------------------- 1.事务经典例子：转账A----B，A-100，B+100，必须同时成功特性：A：原子性，事务是一组操作，要不全部成功，要不全部失败C：一致性，事务执行前后要保持一致性，是指前后状态都是一致性，而不能有中间状态I：隔离性...

@TOC

-----------------------------------事务、锁部分----------------------------------------

1.事务

经典例子：转账
A----B，A-100，B+100，必须同时成功
特性：
A：原子性，事务是一组操作，要不全部成功，要不全部失败
C：一致性，事务执行前后要保持一致性，是指前后状态都是一致性，而不能有中间状态
I：隔离性，多个并发的事务之间互不影响，或者说一个事务对其他事务不可见
D：持久性，redo log实现，保证了数据库崩溃也不会影响其结果，因为在每次启动时，会先进行redo log日志的恢复：主要是数据页修改的位置、目前日志写入的位置，来进行恢复，读取到引擎层的缓冲池中，等待时机进行刷到数据库中。

2.并发产生的问题

脏读：读到了其他事务未提交的数据，之后其他事务并且进行了回滚
不可重复读：一个事务读取到了数据，其他事务进行了修改，之前按的事务再次读取，发现值变了。
幻读：主要区别在于读取的是一共多少行这种（读取的一个范围的内容），一般需要是MVCC+Next-Key LOCK实现。

3.四种隔离级别

未提交读：以上问题都有
已提交读：解决脏读
可重复读：解决了脏读、不可重复读。一般可用MVCC实现。（Innodb默认的）
可串行化：完美的，但是一帮并发度就很低了。

4.mysql锁

1.行锁、表锁
相比：innodb支持两种，而mysiam只支持表锁；表锁的封锁粒度大，并发度低，但是维护来说消耗低；行锁的封锁粒度小，并发读大，但是维护复杂；并且行锁会产生死锁，而表锁不会.
2.读锁、写锁
读锁可以与其他读锁一起使用，而写锁只能自己，也叫排他锁；如看房子例子，所有人可以一起看，而如果一个人买了，其他人就不能去看了。
3.意向锁
是不存在的，是表级锁，当你要想获取读锁或者写锁的时候，必须获得先获得意向锁。
用法：减少锁的扫描，当你想往表上加锁的时候，你之前需要扫描一行行是否有行锁；当有了意向锁，你只需要看这个表是否有意向锁，没有直接加，有，就不能加锁
4.乐观锁、悲观锁
乐观锁：CAS 、版本号
悲观锁：读锁、写锁、间隙锁 gap lock、next-key lock（=gap lock + record lock）

5.表结构设计

1.尽量设定主键
主键会自动生成聚簇索引，在查询以及修改的时候可以加快查询的速度
2.主键推荐使用自增id
因为在添加数据的时候，会涉及到节点的拆分，所以如果是uuid的话，那么会涉及到比较多的分裂，而自增id顺序插入
3.字段不要为null，定义为not null
因为定义为null，其也是一种数据结构，会有较多的消耗字节，并且在统计的时候，可能会出现一些问题，有个字段为null，导致计数不对。
4.char与varchar
前者是固定长度，不足的时候补空格，而后者是变长的，是你真正存储了多少，而占用多少；一般对于身份证、MD5加密后字段我们使用char，相对比较稳定，查找快
5.int(10)
表示显示的宽度，对于int‘(1) int(10)存储大小是一样的，但是显示来看，长度不一样，并且不足的时候补零。

6.innodb和Myisam的区别？

前者支持行级锁+表级锁，后面支支持表级锁
前者支持外键，后面不支持
前者支持MVCC，后面不支持
前者支持事务，后面不支持
前者支持全文索引，后面不支持
全文索引：基于相似度的查询，比like快

-----------------------------------表优化部分----------------------------------------

1.表的处理？

1.横向分表
当表的行数很多，成千上万那种，我们可以分成多个表（根据主键），然后按照最后的尾号进行分表，然后在进行不同表的查询。
2.纵向分表
主要涉及到某些字段数据量比较大，而我们不常用的时候，如：字段id-标题-摘要-内容，对于展示，我们只需要字段id-标题-摘要，而内容数据量太大，我们可以单独拿出来 字段id-内容，然后点击详情的时候，再查找

2.超大分页如何处理？

1.数据库层面
limit 100000,10
select * from table where age > 20 limit 1000000,10
select * from table where id in (select id from table where age > 20 limit 1000000,10)
使用索引覆盖来使得数据量减少
2.缓存方面
提前将内容读取到缓存中
3.需求角度
一般不做类似的需求（我做不了）

3.慢查询-Sql语句很慢？

一般我们可以查看慢查询日志进行查看哪条语句执行的慢
分析：
1.explain，看看是否走了索引
2.数据量是否太大，尝试横向分表、纵向分表
3.load额外的数据，优化sql语句，索引覆盖等等

4.三范式

1.单列不可分
如：地址拆分为“xx省，xx市，xx县”
2.不存在部份依赖，不能依赖于主键的一部分
如：某一行的数据只能和一列相关，但是像订房间：订单编号、房间编号、联系人，当一个人顶了多个房间，那么就会出现联系人这个数据的冗余，所以需要把其单独拿出来
3.不存在传递依赖
如：学号、姓名、学院、学院电话；明显学号----学院-------学院电话，有传递依赖

5.主从复制，双写一致性？

这个见redis，原理差不多。
https://blog.csdn.net/qq_37534947/article/details/120411463?spm=1001.2014.3001.5501

6.视图和表的关系?

视图是一个或者多个基本表（视图）导出的表，而视图是一张虚表，不存储真正的数据
优点：1.简化用户的操作；2.让用户可以从多个角度看待同一数据；3.提供一定的数据逻辑独立性
缺点：1.修改限制，对于视图的修改，需要涉及到对原数据表的修改，当然对于简单的操作，这是很方便的，但是当涉及到的视图比较复杂，则整体可以修改比较复杂；2.对于一些查询来说，要把视图的查询转换成对应基本表的查询，如果视图是由一个复杂的多表查询定义，则在转换中需要一定的时间。
创建视图：create view XXX as XXXXXXXXXXXXXX;

7.防止Sql注入的方法？

7.1为什么参数化SQL查询可以防止SQL注入??

一条语句执行，会在mysql的服务层进行进行连接–分析器—优化器，然后执行引擎调用api（引擎层）：
```
select count(1) from students where name='张三'
```
name 参数为张三‘ or '1=1 ，这个参数也会被编译器一同编译？不会的
```
 select count(1) from students where name='张三' or '1=1' 
```
传参的过程将分开了，此时会先将语句分为：
第一步：
```
 select count(1) from students where name=
```
第二步：
传参，此时不会在编译，而是直接拼接（作为参数），这样然后执行，引擎层识别错误。

7.2进行普通用户和管理员的一个权限划分

8.MONyog监控工具

登陆页面
监控页面
性能监控仪表

Mysql指标：1.Connections，2.Cache Misses，3.Statments，4.Database Throughputs（吞吐量）。

系统资源指标：1.CPU Usage，2…DISK IO。
线程页面

显示当前由MySQL执行的线程数，发送到MySQL的每个查询都在线程中执行。
查询分析器信息展示

查询慢日志

参考链接：
https://www.nowcoder.com/discuss/389444?channel=-1&source_id=discuss_terminal_discuss_history_nctrack&ncTraceId=56d60df585ea479caa086fb1c6e658f2.609.16313638423932825
https://www.cnblogs.com/panda-sweets/p/10064368.html

------------------------------------日志部分---------------------------------------------

前提知识：
客户端连接MySQL的过程：
mysql其实是分为服务层和引擎层的：
1.服务层主要包含：客户端进行连接器的连接、然后下一步进行查询缓存，但是缓存有一个弊端，就是涉及到update语句缓存就会失效，（这里类似于redis的缓存旁路失效），所以较高的版本，就把mysql缓存去掉了，然后进行分析器（包括语法分析和词法分析）、之后是优化器，以及最后的执行器。

2：引擎层：这里和磁盘进行交互，首先包含一个 行记录缓冲池 ，然后主要是一些引擎的api接口，我们可以调用进行写入和查询；

1.一条更新语句的执行过程

总览：

主要讲一下执行器到引擎层以及和磁盘交互的过程：

1：当缓冲池有数据的时候，我们会进行加锁，访问修改
2：当缓冲池没有数据的时候：

我们首先要在磁盘文件进行磁盘数据的一个加载，加载到引擎层的缓冲池中。
然后将其写入uodo log日志中，用于回滚。
然后进行事务的更新过程，利用执行器进行更行缓冲池的数据，（这时候我们可以称其为脏数据），因为不同于数据库中的数
如果此时宕机，不用操作，因为没有刷到磁盘中，因为目前都没有提交事务，所以此时宕机都会消失，不会影响磁盘。
redo log日志的写入，这时候：就是记录下来你对数据做了什么修改，比如对“磁盘中某一页的id=10这行记录修改了name字段的值为xxx”，这就是一个日志。mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种先写日志，再写磁盘的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。
redo log写入缓冲区，在你更新的的时候，有三种策略：
5.1）0，事务提交的时候，每s进行写入os buffer，然后立即刷新到磁盘。----所以mysql宕机，会有1s数据的丢失
5.2）1，事务提交的时候，直接写入os buffer中，然后刷新到磁盘。----对于每次提交的事务，都会写到了磁盘，不会有数据的丢失。(推荐，虽然有性能随时，但是保证数据的安全)
5.3）2，事务提交的时候，直接写入os buffer中，然后，每s刷到磁盘。----mysql宕机数据不会丢失，但是如果整个物理机器宕机，也会有1s的数据丢失。

现在来思考🤔:事务已经提交了，redo log 进入了磁盘，但是数据仍然在内存中，还未刷入磁盘，此时机器宕机，数据会丢失？
答案是不会，因为虽然内存里的修改成name=xxx的数据会丢失，但是redo日志里已经说了，对某某数据做了修改 name=xxx。所以此时mysql重启之后，mysql会进行检查恢复，会根据redo日志去恢复之前做过的修改，我们看下图。
binlog日志的写入缓冲区，在你准备提交事务的时候，有两种策略：
6.1）0，提交事务的时候，先刷新到os的cache中，由系统最后再刷新到磁盘文件。
6.2）1，提交事务的时候会立即刷新到磁盘中。
6.3）N，N个事务提交的时候才会写到磁盘中。
基于bin log 和 redo log的事务提交
最后一步，我们将binlog 写入磁盘后，会把此次更新的binlog文件名称和这次更新的binlog日志在文件中的位置（即：名称+位置），都会写入到redo log中，同时再redo log中写入commit标记，在完成这个事情后，才算完成最终的提交。
引：两阶段提交
**以上所有日志的写入磁盘，都是在事务提交前进行的（可以这样理解，写入的时候，都算事务提交的阶段，但是最后的commit才标志着最后的事务的完成），**可有看到redo log 经历了prepare和commit两个状态的阶段，为什么有两个阶段？
1）先写redo log，再写bin log，如果redo log成功，bin log写入失败，则系统恢复的时候，redo log进行了恢复，bin log就会少了这次数据的修改，所以之后恢复临时库的时候，bin log这句就丢失了。
2）先写bin log，再写redo log，如果bin log写完，而redo log崩溃，恢复的时候，redo log不会操作，但是如果恢复临时库的时候，bin log同样会执行这句，同样导致当前库和临时库的不同。
而两阶段就不同了：
1）binlog有记录，redo log commit阶段，所以是正常完成的事务，不用恢复。
2）bin log 有记录，redo log prpare阶段，所以此时需要重新提交事务。
3）bin log无记录，redo log prepare阶段，回滚事务。
注：其实这里还是不太理解，但是本质主要是解决了redo log和bin log日志的数据库一致性。
事务完成
mysql会有个后台线程将内存数据（某个时间内）刷入到磁盘

在你IO线程把脏数据刷回磁盘之前，哪怕mysql宕机崩溃也没关系，因为重启之后，会根据redo日志恢复之前提交事务做过的修改到内存里去，就是id=10的数据的name修改为了xxx，然后等适当时机，IO线程自然还是会把这个修改后的数据刷到磁盘上的数据文件里去的。

2.redo log的记录形式

redo log是一个循环写入的日志：
write pos：日志刷盘位置
check point：数据页的刷盘位置
所以一般来说，write pos会在check point前面，而check point和 write pos之间就是待要数据页的刷新操作，而write pos和check point之间是空的位置。

对于每次重启或者是崩溃恢复的时候：
1：write pos会在check point前面，则进行check point到write pos的一个恢复
2：当然存在check point在write pos之前的情况，这时候，就不需要恢复了，write pos继续在check point开始即可
3：如何区分两者谁在前，谁在后，是有一个LSN位置序号进行比较的。

3.三种日志的比较

有了上面的理解：
1）bin log
三种格式：
STATMENT格式： 基于SQL语句的复制，每一条修改数据的sql语句会记录到bin log中
row格式： 基于行的复制，不记录每条sql语句的上下问信息，仅需要记录哪条数据被修改了，以及修改成什么样了，这里不同于redo log，redo log是记录的物理页变更，精确到了哪一页的哪一行的直接数据变化。
MIXED格式： 一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog
用途：主要用于主从复制。

2）redo log 持久性的表示：记录的是物理页的记录
出发原则：
因为 Innodb 是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！
所以：redo log其实只是记录了物理页的变更，并且可以顺序IO写入。

用途：主要用于崩溃恢复的一个持久性。

3）undo log日志
数据库事务四大特性中有一个是 原子性 ，具体来说就是原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上，原子性底层就是通过 undo log 实现的。undo log 主要记录了数据的逻辑变化 ，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。

同时， undo log 也是 MVCC(多版本并发控制)实现的关键。

4.MVCC的实现原理

出发点：读写分离，对于读不加锁，写加锁，可以实现提交读、可重复读的隔离级别。
实现：undo log 多版本链条 + ReadView（读视图）
1.undo log链条：
每条数据其实有两个隐藏的字段：trx_id(最近更新这条数据的事务id)、roll_pointer（指向之前的undo log）
每次修改数据的时候都会更新trx_id、roll_pointer这两个字段，同时之前的索格数据块找对应的undo log通过roll_pointer指针串联起来，形成一个版本链条。
2.ReadView 读视图：
执行一个事务，会生成一个ReadView，主要由一下组成：
m_ids:Mysql哪些事务执行还未提交的事务列表
min_ids:m_ids最小的事务id
max_ids:m_ids最大的事务id
cur_ids:当前的事务id
查询（并发）：
1.trx_id（查询的数据的事务id） < min_ids:说明肯定能查询到；
2.trx_id > max_ids:说明查询不到，只能通过undo log多版本链条查看以前的；
3.trx_id = cur_ids:说明是当前事务修改的，可以查询到；
4.min_ids < trx_id < max_ids并且trs_id在当前m_ids中：其他事务正在处理，所以不能查询到。
3.已提交读
本质：每次查询的时候，都会生成一个新的读视图
事务B修改后，提交；这时候的事务A再次查询的时候，就会有新的readview，而此时的ReadView的事务列表中就会去掉事务B，从而查询到事务B修改后的。
4.可重复读
本质：每次查询的时候，不需要都生成一个新的读视图，只需要事务刚开始查询的时候的第一次即可。
这样的话，在事务B修改后，提交后，事务A再次查询的时候，因为B是当前的事务列表中，所以会根据undo log链条进行向前查询，从而查询查询到之前的。

5.一条查询语句的执行过程

select查询顺序应该是：
from---->where ----->group by----->having --------->select--------->order by ------>limit的吗？
所以相对于磁盘来说：

从磁盘读取表到内存中
where进行第一步过滤
group by进行分组，生成多张临时表
对每张临时表进行过滤
然后进行条件查询，将结果又组合到一张表中
然后进行排序
最后进行limit过滤

注：当然前面还是会有：连接器—查询缓存-----分析器----优化器-----执行引擎-------缓冲池行记录-------然后进行读取。
补充：
在 MySQL5.7.5 之前的版本，ONLY_FULL_GROUP_BY sql mode默认不开启。在5.7.5或之后的版本默认开启。允许在 having condition 中使用 select list 中的 alias。

6.日志最后

感谢下面各位大佬博客的讲解；然后对于我来说的一些认识：从三种日志，然后分别讲解了三种日志的不同点，针对三种日志在一条查询语句和一条修改语句的过程，当然查询语句不会用到，然后redo log的一个崩溃恢复的过程，包括日志的write pos和数据页的checkpoint，但是修改语句会涉及到redo log和bin log的一个顺序问题，而由此产生了两段提交过程来解决它俩不一致的问题，当然某些底层细节还是不太懂，就是在不同的刷盘策略下，其应对崩溃的解决方法，这里只知道个整体把，到了细节还是需要用到的时候会有更深的理解吧。

------------------------------------索引部分---------------------------------------------

1.hash索引

CREATE TABLE `testhash` (
  `fname` varchar(50) DEFAULT NULL,
  `lname` varchar(50) DEFAULT NULL,
  KEY `fname` (`fname`) USING HASH
) ENGINE=MEMORY;

为什么用MEMORY存储引擎，因为mysql只有MEMORY存储引擎显示支持哈希索引。

看如下查询：

select lname from testhash where fname ='Peter'

Mysql首先计算Peter的哈希值是8784，然后到哈希索引中找到对应的行指针，根据指针找到对应的数据行。索引只存储哈希码及行指针，所以索引的数据结构非常的紧凑，这也让哈希索引查找速度非常快，但是哈希索引也有他的限制。

注：是不是和MISIAM存储引擎很相似，都是索引和数据分开的。

2.innodb默认不支持哈希索引

只能说一般是InnoDB自己在优化的过程中，才会自动的创建一张表来生成哈希索引。

3.优点和缺点

等值查询比较快
不适合范围查找

4.聚集索引和非聚集索引

概念：什么是聚集索引，什么是非聚集索引？
主要出发点：是要看索引的排列顺序和表记录的排列顺序是否一致，因为对于InnoDB存储引擎来说，其主要是B+树，而B+树的数据结构中，存储的索引+数据，同时保证了排列顺序的一致，所以是聚集索引，而MySIAM是非聚集索引。
聚集索引：
主键索引：在结构中同时保留了主键key+行记录 .ibd结构 （数据就这一份）
辅助索引：在结构中为辅助键的key + 主键的key （这是什么结构？不知道）
回表：所以对于辅助索引一般会有回表操作，但是对于索引覆盖可以避免回表，情形如下：
```
select age from employee where age < 20
```
非聚集索引
主键索引：在结构中保存的是主键key + 行记录的地址 .myd .myi（本质：数据和索引分离）
辅助索引：结构和主键索引一样

5.常见的索引

主键索引（唯一为空）
- 如果定义了主键，那么InnoDB会使用主键作为聚簇索引
- 如果没有定义主键，那么会使用第一非空的唯一索引（NOT NULL and UNIQUE INDEX）作为聚簇索引
- 如果既没有主键也找不到合适的非空索引，那么InnoDB会自动生成一个包含了ROW_ID值的列作为聚簇索引，行都会根据这个ROW_ID排序。
- 注：没有主键的结果
  很明显，缺少主键的表，InnoDB会内置一列用于聚簇索引来组织数据。而没有建立主键的话就没法通过主键来进行索引，查询的时候都是全表扫描，小数据量没问题，大数据量就会出现性能问题。
唯一索引
就是唯一值的列建立的索引
普通索引
不涉及唯一+非空

6.哪些需要创建索引？哪些不需要创建索引？

需要创建索引
1.经常查询的字段可以加快速度
2.主键（默认聚簇索引）
3.where中常用的列字段
4.在经常需要排序的列上加索引，**因为本身索引查出来就是有序的，**所以会加快速度
5.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
哪些不需要创建索引
1.查询中很少使用的字段
2.对于那些定义为text, image和bit数据类型的列不应该增加索引：这是因为，这些列的数据量要么相当大，要么取值很少。 取值大，索引很复杂，取值小，建立不出什么东西。
3.对于那些只有很少数据值的列也不应该增加索引，因为在查询的时候会涉及到很大数据行的，并且涉及到回表，所以不一定很快。

7.联合索引以及最左匹配原则？

MySql使用索引时需要索引有序，假设现在建立了“name，age，school”的联合索引。
那么索引的排序为从左到右：先按照name排序，如果name相同则使用age排序，如果age也相等则使用school排序。
原则：建立联合索引的时候要把查询频繁的字段放在前面。

8.如何查看创建的索引有没有被使用到？

命令：explain
分析：type字段
all、range、ref（该索引列的值并不唯一）、ref_eq(使用了索引，并且值唯一)、const（主键放在where后）

9.索引失效原则？

使用不等<>= 查询
不符合最左匹配原则
联合索引，使用范围查找，后面的部分
like字段 like"%a"，最左边是通配符
mysql优化器本身分析出不走索引快的时候
索引列进行操作（表达式运算/分组函数）

10.索引的优缺点？

优点：加快查询的速度
缺点：数据的写入过程会涉及到索引的更新，也就是索引的维护
1.节点的插入：主要涉及到节点的分裂，当然还有节点索引值的修改
2.节点的删除：主要涉及到节点的合并，当然还有索引值的修改

https://cloud.tencent.com/developer/article/1692119
https://blog.csdn.net/doctor_who2004/article/details/77414742
https://www.nowcoder.com/discuss/389444?type=post&order=time&pos=&page=1&ncTraceId=&channel=-1&source_id=search_post_nctrack

------------------------------------补充部分---------------------------------------------

1.主键超键候选键外键

主键：数据库表中对储存数据对象（一行）予以唯一和完整标识的数据列或属性的组合。唯一且不能为空。
超键：也是唯一标识，但是可以包含其他非主键字段，所以其包含主键。
候选键：也是唯一标识，但是是最小的超键，即不包含无关的属性。
外键：一个表中存在的另一个表的主键是这个表的外键。

如：表（学号、姓名、身份证）
学号：主键、超键
身份证：候选键、主键、超键
学号+姓名：超键
关系：超键>候选键>主键

２.drop,delete与truncate的区别

drop直接删掉表,包括数据+结构。
truncate删除表中数据，再插入时自增长id又从1开始。
delete删除表中数据，可以加where字句。

3.内连接、外连接、笛卡尔积

内连接：join/inner join
- 等值连接：select * from R,S where R.B=S.B;或select * from R inner join S on R.B=S.B;
  
  注：相同的部分都保留，而自然连接不是。
交叉连接/笛卡尔积（cross join）
- select * from R,S;或select * from R cross join S;
自然连接（natural join）
- select R.A,R.B,S.C from R,S where R.B = S.B;或select * from R natural join S;
  
  注：先进行笛卡儿积操作，然后重复的属性只保留一份。
外连接（natural join）
- 左外连接：select * from R left join T on R.B=T.B;
- 右外连接：select * from R right join T on R.B=T.B;
全连接
Mysql不存在！
三张表：
R表
S表
T表