MySQL Management and optimization (19): Application optimization

Application optimization: Using connection pooling:

• with a database connection pool, you can reuse database connection objects, reducing the resource consumption associated with creating new connections.

Reduce access to MySQL: Avoid repeated searches of the same data:

• Clarify your application's business logic and minimize the number of queries.

Use Query caching:

• parameters for MySQL Query cache:

mysql> SHOW VARIABLES like '%query_cache% '; +------------------------------+---------+| Variable_name                | Value   |+------------------------------+---------+| have_query_cache             | YES     |--whether the server is configured for caching | query_cache_limit            | 1048576 |--Limit the size of the cached individual query results, default 1m| query_cache_min_res_unit     | 4096    |--Minimum block size when allocating cache, default 4kb| query_cache_size             | 1048576 | --buffer size, depending on the system will have a minimum buffer size | Query_cache_type             | Off     |--0, close cache, 1 open cache, 2 requests using cache (using select Sql_cache) | query_cache_wlock_invalidate | OFF     |-for the MyISAM table, after acquiring the write lock, whether to fail the other connection read cache request +------------------------------+---------+

• To view the cache status:

mysql> SHOW STATUS like '%qcache% '; +-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| Qcache_free_blocks      | 1       |--free memory blocks in the query cache | Qcache_free_memory      | 1031336 |--Number of free memory for query cache | Qcache_hits             | 0       |--Number of cache samples | Qcache_inserts          | 0       |--Number of queries added to the cache | Qcache_lowmem_prunes    | 0       |--Number of queries deleted due to missing cache | qcache_not_cached       | 1       |-The number of queries that are not cached (cannot be cached, or because of Query_cache_type) | Qcache_queries_in_cache | 0       |--Number of queries registered in the cache | Qcache_total_blocks     | 1       |--Total number of blocks in query cache +-------------------------+---------+

Add the cache layer:

• That is, when caching is done on the application side, you should be aware of when to fail the cache.

Load balancing: Use MySQL replication to split the query operation:

• with MySQL master/slave server for read-write separation, the primary server assumes write operations, writes from the server, and can set the need to replicate tables to the Blackhole engine, and then define the Replicate-do-table parameter to replicate only those tables.

Using a distributed Database architecture:

• refer to MySQL's cluster feature, distributed transactions only support InnoDB storage engine.

Other optimization measures:

• for MyISAM tables that do not have a delete operation, insert and select are parallel. If there is a delete operation, try to perform a bulk delete operation in idle, delete should perform optimize operation, eliminate the void caused by the deletion operation.
• Take advantage of the fact that the column has a default value. Inserting values is explicitly inserted only when the insertion value differs from the default value, which reduces the parsing of MySQL and increases the insertion speed.
• table fields try not to use self-growth variables, and in high concurrency, the field's self-increment may have a greater effect on efficiency, and it is recommended to implement the self-growth of the field through the application.

Do not hesitate to correct me.

MySQL Management and optimization (19): Application optimization