Mysql常见问题汇总（4）- 日志篇

1.如何提高insert的性能 && redo日志刷盘策略

1. 合并多条insert为一条

   • 多条insert合并后日志量减少，降低了日志刷盘的数据量和频率，从而提高效率，同时减少sql语句解析次数，网络传输io

2. 修改bulk_insert_buffer_size，调大批量插入的缓存

3. 手动使用事务

   • 不使用autocommit，减少提交的次数，进而减少事务的消耗

4. 设置innodb_flush_log_at_trx_commit = 0

   • 这个是控制redo日志刷盘的策略
   • 当该参数为0 时，事务提交之前只写到redo log buffer，不刷盘，后台线程每隔1s批量刷盘。
     • 因为是批量IO，所以速度很快，插入性能高

• 参数为1时（默认），写redo log buffer，立即刷盘，执行fsync确认落盘

参数为2，事务提交后写到OS缓存，每秒刷盘

2. mysql 读写分离 & 主从同步

通过同步数据在不同数据库服务器上，一个服务器只负责写操作,称为主库，另一个只负责读操作，称为从库。

既然涉及读写分离，就离不开数据同步问题。

主库和从库的的数据同步通过bin log，relay log ，主库中的Log dump线程，从库中的 IO线程和sql 线程。

主库写入数据(insert/update)时，会把更新写到bin log中，Log dump线程会把bin log中的更新内容发送给从库，从库通过IO线程接受写入relay log（中继日志），SQL线程再从中继日志中把改动写入从库数据库

主从同步模式：

• 全同步复制：主库写入binlog后强制同步日志到从库，所有从库都执行完才返回给客户端，性能低下
• 半同步复制：不同于全同步复制，只要有一个从库执行完返回ack给主库，就默认为同步完成

3. mysql的分库分表

分库分表包含分库和分表两个部分，这两个部分可以统称为数据分片。

• 垂直拆分：关注点在于业务，比方说，有用户表和内容表，通过把用户表放在一个数据库，内容表放在另一个数据库实现垂直拆分。这种拆分可以解决数据库数据文件过大的问题，但是当用户表一大了，这种拆分方式不能解决查询效率低的问题。
• 水平拆分：关注点在将一张表的数据拆分到多张表或者多个数据库中（单一数据表按照某种规则拆分），理论上突破了单机数据量处理的瓶颈，扩展相对自由
  • 常见策略可以是：
  • 按照范围分片
  • 按照时间分片
  • 按照枚举值分片：比如不同地区分片
  • 水平拆分可以通过对 主键 取hash后取模，拆分到不同的表中

分库分表最常用的组件：Mycat\ ShardingSphere

分库分表常见问题：

1. 跨库查询
2. 跨库排序
3. 分布式事务
4. 主键重复