当前位置：首页 > news >正文

八股学习 Mysql

news 来源：原创 2025/6/8 21:49:21

八股学习 Mysql

常见面试问题
- 优化
- 其他
定位慢查询
- 方案一：开源工具
- 方案二：MySQL自带慢日志
SQL执行计划
- 示例场景
- 名词解释
索引
- 概念
- 底层数据结构
- 聚簇索引、二级索引（非聚簇索引）
- 覆盖索引
- 覆盖索引应用场景
- 创建原则
- 索引失效
SQL优化
- 表的设计优化
- 索引优化
- SQL语句优化
- 主从复制、读写分离
- 分库分表
事务
- 事务特性ACID
- 并发事务问题、隔离级别
- undo log 与 redo log

常见面试问题

优化

定位慢查询；
SQL执行计划；
索引：存储引擎、索引底层数据结构、聚簇和非聚簇索引、索引创建原则、索引失效场景；
SQL优化经验；

其他

事务相关：事务特性、隔离级别、NVCC；
主从同步原理；
分库分表；

定位慢查询

慢查询通常出现于聚合查询、多表查询、表数据量过大查询、深度分页查询等情况下，表现为：页面加载过慢，接口压测响应时间过长;

方案一：开源工具

调试工具：Arthas
运维工具：Prometheus、Skywalking

方案二：MySQL自带慢日志

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

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

SQL执行计划

SQL执行很慢，如何分析呢？

可以采用MySQL自带的分析工具EXPLAIN；
通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描；
通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复；

示例场景

直接在select语句前加上关键字explain/desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

在这里插入图片描述

名词解释

possible_key: 当前sql可能会使用到的索引；
key: 当前sql实际命中的索引；
key_len：索引占用大小；
Extra: 额外的优化建议；
- Using where;Using Index: 查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据；
- Using index condition: 查找使用了索引，但是需要回表查询数据；
type: 这条sql的连接的类型，性能由好到差为NULL、system、cosnt、eq_ref、ref、range、index、all
- system: 查询系统中的表；
- const: 根据主键查询
- eq_ref: 主键索引查询或唯一索引查询
- ref: 索引查询
- range: 范围查询
- index: 索引树扫描
- all: 全盘扫描

索引

概念

帮助MySQL高效获取数据的数据结构（有序）；
提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

底层数据结构

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引：

阶数更多，路径更短；
磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据；
B+树便于扫库和区间查询，叶子节点是一个双向链表；

聚簇索引、二级索引（非聚簇索引）

以下面的表结构为例进行演示：
在这里插入图片描述

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（必须存在且只能存在一个）；
在这里插入图片描述
聚簇索引选取规则：

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

二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（可以存在多个）；
在这里插入图片描述
回表查询：通过二级索引找到对应的主键值，到聚簇索引中查找整行数据；
例如：select * from user where name = 'Arm

在这里插入图片描述

覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列在该索引中已经全部可以找到；
以下列数据表格为例进行演示：
在这里插入图片描述
其中，我们分别执行三条语句：
select * from tb user where id = 1(覆盖索引)
select id,name from tb user where name = 'Arm’（覆盖索引）
select id,name,gender from tb user where name = 'Arm'（覆盖索引）

这是因为前两条语句通过聚簇（聚集）索引或者二次（辅助）索引，不需要产生回表查询便可以得到所有数据，而gender无法通过辅助索引一次性查到，需要回表查询使用主键id才能查到；
在这里插入图片描述

覆盖索引应用场景

问题：在数据量比较大时，limit分页查询需要对数据进行排序，效率低；
解决方案：覆盖索引+子查询，先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了，因为查询id的时候，走的覆盖索引，所以效率可以提升很多；

创建原则

（重要）针对于数据量较大，且查询比较频繁的表建立索引；（单表超过10万数据)
（重要）针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引；
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高；
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引；
（重要）尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率；
（重要）要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率；
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它；当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询；

索引失效

违反最左前缀法则；
范围查询右边的列，不能使用索引；
不要在索引列上进行运算操作，索引将失效；
字符串不加单引号，造成索引失效(类型转换)；
以%开头的Like模糊查询(不一定，是可能失效)；

SQL优化

表的设计优化

设置合适的数值（tinyint int bigint）,根据实际情况选择；
设置合适的字符串类型（char varchar）char定长效率高，varchar可变长度，效率稍低；

索引优化

参考优化创建原则和索引失效

SQL语句优化

SELECT语句务必指明字段名称（尽可能避免直接使用select *,减少回表查询）
SQL语句要避免索引失效的写法
尽量用union all代替union, union会多一次过滤，效率低;
避免在where子句中对字段进行表达式操作
Join优化能用inner join 就不用left join right join，如必须使用，一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。

主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响，可以采用读写分离的架构。
读写分离解决的是，数据库的写入，影响了查询的效率。
在这里插入图片描述

分库分表

事务

事务特性ACID

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

并发事务问题、隔离级别

并发事务存在以下几种问题：

脏读:一个事务读到另外一个事务还没有提交的数据；
不可重复读:一个事务先后读取同一条记录，但两次读取的数据不同；
幻读:一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

解决方法是将事务进行隔离，不同的隔离级别可以不同程度解决相关问题：（× √ 表示存在/不存在相关问题），数据隔离级别越高，数据越安全，但是性能越低；
在这里插入图片描述

undo log 与 redo log

缓冲池(buffer pool):主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改査操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度;
数据页(page):是InnoD8 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB;页中存储的是行数据;
redo log:重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性，该日志文件由两部分组成:重做日志缓冲(redolog bufer)以及重做日志文件(redolog file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用;
undo log:回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚和 MVCC(多版本并发控制)。undo log和redo log记录物理日志不一样，它是逻辑日志。可以实现事务的一致性和原子性；可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当执行roll back时，就可以从undo log中的逻辑记录读取到相应的内容。当update一条记录时，它记录一条对应相反的update记:并进行回滚。

八股学习 Mysql

常见面试问题

优化

其他

定位慢查询

方案一：开源工具

方案二：MySQL自带慢日志

SQL执行计划

示例场景

名词解释

索引

概念

底层数据结构

聚簇索引、二级索引（非聚簇索引）

覆盖索引

覆盖索引应用场景

创建原则

索引失效

SQL优化

表的设计优化

索引优化

SQL语句优化

主从复制、读写分离

分库分表

事务

事务特性ACID

并发事务问题、隔离级别

undo log 与 redo log

相关文章：