0.07s查询在mariadb上需要11.68s？

n8ghc7c1  于 2021-06-20  发布在  Mysql
关注(0)|答案(4)|浏览(672)

涉及到不同的硬件（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                                     |
+------+--------------+---------------+------+---------------------------------------------+-----------------------+---------+-------+-------+-------------------------------------------------+
sqlmysqlmariadbquery-optimizationexplain

4条答案

按热度按时间
8ljdwjyq

8ljdwjyq1#

目前尚不清楚为什么mariadb使用这种次优的执行计划。这可能是假设您的数据分布有问题（尽管我不确定在哪种情况下这是最佳方案）。使用它可能会有所帮助 optimize table card_legality, _cache_card; 修正统计数据。
如果没有，既然我们在评论中确定 (card_id,format_id) 是唯一的，我会尝试添加以下索引

CREATE UNIQUE INDEX uidx_card_legality ON card_legality(format_id, card_id)

使用

SELECT c.* 
FROM _cache_card AS c
JOIN card_legality l FORCE INDEX (uidx_card_legality)
ON l.card_id = c.id AND l.format_id = 35;

这基本上就是mysql当前执行查询的方式（在动态创建这个索引时），尽管您似乎用lukasz的答案尝试了这个索引，但它没有起作用。
你应该移除 force index （这只是为了确保mariadb没有做其他事情的余地）并检查mariadb/mysql是否仍然使用它。同时测试 format_id ，作为 35 可能是一个离群值（例如，您可能只有该格式的少数条目），为此优化执行计划可能会减慢对所有其他值的查询。当然，要确保您正在比较类似的结果集，就好像mariadb有10k个format35条目，而mysql没有，这不是公平的竞争。

赞(0）分享  回复(0）举报  2021-06-21
csga3l58

csga3l582#

产生这种差异的原因始于MySQL5.6和Mariadb10.0的问世——他们分别开发了几个改进的优化。你击中了一个涉及 IN 其中一个做出了另一个没有（也许还没有）得到的重大改进。
避免 IN ( SELECT ... ) 每当 JOIN 很实用。 EXISTS( SELECT 1 ... ) 是另一个要实验的构造。
索引：

PRIMARY KEY on every table !
card_legality:  INDEX(format_id, card_id) -- in this order
_cache_card:  (id)  -- This seems like a serious omission !

影响性能的因素：使用 *TEXT 当一个小人 VARCHAR 就够了。
计时时，运行查询两次。第一种方法将数据复制到ram（buffer\u pool）中；二是比较现实。
多少公羊？它的价值是什么 innodb_buffer_pool_size 每个人？

赞(0）分享  回复(0）举报  2021-06-21
rhfm7lfc

rhfm7lfc3#

IN 从优化的Angular 来看，这可能很棘手。我想知道这在两个系统上是否更有效：

SELECT c.* 
FROM _cache_card  c 
WHERE EXISTS (SELECT 1
              FROM card_legality cl
              WHERE cl.card_id = c.id AND format_id = 35
             );
赞(0）分享  回复(0）举报  2021-06-21
vfhzx4xs

vfhzx4xs4#

我将添加索引：

CREATE INDEX idx_name ON card_legality(format_id, card_id);

dbfiddle演示

SELECT * 
FROM _cache_card AS c 
WHERE c.id IN (SELECT card_id FROM card_legality WHERE format_id = 35);
               -- covering index created before
赞(0）分享  回复(0）举报  2021-06-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com