Как оптимизировать запрос SELECT INTO OUTFILE с помощью LEFT JOIN в MySQL

Question

Как оптимизировать запрос SELECT INTO OUTFILE с помощью LEFT JOIN в MySQL

Я выполняю запрос под таблицей, в которой содержится 4,45 млн строк, и на выполнение операции уходит более 15-20 минут. Я пытался изменить Engine с Innodb на MyISAM, но ничего не работает. Я также попытался добавить несколько индексов с типом normal и unique, но это все еще занимает то же время.

Вот мой запрос:

SELECT 
a.source, a.destination, a.forward_to, a.start_epoch, a.end_epoch, a.duration, a.billsec, a.outbound_billsec, a.pool_id, a.group_id, a.cost, a.outbound_cost, a.net, a.keep, a.payin, a.payout, a.campaign_id, a.buyer, a.hangup_cause, a.endpoint_disposition, a.uuid, a.agreement, a.agreement_type, a.contract, a.contract_type, a.sip_received_ip,a.termination_ip, 
REPLACE(REPLACE(ifnull(b.line_type,''),'\n',' '),'\r',' ') AS line_type, 
REPLACE(REPLACE(ifnull(b.ocn,''),'\n',' '),'\r',' ') AS ocn, 
REPLACE(REPLACE(ifnull(b.spid_carrier_name,''),'\n',' '),'\r',' ') AS spid_carrier_name 
INTO OUTFILE '/tmp/test-husnain01' 
FIELDS TERMINATED BY ',' FROM inbound_022018 a 
LEFT JOIN wireless_checks b ON (a.uuid = b.uuid) 
WHERE date(a.start_epoch)='2018-02-19' AND 
a.endpoint_disposition='ANSWER' AND 
a.direction='inbound' AND 
a.billed=1;

Ниже моя структура таблицы (inbound_022018):

      CREATE TABLE `inbound_022018` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `source` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `destination` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `prefix` int(22) NOT NULL,
        `forward_to` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `supplier` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `agreement` int(11) NOT NULL,
        `agreement_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `payout` float(11,4) NOT NULL,
        `pool_id` int(11) NOT NULL,
        `group_id` int(11) NOT NULL,
        `campaign_id` bigint(22) NOT NULL,
        `lead` int(1) NOT NULL,
        `cpl` float(11,4) NOT NULL,
        `buyer` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `contract` int(11) NOT NULL,
        `contract_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `payin` float(11,4) NOT NULL,
        `gross` float(11,4) NOT NULL,
        `cost` float(11,4) NOT NULL,
        `outbound_cost` float(11,4) NOT NULL,
        `net` float(11,4) NOT NULL,
        `keep` float(11,4) NOT NULL,
        `direction` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
        `session_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
        `uuid` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
        `sip_from_uri` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
        `sip_received_ip` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `domain_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
        `sip_req_uri` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
        `endpoint_disposition` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
        `hangup_cause` varchar(80) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
        `hangup_cause_q850` varchar(80) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
        `start_epoch` datetime DEFAULT NULL,
        `answer_epoch` datetime DEFAULT NULL,
        `bridge_epoch` datetime DEFAULT NULL,
        `progress_epoch` datetime DEFAULT NULL,
        `progress_media_epoch` datetime NOT NULL,
        `end_epoch` datetime NOT NULL,
        `digits_dialed` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `last_app` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `last_arg` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `duration` int(11) NOT NULL,
        `g30` int(1) DEFAULT NULL,
        `billsec` int(11) NOT NULL,
        `outbound_duration` int(11) NOT NULL,
        `outbound_billsec` int(11) NOT NULL,
        `progresssec` int(11) NOT NULL,
        `answersec` int(11) NOT NULL,
        `waitsec` int(11) NOT NULL,
        `progress_mediasec` int(11) NOT NULL,
        `flow_billsec` int(11) NOT NULL,
        `sip_hangup_disposition` int(11) NOT NULL,
        `callForwarded` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `forwardUuid` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `call_type` enum('s','v') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 's',
        `billed` int(1) NOT NULL,
        `uc` int(1) NOT NULL,
        `suc` int(1) NOT NULL,
        `callinfo` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `termination_ip` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `switchname` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
        `org_charges` float(11,4) NOT NULL,
        `call_summary` text,
        PRIMARY KEY (`id`),
        UNIQUE KEY `index_inbound_0717` (`id`) USING BTREE,
        UNIQUE KEY `index_uuid` (`uuid`) USING BTREE,
        UNIQUE KEY `index_all` (`id`,`campaign_id`,`session_id`,`uuid`) USING BTREE,
        KEY `index_source` (`source`) USING BTREE,
        KEY `index_destination` (`destination`) USING BTREE,
        KEY `index_endpoint` (`endpoint_disposition`) USING BTREE,
        KEY `index_build` (`billed`) USING BTREE,
        KEY `index_campainid` (`campaign_id`) USING BTREE
      ) ENGINE=MyISAM AUTO_INCREMENT=4457485 DEFAULT CHARSET=latin1

