Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to use mysql to achieve a little magic". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The test process is as follows:

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 |

+-+

| warm congratulations on the 100 million anniversary of the Big Bang |

| suddenly changed. Changed. |

+-+

2 rows in set (0.00 sec)

Mysql >

The table used in the test is the MyIsam engine.

The fact that there is no update,delete,insert operation in the execution of flush table shows that this phenomenon is related to caching and so on.

Let's see what flush table did.

The mysql reference manual explains:

Close open tables and force all tables in use to close. This also flushes the query cache. Like the RESET QUERY CACHE statement, FLUSH TABLES also cancels all query results from the query cache.

Hehe, let's analyze how this "magic" came into being.

First of all, we think of query cache, which is easy to understand.

The query cache parameters in this test are as follows:

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)

The function of query cache in mysql is to cache the query results. In the next query, if the query statements are the same, the results will be extracted directly from the cache, rather than from the data table.

Of course, not all queries will be cached, and certain conditions must be met: the size of the result set that exceeds the query_cache_limit is not cached, the result in procedure is not cached, and the external result set in the subquery is not cached.

These problems do not exist in this test.

The whole process is as follows:

Insert the data first:

Mysql > insert into test values ("warm congratulations on the 100 million anniversary of the Big Bang")

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test values ("suddenly changed")

Query OK, 1 row affected (0.00 sec)

Then copy the data file to another location

Root@qadb:/var/lib/mysql/test# cp-a test.* / root/test

Delete data from a table

Mysql > delete from test

Query OK, 3 rows affected (0.00 sec)

Insert new data

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)

Finally, the previously backed up data files are overwritten with the current data files.

Root@qadb:/var/lib/mysql/test# cp-a / root/test.*.

Now you can perform the "magic" of select and flush,select on its client.

It should be clear to everyone here that if we use DDL statements to change the data, the relevant query cache will become invalid accordingly, and if we use it again, it will be read from the data table. However, if you use a direct data file overwrite, at least currently mysql's MyIsam engine does not know that the data has changed, and the corresponding query cache will not fail. The result of the first select is actually the old data taken from the cache, until the query cache is emptied after the flush, and then the select is read from the new data file.

Now I'll do a little "magic" without using 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 |

+-+

| warm congratulations on the 100 million anniversary of the Big Bang |

| suddenly changed. Changed. |

+-+

2 rows in set (0.00 sec)

Mysql >

Generally here, we will have some questions, why do we not take data from cache? why does this phenomenon also occur?

If we still follow the previous operation, it is not possible to overwrite with the backed-up data files.

You can see that there are two cases, if there is more data backed up than the current data, only part of the data in the new data file is displayed. If the backup data is less than the current data, the query reports an error.

Mysql > select sql_no_cache * from test

ERROR 1194 (HY000): Table 'test' is marked as crashed and should be repaired

These are related to the fault detection mechanism of mysql, which I have not studied in depth. We'll see it later when we have time.

If you do flush table or repair in the above situation, you can return to normal, but you can't repeat our "magic" this time.

Tell me about the process of my operation:

Insert data-> backup table data file

Delete old data

Insert new data

This process is the same as the previous scene. After that, it was different.

Delete the current data file

Root@qadb:/var/lib/mysql/test# rm test.*

Copy back up data files

Root@qadb:/var/lib/mysql/test# cp-a / root/temp/test.*.

Now the previous client can perform the magic of select,flush,select again.

Why is it this time?

First of all, declare that this test can only be implemented in linux/unix, and windows cannot.

We know that mysql manipulates data files through os, which is related to the processing of files by linux/unix.

A classic question is, in windows, when a file is used, can another process delete the file? We all know that. No.

But what about in linux/unix? Yes, you can. Does the previous process of reading the file report an error after the file is deleted by another process? I won't either.

In fact, this file was not really deleted at this time. In linux/unix, the file is actually released only if all processes / threads that manipulate the file exit.

The operation of analyzing this select,flush,select is as follows:

Although another process deletes the data file and copy into the new data file, because the previous table is open, the mysql background thread has been using the old data file, which is not really deleted. At this point, the data of select is still old data.

Flush closes the table, the thread using the old data file exits, and the old data file is actually released.

Again, the select table open reads the new data file. At this point, the data of select is new.

"how to use mysql to achieve a little magic" content is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.