Visitar URL original
The following article is from moon聊技术 Author moon聊技术
玩玩技术，聊聊人生，看看生活，搞搞理想
35 个 Mysql 常见的小问题，moon 浓缩成了精华，祝大家卷的愉快~
1.说一说三大范式
2.MyISAM 与 InnoDB 的区别是什么？
3.为什么推荐使用自增 id 作为主键？
4.一条查询语句是怎么执行的?
5.使用 Innodb 的情况下，一条更新语句是怎么执行的?
6.Innodb 事务为什么要两阶段提交?
7.什么是索引?
8.索引失效的场景有哪些?
9.为什么采用 B+ 树,而不是 B-树
10.WAl 是什么?有什么好处?
11.什么是回表?
12.什么是索引下推?
13.什么是覆盖索引?
14.什么是最左前缀原则?
15.普通索引和唯一索引该怎么选择?
16.什么是事务?其特性是什么?
17.事务的隔离级别?
18.binlog 是做什么的?
19.undolog 是做什么的?
20.relaylog 是做什么的?
21.redolog 是做什么的?
22.redolog 是怎么记录日志的?
23.redolog 和 binlog 的区别是什么?
24.说一说 mvcc 吧，有什么作用?
25.一条 Sql 语句查询一直慢会是什么原因?
26.一条 Sql 语句查询偶尔慢会是什么原因?
27.Mysql 主从之间是怎么同步数据的?
28.主从延迟要怎么解决?
29.删除表数据后表的大小却没有变动,这是为什么?
30.为什么 VarChar 建议不要超过255?
31.分布式式事务怎么实现?
32.Mysql 中有哪些锁?
33.为什么不要使用长事务?
34.buffer pool 是做什么的?
35.说说你的 Sql 调优思路吧
「第一范式」：数据库中的字段具有「原子性」，不可再分，并且是单一职责
「第二范式」：「建立在第一范式的基础上」，第二范式要求数据库表中的每个实例或行必须「可以被惟一地区分」。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键
「第三范式」：「建立在第一，第二范式的基础上」，确保每列都和主键列直接相关，而不是间接相关不存在其他表的非主键信息
但是在我们的日常开发当中，「并不是所有的表一定要满足三大范式」，有时候冗余几个字段可以少关联几张表，带来的查询效率的提升有可能是质变的
5.Innodb 有 「redolog」 日志文件，MyISAM 没有
6.「Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI」
8、「InnoDB 必须有唯一索引(主键)」,如果没有指定的话 InnoDB 会自己生成一个隐藏列Row_id来充当默认主键，「MyISAM 可以没有」
用以下语句来举例，c 字段无索引，id 为主键索引
update T set c=c+1 where id=2;
1.执行器先找引擎取 id=2 这一行。id 是主键，引擎直接用树搜索找到这一行
2.执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口「写入这行新数据」
3.引擎将这行新数据更新到内存中，同时将这个更新操作「记录到 redo log 里面」，此时 redo log 处于 「prepare」 状态。然后告知执行器执行完成了，随时可以提交事务
4.执行器「生成这个操作的 binlog」，并把 binlog 「写入磁盘」
5.执行器调用引擎的「提交事务」接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，「更新完成」
先写 redolog 后写binlog。假设在 redolog 写完，binlog 还没有写完的时候，MySQL 进程异常重启，这时候 binlog 里面就没有记录这个语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 「binlog 丢失」，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写 binlog 后写 redolog。如果在 binlog 写完之后 crash，由于 redolog 还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是 binlog 里面已经记录了“把c从0改成1”这个日志。所以，在之后用 binlog 来恢复的时候就「多了一个事务出来」，恢复出来的这一行 c 的值就是 1，与原库的值不同。
可以看到，「如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致」。
相信大家小时候学习汉字的时候都会查字典，想想你查字典的步骤，我们是通过汉字的首字母 a～z 一个一个在字典目录中查找，最终找到该字的页数。想想，如果没有目录会怎么样，最差的结果是你有可能翻到字典的最后一页才找到你想要找的字。
索引就「相当于我们字典中的目录」，可以极大的提高我们在数据库的查询效率。
以下随便列举几个，不同版本的 mysql 场景不一
1.最左前缀法则（带头索引不能死，中间索引不能断
2.不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描
3.不能继续使用索引中范围条件（bettween、<、>、in等）右边的列，如：
select a from user where c > 5 and b = 4；
4.索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
5.索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描。
6.索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描，也是最左前缀原则。
7.索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
8.索引字段使用 or 时，会导致索引失效而转向全表扫描
B+ 树只在叶子结点储存数据，非叶子结点不存具体数据，只存 key，查询更稳定，增大了广度，而一个节点就是磁盘一个内存页，内存页大小固定，那么相比 B 树，B- 树这些「可以存更多的索引结点」，宽度更大，树高矮，节点小，拉取一次数据的磁盘 IO 次数少，并且 B+ 树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，效率更高。
WAL 就是 Write-Ahead Logging，其实就是「所有的修改都先被写入到日志中，然后再写磁盘」，用于保证数据操作的原子性和持久性。
好处:
回表就是先通过数据库索引扫描出该索引树中数据所在的行，取到主键 id，再通过主键 id 取出主键索引数中的数据，即基于非主键索引的查询需要多扫描一棵索引树.
如果存在某些被索引的列的判断条件时，MySQL 将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，「只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器」 。
覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，可以减少回表的次数。比如:
select id from t where age = 1;
id 为主键索引，age 为普通索引，age 这个索引树存储的就是逐渐信息，可以直接返回
最左前缀其实说的是，在 where 条件中出现的字段，「如果只有组合索引中的部分列，则这部分列的触发索引顺序」，是按照定义索引的时候的顺序从前到后触发，最左面一个列触发不了，之后的所有列索引都无法触发。
比如「有一个 (a,b,c) 的组合索引」
where a = 1 and b = 1
此时 a,b 会命中该组合索引
where a = 1 and c = 1
此时 a 会命中该组合索引, c 不会
where b = 1 and c = 1
此时不会命中该组合索引
查询
更新
所以「唯一索引更加适合查询的场景,普通索引更适合插入的场景」
事务是指是程序中一系列操作必须全部成功完成，有一个失败则全部失败。
特性
「脏读」
「不可重复读」
「幻读」
binlog 是归档日志，属于 Server 层的日志，是一个二进制格式的文件，用于「记录用户对数据库更新的SQL语句信息」。
主要作用
undolog 是 InnoDB 存储引擎的日志，用于保证数据的原子性，「保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修改之前的数据，可以用于回滚」，同时可以提供多版本并发控制下的读（MVCC）。
主要作用
relaylog 是中继日志，「在主从同步的时候使用到」，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容。
master 主节点的 binlog 传到 slave 从节点后，被写入 relay log 里，从节点的 slave sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器，从而「使从服务器和主服务器的数据保持一致」。
redolog 是 「InnoDB 存储引擎所特有的一种日志」，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
可以做「数据恢复并且提供 crash-safe 能力」
当有增删改相关的操作时，会先记录到 Innodb 中，并修改缓存页中的数据，「等到 mysql 闲下来的时候才会真正的将 redolog 中的数据写入到磁盘当中」。
InnoDB 的 redo log 是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。「从头开始写，写到末尾就又回到开头循环写」。
所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生「内存抖动」现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷盘了。
MVCC:多版本并发控制，是现代数据库(包括 MySQL、Oracle、PostgreSQL 等)引擎实现中常用的处理读写冲突的手段，目的在于「提高数据库高并发场景下的吞吐性能」。
在 MVCC 协议下，每个读操作会看到一个一致性的快照，「这个快照是基于整个库的」，并且可以实现非阻塞的读，用于「支持读提交和可重复读隔离级别的实现」。
MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务 ID，在同一个时间点，不同的事务看到的数据是不同的，这个修改的数据是「记录在 undolog 中」的。
「同步策略」：
在使用 delete 删除数据时，其实对应的数据行并不是真正的删除，是「逻辑删除」，InnoDB 仅仅是将其「标记成可复用的状态」，所以表空间不会变小
当定义varchar长度小于等于255时，长度标识位需要一个字节(utf-8编码)
当大于255时，长度标识位需要两个字节，并且建立的「索引也会失效」
以下并不全，主要理解下锁的意义即可
buffer pool 是一块内存区域，为了「提高数据库的性能」，当数据库操作数据的时候，把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool 里面的数据，数据库的增删改查都是在 buffer pool 上进行
buffer pool 里面缓存的数据内容也是一个个数据页
其中「有三大双向链表」:
预读机制: