Shulou(Shulou.com)06/01 Report--

An introduction

It is believed that in the process of dealing with developers, DBA will often encounter requirements such as paging query order by sorting. This paper comes from a case in the production process. When version 5.6 database uses a non-unique field of limit and order by, the result set is not always determined. It has been identified as bug. For more information, see the official bug of MySQL.

Remind readers to pay attention.

Second analysis

Environmental preparation

CREATE TABLE `tb1` (

`id` bigint (20) NOT NULL AUTO_INCREMENT

`a` decimal (19pr 2) NOT NULL

`acid` bigint (20) NOT NULL

`prid` bigint (20) NOT NULL

PRIMARY KEY (`id`)

KEY `idx_ prid` (`prid`)

KEY `idx_ acid` (`acid`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 notice that field a has no index on it.

Initialization data

INSERT INTO `tb1` (`id`, `a`, `acid`, `prid`)

VALUES (1pyrrine 2.00pyrr3), (2pjor3.00pyr2), (3pjre 4.00pyr2), (4pjor5.00pyr2), (5pyrrp2), (6pr 8.00pyrr2), (7ml10.00pyr2), (8pr 12.00pyr2), (9pr 16.00 2pr 3), (10jie 20.00 2pr 3), (11pr 6.00pyrus 2pr 4), (12jue 8.00pyrrine 2pen4), (13jue 10.00pr 4), (14rec 12.00pr 2pr 4), (15pr 5.00pr 2pr 2), (166.00 pr 2), (166.00 pr 2) Perform two order by sorts based on non-indexed fields with duplicate values

Mysql > select * from tb1 order by a desc limit 4

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

| | 8 | 12.00 | 2 | 3 | |

+-- +

4 rows in set (0.00 sec) to get the result set with id of 10,9,14,8

Mysql > select * from tb1 order by a desc limit 3

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 | | 8 | 12.00 | 2 | 3 |

+-- +

3 rows in set (0.00 sec) to get the result set with id of 109 8

Index the a field

Mysql > alter table tb1 add key ind_tb1a (a)

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > select * from tb1 order by a desc limit 3

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 8 | 12.00 | 2 | 3 | |

+-- +

3 rows in set (0.00 sec) to get the result set with id of 109 8

Mysql > select * from tb1 order by a desc limit 4

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

| | 8 | 12.00 | 2 | 3 | |

+-- +

4 rows in set (0.00 sec) to get the result set with id of 10,9,14,8

From the above tests, the result set is uncertain for a non-unique field, whether it contains an index or not.

Three solutions

1 if the business attribute ensures that the a field cannot be unique, you need to add the sorting of a unique field, such as id, to the sort result.

Mysql > select * from tb1 order by a desc, id desc limit 4

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

| | 8 | 12.00 | 2 | 3 | |

+-- +

4 rows in set (0.00 sec) mysql > select * from tb1 order by a desc, id desc limit 3

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

+-- +

3 rows in set (0. 00 sec) after sorting using order by id/unique_key, the first three result sets are consistent. The result set meets our needs. In order to solve the problems caused by uncertainty.

Whether unnecessary order by can be removed or not is determined by the business logic.

Four summaries

DBA should note that the sorted result set based on non-unique fields is uncertain during communication / training with developers. If the business logic has high requirements for paging or order by result sets, remember to sort with a unique key.

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.