Innodb table select query order, innodbselect Query

Innodb table select query order, innodbselect Query

Today, a student from zhishutang reported that in the optimization class, the teacher told me that Innodb is stored in the primary key order, and the read time is read in the primary key order. However, an exception is found, as shown below:

CREATE TABLE zst_t1 ( uid int(10) NOT NULL AUTO_INCREMENT, id int(11) NOT NULL, PRIMARY KEY ( uid ), KEY idx_id ( id ) ) ENGINE=InnoDB；'

Write Data:

INSERT INTO zst_t1 VALUES (1,1),(12,1),(22,1),(23,1),(33,1),(2,2),(3,2),(10,2),(11,2),(4,4),(13,4),(14,4); 

Run the query:

select * from zst_t1;

Why is the order disordered? Isn't Innodb tables fully stored by primary keys?

Use the innodb_ruby tool to check the storage structure.

It seems that the storage is still sorted by the primary key. No problem.

Let's take a look at the index of the table:

I should have understood how it happened here. The original query was covered by indexes and the original data was not read back into the table. In addition, the Innodb Binary Index contains primary key storage.

To continue to prove:

If you see using index, it indicates that the query uses the index to query the results without reading the original table.

Then we create an operation to read data through the primary key:

select * from zst_t1 use index(primary);

select * from zst_t1 use index(primary);  # Confirm.

Summary:

This is actually a query case included in the index. If you calm down and think about it, you may soon understand it. You do not need to check the problem.

The technology is hard to solve. If you do more, you will understand it :).