MySQL面试题的补充总结

MySQL面试题的补充总结

MySQL联表查询条件中WHERE和ON的区别

先来看一个关联查询的DEMO:

select * from student s left join class c on s.classId=c.id order by s.id

查询结果如下：

下面在ON条件中拼接一个条件：s.name="张三"

select * from student s left join class c on s.classId=c.id and s.name="张三" order by s.id

查询结果如下：

在ON条件中拼接一个条件：c.name="三年级三班"

select * from student s left join class c on s.classId=c.id and c.name="三年级三班" order by s.id

查询结果如下：

数据库在通过连接两张表或者多张表来返回记录的时候，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left join的时候，on和where条件的区别如下：

• on条件是在生成临时表的时候使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录
• where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

总之：LEFT JOIN联表查询ON可以理解为是在两张表中进行条件筛选（即，在生成临时中间表的时候进行条件筛选），满足条件的则展示左右表拼接的数据记录，不满足条件的，则优先展示左表中的数据，右表中不满足条件的字段为NULL。而联表查询的时候WHERE则可以理解为是在一张表进行条件过滤（即，将生成的临时表看成一张表）

---

假设有两张表：

表1：tab1

id	size
1	10
2	20
3	30

表2：

size	name
10	AAA
20	BBB
30	CCC

两条SQL：

---sql1
select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’

--- sql2
select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL的过程：

-- 中间表on条件: 
tab1.size = tab2.size

-- 再对中间表过滤where 条件：
tab2.name=’AAA’

第二条SQL的过程：

-- 中间表on条件: (条件不为真也会返回左表中的记录)
tab1.size = tab2.size and tab2.name=’AAA’

其实以上结果的关键原因就是left join，right join，full join的特殊性，不管on上的条件是否为真都会返回left或者right表中的记录，full则具有left和right的特性的并集。而inner join没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

MySQL主从复制与表拆分相关问题

问题：如何做慢SQL优化？

首先要搞明白慢的原因是什么：是查询条件没有命中索引？还是load了不需要的数据列？还是数据量太大？所以优化也是针对这三个方向来的：

• 首先使用explain分析语句的执行计划，查看使用索引的情况，是不是查询没走索引，如果可以加索引解决，优先采用加索引解决。
• 分析语句，看看是否存在一些导致索引失效的用法，是否load了额外的数据，是否加载了许多结果中并不需要的列，对语句进行分析以及重写
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行垂直拆分或者水平拆分

1、水平拆分与垂直拆分

1.1、水平分表

例如：QQ的登录

• 假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候数据库都要从这100亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1亿条记录，比如：qq0，qq1，qq2，qq3...qq99表。
• 用户登录的时候，可以将用户的id%100，那么就会得到0-99的数，查询表的时候，将表名 qq+取模的数连接起来，就构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。

1.2、垂直分表

• 垂直分割指的是：表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。

例如：学生答题表student_question，有如下5个字段：

Id，name，分数，题目，回答。其中题目和回答是比较大的字段，Id，name，分数比较小。

如果我们只想查询id为8的学生的分数：select 分数 from tt where id=8;

• 虽然只是查询分数，但是题目和回答这两个大字段也是要被扫描的，很消耗性能。然而我们只需要关心分数，并不想查询题目和回答。这种情况下就可以使用垂直分割。
• 我们可以把题目单独放到一张表（tt表）中，通过 id与tt表简历一对一的关系，同样将回答单独放到一张表中。这样我们查询student_question中的分数就不会扫描题目和回答这两个大字段了。

1.3、小结

• 水平分割是表中数据量过大的时候，严重影响查询效率时，将一张数据量庞大的表按照某种条件进行拆分成N张名称不同，字段和数据类型相同的表。
• 垂直分表是表中记录数不多，但是字段很多，且字段长，表占用空间大的情况下，把大的字段拆分到另一个表，并且该表与原表是一对一的关系。

---

2、主从复制

这里，先解释一下binlog和relaylog日志的作用：

• binlog（二进制日志）：用于主从复制、实现主从同步
• relaylog（中继日志）：用于数据库主从同步，将主库发送来的binlog先保存在本地，然后从库根据该日志内容回放主库进行的操作

