The difference and connection between index and Multiple-column indexes in MySQL

Indexes have an immediate effect on raising the query speed of select/update statements, indexed and non-indexed, and the query speed is often several orders of magnitude.

This discussion uses the scenario for index (each column as an index, a single row index) and Multiple-column Indexes (multiple columns as an index, up to 16 columns, composite index).

Common ways to create or add indexes:

Way one, build the table when you create a new

CREATE TABLETest (IDINT  not NULL, Last_NameCHAR( -) not NULL, first_nameCHAR( -) not NULL,    PRIMARY KEY(ID),--single-column index    INDEXname (last_name)--Composite Index--INDEX name (last_name,first_name));

Mode two, add an index to the Save table:

/* single-column index */ ALTER TABLE ADD INDEX name (last_name); /* Composite Index */ ALTER TABLE ADD INDEX name (last_name,first_name);

It is important to note that the order of the indexes is related to whether the index can be hit and discussed in detail later.

The official MySQL document describes the composite index: MySQL can use a composite index to query all the columns in the index, or to query the first column in the composite index, the top two columns, the first three columns, and so on.

If you specify that the column order is correct relative to the order of the definitions in the index, a simple composite lead can speed up several types of queries in a table. Official address

The above-mentioned packet can be read as follows:

• Advantages of composite indexes: A composite index solves the efficiency problem of multiple queries
• Hit rule for composite index: the first half of the sentence describes the leftmost prefix (leftmost prefix) rule of the hit composite index, which is the index order problem mentioned above. This rule is for the index column in the WHERE statement.

Simply put, the order in which the composite index is joined IS (col1, col2, col3) then the WHERE statement (col1), (Ol1, col2), (col1, col2, col3) can hit the index, but (col2), (col3), (Ol2, col3) The index rule cannot be hit because the leftmost prefix rule is not satisfied.

For a chestnut, suppose the new composite index is as follows:

CREATE TABLETest (IDINT  not NULL, Last_NameCHAR( -) not NULL, first_nameCHAR( -) not NULL,    PRIMARY KEY(ID),INDEXname (last_name,first_name));

Based on the leftmost prefix principle, the following query statements can hit the index:

SELECT *  fromTestWHERELast_Name='Widenius'; SELECT *  fromTestWHERELast_Name='Widenius'  andFirst_Name='Michael'; SELECT *  fromTestWHERELast_Name='Widenius'   and(first_name='Michael' ORFirst_Name='Monty'); SELECT *  fromTestWHERELast_Name='Widenius'   andFirst_Name>='M'  andFirst_Name< 'N';

The following query statement cannot hit a composite index:

 select  *   test where  first_name=   '    

If you don't know whether to hit the index, explain is a good tool to make the most of it. For example, the following statement, see the results of the key can know whether the hit, empty is the wood has hit:

SELECT *  from WHERE first_name='Michael';

Usage scenarios:

In the report set meter is generally used ETL tools to extract business data into the fact table, such as the use of Kettle update/insert, its internal principle is the first query does not exist, such as inserting data, there is data updates.

So if there is a lot of data there will be a lot of query action, and is a multi-conditional query. In this case, composite index efficiency is the highest, but often the actual business will also be a single row as a query, this is the need for a common index to meet this requirement.

It is also normal for the same column to appear in both the composite and single-row indexes. Give a practical example as follows:

CREATE TABLE' St_stock ' (' ID ')int( One) not NULLAuto_increment COMMENT'self-increment ID', ' Id_company 'int( One) not NULLCOMMENT'Organization Dimension', ' id_date 'int( One) not NULLCOMMENT'Date Dimension', ' Id_part 'int( One) not NULLCOMMENT'Dimension of material',  ` Number`decimal( -,2)DEFAULT NULLCOMMENT'number of outbound libraries', ' Subtotal 'decimal( +,6)DEFAULT NULLCOMMENT'Out-of- stock amount',  PRIMARY KEY(' id '),KEY' idx_org ' (' Id_company '),KEY' idx_part ' (' Id_part '),KEY' Idx_date_org_part ' (' id_date ', ' id_company ', ' Id_part ')) ENGINE=InnoDB auto_increment=1 DEFAULTCHARSET=UTF8 COMMENT='xxxxxx';

Actual effect:

#2498和 #2499 data is greater than or equal to #2496, #2497, but the set time is nearly half shortened, the effect is still very obvious.

The optimization effect does not reach a number or more advanced because the optimization is just one conversion in the job, just one of the many factors of optimization.

Even so, it achieves the desired effect.

Finally, it is important to note that index maintenance is a cost, not the more the better. Other references are as follows:

https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/

https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/

Https://dev.mysql.com/doc/refman/5.7/en/drop-index.html

Https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html

Https://dev.mysql.com/doc/refman/5.7/en/create-index.html

The difference and connection between index and Multiple-column indexes in MySQL