MySQL optimization (7)

7.4.5how does MySQL use index indexes to quickly find records with specific values. If no index exists, MySQL must read the entire table from the first row of records to retrieve records. The larger the table, the larger the resource consumption. If there is an index on the field, MySQL will be able to quickly determine the location from which the data file to start searching for records, without having to look for all the numbers

7.4.5 how does MySQL use index indexes to quickly find records with specific values. If no index exists, MySQL must read the entire table from the first row of records to retrieve records. The larger the table, the larger the resource consumption. If there is an index on the field, MySQL will be able to quickly determine the location from which the data file to start searching for records, without having to look for all the numbers

7.4.5 how does MySQL use indexes?

The index is used to quickly find records with specific values. If no index exists, MySQL must read the entire table from the first row of records to retrieve records. The larger the table, the larger the resource consumption. If the field has an index, MySQL can quickly determine the location where the data file is located to search for records without searching all the data. If the table contains 1000 records, this is at least 100 times faster than reading data sequentially. Note: If you need to access almost all 1000 records, sequential reading will be faster, because this will minimize the number of disk searches.

Most MySQL indexes (PRIMARY KEY,UNIQUE,INDEXAndFULLTEXTIs stored in the B-tree mode. Only space fields are stored in the R tree,MEMORY(HEAP) Tables support hash indexes.

By default, strings are automatically compressed with spaces in the prefix and suffix. For details, see "14.2.5CREATE INDEXSyntax ".

Generally, indexes can be used in the following situations. Hash index (usedMEMORYTable) is unique.

• Search for matching as soon as possibleWHEREClause records.
• Exclude records based on conditions. If multiple indexes are available, MySQL usually selects the index with the least records.
• Query records from other tables during table join queries.
• To specify the index FieldKey_colFind itsMIN()OrMAX()Value. The optimizer will check the index
  Key_colCheck whether other indexes are used before the field.WHERE key_part_# = constantClause. In this case,
  MySQL willMIN()OrMAX()The expression performs an index search separately and replaces it with a constant. When all expressions are replaced with constants, the query returns immediately. As follows:

  SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;

• Sort or group a table. When grouping or sorting an available leftmost prefix index (for exampleORDER
BY key_part1, key_part2). If all the index parts followDESCSort the index in reverse order. For details, see "7.2.9
  How MySQL OptimizesORDER BY".
• In some cases, the query can be optimized so that results can be directly obtained without computing data. When the query uses a numeric field in the table and the field is the leftmost part of the index, the results may be obtained quickly from the index tree:
  

  SELECT key_part3 FROM tbl_name WHERE key_part1=1 

For exampleSELECTStatement:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; 

If col1And col2 If there is a multi-field index on it, the corresponding records can be obtained directly. Ifcol1And col2If there are independent indexes, the optimizer will first find the index with the most limit, and then decide which index to use based on which index can find fewer records.

If there is a multi-field index in the table, any leftmost prefix of the index can be used by the optimizer to retrieve records. For example(col1, col2, col3)There is an index on it, then it is combined by field(col1),(col1, col2), And(col1, col2,
col3)Indexes are used for search.

MySQL cannot use partial indexes in non-leftmost prefix indexes. Assume that the followingSELECTStatement:

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If(col1, col2, col3)There is an index on, and only the first query uses the index. Although the second and third fields contain index fields(col2)And(col2, col3)Not an index(col1, col2, col3)The leftmost prefix.
When you do=,>,>=,<,<=, OrBETWEENIndexes are also used for comparison.
MySQL is runningLIKEIndexes may also be used for comparison. IfLIKEIf the parameter is a fixed string starting with a non-wildcard character. The followingSELECTThe statement uses the index:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

In the first query, only'Patrick' <= key_col < 'Patricl'Records are retrieved. In the second query, only search'Pat' <= key_col < 'Pau'.
BelowSELECTThe statement does not use an index:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In the first statement,LIKEThe parameter starts with a wildcard. In the second statement,LIKEIs not a constant value.
MySQL 4.0 and later will create an additionalLIKEOptimized. If you use... LIKE '%string%'AndStringMySQL uses more than 3 CharactersTurbo Boyer-MooreAlgorithm to initialize the mode and use this mode to accelerate the search.
Usecol_name IS NULLThe index will also be used during the search. If the fieldCol_nameIf there is an index on it.
AnyWHEREClause does not span allANDThe index of the level clause is not used to optimize the query. In other words, if you want to enable an indexANDThe prefix field of the index must be used in each clause.
BelowWHEREClause index:

... WHEREIndex_part1= 1 ANDIndex_part2= 2 ANDOther_column= 3
/*Index= 1 ORIndex= 2 */
... WHEREIndex= 1 or a = 10 ANDIndex= 2
/* Optimized like"Index_part1= 'Hello '"*/
... WHEREIndex_part1= 'Hello' ANDIndex_part3= 5
/* Use IndexesIndex1, But not usedIndex2OrIndex3*/
... WHEREIndex1= 1 ANDIndex2= 2 ORIndex1= 3 ANDIndex3= 3;

BelowWHEREClauseNoUse index:

/* UselessIndex_part1*/
... WHEREIndex_part2= 1 ANDIndex_part3= 2
/* All AND parts are not indexed */
... WHEREIndex= 1 or a = 10
/* The index does not span all fields */
... WHEREIndex_part1= 1 ORIndex_part2= 10

Sometimes MySQL does not use any available indexes. One case is that the optimizer thinks that if an index is used, it will need to retrieve a larger part of the table records (at this time, scanning the table may be faster, because this requires fewer searches ). However, if a queryLIMITMySQL will certainly use indexes to retrieve only some records, because it can retrieve fewer records faster and return them to the results.
The following are some different features of hash indexes:

• They are only used=Or<=>Comparison (but not very fast ).
• The optimizer cannot use Hash indexes for acceleration.ORDER BYOperation (this index cannot be used to search for the next record in order ).
• MySQL cannot determine the number of records between two values (this is determined by the range optimization program ). This isMyISAMThe table type is changed to hash IndexMEMORYType may affect some queries.
• Only the full index key can be used to retrieve records (if the index is a B-tree index, some indexes with any prefix can also be used to retrieve records ).
  

7.4.6 MyISAMIndex Cache

To minimize disk I/O,MyISAMThe storage engine uses a policy used by many database systems. It uses a mechanism to save the most frequently accessed tables in the memory block:

• For an index block, it maintains an index cache structure. This struct contains many buffer blocks of the most frequently used index blocks.
  
• MySQL does not use a specific cache for data blocks. It relies on the local file system cache of the operating system.
  

This chapter first describes MyISAMBasic operations on index cache. Then we will discuss the improvements made in MySQL 4.1, which improves the index cache performance and can better control cache operations:

• Threads no longer access the index cache serially. Multiple Threads can access the index cache in parallel.
  
• You can set multiple index caches and specify the data table indexes to specific caches.
  

Index Cache MechanismISAMTables also apply. However, this effect is weakening. Since MySQL 3.23MyISAMAfter the table type is introduced,ISAMIt is no longer recommended. MySQL 4.1 continues this trend,ISAMThe type is disabled by default.
You can use System Variableskey_buffer_sizeTo control the size of the index cache block. If the value is 0, no cache is used. When this value is smaller than or equal to the minimum number (8) of block buffering, cache is not used.
When the index cache cannot be operated, the index file is accessed only through the local file system buffer provided by the operating system (in other words, the access policy used in the table index block is consistent with that in the data block ).
An index block is stored inMyISAMThe index file is a unit of continuous access. Generally, the size of the index block is the same as that of the B-tree index node (the index is represented by the B-tree structure in the disk. At the bottom of the tree, the leaf node is not a leaf node ).
The size of all blocks in the index cache structure is the same. This value may be equal to, greater than, or smaller than the index block size of the table. These two values are usually different.
When you must access the index block from any table, the server first checks whether there are available buffer blocks in the index cache. If yes, the server accesses the cached data instead of the disk. That is to say, it directly accesses the cache, rather than accessing the disk. Otherwise, the server selects one or more cache buffer blocks that contain different index blocks and replaces the contents with the copy of the index blocks of the Request table. Once the new index block is in the cache, the index data can be accessed.
When the block content to be replaced is modified, the block is considered as 'dirty. Before replacement, its content must be refreshed to the index it points.
Generally, the server follows the LRU (least recently used) Policy: When you want to replace a block, it selects the index block that is least recently used. To make the selection easier, the index cache module maintains a package containing all the queue (LRU chain) that uses the block ). When a block is accessed, it is placed at the end of the queue. When a block is to be replaced, the block at the beginning of the queue is the least recently used. It is the first candidate to delete objects.

