银行面试之MySQL数据库（大表优化）

参考：https://segmentfault.com/a/1190000006158186

大表优化

当MySQL单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化

---

单表优化

字段

• 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
• VARCHAR的长度只分配真正需要的空间
• 使用枚举或整数代替字符串类型
• 尽量使用TIMESTAMP而非DATETIME
• 单表不要有太多字段，建议在20以内
• 避免使用NULL字段，很难查询优化且占用额外索引空间
• 用整型来存IP

索引

• 考虑在WHERE和ORDER BY命令上涉及的列建立索引
• 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
• 字符字段只建前缀索引
• 不用外键和UNIQUE，由程序保证约束

---

主从复制、读写分离

主从复制使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

目的：

• 在主服务器上执行写入和更新、在从服务器上向外提供读功能，实现读写分离。
• 可以动态地调整从服务器地数量从而调整整个数据库的性能。
• 数据备份，保证数据安全。

原理：

• binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
• I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
• SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。
  
  主从复制的延时问题：
  • 半同步复制：主库写入binlog之后，强制立刻将数据同步到从库，调用IO线程写入从服务器的Relaylog中，并返回一个ack给主库，主库接收到至少一个从库的ack之后才认为写操作完成。
  • 并行复制：从库开启多个线程，并行读取Relaylog中不同库的日志，然后并行重放不同库的日志

读写分离能提高性能的原因：

• 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
• 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
• 增加冗余，提高可用性。

---

缓存

可以在MySQL内部、数据访问层（MyBatis）、应用服务层、Web层做缓存

• 服务层的缓存实现主要有两种方式：
  • 直写式：在数据写入数据库后，同时更新缓存，维持数据库与缓存的一致性。
  • 回写式：有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。

---

表分区

垂直分区

垂直分区是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

水平分区

水平分区（Sharding）是将同一个表中的记录拆分到多个结构相同的表中。