Analysis on the principle and cache process of using Memcache to cache mysql databases
For large websites such as facebook and ebay, if Memcache is not used as the intermediate cache layer, data access cannot be avoided. for general websites, as long as they have independent servers, you can configure Memcache to spe
Setflushmode () method of the Session Reflesh (): Forces the state of an object in the cache to be consistent with the record of the database. Forces the SELECT statement to be sent so that the state of the object in the session cache is consistent with the corresponding record in the data table Clear (): Clean up the
statements are used IN the query statement.
The query statement is similar to SELECT... INTO statement for data export;
When the transaction isolation level is Serializable, all query statements cannot be cached;
Query temporary tables;
Query statement with warning information;
Query statements that do not involve any tables or views;
A user can only query statements with column-level permissions;
Advantages and disadvantages of querying cache
Recently, a project was developed to replace the data table prefix with different schemas (database architecture) when creating a database. This design makes the data table name Friendly and logically clear and more compliant with the SQL Server database specifications.
However, the problem occurred yesterday when the data table
Basic tutorial on querying the cache mechanism of MySQL,
The MySQL cache mechanism simply caches the SQL text and query results. If the same SQL statement is run, the server directly obtains the results from the cache without parsing and executing the SQL statement. If the
1. Query cache key is a SQL string, and requires exact matching: The case is not the same or more than one space, will result in cache miss
2. Query cache directly the user input of the original SQL as a key, not to parse or optimized after the SQL as a key, also will not be a subquery in which the independent cache
connection_id found_rows get_lock release_lock load_file Master_po S_wait now sysdate current_timestamp curdate current_date curtime current_time DATABASE ENCRYPT (only one parameter invocation) last_insert_id RAND unix_timestamp (no parameter calls) USER BENCHMARK
If a query contains user variables, refer to the MySQL system database, or one of the following formats, SELECT ... In SHARE MODE, SELECT ... Into outfile ..., SELECT ... Into DumpFile .
received select statement in string mode based on the preset hash algorithm, then, go to the Query Cache to check whether the data has been cached. That is to say, if the select request is already in the cache, the data will be directly returned, thus omitting all subsequent steps (such as SQL statement parsing, optimizer optimization and Data Request to the storage engine), greatly improving performance.
algorithm and then go directly to the query cache to find out if it is already cached. That is, if it is already in the cache, the select request returns the data directly, omitting all subsequent steps (such as parsing of SQL statements, optimizer optimizations, and requesting data to the storage engine), which greatly improves performance.
Of course, Query Cache
Mysql memory engine cache and file cache, which is more efficient session?
Data is stored by file hard disk by default. ---> hard disk
Change to mysql memory engine storage ----> Memory
Regardless of other factors, which of the two solutions is faster?
Hard disk File> mys
have explicitly stated that you need to use Query Cache), MySQL will directly HASH the received select statement in string mode based on the preset hash algorithm, then, go to the Query Cache to check whether the data has been cached. That is to say, if the select request is already in the cache, the data will be dire
For large websites such as facebook and ebay, if Memcache is not used as the intermediate cache layer, data access cannot be avoided. For general websites, as long as they have independent servers, you can configure Memcache to speed up Website access and reduce database pressure. Here we will mainly discuss the process relationship between Memcache and MySQL database interaction, and understand the role of
1 , MySQL query cacheMany database products are able to cache the execution plan of a query, and the SQL parsing and execution plan generation phases can be skipped for the same type of SQL. MySQL also has a different type of cache: Cache the full select query result, which
MySQLquerycache has been provided since MySQL 4.1. However, we are only able to study it today. By default, MySQL does not start this function. you may find that the value of its variable have_query_cache is yes through showvariableslike '% query_cache %, for beginners of MYSQL, it is easy to think that this parameter is "YES", which indicates that it is enabled.
Session
The default is to use the file hard disk to store data; ---> HDD
If you change to MySQL memory engine storage----> Memory
Regardless of other factors, what is the speed of these two options, only in terms of efficiency?
HDD File > mysql;
The end result?! Special Ask.
Reply to discussion (solution)
Latter!This, you'll know when you actually compare it.
The static fi
default value is 0 and the size must be an integer multiple of 1024. If it is not an integer multiple, mySQL automatically adjusts and reduces the minimum volume to a multiple of 1024.◆ Query_cache_type: the switch that controls the Query Cache function. It can be set to 0 (OFF), 1 (ON), and 2 (DEMAND). The meanings are as follows:
0 (OFF): disables the Query Cache
query caching is as follows:1: Hit conditionThe cache exists in a hash table, by querying SQL, querying the database, the client protocol, and so on as key. Before deciding whether to hit, MySQL will not parse SQL, but directly use SQL to query the cache, any character on the SQL, such as spaces, comments, will cause
default value is 0 and the size must be an integer multiple of 1024. If it is not an integer multiple, MySQL automatically adjusts and reduces the minimum size to a multiple of 1024.
Query_cache_type: the switch that controls the query cache function. It can be set to 0 (off), 1 (on), and 2 (Demand). The meanings are as follows:
0 (off): disables the query cache
explicitly stated that Query Cache is not allowed, or you have explicitly stated that you need to use Query Cache), MySQL will directly HASH the received select statement in string mode based on the preset hash algorithm, then, go to the Query Cache to check whether the data has been cached. That is to say, if the sel
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.