你们好，我是漫步coding, 最近在整理2022年MySQL最新面试题, 你们也能够经过我下面的博客地址在线阅读, 今天讲讲第8篇 - MySQL数据库优化。本文首发于公众号: 漫步codingphp
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操做
垂直切分的优势：
- 解决业务系统层面的耦合，业务清晰
- 与微服务的治理相似，也能对不一样业务的数据进行分级管理、维护、监控、扩展等
- 高并发场景下，垂直切分必定程度的提高IO、数据库链接数、单机硬件资源的瓶颈
缺点：
- 部分表没法join，只能经过接口聚合方式解决，提高了开发的复杂度
- 分布式事务处理复杂
- 依然存在单表数据量过大的问题（须要水平切分）
六、水平分表的适用场景和优缺点
出现几率: ★★★
水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不一样的条件分散到多个数据库或多个表中，每一个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。如图所示：
水平切分的优势：
- 不存在单库数据量过大、高并发的性能瓶颈，提高系统稳定性和负载能力
- 应用端改造较小，不须要拆分业务模块
缺点：
- 跨分片的事务一致性难以保证
- 跨库的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的世界里漫步。