怎么用mysql實現一個小魔術

本篇內容介紹了“怎么用mysql實現一個小魔術”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

測試過程如下：
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實現一個小魔術”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！