Эффективный выбор строк, в которых существует строка в связанной таблице

Question

Эффективный выбор строк, в которых существует строка в связанной таблице

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

мой users Таблица содержит 430 825 записей, так что с этим не должно быть таких трудностей. Прямо сейчас я близок, у меня есть запрос, который получает то время выполнения.047s, которое я ищу, но если я добавлю еще один кусок к этому, он станет очень медленным.

Вот мой текущий запрос, быстрый:

select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or`UserID`in(select*
        from(select`UserID`
            from`invoices`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`quoterequests`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`userassociations`
            where`_Email`='brian@yeet.com'
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`usercustomerflags`
            where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
            and`__Active`=1)`a`)
    or not exists(select 1 
        from`usercustomerflags`
        where`__Active`=1 
        and`users`.`UserID`=`UserID`))
and`Deleted`=0 
order by`DateTimeAdded`desc 
limit 50;

(Дополнительный select*from(...) из-за этого /questions/35833678/podzapros-kotoryij-dolzhen-byit-nezavisimyim-ne-yavlyaetsya-zachem/35833706#35833706)

В середине я тяну пользователей дальше по адресу электронной почты, проверяя другие связанные таблицы на наличие писем, которые могут быть связаны с этим пользователем. Мол, следующая часть ищет пользователей, включая их CC-адреса, когда кавычки рассылаются клиентам.

select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or`UserID`in(select*
        from(select`UserID`
            from`invoices`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`quoterequests`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`userassociations`
            where`_Email`='brian@yeet.com'
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`userquotesemails`
            where`Email`='brian@yeet.com'
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`usercustomerflags`
            where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
            and`__Active`=1)`a`)
    or not exists(select 1 
        from`usercustomerflags`
        where`__Active`=1 
        and`users`.`UserID`=`UserID`))
and`Deleted`=0 
order by`DateTimeAdded`desc 
limit 50;

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

Первый и второй объясняют соответственно

+----+--------------------+-------------------+--+----------------+---------------------------------------------------------------------------------------------+------------------------------+------+-----------------------+---+-------+---------------------------------+
|  1 | PRIMARY            | <subquery6>       |  | ALL            |                                                                                             |                              |      |                       |   | 0.00  | Using temporary; Using filesort |
|  1 | PRIMARY            | users             |  | eq_ref         | PRIMARY,UserID_UNIQUE,fk_users_1_idx,users_Customers                                        | PRIMARY                      |  144 | <subquery6>.UserID    | 1 | 50.00 | Using where                     |
|  6 | MATERIALIZED       | userassociations  |  | ref            | userassociations_UserID,userassociations__Email                                             | userassociations__Email      | 1026 | const                 | 3 | 10.00 | Using where                     |
| 10 | DEPENDENT SUBQUERY | usercustomerflags |  | ref            | usercustomerflags_UserID_idx                                                                | usercustomerflags_UserID_idx |  144 | sterling.users.UserID | 1 | 10.00 | Using where                     |
|  8 | DEPENDENT SUBQUERY | usercustomerflags |  | index_subquery | usercustomerflags_CustomerFlagID_idx,usercustomerflags_UserID_idx                           | usercustomerflags_UserID_idx |  144 | func                  | 1 | 4.95  | Using where                     |
|  4 | DEPENDENT SUBQUERY | quoterequests     |  | index_subquery | quoterequests_CompanyID,quoterequests_UserID,quoterequests__Latest,quoterequests_UserQuotes | quoterequests__Latest        |  145 | func                  | 2 | 5.00  | Using where                     |
|  2 | DEPENDENT SUBQUERY | invoices          |  | index_subquery | Invoice_UserID_idx,Invoice_CompanyID_idx,invoices_SampleRequests,invoices_LateOrdersBubble  | Invoice_UserID_idx           |  145 | func                  | 1 | 3.33  | Using where                     |
+----+--------------------+-------------------+--+----------------+---------------------------------------------------------------------------------------------+------------------------------+------+-----------------------+---+-------+---------------------------------+

