MySQL не использует индексы при соединении с другой таблицей

У меня есть две таблицы, первая таблица содержит все статьи /сообщения в блоге в CMS. Некоторые из этих статей могут также появляться в журнале, и в этом случае они имеют отношение внешнего ключа к другой таблице, которая содержит информацию о журнале.

Вот упрощенная версия синтаксиса create table для этих двух таблиц с выделенными несущественными строками:

CREATE TABLE `base_article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_published` datetime DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `content` longtext,
  `is_published` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `base_article_date_published` (`date_published`),
  KEY `base_article_is_published` (`is_published`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mag_article` (
    `basearticle_ptr_id` int(11) NOT NULL,
    `issue_slug` varchar(8) DEFAULT NULL,
    `rubric` varchar(75) DEFAULT NULL,
    PRIMARY KEY (`basearticle_ptr_id`),
    KEY `mag_article_issue_slug` (`issue_slug`),
    CONSTRAINT `basearticle_ptr_id_refs_id` FOREIGN KEY (`basearticle_ptr_id`) REFERENCES `base_article` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CMS содержит около 250 000 статей, и я написал простой скрипт Python , который может быть используется для заполнения тестовой базы данных выборочными данными, если они хотят реплицировать эту проблему локально.

Если я выберу из одной из этих таблиц, у MySQL нет проблем с выбором соответствующего индекса или быстрого поиска статей. Однако, когда две таблицы объединяются в простой запрос, например:

SELECT * FROM `base_article` 
INNER JOIN `mag_article` ON (`mag_article`.`basearticle_ptr_id` = `base_article`.`id`)
WHERE is_published = 1
ORDER BY `base_article`.`date_published` DESC
LIMIT 30

MySQL не может подобрать подходящий запрос и производительность. Ниже приведено соответствующее объяснение (время выполнения которого составляет более секунды):

+----+-------------+--------------+--------+-----------------------------------+---------+---------+----------------------------------------+-------+----------+---------------------------------+
| id | select_type |    table     |  type  |           possible_keys           |   key   | key_len |                  ref                   | rows  | filtered |              Extra              |
+----+-------------+--------------+--------+-----------------------------------+---------+---------+----------------------------------------+-------+----------+---------------------------------+
|  1 | SIMPLE      | mag_article  | ALL    | PRIMARY                           | NULL    | NULL    | NULL                                   | 23830 | 100.00   | Using temporary; Using filesort |
|  1 | SIMPLE      | base_article | eq_ref | PRIMARY,base_article_is_published | PRIMARY | 4       | my_test.mag_article.basearticle_ptr_id |     1 | 100.00   | Using where                     |
+----+-------------+--------------+--------+-----------------------------------+---------+---------+----------------------------------------+-------+----------+---------------------------------+
  • EDIT SEPT 30: я могу удалить предложение WHERE из этого запроса, но EXPLAIN по-прежнему выглядит одинаково, и запрос все еще медленный.

Одно потенциальное решение - заставить индекс. Выполнение того же запроса с помощью FORCE INDEX (base_articel_date_published) приводит к запросу, который выполняется примерно за 1.6 миллисекунды.

+----+-------------+--------------+--------+---------------+-----------------------------+---------+-------------------------+------+-----------+-------------+
| id | select_type |    table     |  type  | possible_keys |             key             | key_len |           ref           | rows | filtered  |    Extra    |
+----+-------------+--------------+--------+---------------+-----------------------------+---------+-------------------------+------+-----------+-------------+
|  1 | SIMPLE      | base_article | index  | NULL          | base_article_date_published |       9 | NULL                    |   30 | 833396.69 | Using where |
|  1 | SIMPLE      | mag_article  | eq_ref | PRIMARY       | PRIMARY                     |       4 | my_test.base_article.id |    1 | 100.00    |             |
+----+-------------+--------------+--------+---------------+-----------------------------+---------+-------------------------+------+-----------+-------------+

Я бы предпочел не форсировать индекс по этому запросу, если я могу его избежать, по нескольким причинам. В первую очередь этот базовый запрос может быть отфильтрован /изменен различными способами (например, с помощью issue_slug), после которого base_article_date_published больше не может быть лучшим индексом для использования.

Может ли кто-нибудь предложить стратегию повышения производительности для этого запроса?

10 голосов | спросил Joshmaker 30 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 30 Sep 2013 01:36:45 +0400 2013, 01:36:45

1 ответ


3

РЕАКТОР ЗАПРОС

SELECT * FROM
(SELECT * FROM base_article
WHERE is_published = 1
ORDER BY date_published LIMIT 30) A
INNER JOIN mag_article B
ON A.id = B.basearticle_ptr_id;

или

SELECT B.*,C.* FROM
(SELECT id FROM base_article
WHERE is_published = 1
ORDER BY date_published LIMIT 30) A
LEFT JOIN base_article ON A.id = B.id
LEFT JOIN mag_article C ON B.id = C.basearticle_ptr_id;

ИЗМЕНИТЬ ВАШИ ИНДЕКСЫ

ALTER TABLE base_article DROP INDEX base_article_is_published;
ALTER TABLE base_article ADD INDEX ispub_datepub_index (is_published,date_published);

ДАЙТЕ ЭТО ПОВРЕЖДЕНИЕ !!!

ответил RolandoMySQLDBA 30 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowMon, 30 Sep 2013 01:50:00 +0400 2013, 01:50:00

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132