09年系统架构师大会中一个mysql小"魔术"的分析

来源http://fuleqing.itpub.net/ 转载请注明作者和出处。

记得在09年系统架构师大会上一个51.com的DBA秀了一个mysql的小“魔术”，其大概过程是不更改数据库表中的数据，只是执行了一下flush table表中的数据就全部改变。
之前没研究过mysql，一时也不明就里。
这两天对mysql很感兴趣，想起了去年这位老兄的“魔术”。今天就做了个测试重现了一下这个场景。

[@more@]

测试过程如下：
mysql> select * from test;
+------+
| name |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)

mysql> select * from test;
+------+
| name |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------------------------------+
| name |
+------------------------------+
| 热烈祝贺宇宙大爆炸一万万周年 |
| 忽忽变了变了 |
+------------------------------+
2 rows in set (0.00 sec)

mysql>

测试中使用的表是MyIsam引擎。
只是执行flush table并没有update，delete，insert的操作说明这个现象也就是跟缓存之类的有关。
先看看flush table是做了什么。
mysql参考手册中解释：
关闭打开的表，并迫使所有正在使用的表关闭。这也会刷新查询缓存。和RESET QUERY CACHE语句一样，FLUSH TABLES还会取消来自查询缓存的所有查询结果。
呵呵 下面来分析一下这个“魔术”是怎么产生的。
首先我们会想到query cache，其实这很容易理解。
本次测试中query cache参数如下：
mysql> show variables like 'q%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 34603008 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+
7 rows in set (0.00 sec)
mysql中的query cache的功能是缓存查询结果，在下次查询是如果查询语句相同会直接从缓存中提取结果，而不是从数据表中查询。
当然并非是所有的查询都会被缓存，要满足一定条件:结果集超过query_cache_limit的大小不缓存,procedure中的结果不缓存，子查询中的外联结果集不缓存。。
本次测试不存在这些问题。
所有过程如下:
先插入数据：
mysql> insert into test values("热烈祝贺宇宙大爆炸一万万周年");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values("忽忽变了变了");
Query OK, 1 row affected (0.00 sec)
然后把数据文件copy到其他位置
root@qadb:/var/lib/mysql/test# cp -a test.* /root/test
删除表中的数据
mysql> delete from test;
Query OK, 3 rows affected (0.00 sec)
插入新数据
mysql> insert into fuleqing values('1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into fuleqing values('2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into fuleqing values('3');
Query OK, 1 row affected (0.00 sec)

mysql> insert into fuleqing values('4');
Query OK, 1 row affected (0.00 sec)

mysql> insert into fuleqing values('5');
Query OK, 1 row affected (0.00 sec)

最后把之前备份的数据文件覆盖现在的数据文件
root@qadb:/var/lib/mysql/test# cp -a /root/test.* .

现在在之客户端执行select ，flush，select这个”魔术“就可以了

到这里大家应该都看明白了吧,如果我们使用DDL语句更改数据则相关的query cache则会相应的失效，再次使用会从数据表中读取。但如果使用直接数据文件覆盖的话至少目前mysql的MyIsam引擎并不知道数据发生了变化，相应的query cache并不会失效。第一次select的结果其实是从cache里取的旧数据，直到flush后query cache被清空，然后再select才是从新的数据文件读取。

下面我再做一个小”魔术“，不使用query cache
mysql> select sql_no_cache * from test;
+------+
| name |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)

mysql> select sql_no_cache * from test;
+------+
| name |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select sql_no_cache * from test;
+------------------------------+
| name |
+------------------------------+
| 热烈祝贺宇宙大爆炸一万万周年 |
| 忽忽变了变了 |
+------------------------------+
2 rows in set (0.00 sec)

mysql>

一般到这里大家会产生点疑问，都不从cache里取数据了为什么也会出现这个现象呢？

如果我们还是按照之前的操作，用备份的数据文件覆盖是不行的。
大家可以看到有两种情况，如果备份的数据比当前的数据多，只显示新数据文件中的部分数据。如果备份的数据比当前的数据少，查询即报错。
mysql> select sql_no_cache * from test;
ERROR 1194 (HY000): Table 'test' is marked as crashed and should be repaired
这些跟mysql的故障侦测机制有关，我没有深入的研究。等以后有时间再看
在出现上面这些情况时做一下flush table或者repair一下都可以恢复正常，但是重现不了我们这次的”魔术“
说一下我操作的过程：
插入数据->备份表的数据文件

delete旧数据
insert新数据
这个过程跟前一个场景相同.之后就不一样了
删除当前数据文件
root@qadb:/var/lib/mysql/test# rm test.*
copy回备份数据文件
root@qadb:/var/lib/mysql/test# cp -a /root/temp/test.* .
现在前面的客户端又可以执行select,flush,select这个"魔术"了

这次又是为什么呢?
首先声明这次的测试只有在linux/unix中才能实现,windows不可以
我们知道mysql是通过os来操作数据文件的,这就要跟linux/unix对文件的处理有关.
一个比较经典的问题,在windows中当一个文件被使用的时候,另一个进程可以删除这个文件吗?这个我们都知道不行。
但在linux/unix中呢？是可以的。之前读取文件的进程在文件被另一个进程删除后会报错吗？也不会。

这个文件这时候其实并没有真正意义上删除。在linux/unix中只有所有操作该文件的进程/线程都退出,该文件才会真正的被释放。
分析本次的select，flush，select的操作如下：
虽然另一个进程删除了数据文件并copy进新的数据文件但由于之前表是open的，mysql后台线程一直在使用旧数据文件，该文件并未真正删除。此时select的数据仍是旧数据。
flush 关闭表，使用旧数据文件的线程退出，旧数据文件被真实释放；
再次select 表open这时读取新的数据文件。此时select的数据为新数据。

总结：
第一次的”魔术“是由于mysql的query cache造成的有趣现象；
第二次的”魔术“是由于linux/unix操作系统本身对文件的并发处理机制造成的有趣现象；

如果有兴趣大家可以再研究研究有没有其他的可能。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/598443/viewspace-1033813/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/598443/viewspace-1033813/