使 MySQL 使用主索引而不提示或強制

我有以下數據庫結構：

CREATE TABLE `question` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `level_id` int(11) NOT NULL,
 `video_id` int(11) NOT NULL,
 `is_active` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `question_b8f3f94a` (`level_id`),
 KEY `question_c11471f1` (`video_id`),
 CONSTRAINT `level_id_refs_id_27dd88d9` FOREIGN KEY (`level_id`) REFERENCES `level` (`id`),
 CONSTRAINT `video_id_refs_id_1c4fbe15` FOREIGN KEY (`video_id`) REFERENCES `video` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3054 DEFAULT CHARSET=utf8 

CREATE TABLE `level` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `success_rate` tinyint(3) unsigned NOT NULL,
 `name` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8

CREATE TABLE `video` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `file` varchar(200) DEFAULT NULL,
 `name` longtext NOT NULL,
 `subtitle` longtext NOT NULL,
 `producer` longtext,
 `director` longtext,
 `details` longtext,
 `related_content_url` longtext,
 `counter` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3055 DEFAULT CHARSET=utf8 

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `password` varchar(128) NOT NULL,
 `last_login` datetime NOT NULL,
 `is_superuser` tinyint(1) NOT NULL,
 `username` varchar(82) NOT NULL,
 `first_name` varchar(30) NOT NULL,
 `last_name` varchar(30) NOT NULL,
 `email` varchar(82) NOT NULL,
 `is_staff` tinyint(1) NOT NULL,
 `is_active` tinyint(1) NOT NULL,
 `date_joined` datetime NOT NULL,
 `is_verified` tinyint(1) NOT NULL,
 `verification_code` varchar(40) DEFAULT NULL,
 `birthday` date DEFAULT NULL,
 `gender` varchar(1) DEFAULT NULL,
 `language_id` int(11) DEFAULT NULL,
 `auth_token` varchar(40) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `username` (`username`),
 UNIQUE KEY `verification_code` (`verification_code`),
 KEY `user_784efa61` (`language_id`),
 KEY `email_idx` (`email`),
 CONSTRAINT `language_id_refs_id_016597a8` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=469322 DEFAULT CHARSET=utf8  


CREATE TABLE `star` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`question_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`counter` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `star_25110688` (`question_id`),
KEY `star_6340c63c` (`user_id`),
CONSTRAINT `question_id_refs_id_3c6023b7` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`),
CONSTRAINT `user_id_refs_id_4b270cea` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3737324 DEFAULT CHARSET=utf8 

我正在使用的 orm 為特定頁面生成以下查詢：

SELECT *
 FROM `star` 
 INNER JOIN `question` ON (`star`.`question_id` = `question`.`id`)
 INNER JOIN `level` ON (`question`.`level_id` = `level`.`id`)
 INNER JOIN `video` ON (`question`.`video_id` = `video`.`id`)
 INNER JOIN `user` ON (`star`.`user_id` = `user`.`id`)
 ORDER BY  `star`.`id` DESC 
LIMIT 10;

此查詢執行了很長時間。

+------+-------------+----------+--------+---------------------------------------------+-------------------+---------+----------------------------+------+---------------------------------+
| id   | select_type | table    | type   | possible_keys                               | key               | key_len | ref                        | rows | Extra                           |
+------+-------------+----------+--------+---------------------------------------------+-------------------+---------+----------------------------+------+---------------------------------+
|    1 | SIMPLE      | level    | ALL    | PRIMARY                                     | NULL              | NULL    | NULL                       |   24 | Using temporary; Using filesort |
|    1 | SIMPLE      | question | ref    | PRIMARY,question_b8f3f94a,question_c11471f1 | question_b8f3f94a | 4       | level.id          |   63 |                                 |
|    1 | SIMPLE      | video    | eq_ref | PRIMARY                                     | PRIMARY           | 4       | question.video_id |    1 |                                 |
|    1 | SIMPLE      | star     | ref    | PRIMARY,star_25110688,star_6340c63c         | star_25110688     | 4       | question.id       |  631 | Using index condition           |
|    1 | SIMPLE      | user     | eq_ref | PRIMARY                                     | PRIMARY           | 4       | star.user_id      |    1 |                                 |
+------+-------------+----------+--------+---------------------------------------------+-------------------+---------+----------------------------+------+---------------------------------+

“按star.id 排序”不使用主鍵。添加 use index(primary) 為我解決了這個問題。但我不想手動編寫查詢。

有沒有其他方法可以強制 mysql 使用主索引（使用 ordr by 等），而不需要手動提示或強制索引？

這是重寫的建議。也許可以說服您的 ORM 生成此查詢。它與原版僅略有不同。唯一的變化是派生表而不是基表star。由於表定義了外鍵並且查詢從 開始star，遵循外鍵，結果將是相同的：

SELECT *
 FROM 
     ( SELECT * FROM star ORDER BY id DESC LIMIT 10 )                -- the only change
     AS star  
 INNER JOIN `question` ON (`star`.`question_id` = `question`.`id`)
 INNER JOIN `level` ON (`question`.`level_id` = `level`.`id`)
 INNER JOIN `video` ON (`question`.`video_id` = `video`.`id`)
 INNER JOIN `user` ON (`star`.`user_id` = `user`.`id`)
 ORDER BY  `star`.`id` DESC 
LIMIT 10 ;

另一個想法是保留您的查詢，但將所有INNER聯接更改為LEFT聯接。這可能會產生一個不同的計劃，該計劃利用star. 並且由於外鍵，我們再次確定查詢是等效的並且會產生相同的結果。

---

與效率不完全相關：

• SELECT *不應該使用。僅添加您需要的列列表，而不是所有涉及的表的所有列。無論如何，您將如何在結果中確定該id列來自star.id或來自question.id或來自…？

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