更多特训营笔记详见个人主页【面试鸭特训营】专栏
250101
1. MySQL 中如何解决深度分页的问题？
什么是深度分页问题
-
-- 查询第 2 页的 10 条数据，即第 21 ~ 30 条数据 select * from student order by id limit 2, 10; -- 查询第 200000 页的 10 条数据，即第 200001 ~ 200010 条数据 select * from student order by id limit 200000, 10; -
在分页查询时，当页码数越大，查询性能越差，耗时越长
-
-- 以下两条查询语句是等效的，都是查询第 2000001 ~ 2000010 条数据 select * from student where age >= 18 limit 200000, 10; -- 跳过前 20000 条记录（逗号前的数据指定） -- 返回接下来的 10 条记录（逗号后的数据指定） select * from student where age >= 18 limit 10 offset 200000; -- 返回 10 条记录（由 limit 指定） -- 跳过前 200000 条记录（由 offset 指定）
造成原因
- MySQL 引擎层将 offset + limit 条记录，全都返回给了 server 层
- 经过 server 层的 where 条件筛选后，直接放弃掉前 offset 条记录，从符合条件的第 offset 条记录开始，发送给客户端，发送了 limit 条记录之后，查询结束
- 由于引擎层的 “懒惰” ，给 server 层造成巨大压力，以及给数据传输带来了资源消耗
优化方案
子查询优化
-- 优化前
select * from student where age >= 18 limit 200000, 10;
-- 优化后
select * from student where age >= 18 and id >=
(select id from student where age >= 18 order by id limit 200000, 1)
order by id limit 10;
- age 有索引的情况下，查询时会直接扫描 age 的二级索引，二级索引的数据量更少， 且在子查询中不需要回表可以直接得到 id ，将子查询得到的 id 再去主键索引查询，速度更快，数据量也更小
- 如果直接扫描主键索引的话，由于主键索引包含全部的数据，所以数据量较大
标记分页
-- 优化前
select * from student where age >= 18 limit 200000, 10;
-- 优化后
select * from student
where age >= 18 and id > last_max_id -- last_max_id是上一页最后一条数据的id
order by id limit 10;
- 每次分页都返回当前最大的 id ，下次查询时带上这个 id ，利用 id > last_max_id 进行过滤
- 这种查询仅适用于连续查询的情况，如果有跳页则无法生效
elasticsearch
- 可以考虑使用搜索引擎来解决这个问题
- 但是 es 也会有深度分页的问题
2. 什么是 MySQL 的主从同步机制？它是如何实现的？
- 主从同步机制是将主数据库（Master）上的数据同步到一个或多个从数据库（Slave）中
- 主从复制的三种方案
- 异步复制
- 主库不需要等待从库的相应
- 性能较高，数据一致性较差
- 同步复制
- 主库同步等待所有从库确认收到数据
- 性能较差，数据一致性高
- 半同步复制
- 主库等待至少一个从库确认收到数据
- 性能折中，数据一致性较高
- 异步复制
异步复制
- MySQL 默认方案
- 具体流程如下
- 主库
- 接受到提交事务的请求
- 更新数据
- 将数据写到 binlog 中
- 给客户端响应
- 推送 binlog 到从库中
- 从库
- 由 I/O 线程将同步过来的 binlog 写入到 relay log 中
- 由 SQL 线程从 relay log 重放事件，更新从库数据
- 给主库返回响应
- 主库
- 异步复制有丢失数据的风险
- 当【数据已经从主库推送给从库，主库已经给客户端响应，但从库还还没有同步来自主库的数据】时，如果这时候主库挂了，从库晋升成主库，就会导致部分数据信息丢失
同步复制
- 主库将 binlog 复制到所有的从库后，只有等所有的从库全都相应了之后，才会给客户端响应
- 性能很差，一般不会采用
半同步复制
- MySQL 5.7 版本之后才有，有一个参数可以选择【成功同步几个从库，就会返回响应】
- 举例说明
- 总共有 3 个从库，配置的参数为 1
- 只有在主库等到了 3 个从库中至少 1 个从库，给主库响应，说它复制成功了之后
- 主库才会返回响应给客户端，此时主库不会等待其他另外两个未响应的从库
- 性能较好，数据可靠性也得到增强，只有当主库和响应数据信息的从库同时挂掉，才会导致数据缺失
3. 如何处理 MySQL 的主从同步延迟？
延迟带来的问题
- 可能在某个时刻，主库存在数据 A ，由于同步延迟，从库还未同步成功数据 A
- 这时候在从库查数据 A 就会查不到
延迟能否完全消除
- 延迟不可能完全消除掉，只能是尽可能的减少延迟时间
主库和从库的作用
- 主库：负责增删改等写操作
- 从库：负责查询等读操作
优化延迟的方案
二次查询
- 优化措施
- 如果在从库查不到数据，第二次查询的时候就去主库里查
- 存在风险
- 会导致主库承担一部分读的压力
- 要是有【老登】故意查主库里也不存在的数据，会对主库产生冲击
强制转移
- 强制将【写之后立马读的操作】转移到主库
- 代码编写的时候，直接把写入后立马查询的操作固定去主库查询
- 不推荐，比较死板
使用缓存
- 主库写入数据后，立马同步到缓存上
- 可以先查缓存，避免了从库同步延迟的问题
- 但是又会引入缓存一致性的问题