MySQL Index Analysis

Source: Internet
Author: User

MySQL composite unique index analysis on composite unique index (unique key or unique index), online search Many people say: "This index plays a key role in the constraints, the query performance is not improved or the query did not walk the index column." Others say, "The query uses the same index as the normal index." So what is the problem? Test prep Work

Prepare to build a table statement, insert data, and so on:

--Build a table (note: A, B is a composite unique index)
Create table test0 (id bigint not null primary key auto_increment, a varchar (ten) not null, b varchar
(ten) not null, unique index (A, b)) Engine=innodb Charset=utf8 auto_ increment=1;


--Inserting data
Insert into Test0 (A, b)values(' China ', ' Chinese ');
Insert into Test0 (A, b)values(' Japan ', ' Japanese ');
Insert into Test0 (A, b)values(' Germany ', ' German');
Insert into Test0 (A, b)values(' Korea ', ' Korea');
Insert into Test0 (A, b)values(' France ', ' frence');
Insert into Test0 (A, b)values(' Australia ', ' Australia ' );
Insert into Test0 (A, b)values(' America ', ' American');
Insert into Test0 (A, b)values(' Brazil ', ' Brazil');

--Execution Plan one (query existence record)
Explain Select * from test0 where a=' France ' and b=' frence ' /c19>;
--Execution Plan II (no record is available for enquiry)
Explain Select * from test0 where a=' France ' and b=' America ';

Executive Plan One:

Implementation Plan II:

It can be seen from the following:
Execution plan One, hit all indexes when the query condition matches;
Execution plan two, the query condition does not match when the index is not hit, and returns a extra "impossible WHERE noticed after reading const tables"

So what's the difference between these two queries?
What is MySQL's execution plan and optimization plan for this kind of index?
I had to read the official doc once and the key points were summarized as follows:

Explain Join types Execution plan type list, from performing best to worst:
1. There is only one row of data in the System
Table (=system table). This is a special case of a constant connection type.
2. Const
has only one row of data to match when the table is read at the beginning of the query. Because only one row of data is matched, the value column can be considered a constant-level optimization. Constant table queries are very fast because they can be hit only once. The
constant type is only used if it matches all of the column values of "PRIMARY KEY" or "UNIQUE INDEX".
The following query, the table is executed as a constant table:

    -- query 1
SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
    1. Ref
      All rows with an indexed value match are read from the previous table by reading the various combinations of rows from this table. If the connection uses only the leftmost prefix of key, or if key is not primary key and unique index use ref (in other words, if the join follows the key value does not query the single row of data). This is a good type if the key value is used to match only a few rows.
      Ref can be used in indexed columns, with the "=" or "<=>" operation when conditions match. In the following example, MySQL can handle ref_table using a ref connection:
 < Span class= "Hljs-keyword" >select  * from  ref_table where  key_column=expr;  

select * FROM ref_table,other_table
where Ref_table.key_column=other_table.column ;


select * FROM ref_table,other_table
where ref_table.key_column_part1=other_table. column
and ref_table.key_column_part2=1 ;
    1. Ref_or_null
      This connection is somewhat similar to ref, but when the query row contains null values, MySQL makes additional queries. This type of connection optimization is most often used when troubleshooting subqueries. In the following example, MySQL uses the REF_OR_NULLL connection to handle the ref_table:
SELECT * FROM ref_table      WHERE key_column=expr OR key_column IS NULL;
参见 8.2.1.6, “IS NULL 优化”.
    1. Index_merge
      This type of connection indicates that index merge optimizations are enabled. In this scenario, the key column in the output row contains a list for the index, and Key_len displays the longest key parts used by the index. For more information, see "Index consolidation Optimization".

      1. Range
        Queries these rows with an index only when rows are retrieved from a given range. The key column of the output row indicates which index to use. The Key_len column shows the longest key part used. This connection ref column is null.
        Use the range connection when the key column and a constant use "=", "<>", ">", ">=", "<", "<=", is NULL, "<=>", between, or in () comparison:
     SELECT *  from tbl_name WHERE key_column = ten;

SELECT * from tbl_name WHERE key_column between and 20 ;
SELECT * from tbl_name WHERE key_column in (ten,30 );

SELECT * from tbl_name WHERE key_part1 = Ten
and Key_part2 in (ten,+);
    1. Index
      The index connection type is the same as all except the Scan index tree. Two modes of execution:
      If the index is a covering index in the query and can query all the satisfied data in the table, only the index tree is scanned. In this scenario, the extra column displays "Using index". Indexing scans are usually much faster than full table scans, because the number of indexes is usually much less than the amount of data in the table. The full table Scan optimization scheme is to retrieve the data by reading the index and retrieving it in the index order. Using an index is not displayed in the Etra column. When a column uses a single index, MySQL uses this type of connection.

      1. All
        Full table scan. In general, you should avoid full table scans by adding indexes.

EXPLAIN Extra Information

The Extra column of EXPLAIN output contains additional information about how MySQL resolves the query. The following list explains the values that can appear on this column. If you want to make your queries as fast as possible, look out for Extra values of using Filesort and using temporary.

1. Child of ' table ' pushed [email protected]

This table was referenced as the child of table in a joins that can be pushed down to the NDB kernel. Applies only in MySQL Cluster NDB 7.2 and later when Pushed-down joins is enabled. See the description of the Ndb_join_pushdown server system variable for more information and examples.2. Const row not found

For a query such as SELECT ... From Tbl_name, the table is empty.
3. Distinct

MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it have fou nd the first matching row.

4. Full scan on NULL key

This occurs to subquery optimization as a fallback strategy when the optimizer cannot use of an Index-lookup access method.

5. Impossible having

The HAVING clause are always false and cannot select any rows.

6. Impossible WHERE

The WHERE clause is always false and cannot select any rows.

Impossible WHERE noticed after reading const tables

MySQL has a read all const (and system) tables and notice the WHERE clause are always false.

MySQL Index Analysis

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.