怎么用mysql实现一个小魔术

39次阅读

共计 3688 个字符，预计需要花费 10 分钟才能阅读完成。

本篇内容介绍了“怎么用 mysql 实现一个小魔术”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

测试过程如下：
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

测试中使用的表是 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

一般到这里大家会产生点疑问，都不从 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 实现一个小魔术”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！

正文完