2022最新版MySQL大厂面试题（六）

133、MySQL数据库cpu飙升的话你会如何分析

重点是定位问题。

先

1、使用top观察mysqld的cpu利用率

1. 切换到常用的数据库

2. 使用show full processlist;查看会话

3. 观察是哪些sql消耗了资源，其中重点观察state指标

4. 定位到具体sql

2、pidstat

1. 定位到线程

2. 在PERFORMANCE_SCHEMA.THREADS中记录了thread_os_id 找到线程执行的sql

3. 根据操作系统id可以到processlist表找到对应的会话

4. 在会话中即可定位到问题sql

3、使用show profile观察sql各个阶段耗时

4、服务器上是否运行了其他程序

5、检查一下是否有慢查询

6、pref top

使用pref 工具分析哪些函数引发的cpu过高来追踪定位

134、有没有进行过分库分表？

135、什么是分库分表？

垂直分库

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同 的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：

系统被切分成了，用户，订单交易，支付几个模块。

水平分表

把一张表里的内容按照不同的规则 写到不同的库里

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分 到一个数据库，而另外的某些行又切分到其他的数据库中，如图：

136、什么时候进行分库分表？有没有配合es使用经验？

1. 能不分就不分

2. 单机性能下降明显的时候

3. 增加缓存（通常查询量比较大），细分业务

4. 首先尝试主被集群，读写分离

5. 尝试分库

6. 尝试分表 -> 冷热数据分离

大数据量下可以配合es完成高效查询

137、说一下实现分库分表工具的实现思路

1. 伪装成mysql服务器，代理用户请求转发到真实服务器

2. 基于本地aop实现，拦截sql，改写，路由和结果归集处理。

138、用过哪些分库分表工具？

139、分库分表后可能会有哪些问题？

经典的问题

1. 执行效率明显降低

2. 表结构很难再次调整

3. 引发分布式id问题

4. 产生跨库join

5. 代理类中间件网络io成为瓶颈

140、说一下读写分离常见方案？

141、为什么要使用视图？ 什么是视图？

视图定义： 1、视图是一个虚表，是从一个或几个基本表（或视图）导出的表。 2、只存放视图的定义，不存放视图对应的数据。 3、基表中的数据发生变化，从视图中查询出的数据也随之改变。 视图的作用： 1、视图能够简化用户的操作 2、视图使用户能以多种角度看待同一数据 3、视图对重构数据库提供了一定程度的逻辑独立性 4、视图能够对机密数据提供安全保护 5、适当的利用视图可以更清晰的表达查询

142、什么是存储过程？有没有使用过？

项目中禁止使用存储过程，存储过程难以调试和扩展，更没有移植性

143、有没有使用过外键？有什么需要注意的地方？

不得使用外键与级联，一切外键概念必须在应用层解决。

说明：以学生和成绩的关系为例，学生表中的 student_id是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为 级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻 塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

144、用过processlist吗？

关键的就是state列，mysql列出的状态主要有以下几种：

• Checking table 正在检查数据表（这是自动的）。

• Closing tables 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

• Connect Out 复制从服务器正在连接主服务器。

• Copying to tmp table on disk 由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。

• Creating tmp table 正在创建临时表以存放部分查询结果。

• deleting from main table 服务器正在执行多表删除中的第一部分，刚删除第一个表。

• deleting from reference tables 服务器正在执行多表删除中的第二部分，正在删除其他表的记录。

• Flushing tables 正在执行FLUSH TABLES，等待其他线程关闭数据表。

• Killed 发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。

• Locked 被其他查询锁住了。

• Sending data 正在处理Select查询的记录，同时正在把结果发送给客户端。Sending data”状态的含义，原来这个状态的名称很具有误导性，所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”。

• Sorting for group 正在为GROUP BY做排序。

• Sorting for order 正在为ORDER BY做排序。

• Opening tables 这个过程应该会很快，除非受到其他因素的干扰。例如，在执Alter TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。

• Removing duplicates 正在执行一个Select DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。

