跟随黑马面Java-3，Mysql篇

第三篇：Mysql篇

“求其上，得其中；求其中，得其下，求其下，必败”

新版Java面试专题视频教程，java八股文面试全套真题+深度详解（含大厂高频面试真题）_哔哩哔哩_bilibili

开篇

image-20240516001647722

Mysql-优化

定位慢查询

  • 方案一：开源工具

  • 方案二：Mysql自带慢日志

    • 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息

      1
      2
      3
      4
      # 开启MVSQL慢日志查询开关
      slow_query_log=1
      # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
      long_query_time=2

    • 配置完毕之后，通过指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

      /var/lib/mysql/localhost-slow.log

    image-20240516003736032
分析慢查询

  • 可以采用expalin或者desc命令获取Mysql如何执行SELECT命令

    1
    2
    --  直接在select语句前加上explain/desc
    EXPLAIN SECLET 字段列表 FROM 表名 WHERE 条件;
    image-20240516004054980

    • possible_key当前sql可能会使用到的索引

    • key当前sql实际命中的索引

    • key_len索引占用的大小

    • type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all

      • NULL: 不查询任何表
      • system：查询系统中的表
      • const: 根据主键查询
      • eq_ref：主键索引查询或唯一索引查询
      • ref：索引查询
      • range：范围查询
      • index：索引树扫描
      • all：全盘扫描

    • Extra额外的优化建议

    我们可以通过key和key_len来查看是否可能会命中索引，也可以通过Extra来查看优化建议

    image-20240516004358834 image-20240516004818659
索引概念以及索引底层数据结构

  • 数据结构对比

    MySQL默认使用的索引底层数据结构是B+树。

    • 与二叉搜索树、红黑树对比：

      image-20240516005436340

      • 二叉搜索树因为插入顺序不当可能会退化成链表，这会导致搜索效率由理想情况的O(logn)退化成O(n)。

      • 红黑树大部分情况下的查找复杂度确实为O(logn)，但是在极端情况下可能出现遍历过深，导致效率下降。

    • B树

      B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

      以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key。

      image-20240516010343719

      此图中，蓝色部分表示数据，灰色部分表示指针。B树中每一分支最多存储4个key，这4个key从左向右是逐渐变大的。根节点中20左边的指针，指向的是小于20的节点；20与30之间的指针指向的是20~30的节点。

    • B+树

      B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

      ds-bplus-tree2

      B+树只有叶子节点真正存储数据，路径节点存储的都是指针。叶子节点之间是有双向指针的，如果我们想要范围查找，那么就不需要再返回根节点了，这加快了区间查询效率。

      B树与B+树对比

      ①：磁盘读写代价B+树更低；②：查询效率B+树更加稳定；③：B+树便于扫库和区间查询

image-20240516011601848
聚簇索引和非聚簇索引

也叫聚集索引和非聚集索引

  • 聚簇索引和非聚簇索引

    分类 含义 特点
    聚集索引(Clustered Index) 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有，而且只有一个
    二级索引(SecondaryIndex) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个

    • 聚集索引选取规则：

      • 如果存在主键，主键索引就是聚集索引。
      • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
      • 如果表没有主键，或没有合适的唯一索引。则InnoDB会自动生成一个rowid作为隐藏的聚集索引
      image-20240516090712593

    • 聚簇索引

      image-20240516090948753

      注意：叶子节点中存储的是一行的数据。中途节点存储的是主键。对于每个表，有且只能有一个聚簇索引。

    • 非聚簇索引/二级索引

      image-20240516091117401

      注意：上述表结构对name字段进行了唯一性标注，叶子节点中存储的是主键。我们单独给字段创建的索引，大部分都是二级索引。

  • 回表查询

    回表查询（Table Lookup or Row Lookup）是指在数据库查询过程中，通过索引找到记录的位置后，还需要再次访问表的实际数据行以获取所需的完整数据。

    1
    select *from user where name = 'Arm';

    这将先从二级索引中查找主键，然后从聚簇索引中查找行数据。这个过程就是回表查询。

    image-20240516091545951