+----+--------------------+-------------------+--+-----+---------------------------------------------------------------------------------------------+--------------------------------+------+-----------------------+--------+--------+-------------+
|  1 | PRIMARY            | users             |  | ref | fk_users_1_idx,users_Customers                                                              | users_Customers                |    4 | const                 | 227515 | 100.00 | Using where |
| 12 | DEPENDENT SUBQUERY | usercustomerflags |  | ref | usercustomerflags_UserID_idx                                                                | usercustomerflags_UserID_idx   |  144 | sterling.users.UserID |      1 | 10.00  | Using where |
| 10 | SUBQUERY           | usercustomerflags |  | ALL | usercustomerflags_CustomerFlagID_idx,usercustomerflags_UserID_idx                           |                                |      |                       |   3509 | 4.94   | Using where |
|  8 | SUBQUERY           | userquotesemails  |  | ref | userquotesemails_Email__Active,userquotesemails_UserID                                      | userquotesemails_Email__Active | 1027 | const,const           |      1 | 100.00 |             |
|  6 | SUBQUERY           | userassociations  |  | ref | userassociations_UserID,userassociations__Email                                             | userassociations__Email        | 1026 | const                 |      3 | 10.00  | Using where |
|  4 | SUBQUERY           | quoterequests     |  | ref | quoterequests_CompanyID,quoterequests_UserID,quoterequests__Latest,quoterequests_UserQuotes | quoterequests_CompanyID        |  144 | const                 |  16702 | 10.00  | Using where |
|  2 | SUBQUERY           | invoices          |  | ref | Invoice_UserID_idx,Invoice_CompanyID_idx,invoices_SampleRequests,invoices_LateOrdersBubble  | Invoice_CompanyID_idx          |  144 | const                 |  17678 | 10.00  | Using where |
+----+--------------------+-------------------+--+-----+---------------------------------------------------------------------------------------------+--------------------------------+------+-----------------------+--------+--------+-------------+

Кроме того, я попытался использовать соединения, например, присоединение к invoices таблица и т. д., но тогда я получаю проблему наличия дублирующих строк пользователя для каждого invoice или же quoterequest Соединения получают, и группировка / различение и упорядочение полученных данных стали чрезвычайно медленными, в считанные минуты.

Я также попробовал только "существующую" версию первого запроса, как это было предложено в документе https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization-with-exists.html примерно так:

select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or exists(select 1 
        from`invoices`
        where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
        and`__Active`=1 
        and`users`.`UserID`=`UserID`)
    or exists(select 1 
        from`quoterequests`
        where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
        and`__Active`=1 
        and`users`.`UserID`=`UserID`))
and(exists(select 1 
        from`userassociations`
        where`_Email`='brian@yeet.com'
        and`__Active`=1 
        and`users`.`UserID`=`UserID`))
and(exists(select 1 
        from`usercustomerflags`
        where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
        and`__Active`=1 
        and`users`.`UserID`=`UserID`)
    or not exists(select 1 
        from`usercustomerflags`
        where`__Active`=1 
        and`users`.`UserID`=`UserID`))
and`Deleted`=0 
order by`DateTimeAdded`desc 
limit 50;

Но это дает мне 5,516 с, так что это определенно не похоже на правильное направление.

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

Я выделил самую маленькую подзадачу и горлышко бутылки, я думаю, что у меня есть Вот мой более легкий запрос

select`users`.`UserID`,`users`.`_Customer`
from`users`
left join`userassociations`on`userassociations`.`UserID`=`users`.`UserID`
and`userassociations`.`__Active`=1 
where(`users`.`Email`='brian@stumpyinc.com'
    or`userassociations`.`_Email`='brian@stumpyinc.com')
and`users`.`Deleted`=0 
order by`users`.`DateTimeAdded`desc 
limit 50;

