Предложения по ускорению LEFT JOIN в MySQL

У меня есть таблица с именем usr_data с 40 000 или около того записей, которые я хочу соединить с данными организации пользователя (примерно 1000 записей) через select в соединении, очевидно, это очень медленно (до 150 секунд).

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

Мой Запрос 

    SELECT  usr_data.usr_id,usr_data.login,orgus.title FROM usr_data
    LEFT JOIN (
        SELECT object_reference.ref_id,rbac_ua.usr_id,object_data.obj_id,object_data.title
        FROM rbac_ua
        JOIN rbac_fa ON rbac_fa.rol_id = rbac_ua.rol_id
        JOIN object_reference ON rbac_fa.parent = object_reference.ref_id
        JOIN object_data ON object_data.obj_id = object_reference.obj_id
        JOIN object_data role ON role.obj_id = rbac_ua.rol_id
        WHERE object_data.type = 'orgu') as orgus on orgus.usr_id = usr_data.usr_id
    WHERE usr_data.usr_id > 0 AND usr_data.login <> "anonymous"

Описание запроса 

+------+-------------+------------------+--------+-----------------------+---------+---------+--------------------------------+------+--------------------------+
| id   | select_type | table            | type   | possible_keys         | key     | key_len | ref                            | rows | Extra                    |
+------+-------------+------------------+--------+-----------------------+---------+---------+--------------------------------+------+--------------------------+
|    1 | SIMPLE      | usr_data         | range  | PRIMARY,i1_idx        | PRIMARY | 4       | NULL                           | 8148 | Using where              |
|    1 | SIMPLE      | rbac_ua          | ref    | PRIMARY,i1_idx,i2_idx | PRIMARY | 4       | ildCPC.usr_data.usr_id         |    2 | Using where; Using index |
|    1 | SIMPLE      | rbac_fa          | ref    | PRIMARY,i1_idx        | PRIMARY | 4       | ildCPC.rbac_ua.rol_id          |    1 | Using where; Using index |
|    1 | SIMPLE      | role             | eq_ref | PRIMARY               | PRIMARY | 4       | ildCPC.rbac_ua.rol_id          |    1 | Using index              |
|    1 | SIMPLE      | object_reference | eq_ref | PRIMARY,i1_idx        | PRIMARY | 4       | ildCPC.rbac_fa.parent          |    1 | Using where              |
|    1 | SIMPLE      | object_data      | eq_ref | PRIMARY,i1_idx        | PRIMARY | 4       | ildCPC.object_reference.obj_id |    1 | Using where              |
+------+-------------+------------------+--------+-----------------------+---------+---------+--------------------------------+------+--------------------------+

Usr_data таблица 

+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| usr_id               | int(11)       | NO   | PRI | 0       |       |
| login                | varchar(80)   | YES  | MUL | NULL    |       |
| passwd               | varchar(80)   | YES  |     | NULL    |       |
| firstname            | varchar(32)   | YES  |     | NULL    |       |
| lastname             | varchar(32)   | YES  |     | NULL    |       |
| title                | varchar(32)   | YES  |     | NULL    |       |
| gender               | char(1)       | YES  |     | m       |       |
| email                | varchar(80)   | YES  |     | NULL    |       |

Индексы Usr_data 

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| usr_data |          0 | PRIMARY  |            1 | usr_id      | A         |       29354 |     NULL | NULL   |      | BTREE      |         |               |
| usr_data |          1 | i1_idx   |            1 | login       | A         |       29354 |     NULL | NULL   | YES  | BTREE      |         |               |
| usr_data |          1 | i1_idx   |            2 | passwd      | A         |       29354 |     NULL | NULL   | YES  | BTREE      |         |               |
| usr_data |          1 | i2_idx   |            1 | ext_account | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| usr_data |          1 | i2_idx   |            2 | auth_mode   | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Я попытался сделать временную таблицу для этого выбора в левом соединении, но это не очень-то ускоряет его, запрос занимает до 150 секунд в настоящее время, с правым соединением он сокращает его примерно до 1 секунды. (Это столик поменьше).

select * from object_data where type = 'orgu' returns 1058 rows.

Показать создать таблицу rbac_ua 

