I. theory: 1. use connection pool a to treat the connection as an object or device and put it in the 'connection pooling. Connect 2 from the connection pool where you need to access the database to reduce access to mysql: a to avoid repeated searches for the same data B to use query cache 1) have_query_cache: 1. theory:
1. use the connection pool
A. Use the connection as an object or device and put it in the 'connection pool. Connect from the connection pool wherever you need to access the database
2. reduce access to mysql:
A. avoid repeated searches for the same data
B. use query cache
1) have_query_cache: indicates whether the high-speed cache has been configured on the server during installation.
2) query_cache_size: indicates the cache size.
3) query_cache_type: 0/off = cache off, 1/on = Cache open (except select with SQL _no_cache), 2/demand (only select statements with SQL _cache provide high-speed cache)
3. use the show status command to monitor query cache usage:
A. qcache_queries_in_cache: number of queries registered in the cache
B. qcache_inserts: number of queries added to the cache
C. qcache_his: number of cache samples
D. qcache_lowmem_prunes: number of queries deleted from the cache due to lack of memory
E. qcache_not_cached: number of queries not cached
F. qcache_free_memory: queries the total memory size of the cache space.
G. qcache_free_blocks: queries the number of idle memory blocks in the cache.
H. qcache_total_blocks: queries the total number of cached blocks.
4. added the cache layer:
A. extract part of the data from the database and save it as a file.
B. create a secondary database
5. server load balancer:
A. query by mysql replication and shunting
B. adopt the distributed database architecture. you can use the mysql cluster function, but you must use the innod storage engine.
6. Other optimization solutions:
A. for myisam tables without row deletion operations, the insert operation can be performed in parallel with the query operation. Because no blocking occurs. If you must delete it, you can delete it in idle time. And perform the optimize operation after the deletion.
B. make full use of the column's default values. the insert value is 'clear' only when the inserted value is different from the default value, which improves the insert speed.
C. do not use auto-increment variables for table fields as much as possible, because auto-increment in high concurrency may affect the efficiency.
II. practice:
mysql> use sakila;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show variables like '%query_cache%';+------------------------------+---------+| Variable_name | Value |+------------------------------+---------+| have_query_cache | YES || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 0 || query_cache_type | OFF || query_cache_wlock_invalidate | OFF |+------------------------------+---------+6 rows in set (0.00 sec)