MYSQL/MARIADB Foundation Second time

1
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 the various system tables in the MySQL database;
2. Include a user-defined function (UDF) in the query statement
3. Storage function;
4. User-defined variables;
5. Query requests initiated for temporary tables;
6. Queries containing column-level authorizations;
7. Internal key function of MySQL with indeterminate result value: 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;
1
Server parameters related to query caching:
Image.png
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;

注意：如果查询缓存中确实没有对应查询语句的查询缓存，此数值的增加也是正常现象；

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;

1. Write as much as possible (build a custom process; enable transactions) and minimize multiple single writes;
2. Cache space should not be set too large, if a large number of cache failure, it will cause the execution of MySQL engine pressure surges, may lead to the server suspended animation;
3. If necessary, You need to use the parameters in the SELECT statement such as Sql_cache and Sql_no_cache to manually control the cache deposit or not;
4. Disabling caching may improve server performance for intensive write scenarios;
Next Example:
1. First, make sure that Query_cache_type is turned on:
Image.png
2. Set a size for the cache space
Image.png
image.png
image.png
3. Query the data table for a data,
Image.png
//is not hit in the status parameter one time because there is no cache, and after this query is completed, a message is cached;
Image.png
4. Repeat the same query as the last query;
/ /This hits segment hit once; you can check the number of hits multiple times;
Image.png
image.png
1
MySQL index
So what's the index for? What is the function of the index: 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 a specified table, i.e. extracting data from one or some of the fields in a table, Save as data that is organized with a specific data structure. The
indexes have different types: fulltext,spacial,b+ Tree,hash; Because of the different storage engines, the types of indexes supported are different:
1
2
InnoDB storage Engine support: B + tree,hash;
Memory storage Engine: The supported hash index displayed;

1. Full key match: Exact Match of a certain value;
Select ... where name= ' Xu Wenlong '
2. Left prefix match: The value is exactly part of the data starting position:
Select ... where Name like ' g% '
3. Continuous numerical matching of interval data, usually used for between ... And ... In the 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;
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 the index:

1. If the query condition is not accurate from the leftmost column, the index is invalid;
Index The Stuid field, select ... where Name like ' A% ' and stuid>10;
2. If multiple columns are indexed, the index is invalid if you skip a column in the index:
Index Stuid,name,age, select ... where stuid>10 and age>20;
3. If multiple columns are indexed and a column is scoped in the query statement, the right column can no longer use the index to refine the query;
Index Stuid,name,age, select ... where stuid>10 and Name like ' a% ';
1
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 creating and using temporary tables;
4. Convert the random io into sequential io;
General rules for defining indexes:
1. Select the data type to use for indexing;
The smaller the data type, the more suitable the index;
The simpler the data type is, the more suitable the index;
Try to avoid a "null" value in the field, or "null" if you must use a null value, "0" or an empty string or an approved special value;
2. Select the type of primary key;
Priority selection of integral type;
The XXX data can be processed more quickly, and the auto_increment modifier can be used to avoid duplicate data;
Try to avoid using character type;
Storing character data requires more space, processing character data consumes more CPU and memory resources, and processing speed is 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. Leftmost prefix index: The index should be constructed in one or more consecutive characters on the leftmost side of the field; The 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. Selection of multi-column indexes;
The WHERE Condition clause uses the AND operator to concatenate multiple query conditions, and avoid using the OR operator as much as possible;
If you use the OR operator, you should make a single-column index 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_options

explain_type:extended | Partitions
EXTENDED: Display extended information;
Partitions: for partitioned tables;
Here's an example:
Image.png
Let's make an explanation of the fields:
ID: The number of each SELECT statement in the current query statement;

Select_type: Query type;
1
Simple query: Easy//by cross-linking to query;
Image.png
1
Complex query:
Simple subquery (for subqueries in a WHERE clause): subquery
Image.png

Used for subqueries in the FROM statement; DERIVED
Image.png

The first query in a federated query: PRIMARY
Other queries in union queries: union
Image.png
Temporary table queries generated when federated queries: Union RESULT
Image.png
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, and MySQL will traverse the entire table to find rows that can match;
Index: Full table scan, unlike all, the index type simply traverses the index tree;
Range: Index range scan, the scan of an index starts at a certain point, and returns a row that matches a certain domain value;
You can typically base a WHERE clause on a specified index, using the in list, between ... And ..., or with a "=", ">", "<" and other inquiries;
Ref: Scanning with a non-unique index or using a unique index for the left prefix; Returns a row that matches a single value;
Eq_ref: Similar to ref, the difference is the use of a unique index, for each index key value, there is only one record match in the table, whether it is a single-table query or a multi-table query, the primary key or unique key index is used 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 to query;
Null:mysql the query statement during the optimization process, but does not have to access the table or index;
Image.png
Image.png
Image.png
Image.png
Image.png
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 inspected by the storage engine;
Using index: Use an overlay index for retrieval;
Using temporary: A temporary table is used to hold the query result set during the query process, and it is common to sort or group queries;
A sort operation that cannot be done with an index in a using Filesort:mysql is called a "file sort";
Using join buffer: Emphasizes that the index is not used when getting join conditions, and that a connection buffer is required to store intermediate results. If this value appears, you need to add an index based on the specific circumstances of the query to prompt query performance;
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 only done 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 during the implementation of the query;
Filtered: Select the ratio of the final query result after filtering again from the optional line;
Image.png
Image.png
Image.png

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.EXPLAIN statement does not consider any cache;
The 3.EXPLAIN statement does not display the optimized operation that was made by MySQL's own query execution;
4. Some of the statistical information is not accurate, but the result of estimation;
The 5.EXPLAIN statement can parse only the SELECT statement, other actions that imply the SELECT statement, and the other non-select operations can be viewed only after the override is select to view the execution plan

MYSQL/MARIADB Foundation Second time