Вот вторая таблица (wireless_checks):

         CREATE TABLE `wireless_checks` (
        `id` int(22) NOT NULL AUTO_INCREMENT,
        `date` varchar(10) NOT NULL,
        `uuid` varchar(100) NOT NULL,
        `tn` varchar(11) NOT NULL,
        `lrn` varchar(11) NOT NULL,
        `ported_status` varchar(2) NOT NULL,
        `ported_date` varchar(11) NOT NULL,
        `ocn` varchar(10) NOT NULL,
        `line_type` int(1) NOT NULL,
        `spid` varchar(10) NOT NULL,
        `spid_carrier_name` varchar(100) NOT NULL,
        `spid_carrier_type` varchar(10) NOT NULL,
        `altspid_carrier_name` varchar(10) NOT NULL,
        `altspid_carrier_type` varchar(10) NOT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `index_uuid` (`uuid`) USING BTREE
      ) ENGINE=MyISAM AUTO_INCREMENT=36175 DEFAULT CHARSET=latin1

Пожалуйста, объясните мне, как я могу оптимизировать этот запрос, чтобы сократить время выполнения. Я также открыт для обхода, если есть какой-либо другой подход, чтобы сделать это. Любая помощь будет оценена.

Спасибо

Husnain

0

mysql innodb myisam into-outfile

Источник

user5541302 21 фев '18 в 19:33

2 ответа

Другие вопросы по тегам mysql innodb myisam into-outfile

user1766831 04 мар '18 в 00:06 2018-03-04 00:06 · Answer 1 · 2018-03-04 00:06

SELECT INTO OUTFILE это не проблема. Многочисленные другие вещи замедляют запрос.

Вот фрагменты, которые мне нужно обсудить:

    FROM  inbound_022018 a
    LEFT JOIN  wireless_checks b  ON (a.uuid = b.uuid)
    WHERE  date(a.start_epoch)='2018-02-19'
      AND  a.endpoint_disposition='ANSWER'
      AND  a.direction='inbound'
      AND  a.billed=1;

    `uuid` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,

    `uuid` varchar(100) NOT NULL ... DEFAULT CHARSET=latin1

    float(11,4)

    `date` varchar(10) NOT NULL, ...
    `ported_date` varchar(11) NOT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `index_inbound_0717` (`id`) USING BTREE,

    PRIMARY KEY (`id`), ...
    UNIQUE KEY `index_all` (`id`,`campaign_id`,`session_id`,`uuid`) USING BTREE,

Много проблем:

UUID известны как "случайные". Насколько велики ваши столы? Если они больше, чем должны быть кэшированы в оперативной памяти, запрос суждено быть ужасно медленным.
При сравнении двух строк (a.uuid = b.uuid), индексы нельзя использовать, если кодировка или сопоставление отличаются. Исправь это.
Еще меньше будет конвертировать из строк в BINARY(16), (Код доступен в другом месте.)
UUID, если у вас нет чего-то особенного, может быть `CHAR(26) CHARSET ascii. Это очищает несколько вещей.
Таблица a нужен композит INDEX(billed, direction, endpoint_disposition, start_epoch) сделать WHERE более эффективным. Первые 3 столбца могут быть в любом порядке.
Измените дату теста, как указано ниже.
PRIMARY KEY это UNIQUE ключ; удалить последний.
FLOAT(m,n) это бесполезная конструкция, потому что она включает в себя два округления. Для денежных значений используйте DECIMAL(m,n); для "научных" ценностей используйте FLOAT без (m,n),
Почти никогда не допустимо, чтобы вторичный ключ начинался со всех столбцов PK. (Хорошо, MyISAM может принести пользу, но InnoDB редко приносит пользу.)
Если вам не нужно b.id для чего угодно, избавиться от этого и продвигать uuid быть ПК. Это ускорит JOIN для InnoDB.
Если у вас нет веских причин, не указывайте даты VARCHAR,
Не используйте MyISAM; исправить проблемы, которые я обсуждал здесь. Затем вернитесь для дальнейшего обсуждения, если это необходимо.

Когда столбец "скрыт" внутри функции (например, DATE()), индексирование столбца не помогает. Изменить на

WHERE  a.start_epoch >= '2018-02-19'
  AND  a.start_epoch  < '2018-02-19' + INTERVAL 1 DAY

С этим изменением 4-й столбец в моем предложенном INDEX будет использоваться

user5324657 21 фев '18 в 19:48 2018-02-21 19:48 · Answer 2 · 2018-02-21 19:48

Один совет, который должен иметь значение, заключается в том, что вместо того, чтобы делать

WHERE date(a.start_epoch)='2018-02-19'

Вы должны рассчитать это заранее, а затем использовать реальное значение, то есть 1518998400

Причина, по которой это красный флаг, заключается в том, что, помещая функцию слева от сравнения, вы заставляете базу данных выполнить полное сканирование таблицы, запустив эту функцию на всех строках длиной 4,45 м, просто для обработки WHERE пункт. Если вместо этого вы сравниваете сам столбец с реальным значением, не используя DATE функции, то MySQL может оптимизировать запрос гораздо эффективнее, и будет использовать индекс на a.start_epoch если таковой имеется.

Чтобы создать этот индекс просто сделайте

CREATE INDEX epoch_idx on inbound_022018(start_epoch)

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

Ввод EXPLAIN Перед запросом, и взгляд на результаты для особенно больших чисел строк, является хорошим способом установить, где стоимость в запросе. Часто эффективная индексация решит проблему.