MySQL Development Advanced Article series 23 Application layer optimization and query caching

I. Overview

The previous section describes many of the database optimization measures, but in the actual production environment, due to the performance limitations of the database server itself, it is necessary to optimize the application of the foreground, so that the pressure of the front-desk access to the database can be minimized.
　1. Using connection pooling
For access to a database, the cost of establishing a connection is expensive, because connecting to the database server involves several steps such as establishing a physical channel, first shaking the server, parsing the connection string information, authenticating the connection by the server, and so on. Therefore, it is necessary to establish a "connection pool" to improve the performance of access. Connections in the connection pool have been pre-created and can be directly assigned to the application tier, reducing the resources consumed by creating new connections, which are returned to the connection pool for new access.

(1) If an idle connection is available in the pool, the connection is returned.
(2) If the connections in the pool are exhausted, create a new connection to the pool.
(3) If the connection in the pool has reached the maximum number of connections, the request enters the wait queue until an idle connection is available.

//The following is an example of an ADO connection database://Reference System.Data.SqlClient//You can instantiate a SqlConnection object by using string connectionstring            stringConnectionString ="Integrated security=false;server={0};d atabase={1}; User id={2}; PASSWORD={3}; Max Pool size=512; Connect timeout=30"; //You can also use the SqlConnectionStringBuilder class to instantiate a SqlConnection objectSqlConnectionStringBuilder Sqlconnstringbuilder =NewSqlConnectionStringBuilder (); //whether the connection pool is turned on by default to TrueSqlconnstringbuilder.pooling =true; //maximum number of connections in the connection poolSqlconnstringbuilder.maxpoolsize = +; //connection request Wait time-out. The default is 15 seconds, in seconds. Sqlconnstringbuilder.connecttimeout = -; Sqlconnstringbuilder.datasource=""; Sqlconnstringbuilder.userid=""; Sqlconnstringbuilder.password=""; //Connect using User name and passwordSqlconnstringbuilder.integratedsecurity =false; SqlConnection SQL=NewSqlConnection (connectionString); //orsql =NewSqlConnection (sqlconnstringbuilder.connectionstring); //when you're done, remember to close the current connectionSQL.            Close (); //use MySQL as a reference MySql.Data.dllMySql.Data.MySqlClient.MySqlConnection Mysqlconn =NewMySql.Data.MySqlClient.MySqlConnection (); MySql.Data.MySqlClient.MySqlConnectionStringBuilder Mysqlconnstringbuilder=NewMySql.Data.MySqlClient.MySqlConnectionStringBuilder ();

　2. Using the query cache
　　MySQL's query cache has been added since the 4.1 release, and its role is to store the text of the select query and the corresponding results. If you subsequently receive the same query, the server will re-get the query results from the query cache, eliminating the need to parse and execute the query. The applicable object of the query cache is a table that is not updated frequently, and when the table changes (table structure and table Data ), the associated entry for the query cache value is emptied.

-  -'%query_cache%';

Parameter explanation:

Have_query_cache	Indicates whether the MySQL version supports query caching.
Query_cache_limit	Represents the maximum value that a single result set is allowed to cache. 1048576.0/1024.0/1024.0=1.0m default 1M, exceeding the space size is not cached.
Query_cache_min_res_unit	The minimum memory to be consumed by each cached result set.
Query_cache_size	The total amount of memory used to query the cache. 1048576.0/1024.0/1024.0=1.0m default 1M, exceeding the space size is not cached.
Query_cache_type	Turn off caching by default
Query_cache_wlock_invalidate	Controls whether the Query cache associated with the table is invalidated when a write lock is added to the table. OFF: The Query Cache associated with the table is still allowed to be read at the time of the lock. On: The write lock will invalidate all Query caches associated with the table.

--  Monitor Query cache usage like'qcache%'

Parameter explanation:

Qcache_free_memory	The amount of space currently remaining in the query cache.
Qcache_hits	The number of hits for the query cache.
Qcache_inserts	Number of query cache insertions
Qcache_free_blocks	How many remaining blocks are there at the moment? FLUSH QUERY Cache organizes the fragments in the cache to get a free block. This value is larger, which means that memory fragmentation is more
Qcache_lowmem_prunes	How many of the query caches are cleared out because of insufficient memory. The cache is running out of memory and must be cleaned up to provide more space for more queries. This number is best seen over a long period of time, and if the number is growing, it can mean that fragmentation is very serious, or that there is little memory.
Qcache_not_cached	The number of query that cannot be cache. The number of cache queries that are not appropriate, usually because these queries are not SELECT statements
Qcache_queries_in_cache	The query number of the cache in the current query cache.
Qcache_total_blocks	The number of blocks in the current query Cache.

(Query cache chapter is not finished ...) ）