In-depth understanding of InnoDB (8) – single table access

1. Access method

MySQL refers to the way queries are executed as access methods or access types. Access methods fall into two broad categories

1. A full table scan
2. The index

The subdivision can be divided into the following categories (for the convenience of illustration, first build a table)

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
Copy the code

1.1 const

SELECT * FROM single_table WHERE id = 1438;

SELECT * FROM single_table WHERE key2 = 3841;
Copy the code

For example, in the above two SQL statements, the condition is that the primary key or unique secondary index column is compared to the constant.

• The first SQL statement is executed by matching the primary key value directly in the clustered index of the primary key
• The second SQL statement, in the corresponding unique secondary index, according to the query index column for a match, find the primary key ID, and then back to the table to find all the data

Due to the primary key, and the only secondary indexes, their index columns are unique, so the search speed is very fast, so the access method is const, which means constant level query speed. (note: when the query conditions of indexed column is empty, access method can not calculate const, because there may be multiple records for empty)

1.2 ref

The access mode of ref is similar to that of the unique secondary index, which is matched on the secondary index first and then queried back to the table after finding the primary key ID. However, because the value of a normal secondary index column is not unique, it is possible to query multiple records and perform multiple returns to the table.

SELECT * FROM single_table WHERE key1 = 'abc';
Copy the code

For example, in the SQL statement above, because key1 is a common secondary index, there may be multiple records key1=’ ABC ‘, so the query needs to return multiple records to the table

1.3 ref_or_null

This is similar to ref, but with more matching for null values

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;
Copy the code

Select * from primary key where key1 = ‘ABC’ and key1 = null; select * from primary key where key1 = ‘ABC’ and key1 = null

1.4 the range

The range access method means to query according to the range. The previous query is to match a single constant value, while the range is to query a range.

SELECT * FROM single_table WHERE key2 IN (1438.6328) OR (key2 > = 38 AND key2 < = 79);
Copy the code

Select * from primary key where id = 79>=key2>=38; select * from primary key where id = 79>=key2>=38

1.5 the index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';
Copy the code

The condition key_part2 = ‘ABC’ does not satisfy the leftmost match, but key_part1, key_part2, and key_part3 belong to a joint index idx_KEY_part, so the query is in the leaves of the idx_KEY_part index. Match key_part2 = ‘ABC’ one by one and return the index column directly, without returning the table.

Index means to match the query directly in the index.

1.6 all

All is a full table scan

2. Precautions

2.1 Generally, only a single secondary index can be used to perform queries

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;
Copy the code

The query optimizer will select the B+ tree of the index where key1 = ‘ABC’ and key2 > 1000 according to single_table statistics.

For example, in the above example, the optimizer selects key1 = ‘ABC’, then the query will be returned to the table if the primary key meets key1 = ‘ABC’, and the filter will be based on key2 > 1000

2.2 Define the range used by the range access method

2.2.1 All search criteria can use an index

Such as:

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;
Copy the code

Because the conditions are all range queries from the same index, it is only necessary to perform the collection operation on the corresponding range

2.2.2 Some search criteria cannot use the index

SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
Copy the code

Select * from key2 where key2 > 100 = ‘ABC’; select * from key2 where key2 = ‘ABC’; Key2 > 100 AND common_field = ‘ABC’

SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';
Copy the code

SQL > select * from key2 where common_field = ‘ABC’; SQL > select * from key2 where common_field = ‘ABC

2.2.3 Find the range matching interval under complex search conditions

SELECT * FROM single_table WHERE 
        (key1 > 'xyz' AND key2 = 748 ) OR
        (key1 < 'abc' AND key1 > 'lmn') OR
        (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc'));Copy the code

Key1 and KEY2 are mainly used, so they are searched separately

• For key1

1. Replace the conditions that are not key1 with true

(key1 > 'xyz' AND TRUE ) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))
Copy the code

2. Replace the condition that is always TRUE or FALSE

Because key1 < ‘ABC’ AND key1 > ‘LMN’ are always FALSE, the above search criteria can be written as follows:

(key1 > 'xyz') OR (key1 > 'zzz')
Copy the code

3. Merge scope

key1 > xyz
Copy the code

• For key2

1. Replace the conditions that are not key1 with true

(TRUE AND key2 = 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 < 8000 OR TRUE))
Copy the code

2. Once again, we can conclude that the final condition is true, that is, all key2 indexes need to be searched

2.3 Index Merging

The first caveat mentioned earlier is that in general you can only execute a query with a single secondary index, but in special cases you can use multiple secondary indexes in a single query. Therefore, the index merge method is called index merge. There are three types of index merge algorithms.

2.3.1 Intersection computes merger

Intersection. Intersection means to find the Intersection of the results of multiple secondary indexes

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
Copy the code

Implementation process

1. Key1 = ‘a’
2. Key3 = ‘b’
3. Because secondary indexes find the record format is the index column plus the primary key, so they can directly find an intersection of the primary key and then back to the table

Why not just look up the secondary index key1, and filter out the conditions that do not meet KEY3 on the way back to the table?

Because if the number of entries found in KEY1 is very large, then it is necessary to perform many times back to the table, and this type of back table is random I/O, very time-consuming, and choose two secondary index lookup, belongs to the sequential I/O, has better performance.

Use Intersection:

1. Secondary indexes can only be equivalent matches

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';
Copy the code

• The first SQL statement can be used normally
• The second uses a range query
• Article 3 does not use all columns of the federated index

2. Primary key columns can be range matched

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';
Copy the code

Because secondary indexes, the index columns is already sorted, and if the index column all the same record, so will be ordered by according to the primary key, and USES the sorted the primary key to evaluate intersection, is very simple, because the two have sorted the primary key of the sequence, for their intersection time complexity is o (n).

This also answers the first case, why it can only be an equivalent match, because only in this case can the records of the same index column be sorted in the order of the primary key.

The above two cases are only necessary to use Intersection. Intersection index merging is used by the optimizer only when too many records are retrieved from a secondary index based solely on the search criteria, resulting in too much overhead to return to the table, and when the number of records required to return to the table is greatly reduced.

2.3.2 Union merger

Union stands for Union and is used when search terms using different indexes are joined by OR. When using Union:

1. Secondary indexes can only be equivalent to Intersection.

2. Primary key columns can be range matching (like Intersection)

3. Use Intersection to index the merged search criteria

Such as:

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');
Copy the code

1. Press key1 = ‘a’ AND key3 = ‘b’ to use Intersection to obtain an Intersection

Key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’

3. Find the union of the two sets above. And then go back to the table

The optimizer uses Union index merges only when the number of records retrieved from a secondary index based solely on the search criteria is small and access via Union index merges is less costly than a full table scan.

2.3.3 merge Sort – the Union

The Union condition is so strict that only equivalent matching can be used.

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
Copy the code

This is not available for such a range query, and sort-Union index merging is possible for the above SQL.

Key1 < ‘a’ to find all records in order 2. Key3 > ‘z’ to find all records in order 3. Since the primary keys of both sets are sorted, just press Union to do that

Why is there no sort-intersection? Because Union is used when the number of records retrieved from a secondary index based solely on the search criteria is small, the sorting performance penalty is small. Intersection, on the other hand, is used to query a large number of records, which incurs a significant performance loss

2.3.4 Precautions for Joint Indexes

Union index replaces Intersection index merge

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
Copy the code

SQL statements like this, which need to use two separate indexes, can be used directly as a joint index, which can not do b+ tree, but also can not do set operations.

ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);
Copy the code