Ускорение запросов MySQL InnoDB в приложении PHP

Question

Ускорение запросов MySQL InnoDB в приложении PHP

У меня есть некоторые проблемы со следующим кодом в моем приложении PHP/MySQL. Он работает хорошо и занимает около 3-4 секунд, но первое выполнение (за сеанс) занимает около 2 минут. Я думаю, потому что есть некоторые механизмы автоматического кэширования. Есть способ ускорить первое выполнение? У меня есть root-доступ на этом сервере MySQL, но я не могу изменить структуру БД.

Приложение отображается здесь http://hotel.crosstourpoint.eu/, и медленный сценарий http://hotel.crosstourpoint.eu/ajax/html_hotel_details.php. Чтобы проверить это, найдите что-то в главном окне. Пример: введите "Milano" и нажмите "Cerca", нажмите на опцию "Milano", выберите дату начала и дату окончания ("Giorno di Arrivo - Giorno di partenza") и снова нажмите "Cerca". Значок Info (I) открывает медленный скрипт с помощью вызова ajax.

Благодарю.

Код

<?php

// open mysqli connection
$mysqli = new mysqli('localhost', 'hotelbeds', 'import', 'hotelbeds');
if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); }

$code   = (int) $_REQUEST['code'];
$h      = array();

// hotel position
$request = '
    SELECT
        NAME, LATITUDE, LONGITUDE
    FROM
        HOTELS
    WHERE
        HOTELCODE = ' . $code . '   ';

$stmt = $mysqli->prepare($request);
$stmt->execute();
$stmt->bind_result( $h['name'], $h['latitude'], $h['longitude'] );
$stmt->fetch();
$stmt->close();
unset($stmt);
unset($request);

// loading descriptions
$request = '
    SELECT
        HotelFacilities, HotelHowToGetThere, HotelComments
    FROM
        HOTEL_DESCRIPTIONS
    WHERE
        HotelCode = ' . $code . '
        AND
        LanguageCode = "' . HB_LANGCODE . '"    '; 

$stmt = $mysqli->prepare($request);
$stmt->execute();
$stmt->bind_result( $h['facilities'], $h['hotelhowtogetthere'], $h['comments'] );
$stmt->fetch();
$stmt->close();
unset($stmt);
unset($request);

// hotel images
$request = '
    SELECT
        IMAGEPATH
    FROM
        HOTEL_IMAGES
    WHERE
        HOTELCODE = ' . $code . '   '; 
$stmt = $mysqli->prepare($request);
$stmt->execute();
$stmt->bind_result( $imagepath );
$images = array();
while( $stmt->fetch() ) array_push( $images, $imagepath );
$stmt->close();
unset($stmt);
unset($request);

Структура таблиц

ОТЕЛИ: около 50.000 строк
HOTELS_DESCRIPTIONS около 600 000 строк
HOTELS_IMAGES: около 180 000 строк