image-20240516091745464
覆盖索引

覆盖索引（Covering Index）是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

  • 要创建一个覆盖索引,索引的字段必须包含查询语句中所有需要的字段。例如:

    1
    2
    CREATE INDEX idx_name ON table (col1, col2, col3);
    SELECT col1, col2, col3 FROM table WHERE col1 = ?;

    由于索引 idx_name 包含了查询所需的所有列（col1, col2, col3），这个索引就是一个覆盖索引。数据库可以直接从索引中获取数据，而不需要访问表的实际数据行。

image-20240516093908847

  • Mysql超大分页

    • 举例

      在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

      image-20240516094440293

      因为，当在进行分页查询时，如果执行limit 9000000,10，此时需要MySQL排序前9000010记录，而仅仅返回9000000－9000010的记录，其他记录丢弃。这样做查询排序的代价非常大。

    • 优化思路

      一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

      image-20240516094722254

      先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了。

      因为查询id的时候，走的覆盖索引，所以效率可以提升很多

索引创建的原则

  1. **针对于数据量较大，且查询比较频繁的表建立索引。**单表超过10万数据（增加用户体验）。

  2. 针对于常作为查询条件（where）、排序（orderby）、分组（groupby）操作的字段建立索引。

  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引。区分度越高，使用索引的效率越高。

  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

    image-20240516103835335

  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

  7. 如果索引列不能存诸NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

image-20240516104044330
什么情况下索引会失效

  • 背景

    比如tb_seller创建联合索引，字段顺序：name，status，address。

    image-20240516104615741

  • 什么情况下索引会失效

    1. 违反最左前缀法则

      • 匹配最左前缀法则，索引命中

        如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则的话，将走索引。（注意索引长度）

        image-20240516104854291

        需要按顺序匹配索引，上图中都是命中索引的。

      • 违反最左前缀法则，索引失效

        image-20240516105123438

      • 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

        image-20240516105242731

        注意看索引长度，和之前select * from tb_seller where name = '小米科技';一样。

    2. 范围查询右边的列，不能使用索引。

      image-20240516105506457

      上图中第一列能够命中索引，而下图中status > '1'，导致address = '北京市'失效。

    3. 在索引上进行运算操作 ，索引将失效

      image-20240516105722518

    4. 字符串不加单引号，造成索引失效（类型转换）

      image-20240516105848050

    5. 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

      image-20240516110241744
image-20240516110358974
谈一谈你对SQL优化的经验

  • 表的设计优化（参考阿里巴巴Java开发手册（终极版））

    • 比如设置合适的数值（tinyintintbigint），要根据实际情况选择
    • 比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

  • SQL语句的优化

    • SQL查询中，务必指定具体字段名，避免使用 SELECT *。：使用 SELECT * 会返回查询结果中所有字段，包括可能不需要的字段，这样会增加数据传输和处理的负担，尤其是在处理大型表时。另外，如果表结构发生变化，可能会导致代码出现错误或者不可预料的行为。指定具体字段名可以确保只返回需要的数据，提高查询效率和代码的可维护性。

    1. 避免编写SQL语句导致索引失效。：当 SQL 查询条件中使用了函数、表达式、或者类型转换时，会导致数据库无法有效使用索引。索引失效会使查询变慢，因为数据库需要遍历整个表来获取符合条件的数据，而不是直接利用索引进行快速定位。因此，在编写 SQL 语句时，应该避免这些情况，以确保索引的有效使用，提高查询性能。

    2. 使用 UNION ALL 替代 UNION：UNION 操作符会执行额外的步骤来确保结果集中不包含重复的行，这会增加查询的开销。相比之下，UNION ALL 不会去除重复行，因此效率更高。除非需要去重，否则应该优先选择使用 UNION ALL。

    3. 避免在 WHERE 子句中进行字段表达式操作：在 WHERE 子句中对字段进行表达式操作会导致数据库无法使用索引，因为它无法提前确定表达式的结果。这样会导致查询效率下降，尤其是在处理大量数据时。为了保证查询的高效率，应该尽量避免在 WHERE 子句中进行字段表达式操作，或者通过其他方式重写查询以利用索引。

    • 优先选择 INNER JOIN 而不是 LEFT JOIN 或 RIGHT JOIN：INNER JOIN 会返回两个表中共有的记录，而 LEFT JOIN 和 RIGHT JOIN 则会返回左表或右表的所有记录，以及与之匹配的右表或左表的记录。因此，INNER JOIN 比 LEFT JOIN 和 RIGHT JOIN 更高效，因为它只需要处理共有的记录。另外，在进行表连接时，应该将小表作为驱动表，这样可以减少连接操作的数据量，提高查询效率。

      内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不重新调顺序。

  • 主从复制，读写分离

    • 如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响可以采用读写分离的架构。

      读写分离解决的是，数据库的写入影响查询的效率。

      image-20240516122509935

      • 像redis一样，Master负责写操作，Slave负责读操作。

