MySQL series 9 MySQL query cache and index

Tutorial Series

MySQL series: Basic concepts of MySQL relational database
MariaDB-server installation of MySQL series
MySQL Series II Multi-Instance Configuration
MySQL Series 3 Basics
MySQL Series 4 SQL Syntax
MySQL series five views, stored functions, stored procedures, triggers
MySQL series 6 users and authorization
MySQL Series 7 MySQL Storage Engine
MySQL Series 8 MySQL Server Variables
MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control
MySQL Series 11 Logging
MySQL Series 12 Backup and Recovery
MySQL Series 13 MySQL Replication
MySQL Series 14 MySQL High Availability Implementation
MySQL series 15 MySQL common configuration and performance stress test

1. MySQL Architecture

1. Connectors
2. Connection pool, security authentication, thread pool, connection limit, memory check, cache
3. SQL interface DML, DDL
4. SQL parser, which checks the permissions of SQL statements and parses them into binary programs
5. Optimizer, optimize access paths
6. cache,buffer
7. Storage engine innodb
8. File System
9. log

Query Cache

1. SQL Statements

2. Query Cache

3. Parser

4. Parse Tree

5. Preprocessing

6. Find the best query path

7. Query optimization SQL statement

8. Execution plan

9. API calls to storage engines

10. Call data and return results

Cache the result set and SQL statement of SELECT operation or preprocessed query. When there is a new SELECT statement or preprocessed query statement request, first query the cache to determine whether there is an available record set. The judgment criteria are: whether it is exactly the same as the cached SQL statement, case-sensitive.

No need to parse and execute SQL statements. Of course, syntax parsing must be done first. Get query results directly from Query Cache to improve query performance.

The judgment rules of query cache are not smart enough, which increases the threshold for using query cache and reduces its efficiency. The use of query cache will increase the cost of checking and cleaning the record sets in query cache.

Which queries may not be cached:

• The SQL_NO_CACHE parameter is added to the query statement;
• The query statement contains functions that obtain values, including custom functions, such as NOW(), CURDATE(), GET_LOCK(), RAND(), CONVERT_TZ(), etc.
• Query the system database: mysql, information_schema Use SESSION-level variables or local variables in stored procedures in query statements;
• The query statement uses LOCK IN SHARE MODE and FOR UPDATE statements, and the query statement is similar to SELECT ...INTO statements for exporting data;
• Query operations on temporary tables; query statements with warning information; query statements that do not involve any tables or views; query statements for which a user has only column-level permissions;
• When the transaction isolation level is Serializable, all query statements cannot be cached.

Query cache-related server variables:

• query_cache_min_res_unit: The minimum allocation unit of memory blocks in the query cache. The default value is 4k. A smaller value will reduce waste but will lead to more frequent memory allocation operations. A larger value will lead to waste, excessive fragmentation, and insufficient memory.
• query_cache_limit: The maximum value that can be cached for a single query result. The default value is 1M. For statements whose query results are too large to be cached, it is recommended to use SQL_NO_CACHE.
• query_cache_size: the total available memory space for query cache; unit: bytes, must be an integer multiple of 1024, minimum value: 40KB, below which an alarm will be triggered;
• query_cache_wlock_invalidate: If a table is locked by other sessions, can the query cache still return results? The default value is OFF, which means that data can continue to be returned from the cache even if the table is locked by other sessions; ON means that it is not allowed.
• query_cache_type: Whether to enable the cache function, the value is ON, OFF, DEMAND, the default value is ON
  - When the value is OFF or 0, the query cache function is disabled;
  - When the value is ON or 1, the query cache function is turned on. The SELECT result will be cached if it meets the cache conditions. Otherwise, it will not be cached. If SQL_NO_CACHE is explicitly specified, it will not be cached.
  - When the value is DEMAND or 2, query caching is performed on demand, and only SELECT statements that explicitly specify SQL_CACHE are cached; others are not cached.

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+

Optimize query cache:

Query cache-related status variables:

• Qcache_free_blocks: The number of memory blocks in the Query Cache that are in idle state;
• Qcache_free_memory: The total amount of Query Cache memory in idle state;
• Qcache_hits: Query Cache hit count;
• Qcache_inserts: The number of times a new query cache is inserted into the query cache, that is, the number of times there is no hit;
• Qcache_lowmem_prunes: When the Query Cache memory capacity is insufficient, the number of times the old Query Cache needs to be deleted to make room for new Cache objects;
• Qcache_not_cached: The number of SQL statements that are not cached, including SQL statements that cannot be cached and SQL statements that will not be cached due to the query_cache_type setting;
• Qcache_queries_in_cache: the number of SQLs in the Query Cache;
• Qcache_total_blocks: Total blocks in the Query Cache.

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33536824 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+

Hit rate and memory usage estimation:

