涉及到不同的硬件(mysql在我的笔记本电脑上,mariadb在服务器上),但通常差异最多是2x而不是166x!
这些表在每个示例上都包含相同的数据(在\u cache\u card中有18000行,在\u card中有157000行)。
查询
SELECT * FROM _cache_card AS c
WHERE c.id IN (SELECT card_id FROM card_legality WHERE format_id = 35);
解释
马里亚布:
+------+--------------+---------------+------+---------------------------------+-----------+---------+-------+-------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+---------------+------+---------------------------------+-----------+---------+-------+-------+-------------------------------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 9414 | |
| 1 | PRIMARY | c | ALL | NULL | NULL | NULL | NULL | 18567 | Using where; Using join buffer (flat, BNL join) |
| 2 | MATERIALIZED | card_legality | ref | format_id,idx_card_id_format_id | format_id | 4 | const | 9414 | |
+------+--------------+---------------+------+---------------------------------+-----------+---------+-------+-------+-------------------------------------------------+
mysql数据库:
+----+--------------+---------------+------------+--------+---------------------------------+------------+---------+------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+---------------+------------+--------+---------------------------------+------------+---------+------------+-------+----------+-------------+
| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 18055 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 4 | cards.c.id | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | card_legality | NULL | ref | format_id,idx_card_id_format_id | format_id | 4 | const | 37828 | 100.00 | NULL |
+----+--------------+---------------+------------+--------+---------------------------------+------------+---------+------------+-------+----------+-------------+
创建表
两者:
CREATE TABLE `card_legality` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`card_id` int(11) NOT NULL,
`format_id` int(11) NOT NULL,
`legality` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `format_id` (`format_id`),
KEY `idx_card_id_format_id` (`card_id`,`format_id`,`legality`),
CONSTRAINT `card_legality_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `card` (`id`),
CONSTRAINT `card_legality_ibfk_2` FOREIGN KEY (`format_id`) REFERENCES `format` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1190863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
(这里的输出是字符对字符相同的。)
马里亚布:
CREATE TABLE `_cache_card` (
`id` int(11) NOT NULL DEFAULT 0,
`layout` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`face_id` int(11) NOT NULL DEFAULT 0,
`name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`mana_cost` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`cmc` double DEFAULT NULL,
`power` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`toughness` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`loyalty` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`type` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`text` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`search_text` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`image_name` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`hand` mediumtext DEFAULT NULL,
`life` mediumtext DEFAULT NULL,
`starter` mediumtext DEFAULT NULL,
`position` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`name_ascii` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`card_id` mediumtext DEFAULT NULL,
`names` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`legalities` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`pd_legal` int(1) DEFAULT NULL,
`bugs` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
KEY `idx_name_name` (`name`(142))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
mysql数据库:
CREATE TABLE `_cache_card` (
`id` int(11) NOT NULL DEFAULT '0',
`layout` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
`face_id` int(11) NOT NULL DEFAULT '0',
`name` longtext COLLATE utf8mb4_unicode_ci,
`mana_cost` mediumtext COLLATE utf8mb4_unicode_ci,
`cmc` double DEFAULT NULL,
`power` mediumtext COLLATE utf8mb4_unicode_ci,
`toughness` mediumtext COLLATE utf8mb4_unicode_ci,
`loyalty` mediumtext COLLATE utf8mb4_unicode_ci,
`type` longtext COLLATE utf8mb4_unicode_ci,
`text` mediumtext COLLATE utf8mb4_unicode_ci,
`search_text` mediumtext COLLATE utf8mb4_unicode_ci,
`image_name` mediumtext COLLATE utf8mb4_unicode_ci,
`hand` mediumtext CHARACTER SET utf8mb4,
`life` mediumtext CHARACTER SET utf8mb4,
`starter` mediumtext CHARACTER SET utf8mb4,
`position` mediumtext COLLATE utf8mb4_unicode_ci,
`name_ascii` longtext COLLATE utf8mb4_unicode_ci,
`card_id` mediumtext CHARACTER SET utf8mb4,
`names` mediumtext COLLATE utf8mb4_unicode_ci,
`legalities` mediumtext COLLATE utf8mb4_unicode_ci,
`pd_legal` int(1) DEFAULT NULL,
`bugs` mediumtext COLLATE utf8mb4_unicode_ci,
KEY `idx_name_name` (`name`(142))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
其他详细信息
马里亚布:
version=10.2.16-MariaDB
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
mysql数据库:
version=5.7.17
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
在卡片上添加索引(格式\u id,卡片\u id)
这会让事情变得更慢。
CREATE INDEX idx_format_id_card_id ON card_legality(format_id,card_id);
现在超过15秒。
解释(在mariadb上)说:
+------+--------------+---------------+------+---------------------------------------------+-----------------------+---------+-------+-------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+---------------+------+---------------------------------------------+-----------------------+---------+-------+-------+-------------------------------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 16942 | |
| 1 | PRIMARY | c | ALL | NULL | NULL | NULL | NULL | 17653 | Using where; Using join buffer (flat, BNL join) |
| 2 | MATERIALIZED | card_legality | ref | idx_card_id_format_id,idx_format_id_card_id | idx_format_id_card_id | 4 | const | 16942 | Using index |
+------+--------------+---------------+------+---------------------------------------------+-----------------------+---------+-------+-------+-------------------------------------------------+
4条答案
按热度按时间8ljdwjyq1#
目前尚不清楚为什么mariadb使用这种次优的执行计划。这可能是假设您的数据分布有问题(尽管我不确定在哪种情况下这是最佳方案)。使用它可能会有所帮助
optimize table card_legality, _cache_card;
修正统计数据。如果没有,既然我们在评论中确定
(card_id,format_id)
是唯一的,我会尝试添加以下索引使用
这基本上就是mysql当前执行查询的方式(在动态创建这个索引时),尽管您似乎用lukasz的答案尝试了这个索引,但它没有起作用。
你应该移除
force index
(这只是为了确保mariadb没有做其他事情的余地)并检查mariadb/mysql是否仍然使用它。同时测试format_id
,作为35
可能是一个离群值(例如,您可能只有该格式的少数条目),为此优化执行计划可能会减慢对所有其他值的查询。当然,要确保您正在比较类似的结果集,就好像mariadb有10k个format35条目,而mysql没有,这不是公平的竞争。csga3l582#
产生这种差异的原因始于MySQL5.6和Mariadb10.0的问世——他们分别开发了几个改进的优化。你击中了一个涉及
IN
其中一个做出了另一个没有(也许还没有)得到的重大改进。避免
IN ( SELECT ... )
每当JOIN
很实用。EXISTS( SELECT 1 ... )
是另一个要实验的构造。索引:
影响性能的因素:使用
*TEXT
当一个小人VARCHAR
就够了。计时时,运行查询两次。第一种方法将数据复制到ram(buffer\u pool)中;二是比较现实。
多少公羊?它的价值是什么
innodb_buffer_pool_size
每个人?rhfm7lfc3#
IN
从优化的Angular 来看,这可能很棘手。我想知道这在两个系统上是否更有效:vfhzx4xs4#
我将添加索引:
dbfiddle演示