image-20240516122729960

Mysql-其他面试题

事务相关

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

  • 事务的特性-ACID

    • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
    • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
    • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
    • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

  • 最好结合案例

并发事务问题、隔离问题

  • 并发事务问题，解决方法：对事务进行隔离

    问题 描述
    脏读 一个事务读到另外一个事务还没有提交的数据。
    image-20240516135742698
    不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
    image-20240516135833996
    幻读 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。
    image-20240516135945690

  • 事务的隔离级别

    隔离级别 锁 脏读 不可重复读 幻读
    Read uncommitted 未提交读 无锁 ❌ ❌ ❌
    Read committed 读已提交 行锁，读不加锁 ✔️ ❌ ❌
    Repeatable Read(默认) 可重复读 行锁，读写都加锁 ✔️ ✔️ ❌
    Serializable 串行化 表锁 ✔️ ✔️ ✔️

    ❌：不可解决；✔️：可解决

    注意：事务隔离级别越高，数据越安全，但是性能越低。

undo log和redo log的区别

  • 概念

    • 缓冲池（bufferpool）：主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
    • 数据页（page）：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。页中存储的是行数据

  • redo log

    • 正常操作：当我们提交了写事务(update,delete)，将不会直接操作磁盘，首先会去操作内存，即缓冲池。如果其中没有需要操作的数据，将会把某一页的数据加载到缓冲池。等操作完成后，将会按照一定频率把数据同步到磁盘。

      image-20240516142259668

    • 脏页：我们在操作完pool后，还没来得及将也从pool中同步到磁盘中，此时服务器宕机，那么内存中的数据可能消失，那么我们修改的数据也丢失了。

      image-20240516142507904

    • 脏页解决：Mysql中引入了一种日志文件，叫做Redo Log（重做日志），

      • 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性

      • 该日志文件由两部分组成：重做日志缓冲（redolog buffer）以及重做日志文件（redo log file）前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘,发生错误时，进行数据恢复使用。

        image-20240516142910931

  • undo log

    回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC(多版本并发控制)。undo log和redolog记录物理日志不一样，它是逻辑日志。

    • 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然

    • 当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

    undo log可以实现事务的一致性和原子性。

image-20240516143212254
解释MVCC(事务隔离性是如何保证的)

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。

image-20240516143656495

MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

  • 记录中的隐藏字段

    image-20240516143948932
    隐藏字段 含义
    DB_TRX_ID 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
    DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
    DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

  • undo log

    回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

    当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

    而update、delete的时候，产生的undolog日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除

  • undo log版本链

    image-20240516144611383

    每修改一次行，对其进行一次记录；每记录一次，将记录添加到链表。

    不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

  • readView

    readView（读视图）是快照读SQL执行时，MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

    image-20240516145946850

    • 当前读

      读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：

      select …lock in share mode(共享锁)，select …for update、update、insert、delete(排他锁)都是一种当前读。

    • 快照读

      简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

      • ReadCommitted：每次select，都生成一个快照读

      • RepeatableRead：开启事务后第一个select语句才是快照读的地方。

  • MVCC-实现原理

    根据下表，如果RC隔离级别下，事务5的第一次查询创建了读视图，判断读视图中的核心字段。

    image-20240516150736533

    readview中包含了4个核心字段

    字段 含义 在此读视图中的值
    m_ids 当前活跃的事务id集合 {3,4,5}
    min_trx_id 最小的活跃事务ID 3
    max_trx_id 预分配事务id，当前最大事务ID+1（因为事务ID是自增的） 6
    creator_trx_id ReadView创建者的事务ID 5

    规则如下，不需要记，了解即可。

    image-20240516151214931

    • 不同的隔离级别，生成ReadView的时机不同：

      • RC隔离级别下的ReadView

        READ COMMITTED：在事务中每一次执行快照读时生成ReadView。

        image-20240516151338255

      • RR隔离级别下的ReadView

        REPEATABLEREAD：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

        image-20240516151633973
