即使有索引，mysql 文件排序也会发生 -- 我该如何修复

Question

我下面有一个简单的查询，我可以确保表运行速度快。我对查询做了解释，它说“Using where;”使用文件排序。有没有办法摆脱文件排序？该数据现在只有大约 25 项；但最终可能会增加 300 或更多。

mysql> show create table phppos_categories;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_categories | CREATE TABLE `phppos_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `phppos_categories_ibfk_1` (`parent_id`),
  KEY `name` (`name`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM (`phppos_categories`) WHERE `parent_id` = 5 ORDER BY `name` asc;
+----+-----------+-------------------+
| id | parent_id | name              |
+----+-----------+-------------------+
|  3 |         5 | Basketball Shoes  |
|  7 |         5 | Basketball Shorts |
+----+-----------+-------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM (`phppos_categories`) WHERE `parent_id` = 5 ORDER BY `name` asc;
+----+-------------+-------------------+------+------------------------------------+--------------------------+---------+-------+------+-----------------------------+
| id | select_type | table             | type | possible_keys                      | key                      | key_len | ref   | rows | Extra                       |
+----+-------------+-------------------+------+------------------------------------+--------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | phppos_categories | ref  | phppos_categories_ibfk_1,parent_id | phppos_categories_ibfk_1 | 5       | const |    2 | Using where; Using filesort |
+----+-------------+-------------------+------+------------------------------------+--------------------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> 

Answer 1

您可以通过在 (parent_id, name) 上添加多列索引来删除此处的文件排序。

ALTER TABLE phppos_categories ADD INDEX (parent_id,name);

一般来说，当 MySQL 必须使用其中一个索引进行排序时，它只会使用单个索引(如果您只是使用两个索引来查询表，它可以使用索引合并来使用两个索引，但通常情况下这不是无论如何)。解决方案是创建一个涵盖您需要查询的所有列的单个索引。

其次，MySQL 只能对其使用的索引中的最后一列进行“范围”搜索或“排序”。之前的任何列都必须完全相等匹配。

在此基础上，我们可以首先使用 Parent_id 创建一个索引，该索引具有完全相等匹配 (=5)，然后使用名称(即您的顺序约束)创建索引。

要注意的主要事情是，您不想添加不必要的索引，并且适合每个可能的查询的索引可能并不明智，特别是考虑到保留索引所需的额外存储空间和工作最新。作为比较的一部分，您还需要考虑表的更新频率。如果很少更新，那么更多的索引可能不是一个问题。

请参阅此处了解更多信息: https://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html