数据库优化

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 )的⾏格式。可以通过设置默认⾏格式（innodbdefaultrow_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 d esc总结:

CPU报警：很可能是 SQL ⾥⾯有较多的计算导致的
连接数超⾼：很可能是有慢查询，然后导致很多的查询在排队，排查问题的时候可以看到”事发现场“类似的 SQL 语句⼀⼤⽚，那么有可能是没有索引或者索引不好使，可以⽤：explain 分析⼀下 SQL 语句

4、⼤表怎么优化？某个表有近千万数据，CRUD⽐较慢，如何优化？

出现概率: ★★★

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

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

出现概率: ★★★

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

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

垂直切分的优点：

解决业务系统层⾯的耦合，业务清晰

与微服务的治理类似，也能对不同业务的数据进⾏分级管理、维护、监控、扩展等

⾼并发场景下，垂直切分⼀定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

部分表⽆法join，只能通过接⼝聚合⽅式解决，提升了开发的复杂度

分布式事务处理复杂

依然存在单表数据量过⼤的问题（需要⽔平切分）

6、⽔平分表的适⽤场景和优缺点

出现概率: ★★★
⽔平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同⼀个表按不同的条件分散到多个数据库或多个表中，每个表中只包含⼀部分数据，从⽽使得单个表的数据量变⼩，达到分布式的效果。

⽔平切分的优点：

不存在单库数据量过⼤、⾼并发的性能瓶颈，提升系统稳定性和负载能⼒

应⽤端改造较⼩，不需要拆分业务模块

缺点：

跨分⽚的事务⼀致性难以保证

跨库的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将会呈现SlaveSQLRunning=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: