Отладка производительности запросов
Недавно я установил новый модуль (PayPal Express) в серверной части Oxid 6.0.x.
После установки в бэкэнде пункт меню «заказы» в моем бэкэнде истекает. Потому что запрос нужно слишком долго. Я не могу просматривать заказы (это ресурс поставщика CMS).
Я уже пытался отладить его с помощью нашей поддержки управляемого хостера, которая удвоила наш оперативный памяти для увеличения размера буфера mysql, а также тайм-аутов php и т. Д. В php.ini.
После изменения php.ini хостера я все еще вижу запрос со статусом «Копирование в таблицу tmp», хотя хостер увеличил буферы запросов и т. Д.
Вопрос:
Я действительно не уверен, в чем проблема. Есть ли у кого-то идея, что еще я мог бы попробовать?
Я не могу себе представить, что этому маленькому соединению нужно так много буферного пространства и времени для выполнения. Тем более, что соединение с payppaypalpluspayment такое же, как и для oxv_oxpayments_de, которое является мгновенным и имеет тот же первичный ключ.
Это запрос, который я вижу в списке процессов
Показаны строки 0 — 0 (всего 1, запрос занял 200,9683 секунды.) Уже уменьшился примерно через 10 минут после изменения php.ini
select count(*) from `oxorder`
LEFT JOIN `oxv_oxpayments_de` AS `payments` on `payments`.oxid=oxorder.oxpaymenttype
LEFT JOIN `oxv_oxpayments_de` AS pluspayments ON pluspayments.oxid = oxorder.oxpaymenttype
LEFT JOIN payppaypalpluspayment ON payppaypalpluspayment.OXORDERID = oxorder.OXID
LEFT JOIN payppaypalpluspui ON payppaypalpluspui.OXPAYMENTID = payppaypalpluspayment.OXPAYMENTID
where 1 and ( oxorder.oxfolder = 'ORDERFOLDER_NEW' )
count (*) Результат 150000
Проблема может быть в этом присоединении, другие — мгновенные результаты.
строки 0 — 0 (всего 1, запрос занял 153.2391 сек.)
Select count(*) from `oxorder`
LEFT JOIN payppaypalpluspayment ON payppaypalpluspayment.OXORDERID = oxorder.OXID
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE oxorder index NULL MAINIDX 10 NULL 146861 Using index
1 SIMPLE payppaypalpluspayment index NULL OXORDERID 32 NULL 2630 Using where; Using index; Using join buffer (flat, BNL join)
1 SIMPLE payppaypalpluspui ALL NULL NULL NULL NULL 519 Using where
Изменено после добавления предложенных индексов в
oxorder.OXPAYMENTTYPE;
oxorder.oxfolder;
payppaypalpluspui.OXPAYMENTID;
payppaypalpluspayment.OXPAYMENTID;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE oxorder ref OXFOLDER OXFOLDER 98 const 73450 Using index condition
1 SIMPLE payppaypalpluspayment ALL NULL NULL NULL NULL 2634 Using where; Using join buffer (flat, BNL join)
1 SIMPLE payppaypalpluspui ref OXPAYMENTID OXPAYMENTID 34 payppaypalpluspayment.OXPAYMENTID 1 Using where; Using index
После того, как этот счетчик выбрал, бэкэнд выполняет тот же запрос с select oxorder. * И одним столбцом из payppaypalpluspui (занимает около 600 секунд).
Пользовательский интерфейс уже истек. Когда я удаляю последние два соединения и пробую это вручную на сервере, запрос < 1 сек
д.б.н.
CREATE TABLE `payppaypalpluspayment` (
`OXID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Payment oxid id',
`OXORDERID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Order id',
`OXSALEID` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment sale id',
`OXPAYMENTID` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment id',
`OXSTATUS` varchar(32) NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment status',
`OXDATECREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Payment creation date',
`OXTOTAL` double NOT NULL DEFAULT '0' COMMENT 'Total payment amount',
`OXCURRENCY` varchar(32) NOT NULL DEFAULT '' COMMENT 'Payment currency',
`OXPAYMENTOBJECT` blob NOT NULL COMMENT 'Serialized payment object',
PRIMARY KEY (`OXID`),
UNIQUE KEY `OXORDERID` (`OXORDERID`),
UNIQUE KEY `OXSALEID` (`OXSALEID`)
KEY `OXPAYMENTID` (`OXPAYMENTID`) <<< added this index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='PayPal Plus payment data model'
Строки 3,228 InnoDB utf8_general_ci 11,4 МиБ
CREATE TABLE `oxorder` (
`OXID` char(32) NOT NULL COMMENT 'Order id',
`OXSHOPID` int(11) NOT NULL DEFAULT '1' COMMENT 'Shop id (oxshops)',
`OXUSERID` char(32) NOT NULL DEFAULT '' COMMENT 'User id (oxuser)',
`OXORDERDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Order date',
`OXORDERNR` varchar(16) NOT NULL COMMENT 'Order number',
.....
PRIMARY KEY (`OXID`),
KEY `MAINIDX` (`OXSHOPID`,`OXSTORNO`,`OXORDERDATE`),
KEY `OXORDERNR` (`OXORDERNR`)
KEY `OXPAYMENTTYPE` (`OXPAYMENTTYPE`), <<<< added this index
KEY `OXFOLDER` (`OXFOLDER`) <<<< added this index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Shop orders information'
149 068 InnoDB utf8_general_ci 258,1 МиБ
CREATE TABLE `payppaypalpluspui` (
`OXID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Payment oxid id',
`OXPAYMENTID` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment id',
`OXREFERENCENUMBER` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI reference_number',
`OXBANKNAME` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction bank name',
`OXACCOUNTHOLDER` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction account holder',
`OXIBAN` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction IBAN',
`OXBIC` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction BIC',
`OXDUEDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'PayPal Plus PuI due date',
`OXTOTAL` double NOT NULL DEFAULT '0' COMMENT 'PayPal Plus PuI Total invoice amount',
`OXCURRENCY` varchar(32) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI invoice currency',
`OXPUIOBJECT` text NOT NULL COMMENT 'JSON representation of the payment instructions',
PRIMARY KEY (`OXID`)
KEY `OXPAYMENTID` (`OXPAYMENTID`) <<<< added this index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='PayPal Plus Pay upon Invoice data model'
Рядов 655 InnoDB utf8_general_ci 1,5 МиБ
буферный пул innodb = 8 ГиБ
RAM 10 ГиБ
Решение
Ваша проблема в том, что вы сравниваете столбцы, закодированные в разных наборах символов, latin1 и utf8. В таких случаях индекс не может использоваться для поиска ключевых слов. Вы должны убедиться, что используете один и тот же набор символов для всех ваших ключевых столбцов.
Другие решения
Изменить: первоначально я думал, что левые присоединения не имеют отношения, но теоретически они могут раздуть счет, как указано в комментариях.
Для этого запроса количества. Все ЛЕВЫЕ СОЕДИНЕНИЯ кажутся неактуальными, поскольку объединенные, кажется, не содержат больше строк, чем левая таблица, и вы извлекаете только количество, а не данные из них. Я бы удалил их все (если бы это было возможно, но теперь я понимаю, что это не так)
Для запроса oxorder. *. Не включайте объединения, которые вы не используете. Также вы думаете об изменении первичного ключа, если вы всегда обращаетесь к объединенной таблице в ситуации один на один
detector