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

2022年05月13日 阅读数:6
这篇文章主要向大家介绍2022年MySQL最新面试题第8篇 - MySQL数据库优化,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

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

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

2022年MySQL最新面试题目录html

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


0、概要

  • 一、为何要优化  
  • 二、数据库结构优化  
  • 三、MySQL数据库cpu飙升到500%的话他怎么处理? 
  • 四、大表怎么优化?某个表有近千万数据,CRUD比较慢,如何优化?
  • 五、垂直分表的适用场景和优缺点  
  • 六、水平分表的适用场景和优缺点  
  • 七、MySQL的复制原理以及流程  
  • 八、读写分离有哪些解决方案? 
  • 九、数据表损坏的修复方式有哪些? 

一、为何要优化数据库

出现几率: ★mysql

固然是让咱们的数据库更稳、更快、更持久了。面试

二、数据库结构优化

出现几率: ★★★sql

其实通常业务开发中, 这个关注的很少,估计是一些偏极客的团队关注的比较多些数据库

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

MySQL支持不一样类型的存储引擎和行格式,针对不一样类型,业务需求的表应该设置合适的存储引擎及索引方法。表设置建议以下:服务器

表列session

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

行格式mybatis

压缩类型的行格式,包括COMPACT, DYNAMIC, 和 COMPRESSED,对于特定操做,减小了存储空间占用,可是增长了CPU计算能力使用。若是主要的负载在缓存命中率及磁盘读写速度,那么这种格式将可以提高数据库反应速度。若是是极端状况负载受限于CPU性能,那么使用这种格式则会下降数据库性能。

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

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

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

出现几率: ★★★

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

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

出现几率: ★★★

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

若是一个表中某些列经常使用,而另一些列不经常使用,则能够采用垂直分割,另外垂直分割可使得数据行变小,一个数据页就能存放更多的数据,在查询时就会减小I/O次数。其缺点是须要管理冗余列,查询全部数据须要join操做

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

垂直切分的优势:

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

缺点:

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

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

出现几率: ★★★

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

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

水平切分的优势:

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

缺点:

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

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

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

读写分离是依赖于主从复制,而主从复制又是为读写分离服务的。由于主从复制要求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数据库出现表损坏, 特别是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的世界里漫步。