Database index-Multi-column index

For a composite index, index the frequency of the field as it appears in the query criteria. in a composite index, a record is first sorted by the first field. For records with the same value on the first field, the system is sorted by the value of the second field, and so on. Therefore, only the first field of the composite index appears in the query criteria, the index may be used, so the application of a high frequency field, placed in front of the composite index, will allow the system to use the index in the most possible way to play the role of the index. Second, multi-column index suitable for the scene

1. Full Field matching

2. Matching part leftmost prefix

3. Match the first column

4. Match the first column range query (can be used like a%, but cannot use like%b)

5. Exact Match of a column and a range match another column

The fields that appear in the order by action also apply to rules that are found by value, and the fields that appear in Where+order by can be set up to meet the above five rule multi-column indexes. You need to use multiple columns to find the leftmost indexed column, you cannot skip the middle column, and if a column is a range query, all columns to the right of it cannot use the index. In what case is the range query, and under what circumstances are multiple equivalent queries? If there is an order by sort, multiple equals condition queries are range queries, and there is no limit to the order by ordering.

For example, to create a multicolumn index (name, age, ID), you can use only the first two columns of the index. In is a range query
... where name= ' nginx.cn ' and age in (15,16,17) the ORDER by ID

The entire index can be used, in is query by value
... where name= ' nginx.cn ' and age in (15,16,17) and id = ' 3 '

The establishment of compound index and the principle of leftmost prefix

The prefix for the index string value (Prefixe). If you need to index a string data column, it is best to specify the prefix length in any appropriate case.
For example, if you have a char (200) data column, do not index the entire data column if the first 10 or 20 characters are different. Indexing the first 10 or 20 characters saves a lot of space. You can index the prefixes of Char, VARCHAR, BINARY, VARBINARY, blob, and text data columns.
Suppose you set up a composite index on a table's state, city, and zip data columns. The rows of data in the index are arranged in state/city/zip order, so they are also automatically arranged in the order of state/city and state. This means that MySQL can use this index even if you specify only the state value in the query, or specify the state and city values. Therefore, this index can be used to search for a combination of data columns as follows:
State, city, zip
State, City
State
MySQL cannot use this index to search for content that is not contained in the leftmost prefix. For example, if you search by city or zip, you will not use this index. If you search for a given state and a specific zip code (1 and 3 columns of the index), the index is also not available for this combined value, although MySQL can use the index to find the matching state to narrow the search.
If you consider adding an index to a table that has already been indexed, consider whether the index you are increasing is the leftmost prefix of an existing multi-column index. If so, do not increase the index because it already has (for example, if you have an index on state, city, and zip, there is no need to increase the index of the state).

Iv. Understanding Single index, multi-column index, and leftmost prefix principle through an example

Example: Now we want to find out the user ID that meets the following criteria:
Mysql>select ' uid ' from people WHERElname ' = ' Liu '   and  ' fname ' = ' zhiqun ' and ' age ' =26  ; because we don't want to scan the entire table, consider using an index.

       1, Single-column index:
        ALTER TABLE People ADD INDEX lname (lname);
      index The lname column so that the range is limited to the result set 1 of lname= ' Liu ', and then the result set 1 is scanned, Produces a result set satisfying fname= ' Zhiqun ' 2, then scans the result set 2, finds the result set of Age=26 3, which is the final result.

       because the index of the lname column is established, it is much more efficient than performing a full scan of the table. But the number of records we require to scan is still far beyond what is actually needed. Although we can delete the index on the lname column, and then create an index of the fname or age column, the efficiency of the index search is still similar regardless of which column is created.

     2, Multi-column index:
     ALTER TABLE People ADD INDEX lname_fname_age (lame,fname,age);

To improve search efficiency, we need to consider using multi-column indexes, because the index file is saved in b-tree format, so we don't have to scan any records to get the final result.

Note: When executing a query in MySQL, only one index can be used, and if we are building an index on lname,fname,age, we can only use one index when executing the query, and MySQL chooses the most rigorous index to get the lowest number of result set records.

3. Leftmost prefix: As the name implies, is the leftmost priority, the above example we created the Lname_fname_age Multi-column index, equivalent to create a (lname) single-row index, (lname,fname) composite Index and (lname,fname,age) composite index.

Note: When creating a multicolumn index, the most frequently used column in the WHERE clause is placed on the leftmost line, depending on the business requirements.

First, Introduction

The index of the database can speed up the query because the index uses a specific data structure (B-tree) to store additional organizations for specific columns, speeding up the storage engine (the index is the storage engine implementation) to find records.
Index optimization is the most important means of database optimization.

If a query statement uses an index (usually a where condition to match an index), the tree's structure is used to speed up the lookup, and the index finds the row in the table by value, and the clustered index (index and data is stored together) that does not need to query the data back to the table. It is usually necessary to return the table and check the data, which consumes additional disk IO. So there are times when (such as sequentially reading data) a full table scan is faster than using an index.

When a query condition has only one field, the index can be indexed in that field, and the place to optimize is to use a prefix index for the text BLOB field.

Single-column and multicolumn indexes differ greatly when the query condition has multiple fields. If you use a multicolumn index, the order of the fields in the where condition is important, and you need to satisfy the leftmost prefix column. Leftmost prefix: All fields in the query condition need to appear sequentially from the left to the multiple-column index, the number of fields in the query criteria is less than the number of fields equal to the multiple-column index, and the intermediate fields cannot have fields for the range query (<,like, etc.), so that SQL can use the multicolumn index.

Database index-Multi-column index