CREATE TABLE `HOTELS` (
  `HOTELCODE` varchar(8) collate utf8_spanish_ci NOT NULL,
  `NAME` varchar(50) collate utf8_spanish_ci NOT NULL,
  `CATEGORYCODE` varchar(5) collate utf8_spanish_ci NOT NULL,
  `DESTINATIONCODE` varchar(3) collate utf8_spanish_ci NOT NULL,
  `ZONECODE` varchar(8) collate utf8_spanish_ci default NULL,
  `CHAINCODE` varchar(5) collate utf8_spanish_ci default NULL,
  `LICENCE` varchar(15) collate utf8_spanish_ci default NULL,
  `LATITUDE` varchar(45) collate utf8_spanish_ci default NULL,
  `LONGITUDE` varchar(45) collate utf8_spanish_ci default NULL,
  PRIMARY KEY  (`HOTELCODE`),
  KEY `HOTELS_CATEGORIES_FK` (`CATEGORYCODE`),
  KEY `HOTELS_ZONES_FK` (`ZONECODE`),
  CONSTRAINT `HOTELS_ZONES_FK` FOREIGN KEY (`ZONECODE`) REFERENCES `ZONES` (`ZONECODE`) ON DELETE CASCADE,
  CONSTRAINT `HOTELS_CATEGORIES_FK` FOREIGN KEY (`CATEGORYCODE`) REFERENCES `CATEGORIES` (`CategoryCode`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='Hotels'

CREATE TABLE `HOTEL_DESCRIPTIONS` (
  `HotelCode` varchar(8) collate utf8_spanish_ci NOT NULL,
  `LanguageCode` varchar(3) collate utf8_spanish_ci NOT NULL,
  `HotelFacilities` varchar(2000) collate utf8_spanish_ci default NULL,
  `HotelLocationDescription` varchar(2000) collate utf8_spanish_ci default NULL,
  `HotelRoomDescription` varchar(2000) collate utf8_spanish_ci default NULL,
  `HolelSportDescription` varchar(2000) collate utf8_spanish_ci default NULL,
  `HotelMealsDescription` varchar(2000) collate utf8_spanish_ci default NULL,
  `HotelPaymentMethods` varchar(2000) collate utf8_spanish_ci default NULL,
  `HotelHowToGetThere` varchar(2000) collate utf8_spanish_ci default NULL,
  `HotelComments` varchar(2000) collate utf8_spanish_ci default NULL,
  PRIMARY KEY  (`HotelCode`,`LanguageCode`),
  KEY `HOTEL_DESCRIPTIOS_LANGUAGES_FK` (`LanguageCode`),
  CONSTRAINT `HOTEL_DESCRIPTIOS_LANGUAGES_FK` FOREIGN KEY (`LanguageCode`) REFERENCES `LANGUAGES` (`LANGUAGECODE`) ON DELETE CASCADE,
  CONSTRAINT `HOTEL_DESCRIPTIOS_HOTELS_FK` FOREIGN KEY (`HotelCode`) REFERENCES `HOTELS` (`HOTELCODE`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='Hotel_Descriptions'

CREATE TABLE `HOTEL_IMAGES` (
  `HOTELCODE` varchar(8) collate utf8_spanish_ci NOT NULL,
  `IMAGECODE` varchar(3) collate utf8_spanish_ci NOT NULL,
  `ORDER_` varchar(5) collate utf8_spanish_ci NOT NULL,
  `VISUALIZATIONORDER` varchar(5) collate utf8_spanish_ci default NULL,
  `IMAGEPATH` varchar(2000) collate utf8_spanish_ci NOT NULL,
  PRIMARY KEY  (`HOTELCODE`,`IMAGECODE`,`ORDER_`),
  KEY `HOTEL_IMAGES_IMAGE_TYPES_FK` (`IMAGECODE`),
  CONSTRAINT `HOTEL_IMAGES_IMAGE_TYPES_FK` FOREIGN KEY (`IMAGECODE`) REFERENCES `IMAGE_TYPES` (`IMAGECODE`) ON DELETE CASCADE,
  CONSTRAINT `HOTEL_IMAGES_HOTELS_FK` FOREIGN KEY (`HOTELCODE`) REFERENCES `HOTELS` (`HOTELCODE`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='Hotels_Images'

Дополнительная информация (редактировать):

Ubuntu 64bit 8.04.2 Linux hostname
2.6.24-23-SMP-сервер № 1 Ср 1 апреля 22:14:30 UTC 2009 x86_64 GNU / Linux
mysql Ver 14.12 Distrib 5.0.51a, для debian-linux-gnu (x86_64) с использованием readline 5.2
innodb_buffer_pool_size 512

Объясняет:

>> EXPLAIN SELECT NAME, LATITUDE, LONGITUDE  FROM HOTELS WHERE HOTELCODE = 136224
id | select_type | table | type | possibile_keys | key key_len | ref | rows
1 SIMPLE HOTELS ALL PRIMARY 47373 Using where

>> EXPLAIN  SELECT HotelFacilities, HotelHowToGetThere, HotelComments FROM   HOTEL_DESCRIPTIONS WHERE HotelCode = 136224 AND LanguageCode = "ITA"
id | select_type | table | type | possibile_keys | key key_len | ref | rows
1 SIMPLE HOTEL_DESCRIPTIONS ref PRIMARY,HOTEL_DESCRIPTIOS_LANGUAGES_FK HOTEL_DESCRIPTIOS_LANGUAGES_FK 11 const 75378 Using where

>> EXPLAIN SELECT IMAGEPATH FROM HOTEL_IMAGES WHERE HOTELCODE = 136224
id | select_type | table | type | possibile_keys | key key_len | ref | rows
1 SIMPLE HOTEL_IMAGES ALL PRIMARY 158786    Using where

0

php mysql preloading warm-up

Источник

user474376 05 май '11 в 12:24

3 ответа

Решение

Йохан дал несколько полезных советов по настройке запросов, однако, согласно моему комментарию, это не имеет ничего общего с тем, почему "первое выполнение (за сеанс) занимает около 2 минут". Даже без этих мер запросы должны занимать доли секунды - и в показанном вами коде нет ничего, что объясняло бы, почему медленное поведение характерно для "первого выполнения (за сеанс)".

Что показывает ваш медленный журнал запросов?

Хотя я обычно благодарен за то, что люди вырезали ненужные вещи из кода, размещенного здесь, в этом случае я думаю, что вы вырезали все, что на самом деле вызывает медлительность.

Что заставляет вас думать, что медлительность характерна для первого выполнения за сеанс?

Я думаю, что вы должны профилировать остальную часть кода в сценарии, чтобы найти проблему.

1

Источник

user223992 05 май '11 в 13:29

Я предлагаю просто обходной путь:
как только сеанс запущен, запустите ajax-запрос, который запускает тот же запрос с любым значением $code, просто чтобы разбудить базу данных, поэтому, когда пользователь удовлетворяет потребность в запросе, его выполнение займет 3-4 секунды.

0

Источник

user443685 05 май '11 в 13:18

Другие вопросы по тегам php mysql preloading warm-up

user650492 05 май '11 в 13:19 2011-05-05 13:19 · Accepted Answer · 2011-05-05 13:19

Вы говорите, что не можете изменить структуру БД, это очень прискорбно, потому что я в основном даю советы по структуре БД...

Присоединяйтесь к запросам
Ваши запросы примерно так же трудны, как и собираются.

Возможно, вы захотите поместить их все в один большой запрос, например:

$request = 'SELECT         
  h.NAME, h.LATITUDE, h.LONGITUDE
  ,hd.HotelFacilities, hd.HotelHowToGetThere, hd.HotelComments
  ,hi.ImagePath
FROM HOTELS 
INNER JOIN HOTEL_DESCRIPTIONS hd ON (h.Hotelcode = hd.Hotelcode)
INNER JOIN HOTEL_IMAGES hi ON (h.Hotelcode = hi.Hotelcode)
WHERE HD.Hotelcode = "' .$code. '" AND HD.LanguageCode = "' . HB_LANGCODE . '"  ';

Оптимизировать кеш
Это позволит убедиться, что больше из них помещается в кеш запросов.
Задержка первого запроса вызвана холодным кешем запросов,
см.: http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
Для получения дополнительной информации об этом. (Обратите внимание, что в статье говорится, что prepared statements are not cached это уже не так; по состоянию на 5.1.17 подготовленные операторы кэшируются.)

Несколько предложений по структуре стола

Основной ключ
Сделать поле hotelcode целое число. Сделайте автоинкремент только для столовой отеля. Код отеля является целым (см.: $code = (int) $_REQUEST['code'];)
Так зачем делать это varchar?

Используйте char(x) для небольших значений x, а не varchar
Не используйте varchar(3), используйте символ (3). Varchar (3) имеет переменную длину и требует дополнительного времени обработки, чтобы определить длину строки, только с 3 символами нет реальной экономии места. Я бы рекомендовал использовать char(x) для x < 8.

Внешние ключи
Попробуйте использовать только целые числа для внешних ключей, они работают быстрее, а внешние ключи обычно связаны с первичным ключом какой-либо другой таблицы (PK), который в любом случае должен быть целым числом (см. Пункт выше).

InnoDB и первичные ключи
В InnoDB первичный ключ прикрепляется ко всем индексам, поэтому его короткое замыкание ускоряет каждую вставку, обновление и выбор.
От: http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html

Каждая запись вторичного индекса также содержит все поля первичного ключа, определенные для ключа кластеризованного индекса, которых нет во вторичном индексе. Если какое-либо из этих полей первичного ключа имеет переменную длину, заголовок записи для каждого вторичного индекса будет иметь часть переменной длины, чтобы записывать их длины, даже если вторичный индекс определен для столбцов фиксированной длины.

ПК не должен быть составным ключом
Для таблиц, имеющих составной первичный ключ, убейте его и замените его целочисленным первичным ключом с автоинкрементом (с именем id или что-то типа того). Это связано с тем, что innoDB хранит копию PK в каждом дереве индекса B+ (см. Пункт выше).
Замените текущий первичный ключ уникальным ключом, чтобы убедиться, что ни в одном отеле нет двух описаний на одном языке и т. Д.