为什么使用主从复制、读写分离？

主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。你想一下，假设是单机，读写都在一台MySQL上面完成，性能肯定不高。如果有三台MySQL服务器，一台主机（master）只负责写操作，两台从机（slave）只负责读操作，性能不就大大提高了吗？

所以主从复制、读写分离就是为了数据库能支持更大的并发。

随着业务量的扩展、如果是单机部署的MySQL，会导致I/O频率过高。采用主从复制、读写分离可以提高数据库的可用性。

2.1 MySQL主从复制介绍

MySQL主从复制涉及到三个线程，一个运行在主节点（Log Dump Thread），其余两个（I/O Thread，SQL Thread）运行在从节点。如下图所示：

主从复制默认是异步的模式，具体过程如下：

• 从节点上的I/O线程连接主节点，请求读取主库的二进制日志文件（bin log日志）的指定位置（bin log position）之后的日志内容；
• 主节点线程接收到来自从节点的I/O线程的请求后，读取主节点中的二进制日志文件（bin log 日志）的指定位置之后的日志信息，返回给从节点。
  • 返回给从节点的内容信息中除了日志所包含的信息之外，还包括此次返回的信息 bin-log file（二进制日志文件）以及bin-log position (读取的位置)
• 从节点的I/O线程接收到内容后，将接收到的日志内容更新到relay log（中继日志）中，并将读取到的bin log file（文件名）和position（位置）保存到mast-info文件中，以便在下一次读取的时候能够清楚的告诉Master：“我需要从某个bin-log的哪个位置开始往后的日志内容”
• 从节点的SQL线程检测到relay-log中新增加了内容后，会解析了relay-log的内容，并在本数据库中执行。

这里过程说的有点过于细致了，下面对其原理做一些容易理解的概述：

• 当master节点进行insert、update、delete操作的时候，会按照顺序写入到主机的binlog日志中
• slave从库连接master主库，master有多少个slave就会创建多少个binlog dump线程
• 当master节点的binlog发生变化的时候，binlog dump线程会通知所有的slave节点，并将相应的binlog内容推送给slave节点。
• 从机的I/O线程接收到binlog内容后，将内容写入到本地的relaylog（中继日志）
• 从机的SQL线程读取I/O线程写入的relaylog，并且根据relaylog的内容对从数据库回放主库之前的操作

2.2 异步复制，主库宕机后，数据可能丢失？

可以使用半同步复制或者全同步复制

半同步复制

修改语句写入binlog后，不会立即给客户端返回结果。而是首先通过logdump线程将binlog发送给从节点，从节点的I/O线程收到binlog内容后，写入到relaylog（中继日志），然后返回ACK给主节点，主节点收到ACK后，在返回给客户端成功。

半同步复制的特点：

• 确保事务提交后binlog至少传输到一个从库，不保证从库执行完这个事务的binlog。
• 性能有一定的降低，响应时间会更长
• 网络异常或从库宕机，卡住主库，直到超时或者从库恢复

全同步复制：主节点和所有从节点全部执行了该事务并确认才会向客户端返回成功。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会受到严重影响。

主库压力大，从库复制很可能出现延迟？

• 可以使用并行复制（并行是指从库多个SQL线程并行执行relaylog），解决从库复制延迟的问题。
• MySQL5.7中引入基于组提交的并行复制，其核心思想：一个组提交的事务都是可以并行回放，因为这些事务都已经进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。
• 判断事务是否处于一个组是通过last_commited变量，last_commited表示事务提交的时候，上次事务提交的编号，如果事务具有相同的last_commited，则表示这些事务都在一组内，可以进行并行的回放。

MySQL读写分离原理

简单来说，读写分离是基于主从同步的情况下实现的，只在mysql从服务器上读。基本原理是让主数据库处理事务性查询，而从数据库处理select查询。数据库复制被用来把事物性查询导致的变更通过主从复制同步到集群中的数据库。
具体的读写分离方式请参考这篇博客（不在详细介绍）：https://www.cnblogs.com/liushuqing/articles/11602031.html