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

Query cache

Query cache-downloads the results to be found, and returns the cached results directly if the query statements are the same.

If the application needs to query multiple times in a scenario and does not need to be updated frequently, using query caching can improve performance. As shown in the picture, you can see if caching is enabled.

The value of query_cache_type (on;) indicates that caching is enabled.

 query_cache_type: whether to enable caching. Values include ON, OFF, and DEMAND, which means enabled or disabled. If necessary, you can set caching in my.cnf.  query_cache_min_res_unit: the minimum allocation unit of memory blocks in the query cache. Default is 4k. A smaller value will reduce waste, but will lead to more frequent memory allocation operations, higher values will lead to waste, lead to too many fragments, and insufficient memory. Query_cache_limit: the maximum value that a single query result can be cached. Default is 1m. For statements that are too large to cache. It is recommended to use SQL_NO_CACHE  query_cache_size: query the total memory space available in the cache Unit byte, must be an integer multiple of 1024, with a minimum value of 40KB. Below this value, there is an alarm  query_cache_wlock_invalidate: if a table is locked by another session, whether the result can still be returned from the query cache. The default value is OFF, indicating that you can continue to return data from the cache in a scenario where the table is locked by another session. ON indicates that cache control of SELECT statements is not allowed  SQL_CACHE: explicitly specify that query results are stored in cache  SQL_NO_CACHE: explicit query results are not cached when the value of  query_cache_type parameter variable  query_cache_type is OFF or 0, the query caching function is turned off when the value of  query_cache_type is ON or 1, and the query caching function is turned on, and the SELECT results will be cached when the cache conditions are met, otherwise Do not cache, explicitly specify SQL_NO_CACHE, do not cache. This is the default value. When the value of  query_cache_type is DEMAND or 2, the query caching function will be carried out as needed, and only the SELECT statements that explicitly specify SQL_CACHE will be cached. Others are not cached # query cache-related state variables: SHOW GLOBAL STATUS LIKE 'Qcache%'

Qcache_free_blocks: total Block in the number of memory Block in idle Query Cache. When Qcache_free_blocks is relatively large, it is possible to use memory fragments to clean up  Qcache_free_memory: total Query Cache memory in idle state. Number of Qcache_hits:Query Cache hits  Qcache_inserts: number of new Query Cache inserted into Query Cache That is, the number of missed Qcache_lowmem_prunes: record the number of queries removed from the query cache due to insufficient memory  Qcache_not_cached: the number of SQL that has not been Cache, including the SQL that cannot be Cache and the SQL statement  Qcache_queries_in_cache that will not be Cache due to query_cache_type: the number of SQL in Query Cache

-

Storage engine MyISAM engine features  does not support transactions  table-level locking  reads and writes cannot be read, cannot be written while reading  only cache indexes  does not support foreign key constraints  does not support clustered indexes  read data faster, consume less resources  does not support MVCC (multi-version concurrency control mechanism) high concurrency  crash recovery poor  MySQL5.5.5 default database engine

-

MyISAM Storage engine-applicable scenario

Read-only (or write less), smaller tables (can accept long repair operations) MyISAM engine files tbl_name.frm table format definition tbl_name.MYD data files tbl_name.MYI index files

-

Features of InnoDB engine

Row-level lock  supports transactions, suitable for handling a large number of short-term transactions  read and write blocking and transaction isolation level related  cacheable data and indexes  supports clustered indexes  crash recovery better  supports MVCC high concurrency  supports full-text indexing after MySQL5.5  is the default database engine from MySQL5.5.5

InnoDB database files data and indexes of all InnoDB tables are placed in the same tablespace file: datadir defined directory data files: ibddata1, ibddata2,... Each table uses a separate table space to store the table's data and indexes enabled: innodb_file_per_table=ON two types of files are placed in the database separate directory data files (storage data and indexes): tb_name.ibd table format definition: tb_name.frm management storage engine  view mysql supported storage engine show engines; view the current default storage engine show variables like'% storage_engine%' Set the default storage engine vim / etc/my.conf [mysqld] default_storage_engine= InnoDB the storage engine used by show table status from db_name; to view all tables in the library the storage engine show table status like 'tb_name' of the specified tables in the library; show create table tb_name Set the storage engine of the table: CREATE TABLE tb_name (...) ENGINE=InnoDB; ALTER TABLE tb_name ENGINE=InnoDB

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.