Блог

итак, у меня есть база данных объемом 560 МБ с самой большой таблицей 500 МБ (более 10 миллионов строк)

мой запрос должен объединять 5 таблиц и занимает около 10 секунд….

 SELECT DISTINCT trips.tripid                                AS tripid,
                stops.stopdescrption                        AS "perron",
                Date_format(segments.segmentstart, "%H:%i") AS "time",
                Date_format(trips.tripend, "%H:%i")         AS "arrival",
                Upper(routes.routepublicidentifier)         AS "lijn",
                plcend.placedescrption                      AS "destination"
FROM   calendar
       JOIN trips
         ON calendar.vsid = trips.vsid
       JOIN routes
         ON routes.routeid = trips.routeid
       JOIN places plcstart
         ON plcstart.placeid = trips.placeidstart
       JOIN places plcend
         ON plcend.placeid = trips.placeidend
       JOIN segments
         ON segments.tripid = trips.tripid
       JOIN stops
         ON segments.stopid = stops.stopid
WHERE  stops.stopid IN ( 43914, 23899, 23925, 23908,
                         23913, 19899, 23871, 43902,
                         23876, 25563, 18956, 19912,
                         23889, 23861, 23879, 23884,
                         23856, 19920, 19898, 23916,
                         23894, 20985, 23930, 20932,
                         20986, 22434, 20021, 19893,
                         19903, 19707, 19935 )
       AND calendar.vscdate = Str_to_date('25-10-2011', "%e-%c-%Y")
       AND segments.segmentstart >= Str_to_date('15:56', "%H:%i")
       AND routes.routeservicetype = 0
       AND segments.segmentstart > "00:00:00"
ORDER  BY segments.segmentstart 
  

что я могу сделать, чтобы ускорить это? любые советы приветствуются, я довольно новичок в sql…
но я не могу изменить структуру БД, потому что она не моя…

Используйте EXPLAIN, чтобы найти узкие места: http://dev.mysql.com/doc/refman/5.0/en/explain.html Тогда, возможно, добавьте индексы.

Если вам не нужно выбирать ВСЕ строки, используйте LIMIT, чтобы ограничить количество возвращаемых результатов.

Просто взглянув на запрос, я бы сказал, что вы должны убедиться, что у вас есть индексы на trips.vsid , calendar.vscdate , segments.segmentstart и routes.routeservicetype . Я предполагаю, что уже есть индексы для всех первичных ключей в таблицах.

Использование explain , как предложил Бриедис, покажет вам, насколько хорошо работают индексы.

Возможно, вы захотите добавить покрывающие индексы для некоторых таблиц, например, индекс trips.vsid where tripid и routeid включены. Таким образом, база данных может использовать только индекс для данных, которые необходимы из таблицы, а не для чтения из фактической таблицы.

Редактировать:

План выполнения сообщает вам, что он успешно использует индексы для всего, кроме segments таблицы, где он выполняет сканирование таблицы и фильтрует по where условию. Вы должны попытаться создать покрывающий индекс для segments.segmentstart , включив tripid и stopid .

Попробуйте добавить индекс кластеров в routes таблицу для обоих routeservicetype и routeid .

В зависимости от частоты данных в routeservicetype поле, вы можете получить улучшение, сократив объем данных, сравниваемых в соединении с таблицей trips.

Глядя на план объяснения, вы также можете захотеть изменить последовательность использования таблицы, используя STRAIGHT_JOIN вместо JOIN (или INNER JOIN ) , поскольку у меня были реальные улучшения с помощью этой техники.

По сути, поместите таблицу с наименьшим количеством строк извлеченных данных в начале запроса и таблицу с наибольшим количеством строк в конце (в данном случае, возможно segments , таблицу?), За исключением простых поисковых запросов (например. для описания).

Вы также можете рассмотреть возможность изменения WHERE предложения для фильтрации segments таблицы stopid вместо stops таблицы и создания кластеризованного индекса в segments таблице на ( stopid , tripid и segmentstart ) — этот индекс будет эффективно удовлетворять двум предложениям join и двум where из одного индекса…

Для построения индекса…

 ALTER TABLE segments ADD INDEX idx_qry_helper ( stopid, tripid, segmentstart );
  

И измененное WHERE предложение…

 WHERE  segments.stopid IN ( 43914, 23899, 23925, 23908,
                     23913, 19899, 23871, 43902,
                     23876, 25563, 18956, 19912,
                     23889, 23861, 23879, 23884,
                     23856, 19920, 19898, 23916,
                     23894, 20985, 23930, 20932,
                     20986, 22434, 20021, 19893,
                     19903, 19707, 19935 )
         :
         :
  

В конце концов, 10-секундный ответ на то, что кажется сложным запросом к довольно большому набору данных, не так уж и плох!