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

Preface

One day, Xiaoming said anxiously on the communication tool: there is a strange problem on this sideline. Please identify it greatly by DBA and execute the statement select xx from table_name wheere xxx order by field A limit offset.

The total number of table data is 48, the number of pages is normal, but the results are mixed, and the data from the first page appears on the second page; if order by field B would not have this phenomenon, how could this be!

In fact, the question is very simple, if you have read the official file carefully. ~ ^ _ ^ ~

Let's first take a look at what the official file says:

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders.

Problem recurrence

This experiment uses community version MySQL 5.6.26 (because this is the version O (∩ _ ∩) O~ where Xiaoming has problems). First, create the experimental environment and initialize the test data:

Root@localhost [(none)] > select @ @ version;+-+ | @ @ version | +-+ | 5.6.26-log | +-+ 1 row in set (0.00 sec) root@localhost [(none)] > show variables like "sql_mode" +-+-+ | Variable_name | Value | +-+ | sql_mode | NO_ENGINE_SUBSTITUTION | +- -+-+ 1 row in set (0.00 sec) root@localhost [(none)] > create database glon_ho Query OK, 1 row affected [(none)] > use glon_hoDatabase changedroot@localhost [glon_ho] > create table glon (- > id int not null auto_increment primary key,-> name varchar (20) not null,-> create_time datetime not null,-> age tinyint unsigned default 18->) Query OK, 0 rows affected (.01 sec) root@localhost [glon_ho] > INSERT INTO `glon` VALUES (1, 'Eason Chan',' 2017-05-02 08), (2, 'Glon Ho',' 2017-05-03 12 sec 10), 18), (3, 'Zhao Min,' 2017-05-03 14 sec 10 10, 17), (4, 'Jacky Cheung',' 2017-05-02 1400 root@localhost, 22), (5) 'Chow Chi-Ruo', '2017-05-02 14 _-_ Query OK, 8 rows affected (0.01sec) Records: 8 Duplicates: 0 Warnings: 0root@localhost [glon_ho] > select * from glon +-+ | id | name | create_time | age | +-+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | 5 | Zhou Zhiluo | 2017-05-02 14:00:00 | 16 | 6 | Andy Lau | 2017-05-02 14:00:00 | | 50 | | 7 | Supreme Treasure | 0-05-02 14:00:00 | 20 | | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | +-+ 8 rows in set (2017 sec) |

A glon table is created with fields such as self-increment id, name name, age age, and user registration time create_time.

Then let's reproduce the problem:

Sort according to the user registration time create_time:

Root@localhost [glon_ho] > select * from glon ORDER BY create_time limit 0,4 +-+ | id | name | create_time | age | +-+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | +- -+-+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time limit 4 4 +-+ | id | name | create_time | age | +-+ | 7 | Supreme Treasure | 2017-05 -02 14:00:00 | 20 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | +-+ 4 sec

You can see that Liu Sanjie with id 8 appeared in the two query results. From the above initialization data, there are a total of 8 pieces of data. Now there are not only duplicate data in paging, but also a missing one!

The problem does reappear, but don't worry, let's try a few more groups of other sorting methods.

Combined sorting of create_time and age

Root@localhost [glon_ho] > select * from glon ORDER BY create_time,age limit 0,4 +-+ | id | name | create_time | age | +-+ | 1 | Eason Chan | 2017-05 -02 08:10:10 | 19 | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 7 | Supreme Treasure | 2017-05-02 14:00:00 | 20 | +- -+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time Age limit 4, 4 +-+ | id | name | create_time | age | +-+ | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | +-+-- -- +-+ 4 rows in set (0.00 sec)

Combined sorting of create_time and id

Root@localhost [glon_ho] > select * from glon ORDER BY create_time,id limit 0,4 +-+ | id | name | create_time | age | +-+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +-+-- -+-+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time Id limit 4, 4 +-+ | id | name | create_time | age | +-+ | 7 | Supreme Treasure | 2017-05 -02 14:00:00 | 20 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | +-+ 4 sec

Primary key id sort

Root@localhost [glon_ho] > select * from glon ORDER BY id limit 0,4 +-+ | id | name | create_time | age | +-+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | +- -+-+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY id limit 4 4 +-+ | id | name | create_time | age | +-+ | 5 | Zhou Zhi Ruo | 2017-05 -02 14:00:00 | 16 | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | | 7 | Supreme Treasure | 2017-05-02 14:00:00 | 20 | | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | +-+ 4 sec

See, the following groups of sorting methods are no longer a problem, combined with official files, we know that when order by sorting, if there are multiple rows of the same column value in the sorting field, the sorting result is uncertain. So the following groups of combinatorial sorting or primary key id sorting, because of the high uniqueness, so the sorting is determined, there will be no confusion of results.

Can we end this, no way? let's take a look at the following experiment and continue to consolidate it:

Sort by age age:

Root@localhost [glon_ho] > select * from glon ORDER BY age limit 0,4 +-+ | id | name | create_time | age | +-+ | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +- -+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY age limit 4 4 +-+ | id | name | create_time | age | +-+ | 8 | Liu Third Sister | 2017-05-02 14:00:00 | 19 | 7 | Supreme Treasure | 2017-05-02 14:00:00 | 20 | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +-+-- -+-+ 4 rows in set (0.00 sec)

Why, this sort is only sorted according to a field age, how come there is no problem? Don't worry, there are other tricks:

Root@localhost [glon_ho] > insert into glon values (9 Magi 'Qiao Feng', '2017-05-03 13 purge 10 insert into glon values 10 insert into glon values 22), (10 Magi' Duan Yu', '2017-05-03 15 purge 10 purge 19), (11 Magi' Guo Jing', '2017-05-03 17 purge 10 purge 10 purge 20), (12 Jing' Huangrong', '2017-05-03 08) Query OK, 4 rows affected (0.01sec) Records: 4 Duplicates: 0 Warnings: 0root@localhost [glon_ho] > select * from glon +-+ | id | name | create_time | age | +-+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | 5 | Zhou Zhiluo | 2017-05-02 14:00:00 | 16 | 6 | Andy Lau | 2017-05-02 14:00:00 | | 50 | | 7 | Supreme Zunbao | 2017-05-02 14:00:00 | 20 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 9 | Qiao Feng | 2017-05-03 13:10:10 | 22 | 10 | Duan Yu | 2017-05-03 15:10:10 | 19 | 11 | Guo Jing | 2017-05-03 17:10:10 | 20 | 12 | | | Huang Rong | 0-05-03 08:10:10 | 19 | +-+ 12 rows in set (sec) |

I added a few more pieces of data to the glon table, and then take a look at:

Root@localhost [glon_ho] > select * from glon ORDER BY create_time limit 0,4 +-+ | id | name | create_time | age | +-+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | +-+-- -+-+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time limit 4 4 +-+ | id | name | create_time | age | +-+ | 7 | Supreme Treasure | 2017-05 -02 14:00:00 | 20 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 12 | Huang Rong | 2017-05-03 08:10:10 | 19 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | +-+-- + 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time limit 8 4 +-+ | id | name | create_time | age | +-+ | 9 | Qiao Feng | 2017-05-03 13:10:10 | 22 | | 3 | Zhao Min | 0-05-03 14:10:10 | 17 | 10 | Duan Yu | 2017-05-03 15:10:10 | 19 | 11 | Guo Jing | 2017-05-03 17:10:10 | 20 | +-+ 4 rows in set (2017 sec)

According to the order of create_time, there are no more questions, and then:

Root@localhost [glon_ho] > select * from glon ORDER BY age limit 0,4 +-+ | id | name | create_time | age | +-+ | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +- -+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY age limit 4 4 +-+ | id | name | create_time | age | +-+ | 12 | Huangrong | 2017-05 -03 08:10:10 | 19 | 10 | Duan Yu | 2017-05-03 15:10:10 | 19 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 7 | Supreme Treasure | 2017-05-02 14:00:00 | 20 | +- -- + 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY age limit 8 4 +-+ | id | name | create_time | age | +-+ | 7 | to Zun Bao | 2017-05-02 14:00:00 | 20 | 9 | Qiao Feng | 2017-05-03 13:10:10 | 22 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +-+-- -+-+ 4 rows in set (0.00 sec)

You can see that the problem arises, sorted by age age.

Then take a look at the sort of combination:

Root@localhost [glon_ho] > select * from glon ORDER BY create_time,id limit 0,4 +-+ | id | name | create_time | age | +-+ | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +-+-- -+-+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time Id limit 4, 4 +-+ | id | name | create_time | age | +-+ | 7 | Supreme Treasure | 2017-05 -02 14:00:00 | 20 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 12 | Huang Rong | 2017-05-03 08:10:10 | 19 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | +-+-- + 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time Id limit 8, 4 +-+ | id | name | create_time | age | +-+ | 9 | Qiao Feng | 2017-05-03 13:10:10 | 22 | | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 10 | Duan Yu | 2017-05-03 15:10:10 | 19 | 11 | Guo Jing | 2017-05-03 17:10:10 | 20 | +-+ 4 rows in set (2017 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time Age limit 0, 4 +-+ | id | name | create_time | age | +-+ | 1 | Eason Chan | 2017-05 -02 08:10:10 | 19 | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 7 | Supreme Treasure | 2017-05-02 14:00:00 | 20 | +- -+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time Age limit 4, 4 +-+ | id | name | create_time | age | +-+ | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | 12 | Huangrong | 2017-05-03 08:10:10 | 19 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | +-+-- -- +-+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY create_time Age limit 8, 4 +-+ | id | name | create_time | age | +-+ | 9 | Qiao Feng | 2017-05-03 13:10:10 | 22 | | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 10 | Duan Yu | 2017-05-03 15:10:10 | 19 | 11 | Guo Jing | 2017-05-03 17:10:10 | 20 | +-+ 4 rows in set (2017 sec) root@localhost [glon_ho] > select * from glon ORDER BY age Id limit 0, 4 +-+ | id | name | create_time | age | +-+ | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +- -+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY age Id limit 4, 4 +-+ | id | name | create_time | age | +-+ | 8 | Liu Sanjie | 2017-05 -02 14:00:00 | 19 | 10 | Duan Yu | 2017-05-03 15:10:10 | 19 | 12 | Huangrong | 2017-05-03 08:10:10 | 19 | 7 | Supreme Treasure | 2017-05-02 14:00:00 | 20 | +- -- + 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon ORDER BY age Id limit 8, 4 +-+ | id | name | create_time | age | +-+ | 11 | Guo Jing | 2017-05-03 17:10:10 | 20 | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | 9 | Qiao Feng | 2017-05-03 13:10:10 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +-+-- -+ 4 rows in set (0.00 sec) thinking

Since the sort is uncertain, would it be useful to index the sort field?

Root@localhost [glon_ho] > alter table glon add index ix_age (age) Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0root@localhost [glon_ho] > show create table glon\ Graph * 1. Row * * Table: glonCreate Table: CREATE TABLE `glon` (`id`int (11) NOT NULL AUTO_INCREMENT, `name` varchar (20) NOT NULL, `create_ time` datetime NOT NULL `age` tinyint (3) unsigned DEFAULT'18 years, PRIMARY KEY (`id`), KEY `ix_ age` (`age`) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf81 row in set (0.00 sec) root@localhost [glon_ho] > select * from glon order by age limit 0Magne4 +-+ | id | name | create_time | age | +-+ | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +- -+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon order by age limit 4 +-+ | id | name | create_time | age | +-+ | 12 | Huangrong | 2017-05 -03 08:10:10 | 19 | 10 | Duan Yu | 2017-05-03 15:10:10 | 19 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 7 | Supreme Treasure | 2017-05-02 14:00:00 | 20 | +- -- + 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from glon order by age limit 8 +-+ | id | name | create_time | age | +-+ | 7 | to Zun Bao | 2017-05-02 14:00:00 | 20 | 9 | Qiao Feng | 2017-05-03 13:10:10 | 22 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +-+-- -+-+ 4 rows in set (0.00 sec)

It is also possible not to add an additional column after the order by to increase uniqueness, which can be rewritten as follows:

Root@localhost [glon_ho] > select * from (select distinct g.* from glon g order by age) t limit 0pr 4 +-+ | id | name | create_time | age | +-+ | 5 | Zhou Zhi Ruo | 2017-05-02 14:00:00 | 16 | 3 | Zhao Min | 2017-05-03 14:10:10 | 17 | 2 | Glon Ho | 2017-05-03 12:10:10 | 18 | | 1 | Eason Chan | 2017-05-02 08:10:10 | 19 | +- -+ 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from (select distinct g.* from glon g order by age) t limit 4 +-+ | id | name | create_time | age | +-+ | 10 | Duan Yu | 2017-05 -03 15:10:10 | 19 | 8 | Liu Sanjie | 2017-05-02 14:00:00 | 19 | 12 | Huang Rong | 2017-05-03 08:10:10 | 19 | 11 | Guo Jing | 2017-05-03 17:10:10 | 20 | +- -- + 4 rows in set (0.00 sec) root@localhost [glon_ho] > select * from (select distinct g.* from glon g order by age) t limit 8 +-+ | id | name | create_time | age | +-+ | 7 | to Zun Bao | 2017-05-02 14:00:00 | 20 | 9 | Qiao Feng | 2017-05-03 13:10:10 | 22 | | 4 | Jacky Cheung | 2017-05-02 14:00:00 | 22 | | 6 | Andy Lau | 2017-05-02 14:00:00 | 50 | +-+-- -+-+ 4 rows in set (0.00 sec)

In short, if it happens, the easiest way is to add an index on the sort (such as create time), and then express the primary key on order by, and the problem will be solved very satisfactorily.

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.