7.4.6.1 shared access index Cache

Before MySQL 4.1, access to the index cache is serialized: two threads cannot concurrently access the index cache buffer. The server can only process a request to access the index block after processing the previous request. As a result, the index block required by the new request is not in any index cache ring Chong block, because other threads buffer the index block for updates.

Starting from MySQL 4.1.0, the server supports shared access to index cache:

• No buffer being updated can be accessed by multiple threads.
• When the buffer is being updated, the thread that needs to use this buffer can only wait until the update is complete.
• Multiple Threads can initialize requests that need to replace the cache block, as long as they do not interfere with other threads (that is, they request different index blocks, so different cache blocks are replaced ).
  

The shared access to the index cache significantly improves the server throughput.

7.4.6.2 multi-index Cache

Shared access index cache improves performance, but cannot completely eliminate conflicts between threads. They are still competing to control the structure of the access index cache buffer. To further reduce index cache access conflicts, MySQL 4.1.1 provides multiple index cache features. This can specify different table indexes to different index caches.
When multiple index caches exist, the server processes the specifiedMyISAMWhen querying a table, you must know which one to use. By default, allMyISAMAll table indexes are cached in the default index cache. You can useCACHE INDEXStatement.
As shown in the following statementt1,t2Andt3Cache to namehot_cacheCache:

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

