MySQL множественные состояния отправки данных

Мой запрос выполняется слишком долго. Когда я его профилирую, я вижу что-то вроде этого:

    Sending data    0.039324
    executing   0.000011
    Sending data    0.039662
    executing   0.000012
    Sending data    0.040380
    executing   0.000015
    Sending data    0.035879
    executing   0.000012
    Sending data    0.035426
    executing   0.000012
    Sending data    0.038107
    executing   0.000011
    Sending data    0.035247
    executing   0.000011
    Sending data    0.050108
    executing   0.000014
    Sending data    0.045458
    executing   0.000012
    Sending data    0.034700
    executing   0.000012
    Sending data    0.036205
    executing   0.000012
    Sending data    0.034602
    executing   0.000015
    Sending data    0.034580
    executing   0.000012
    Sending data    0.034477
    executing   0.000010
    Sending data    0.034382
    executing   0.000010
    Sending data    0.034416
    executing   0.000011
    Sending data    0.034335
    executing   0.000010
    Sending data    0.034474
    executing   0.000010
    Sending data    0.034405
    executing   0.000010
    Sending data    0.034433
    executing   0.000011
    Sending data    0.034544
    executing   0.000010
    Sending data    0.034525
    executing   0.000011
    Sending data    0.034459
    executing   0.000010
    Sending data    0.034766
    executing   0.000011
    Sending data    0.034633
    executing   0.000010
    Sending data    0.034574
    executing   0.000011
    Sending data    0.034607
    executing   0.000010
    Sending data    0.034613
    executing   0.000011
    Sending data    0.034394
    executing   0.000010
    Sending data    0.034606
    executing   0.000011
    Sending data    0.034790
    executing   0.000011
    Sending data    0.034614
    executing   0.000011
    Sending data    0.034497
    executing   0.000010
    Sending data    0.034756
    executing   0.000010
    Sending data    0.034440
    executing   0.000010
    Sending data    0.034414
    executing   0.000011
    Sending data    0.034484
    executing   0.000011
    Sending data    0.034490
    executing   0.000011
    Sending data    0.034672
    executing   0.000011
    Sending data    0.034455
    executing   0.000011
    Sending data    0.034430
    executing   0.000011
    Sending data    0.034509
    executing   0.000012
    Sending data    0.034432
    executing   0.000012
    Sending data    0.034348
    executing   0.000011
    Sending data    0.034378
    executing   0.000011
    Sending data    0.034356
    executing   0.000011
    Sending data    0.034631
    end 0.000014
    query end   0.000007
    closing tables  0.000010
    freeing items   0.000025
    logging slow query  0.000003
    logging slow query  0.000004
    cleaning up 0.000004

В нем слишком много отправляемых данных.

Я выполнил запрос:

SELECT COUNT(*) as count from OrdersArchive where ID>0 and PId IN ('2564') and 
   (
   ID like '17000106864' 
   OR `OrderID` like '17000106864' 
   OR `ID` IN
   (
       SELECT `transferID`
       FROM `custom_fields`
       WHERE `fieldName` = 'invoiceNumber'
       AND `value` like '%17000106864%'
   )
   OR `tpb` LIKE '17000106864' 

   )

Объясните шоу


id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY OrdersArchive   range   PRIMARY,ID_UNIQUE   PRIMARY 4   NULL    41609   Using where
2   DEPENDENT SUBQUERY  custom_fields   ALL NULL    NULL    NULL    NULL    93141   Using where

Структуры таблиц MySQL:

