mysql - 慢内连接顺序查询

Question

我的查询速度有问题。问题类似于this one，但找不到解决方案。解释说明 MySQL 正在使用:Using index condition;在哪里使用；使用临时的；在公司表上使用文件排序。

Mysql slow query: INNER JOIN + ORDER BY causes filesort

慢查询:

SELECT * FROM companies 
    INNER JOIN post_indices
        ON companies.post_index_id = post_indices.id
    WHERE companies.deleted_at is NULL
    ORDER BY post_indices.id
    LIMIT 1;
# 1 row in set (5.62 sec)

但是如果我从查询中删除 where 语句，它真的很快:

SELECT * FROM companies
    INNER JOIN post_indices
        ON companies.post_index_id = post_indices.id
    ORDER BY post_indices.id
    LIMIT 1;
# 1 row in set (0.00 sec)

我已经尝试在 companies 表上使用不同的索引:

1. index_companies_on_deleted_at
2. index_companeis_on_post_index_id
3. index_companies_on_deleted_at_and_post_index_id
4. index_companies_on_post_index_id_and_deleted_at

index_companies_on_deleted_at 索引由 MySQL 自动选择。使用上述索引的相同查询的统计信息:

1. 5.6 秒
2. 3.4 秒
3. 8.5 秒
4. 3.5 秒

有什么提高查询速度的想法吗？再说一次 - 没有 where deleted_at is null 条件查询是即时的..

• 公司表有 130 万行。
• PostIndices 表有 3k 行。

更新 1:

Order by post_indices.id 是为了简单起见，因为它已经被编入索引。但它将用于连接表的其他列 (post_indices)。所以按 companies.post_index_id 排序不会解决这个问题

更新 2: 给 Rick James

您的查询只需 0.04 秒即可完成。并解释说使用了 index_companies_on_deleted_at_and_post_index_id 索引。所以是的，它工作得更好，但这并不能解决我的问题(需要在 post_indices 列上排序，将来会这样做，所以 id post_indices.id 用于简化示例。将来它例如 post_indices.city)。

我使用 WHERE 但没有使用 ORDER BY 的查询是即时的。

更新 3:

解释查询。我还注意到索引的顺序很重要。 index_companies_on_deleted_at 索引如果高于(较早创建的)则使用 index_companies_on_deleted_at_and_post_index_id。否则使用以后的索引。我的意思是 MySQL 自动选择。

mysql> EXPLAIN SELECT * FROM companies INNER JOIN post_indices ON post_indices.id = companies.post_index_id WHERE companies.deleted_at IS NULL ORDER BY post_indices.id LIMIT 1;
+----+-------------+--------------+------------+--------+----------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------------------------------------------------------+--------+----------+---------------------------------------------------------------------+
| id | select_type | table        | partitions | type   | possible_keys                                                                                                  | key                           | key_len | ref                                                  | rows   | filtered | Extra                                                               |
+----+-------------+--------------+------------+--------+----------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------------------------------------------------------+--------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | companies    | NULL       | ref    | index_companies_on_post_index_id,index_companies_on_deleted_at,index_companies_on_deleted_at_and_post_index_id | index_companies_on_deleted_at | 6       | const                                                | 638692 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | post_indices | NULL       | eq_ref | PRIMARY                                                                                                        | PRIMARY                       | 4       | enbro_purecrm_eu_development.companies.post_index_id |      1 |   100.00 | NULL                                                                |
+----+-------------+--------------+------------+--------+----------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------------------------------------------------------+--------+----------+---------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


mysql> EXPLAIN SELECT * FROM companies USE INDEX(index_companies_on_post_index_id) INNER JOIN post_indices ON post_indices.id = companies.post_index_id WHERE companies.deleted_at IS NULL ORDER BY post_indices.id LIMIT 1;
+----+-------------+--------------+------------+--------+----------------------------------+---------+---------+------------------------------------------------------+---------+----------+----------------------------------------------+
| id | select_type | table        | partitions | type   | possible_keys                    | key     | key_len | ref                                                  | rows    | filtered | Extra                                        |
+----+-------------+--------------+------------+--------+----------------------------------+---------+---------+------------------------------------------------------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | companies    | NULL       | ALL    | index_companies_on_post_index_id | NULL    | NULL    | NULL                                                 | 1277385 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | post_indices | NULL       | eq_ref | PRIMARY                          | PRIMARY | 4       | enbro_purecrm_eu_development.companies.post_index_id |       1 |   100.00 | NULL                                         |
+----+-------------+--------------+------------+--------+----------------------------------+---------+---------+------------------------------------------------------+---------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


