顺序不一致
示例一
表 item 建了 department_code + category 联合索引。
不带条件查询,默认数据结果以自增主键 id 顺序输出;带条件,输出的数据 id 却不是顺序的,而是按索引列的顺序输出。
mysql> SELECT i.id, i.department_code, i.category FROM item i LIMIT 10;
+----+-----------------+----------------------------+
| id | department_code | category |
+----+-----------------+----------------------------+
| 23 | 10500101 | complained |
| 24 | 10500101 | complained |
| 14 | 10500101 | COVID_19_NCP |
| 15 | 10500101 | COVID_19_NCP |
| 16 | 10500101 | COVID_19_NCP |
| 17 | 10500101 | COVID_19_NCP |
| 18 | 10500101 | COVID_19_NCP |
| 19 | 10500101 | COVID_19_NCP |
| 21 | 10500101 | COVID_19_NCP |
| 20 | 10500101 | history_of_present_illness |
+----+-----------------+----------------------------+
10 rows in set (0.06 sec)
mysql> SELECT i.id, i.department_code, i.category FROM item i WHERE i.department_code = '13100101' LIMIT 10;
+----+-----------------+-----------------+
| id | department_code | category |
+----+-----------------+-----------------+
| 40 | 13100101 | allergy_history |
| 37 | 13100101 | complained |
| 54 | 13100101 | complained |
| 55 | 13100101 | complained |
| 59 | 13100101 | complex_issues |
| 35 | 13100101 | COVID_19_NCP |
| 36 | 13100101 | COVID_19_NCP |
| 41 | 13100101 | COVID_19_NCP |
| 47 | 13100101 | COVID_19_NCP |
| 48 | 13100101 | COVID_19_NCP |
+----+-----------------+-----------------+
10 rows in set (0.05 sec)
示例二
下面的示例,id 是自增主键,查第 56,57,58 数据行,并不是按 id 递增顺序输出,原因是该表只有 4 个字段且除 id 外的其它三个字段建了 组合索引 `idx_orgCode_articleId_relateWordId
(org_code
,article_id
,relate_word_id
)` ,这样的话,select * 查询都会走索引,那么输出的排序就会按索引的字段排序规则输出。
mysql> select * from article_relate_word;
+----+----------+------------+----------------+
| id | org_code | article_id | relate_word_id |
+----+----------+------------+----------------+
| 1 | 74251 | 1 | 10 |
| 2 | 74251 | 1 | 11 |
| 3 | 74251 | 1 | 12 |
| 4 | 74251 | 1 | 13 |
| 5 | 74251 | 2 | 43 |
| 6 | 74251 | 2 | 44 |
| 7 | 74251 | 2 | 45 |
| 8 | 74251 | 2 | 46 |
| 10 | 74251 | 10 | 1 |
| 11 | 74251 | 10 | 2 |
| 12 | 74251 | 10 | 3 |
| 13 | 74251 | 11 | 1 |
| 14 | 74251 | 11 | 2 |
| 15 | 74251 | 11 | 3 |
| 55 | 74251 | 12 | 1 |
| 56 | 74251 | 12 | 2 |
| 58 | 74251 | 12 | 2 |
| 57 | 74251 | 12 | 3 |
| 31 | 74251 | 14 | 1 |
| 32 | 74251 | 14 | 2 |
| 33 | 74251 | 14 | 3 |
| 40 | 74251 | 17 | 1 |
| 41 | 74251 | 17 | 2 |
| 42 | 74251 | 17 | 3 |
+----+----------+------------+----------------+
24 rows in set (0.02 sec)
不走索引查询
不走索引的查询,结果默认以 rowid 顺序输出,如果有自增主键,可简单理解为按自增主键顺序输出,即按插入顺序输出。
走索引查询
如果查询的列走了索引,或带条件查询走了索引,就会按索引列的排序规则的顺序输出。该顺序不等于id自增顺序(插入顺序),如果需要按指定列排序,使用 order by 指定排序。
不带条件得到的结果数据的顺序跟查询列有关,根本是与查询列所使用的索引有关。
相关参考
更多内容请访问:IT源点
注意:本文归作者所有,未经作者允许,不得转载