Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "how to optimize MYSQL single table query". Many people will encounter such a dilemma in the operation of actual cases, 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!

Single table query optimization

(1) many people may not know that MYSQL has a magic keyword EXPLAIN keyword, which allows you to see how MYSQL handles your SQL statements, which can help us determine whether the query statement or table structure affects the performance of your database.

(2) when we query the database, we should make clear which fields you need to query, instead of using select *.

(3) you can use joins (JOIN) to replace subqueries.

(4) when using limit, count or conditional where clauses, all the conditions that need to be restricted should be added to ensure that what data you want to check is the data you need to find. Try not to find useless data and do not abuse it. For example, I want to find the account corresponding to the name, so I can use limit 1, so that mysql stops searching after finding a piece of data, rather than stopping after full-text search.

Enable query caching:

This is one of the effective ways to improve the query. When the query is executed, the corresponding query results will be put into a cache, so that the same query will not have to manipulate the table, but directly access the cache to retrieve the query results.

Query caching workflow:

(1) the server receives the SQL and uses SQL+DB+Query_cache_query_flags as the hash lookup key to submit it to the cache for query.

(2) if the relevant processing result is found in the cache, the processing result is returned to the client.

(3) if the cache is not found, perform some operations such as SQL parsing, preprocessing and SQL optimization.

(4) after executing the SQL, save the result set to the cache and return the processing result to the client.

So, how do we determine when caching needs to be turned on? It makes sense to turn it on to significantly improve the performance of the system. The methods are as follows:

1) by judging the cache hit rate, the cache hit rate = cache hit times / query times.

2) through cache write rate, write rate = cache write times / query times.

3) judging by the hit-write rate, the ratio is the number of hits / writes, which is called an index in MySQL that reflects the performance improvement. Generally speaking, 3:1 is considered to be effective for the query cache, and it is best to reach 10:1.

The main parameters related to caching are shown in the following table. You can use the command SHOW VARIABLES LIKE'% query_cache%' to view

Timing of cache data failure

When the structure or data of the table changes, the data in the query cache is no longer valid. Data in the cache is invalidated when INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE statements are executed. Therefore, the query cache is suitable for use when there are a large number of the same queries, but not when there are a large number of data updates.

You can use the following three SQL to clean up the query cache:

1. FLUSH QUERY CACHE; / / Clean up the memory fragments of the query cache.

2. RESET QUERY CACHE; / / remove all queries from the query cache.

3. FLUSH TABLES; / / close all open tables, and this operation will empty the contents of the query cache.

This is the end of the content of "how to optimize MYSQL single table query". 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.