mysql - 加入“2列”非常慢
问题描述
我有 2 张桌子,我需要INNER JOIN on 2 equal columns
这样:
SELECT
`table1`.*
FROM
`table1`
INNER JOIN `table2`
ON `table2`.`type` = `table1`.`type`
AND `table2`.`num` = `table1`.`num`
WHERE
`table2`.`another_int` = 1
ORDER BY
`table1`.`id` DESC
LIMIT 10 OFFSET 0
当我尝试时,查询需要1500ms
但是删除 2 个 JOIN 条件中的任何一个,或删除ORDER BY
将导致查询在1ms
更多信息:
表 2 有 1500 行,表 1 有 ~400,000 行
type 和 num 列都在两个表上都有索引,
id
表 1 上的(排序依据)也是主要的,因此被索引。type
:两个表上的 ENUM 具有完全相同的选项(6 个枚举选项)num
: 两个表上的无符号大整数
使用EXPLAIN
:两个表都使用键,但额外列显示表 2:“使用索引;使用临时;使用文件排序”
删除 WHERE 条件或 LIMIT OFFSET 没有效果,但我刚刚注意到,删除ORDER BY
同时保持 LIMIT 会导致查询在不到 1ms 的时间内运行
不知道这里有什么问题或我应该做什么,所以任何帮助表示赞赏......
编辑
/* Table 1 Keys */
KEY `posts_user_id_foreign` (`user_id`),
KEY `posts_composite_ind` (`post_followable_type`,`post_followable_id`,`id`) USING BTREE,
CONSTRAINT `posts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=456501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/* Table 2 Keys */
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_follows` (`user_id`,`followable_type`,`followable_id`),
CONSTRAINT `follows_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1525 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/* Query */
SELECT
`posts`.*, `follows`.`user_id` AS `current_user`
FROM
`posts`
INNER JOIN `follows`
ON `follows`.`followable_type` = `post_followable_type`
AND `follows`.`followable_id` = `post_followable_id`
WHERE
`follows`.`user_id` = 1
ORDER BY
`posts`.`id` DESC
LIMIT 11 OFFSET 0
/* Explain */
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|-------------|---------|------------|------|---------------------|---------------------|---------|-----------------------------------------------------|------|----------|----------------------------------------------|
| 1 | SIMPLE | follows | | ref | unique_user_follows | unique_user_follows | 8 | const | 511 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | posts | | ref | posts_composite_ind | posts_composite_ind | 9 | db.follows.followable_type,db.follows.followable_id | 453 | 100.00 | |
/* End */
解决方案
表2需要INDEX(another_int, num, type)
;another_int
必须是第一。但我猜你有。
摆脱follows.id
和提升的UNIQUE
关键是PRIMARY KEY
。
table1 需要INDEX(num, type)
;列可以按任意顺序排列。好的,我看到你有这样的索引。无需更改。
推荐阅读
- docker - 如何在docker centos7中安装gcc7
- rust - 未找到本地依赖项
- sql-server - 使用 dbmail sp 打印整个表
- r - 忽略每行中包含零的列并创建一个新对象
- ios - CVPixelBufferGetWidth(pixelbuffer) 和 CVPixelBufferGetWidthOfPlane(pixelbuffer, 0) 有什么区别?
- python - 在键列值相同的任何时候切换两行中的两个值
- tinymce - TinyMCE复制粘贴IE11
- javascript - 在现代 Firefox/Chrome/Edge 浏览器中对 PDF 进行数字签名
- sharepoint - 如何自动将视频添加到一页?
- javascript - 如何使用html中的箭头在不同部分显示下拉菜单