MySQL常见问题与解答

MySQL常见问题与解答原创

财税管理

IT技术

艾贺521

4人赞赏了该文章 988次浏览编辑于2019年04月25日 21:42:33

收集常见数据库问题

锁机制介绍：行锁、表锁、排他锁、共享锁，悲观锁、乐观锁。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

读锁：也叫做共享锁，即多个用户可以同时的进行并发读操作
写锁：也叫排他锁，在进行写锁的时候，其它的操作无法进行

悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。比如Java synchronized。

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。

2. 乐观锁的业务场景及实现方式；
乐观锁（Optimistic Lock）：
每次获取数据的时候，都不会担心数据被修改，所以每次获取数据的时候都不会进行加锁，但是在更新数据的时候需要判断该数据是否被别人修改过。如果数据被其他线程修改，则不进行数据更新，如果数据没有被其他线程修改，则进行数据更新。由于数据没有进行加锁，期间该数据可以被其他线程进行读写操作。

乐观锁：比较适合读取操作比较频繁的场景，如果出现大量的写入操作，数据发生冲突的可能性就会增大，为了保证数据的一致性，应用层需要不断的重新获取数据，这样会增加大量的查询操作，降低了系统的吞吐量。

Java的CAS操作则是一种乐观方式，不断的循环，当目标值与期望值相等的时候认为操作是成功的。

3. 事务介绍，分布式事物的理解，常见的解决方案有哪些，什么是两阶段提交、三阶段提交；
数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

分布式事务：一个大型项目涉及到多个库的操作，这些操作在一个事务中完成。

CAP理论，Base理论。

解决方案：

数据库官方提供的XA标准实现
基于可靠消息的分布式事务
TCC

参考：常用的分布式事务解决方案

4. MySQL记录binlog的方式主要包括三种格式？每种格式的优缺点是什么？

STATEMENT：每一条会修改数据的sql都会记录在binlog中。
ROW：　不记录sql语句上下文相关信息，仅保存哪条记录被修改，也就是说日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。
MIXED：是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种；

参考：MySQL binlog相关分析

5. 分布式事务的原理2阶段提交，同步\异步\阻塞\非阻塞；
两段式提交是把分布式事务分为两个阶段：都有事务管理器发起(协调者)

准备阶段，协调者发指令，参与者评估自己的状态，写WAL日志，然后锁定资源，不会提交
提交阶段，如果每个资源都返回准备成功，那么协调者发起提交指令，如果任何一个参与者返回准备失败，那么则发起终止指令，取消事务

一个故事：
出场人物：老张，水壶两把（普通水壶，简称水壶；会响的水壶，简称响水壶）。
1 老张把水壶放到火上，立等水开。（同步阻塞）
老张觉得自己有点傻
2 老张把水壶放到火上，去客厅看电视，时不时去厨房看看水开没有。（同步非阻塞）
老张还是觉得自己有点傻，于是变高端了，买了把会响笛的那种水壶。水开之后，能大声发出嘀~~~~的噪音。
3 老张把响水壶放到火上，立等水开。（异步阻塞）
老张觉得这样傻等意义不大
4 老张把响水壶放到火上，去客厅看电视，水壶响之前不再去看它了，响了再去拿壶。（异步非阻塞）

所谓同步异步，只是对于水壶而言。
普通水壶，同步；响水壶，异步。
虽然都能干活，但响水壶可以在自己完工之后，提示老张水开了。这是普通水壶所不能及的。
同步只能让调用者去轮询自己（情况2中），造成老张效率的低下。
所谓阻塞非阻塞，仅仅对于老张而言。
立等的老张，阻塞；看电视的老张，非阻塞。
情况1和情况3中老张就是阻塞的，媳妇喊他都不知道。虽然3中响水壶是异步的，可对于立等的老张没有太大的意义。所以一般异步是配合非阻塞使用的，这样才能发挥异步的效用。

6. 数据库事务隔离级别，MySQL默认的隔离级别、Spring如何实现事务、JDBC如何实现事务、嵌套事务实现、分布式事务实现；

Read Uncommited：只要有修改都会立刻被看到，隔离级别最低
Read Commited：只要数据被提交了，就能被读到，Oracle 默认级别
Repeatable Read：在同一个事务中，不管数据有没有被其他线程修改，提交，同一个事务多次读到的数据是相同的
Serialize：阻塞读写，只能有一个事务进行，隔离级别最高

Spring基于Aop，底层用的还是JDBC，参考Spring事务原理分析

JDBC则是首先设置autoCommit(false)，然后在commit或rollback。

嵌套事务则根据数据库的传播行为，如下：

PROPAGATION_REQUIRED 如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。这是最常见的选择。
PROPAGATION_SUPPORTS 支持当前事务，如果当前没有事务，就以非事务方式执行。
PROPAGATION_MANDATORY 使用当前的事务，如果当前没有事务，就抛出异常。
PROPAGATION_REQUIRES_NEW 新建事务，如果当前存在事务，把当前事务挂起。
PROPAGATION_NOT_SUPPORTED 以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
PROPAGATION_NEVER 以非事务方式执行，如果当前存在事务，则抛出异常。
PROPAGATION_NESTED 如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

当传播行为是PROPAGATION_NESTED，底层的JDBC类型必须是3.0

参考：Spring事务传播行为详解

7. SQL的整个解析、执行过程原理；
SQL解析是一项复杂的技术，一般都是由数据库厂商来掌握，当然也有公司专门提供SQL解析的API。如果做数据库中间件，就必须要进行SQL解析。

SQL解析与优化是属于编译器范畴，和C等其他语言的解析没有本质的区别。其中分为，词法分析、语法和语义分析、优化、执行代码生成。对应到MySQL的部分，如下图

image

具体参考：SQL解析在美团点评中的应用

8. SQL行转列
参考：MySQL行转列实现和总结

9. 为什么要使用索引？
当使用全表扫描的时候，要把所有的数据加载进内存中，进行扫描，数据量很大的时候，速度会非常慢。很多时候，我们要尽量避免全表扫描，依靠索引，我们就能大幅提升查询的速度。

10. 什么样的信息能成为索引？

唯一键，普通键，主键都可以。

11. 索引的数据结构？
二叉查找树，B树，B+树，Hash

二叉查找树可能会极端化，完全的像某一边倾斜。查询速度就降低到O(n)
B树，又叫平衡多路查找树，根节点至少有两个孩子，树中每个节点最多包含m(m>=2)个孩子，除根节点和叶子节点外，其它每个节点至少有ceil(m/2)个孩子，m是几阶树，所有叶子节点都位于同一层。一般不会出现二叉树完全倾斜的情况
B+树是B树的变体，非叶子节点仅用来做索引，叶子节点用来存储数据。存储的关键字个数更多，所有叶子节点都有一个链表指针指向下一个叶子节点(方便在叶子节点做范围统计)。B+树更适合做存储索引。