大厂整理出来的mysql优化面试题目

1.数据库结构优化

其实一般业务开发中, 这个关注的不多，估计是一些偏极客的团队关注的比较多些

使表占用尽量少的磁盘空间。减少磁盘I/O次数及读取数据量是提升性能的基础原则。表越小，数据读写处理时则需要更少的内存，同时，小表的索引占用也相对小，索引处理也更加快速。

MySQL支持不同类型的存储引擎和行格式，针对不同类型，业务需求的表应该设置合适的存储引擎及索引方法。表设置建议如下：

表列

• 为列选择最合适(通常最小)的数据类型。MySQL 具有许多不同列类型以最大化的减少磁盘和内存占用。例如，使用足够小的整型来表示小范围的小整型数据。MEDIUMINT 通常是一个很好的选择，它只占用INT 25%，甚至更小的空间。
• 如果可能，则将列声明为NOT NULL。声明为NOT NULL，使得优化器能够更好的使用索引，并避免了判断NULL的处理，这使得SQL 操作执行的更加快速。同时也为每列节省了1 bit的空间。如果确实需要使用NULL 值，那么也应该避免所有列都允许NULL。
• InnoDB 表默认使用动态类型(DYNAMIC )的行格式。可以通过设置默认行格式(innodb_default_row_format)，或者在表定义(CREATE TABLE 或 ALTER TABLE )中声明使用的行格式。

行格式

压缩类型的行格式，包括COMPACT, DYNAMIC, 和 COMPRESSED，对于特定操作，减少了存储空间占用，但是增加了CPU计算能力使用。如果主要的负载在缓存命中率及磁盘读写速度，那么这种格式将能够提升数据库反应速度。如果是极端情况负载受限于CPU性能，那么使用这种格式则会降低数据库性能。

压缩行格式也会对使用utf8mb3 或者 utf8mb4格式的变长CHAR 类型列存储进行优化处理。对于使用ROW_FORMAT=REDUNDANT, CHAR(N) 定义的表，每个列值最多占用 N × 个字节长度。许多语言可以使用但字节的utf8格式表示，所以规定那个长度的定义通常会造成空间浪费。压缩行格式定义下，InnoDB 会每一个列值分配一个N 到 N× 个字节的空间。

2.MySQL数据库cpu飙升到500%的话, 应该怎么处理?

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

show full processlist 可以看到所有链接的情况，但是大多链接的 state 其实是 Sleep 的，这种的其实是空闲状态，没有太多查看价值

我们要观察的是有问题的，所以可以进行过滤：

-- 查询非 Sleep 状态的链接，按消耗时间倒序展示，自己加条件过滤

select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc 

总结:

CPU报警：很可能是 SQL 里面有较多的计算导致的

连接数超高：很可能是有慢查询，然后导致很多的查询在排队，排查问题的时候可以看到”事发现场“类似的 SQL 语句一大片，那么有可能是没有索引或者索引不好使，可以用：explain 分析一下 SQL 语句

3.MySQL的复制原理以及流程

MySQL主从复制工作原理

• 在主库上把数据更高记录到二进制日志
• 从库将主库的日志复制到自己的中继日志
• 从库读取中继日志的事件，将其重放到从库数据中
• 基本原理流程，3个线程以及之间的关联
• 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中;
• 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中;
• 从：sql执行线程——执行relay log中的语句;

复制过程

• Binary log：主数据库的二进制日志
• Relay log：从服务器的中继日志
• 第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
• 第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。
• 第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

4、读写分离有哪些解决方案?

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读(如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave)。

方案一

• 使用mysql-proxy代理
• 优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，mysql官方不建议实际生产中使用
• 缺点：降低性能， 不支持事务

方案二

• 使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。
• 如果采用了mybatis， 可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select

都访问salve库，这样对于dao层都是透明。 plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。不过这样依然有一个问题，也就是不支持事务， 所以我们还需要重写一下DataSourceTransactionManager， 将read-only的事务扔进读库，其余的有读有写的扔进写库。

方案三

• 使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务.
• 缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

以上就是“大厂整理出来的mysql优化面试题目”，你能回答上来吗?如果想要了解更多的相关内容，可以关注极悦Java官网。