First, early preparation

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

  • We created a cluster index and four secondary indexes for this single_table table, respectively:

    • foridColumn created by the cluster index.
    • forkey1Established by the columnidx_key1Secondary index.
    • forkey2Established by the columnidx_key2Secondary index, and this index is the only secondary index.
    • forkey3Established by the columnidx_key3Secondary index.
    • forkey_part1,key_part2,key_part3Established by the columnidx_key_partSecondary index, which is also a federated index.

  • Then we need to insert 10000 rows for the table, and insert random values for all columns except the ID column

Second, the concept of access methods

  • There are two ways to execute a query:

    • Query using full table scan

      • This makes sense: you scan every row of the table and add the records that match the search criteria to the result set. Any query can be executed this way, but it’s also the dumbest way to execute it.

    • Use indexes for queries

      • If the search criteria in the query statement can be used for an index, then using the index directly to execute the query may speed up query execution

  • A query may be executed using multiple access methods, and the final query result may be the same, but the execution time may not be the same

1, the const

  • Use the primary key index directly
SELECT * FROM single_table WHERE id = 1438;
Copy the code
  • With secondary indexes, a back table may be required
SELECT * FROM single_table WHERE key2 = 3841;
Copy the code

  • The access method to locate a record by primary key or unique secondary index column is defined as:const, which meansConstant level, the cost is negligible. But this kind ofconstThe access method is only valid if the primary key column or unique secondary index column is equivalent to a constant. If the primary key or unique secondary index column is composed of multiple columns, each column in the index must be equivalent to a constantconstThe access method is valid (this is because only a single record can be located if all columns in the index are equivalent to each other).

2, ref

  • Sometimes we compare a common secondary index column to a constant, like this:
SELECT * FROM single_table WHERE key1 = 'abc';
Copy the code
  • We can also use the secondary index to find the corresponding record firstidValue, and then go back to the table to find the complete user record in the clustered index. Since ordinary secondary indexes do not restrict the uniqueness of index column values,So it’s possible to find multiple corresponding recordsThat is, the cost of using a secondary index to perform a query depends on the equivalent number of matched secondary index records

3, ref_or_null

  • Set the value of this column toNULLAs shown in the following query:
SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;
Copy the code
  • When the query is executed using a secondary index rather than a full table scan, the access method used for this type of query is calledref_or_nulltheref_or_nullThe execution process of the access method is as follows:

4, the range

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

  • We can call the equivalent matching of the index columnsSingle point range“Said aboveThe scope of 1andScope 2Can be called a single point interval, likeThe scope of 3This is what we call the continuous range interval.

5, the index

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

  • Since key_part2 is not the leftmost index column of the union index IDX_KEY_part, we cannot use the ref or range access methods to execute this statement. However, this query satisfies the following two conditions:

    • Its query list has only three columns:key_part1.key_part2.key_part3, and the indexidx_key_partIt has these three columns.
    • Onlykey_part2The column. This column is also included in the indexidx_key_partIn the.

  • That is, we can compare key_part2 = ‘ABC’ directly by iterating through the leaves of the idX_KEY_part index, Add the values of key_PART1, KEY_PART2, key_part3 columns from the matched secondary index record directly to the result set. Due to the secondary index record much smaller than clustering cable records (clustering index record to store all user-defined columns, and the so-called hidden columns, and the secondary index record only need deposit index columns and primary key), also need not to carry on the back to the table and the process operation, so directly through the secondary index than directly through the cluster index cost much smaller, The uncle who designed MySQL called this execution by traversing the secondary index record: index.

5, all

  • A full table scan

6, the index of the merge

  • It is also possible to use multiple secondary indexes in a single query

(1) Intersection

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

Assume that the query is executed using the Intersection merge. The process looks like this:

  • fromidx_key1Corresponding to the secondary indexB+In the tree to take out thekey1 = 'a'Of the relevant records.
  • fromidx_key3Corresponding to the secondary indexB+In the tree to take out thekey3 = 'b'Of the relevant records.
  • Secondary index records are made byIndex column + primary keySo we can figure out these two result setsidThe intersection of values.
  • Generated in the previous stepidThe value list performs a backtable operation, that is, from the cluster index to the specifiedidThe complete user record of the value is retrieved and returned to the user.

(2)

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

Intersection is the meaning of Intersection. This applies when search conditions using different indexes are joined by AND. Union is the meaning of a Union, and applies when search conditions using different indexes are joined by an OR. As with the Intersection index merge, MySQL uses the Union index merge only under certain circumstances: