скорость увеличения для MySQL JOIN для двух больших таблиц
Я должен СОЕДИНИТЬ с большими таблицами в запросе MySQL, и он берет действительно долго - приблизительно 180 секунд. Есть ли какие-либо подсказки для оптимизации слияния?
Моя таблица имеет 10 полей. Я только использую 4 в запросе - все строки. Таблица имеет приблизительно 600 000 строк, и результат должен иметь приблизительно 50 строк.
Четыре используемых строки: Заголовок, Переменные, Местоположение, Дата
Вот мой запрос:
SELECT DISTINCT t1.Title, t1.Variables FROM `MyTABLE` t1 JOIN `MyTABLE` t2
USING (Title, Variables)
WHERE (t1.Location, t1.Date) = ('Location1', 'Date1')
AND (t2.Location, t2.Date) = ('Location2', 'Date2')
7 ответов
] Как указывали другие, нужны правильные индексы. Для этого конкретного запроса вы можете использовать такие индексы, как:[
] []([]Location, Date[]) или ([]Date, Location[]) (для пункта []WHERE[]).
и
([]Название, переменные[]) или ([]Переменные, название[]) (для условия []join[], []ON[] clause)[
]Было бы полезно точно знать размер (то есть тип данных) столбцов расположения, даты, названия и переменных, так как большой индекс, скорее всего, будет медленнее маленького. [
] [] Наконец, просто совет: я бы не стал использовать причудливые конструкции сравнения, как вы. Скорее всего, [
] [USING (Title, Variables)
] подойдет, но я бы, конечно, проверил, ведут ли себя [
] [(t1.Location, t1.Date) = ('Location1', 'Date1')
] и [
] [(t2.Location, t2.Forecast_date) = ('Location2', 'Date2')
] так, как вы ожидаете. Так что я бы определенно запустил []EXPLAIN[] и сравнил результат с "обычным" старомодным сравнением, например:[
t1.Location = 'Location1'
AND t1.Date = 'Date1'
AND t2.Location = 'Location2'
AND t2.Forecast_date = 'Date2'
]Вы можете утверждать, что логически это одно и то же, и это не должно иметь значения - вы были бы правы. Но опять же, оптимизатор MySQL не очень умён, и всегда есть вероятность появления ошибок, особенно с редко используемыми функциями. Я думаю, что это такая возможность. Поэтому я бы хотя бы попробовал EXPLAIN и посмотрел, оцениваются ли эти альтернативные нотации одинаково.[
] []Но, как отметил Бенокрапо, не проще ли было бы сделать что-то подобное:[
] [SELECT Title, Variables
FROM MyTABLE
WHERE Location = 'Location1' AND Date = 'Date1'
OR Location = 'Location2' AND Date = 'Date2'
GROUP BY Title, Variables
HAVING COUNT(*) >= 2
]EDIT: Я изменил []HAVING COUNT(*) = 2[] на []HAVING COUNT(*) >== 2[]. См. комментарии (еще раз спасибо, BenoKrapo)[
]EDIT: через несколько дней после публикации этого ответа я нашел это сообщение от Марка Каллагана, MySQL Architect for Facebook: []http://www.facebook.com/note.php?note_id=243134480932[] По сути, он описывает, как похожие, но разные 'умные' сравнения дают ужасную производительность из-за ошибки в MySQL оптимизаторе. Так что я хочу сказать, что если вы попытаетесь не понравиться ваш синтаксис, когда будете страдать, вы можете столкнуться с ошибкой.[
].]Без описания таблиц и запроса мы мало что можем сделать, чтобы помочь.[
] []Есть несколько вещей, которые могут определить скорость соединения.[
] [- ]
[
- ]Движок базы данных: Вы используете InnoDB или MyISAM? Или, может быть, любой другой движок? Некоторые из них быстрее, чем другие, что влияет на количество присоединений.[ ] [
- ]Индексы: Индексируются ли соответствующие столбцы совпадений?[ ] [
- ]Индексы разделов: Может быть, вы можете разбить таблицу на индексы, чтобы сделать её ещё быстрее?[ ] [
]Также, посмотрите на []EXPLAIN []запрос[][] , который посмотрит на все шаги, которые mysql выполняет для выполнения. Это может вам очень помочь. [
] Попробуйте использовать составной индекс на столбцах, в которых находится пункт, и попытайтесь поместить все остальные столбцы в выборку в Included Columns, это сэкономит стоимость традиционного поиска вверх[
].] Да. Создавать соответствующие индексы на основе выполняемых запросов к соответствующим таблицам [
].]Можете ли вы подготовить SQL оператор с помощью "EXPLAIN", а затем запустить его повторно, скорее всего, из-за пропущенных индексов на столбцах, к которым вы присоединяетесь.[
] []Также попробуйте использовать STRAIGHT_JOIN и упомянуть таблицу, которая медленнее по размеру слева, а справа - большую, чтобы намекнуть MySQL на выбор первой таблицы.[
].] Убедитесь, что поля, которым вы соответствуете, проиндексированы. Совпадение числовых значений также быстрее, чем строки.[
] []Но не проще ли было бы просто написать[
] [SELECT DISTINCT
Title,
Variables
FROM `MyTABLE`
WHERE
Location = 'Location1' AND Date = 'Date1'
OR
Location = 'Location2' AND Date = 'Date2'
] Это может быть немного жульничеством, но на самом деле мне проще было соединить два запроса вместе в PHP после запроса. Это работает только потому, что я выбираю две разные переменные.[
] [$query = "SELECT DISTINCT Title, Variables FROM
MyTABLE WHERE Location='Location1' AND Variable='Variable1'";
$result = mysql_result($query);
while ($row = mysql_array_assoc($result)) {
$Title = $row['Title'];
$Variables = $row['Variables'];
$Array_result1[$Title] = $Variables;
}
$query = "SELECT DISTINCT Title, Variables FROM
MyTABLE WHERE Location='Location2' AND Variable='Variable2'";
$result = mysql_result($query);
while ($row = mysql_array_assoc($result)) {
$Title = $row['Title'];
$Variables = $row['Variables'];
$Array_result2[$Title] = $Variables;
}
$Array_result = array_intersect($Array_result1, $Array_result2);
]Мне понравилась идея использовать только один MySQL-запрос для объединения двух запросов, но это намного быстрее.[
].