BufferPool缓存机制

BufferPool缓存机制
mysql 的数据是存放在磁盘上的，但是每次去读数据都需要通过这个磁盘io，效率就很低，使用 innodb 提供了一个缓存 buffer，这个 buffer 中包含了磁盘部分数据页的一个映射，作为访问数据库的一个缓冲，从数据库读取一个数据，就会先从这个 buffer 中获取，如果 buffer 中没有，就从这个磁盘中获取，读取完再放到这个 buffer 缓冲中，当数据库写入数据的时候，也会首先向这个 buffer 中写入数据，定期将 buffer 中的数据刷新到磁盘中，进行持久化的一个操作。

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？
因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。
因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。 Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。 更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。 正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求。

重做日志刷新到磁盘的策略
通过参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略。
该参数的默认值为1，表示事务提交时必须调用一次fsync操作。
0表示事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，而在master thread中每1秒会进行一次重做日志文件的fsync操作。
2表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。在这个设置下，当MySQL数据库发生宕机而操作系统不发生宕机时，并不会导致事务的丢失。而当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。
举例：逐条插入50万条数据。
innodb_flush_log_at_trx_commit = 1时： 用时 2 分 13 秒。50 万次写入重做日志；fsync操作50万次。
innodb_flush_log_at_trx_commit = 0时： 用时 23 秒。约23次写如重做日志；fsync操作约23次。
innodb_flush_log_at_trx_commit = 2时： 用时 35 秒。50万次写入重做日志（仅缓存）；fsync操作0次。

虽然用户可以通过设置参数innodb_flush_log_at_trx_commit为0或2来提高事务提交的性能，但是需要牢记的是，这种设置方法丧失了事务的ACID特性。而针对上述存储过程，为了提高事务的提交性能，应该在将50万行记录插入表后进行一次的COMMIT操作，而不是在每插入一条记录后进行一次COMMIT操作。这样做的好处是还可以使事务方法在回滚时回滚到事务最开始的确定状态。

正确方法：innodb_flush_log_at_trx_commit = 1，将50万条数据在一个事务或者多个事务中分派提交，减少fsync次数。

filesort的过程
filesort的过程是这样的：
第一步先根据表的索引或者全表扫描，读取所有满足条件的记录。
第二步，存储每一行排序列，就是order by用到的列值，还有行记录指针，就是指向该行数据的行指针，把这二个存储到缓冲区。
第三步，当缓冲区满后，运行一个快速排序来将缓冲区中数据排序，将排序完的数据存储到一个临时文件，保存一个存储块的指针，当然如果缓冲区不满，则不会重建临时文件了。直到将所有行读完，建立相应有序的临时文件。
第四步，对块级进行排序，这个类似归并排序算法，只通过两个临时文件的指针来不断交换数据，最终达到两个文件，都是有序的，直到所有的数据都排序完毕。
第五步，采取顺序读的方式，将每行数据读入内存，取出数据传到客户端。