mysql> EXPLAIN SELECT * FROM companies USE INDEX(index_companies_on_deleted_at_and_post_index_id) INNER JOIN post_indices ON post_indices.id = companies.post_index_id WHERE companies.deleted_at IS NULL ORDER BY post_indices.id LIMIT 1;
+----+-------------+--------------+------------+--------+-------------------------------------------------+-------------------------------------------------+---------+------------------------------------------------------+--------+----------+--------------------------------------------------------+
| id | select_type | table        | partitions | type   | possible_keys                                   | key                                             | key_len | ref                                                  | rows   | filtered | Extra                                                  |
+----+-------------+--------------+------------+--------+-------------------------------------------------+-------------------------------------------------+---------+------------------------------------------------------+--------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | companies    | NULL       | ref    | index_companies_on_deleted_at_and_post_index_id | index_companies_on_deleted_at_and_post_index_id | 6       | const                                                | 638692 |   100.00 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | post_indices | NULL       | eq_ref | PRIMARY                                         | PRIMARY                                         | 4       | enbro_purecrm_eu_development.companies.post_index_id |      1 |   100.00 | NULL                                                   |
+----+-------------+--------------+------------+--------+-------------------------------------------------+-------------------------------------------------+---------+------------------------------------------------------+--------+----------+--------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

更新 4:

我删除了不相关的列:

| companies | CREATE TABLE `companies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`post_index_id` int(11) DEFAULT NULL,
`vat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`note` text COLLATE utf8_unicode_ci,
`state` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'new',
`deleted_at` datetime DEFAULT NULL,
`lead_list_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_companies_on_vat` (`vat`),
KEY `index_companies_on_post_index_id` (`post_index_id`),
KEY `index_companies_on_state` (`state`),
KEY `index_companies_on_deleted_at` (`deleted_at`),
KEY `index_companies_on_deleted_at_and_post_index_id` (`deleted_at`,`post_index_id`),
KEY `index_companies_on_lead_list_id` (`lead_list_id`),
CONSTRAINT `fk_rails_5fc7f5c6b9` FOREIGN KEY (`lead_list_id`) REFERENCES `lead_lists` (`id`),
CONSTRAINT `fk_rails_79719355c6` FOREIGN KEY (`post_index_id`) REFERENCES `post_indices` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2523518 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

| post_indices | CREATE TABLE `post_indices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`county` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`postal_code` int(11) DEFAULT NULL,
`group_part` int(11) DEFAULT NULL,
`group_number` int(11) DEFAULT NULL,
`group_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3101 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

更新 5:

另一位开发人员在他的本地机器上使用完全相同的数据集(转储/恢复)测试了相同的查询。他得到了完全不同的解释:

mysql> explain SELECT * FROM companies      INNER JOIN post_indices         ON companies.post_index_id = post_indices.id     WHERE companies.deleted_at is NULL     ORDER BY post_indices.id     LIMIT 1;
+----+-------------+--------------+-------+----------------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+----------------------------------------------------+------+-----------------------+
| id | select_type | table        | type  | possible_keys                                                                                                  | key                                             | key_len | ref                                                | rows | Extra                 |
+----+-------------+--------------+-------+----------------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+----------------------------------------------------+------+-----------------------+
|  1 | SIMPLE      | post_indices | index | PRIMARY                                                                                                        | PRIMARY                                         | 4       | NULL                                               |    1 | NULL                  |
|  1 | SIMPLE      | companies    | ref   | index_companies_on_post_index_id,index_companies_on_deleted_at,index_companies_on_deleted_at_and_post_index_id | index_companies_on_deleted_at_and_post_index_id | 11      | const,enbro_purecrm_eu_development.post_indices.id |  283 | Using index condition |
+----+-------------+--------------+-------+----------------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+----------------------------------------------------+------+-----------------------+
2 rows in set (0,00 sec)

他的 PC 上的相同查询是即时的。不知道为什么会这样。我也尝试过使用 STRAIGHT_JOIN。当我强制 MySQL 首先读取 post_indices 表时，它也非常快。但这对我来说仍然是个谜，为什么同样的查询在另一台机器上很快(mysql -v 5.6.27)而在我的机器上很慢(mysql -v 5.7.10)

看来问题是 MySQL 使用了错误的表作为第一个要读取的表。

Answer 1

这样效果更好吗？

SELECT * FROM companies AS c
INNER JOIN post_indices AS pi
    ON c.post_index_id = pi.id
WHERE    c.deleted_at is NULL
ORDER BY c.post_index_id           -- Note
LIMIT 1;

INDEX(deleted_at, post_index_id)  -- note

就此而言，使用 WHERE 但不使用 ORDER BY 时，它的运行速度有多快？