java面试八股 mysql篇

1.如何定位慢索引

方案一：开源工具

调试工具：Arthas

运维工具：Prometheus，Skywalking

 

方案二：MySQL自带慢日志

慢查询日志记录了所有执行时间超过指定参数的所有SQL语句的日志

需要 手动 在MySQL的配置文件中进行修改

 

2.如何分析、优化sql语句

可以采用EXPLAIN或者DESC命令获取MySQL如何执行select语句的信息

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

system：查询系统中的表

const：根据主键查询

eq_ref：主键索引查询或唯一索引查询

ref：索引查询

range：范围查询

index：索引树扫描

all：全盘扫描 

 

通过key和key_len检查是否命中了索引（索引本身是否存在是否有失效的情况）

通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描

通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

 

3.索引的概念

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

通过提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）

通过索引列队数据进行排序，降低数据排序的成本，降低了CPU的消耗

 

底层数据结构

采用B+树的数据结构存储索引

阶数    阶数-1的叶子结点

阶数更多，路径更短

磁盘读写代价更低，非叶子节点只存储指针，叶子节点存储数据

便于扫库和区间查询，叶子节点是一个双向链表

 

 

4.聚簇索引  非聚簇索引 回表查询

 

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

 

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

 

聚簇索引（聚集索引）：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个

非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个

通过二级索引找到对应的主键值，到聚簇索引中查找整行数据，这个过程就是回表

 

5.覆盖索引，超大分页优化

覆盖索引：查询使用了索引，并返回需要返回的列，在该索引中已经全部能够找到

如果返回的列没有创建索引，有可能触发回表查询，导致速度慢

 

mysql的超大分页处理，使用limit进行分页查询，越往后，分页查询效率越低

通过覆盖索引+子查询

 

6.索引创建的原则

 

1.陈述自己在实际的工作中怎么用的

2.主键索引

3.唯一索引

4.根据业务创建的索引（复合索引）

 

1.针对数据量较大，查询比较频繁的表建立索引

2.针对于常做查询条件，排序，分组操作的字段建立索引

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

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

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

6.要控制索引的数量，索引越多，维护索引结构的代价就越大，影响增删改的效率

7.如果索引列不能存储NULL值，请在创建表是使用not null约束他

 

7.索引失效

1.违反最左前缀法则

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

3.不要在索引列进行运算操作

4.字符串不加单引号（会自动进行类型转换）

5.以%开头的like模糊查询，头部失效，尾部不失效

 

8.对sql优化的经验

1.表的设计优化

设置合适的数值

设置合适的字符串类型 char varchar

 

2.索引优化

 

3.sql语句优化

务必指明字段名称

避免索引失效写法

union尽量避免

避免在where字句中对字段进行表达式操作

join 避免使用left join reght join  （如果使用 一定要以小表为驱动）

 

4.主从复制，读写分离

  • 主库（Master）处理写操作（INSERT/UPDATE/DELETE）；
  • 从库（Slave）处理读操作（SELECT）；
  • 通过主从复制（Replication）同步数据；
  • 注意：存在主从延迟，强一致性读仍需走主库。

 

5.分库分表

 

9.事务的特性

ACID是什么？可以详细说一下吗？

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

 

10.并发事务的问题

脏读（Dirty Read）
一个事务读到另外一个事务还没有提交的数据。

不可重复读（Non-Repeatable Read）
一个事务多次查询，结果不一致，因为其他事务在中间修改并提交了数据。

幻读（Phantom Read）
一个事务按照某个条件多次查询，结果数量不一致，因为其他事务在中间插入或删除了符合该条件的数据。

隔离级别

READ UNCOMMITTED 未提交读
READ COMMITTED 读已提交
REPEATABLE READ 可重复读 
SERIALIZABLE 串行化

 

11.undo log 和 redo log的区别

缓冲池（buffer pool）：主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

数据页（page）：InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认认为 16KB。页中存储的是行数据。

 

Undo Log （回滚日志）是 InnoDB 存储引擎实现事务 原子性 和 一致性 的关键机制。它的主要作用是记录事务执行过程中对数据所做的“修改前的旧值”。

 