CREATE TABLE IF NOT EXISTS `OrdersArchive` (
  `ID` int(11) NOT NULL,
  `ids` int(11) NOT NULL DEFAULT '0',
  `OrderID` varchar(11) NOT NULL DEFAULT '0',
  `PricePosition` int(11) NOT NULL DEFAULT '0',
  `Reverse` tinyint(1) DEFAULT NULL,
  `DataOrder` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DataFlightTrain` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Customer` varchar(255) DEFAULT NULL,
  `PhoneCustomer` varchar(255) DEFAULT NULL,
  `EmailCustomer` varchar(255) DEFAULT NULL,
  `Provider` int(11) DEFAULT NULL,
  `DeliveryTime` timestamp NULL DEFAULT NULL,
  `Address1` varchar(255) DEFAULT NULL,
  `Address2` varchar(255) NOT NULL,
  `Passangers` varchar(1024) DEFAULT NULL,
  `PassangersPhones` varchar(255) NOT NULL,
  `PassangersEmailes` varchar(255) NOT NULL,
  `FlightTrain` varchar(255) DEFAULT NULL,
  `QuantityPassangers` int(11) DEFAULT '1',
  `NamePlate` varchar(255) DEFAULT NULL,
  `PhoneDriver` varchar(255) DEFAULT NULL,
  `PhoneDriverNeed` tinyint(1) DEFAULT '0',
  `Status` int(11) DEFAULT NULL,
  `Operator` int(11) DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `usn` varchar(256) NOT NULL,
  `ArendaNeed` varchar(255) DEFAULT '',
  `ArendaHour` int(11) DEFAULT NULL,
  `ArendaMinutes` varchar(255) DEFAULT '',
  `Cost` double DEFAULT NULL,
  `Notes` text NOT NULL,
  `notes2` varchar(256) NOT NULL DEFAULT '',
  `PId` int(11) NOT NULL DEFAULT '0',
  `Voucher` varchar(256) NOT NULL,
  `Invoice` varchar(256) NOT NULL,
  `Meet` varchar(255) NOT NULL,
  `Toward` varchar(255) NOT NULL,
  `techStatus` int(2) NOT NULL DEFAULT '0',
  `City` varchar(55) NOT NULL,
  `City2` varchar(55) NOT NULL,
  `Auto` varchar(30) NOT NULL,
  `department` varchar(255) NOT NULL DEFAULT '',
  `nsktime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tpb` varchar(255) NOT NULL DEFAULT '',
  `ban_add_races` int(1) NOT NULL DEFAULT '0',
  `paid` int(10) NOT NULL DEFAULT '0',
  `taxi` varchar(255) NOT NULL DEFAULT '',
  `price_client` int(11) DEFAULT NULL,
  `comission_from_client` int(11) DEFAULT NULL,
  `primechanie` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `fk_Orders_Users1_idx` (`Operator`),
  KEY `fk_Orders_Providers1_idx` (`Provider`),
  KEY `fk_Orders_OrderStatus1_idx` (`Status`),
  KEY `ids` (`ids`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## and other table

CREATE TABLE IF NOT EXISTS `custom_fields` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `transferID` int(11) NOT NULL,
  `fieldName` varchar(255) NOT NULL,
  `value` varchar(1024) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=325452 ;

mysql php profiling explain entity-attribute-value
person Влад Чачиев    schedule 30.08.2017    source источник
comment
Вам не хватает некоторых индексов, например OrderID и tpb в OrdersArchive планшете. Таблица custom_fields всегда будет довольно неэффективной. В любом случае, я предполагаю, что вы хотите, чтобы ваш запрос выполнялся быстрее, насколько быстрее? Не забывайте, что платформа, на которой вы запускаете запрос, также влияет на скорость.   -  person KIKO Software    schedule 30.08.2017
comment
Добавлены упомянутые вами индексы, все равно плохие результаты, такие как запрос 2 минуты. Удаление подзапроса из запроса действительно ускоряет выполнение почти до 0,1 секунды.   -  person Влад Чачиев    schedule 30.08.2017
comment
Подзапрос возвращает единственный результат, поэтому он не обязательно должен быть подзапросом. Я не ожидаю, что MySQL будет запускать его для каждой строки OrdersArchive, но может. Поскольку вы используете PHP, попробуйте сначала выполнить подзапрос, а затем сгенерировать OR ID IN (.....) с помощью PHP, чтобы он был постоянным массивом идентификаторов. Сколько может быть счетов-фактур?   -  person KIKO Software    schedule 30.08.2017
comment
AND value like '%17000106864%' Это медленно, потому что "% ..." не может использовать индекс   -  person Strawberry    schedule 30.08.2017
comment
Я пытаюсь переписать запрос, чтобы использовать JOIN, но теперь он отправляет count = 2, но реальный счет равен 1. Запрос: SELECT DISTINCT COUNT (*) as count FROM OrdersArchive INNER JOIN custom_fields on OrdersArchive.ID = _2 _._ 3_ WHERE OrdersArchive .ID ›0 И OrdersArchive.PId IN ('2564') И (OrdersArchive.ID КАК '17000106864' ИЛИ ​​OrdersArchive.OrderID КАК '17000106864' ИЛИ ​​OrdersArchive.tpb LIKE '17000106864' ИЛИ ​​(_6 _._ 7_ = 'custom_fieldsNumber_ .value как '% 17000106864%'))   -  person Влад Чачиев    schedule 30.08.2017

Ответы (3)


arrow_upward
0
arrow_downward

Хотя текущий запрос SELECT, вероятно, можно было бы до некоторой степени улучшить, я думаю, что это помогло бы гораздо больше, если бы вы могли хранить данные более эффективным способом, особенно если вы можете удалить необходимость в

И value как "% 17000106864%"

Если вы создадите отдельное поле для invoiceNumber и заполните его при вставке данных, вы можете проиндексировать его и выбрать / присоединиться к нему следующим образом:

ГДЕ invoiceNumber = 17000106864

Если вы ищете только одну запись, добавление LIMIT в запрос также поможет.

person SpacemanSpiff    schedule 30.08.2017

arrow_upward
0
arrow_downward

Что ж, я добился этого с помощью INNER JOIN. Бежит 0,34 сек.

Окончательный запрос:

SELECT COUNT(`OrdersArchive`.ID) as count, `OrdersArchive`.ID
FROM `OrdersArchive`
INNER JOIN `custom_fields` on `OrdersArchive`.ID = `custom_fields`.`transferID`
WHERE `OrdersArchive`.ID>0 
AND `custom_fields`.`fieldName` = 'invoiceNumber'
AND 
`OrdersArchive`.PId IN ('2564') AND
(
   `OrdersArchive`.ID LIKE '17000106864' 
   OR `OrdersArchive`.`OrderID` LIKE '17000106864' 
   OR `OrdersArchive`.`tpb` LIKE '17000106864' 
   OR (
 `custom_fields`.`value` like '%17000106864%'
   )
)
person Влад Чачиев    schedule 30.08.2017

arrow_upward
0
arrow_downward

Вернемся к вопросу, указанному в заголовке («несколько состояний отправки данных») ...

IN(SELECT ...) часто бывает очень неэффективным. В вашем случае он выполняется повторно, тем самым «выполняя» и «отправляя данные» для каждого вызова.

В других ответах содержится ответ на другой подразумеваемый вопрос («запрос выполняется слишком долго») путем преобразования этой конструкции в JOIN.

Другие вопросы;

PRIMARY KEY (`ID`),   -- This is UNIQUE and an INDEX
UNIQUE KEY `ID_UNIQUE` (`ID`),  -- totally redundant; DROP it

Вероятно, вас беспокоят проблемы с производительностью, поскольку используется схема «EAV» (Entity-Attribute-Value), и вам нужно фильтровать по некоторому ключу-значению («invoiceNumber»). Вы можете вытащить его из таблицы "ключ-значение" (custom_fields) и поместить в основную таблицу (OrdersArchive).

Но потом я вижу, что OrdersArchive имеет довольно большое количество столбцов. Но у меня нет никаких конкретных предложений по этому поводу.

custom_fields совершенно лишен индексов. См. мои советы по индексированию общего ключа. таблица значений.

person Rick James    schedule 02.09.2017