Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "selective parsing of mysql federated index". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The selectivity of federated index is discussed through the following experiments:

Version: percona 5.6.27

Create table test (

An int

B int

C int

Name varchar (32)

PRIMARY key (a)

Key index_a_b_c (a _ c) ENGINE=INNODB

Insert into test values (1, 1, 1, 1, 1, 1, 3, and 3)

Insert into test values (2, 1, 1, 2, 2, 1, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 1, 2, 2, 2, 1, 2, 2, 1, 2, 2, 2, 1, 2, 2, 2, 1, 2, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 1, 2, 2, 2, 2, 1, 2, 2, 2, 2, 2, 2, 1, 2, 2, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, 2,

Insert into test values (3 people, 1 people, 3 people, 3 people, 1).

Insert into test values (4, 1, 2, 3, 7)

Insert into test values (5, 1, 1, 1, 3, 4, Lucy)

Insert into test values (6, 2, 2, 2, 3, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 6, 4, 6, 4, 4, 6, 4, 4, 6, 4, 4, 6, 4, 4, 4, 6, 4, 4, 6, 4, 4, 6, 4, 4, 4, 6, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 4, 4, 6, 4, 6, 4, 4,

Insert into test values (7, 3, 1, 1, 2, 5, 5, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,

Insert into test values (8, 2, 1, 1, 3, 4, 7, 3, 4, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,

One: the selectivity of where condition to federated index

Mysql > explain select * from test where astat2

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ref | index_a_b_c | index_a_b_c | 5 | const | 2 | NULL |

+-- +

Mysql > explain select * from test where axi2 and bliss 1

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ref | index_a_b_c | index_a_b_c | 10 | const,const | 1 | NULL |

+-- +

Mysql > explain select * from test where axiom 2 and baccalaure2 and canton3

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ref | index_a_b_c | index_a_b_c | 15 | const,const,const | 1 | NULL |

+-- +

These three are normal methods of use, and all of them are indexed.

Mysql > explain select * from test where axiom 2 and cantilever 3

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | test | ref | index_a_b_c | index_a_b_c | 5 | const | 2 | Using index condition |

+-+-

1 row in set (0.00 sec)

If you omit b, you will also leave the index.

Mysql > explain select * from test where baggage 2 and cantilever 3

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 8 | Using where |

+-- +

1 row in set (0.00 sec)

Mysql > explain select * from test where censor 3

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 8 | Using where |

+-- +

If an is omitted, the index will not be left out.

Conclusion: there must be the first field of the federated index as the wehre condition

Second, the sorting selectivity of federated index

The sorting of the federated index will be sorted in the order of (arem bjorc).

The order of the test data in the joint index will be (1), (1), (1), (1), (1), (1), (2), (2), (2) and (3) sequentially.

Mysql > explain select * from test where axi2 order by b

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ref | index_a_b_c | index_a_b_c | 5 | const | 2 | Using where |

+-- +

Mysql > explain select * from test where axi2 order by c

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | test | ref | index_a_b_c | index_a_b_c | 5 | const | 2 | Using where; Using filesort |

+-+-

You can see that the second Using filesort uses temporary table sorting, which is inefficient. You can know from the sorting of the federated index that when you specify the value of a, these values will be sorted by the value of b, not by the value of c, so order by b no longer needs to be sorted by filesort, whereas order by b needs to be reordered.

So select * from test where axiom 2 and baggage 2 order by c; does not filesort sort.

Mysql > explain select * from test where axi2 and bliss 2 order by c

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ref | index_a_b_c | index_a_b_c | 10 | const,const | 1 | Using where |

+-- +

Conclusion: when sorting a field in a federated index, the best method is to have the field filtering condition before the sorted field in the federated index.

This is the end of the content of "selective parsing of mysql Union Index". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.