Index usage policy and optimization

index usage policy and optimization

MySQL optimization is mainly divided into structural optimization (Scheme optimization) and query Optimization (optimization). The high performance indexing strategy discussed in this chapter belongs to the structure optimization category. The content of this chapter is based entirely on the theoretical basis above, in fact, once understanding the mechanism behind the index, then the choice of high-performance strategy becomes pure reasoning, and can understand the logic behind these strategies. Sample Database

In order to discuss the indexing strategy, a database with a small amount of data is required as an example. This article selects one of the sample databases provided in the MySQL official documentation: employees. This database has a moderate complexity and a large amount of data. The following figure is the E-r diagram of this database (referenced from the official MySQL manual):

Figure 12

The page for this database in the MySQL official documentation is http://dev.mysql.com/doc/employee/en/employee.html. This database is described in detail, and provides the download address and import method, if interested in importing this database to their own MySQL can refer to the content of the text. the leftmost prefix principle and related optimizations

The primary condition for efficient use of indexes is to know what queries will be used in the index, which is related to the "leftmost prefix principle" in B+tree, which illustrates the principle of the leftmost prefix by example.

Let's talk about the concept of federated indexing. In the above, we assume that the index only refers to a single column, in fact, the index in MySQL can refer to multiple columns in a certain order, such an index is called a federated index, in general, a federated index is an ordered tuple <a1, A2, ..., An>, where each element is a column of the data table, In fact, to strictly define the index requires a relational algebra, but here I do not want to discuss too much of the topic of relational algebra, because it will be very boring, so this is not strictly defined here. In addition, a single-column index can be considered a special case where the number of federated indexed elements is 1.

Take the Employees.titles table as an example, and see what indexes are on it:

   
   
    
    SHOW INDEX from Employees.titles; +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
    
    | Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Null |
    
    Index_type | +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
    
    |          Titles | 0 |            PRIMARY | 1 | Emp_no |        A |      NULL | |
    
    BTREE | |          Titles | 0 |            PRIMARY | 2 | Title |        A |      NULL | |
    
    BTREE | |          Titles | 0 |            PRIMARY | 3 | From_date |      A |      443308 | |
    
    BTREE | |          Titles | 1 |            Emp_no | 1 | Emp_no |      A |      443308 | |
    
    BTREE | +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
   
    

From the results can be to the main index of the titles table is <emp_no, title, From_date> and a secondary index <emp_no>. To avoid multiple indexes making things complicated (the MySQL SQL optimizer behaves more complex at multiple indexes), here we drop the secondary index:

   
   
    
    ALTER TABLE employees.titles DROP INDEX emp_no;
   
   

This allows you to focus on the behavior of the index primary. case One: full column matching.

 EXPLAIN select * from Employees.titles WHERE emp_no= ' 10001 ' and title= ' Se
    
    Nior Engineer ' and from_date= ' 1986-06-26 '; +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    
    | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
    
    Extra |  +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    
    | 1 | Simple | Titles | Const | PRIMARY | PRIMARY | 59 |    Const,const,const |       1 |
    
    | +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
   
   

It is clear that indexes can be used when exact matches are made by all columns in the index (where exact matches are referred to as "=" or "in" matches). One thing to note here is that the index is theoretically sensitive to order, but because the MySQL query optimizer automatically adjusts the conditional order of the WHERE clause to use the appropriate index, for example, we reverse the condition order in Where:

   
   
    
    EXPLAIN SELECT * from Employees.titles WHERE from_date= ' 1986-06-26 ' and emp_no= ' 10001 ' and title= ' Senior Engineer ';
    
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    
    | ID | Select_type | Table  | type  | possible_keys | key     | key_len | ref               | rows | Extra |
    
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
    
    |  1 | Simple      | titles | const | PRIMARY       | PRIMARY |      | Const,const,const    | 1 |       |
    
    +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
   
   

The effect is the same. Scenario Two: the leftmost prefix matches.

 EXPLAIN select * from Employees.titles WHERE emp_no= ' 10001 '; +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
    
    | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
    
    Extra |  +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
    
    | 1 | Simple | Titles | Ref | PRIMARY | PRIMARY |