Подтвердить что ты не робот

Медленный MySQL "INNER JOIN"

На веб-сайте я использую django для выполнения некоторых запросов:

Линия django:

CINodeInventory.objects.select_related().filter(ci_class__type='equipment',company__slug=self.kwargs['company'])

генерирует такой запрос MySQL:

SELECT *
FROM `inventory_cinodeinventory`
INNER JOIN `ci_cinodeclass` ON ( `inventory_cinodeinventory`.`ci_class_id` = `ci_cinodeclass`.`class_name` )
INNER JOIN `accounts_companyprofile` ON ( `inventory_cinodeinventory`.`company_id` = `accounts_companyprofile`.`slug` )
INNER JOIN `accounts_companysite` ON ( `inventory_cinodeinventory`.`company_site_id` = `accounts_companysite`.`slug` )
INNER JOIN `accounts_companyprofile` T5 ON ( `accounts_companysite`.`company_id` = T5.`slug` )
WHERE (
`ci_cinodeclass`.`type` = 'equipment'
AND `inventory_cinodeinventory`.`company_id` = 'thecompany'
)
ORDER BY `inventory_cinodeinventory`.`name` ASC

Проблема заключается в том, что для всего 40 000 записей в основной таблице требуется 0,5 секунды для обработки.

Я проверил все индексы, создаю те, которые необходимы для сортировки или объединения: у меня все еще проблема.

Самое смешное, что если я заменил последний INNER JOIN LEFT JOIN, запрос будет в 10 раз быстрее! К сожалению, поскольку я использую django для запроса, у меня нет доступа к запросам SQL, которые он генерирует (я не хочу сам делать сам SQL).

для последнего соединения как "INNER JOIN" EXPLAIN дает:

+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
| id | select_type | table                     | type   | possible_keys                                                                                            | key                                | key_len | ref                                            | rows  | Extra                           |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | accounts_companyprofile   | const  | PRIMARY                                                                                                  | PRIMARY                            | 152     | const                                          |     1 | Using temporary; Using filesort |
|  1 | SIMPLE      | inventory_cinodeinventory | range  | inventory_cinodeinventory_41ddcf59,inventory_cinodeinventory_543518c6,inventory_cinodeinventory_14fe63e9 | inventory_cinodeinventory_543518c6 | 152     | NULL                                           | 42129 | Using where                     |
|  1 | SIMPLE      | T5                        | ALL    | PRIMARY                                                                                                  | NULL                               | NULL    | NULL                                           |     3 | Using join buffer               |
|  1 | SIMPLE      | accounts_companysite      | eq_ref | PRIMARY,accounts_companysite_543518c6                                                                    | PRIMARY                            | 152     | cidb.inventory_cinodeinventory.company_site_id |     1 | Using where                     |
|  1 | SIMPLE      | ci_cinodeclass            | eq_ref | PRIMARY                                                                                                  | PRIMARY                            | 92      | cidb.inventory_cinodeinventory.ci_class_id     |     1 | Using where                     |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+

Для последнего соединения как "LEFT JOIN" я получил:

+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+---------+---------+------------------------------------------------+------+-------------+
| id | select_type | table                     | type   | possible_keys                                                                                            | key     | key_len | ref                                            | rows | Extra       |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+---------+---------+------------------------------------------------+------+-------------+
|  1 | SIMPLE      | accounts_companyprofile   | const  | PRIMARY                                                                                                  | PRIMARY | 152     | const                                          |    1 |             |
|  1 | SIMPLE      | inventory_cinodeinventory | index  | inventory_cinodeinventory_41ddcf59,inventory_cinodeinventory_543518c6,inventory_cinodeinventory_14fe63e9 | name    | 194     | NULL                                           |  173 | Using where |
|  1 | SIMPLE      | accounts_companysite      | eq_ref | PRIMARY                                                                                                  | PRIMARY | 152     | cidb.inventory_cinodeinventory.company_site_id |    1 |             |
|  1 | SIMPLE      | T5                        | eq_ref | PRIMARY                                                                                                  | PRIMARY | 152     | cidb.accounts_companysite.company_id           |    1 |             |
|  1 | SIMPLE      | ci_cinodeclass            | eq_ref | PRIMARY                                                                                                  | PRIMARY | 92      | cidb.inventory_cinodeinventory.ci_class_id     |    1 | Using where |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+---------+---------+------------------------------------------------+------+-------------+

Кажется, что в случае "INNER JOIN" MySQL не находит индексов для соединения T5: почему?

Профилирование дает:

