MYSQL/MARIADB Foundation (2)

MARIADB Query Cache

The data in the cache is in open source form, in the form of key-value pairs (k/v)

Key: The hash value of the query statement;

Value: Query result of query statement;

The data in the cache is mainly through the comparison of the hash value of the entire query statement, the exact same hit, so that the cache response to client requests can improve the retrieval efficiency; Of course, not all of the query data can be cached, then what data cannot be cached?

1. The database to be queried may contain sensitive information, such as each system table in the MySQL database; 2. A user-defined function (UDF) 3 is included in the query statement. Storage functions ; 4. user-defined variables; 5. Query requests initiated for temporary tables; 6. Queries containing column-level authorizations; 7. Internal key functions for MySQL with indeterminate result values, such as: Now (), current_date (), Current_time (), Current_ USER (),...

Server parameters related to query caching:

MariaDB [hellodb]> show global variables like ' query_cache% '; +------------------------------+---------+| variable_name | Value |+------------------------------+---------+| Query_cache_limit | 1048576 | | Query_cache_min_res_unit | 4096 | | Query_cache_size | 0 | | query_cache_strip_comments | OFF | | Query_cache_type | On | | query_cache_wlock_invalidate| OFF |+------------------------------+---------+

Query_cache_limit: Maximum number of bytes that can cache query results; (upper limit of single statement result set)

When using the SELECT query statement, you should try to avoid the application of the "SELECT *" Query method, and also need the WHERE clause or HAVING clause to optimize the query results, so as to make the query results as accurate as possible;

For those statements that have large query result sets, you should explicitly use the Sql_no_cache parameter explicitly in select to avoid query results such as caching and then removing the cache;

Query_cache_min_res_unit: The smallest allocation unit of memory block in query cache, can effectively avoid memory fragmentation;

Larger variable values can lead to wasted memory space;

Smaller variable values can reduce memory space waste, but will result in more frequent memory allocations and recycling operations, which in the long run inevitably result in memory fragmentation;

Query_cache_size: The total available space in memory for the query cache request;

The unit is a byte, and the set value must be an integer multiple of 1024;

Query_cache_strip_comments: Used to control whether to remove the comment portion of the SQL query statement and then save as the key part of the query cache;

The default value is "OFF", and if enabled, query statements in the Insert query cache are not part of the comment

Query_cache_type: switch that the cache function is turned on or off;

On: Enabled; only the query results of the "Sql_no_cache" parameter are not cached;

OFF: Disables caching;

DEMAND: Cache On Demand, default not cache, cache only the query result of "Sql_cache" parameter;

Query_cache_wlock_invalidate: If a connection session has a write lock applied to a table, is it still possible to query from the cache and return the query results;

The default value is "OFF", which means yes;

On, means no;

Server parameters related to query caching:

Qcache_free_blocks: Indicates how many blocks are currently remaining in the query cache, or if the value is large enough to indicate too much memory fragmentation in the query cache;

Qcache_free_memory: Memory space that is still idle in the query cache;

This status parameter allows you to evaluate the usage of the query cache in the current system;

If the amount of surplus is too small, and the remaining blocks are many, a large amount of memory space exists in the form of fragmentation;

If the remaining amount is too small, and the remaining blocks are not many, the allocated query cache memory space is just or slightly missing;

If the amount of surplus is large, the allocated query cache memory space too much, should be adjusted accordingly; Once the cache size is adjusted, the query results stored in it will be cleared immediately;

Qcache_hits: Indicates the number of hits in the query cache, and the larger the number, the better the cache effect;

Qcache_inserts: Indicates the number of misses that were then processed to add the query result to the query request;

The larger the value, the less desirable the query caching effect is.

You can reduce the number of such query requests by standardizing the SQL statements that write query requests;

Note: If the query cache does not have a query cache corresponding to the query, the increase of this value is also normal phenomenon;

Qcache_lowmem_prunes: This parameter records how many query requests are removed from the cache based on the LRU algorithm because of insufficient memory space, or if the value is too large, it means that the allocated memory space is too small;

Qcache_not_cached: The number of query requests that are not cached, depending on the setting of the Query_cache_type variable;

Qcache_queries_in_cache: The number of results of query requests cached in the current query cache;

Qcache_total_blocks: The total number of blocks allocated in the current query cache;