+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rbac_ua | CREATE TABLE `rbac_ua` (
  `usr_id` int(11) NOT NULL DEFAULT '0',
  `rol_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`usr_id`,`rol_id`),
  KEY `i1_idx` (`usr_id`),
  KEY `i2_idx` (`rol_id`),
  KEY `rol_id` (`rol_id`,`usr_id`),
  KEY `rol_usr` (`rol_id`,`usr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Показать создать таблицу rbac_fa 

+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                    |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rbac_fa | CREATE TABLE `rbac_fa` (
  `rol_id` int(11) NOT NULL DEFAULT '0',
  `parent` int(11) NOT NULL DEFAULT '0',
  `assign` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `protected` char(1) COLLATE utf8_unicode_ci DEFAULT 'n',
  PRIMARY KEY (`rol_id`,`parent`),
  KEY `i1_idx` (`parent`),
  KEY `parent` (`parent`,`rol_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

ПОКАЗАТЬ СОЗДАТЬ ТАБЛИЦУ object_data 

+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| object_data | CREATE TABLE `object_data` (
  `obj_id` int(11) NOT NULL DEFAULT '0',
  `type` char(4) COLLATE utf8_unicode_ci DEFAULT 'none',
  `title` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` char(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `owner` int(11) NOT NULL DEFAULT '0',
  `create_date` datetime DEFAULT NULL,
  `last_update` datetime DEFAULT NULL,
  `import_id` char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`obj_id`),
  KEY `i1_idx` (`type`),
  KEY `i2_idx` (`title`),
  KEY `i4_idx` (`import_id`),
  FULLTEXT KEY `i3_idx` (`title`,`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Показать создать таблицу object_reference 

+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                                                               |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| object_reference | CREATE TABLE `object_reference` (
  `ref_id` int(11) NOT NULL DEFAULT '0',
  `obj_id` int(11) NOT NULL DEFAULT '0',
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`ref_id`),
  KEY `i1_idx` (`obj_id`),
  KEY `i2_idx` (`deleted`),
  KEY `obj_id` (`obj_id`,`ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Если я упущу какую-либо полезную информацию, пожалуйста, дайте мне знать.

Заранее благодарю.

3 2
mysql query-performance

3 ответа:

Используя LEFT JOIN, вы требуете большой результирующий набор, содержащий строку для каждого элемента, который соответствует вашим WHERE usr_data whatever предложениям. Это займет некоторое время, чтобы подтолкнуть к вашему клиентскому программному обеспечению, даже если планировщик запросов делает большую работу.

usr_data.usr_id > 0 кажется излишним, потому что ваш столбец usr_id, похоже, является столбцом автоинкремента. Все ли значения выше нуля?

Подселект, безусловно, является производственным Боровом.

Мне кажется, мистер Трэшер уже близко. Проблема заключается в упоминании LEFT JOINed colum таблицы в предложении WHERE: это преобразует LEFT JOIN в прямую JOIN. Попробуйте это, чтобы увидеть, если вы получите соответствующие результаты. Я не понимаю вашей схемы, так что есть небольшая догадка. 
     SELECT usr_data.usr_id, usr_data.login, object_data.title 
       FROM usr_data
  LEFT JOIN rbac_ua ON rbac_ua.usr_id = usr_data.usr_id
  LEFT JOIN rbac_fa ON rbac_fa.rol_id = rbac_ua.rol_id
  LEFT JOIN object_reference ON rbac_fa.parent = object_reference.ref_id
  LEFT JOIN object_data   ON object_data.obj_id = object_reference.obj_id
                         AND object_data.type = 'orgu'
  LEFT JOIN object_data role ON role.obj_id = rbac_ua.rol_id
      WHERE usr_data.login <> 'anonymous'
Обратите внимание, как object_data.type = 'orgu' оказался в предложении ON. (Да, предложения ON могут содержать те же вещи, что и предложения WHERE!) Что удерживает его от превращения LEFT JOIN в прямую JOIN.

Я не уверен в точке этой линии. 

  LEFT JOIN object_data role ON role.obj_id = rbac_ua.rol_id

Эта таблица не имеет кажется, это вносит свой вклад в ваш набор результатов.

1

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

SELECT  
    usr_data.usr_id,
    usr_data.login,
    object_data.title,
    object_reference.ref_id,
    rbac_ua.usr_id,
    object_data.obj_id,
    object_data.title
FROM usr_data
LEFT JOIN rbac_ua
ON rbac_ua.usr_id = usr_data.usr_id
JOIN rbac_fa ON rbac_fa.rol_id = rbac_ua.rol_id
JOIN object_reference ON rbac_fa.parent = object_reference.ref_id
JOIN object_data ON object_data.obj_id = object_reference.obj_id
JOIN object_data role ON role.obj_id = rbac_ua.rol_id
WHERE 
    object_data.type = 'orgu' AND 
    usr_data.usr_id > 0 AND 
    usr_data.login <> "anonymous"
2

Из EXPLAIN я вижу, что оптимизатор решил, что "LEFT" не повлияло. Так что он ее удалил. Затем было решено, что" производную таблицу " можно превратить в JOIN. Ты мог бы сделать все это.

Но реальная проблема в производительности заключается в том, что важная часть фильтрации находится на type = 'orgu', но она скрыта в "последней" таблице.

(Далее я предполагаю из-за отсутствия SHOW CREATE TABLE для каждой таблицы. И DESCRIBE не является описательным объявлением SHOW CREATE.)

Давайте посмотрим на это: 
SELECT  ud.usr_id, ud.login, od.title
    FROM  object_data AS od
    JOIN  object_reference AS r  ON od.obj_id = r.obj_id
    JOIN  rbac_fa AS rfa  ON rfa.parent = r.ref_id
    JOIN  rbac_ua AS rua  ON rfa.rol_id = rua.rol_id
    JOIN  usr_data AS ud  ON ud.usr_id = rua.usr_id
     -- unnec?:  JOIN  object_data role  ON role.obj_id = rua.rol_id
    WHERE  od.type = 'orgu'
      AND  ud.usr_id > 0
      AND  ud.login <> "anonymous"

Я предлагаю его как более эффективный после добавления индексов ниже. Это, вероятно, будет иметь место, если type = 'orgu' достаточно избирательно.

Обратите внимание, что role ничего не добавляет к запросу, кроме проверки того, что роль действительно существует для данного obj_id.

Индексы, необходимые для того, чтобы он мог начать с type = 'orgu': 

object_data: INDEX(type)
object_reference:  INDEX(obj_id, ref_id)  -- (covering, too)
rbac_fa:  INDEX(parent, rol_id)  -- (covering, too)
rbac_ua:  INDEX(rol_id, usr_id)  -- (covering, too)

Если это не поможет, пожалуйста, предоставьте EXPLAIN SELECT ... результат. И расскажи нам сколько строк object_data имеют тип=orgu.

1