starting                            0.000011
checking query cache for query  0.000086
Opening tables                  0.000014
System lock                     0.000005
Table lock                          0.000052
init                            0.000064
optimizing                          0.000021
statistics                          0.000180
preparing                           0.000024
Creating tmp table                  0.000308
executing                           0.000003
Copying to tmp table            0.353414   !!!
Sorting result                  0.037244
Sending data                    0.035168
end                             0.000005
removing tmp table                  0.550974   !!!
end                             0.000009
query end                           0.000003
freeing items                   0.000113
storing result in query cache   0.000009
logging slow query                  0.000002
cleaning up                     0.000004

Итак, кажется, есть шаг, когда mysql использует временную таблицу. Этот шаг не происходит с LEFT JOIN, только с INNER JOIN. Я попытался избежать этого, включив в запрос "индекс силы для соединения", но это не помогло...

объединяются следующие таблицы:

CREATE TABLE IF NOT EXISTS `accounts_companysite` (
  `slug` varchar(50) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `name` varchar(128) NOT NULL,
  `address` longtext NOT NULL,
  `city` varchar(64) NOT NULL,
  `zip_code` varchar(6) NOT NULL,
  `state` varchar(32) NOT NULL,
  `country` varchar(2) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `fax` varchar(20) NOT NULL,
  `more` longtext NOT NULL,
  PRIMARY KEY (`slug`),
  KEY `accounts_companysite_543518c6` (`company_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `accounts_companyprofile` (
  `slug` varchar(50) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `name` varchar(128) NOT NULL,
  `address` longtext NOT NULL,
  `city` varchar(64) NOT NULL,
  `zip_code` varchar(6) NOT NULL,
  `state` varchar(32) NOT NULL,
  `country` varchar(2) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `fax` varchar(20) NOT NULL,
  `contract_id` varchar(32) NOT NULL,
  `more` longtext NOT NULL,
  PRIMARY KEY (`slug`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `inventory_cinodeinventory` (
  `uuid` varchar(36) NOT NULL,
  `name` varchar(64) NOT NULL,
  `synopsis` varchar(64) NOT NULL,
  `path` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `root_id` varchar(36) DEFAULT NULL,
  `parent_id` varchar(36) DEFAULT NULL,
  `order` int(11) NOT NULL,
  `ci_class_id` varchar(30) NOT NULL,
  `data` longtext NOT NULL,
  `serial` varchar(64) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `company_site_id` varchar(50) NOT NULL,
  `vendor` varchar(48) NOT NULL,
  `type` varchar(64) NOT NULL,
  `model` varchar(64) NOT NULL,
  `room` varchar(30) NOT NULL,
  `rack` varchar(30) NOT NULL,
  `rack_slot` varchar(30) NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `inventory_cinodeinventory_1fb5ff88` (`root_id`),
  KEY `inventory_cinodeinventory_63f17a16` (`parent_id`),
  KEY `inventory_cinodeinventory_41ddcf59` (`ci_class_id`),
  KEY `inventory_cinodeinventory_543518c6` (`company_id`),
  KEY `inventory_cinodeinventory_14fe63e9` (`company_site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Я также попытался настроить MySQL, добавив в my.cnf:

join_buffer_size        = 16M
tmp_table_size          = 160M
max_seeks_for_key       = 100

... но это не помогает.

С django легко использовать Postgresql вместо Mysql, поэтому я попробовал: с тем же запросом и с теми же данными в db, postgres намного быстрее, чем Mysql: x10 быстрее, используя INNER JOIN (анализ показывает, что он использует индексы в отличие от Mysql)

У вас есть идея, почему мой MySQL INNER JOIN настолько медленный?

ИЗМЕНИТЬ 1:

после некоторого тестирования я уменьшаю проблему до этого запроса:

SELECT *
FROM `inventory_cinodeinventory`
INNER JOIN `accounts_companyprofile` ON `inventory_cinodeinventory`.`company_id` = `accounts_companyprofile`.`slug`
ORDER BY `inventory_cinodeinventory`.`name` ASC

Этот запрос выполняется очень медленно, и я не понимаю, почему. Без предложения ORDER BY это быстро, но не с ним, хотя, индекс имени установлен:

CREATE TABLE IF NOT EXISTS `inventory_cinodeinventory` (
  `uuid` varchar(36) NOT NULL,
  `name` varchar(64) NOT NULL,
  `synopsis` varchar(64) NOT NULL,
  `path` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `root_id` varchar(36) DEFAULT NULL,
  `parent_id` varchar(36) DEFAULT NULL,
  `order` int(11) NOT NULL,
  `ci_class_id` varchar(30) NOT NULL,
  `data` longtext NOT NULL,
  `serial` varchar(64) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `company_site_id` varchar(50) NOT NULL,
  `vendor` varchar(48) NOT NULL,
  `type` varchar(64) NOT NULL,
  `model` varchar(64) NOT NULL,
  `room` varchar(30) NOT NULL,
  `rack` varchar(30) NOT NULL,
  `rack_slot` varchar(30) NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `inventory_cinodeinventory_1fb5ff88` (`root_id`),
  KEY `inventory_cinodeinventory_63f17a16` (`parent_id`),
  KEY `inventory_cinodeinventory_41ddcf59` (`ci_class_id`),
  KEY `inventory_cinodeinventory_14fe63e9` (`company_site_id`),
  KEY `inventory_cinodeinventory_543518c6` (`company_id`,`name`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ИЗМЕНИТЬ 2:

Предыдущий запрос может быть разрешен с помощью "FORCE INDEX FOR ORDER BY (name)". К сожалению, этот совет не работает с первоначальным запросом в моей теме...

ИЗМЕНИТЬ 3:

Я восстановил базу данных с заменой первичных ключей uuid от varchar на целое число: это вообще не помогает... плохие новости.

ИЗМЕНИТЬ 4:

Я попробовал Mysql 5.5.20: не лучше. Postgresql 8.4 в 10 раз быстрее для этого конкретного запроса.

Я немного изменил условие resquest (удалил соединение T5):

SELECT *
FROM `inventory_cinodeinventory`
INNER JOIN `ci_cinodeclass` ON ( `inventory_cinodeinventory`.`ci_class_id` = `ci_cinodeclass`.`class_name` )
INNER JOIN `accounts_companyprofile` ON ( `inventory_cinodeinventory`.`company_id` = `accounts_companyprofile`.`slug` )
INNER JOIN `accounts_companysite` ON ( `inventory_cinodeinventory`.`company_site_id` = `accounts_companysite`.`slug` )
WHERE (
`ci_cinodeclass`.`type` = 'equipment'
AND `inventory_cinodeinventory`.`company_id` = 'thecompany'
)
ORDER BY `inventory_cinodeinventory`.`name` ASC

Это работает нормально, но у меня есть другие запросы, немного разные, если этот трюк не работает.

Фактически, после поиска кажется, что, как только вы присоединитесь к двум таблицам, которые имеют "много общего", то есть, допустим, половина строк правой таблицы может быть присоединена к тем, которые находятся в левой таблице (это это мой случай): Mysql предпочитает использовать сканирование таблицы вместо индекса: быстрее я нашел где-нибудь (!!)

4b9b3361

ОТВЕТЫ

Ответ 1

Ваша настоящая проблема связана со второй строкой в ​​вашем первом объяснении:

+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
| id | select_type | table                     | type   | possible_keys                                                                                            | key                                | key_len | ref                                            | rows  | Extra                           |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+   
|  1 | SIMPLE      | inventory_cinodeinventory | range  | inventory_cinodeinventory_41ddcf59,inventory_cinodeinventory_543518c6,inventory_cinodeinventory_14fe63e9 | inventory_cinodeinventory_543518c6 | 152     | NULL                                           | 42129 | Using where                     |

Вы анализируете строки 42129, используя это предложение WHERE:

AND `inventory_cinodeinventory`.`company_id` = 'thecompany'

Если у вас его еще нет, вы должны иметь индекс для inventory_cinodeinventory для (company_id, name)

то есть.

ALTER TABLE `inventory_cinodeinventory`
  ADD INDEX `inventory_cinodeinventory__company_id__name` (`company_id`, `name`);

Таким образом, ваши предложения WHERE и ORDER BY не конфликтуют друг с другом, вызывая неправильный выбор индекса, который, похоже, происходит прямо сейчас.

Если у вас уже есть индекс с этими столбцами, в этом порядке я бы предложил запустить OPTIMIZE TABLE inventory_cinodeinventory;, чтобы узнать, использует ли он MySQL правильный индекс.

В общем, у вас большая проблема (я полагаю, это связано с дизайном Django, но мне не хватает опыта использования этой структуры), поскольку у вас есть эти огромные ключи. Все ключи в вашем EXPLAIN имеют длину 152 и 92 байта. Это приводит к значительно большему индексу, что означает больший объем доступа к диску, что означает более медленные запросы. Первичный и внешний ключи в идеале должны быть int или очень короткие столбцы varchar (например, varchar (10)). varchar(50) для этих ключей будет помещен значительный постоянный множитель на время ответа БД.

Ответ 2

Как заметил заметный компилятор, у меня определенно будет индекс в вашей первой таблице на основе идентификатора и имени компании (так что часть имени оптимизирована для предложения order by).

Хотя я тоже ничего не делал с django, еще одно оптимизирующее ключевое слово MySQL - "STRAIGHT_JOIN", которое сообщает оптимизатору выполнить запрос в том порядке, в котором вы ему сказали. ex:

SELECT STRAIGHT_JOIN * FROM ...

В обоих случаях ваших запросов "Объяснить" это по какой-то причине зацикливается на факте, что файл companyprofile является одной записью и может попытаться использовать THAT в качестве основы для соединения и в противном случае обработать стек. Выполняя straight_join, вы говорите MySQL, что вы ЗНАЕТ, что основная таблица - "Inventory_CINodeInventory" и использует ее сначала... другие таблицы - это скорее таблица поиска или "ссылки" других простых элементов, которые вы также хотите. Я видел только одно ключевое слово для запроса, который не будет выполняться полностью (убил задачу через 30 часов), против данных gov't контрактов более 14 миллионов записей менее чем за 2 часа... NOTHING ELSE в запросе изменен, только этот КЛЮЧЕВОЙ. (но обязательно включите другой индекс, если он еще не был выполнен).

КОММЕНТАРИЙ за последние изменения до вопроса...

Вы упомянули, что запрос SLOW с порядком, но FAST без него. Сколько записей фактически возвращается из набора результатов. Другая тактика, которую я использовал раньше, - это обернуть запрос как выбор, чтобы просто вернуть ответ, а затем применить порядок к результатам OUTER... Что-то вроде

select *
   from 
      ( select your Entire Query
           from ...
           Without The Order by clause 
      ) as FastResults
   order by
      FastResults.Name

Это, вероятно, выходит из автозадания django вашего SQL-оператора, но стоит попробовать для доказательства концепции. У вас уже есть рабочий синтаксис для запуска, я бы дал этот снимок.

Ответ 3

Я заметил, что вы используете:

ДВИГАТЕЛЬ = MyISAM

Просто догадайтесь, но вы можете попытаться переключить движок таблицы в InnoDB. Это быстрее, если используется с несколькими запросами на объединение.

ДВИГАТЕЛЬ = InnoDB

Двигатель InnoDB не может использоваться для полнотекстового поиска, но существует большая разница с общей производительностью.

Ответ 4

Вы можете попытаться использовать представление при доступе к данным: 

CREATE VIEW v AS SELECT *
FROM inventory_cinodeinventory
LEFT JOIN ci_cinodeclass ON ( inventory_cinodeinventory.ci_class_id = ci_cinodeclass.class_name )
LEFT JOIN accounts_companyprofile ON ( inventory_cinodeinventory.company_id = accounts_companyprofile.slug )
LEFT JOIN accounts_companysite ON ( inventory_cinodeinventory.company_site_id = accounts_companysite.slug )
LEFT JOIN accounts_companyprofile T5 ON ( accounts_companysite.company_id = T5.slug )
ORDER BY inventory_cinodeinventory.name ASC

Недостатком здесь является то, что вам нужно написать "чистый sql" на сервере. И вы должны создать модель для этого нового представления.

Edit:
Вы также можете создать представление с внутренними соединениями. Это также может быть быстрее, чем непосредственно запрашивать таблицу.

CREATE VIEW v AS SELECT *
FROM inventory_cinodeinventory
INNER JOIN ci_cinodeclass ON ( inventory_cinodeinventory.ci_class_id = ci_cinodeclass.class_name )
INNER JOIN accounts_companyprofile ON ( inventory_cinodeinventory.company_id = accounts_companyprofile.slug )
INNER JOIN accounts_companysite ON ( inventory_cinodeinventory.company_site_id = accounts_companysite.slug )
INNER JOIN accounts_companyprofile T5 ON ( accounts_companysite.company_id = T5.slug )
ORDER BY inventory_cinodeinventory.name ASC

Ответ 5

Сделайте свои ключи соединения в int unsigned

и добавьте inventory_cinodeinventory. ci_class_id > 0 (ci_class_id__gt = 0) (то же самое с остальными ключами в соединениях), где

Он укажет MySQL на ваши ключи, сохранив их в стиле ORM django

Ответ 6

Я применил исправление для INNER JOIN для Django ORM, он будет использовать STRAIGHT_JOIN в случае заказа с INNER JOINs. Я поговорил с разработчиками Django, и мы решили сделать это как отдельный бэкэнд на данный момент. Итак, вы можете проверить это здесь: https://pypi.python.org/pypi/django-mysql-fix