0.07s запрос на MySQL занимает 11.68s на MariaDB?
Здесь задействовано другое оборудование (MySQL на моем ноутбуке, MariaDB на сервере), но обычно разница составляет максимум 2x, а не 166x!
Таблицы содержат одинаковые данные для каждого экземпляра (18 000 строк в _cache_card и 157 000 строк в card_legality).
ВОПРОС
SELECT * FROM _cache_card AS c
WHERE c.id IN (SELECT card_id FROM card_legality WHERE format_id = 35);
EXPLAIN
MariaDB:
+------+--------------+---------------+------+---------------------------------+-----------+---------+-------+-------+-------------------------------------------------+
| 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
(Выходные данные здесь идентичны.)
MariaDB:
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
ДРУГИЕ ДЕТАЛИ
MariaDB:
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
ИНДЕКС ДОБАВЛЕНИЯ НА card_legality(format_id,card_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 ответа
Неясно, почему 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 в настоящее время выполняет ваш запрос (при создании этого индекса на лету), хотя вы, кажется, пытались использовать этот индекс с ответом Лукаша, и он не работал.
Вы должны удалить force index (это просто для того, чтобы убедиться, что у MariaDB нет места для маневра, чтобы заняться чем-то другим) и проверить, использует ли MariaDB/MySQL это все еще. Также проверьте, что происходит для других значений format_id, как 35 может быть выбросом (например, у вас может быть несколько записей для этого формата), и оптимизация плана выполнения для этого может замедлить запрос для всех других значений. И, конечно же, убедитесь, что вы сравниваете похожие наборы результатов, как будто MariaDB имеет, например, 10 тыс. Записей для формата 35, в то время как в MySQL их нет, что не будет честной борьбой.
Причина разницы началась, когда вышли MySQL 5.6 и MariaDB 10.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_pool); второе реалистично для сравнения.
Сколько оперативки? Какова стоимость innodb_buffer_pool_size для каждого?
IN может быть сложно с точки зрения оптимизации. Интересно, работает ли это лучше на двух системах:
SELECT c.*
FROM _cache_card c
WHERE EXISTS (SELECT 1
FROM card_legality cl
WHERE cl.card_id = c.id AND format_id = 35
);
Я бы добавил индекс:
CREATE INDEX idx_name ON card_legality(format_id, card_id);
SELECT *
FROM _cache_card AS c
WHERE c.id IN (SELECT card_id FROM card_legality WHERE format_id = 35);
-- covering index created before