И объяснить

+---+--------+------------------+--+-----+--------------------------------------------------------+-------------------------+-----+-----------------------+--------+--------+-------------+
| 1 | SIMPLE | users            |  | ref | users_getemail_INDEX,unify_email_INDEX,users_Customers | users_Customers         |   4 | const                 | 221463 | 100.00 | Using where |
| 1 | SIMPLE | userassociations |  | ref | userassociations_UserID                                | userassociations_UserID | 144 | sterling.users.UserID |      1 | 100.00 | Using where |
+---+--------+------------------+--+-----+--------------------------------------------------------+-------------------------+-----+-----------------------+--------+--------+-------------+

Этот запрос занимает около 1,5 секунд, чтобы выполнить

CREATE TABLE `users` (
  `UserID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ...
  `Email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ...
  `DateTimeAdded` datetime DEFAULT NULL,
    ...
  `Deleted` int(1) NOT NULL DEFAULT '0',
    ...
  `_LatestInvoiceDateTimeAdded` datetime DEFAULT NULL,
  `_InvoiceCount` int(11) NOT NULL DEFAULT '0',
  `_Customer` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ...
  PRIMARY KEY (`UserID`),
  UNIQUE KEY `UserID_UNIQUE` (`UserID`),
    ...
  KEY `users_getemail_INDEX` (`Email`(191),`_InvoiceCount`,`_LatestInvoiceDateTimeAdded`,`DateTimeAdded`),
  KEY `unify_email_INDEX` (`Email`(191),`UserID`),
    ...
  KEY `users_Customers` (`Deleted`,`DateTimeAdded`),
    ...
  KEY `users_DateTimeAdded` (`DateTimeAdded`,`UserID`),
  FULLTEXT KEY `users_FULLTEXT__Customer` (`_Customer`),
    ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `userassociations` (
   `UserAssociationID` binary(16) NOT NULL,
   `UserID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
   `AssociatedUserID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
   `_Email` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL,
   `__UserID` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `__Active` tinyint(1) NOT NULL DEFAULT '1',
   `__Added` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
   `__Updated` timestamp(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
   PRIMARY KEY (`UserAssociationID`),
   KEY `userassociations_UserID` (`UserID`),
   KEY `userassociations_AssociatedUserID` (`AssociatedUserID`),
   KEY `userassociations___UserID` (`__UserID`),
   KEY `userassociations__Email` (`_Email`),
   CONSTRAINT `userassociations_AssociatedUserID` FOREIGN KEY (`AssociatedUserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `userassociations_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `userassociations___UserID` FOREIGN KEY (`__UserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Хм... Похоже, это сработало, но я нашел пару столов, с которыми он не так эффективен, и это мое users а также invoices столы.

У меня есть эти индексы:

users:    INDEX(`CompanyID`, `Deleted`, `DateTimeAdded`) 
invoices: INDEX(`UserID`, `__Active`) 
invoices: INDEX(`CompanyID`)
users:    INDEX(`UserID`, `Deleted`)

и запрос

select`users`.`UserID`,`users`.`DateTimeAdded`
from`users`
join`invoices`on`invoices`.`UserID`=`users`.`UserID`
and`invoices`.`__Active`=1 
where`invoices`.`CompanyID`='3e55c8b4-d8b6-11e4-b38f-b8ca3a83b4c8'
and`users`.`Deleted`=0 
order by`DateTimeAdded`desc 
limit 200;

Один этот запрос занимает 0,3 секунды, что мне кажется медленным, так как он не использует индексы наилучшим образом, особенно потому, что users только 430,997 строк и invoices только 194,180, и, похоже, это должен быть довольно простой запрос.

РЕДАКТИРОВАТЬ: На самом деле это гораздо хуже, чем если, если указан CompanyID только ~4 строки, то этот запрос занимает 3,5 секунды

+---+--------+----------+--+-----+------------------------------------------------+-----------------------+-----+--------------------------------+------+--------+----------------------------------------------+
| 1 | SIMPLE | invoices |  | ref | Invoice_CompanyID_idx,invoices_UserID___Active | Invoice_CompanyID_idx | 144 | const                          | 7750 | 10.00  | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | users    |  | ref | users_UserID_Deleted                           | users_UserID_Deleted  | 148 | sterling.invoices.UserID,const |    1 | 100.00 |                                              |
+---+--------+----------+--+-----+------------------------------------------------+-----------------------+-----+--------------------------------+------+--------+----------------------------------------------+

0

mysql performance mysql-5.7 percona percona-xtradb-cluster

Источник

user728236 07 дек '17 в 21:30

2 ответа

Решение

Уверен, что вы можете заменить все эти подзапросы в WHERE пункт с сочетанием INNER а также LEFT JOINs. Попробуй это:

SELECT u.UserID 
FROM users u
INNER JOIN userassociations ua ON ua.userid = u.userid
LEFT JOIN usercustomerflags uc ON uc.userid = u.userid AND uc.__Active =1
LEFT JOIN invoices i ON i.userid = u.userid AND i.__Active =1
LEFT JOIN quoterequests q ON q.userid = u.userid AND q.__Active =1
WHERE ua._Email ='brian@yeet.com'
AND ua.__Active =1
AND (uc.userid IS NULL 
        OR (uc.userid IS NOT NULL AND uc.CustomerFlagID IN (10,27,17,1,2,3,4,5,6))
    )
AND (u.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8'
        OR (i.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' AND i.userid IS NOT null)
        OR (q.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' AND q.userid IS NOT null)
    )
AND u.Deleted =0 
ORDER BY u.DateTimeAdded desc 
LIMIT 50;

0

Источник

user1169697 07 дек '17 в 21:51

Другие вопросы по тегам mysql performance mysql-5.7 percona percona-xtradb-cluster

user1766831 08 дек '17 в 04:44 2017-12-08 04:44 · Accepted Answer · 2017-12-08 04:44

Для этой небольшой проблемы:

( select u.`UserID`, u.`_Customer`, u.DateTimeAdded
    from  `users` AS u
    where  u.`Email` = 'brian@stumpyinc.com'
      and  u.`Deleted` = 0
      AND EXISTS ( SELECT * FROM `userassociations`
                       WHERE UserId = u.UserID
                         AND __Active = 1 )
    order by  u.`DateTimeAdded` desc
    limit  50
)
UNION DISTINCT
( select u.`UserID`, u.`_Customer`, u.DateTimeAdded
    from  `users` AS u
    JOIN  `userassociations` AS ua
         ON  ua.`UserID` = u.`UserID`
        and  ua.`__Active` = 1
    where  ua.`_Email` = 'brian@stumpyinc.com' 
      and  u.`Deleted`=0
    order by  u.`DateTimeAdded` desc
    limit  50
)
order by `DateTimeAdded` desc
limit  50

Это будет необходимо:

u:  INDEX(Email, Deleted, DateTimeAdded)  -- date last
ua: INDEX(UserId, __Active)   -- either order
ua: INDEX(_Email)
u:  INDEX(UserID, Deleted)

(Дайте мне знать, если вы получаете синтаксические ошибки. Если это слишком медленно, пожалуйста, предоставьте EXPLAIN.)

Префикс индекса (Email(191)) обычно бесполезен. Избавься если это. Вот 5 способов избежать этого: http://mysql.rjweb.org/doc.php/limits

PK является УНИКАЛЬНЫМ ключом, поэтому избавьтесь от второго:

PRIMARY KEY (`UserID`),
UNIQUE KEY `UserID_UNIQUE` (`UserID`),

Пахнет как UUID; используйте ascii (ascii_general_ci), а не utf8mb4:

... char(36) COLLATE utf8mb4_unicode_ci

INT(1) занимает 4 байта; использование TINYINT для флагов.