Redo Log （重做日志）是 InnoDB 实现事务 持久性 的核心机制。它的主要作用是记录事务对数据页所做的“物理修改内容”，确保即使数据库发生崩溃，已提交的事务也不会丢失。

  • redo log：记录的是数据页的物理变化，服务宕机可用来同步数据
  • undo log：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
  • redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

 

12.MVCC

怎么保证事务的隔离性

MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

● 隐藏字段： ① trx_id(事务id)，记录每一次操作的事务id，是自增的 ② roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址

● undo log： ① 回滚日志，存储老版本数据 ② 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

● readView解决的是一个事务查询选择版本的问题

根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据 不同的隔离级别快照读是不一样的，最终的访问的结果不一样 RC：每一次执行快照读时生成ReadView RR：仅在事务中第一次执行快照读时生成ReadView，后续复用

 

13.主从同步原理

MySQL主从复制的核心就是二进制日志，二进制日志记录了所有的DDL语句和DML语句。
具体的主从同步过程大概的流程是这样的：

    1. Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。
    2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log。
    3. slave重做中继日志中的事件，将改变反映它自己的数据。

 

14.分库分表

在之前参与的一个电商平台订单系统项目中，我们面临一个典型的高并发、大数据量业务场景：

  • 业务背景：平台每日产生数百万笔订单，订单数据增长迅速，单表数据量在半年内达到1200万条记录，表大小超过25GB，且高峰期每秒有上千次读写请求。
  • 核心痛点：
    • 单表数据量过大导致查询变慢（尤其是按用户ID或订单状态查询）；
    • 写入压力大，影响事务提交性能；
    • 索引膨胀严重，全表扫描风险增加；
    • 数据库连接池接近瓶颈，无法支撑持续增长的并发访问。

为了解决这些问题，我们对数据库进行了分库分表设计，并结合冷热数据分离策略，显著提升了系统的稳定性与可扩展性。

1. 水平分库（Sharding by Database）

  • 策略：按照 user_id 进行哈希取模，将用户订单数据分散到多个物理数据库中（例如 4 个分库）。
  • 目的：
    • 解决海量数据存储问题；
    • 分摊写入压力和网络连接数；
    • 提升整体吞吐能力（每个库独立承载部分流量）。
  • 效果：单库数据量降至约 300 万条，写入延迟下降 60%，连接池压力明显缓解。

2. 水平分表（Sharding by Table）

  • 策略：在每个分库内，进一步对订单表按 order_id 的前缀或时间范围进行分表（如按月分表：orders_202401, orders_202402）。
  • 目的：
    • 避免单表过大带来的索引失效、锁竞争等问题；
    • 支持更高效的分区查询（如查询某个月的订单）。
  • 效果：单表数据控制在 100 万以内，查询响应时间从平均 800ms 降到 150ms。

3. 垂直分库（Vertical Sharding）

  • 策略：将不同业务模块的数据拆分到不同的数据库中。
    • 如：订单主信息 → order_db
    • 订单物流信息 → logistics_db
    • 用户行为日志 → behavior_db
  • 目的：
    • 降低单库复杂度；
    • 提高磁盘 I/O 和网络连接效率；
    • 实现服务解耦，便于独立扩容和维护。
  • 效果：各业务模块互不影响，运维更加灵活。

4. 垂直分表（Vertical Partitioning + 冷热数据分离）

  • 策略：
    • 将频繁访问的字段（如订单状态、金额、用户ID）放在主表（hot table）；
    • 将不常访问的字段（如备注、附件路径、操作日志等）拆分到副表（cold table）；
    • 同时，将超过 6 个月的“冷数据”归档至历史表或 Hive/ClickHouse 中。
  • 目的：
    • 减少主表宽度，提升查询效率；
    • 实现冷热数据隔离，避免冷数据占用缓存资源；
    • 多表之间互不影响，降低锁冲突概率。
  • 效果：热点查询性能提升 70%，内存使用优化明显。

 

 

posted @ 2025-11-11 14:06  财神给你送元宝  阅读(3)  评论(0)    收藏  举报