image-20240516152109165
MySQL主从同步原理

MySQL主从复制的核心在于二进制文件。

二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

主库专门写入，从库专门读取。当主库修改数据时，会将日志文件写入binlog。从库的IO线程监听主库的binlog，读取到差异后写入从库的Relay log（中继日志）。接着，从库的SQL线程读取中继日志的文件，执行其中的差异命令。执行完成后，主从库的数据保持同步。

image-20240516152913830
Mysql分库分表

我们之前提到了主从同步，其中主库负责写入，从库负责读取。假如现在有海量的数据进行存储，那这个主从同步架构无法解决此问题。因为那个架构只能解决访问压力，不能解决存储压力。随着项目业务数据逐渐增多，业务发展比较迅速（单表的数据量达到100万或者20G），优化也解决不了性能问题（主从读写分离、查询索引），或者遇到了IO瓶颈（磁盘、网络）/CPU瓶颈（聚合查询、连接数太多）

image-20240516153437953

  • 分库策略

    image-20240516155122602

    • 垂直拆分

      • 垂直分库：将不同业务模块的数据分布到不同的数据库中。例如，一个电子商务系统可以将用户信息、订单信息、商品信息分别存储在不同的数据库中。这样可以减少单个数据库的负担，提高整体系统的性能和可扩展性。

        image-20240516155413633
        特点
        按业务对数据分级管理、维护、监控、扩展
        在高并发下，提高磁盘IO和数据量连接数

      • 垂直分表：将同一个业务模块的数据按列拆分成多个表。例如，将商品表中的基本信息（如商品名、价格）和扩展信息（如描述信息）分成两张不同的表。这种方式可以优化某些查询，提高数据库的读写效率。

        image-20240516155802092
        拆分规则 特点
        把不常用的字段单独放在一张表
        把text，blob等大字段拆分出来放在附表中        		 冷热数据分离
        减少IO过渡争抢，两表互不影响

    • 水平拆分

      • 水平分库：将同一个表的数据按行拆分到多个数据库中。例如，将用户数据按用户ID范围分布到不同的数据库中，如用户ID在1-100万的放在数据库A，ID在100万-200万的放在数据库B；或者按照ID进行取模，取模后余0则在数据库A，余2则在数据库B。这样可以有效分散负载，避免单个数据库的性能瓶颈。

        image-20240516160351168
        路由规则 特点
        根据id进行取模，分配节点
        按id大小，也就是范围路由，节点1(1-100万),节点2(100万-200万）        		 解决了单库大数量，高并发的性能瓶颈问题
        提高了系统的稳定性和可用性

      • 水平分表：将同一个表的数据按行拆分成多个表。例如，将订单表按照订单日期拆分成多张表，每个月的数据存储在一张表中。这样可以减少单表的数据量，提高查询和写入的效率。

        image-20240516161542981
        特点
        优化单一表数据量过大而产生的性能问题
        避免IO争抢并减少锁表的几率

  • 新的问题和新的技术

    分库之后的问题：

    • 分布式事务一致性问题

    • 跨节点关联查询

    • 跨节点分页、排序函数

    • 主键避重

    针对上述问题，我们可以采用中间件来桥接Java应用程序和数据库群，主流的中间件有：

    • sharding-sphere

    • mycat

    image-20240516161911448
image-20240516162138709