Calculation of query Cache hit ratio: qcache_hits/(Qcache_hits + qcache_inserts)

Note: If you write to the table, such as add and remove changes, MySQL automatically clears the cache entries associated with the table in the query cache, so the query requests related to this table must rebuild the cached content;

How the cache is optimized:

1. Write as much as possible (build a custom process, enable transactions), minimize the number of single write operations, 2. Cache space should not be set too large, if a large amount of cache failure at the same time, will make the execution of MySQL engine pressure surges, may cause the server to feign death; 3. If necessary, use Sql_ The parameters in the SELECT statement, such as cache and Sql_no_cache, to manually control the cache deposit or not; 4. Disabling caching may improve server performance for intensive write scenarios;

Let's do an example:

1. First make sure the Query_cache_type is open:

2. Set a size for the cache space

3. Check the data sheet for a particular data;

A miss in the status parameter occurs once because there is no cache, and after this query is completed, a message is cached;

4. Repeat the same query as the last query;

This hits segment hit once; you can check the number of hits multiple times;

MySQL's index

So what is the index for what, that is, what the index does: when there is too much data in MySQL, the retrieval speed should be high too, so in order to improve the retrieval speed, the concept of index is proposed. What is an index: a subset of the data in the specified table , which is to extract data from one or some of the fields in a table and save it as data organized with a specific data structure.

There are different types of indexes: fulltext,spacial,b+ Tree,hash; Because of the different storage engines, the types of indexes supported are different:

InnoDB Storage Engine Support: B + tree,hash;memory storage Engine: Supported HASH index displayed;

Here's a description of the different indexes:

B + Tree index: That is, sequential storage, where all the stored data is stored on the leaf node, and each leaf node has a sequential access pointer, which points to the adjacent leaf nodes;

B + Tree Index uses the scene:

