---
layout: post
title:  Mysql45讲关键知识点
category: 技术
tags: mysql db web
keywords: mysql db web
description: 
date: 2022-10-01
author: followtry
published: true
---


## 为什么Mysql会抖一下

是因为Mysql更新数据只写到redo的log里，达到阈值后会刷脏页，占用CPU资源。脏页是指内存页数据和磁盘页数据不一致的情况。
发生场景
1. redolog写满到阈值后，需将对应的内存页数据刷到磁盘上。 需要尽量避免，否则所有更新操作都会被hang主
2. **内存不足，刷脏页到磁盘上**。常态，最需要关心的。
3. 资源空闲时，刷页。也会时不时的刷下脏页。资源空闲期刷脏页，系统不会有压力。
4. Mysql正常关闭的时候，刷脏页到磁盘。关闭时刷脏页，正常操作，也不会关心性能。

影响性能的几种情况
1. 一次刷脏页太多。
2. 日志写满了，更新全部堵塞住。

### 刷脏控制策略

1. 设置`innodb_io_capacity` 参数，可通过`fio`工具测试磁盘IO的IOPS


### 注意事项
1. 比如查询操作，触发刷脏页时，会判断旁边的页是不是脏页，是的话一起刷掉，而且还可以向下传递。将相邻的脏页一起刷掉。 这也就会导致SQL操作时的rt可能被预期的更慢。可以通过参数`innodb_flush_neighbors`来控制，设置为1则会查找邻居脏页，设置为0则不查找邻居脏页。对于机械硬盘，建议`innodb_flush_neighbors`设置为1，对于SSD，建议设置为0。因为SSD的IOPS比机械硬盘高很多。


## 为什么表数据删掉一半，表文件大小不变

参数`innodb_file_per_table`控制着表数据存放为值,`ON`表示表数据放在.idb后缀文件中。`OFF`表示系统共享空间，默认值为`ON`。而且如果为OFF，则即使删除表，表空间也不会释放。推荐设置为`ON`。

delete 记录和表都不会释放表空间，会使得被释放的页被复用，也就是会产生空洞。

那如何去去掉空洞呢？

1. 重建表。 使用`alter table A engine=InnoDB`命令。其隐含意思是`alter table t engine=innodb,ALGORITHM=inplace;`
2. `analyze table t` 只是对表的索引信息做重新统计，没有修改数据
3. `optimize table` 等于 recreate+analyze

## COUNT(*) 这么慢，我怎么办？

几种获取总数的方式
1. count(*) 会扫描全表，可能会影响性能。 Mysql做了优化，不取值，按行累加
2. count(字段) 表示满足条件的数据里，参数"字段"不为NULL的数量
3. count(id) 因为主键id不能为空，会按主键行累加
4. count(1)  innodb遍历整个表，但不取值，返回给server后，server放入一个1，按行累加


**按照效率排序的话**，`count(字段)`<`count(主键 id)`<`count(1)`≈`count(*)`，所以我建议你，尽量使用 count(*)

## 写Binlog后，commit前崩溃掉怎么保证数据完整。

崩溃恢复的判断规则
1. 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
2. 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整。 binglog完整则提交事务，否则回滚事务。


## redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 `XID`。崩溃恢复的时候，会按顺序扫描 redo log：
1. 如果redo log中有commit则直接提交
2. 如果redo log中只有prepare，但没有commit，则带着`XID`去binlog中寻找，在binlog中通过checksum判断binlog是否完整

## 为什么还要两阶段提交呢？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？

如果必须要举一个场景，来说明这么做的必要性的话，那就是事务的持久性问题。对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。

## 能不能只用redo log，不用binlog

不能。

1. 归档。 redo log循环写，起不到归档的作用。
2. mysql系统依赖，mysql的高可用基础就依赖binlog的复制

## 为什么这些SQL语句逻辑相同，性能却差异巨大？

对索引字段做函数操作，会使得Mysql放弃走树搜索索引

变种情况包括： 
1. 隐式类型转换。 mysql支持字符串转数字的优化，是可以走索引的，反之则不走树索引。
   select * from user where id = '123'; id为int类型，是可以走索引的。
2. 隐式字符编码转化，如果两张表join，且其编码不同，则会进行编码转换，也不走所用了。


## 为什么我只查一行的语句，也执行这么慢？

### 查询长时间不返回

 表被锁住了，可能在等 
1. `等 MDL 锁`
2. `等 flush`
3. `等行锁`

### 查询慢

1. 查询条件上无索引
2. 一致性读和当前读。 如果有大量更新，则一致性读需要执行n次undo日志才能获取到。而当前读直接获取最新的版本数据。

### 幻读是什么，幻读有什么问题？

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。使用Gap锁可以解决幻读问题。有了Gap锁之后，如果想往符合gap条件的间隙加一条数据，会被阻塞。作用于`insert`操作。而Gap锁也只会在`可重复读`的隔离级别下才会生效。

不可重复读是：在同一事务中，两次查询获取到的同一条数据内容不一致。是因为其他事务更新了当前数据，悲观锁是对行加行锁，乐观锁方式是通过MVCC机制。作用于`update`和`delete`操作

Gap锁和行锁合成`next-key锁`.即不仅要锁住当前存在行记录，也要锁住行记录间的间隙，使得新的符合条件数据不能插入。

也就是说**Mysql默认的可重复读级别，如果使用了Gap锁，会使得锁住范围变大，容易导致死锁**，因此有部分公司会将Mysql隔离级别设置为读可提交，并将binlog设置为row方式配合使用。

` select ... for update`语句容易引起死锁。


## "饮鸩止渴"的方式提高性能的办法

### 短连接风暴

#### 使用高峰期，先处理掉那些占着连接但是不工作的线程。

`show processlist`.

如果是连接数过多，你可以**优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。**

断开连接的命令： ` kill connection + id `

#### 减少连接过程的消耗

如果数据库是被连接打挂了，可以选择跳过权限验证环节。

方法是：**重启数据库，并使用–skip-grant-tables 参数启动**

### 慢查询性能问题

#### 索引没设计好

在MySQL 5.6版本以后可**直接执行 alter table 语句**

#### 语句没写好

改写语句，比如改掉隐式函数转换等

#### Mysql没选对索引

可以使用`force index` 强制指定索引

### QPS 突增问题

1. 业务上紧急下掉该功能
2. 数据库上删掉该业务使用的数据库账号，使其不能访问数据库
3. 语句改写，将访问大的QPS的SQL语句改写为select 1。 仅仅只是用于止血，业务上的风险很高