mysql进阶问题与解答

数据库如何应对大规模写入和读取

传统的关系型数据库已经无法满足快速查询与插入数据的需求。这个时候NoSQL的出现暂时解决了这一危机。它通过降低数据的安全性，减少对事务的支持，减少对复杂查询的支持，来获取性能上的提升。

但是，在有些场合NoSQL一些折衷是无法满足使用场景的，就比如有些使用场景是绝对要有事务与安全指标的，比如转账业务。这个时候NoSQL肯定是无法满足的，所以还是需要使用关系性数据库。

方案一：缓存和读写分离

使用redis等缓存数据库做缓冲，从redis中读取数据，降低mysql的开销。

将数据库做主从数据库集群，从数据库用于读取，主数据库负责写入。

方案二：表切分（水平切分，垂直切分）

水平切分：不改变表的结构，通过对数据的拆分达到分片的目的。（分片的标准可以按照hash来，也可以按照日期来）

垂直切分：修改表结构，按照访问的差异将某些列拆分出去，一般查询数据的时候可能会用到 join 操作；把常用的字段放一个表,不常用的放一个表，把字段比较大的比如text的字段拆出来放一个表里面。

方案三：分库

数据量过大时，可以使用分表的方式，解决查询效率下降的问题。但是分表的方式无法解决高并发的问题，这个时候就要用到分库了，分库就是对数据库进行拆分，将数据放在不同的数据库中，不同的主机上，通过对某个字段取模hash的方式对数据的访问进行路由。

千万级数据的分页显示

方案一：使用limit

select * from table where ... group by id limit 523,10;

从第523个数据后读取十个。

方案二：自增字段

在待查询的数据库表上增加一个用于查询的自增字段（比如“queryId”），先按照大小顺序的倒序查出所有的queryId，因为只是查询queryId字段，即使表格中的数据量很大，该查询也会很快得到结果。然后将得到的queryId保存在应用服务器的一个数组中。

主从同步的一致性实现

MySQL复制的基础，就是二进制日志，因为二进志日志binlog里面，记录了所有能改变或者能潜在改变数据库数据的SQL语句，当从服务器把这些SQL语句复制过来之后，再执行一遍，数据就会跟主服务器相同了。

1.数据库有个bin-log二进制文件，记录了所有sql语句。

2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。

3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。

具体需要三个线程来操作：

1.binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。

在从库里，当复制开始的时候，从库就会创建两个线程进行处理：

2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。

3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

主从同步的延迟问题

主库的每秒事务并发数较高时，产生的写操作的数量超过slave一个sql线程所能承受的范围，那么延时就产生了。当然还有就是可能与slave的大型query语句产生了锁等待。

解决方法：

1. 半同步复制

主库在执行完事务后不立刻返回结果给客户端，需要等待至少一个从库接收到并写到relay log中才返回结果给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一个TCP/IP往返耗时的延迟。

2.升级slave的硬件，或者降低slave事务隔离性，因为从库是单线程操作，不会出现事务之间过多的碰撞。

缓存重建

redis自带持久化功能。

MongoDB采用mmap来将数据文件映射到内存中，所以当MongoDB重启时，这些映射的内存并不会清掉，因为它们是由操作系统维护的（所以当操作系统重启时，MongoDB才会有相同问题）。相对于其它一些自己维护Cache的数据库，MongoDB在重启后并不需要进行缓存重建与预热。