Производительность запросов MySQL - огромная разница вовремя
У меня есть запрос, который возвращается за весьма различное количество времени между 2 наборами данных. Для одного набора (база данных A) это возвращается через несколько секунд для другого (база данных B).... хорошо я еще не достаточно долго ждал, но более чем 10 минут. Я вывел обе из этих баз данных к моей локальной машине, где я могу воспроизвести проблему рабочий MySQL 5.1.37.
Любопытно, база данных B меньше, чем база данных A.
Разделенный вниз версия запроса, который воспроизводит проблему:
SELECT * FROM po_shipment ps
JOIN po_shipment_item psi USING (ship_id)
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items
JOIN po_header ph ON pa.hdr_id = ph.hdr_id
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0'
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1'
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2'
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3'
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4'
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5'
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;
ОБЪЯСНИТЬ план запросов для первой базы данных (A), который возвращается через ~2 секунды:
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | ps | range | PRIMARY,IX_ETA_DATE | IX_ETA_DATE | 4 | NULL | 174 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev1 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev2 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev3 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev4 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev5 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | psi | ref | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1 | IX_po_shipment_item_po_shipment1 | 4 | UNIVIS_PROD.ps.ship_id | 1 | |
| 1 | SIMPLE | pa | ref | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | FK_po_alloc_po_shipment1 | 4 | UNIVIS_PROD.psi.ship_id | 5 | Using where |
| 1 | SIMPLE | ph | eq_ref | PRIMARY,IX_HDR_ID | PRIMARY | 4 | UNIVIS_PROD.pa.hdr_id | 1 | |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+
ОБЪЯСНИТЬ план запросов для второй базы данных (B), который возвращается в> 600 секунд:
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | ps | range | PRIMARY,IX_ETA_DATE | IX_ETA_DATE | 4 | NULL | 38 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | psi | ref | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1 | IX_po_shipment_item_po_shipment1 | 4 | UNIVIS_DEV01.ps.ship_id | 1 | |
| 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | |
| 1 | SIMPLE | ev1 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | |
| 1 | SIMPLE | ev2 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev3 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | |
| 1 | SIMPLE | ev4 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | |
| 1 | SIMPLE | ev5 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | |
| 1 | SIMPLE | pa | ref | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | IX_po_alloc_po_shipment_item2 | 4 | UNIVIS_DEV01.ps.ship_id | 4 | Using where |
| 1 | SIMPLE | ph | eq_ref | PRIMARY,IX_HDR_ID | PRIMARY | 4 | UNIVIS_DEV01.pa.hdr_id | 1 | |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+
Когда база данных B работает, я могу посмотреть на MySQL Administrator, и состояние остается при "Копировании к tmp таблице" неограниченно долго. База данных также имеет это состояние, но только в течение секунды или около этого.
Нет никаких различий в структуре таблицы, индексах, ключи и т.д. между этими базами данных (я сделал шоу, составляют таблицы и diff'd их).
Размеры таблиц:
database A:
po_shipment 1776
po_shipment_item 1945
po_alloc 36298
po_header 71642
EVENT_TABLE 1608
database B:
po_shipment 463
po_shipment_item 470
po_alloc 3291
po_header 56149
EVENT_TABLE 1089
Некоторые моменты, которые необходимо отметить:
- Удаление оператора Where делает возврат запроса <1 секундой.
- Удаление GROUP BY делает возврат запроса <1 секундой.
- Удаление ev5, ev4, ev3 и т.д. заставляет запрос стать быстрее для каждого удаленного.
ОБНОВЛЕНИЕ после ответа AJ: - размер ship_id значительно больше на базе данных B (макс. значение = 800002752), чем база данных (макс. значение = 3489). Учитывая, что это таблицы InnoDB, был бы, изменяя какую-либо буферную справку с обработкой ключей этого размера? Дальнейшее Обновление этого: Я уменьшил размер ключей и ПЕРЕПРОАНАЛИЗИРОВАЛ, но все еще никакое изменение в производительности.
ОБНОВИТЕ desc EVENT_TABLE:
Обратите внимание, что это идентично в обеих базах данных
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| EVENT_TABLE_ID | bigint(20) | NO | PRI | NULL | auto_increment |
| EVENT_TYPE | varchar(10) | NO | | NULL | |
| TABLE_ID1 | int(11) | NO | MUL | NULL | |
| TABLE_ID2 | int(11) | YES | | NULL | |
| TABLE_ID3 | int(11) | YES | | NULL | |
| TABLE_ID4 | int(11) | YES | | NULL | |
| EVENT_CREATED_DATE | datetime | NO | | NULL | |
| MESSAGE_REF | varchar(100) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
И в придачу ВЫСТАВОЧНЫЙ CREATE TABLE EVENT_TABLE:
Единственной вещью отличаться по этому между базами данных является автоматическое инкрементное значение
| EVENT_TABLE | CREATE TABLE `EVENT_TABLE` (
`EVENT_TABLE_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`EVENT_TYPE` varchar(10) NOT NULL,
`TABLE_ID1` int(11) NOT NULL,
`TABLE_ID2` int(11) DEFAULT NULL,
`TABLE_ID3` int(11) DEFAULT NULL,
`TABLE_ID4` int(11) DEFAULT NULL,
`EVENT_CREATED_DATE` datetime NOT NULL,
`MESSAGE_REF` varchar(100) DEFAULT NULL,
PRIMARY KEY (`EVENT_TABLE_ID`),
KEY `IX_EVENT_ID_EVENT_TYPE` (`TABLE_ID1`,`EVENT_TYPE`)
) ENGINE=InnoDB AUTO_INCREMENT=1925 DEFAULT CHARSET=utf8 |
Кто-либо может предложить, как решить этот вопрос? Что я пропустил?
ОБНОВЛЕНИЕМ после вопроса от Michael Holzmann Здесь являются новые Планы запросов на основе его обновленного запроса STRAIGHT_JOIN. Обратите внимание, что база данных B имеет "Используя временный файл; Используя filesort", тогда как теперь база данных A не делает. Это могло произойти из-за длинных ключей или чего-то подобного?
база данных A
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+-------------+
| 1 | SIMPLE | ps | index | PRIMARY,IX_ETA_DATE | PRIMARY | 4 | NULL | 168 | Using where |
| 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev1 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev2 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev3 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev4 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev5 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | psi | ref | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1 | IX_po_shipment_item_po_shipment1 | 4 | UNIVIS_PROD.ps.ship_id | 1 | |
| 1 | SIMPLE | pa | ref | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | FK_po_alloc_po_shipment_item1 | 8 | UNIVIS_PROD.psi.UID_items | 6 | Using where |
| 1 | SIMPLE | ph | eq_ref | PRIMARY,IX_HDR_ID | PRIMARY | 4 | UNIVIS_PROD.pa.hdr_id | 1 | |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+-------------+
база данных B
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | ps | range | PRIMARY,IX_ETA_DATE | IX_ETA_DATE | 4 | NULL | 38 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev1 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev2 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev3 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev4 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev5 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | |
| 1 | SIMPLE | psi | ref | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1 | IX_po_shipment_item_po_shipment1 | 4 | UNIVIS_DEV01.ps.ship_id | 1 | |
| 1 | SIMPLE | pa | ref | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | IX_po_alloc_po_shipment_item2 | 4 | UNIVIS_DEV01.ps.ship_id | 3 | Using where |
| 1 | SIMPLE | ph | eq_ref | PRIMARY,IX_HDR_ID | PRIMARY | 4 | UNIVIS_DEV01.pa.hdr_id | 1 | |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+-------------------------------+------+----------------------------------------------+
ОБНОВЛЕНИЕ Это - определенно связанные данные. Я вывел данные из базы данных A и загрузил его в использование базы данных B:
SELECT * from <table> into outfile <file>
и
LOAD DATA INFILE <file> into table <table>
Затем запрос базы данных B работает быстро - т.е. с такой скоростью, как база данных A. Какие-либо идеи о том, как диагностировать то, что могло быть неправильным с данными??
ОБНОВЛЕНИЕ @newtover: От базы данных A:
+-----------------+---------------------+
| eta_selectivity | ship_id_selectivity |
+-----------------+---------------------+
| 0.0693 | 1.0000 |
+-----------------+---------------------+
1 row in set (0.02 sec)
От базы данных B (плохая)
+-----------------+---------------------+
| eta_selectivity | ship_id_selectivity |
+-----------------+---------------------+
| 0.1814 | 1.0000 |
+-----------------+---------------------+
1 row in set (0.02 sec)
И шоу создает для po_shipment:
| po_shipment | CREATE TABLE `po_shipment` (
`ship_id` int(11) NOT NULL DEFAULT '0',
`ship_type` varchar(16) DEFAULT NULL,
`foreign_agent` varchar(16) DEFAULT NULL,
`agent_ref` varchar(16) DEFAULT NULL,
`exporter_code` varchar(30) DEFAULT NULL,
`importer_code` varchar(30) DEFAULT NULL,
`carrier_code` varchar(30) DEFAULT NULL,
`exporter_name` varchar(50) DEFAULT NULL,
`importer_name` varchar(50) DEFAULT NULL,
`carrier_name` varchar(50) DEFAULT NULL,
`receipt` varchar(30) DEFAULT NULL,
`pol_aol` varchar(50) DEFAULT NULL,
`pod_aod` varchar(30) DEFAULT NULL,
`final_dest` varchar(50) DEFAULT NULL,
`vessel_flno` varchar(30) DEFAULT NULL,
`ets` date DEFAULT NULL,
`eta` date DEFAULT NULL,
`pieces` int(11) DEFAULT '0',
`weight` decimal(17,2) DEFAULT '0.00',
`volume` decimal(17,2) DEFAULT '0.00',
`marks` varchar(500) DEFAULT NULL,
`goods_desc` varchar(500) DEFAULT NULL,
`ship_terms` varchar(16) DEFAULT NULL,
`ship_terms_desc` varchar(50) DEFAULT NULL,
`house_hawb` varchar(30) DEFAULT NULL,
`ocean_mawb` varchar(30) DEFAULT NULL,
`booking_date` date DEFAULT NULL,
`expected_cargo` date DEFAULT NULL,
`mfrt_jobdisp` varchar(30) DEFAULT NULL,
`ship_complete` date DEFAULT NULL,
`user_id` varchar(30) DEFAULT NULL,
`receipt_desc` varchar(60) DEFAULT NULL,
`fin_dest_desc` varchar(60) DEFAULT NULL,
`pol_aol_desc` varchar(60) DEFAULT NULL,
`pod_aod_desc` varchar(60) DEFAULT NULL,
`exporter_ref` varchar(26) DEFAULT NULL,
`carrier_ref` varchar(26) DEFAULT NULL,
`terms_conds` date DEFAULT NULL,
`last_amended` date DEFAULT NULL,
`user_amended` varchar(30) DEFAULT NULL,
`package_type` varchar(24) DEFAULT NULL,
`ext_cancelled` tinyint(1) NOT NULL DEFAULT '0',
`ext_goh` tinyint(1) NOT NULL DEFAULT '0',
`ext_arrival_date` date DEFAULT NULL,
`ext_booking_ref` varchar(255) DEFAULT NULL,
`ext_dc_booked_delivery_date` date DEFAULT NULL,
`ext_dc_booked_delivery_time` varchar(10) DEFAULT NULL,
`ext_comments` text,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
`last_amended_time` int(10) DEFAULT NULL,
`last_amended_uni` varchar(30) DEFAULT NULL,
PRIMARY KEY (`ship_id`),
KEY `IX_ETA_DATE` (`eta`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
ОБНОВИТЕ @chris_I, Если я разделяю запрос вниз путем удаления всех других соединений кроме EVENT_TABLE, я получаю ту же производительность (т.е. дрянной)
SELECT * FROM po_shipment ps
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0'
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1'
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2'
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3'
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4'
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5'
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;
ОБНОВЛЕНИЕ @Marcus Adams: Запрос для планов Вы попросили с удаленными внутренними объединениями:
SELECT * FROM po_shipment ps
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0'
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1'
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2'
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3'
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4'
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5'
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;
План запросов от базы данных (отвечает в 0,35 с),
+----+-------------+-------+-------+------------------------+------------------------+---------+------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------+------------------------+---------+------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | ps | range | IX_ETA_DATE | IX_ETA_DATE | 4 | NULL | 174 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev1 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev2 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev3 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev4 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
| 1 | SIMPLE | ev5 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | |
+----+-------------+-------+-------+------------------------+------------------------+---------+------------------------------+------+----------------------------------------------
План запросов от базы данных B (не отвечает вовремя, это берет для создания чашки чая),
+----+-------------+-------+-------+------------------------+------------------------+---------+-------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------+------------------------+---------+-------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | ps | range | IX_ETA_DATE | IX_ETA_DATE | 4 | NULL | 38 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36
14 ответов
Если это проблема с данными, я не могу сказать вам, в чем именно проблема, но вот моя любимая стратегия решения такого рода проблем:
Попробуйте удалить половину ваших объединений. Повторяйте рекурсивно, пока запрос не будет выполняться быстро. Затем добавьте половину объединений, которые вы удалили на последнем шаге ... (Эта стратегия потребует гораздо меньше шагов, чем удаление и добавление объединения по объединению.)
Как только вы обнаружите «плохое» соединение, вы можете попробовать ограничение его значений дополнительным условием «where» до тех пор, пока запрос снова не будет выполняться быстро ... На каждом шаге всегда старайтесь уменьшить проблему вдвое.
Примечание: вполне возможно, что вы получите намного больше записей для промежуточных результатов ваших объединений, даже если общий объем данных меньше в базе данных B.
Надо попробовать выполнить «ПРОВЕРИТЬ ТАБЛИЦУ» и / или «ОПТИМИЗИРОВАТЬ ТАБЛИЦУ» ?
У меня была аналогичная ситуация , где проблема заключалась в том, что ключевые индексы не обновлялись должным образом, и выполнение CHECK TABLE исправило их. Долгий шанс, но попробовать стоит.
Поскольку это InnoDB, похоже, что это проблема с блокировкой. Что еще происходит одновременно?
Обновите статистику. однажды у меня была аналогичная проблема, и это работает для меня.
Некоторое время не касался MySQL, но Я предполагаю, что проблема связана либо с
- . Вы проверили длину полей ключа / соединения (фактические данные), возможно, это привело к тому, что sort_buffer перешел на страницу с диском, соединяясь с большими (размерными) ключами? (это пахнет проблемой с данными ...)
- Настройки сервера, в основном это запись во временную таблицу в памяти. У меня было нечто подобное несколько лет назад. Вы увеличили размер key_buffer_size, table_cache, read_rnd_buffer_size, sort_buffer, read_buffer_size, чтобы посмотреть, помогает ли это?
Попробуйте добавить STRAIGHT_JOIN в запрос, чтобы узнать, не является ли план выполнения проблемой. Оптимизатор выбирает другой план выполнения для каждой базы данных, и это может быть причиной проблемы.
SELECT STRAIGHT_JOIN * FROM po_shipment ps
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0'
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1'
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2'
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3'
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4'
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5'
JOIN po_shipment_item psi USING (ship_id)
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items
JOIN po_header ph ON pa.hdr_id = ph.hdr_id
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;
ОБНОВЛЕНИЕ
1. Новому плану выполнения для базы данных A не требуется сортировка файлов или временная таблица, поскольку он использует первичный ключ. Я бы начал добавлять USE INDEX в запрос, чтобы увидеть, можете ли вы ускорить запрос к базе данных B. Является ли ship_id первичным ключом po_shipment? если да, то вам нужно выяснить, что стоит больше для группы, или с помощью фильтрации дат.
SELECT STRAIGHT_JOIN * FROM po_shipment ps USE INDEX( PRIMARY )
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0'
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1'
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2'
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3'
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4'
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5'
JOIN po_shipment_item psi USING (ship_id)
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items
JOIN po_header ph ON pa.hdr_id = ph.hdr_id
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
LIMIT 100;
Если это не помогает, попробуйте предложить больше индексов, используемых в плане выполнения базы данных A для базы данных B.
Поскольку кажется, что с данными возникла какая-то проблема, вероятно, было бы полезно выяснить, какие данные вызывают проблему. Создайте третью базу данных C и дважды вставьте половину данных из базы данных B (чтобы у вас было одинаковое количество строк). Если база данных C работает медленнее, чем плохие данные, в противном случае они находятся в другой половине. Повторите эти действия с меньшими и меньшими размерами блоков, чтобы найти данные о проблеме.
Несмотря на то, что база данных B меньше, чем база данных A, таблицы po_header и EVENT_TABLE не меньше пропорционально. Возможно, это как-то связано с разницей в скорости.
Для начала убедитесь, что вы создали индексы для соответствующих полей. Я уверен, что вы это сделали.
Затем попробуйте использовать подсказки индекса ( USE INDEX ), чтобы заставить базу данных правильно использовать индексы.
У меня была аналогичная проблема , в которой я предполагал, что индексы были правильно настроены и использовались mysql, но это не так. Мне удалось обойти это с помощью подсказок по индексу.
Я думаю, вы идете неверным путем. Когда вы выполняете LEFT JOIN, вы получаете обратно все записи в EVENT_TABLE независимо от совпадения с po_shipment.
И вы шесть раз запускали LEFT JOIN к EVENT_TABLE. Вы получаете обратно (6 * (SELECT count (*) FROM EVENT_TABLE)) записей с каждым запросом. Поскольку db A имеет меньше записей, чем db B, конечно, запрос выполняется быстрее на A.
Я думаю, что что-то вроде этого будет работать лучше:
DECLARE @TEMP_EVENT_TYPES table
(
EVENT_TYPE varchar(10) PRIMARY KEY
)
;
/*
INSERT VALUES 'MAS0', 'MAS1', 'MAS2', 'MAS3', 'MAS4', 'MAS5'
*/
;
SELECT * FROM po_shipment ps
JOIN po_shipment_item psi USING (ship_id)
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items
JOIN po_header ph ON pa.hdr_id = ph.hdr_id
INNER JOIN
EVENT_TABLE et ON et.TABLE_ID1 = ps.ship_id
INNER JOIN
@TEMP_EVENT_TYPES tet ON tet.event_type = et.EVENT_TYPE
WHERE ps.eta >= '2010-03-22'
GROUP BY ps.ship_id
Какова избирательность данных в po_shipment.eta и po_shipment.ship_id. Не могли бы вы опубликовать результаты следующего запроса из обеих баз данных:
SELECT
count(distinct eta)/count(*) as eta_selectivity,
count(distinct ship_id)/count(*) as ship_id_selectivity
FROM po_shipment;
Обычно, чем более избирательны данные поля (ближе к 1), тем лучше работает индекс. Причиной также может быть очень неравномерное распределение данных в po_shipment.eta (когда вы используете «2099-01-01» или подобное в качестве специального значения), если оптимизатору не хватает необходимой статистики.
Кстати, не могли бы вы предоставить po_shipment SHOW CREATE TABLE? Индексы на столе могут пролить свет.
UPD : Когда избирательность по полю настолько мала, как для поля эта , индекс в основном бесполезен. Хуже того, это может сбить оптимизатор с толку при выборе и замедлить вставку и обновление данных.
Первое предложение - попробовать удалить индекс в поле eta и измерить результаты. Может случиться так, что для базы данных A оптимизатор даже не пытается использовать индекс из-за очень низкой избирательности, а для базы данных B он дает попытку индекса.
Второе, что меня беспокоит, это почему вы вообще группируете по ship_id? Когда есть необходимость в неявной временной таблице и среди полей есть ТЕКСТ (как в вашем случае) или BLOB, MySQL всегда будет использовать временные таблицы на диске для сортировки (что неявно требуется в GROUP BY). В вашем случае ship_id - это кластерный первичный ключ, и результаты в любом случае будут отсортированы по ship_id.Что вам нужно, так это извлечь базовый запрос (уже сгруппированный по ship_id, если может быть несколько соответствий) и JOIN po_shipment с базовым запросом, применяющим ваше условие диапазона, а не с использованием группы by вверху.
И третье. Вам действительно нужны все поля, когда вы используете * вверху? Присоединившись к 10 столам, вы получите много фидов. Я не верю, что они вам нужны. Даже исключение поля ТЕКСТ из результатов может повысить производительность запроса.
Я думаю, что это может быть ручная сортировка (файловая сортировка) для предложения GROUP BY, которая вызывает заметный недостаток.
Попробуйте использовать подсказку SQL_BIG_RESULT, чтобы увидеть, улучшит ли MySQL свой метод обработки GROUP BY.
SELECT SQL_BIG_RESULT * FROM ...
Из любопытства - имеет ли исходные данные в B много NULL?
Похоже, вы моделируете конечный автомат для po_shipment с записями событий в event_table для каждого перехода между состояниями.
Какую бизнес-логику вы пытаетесь выразить с помощью этого запроса?
Для этого вы должны иметь возможность добавить поле состояния в свой po_shipment, которое суммирует и денормализует серию событий в event_table.
Построение логики для проверки того, что все 6 событий произошли для данной записи, не должно быть в базе данных, оно должно быть в вашей модели и сохраняться в базе данных (классический шаблон конечного автомата для модели).
Тогда просто выберите из po_shipment, где state = 'MAS5' и eta> date;
Я испытал то же самое при использовании селекторов диапазона меньше <и больше, чем > тоже.
Эксперимент: Если диапазон не слишком велик, пытались ли вы расширить диапазон до IN (..., ..., ...) инструкции instad?
Например,
SELECT * FROM po_shipment ps USE INDEX (IX_ETA_DATE)
JOIN po_shipment_item psi USING (ship_id)
JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items
JOIN po_header ph ON pa.hdr_id = ph.hdr_id
LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0'
LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1'
LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2'
LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3'
LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4'
LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5'
WHERE ps.eta IN ('2010-03-22','2010-03-21','2010-03-20',...)
GROUP BY ps.ship_id
LIMIT 100;
Изменить: Добавлена подсказка USE INDEX (), как предложил Салман А. Похоже, mysql видит возможный индекс, но решает не использовать его ... стоит проверить.