Note:If the server compilation supports StorageISAMStorage engine, soISAMThe index cache mechanism is also used for tables. However,ISAMTable indexes can only be cached by default but cannot be customized.
CACHE INDEXThe index cache used in the statement is based onSET GLOBALThe value set by the statement parameter or the value specified by the server startup parameter is as follows:

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

To delete the index cache, you only need to set its size to 0:

mysql> SET GLOBAL keycache1.key_buffer_size=0;

The index cache variable is a struct variable consisting of the name and component. For examplekeycache1.key_buffer_size,keycache1Is the cache name,key_buffer_sizeIs a cache component. For details, see "10.4.1 Structured System Variables", which describes the syntax used to construct index cache System Variables.
By default, table indexes are specified to the master (default) index cache when the server is started. When an index cache is deleted, all indexes specified in the cache are redirected to the default index cache again.
For a busy system, we recommend the following three policies to use the index cache:

• Hot cache occupies 20% of the total cache space. Used to search tables that are heavy but rarely updated.
  
• The cold cache occupies 20% of the total cache space. Tables for medium-intensity updates, such as temporary tables.
  
• The cold cache occupies 60% of the total cache space. It is used as the default cache for all other tables.
  

One advantage of using three caches is that accessing a cache structure does not block access to other caches. Queries accessing a table index do not compete with queries specified to other caches. Performance improvement is also manifested in the following reasons:

• Hot cache is only used to retrieve records, so its content does not need to change. Therefore, no matter when an index block needs to be introduced from the disk, the content of the cache block to be replaced must be refreshed first.
  
• If you do not need to scan all index queries after the index is directed to the hot cache, the index blocks corresponding to non-leaf nodes in Tree B are likely to remain in the cache.
  
• Frequent update operations must be performed on the temporary table. If the node to be updated is already in the cache, it does not need to be read from the disk. When the index size of a temporary table is the same as that of the cold cache, the probability that a node already exists in the cache when it needs to be updated is quite high.
  

Optimize MySQL (6) optimization to MySQL (8 )›