MYSQL 那点破事~ 索引、SQL调优、事务、B+树、分表 (1)

答案：mysql锁分为共享锁( S lock ) 、排他锁 ( X lock )，也叫做读锁和写锁。根据粒度，可以分为表锁、页锁、行锁。

什么是间隙锁？

答案：间隙锁是可重复读级别下才会有的锁，mysql会帮我们生成了若干左开右闭的区间，结合MVCC和间隙锁可以解决幻读问题。

如何避免死锁？

答案：死锁的四个必要条件：1、互斥 2、请求与保持 3、环路等待 4、不可剥夺。

• 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。

• 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。

• 避免大事务，将大事务拆成多个小事务

• 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。

• 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。

• 尽量用主键/索引去查找记录

• 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，避免多个表join，将复杂 SQL 分解为多个简单的 SQL。

数据库的隔离级别？

答案：读未提交、读已提交、可重复读（mysql的默认级别，每次读取结果都一样，但是有可能产生幻读）、串行化。

Mysql有哪些类型的索引？

答案：

• 普通索引：一个索引只包含一个列，一个表可以有多个单列索引。

• 唯一索引：索引列的值必须唯一，但允许有空值

• 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 聚簇索引：也称为主键索引，是一种数据存储方式。B+Tree结构，非叶子节点包含健值和指针，叶子节点包含索引列和行数据。一张表只能有一个聚簇索引。

• 非聚簇索引：不是聚簇索引，就是非聚簇索引。叶子节点只是存索引列和主键id。如果sql还要返回除了索引列的其他字段信息，需要回表，第一次索引一般是顺序IO，回表的操作属于随机IO。回表的次数越多，性能越差。此时我们推荐覆盖索引

什么是覆盖索引和回表？

答案：

1、覆盖索引，指的是在一次查询中，一个索引包含所有需要查询的字段的值，可能是返回值或where条件

select buyer_id from order where money>100

假如我们创建了一个(money，buyer_id)的联合索引，索引的叶子节点包含了buyer_id的信息，则不会再回表查询。

2、回表，指查询时一些字段值拿不到，需要到主键索引B+树再查一次。

Mysql的最左前缀原则？

答案：即最左优先，在检索数据时从联合索引的最左边开始匹配，直到遇到范围查询（如：> 、< 、between、like等）

例子：where a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)组合索引，d是用不到索引的；如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

线上SQL的调优经验？

答案：

• 1、slow_query_log 日志中收集到的慢 SQL ，结合 explain 分析是否命中索引。

• 2、减少索引扫描行数，有针对性的优化慢 SQL。

• 3、建立联合索引，由于联合索引的每个叶子节点包含检索字段的信息，按最左前缀原则匹配后，再按其它条件过滤，减少回表的数据量。

• 4、还可以使用虚拟列和联合索引来提升复杂查询的执行效率。

官方为什么建议采用自增id 作为主键？

答案：自增id是连续的，插入过程也是顺序的，总是插入在最后，减少了页分裂，有效减少数据的移动。所以尽量不要使用字符串（如：UUID）作为主键。

索引为什么采用B+树，而不用B-树，红黑树？

答案：提升查询速度，首先要减少磁盘IO次数，也就是要降低树的高度。

• 平衡二叉树、红黑树，都属于二叉树。时间复杂度为O(n)，当表的数据量上千万时，树的深度很深，mysql读取时消耗大量 IO。另外，InnoDB引擎采用页为单位读取，每个节点一页，但是二叉树每个节点储存一个关键词，导致空间浪费。

• B-树，非叶子节点存储数据，占用较多空间，导致每个节点的指针少很多，无形增加了树的深度。

• B+树数据都存储在叶子节点，非叶子节点只存储健值+指针，索引树更加扁平，三层深度可以支持千万级表存储。同时叶子节点之间通过链表关联，范围查找更快。

• 更多内容，参考 mysql 一棵 B+ 树能存多少条数据？

事务的特性有哪些？

答案：ACID。

• 原子性。一个事务中的操作要么全部成功，要么全部失败。

• 持久性。永久保存在数据库中。

• 一致性。总是从一个一致性的状态转换到另一个一致性的状态

• 隔离性。一个事务的修改在提交前，其他事务是感知不到的

如何实现分布式事务？

答案：

• 1、流水任务，最终一致性，前提是接口要支持幂等性

• 2、事务消息

• 3、二阶段提交

• 4、三阶段提交

• 5、TCC

• 6、Seata 框架

日常工作中，MySQL 如何做优化？

答案：

• 1、分页优化。比如电梯直达，limit 100000,10 先查找起始的主键id，再通过id>#{value}往后取10条

• 2、尽量使用覆盖索引，索引的叶节点中已经包含要查询的字段，减少回表查询

• 3、SQL优化（索引优化、小表驱动大表、虚拟列、适当增加冗余字段减少连表查询、联合索引、排序优化、慢日志 Explain 分析执行计划）。

• 4、设计优化（避免使用NULL、用简单数据类型如int、减少 text 类型、分库分表）。

• 5、硬件优化（使用SSD 减少 I/O 时间、足够大的网络带宽、尽量大的内存）

mysql 主从同步具体过程？

答案：

• master主库，有数据更新，将此次更新的事件类型写入到主库的binlog文件中

• 主库会创建log dump 线程通知slave有数据更新

• slave，向master节点的 log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的Relay log 中继日志中

• slave 再开启一个SQL 线程读取Relay log事件，并在本地执行redo操作。将发生在主库的事件在本地重新执行一遍，从而保证主从数据同步

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

Java高频面试专题合集解析：

当然在这还有更多整理总结的Java进阶学习笔记和面试题未展示，其中囊括了Dubbo、Redis、Netty、zookeeper、Spring cloud、分布式、高并发等架构资料和完整的Java架构学习进阶导图！

更多Java架构进阶资料展示

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
式、高并发等架构资料和完整的Java架构学习进阶导图！**

[外链图片转存中…(img-WcBB6GHB-1712652924197)]

更多Java架构进阶资料展示

[外链图片转存中…(img-kaZlOGOh-1712652924197)]

[外链图片转存中…(img-8OmevaHe-1712652924197)]

[外链图片转存中…(img-8jKIP5QV-1712652924198)]
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！