• Reopen table 获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。

• Repair by sorting 修复指令正在排序以创建索引。

• Repair with keycache 修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。

• Searching rows for update 正在讲符合条件的记录找出来以备更新。它必须在Update要修改相关的记录之前就完成了。

• Sleeping 正在等待客户端发送新请求.

• System lock 正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。

• Upgrading lock Insert DELAYED正在尝试取得一个锁表以插入新记录。=

• Updating 正在搜索匹配的记录，并且修改它们。

• User Lock 正在等待GET_LOCK()。

• Waiting for tables 该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, Alter TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

• waiting for handler insert Insert DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。

145、某个表有数千万数据，查询比较慢，如何优化？说一下思路

1. 前端优化 减少查询

   1. 合并请求:多个请求需要的数据尽量一条sql拿出来

   2. 会话保存：和用户会话相关的数据尽量一次取出重复使用

   3. 避免无效刷新

2. 多级缓存 不要触及到数据库

   1. 应用层热点数据高速查询缓存（低一致性缓存）

   2. 高频查询大数据量镜像缓存（双写高一致性缓存）

   3. 入口层缓存（几乎不变的系统常量）

3. 使用合适的字段类型，比如varchar换成char

4. 一定要高效使用索引。

   1. 使用explain 深入观察索引使用情况

   2. 检查select 字段最好满足索引覆盖

   3. 复合索引注意观察key_len索引使用情况

   4. 有分组，排序，注意file sort，合理配置相应的buffer大小

5. 检查查询是否可以分段查询，避免一次拿出过多无效数据

6. 多表关联查询是否可以设置冗余字段，是否可以简化多表查询或分批查询

7. 分而治之：把服务拆分成更小力度的微服务

8. 冷热数据分库存储

9. 读写分离，主被集群 然后再考虑分库分表

10. 等

146、count(列名)和 count(*)有什么区别？

count()是 SQL92 定义的 标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。 说明：count()会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

147、如果有超大分页改怎么处理？

• select name from user limit 10000,10;在 使用的时候并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行

• 通过索引优化的方案：

  • 如果主键自增可以 select name from user where id > 10000 limit 10;

  • 延迟关联

  • 需要order by时

    • 一定注意增加筛选条件，避免全表排序

      • where -》 order by -》 limit

    • 减少select字段

    • 优化相关参数避免filesort

• 一般大分页情况比较少（很少有人跳转到几百万页去查看数据），实际互联网业务中多数还是按顺序翻页，可以使用缓存提升前几页的查询效率，实际上大多数知名互联网项目也都是这么做的

在阿里巴巴《Java开发手册》中的建议：

【推荐】利用延迟关联或者子查询优化超多分页场景。 说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过 特定阈值的页数进行 SQL 改写。 正例：先快速定位需要获取的 id 段，然后再关联： SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

148、mysql服务器毫无规律的异常重启如何排查问题？

首先是查看mysql和系统日志来定位错误

最常见的是关闭swap分区后OOM问题：

mysql 分为应用进程和守护进程

当应用进程内存占用过高的时候操作系统可能会kill掉进程，此时守护进程又帮我们重启了应用进程，运行一段时间后又出现OOM如此反复

可以排查以下几个关键点

• 运行时内存占用率

• mysql buffer相关参数

• mysql 网络连接相关参数

异常关机或kill -9 mysql 后导致表文件损坏

• 直接使用备份

• 配置 innodb_force_recovery 跳过启动恢复过程

149、mysql 线上修改表结构有哪些风险?

针对ddl命令，有以下几种方式

• copy table 锁原表，创建临时表并拷贝数据

• inplace 针对索引修改删除的优化，不需要拷贝所有数据

• Online DDL 细分DDL命令来决定是否锁表

• 可能会锁表，导致无法读写

• ORM中的映射失效

• 索引失效

建议：建个新表，导入数据后重命名

150、什么是mysql多实例部署？

指的是在一台主机上部署多个实例

主要目的是压榨服务器性能

缺点是互相影响