1, background descriptionI have a user table, the table structure is as follows: 
The index is as follows: 
The data in the table is as follows: 
You can see that the data in the table is sorted by lastname. This is because there is a multi-column index on lastname, familyname, city, and the primary key (index) on userid is added later, so userid is None sequential. Is there a way in mysql to regenerate (or organize) the index so that the query results of the statement "select userid, lastname from user" are sorted by userid by default?
Note: Instead of forcing the use of the index select userid, lastname, from user force index (primary), regenerate or organize the index (reorganize).
Updated on April 29
Actually, I want to ask if there is a command in mysql that can reorganize the existing indexes in the table. That is, after the table has been updated, deleted, and inserted for a period of time, the data will change (structure unchanged), then the indexes of the table will undergo various changes accordingly (such as underlying fragmentation, etc.). So is there a way to reorganize or regenerate these indexes? Otherwise, the indexes can only be deleted and rebuilt. index.
Update on May 2nd
I added a strikethrough to the original question. There are some problems with the initial question, especially the description here is wrong: "You can see The data in the table is sorted by lastname. This is because there is a multi-column index on lastname, familyname, city. The primary key (index) on userid is added later, so userid is unordered. You can see that the data in the table is Sorted by lastname, this is because there is a multi-column index on lastname, familyname, city, and the primary key (index) on userid is added later, so userid is unordered.", as for why it is unordered , @clcx_1315 has already answered, and he also pointed out that even if the index is reorganized (generated), the sentence select userid, lastname from user cannot achieve ordering of userid. I searched Baidu, and it seems that there is no command specifically used to regenerate or organize indexes in MySQL, but there is a command in SQL Server database. If you are interested, please Baidu.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
mysql has the usage of optimize table, but it also reorganizes the table at the same time.
If the system has obvious business peak periods, it is also possible to delete the index and rebuild it.
This situation is because the other columns other than your primary key are a composite index. When you query, mysql will directly go to the index table to find all your data (mysql innodb ordinary index will store the primary key information together), so it is based on your The first field of the index is sorted and retrieved. It does not read the entire table according to the order entered into the disk. The primary key is added according to the order entered into the disk, so it is sequential on the disk, but when you read it, you read the index table. It will be discontinuous. If you want to change this situation, it will be the same if you rebuild the table. It will still be sorted by the index of lastname, unless you enter it in the order of lastname. Another way is to add a redundant field, and then select all When looking at the table, you will not use your covering index query but a full table scan. Of course, the primary keys will be arranged in order.
I remember that mysql will also automatically rebuild the index based on your index situation, but nothing was found specifically.
Leave it to someone who is destined to answer