大家好,我是漫步coding, 最近在整理2022年MySQL最新面试题, 大家也可以通过我​​下面的博客地址在线阅读​​, 今天讲讲第8篇 - MySQL数据库优化。本文首发于公众号: 漫步coding

2022年MySQL最新面试题第8篇 - MySQL数据库优化_数据

2022年MySQL最新面试题目录

  • MySQL数据库基础知识​
  • MySQL索引​
  • MySQL存储引擎​
  • MySQL事务​
  • MySQL数据库读写锁​
  • MySQL视图​
  • MySQL触发器​
  • MySQL数据库优化
  • MySQL部署和运维


0、概要

  • 1、为什么要优化  
  • 2、数据库结构优化  
  • 3、MySQL数据库cpu飙升到500%的话他怎么处理? 
  • 4、大表怎么优化?某个表有近千万数据,CRUD比较慢,如何优化?
  • 5、垂直分表的适用场景和优缺点  
  • 6、水平分表的适用场景和优缺点  
  • 7、MySQL的复制原理以及流程  
  • 8、读写分离有哪些解决方案? 
  • 9、数据表损坏的修复方式有哪些? 

1、为什么要优化数据库

出现概率: ★

当然是让我们的数据库更稳、更快、更持久了。

2、数据库结构优化

出现概率: ★★★

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

使表占用尽量少的磁盘空间。减少磁盘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× 个字节的空间。

3、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 语句

4、大表怎么优化?某个表有近千万数据,CRUD比较慢,如何优化?

出现概率: ★★★

千万级其实数量不大, CRUD比较慢, 可能要考虑磁盘、索引等问题.

5、垂直分表的适用场景和优缺点

出现概率: ★★★

把主码和一些列放到一个表,然后把主码和另外的列放到另一个表中。

如果一个表中某些列常用,而另外一些列不常用,则可以采用垂直分割,另外垂直分割可以使得数据行变小,一个数据页就能存放更多的数据,在查询时就会减少I/O次数。其缺点是需要管理冗余列,查询所有数据需要join操作

2022年MySQL最新面试题第8篇 - MySQL数据库优化_mysql_02

垂直切分的优点:

  • 解决业务系统层面的耦合,业务清晰
  • 与微服务的治理类似,也能对不同业务的数据进行分级管理、维护、监控、扩展等
  • 高并发场景下,垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点:

  • 部分表无法join,只能通过接口聚合方式解决,提升了开发的复杂度
  • 分布式事务处理复杂
  • 依然存在单表数据量过大的问题(需要水平切分)

6、水平分表的适用场景和优缺点

出现概率: ★★★

水平切分分为库内分表和分库分表,是根据表内数据内在的逻辑关系,将同一个表按不同的条件分散到多个数据库或多个表中,每个表中只包含一部分数据,从而使得单个表的数据量变小,达到分布式的效果。如图所示:

2022年MySQL最新面试题第8篇 - MySQL数据库优化_sql_03

水平切分的优点:

  • 不存在单库数据量过大、高并发的性能瓶颈,提升系统稳定性和负载能力
  • 应用端改造较小,不需要拆分业务模块

缺点:

  • 跨分片的事务一致性难以保证
  • 跨库的join关联查询性能较差
  • 数据多次扩展难度和维护量极大

7、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事件,从而与主数据库中的数据保持一致。

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

读写分离是依赖于主从复制,而主从复制又是为读写分离服务的。因为主从复制要求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不会进行拦截,需进行特殊处理。

9、数据表损坏的修复方式有哪些?

MySQL数据库出现表损坏, 特别是MyISAM表数据很大的时候。有三种方法,一种方法使用MySQL的repair table的sql语句,另一种方法是使用MySQL提供的myisamchk,,最后一种是mysqlcheck命令行工具。

1)、repair table(建议方法,对MyISAM引擎表有用)

check table tabTest;

如果出现的结果说Status是OK,则不用修复,如果有Error,可以用:

repair table tabTest;

进行修复,修复之后可以在用check table命令来进行检查。在新版本的phpMyAdmin里面也可以使用check/repair的功能。

2)、myisamchk(该工具必须运行在服务终止条件下,对MyISAM引擎表有用)。

myisamchk tablename.MYI

进行检测。

myisamchk -of tablename.MYI

网上说的其它方法:

那么修复test表的方法为

myisamchk -r -q /var/lib/mysql/db/test.MYI

如果修复全部表,用这个命令

myisamchk -r -q /var/lib/mysql/db/*.MYI

3)、运行mysqlcheck命令行工具(该工具可以在服务运行状态下执行)

检查一个库中的所有表:

$ mysqlcheck -c users -uroot -p

Enter password:

users.account OK
users.alarm OK\

也欢迎关注我的公众号: ​​漫步coding​​​, 回复: ​​mysql​​免费获取最新Mysql面试题汇总(含答案)。一起交流, 在coding的世界里漫步。