mysql：按主鍵順序排列表

我有一個主鍵由兩個整數列組成的表。我在這張表中儲存了大約 2 億行。我的程序需要以相同的順序獲取按主鍵列排序的完整表。

所以，我的查詢看起來像Select * from MYTABLE order by PK1,PK2;

我注意到查詢不是立即發送數據，而是在“排序結果”狀態下花費了大量時間。我原以為mysql會使用主鍵索引以所需的順序直接訪問數據。但是，它似乎沒有順序地掃描它，然後對數據進行排序。

是否可以改變行為並使查詢更有效率？

備註：我嘗試過使用 mysql 5.5 和 mariadb10.2。

以下是一些（我希望）有用的資訊（我嘗試使用索引列和非索引列來解釋一些訂單，並且都給出相同的結果）

mysql> show indexes from reponse;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| reponse |          0 | PRIMARY     |            1 | objet_id    | A         |      502989 |     NULL | NULL   |      | BTREE      |         |               |
| reponse |          0 | PRIMARY     |            2 | question_id | A         |   189627157 |     NULL | NULL   |      | BTREE      |         |               |
| reponse |          1 | idxQuestion |            1 | question_id | A         |        5340 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0,30 sec)

mysql> explain select * from reponse order by objet_id,question_id;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627158 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,23 sec)

mysql> explain select * from reponse order by question_id,objet_id;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627159 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,44 sec)

mysql> explain select * from reponse order by objet_id,question_id;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627159 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,11 sec)

mysql> explain select * from reponse order by n_pose0;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627175 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,26 sec)

mysql> explain select * from reponse order by question_id;
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
|    1 | SIMPLE      | reponse | ALL  | NULL          | NULL | NULL    | NULL | 189627421 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+-----------+----------------+
1 row in set (0,12 sec)

mysql> select version();
+--------------------------------------+
| version()                            |
+--------------------------------------+
| 10.2.12-MariaDB-10.2.12+maria~jessie |
+--------------------------------------+

表 REPONSE 是這樣創建的：

   CREATE TABLE `reponse` (
 `objet_id` int(11) NOT NULL,
 `question_id` int(11) NOT NULL,
 `n_pose0` int(11) NOT NULL,
 `n_pose1` int(11) NOT NULL,
 `n_pose2` int(11) NOT NULL,
 `n_pose3` int(11) NOT NULL,
 `n_pose4` int(11) NOT NULL,
 PRIMARY KEY (`objet_id`,`question_id`),
 KEY `idxQuestion` (`question_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

作為給定答案的補充，您可以添加強制引擎使用索引的提示。我測試了一下，響應時間翻了一番……看來優化器這次做得不錯：

date;mysql --quick -e 'select * from reponse order by objet_id,question_id' > /dev/null;date
Tue Nov 20 15:49:22 CET 2018
Tue Nov 20 15:52:19 CET 2018
date;mysql --quick -e 'select * from reponse force index(PRIMARY) order by objet_id,question_id' > /dev/null;date
Tue Nov 20 15:52:45 CET 2018
Tue Nov 20 15:58:23 CET 2018

ENGINE=MyISAM，這就是選擇查詢計劃的原因。

如果表正在使用InnoDB引擎，那麼主鍵索引將是表的聚集鍵，因此它不必在讀取後進行排序，因為查詢ORDER BY匹配主鍵。

使用 MyISAM 它有兩個選項：讀取索引和（堆）表（不排序）或只讀取表並進行排序。它選擇第二個，因為優化器認為它更快。

引用自：https://dba.stackexchange.com/questions/222877