Match type for MySQL index

Match type for MySQL index

/*

Create a test table

drop table T_index;

CREATE TABLE T_index (

TID int not null PRIMARY key auto_increment,

Tname varchar (+) NOT NULL,

Tage TINYINT Default 0,

Tadd varchar (+) Default ',

Tel int default 0,

Tmob varchar ("DEFAULT"),

TSFZ varchar (+) Default '

)

Engine=innodb DEFAULT Charset=utf8;

Insert data:

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Zhang San Feng ', 120, ' Wudangshan ', 18099001122, ' 012-46319976 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Zhu Yuanzhang ', 56, ' Beijing ', 18112401122, ' 012-40119976 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Yang over ', 25, ' Wuhan ', 18099112122, ' 012-46340116 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Guo Jing ', 45, ' Changsha ', 13149001122, ' 012-46900176 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Huang Lao evil ', 100, ' Hebei ', 13129001122, ' 012-49001976 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Botong ', 102, ' Henan ', 15679001122, ' 012-46319001 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Hong Seven Public ', 78, ' Hefei ', 11243001122, ' 012-46319976 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Yin ', 67, ' Guangxi ', 13214001122, ' 012-14009976 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Ouyang can ', 27, ' Shenzhen ', 15123001122, ' 012-46314006 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Nina ', 10, ' Shanghai ', 13125001122, ' 012-41400976 ', ');

Insert into T_index (TNAME,TAGE,TADD,TEL,TMOB,TSFZ)

VALUES (' Yang Kang ', 30, ' Tibet ', 15798001122, ' 012-46311400 ', ');

Create a multi-column index

ALTER TABLE T_index

Add key (Tname,tage,tadd)

You can use the B-tree query type:

1. Full value matching: The query contains all the columns in the Multi-column index and takes full advantage of the index read data in the query.

This condition exists in a multi-column index and is a full-value match for a single-row index.

In a multi-column index, you may not be able to match all values for some reason, only partial column matching.

For example:

Mysql> Explain select * from T_index where Tname= ' Zhang three winds ' and tage=120 and tadd= ' Wudangshan ';

+----+-------------+---------+------+---------------+-------+---------+-------------------+------+------------- +

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+---------+------+---------------+-------+---------+-------------------+------+------------- +

| 1 | Simple | T_index | Ref | Tname | Tname | 607 |    Const,const,const | 1 | Using where |

+----+-------------+---------+------+---------------+-------+---------+-------------------+------+------------- +

The query uses full-value matching, and all columns in the multi-column index are used in the query

Note: If you do a full value match, then all columns preceding the last column in the multicolumn index must give the exact value, not the range value,

Otherwise, it becomes a partial value match,

2. Partial value matching: The columns in the query are not fully utilized in the multi-column index.

The columns used in the query are included in the multi-column index, but some columns use range values (except for the last column in the index)

Sql11

Mysql> Explain select * from T_index where Tname= ' Zhang three winds ' and tage like ' 12% ' and tadd= ' Wudangshan ';

+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+

| 1 | Simple | T_index | Ref | Tname | Tname | 302 |    Const | 3 | Using where |

+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+

Sql2

Mysql> Explain select * from T_index where Tname= ' Zhang three winds ' and Tage = + and tadd like ' Wudang% ';

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

| 1 | Simple | T_index | Range | Tname | Tname | 607 |    NULL | 1 | Using where |

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

Sql3

Mysql> Explain select * from T_index where Tname= ' Zhang three Winds ' and Tage > and tadd= ' Wudangshan ';

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

| 1 | Simple | T_index | Range | Tname | Tname | 304 |    NULL | 2 | Using where |

+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+

Sql4

Mysql> Explain select * from T_index where Tname= ' Zhang three winds ' and Tage < and tadd= ' Wudangshan ';

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

| 1 | Simple | T_index | All | Tname | NULL | NULL |   NULL | 13 | Using where |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

SQL1 in Tage using a fuzzy query query causes the query to use only the first column of the index (which was previously thought to be used in the first and second columns)

Tadd uses range values in SQL2, but Tadd is the last column in the index, and the query or full-value matching is used.

SQL3 Tage uses a range value, from the execution plan you can see that the query uses the first and second columns of the index

SQL4 uses a full-table scan, does not use any index scan, according to the index usage rules, the query can walk the index scan completely, but why did not go.

My query results are published, estimates will be unexpected.

Mysql> SELECT * from T_index where Tname= ' Zhang three winds ' and Tage < and tadd= ' Wudangshan ';

Empty Set (0.00 sec)

The query comes up with a null value. According to our conventional thinking, SQL4 can use the first and second columns of a multicolumn index. This is a bit different from SQL Server,

Mention here, beware of being deceived.

This article is from "SQL Server MySQL" blog, declined reprint!

Match type for MySQL index