题目一
MyISAM 和 InnoDB 的区别, 什么时候选择 MyISAM
参考回答
InnoDB 是目前 MySQL 主流版本 (5.6,5.7,8.0) 默认的存储引擎, 支持事务, 外键, 行级锁, 对于并发条件下要求数据的一致性, 适用于对数据准确性要求高的场景.
MyISAM 只支持表级锁, 数据排列是按照插入顺序, 没有做规则排序. 适合应用以查询和插入为主, 只有很少量的更新和删除操作, 对事务的完整性和并发性要求不是很高的场景.
实际运用
看到很多人在选择存储引擎的时候会无脑的选择 InnoDB, 这个选择合理的一点是如果对数据准确性要求没有那么高, 直接用 NoSQL 就好了. 用 MySQL 就是为了可靠啊.
但是实际工作中, 我设计的数据库中通常都会有几张 MyISAM 的数据表, 通常用来存储历史记录, 与使用 InnoDB 存储实时记录信息的配合使用.
举个例子: 比如一条物流信息, 在实时的表里存着目前物流的状态: 比如配送中. 这条物流在历史上经过了: 正在通知快递公司取件, XXX 已收揽等, 这张记录表基本只有插入和查询, 并且丢失一个中间状态不影响当前结果, 这就很合适用 MyISAM.
题目二
简述 MySQL 的 MVCC 多版本并发控制
参考回答
MVCC 是对于事务隔离级别的读已提交 RC 和可重复读 RR, 基于乐观锁的实现. 在 LBCC(基于锁的并发控制)RC,RR 和串行化分别是通过加行锁, 间隙锁和表锁来基于悲观锁实现. 而乐观锁的原理就是在特定的时间点 (RC 是每次读时, RR 是事务开始时) 生成一个当前快照, 读数据读取快照, 只在提交时判断是否有冲突, 类似于 Git 的 branch 和 commit.
MVCC 会在新开启一个事务时, 给事务里包含的每行记录添加一个当前事务 ID 和回滚指针. 并包含一个 Read View,Read View 里保存了当前活跃的事务列表, 小于这些列表的最近的事务 ID 才是可见的. 这样保证了读到的都是已提交的事务.
实际运用
MVCC 不仅可以用于数据库, 也是很常见的一种并发控制手段. 比如使用有限状态自动机来控制的订单状态, 在更新订单状态的时候先查询当前状态, 比如当前状态是订单未提交, 则更新时 update XXX set status='订单已提交' where status='订单未提交', 如果执行这条语句时, status 已经发生了改变, 这条语句就执行失败了. 这样不通过数据库自身事务的 MVCC, 在业务逻辑里也实现了 MVCC 思想的乐观锁设计.
题目三
分布式锁的实现方式
参考回答
主流有三种
1 > 基于数据库
1.1 > 基于数据库主键: 插入一条数据, 指定主键. 如果有两条插入会主键冲突, 并发执行失败
1.2 > 基于数据库排他锁: 提交一个 update 事务, 如果这个事务不提交, 其他也对锁定范围内执行 update 就会阻塞, 解决并发问题
2 > 基于缓存比如 Redis 的 setNX
3 > 基于 zookeeper
实际运用
相信很多人选择分布式锁都是选择第二种, 第三种虽然并发性差一下, 如果本来就引入了 zk, 而没有缓存, 而分布式锁应用量又不那么大, 为了减少引入新组件带来的风险和维护成本, 也有可能选择 zk. 很多人大概认为自己没有用过基于数据库的分布式锁, 实际上在不使用 MVCC 的时代并不是这样.
在使用 spring 进行业务开发的时候, 常见的一种场景就是使用 spring 配置事务. 默认级别是 Repeatable Read 可重复读. 在这里面如果使用的是 LBCC, 一进入事务就加入一个排他锁, 比如 insert,update,delete 或者 select XXX for update. 然后做其他的, 比如进行一个 RPC 调用. 这时候一旦出现并发, 只有一个能顺利执行, 其他都会被阻塞. 实际上就相当于使用了分布式锁.
题目四
为什么采用 B + 树作为索引结构?
参考回答
如果采用 Hash 表, 范围查找需要全表扫描; 如果采用二叉查找树, 由于无法保证平衡, 可能退化为链表; 如果采用平衡二叉树, 通过旋转解决了平衡的问题, 但是旋转操作效率太低; 如果采用红黑树, 树太高, IO 次数多; 如果采用普通 B 树, 节点要存数索引和数据, 一个内存页可存储的数据还是少, 另外范围查找也需要多次 IO;
而 B+Tree 有三个特性:
1 > 非叶子节点不存储 data, 只存储索引(冗余), 可以放更多的索引
2 > 叶子节点包含所有索引字段
3 > 叶子节点用指针链接, 提高范围查询的性能
实际运用
在分布式场景下, 我们的业务 ID 都是全局唯一的字符串. 如果单纯从业务上来考虑, 用业务 ID 作为数据库的主键就足够了. 可以 DBA 往往要求使用整型的自增主键作为数据库主键, 而这个主键对业务来说就是个浪费, 没有任何业务含义.
如果了解了索引的底层结构就不难理解
1 > 整型比字符串占用更少的空间
2 > 同时大小比较也很快
3 > 之所以要自增是每次插入新的记录, 对于叶子节点来说: 记录会顺序的添加到当前索引节点的后续位置, 当一页写满, 会自动开辟一个新的页. 而如果使用非自增主键, 就需要插入的时候移动数据, 甚至目标页面可能已经被回写到磁盘上而从缓存中清掉, 此时又要读回来. 分页操作造成大量的碎片, 必须通过优化操作重建表并优化填充页面.
题目五
什么叫做覆盖索引?
参考回答
只需要在一棵辅助索引树上就可以获取 SQL 所需要的所有列数据, 不需要回表.
实际运用
一些持久层框架比如 mybatis 的 generator 插件可以自动生成 sql 配置文件, 这些配置文件往往效率很低. 但是刚毕业的同学很多都不会去改这个文件, 比如只需要个别列的时候会用 java 的 lambda 表达式等方式从逻辑上做处理. 结果造成一些性能的问题.
我在根据一些条件进行范围查找的时候, 如果只需要返回 ID 或者个别列, 会自己去改 mybatis 的 generator 自动生成的文件, 原因是尽量使用覆盖索引, 较回表速度快.
想验证是否使用了覆盖索引, 可以用 explain 执行计划, 查看 extra 字段, 如果只显示 Using index 说明正确使用了覆盖索引. 如果 extra 为空或者除了 using index 还有 filesort 说明触发了回表.
题目六
查询在什么时候不走索引
参考回答
主要三种情况
1 > 不满足走索引的条件, 常见的情况有
1.1 > 不满足最左匹配原则
1.2 > 查询条件使用了函数
1.3>or 操作有一个字段没有索引
1.4 > 使用 like 条件以 % 开头
2 > 走索引效率低于全表扫描, 常见的情况有
2.1 > 查询条件对 null 做判断, 而 null 的值很多
2.2 > 一个字段区分度很小, 比如性别, 状态
3 > 需要回表的查询结果集过大, 超过了配置的范围
实际运用
使用索引是为了对查询做优化, 要衡量优化效果需要数据说话. 所以需要一些工具来衡量, 常用的有:
1 > 慢查询日志
开启慢查询日志, 可以针对慢 SQL 进行分析看看哪些可以用索引进行优化
2>show processlist
show processlist 语句可以查看当前正在执行的 SQL, 如果一些 SQL 执行慢, block 了其他的 SQL, 这是个很好的工具
3>show profile 分析 SQL
使用这个工具可以分析出时间究竟耗费在哪个阶段. 先查询是否支持
支持的话, 可以用 select @@profiling 查看是否开启, 如果结果为 0 说明未开启. 需要先 set @@profiling=1;
这时候就可以用 show profiles 查看每一条 SQL 语句耗费的时间
show profile for query XXID 可以查看具体耗费在哪个阶段
4>Trace 分析优化器的执行计划
使用 set optimizer_trace='enabled=on',end_markers_in_json=on; 可以打开 trace 分析, 想查看具体的优化器执行计划, 只要执行
select * from `information_schema`.optimizer_trace 即可
点击开每一步都有很详细的分析
总结
知识只要学透了都可以灵活运用. 在运用的时候要注意衡量效果. 一个常见的误区是开发人员无脑的在 MySQL 上层加缓存, 用来提高效率. 但是缓存只适用于读多写少的情况, 比如在金融交易系统, 数据读写比例 1:1. 数据总是查询出来下一刻就被更新了, 这时候用缓存反而加重系统的负担和复杂性.
这时候, 我们可以先利用工具查询数据库的读写比例. 比如 show global status like 'Com_______' 这个 SQL 可以查看 select,update,insert,delete 都被执行了多少次.
或者 show global status like 'Innodb_row_%' 除了查看 Innodb 的读写情况, 还可以查看锁的情况.
思考
请网上搜索一下「58Mysql 军规」然后思考每条军规背后的理论支撑.
来源: https://www.cnblogs.com/xiexj/p/12952378.html