MySQL select * output result sequence with indexed columns _ MySQL

MySQL select * output result sequence in case of indexed columns bitsCN.com

MySQL select * output result sequence with indexed columns

Create a table, a primary key column, and an index column. Insert a batch of data and call select * from test_ B. it can be found that the output result is not sorted by Id, but by Type.

If you want to order by Id, you can use the force index (primary) hint statement.

[SQL]

Mysql> create table 'test _ B '(

-> 'Id' int (11) not null,

-> 'Type' int (11) default null,

-> Primary key ('id '),

-> KEY 'idx _ type' ('type ')

->) ENGINE = InnoDB default charset = utf8;

Query OK, 0 rows affected (0.20 sec)

Mysql> insert into test_ B values );

Query OK, 5 rows affected (0.09 sec)

Records: 5 Duplicates: 0 Warnings: 0

Mysql> select * from test_ B;

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

| Id | Type |

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

| 1 | 1 |

| 4 | 1 |

| 3 | 2 |

| 7 | 3 |

| 2 | 6 |

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

5 rows in set (0.03 sec)

Mysql> select * from test_ B force index (primary );

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

| Id | Type |

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

| 1 | 1 |

| 2 | 6 |

| 3 | 2 |

| 4 | 1 |

| 7 | 3 |

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

5 rows in set (0.00 sec)

Observe the first two results of select * from test_ B: (1, 1), (4, 1). when the Type is equal, sort by Id. To confirm this, insert more points for observation, with the same conclusion.

[SQL]

Mysql> insert into test_ B values (9, 3), (6, 3), (10, 3 );

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql> select * from test_ B;

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

| Id | Type |

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

| 1 | 1 |

| 4 | 1 |

| 3 | 2 |

| 6 | 3 |

| 7 | 3 |

| 9 | 3 |

| 10 | 3 |

| 2 | 6 |

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

8 rows in set (0.00 sec)

By default, why are the results sorted by index columns? This should start with the internal operation mechanism of the database. First, the system will query the index table (test_ B _indexed_type). The primary key of the index table is the index column type (usually to ensure the uniqueness of the primary key, an id suffix will be added after the type ), find the IDs through the index column, and then use these IDs to query the final result in test_ B. To be the most efficient, the index table will be scanned down along the type primary key, and then the scanned id will be used to request test_ B one by one, as a result, the results are ordered according to the index column.

When the values of the Type column are consistent, the data inserted into the index column can be inserted to the index table in the order of IDs to ensure that the data is sorted by Id when the types are consistent.

The above is the result of learning and thinking about OceanBase's implementation of secondary indexes and concurrent Get.

BitsCN.com