• The minimum allocation unit of memory blocks in the query cache query_cache_min_res_unit: (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
• Query cache hit rate: Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
• Query cache memory usage: (query_cache_size – qcache_free_memory) / query_cache_size * 100%

3. Index

​ Index is a special data structure: it defines the fields used as search conditions during search, and the index is implemented in the storage engine.

Indexes can reduce the amount of data that the service needs to scan, reducing the number of IOs. Indexes can help servers avoid sorting and using temporary tables. Indexes can help convert random I/O to sequential I/O.
But it takes up extra space and affects the insertion speed

1. Index type:

• B+Tree index: sequential storage, the distance from each leaf node to the root is the same, left prefix index, suitable for querying range data;
  - Query types suitable for using B-Tree indexes
  - Full value match
  - Matches the leftmost prefix
  - Match range value
  - Exact match on one column and range match on another column (composite index)
  - Queries that access only indexes
  - Query types that are not suitable for using B-tree indexes
  - Not starting from the leftmost column
  - Cannot skip columns in the index
  - If a column in the query is a range query, the column on the right can no longer use the index to optimize the query
• Hash index: Based on the hash table, it builds an index of key-value pairs. It is particularly suitable for index columns in the exact match index. It only supports equality comparison queries (IN, =, <>). It is not suitable for sequential queries and does not support fuzzy matching. Only the Memory storage engine supports explicit Hash indexes.

• Spatial index (R-Tree): Only MyISAM supports spatial index

• Full text indexing: Find keywords in text

2. High-performance indexing strategy:

• Use columns independently and avoid involving them in calculations as much as possible.
• Use left prefix index: the number of characters on the left side of the field to be indexed is evaluated by index selectivity; index selectivity: the ratio of non-repeated index values ​​to the total number of records in the data table
• Multi-column indexes: It is more appropriate to use multi-column indexes for AND operations rather than creating separate indexes for each column.
• Choose the appropriate index column order: When there is no sorting or grouping, put the columns with the highest selectivity on the left.

3. Index optimization suggestions

• As long as the column contains NULL values, it is best not to set an index in this case. If a composite index has NULL values, this column will not be used when the index is used.
• Try to use short indexes and, if possible, specify a prefix length.
• For columns that are often used in the where clause, it is best to set an index
• For where or order by clauses with multiple columns, a composite index should be created
• For like statements, if they start with % or '-', the index will not be used, but if they end with %, the index will be used.
• Try not to perform operations (function operations and expression operations) on columns.
• Try not to use not in and <> operations
• When joining multiple tables, try to use a small table to drive a large table, that is, a small table joins a large table.
• Use limit when paging at the 10 million level
• For frequently used queries, you can enable caching
• In most cases, joins are much more efficient than subqueries.

4. Creating and deleting indexes

Create Index

CREATE INDEX index_name ON tbl_name (index_col_name,...);

MariaDB [hellodb]> CREATE INDEX index_name ON students(name); #Create a simple index
MariaDB [hellodb]> CREATE INDEX index_name_age ON students(name,age); #Create a composite index

View Index

SHOW INDEXES FROM [db_name.]tbl_name;

MariaDB [hellodb]> SHOW INDEX FROM students\G

Deleting an Index

DROP INDEX index_name ON tbl_name;

MariaDB [hellodb]> DROP INDEX index_name ON students;

Optimize tablespace

MariaDB [hellodb]> OPTIMIZE TABLE students;

View index usage

Enable logging of index usage: SET GLOBAL userstat=1;

View index usage: SHOW INDEX_STATISTICS;

We can count infrequently used indexes for optimization

4. EXPLAIN command

Analyze the effectiveness of the index through EXPLAIN: EXPLAIN SELECT clause, obtain query execution plan information, and view how the query optimizer executes the query

MariaDB [hellodb]> EXPLAIN SELECT name FROM students WHERE name = 'Lin Daiyu'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ref
possible_keys: index_name_age
          key: index_name_age
      key_len: 152
          ref: const
         rows: 1
        Extra: Using where; Using index

• id: the number of each SELECT statement in the current query statement; there are three types of complex queries: simple subqueries, subqueries used in the FROM clause, and union queries (UNION, note: an additional anonymous temporary table will appear in the analysis results of UNION queries)
• select_type:
  - SIMPLE: Simple query
  - SUBQUERY: Simple subquery
  - PRIMARY: the outermost SELECT
  - DERIVED: used for subqueries in FROM
  - UNION: The SELECT statement after the first UNION statement
  - UNION RESULT: anonymous temporary table
• table: the table to which the SELECT statement is associated
• type: association type or access type, which is how MySQL decides to query rows in the table. The following order, performance from low to high
  - ALL: full table scan
  - index: Perform a full table scan according to the order of the index; if "Using index" appears in the Extra column, it means that a covering index is used instead of a full table scan.
  - range: A range scan is performed based on an index with a limited range; the scan position starts at one point in the index and ends at another point
  - ref: Returns all rows in the table that match a single value according to the index
  - eq_ref: returns only one row, but requires an additional comparison with a reference value
  - const, system: return a single row directly
• possible_keys: indexes that may be used for query
• key: the index used in the query
• key_len: The number of bytes used in the index
• ref: The column or constant value used to complete the query using the index represented by the key field
• rows: The number of rows that MySQL estimates it needs to read to find all target rows
• Extra: Additional information
  - Using index: MySQL will use a covering index to avoid accessing the table
  - Using where: The MySQL server will perform another filter after the storage engine retrieves it.
  - Using temporary: MySQL uses a temporary table when sorting the results
  - Using filesort: Use an external index to sort the results

5. SQL statement performance optimization

1. When querying, avoid using * if possible and try to write the full field name
2. In most cases, joins are much more efficient than subqueries.
3. When joining multiple tables, try to use a small table to drive a large table, that is, a small table joins a large table.
4. Use limit when paging at the 10 million level
5. For frequently used queries, you can enable caching
6. Use explain and profile to analyze query statements
7. View the slow query log to find out the optimization of SQL statements with long execution time

This concludes this article on MySQL Series 9: MySQL query cache and index. For more information on MySQL query cache and index, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!