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

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

重点是定位问题。

先

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

切换到常用的数据库
使用show full processlist;查看会话
观察是哪些sql消耗了资源，其中重点观察state指标
定位到具体sql

2、pidstat

定位到线程
在PERFORMANCE_SCHEMA.THREADS中记录了thread_os_id 找到线程执行的sql
根据操作系统id可以到processlist表找到对应的会话
在会话中即可定位到问题sql

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

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

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

6、pref top

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

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

135、什么是分库分表？

垂直分库

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

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

水平分表

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

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

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

能不分就不分
单机性能下降明显的时候
增加缓存（通常查询量比较大），细分业务
首先尝试主被集群，读写分离
尝试分库
尝试分表 -> 冷热数据分离

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

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

伪装成mysql服务器，代理用户请求转发到真实服务器
基于本地aop实现，拦截sql，改写，路由和结果归集处理。

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

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

经典的问题

执行效率明显降低
表结构很难再次调整
引发分布式id问题
产生跨库join
代理类中间件网络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. 合并请求:多个请求需要的数据尽量一条sql拿出来
2. 会话保存：和用户会话相关的数据尽量一次取出重复使用
3. 避免无效刷新
多级缓存不要触及到数据库
1. 应用层热点数据高速查询缓存（低一致性缓存）
2. 高频查询大数据量镜像缓存（双写高一致性缓存）
3. 入口层缓存（几乎不变的系统常量）
使用合适的字段类型，比如varchar换成char
一定要高效使用索引。
1. 使用explain 深入观察索引使用情况
2. 检查select 字段最好满足索引覆盖
3. 复合索引注意观察key_len索引使用情况
4. 有分组，排序，注意file sort，合理配置相应的buffer大小
检查查询是否可以分段查询，避免一次拿出过多无效数据
多表关联查询是否可以设置冗余字段，是否可以简化多表查询或分批查询
分而治之：把服务拆分成更小力度的微服务
冷热数据分库存储
读写分离，主被集群然后再考虑分库分表
等

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