在MariaDB 11数据库中,我有以下表:
CREATE TABLE `tags` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`domain_id` int(10) unsigned NOT NULL DEFAULT 1,
`type` varchar(20) NOT NULL DEFAULT '',
`name` varchar(100) NOT NULL DEFAULT '',
`count` int(10) unsigned NOT NULL DEFAULT 0,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tags_type_index` (`type`),
KEY `tags_domain_id_foreign` (`domain_id`),
KEY `tags_name_index` (`name`),
KEY `tags_count_index` (`count`) USING BTREE,
CONSTRAINT `tags_domain_id_foreign` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
我的查询如下:
select
*
from
tags
where
type = 'tag'
and
domain_id = 95
limit 10;
此查询的总行数为:58,131(无限制)。表中的记录总数为1,466,914行。
我在我的数据库中使用MAX_JOIN_SIZE
来缓解复杂Web应用程序中查询优化不佳的问题。
我试图理解为什么这会导致错误:当我设置SET MAX_JOIN_SIZE = 100000;
时,“SELECT将检查超过MAX_JOIN_SIZE行”?
但它适用于:SET MAX_JOIN_SIZE = 200000;
。
我很好奇,因为首先这个查询中没有连接,其次type = 'tag' and domain_id = 95
的总行数是58,131。
2条答案
按热度按时间zour9fqk1#
即使结果集中的行数低于
max_join_size
的值,服务器也需要访问超过max_join_size
的行才能传递结果。让我们假设有两个相同的表t1和t2,其中有一个没有索引的整数列和1000个相同的唯一值。
SELECT a FROM t1 JOIN t2 WHERE t1.id=t2.id
将返回精确的一行,但要获得结果,服务器需要比较1000 x 1000个值。EXPLAIN输出显示服务器需要检查166160行,这超过了max_join_size的大小。
另请参阅sql_big_selects和max_join_size文档。
9avjhtql2#
查询执行只能使用一个索引来优化
WHERE
子句。根据您的EXPLAIN
结果,它选择使用domain_id
列上的索引。有166160行包含domain_id = 95
,它必须扫描所有这些行才能找到也包含type = 'tag'
的行。这比max_join_size
多,所以你得到一个错误。创建一个包含这两个列的复合索引,那么查询就可以完全满足索引。
如果这样做,就可以删除
type
上的索引,因为多列索引也可以作为其所有前缀的索引。