1. Full key value matching: Exact Match of a value; Select ... where name= ' Xu Wenlong ' 2. Left prefix match: value is exactly part of the data start position: Select ... where Name like ' g% ' 3. Interval data Continuous number Value matching, usually used for between ... And ... Environment: Select ... where age between and 50;4. Discrete value matching of interval data; Usually used in the in-list environment or the or list environment; the exact value match; Select ... where Stuid in (1,3,7 5. Match the left column exactly, the range matches the other right column; Select ... where Stuid > and name like ' a% '; 6. For query requests that overwrite an index:

Non-applicable scenarios:

1. If the query condition is not accurate from the leftmost column, the index is invalid; the Stuid field is indexed, select ... where Name is like ' A% ' and stuid>10;2. If multiple columns are indexed, the index is invalid if a column in the index is skipped: on St Uid,name,age index, select ... where stuid>10 and age>20;3. If multiple columns are indexed and a column is scoped in a query statement, the right column can no longer use the index to refine the query; for Stuid,name, Age index, select ... where stuid>10 and Name like ' a% ';

Hash Index: The index based on the hash table; very suitable for the exact matching of the value of the query request;

Applicable scenario: Only the equivalent comparison query is supported: =,in (),<=> (NULL Safe Equal ());

Not applicable scenario: all non-exact values of the comparison query:

Advantages of the index:

1. Less need to scan the total amount of data, reduce the number of IO; 2. The foot faces the scanned data to be sorted again; 3. Avoid generating and using temporal tables; 4. Convert random io to sequential io;

General rules for defining indexes:

1. Select the data type to use for the index; the smaller the data type is suitable for indexing, the more simple the data type is suitable for indexing, the more likely it is to avoid the "null" value in the field, or the use of "0" or an empty string or an approved special value in place of the "null" value; 2. Select Select the type of the primary key, the preference of the integer type, the XXX data can be processed more quickly, and you can use the Auto_increment modifier to avoid duplication of data, try to avoid the use of character type, storage of character data need to consume more space, processing character data need to consume more CPU and memory resources source, the processing speed is relatively slow;

Definition strategy for high performance indexes:

1. In the WHERE clause to make independent use of a column to determine the best conditions, to avoid the direct parameter operation of the column; Select ... where age+2>10;2. Index of the leftmost prefix: The index should be built on one or more contiguous characters of the leftmost field The specific number of choices can be evaluated by "index selectivity"; Index selectivity: The ratio of non-repeating index values to the total number of records in the data table; 3. Multi-column index selection; Use the And operator in the WHERE condition clause to concatenate multiple query conditions; Avoid using the OR operator if you use the OR operator. This is a single-column index based on each of the columns; 4. Select the appropriate index definition order; the highest-selectivity column is on the leftmost side;

Explain statements

Analyze the index to see the application information of the index;

Usage:

EXPLAIN [Explain_type] SELECT select_optionsexplain_type:extended | Partitions

EXTENDED: Display extended information;

Partitions: for partitioned tables;

Here's an example:

Let's make an explanation of the fields:

ID: The number of each SELECT statement in the current query statement;

Select_type: Query type;

Simple query: Easy//by cross-linking to query;

Complex query:

Simple subquery (for subqueries in a WHERE clause): subquery

Used for subqueries in the FROM statement; DERIVED

The first query in a federated query: PRIMARY

Other queries in union queries: union

Temporary table queries generated when federated queries: Union RESULT

Table: The tables to which the current query statement is directed;

Type: association types, or access types, can also be understood as how MySQL queries rows in a table;

All: Full table scan; MySQL will traverse the entire table to find rows that can match; index: Full table scan, unlike all, the index type simply iterates through the index tree; Range: Index range scan, the scan of an index starts at a point, and returns a row that matches a range of domains; You can typically base a WHERE clause on a specified index, using the in list, between ... And ... or queries with "=", ">", "<", and so on; Ref: Scan with a non-unique index or a left prefix with a unique index; Returns a row that matches a single value; Eq_ref: Similar to ref, the difference is that a unique index is used, and for each index key value, There is only one record match in the table, either a single-table query or a multiple-table query, using a primary key or a unique key index as the association condition; Const,system: When MySQL optimizes the query part and transforms the optimization result into a constant, the const type is used; The system type is a special case of a const type that uses the system type when there is only one row in the table being queried, and null:mysql the query statement during the optimization process without accessing the table or index;

Possible_keys: In order to execute the query statement, MySQL may use which index to find records in the table;

If there is an index on the field involved in the query, the index is listed but not necessarily used by the query;

Key: Displays the actual index used by the MySQL database during the query process, or NULL if the query process does not use any indexes;

Key_len: Indicates the number of bytes that can be referenced in the index, and the length of the index used in the query can be computed by the column;

Note: The value shown in Key_len is usually the maximum possible length of the indexed field, not the actual length of use, so Key_len is calculated based on the length of the field specified when the table is defined, rather than in the table by retrieving the data;

Ref: The column or constant value that is referenced when the query is completed with the index displayed by the key field, or NULL if none is present;

Rows: Indicates that MySQL estimates the number of rows of tables that need to be read in the process of finding all records, based on table statistics and index selection;

Extra: Additional information, or extended information;

Using Where: Indicates that the MySQL server will re-tune the filter after the storage engine is retrieved; many of the where conditions involve columns in the index and when MySQL reads the index, it can be checked by the storage engine; using index: Use an overwrite index for retrieval Using temporary: A temporary table is used to hold the query result set during the query process, which is common in sorting or grouping queries, and the sorting operation cannot be done by using the index in Filesort:mysql, which is called "file sort"; using join Buffer: Emphasizes that the index is not used when getting the join condition, and the connection buffer is required to store the intermediate result. If this value is present, you need to add an index to prompt query performance based on the specific circumstances of the query; impossible where: If the value appears, it means that the WHERE clause does not find a qualifying row; Select tables optimized    Away: This value means that the query is made only by using an index, but the optimizer may give a feasible optimization scheme from the results of the aggregate function; using Sort-union (...)    Using Union (...)    Using intersect (...) Most of the above occurs when you decide to use more than one index in the implementation of a query; filtered: Select the ratio of the final query result after filtering again from the optional line;

Explain summary:

The 1.EXPLAIN statement does not tell about triggers, stored procedure-related information, or the impact of user-defined functions on queries; 2. The explain statement does not take any cache;3 into account. The explain statement does not show the optimized operation that was made by MySQL's own query execution ; 4. Some statistical information is not accurate, but rather an estimate; The 5.EXPLAIN statement can only parse the SELECT statement, other actions that imply the SELECT statement, and other non-select operations can be viewed only after the override is selected.

MYSQL/MARIADB Foundation (2)