MySQL улучшает производительность время выполнения

ProgramBox

MySQL улучшает производительность время выполнения

Post author:admin
Запись опубликована:15 февраля, 2023
Post category:Вопросы по программированию

#mysql #performance

#mysql #Производительность

Вопрос:

Я пытаюсь повысить производительность следующего запроса, для выполнения которого потребовалось 93,2 сек. ниже:

 SELECT  year(date), month(date), `country_name_name`,
        CEIL(count(res.`user_xmpp_login`) /DAY(LAST_DAY(date))) as avgUser,
        CEIL(count(res.user)/DAY(LAST_DAY(date))) as avgPurchase
    FROM  
    (   SELECT  DATE(`user_registration_timestamp`) as date,
                user_country,
                NULL as user, `user_xmpp_login`
            FROM  users
            WHERE  `user_registration_timestamp` >= "2015-01-01 00:00:00"
              AND  `user_registration_timestamp`  < "2016-01-01 00:00:00"
            UNION  ALL 
        SELECT  DATE(`ts`) as date, user_country, user, NULL as `user_xmpp_login`
            FROM  purchase_log p
            INNER JOIN  users u  ON u.`user_xmpp_login` = p.`user`
            WHERE  `ts` >= "2015-01-01 00:00:00"
              AND  `ts`  < "2016-01-01 00:00:00"
              AND  result in ('ok', 'cancelled', 'pending') 
    ) AS res
    INNER JOIN  countries c  ON c.`country_id` = res.`user_country`
    INNER JOIN  country_names cn
               ON (cn.`country_name_country` = c.`country_id`
              AND  cn.`country_name_language` = 'en')
    GROUP BY  1,2,3
    ORDER BY  4 DESC,5 DESC, 3 ASC;

Объяснение команды показывает:

И структура каждой таблицы:

таблица покупок:

 CREATE TABLE `purchase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `result` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `iuser` (`user`),
) ENGINE=InnoDB AUTO_INCREMENT=12710221 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

таблица пользователей:

 CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_country` int(11) DEFAULT NULL,
  `user_xmpp_login` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_registration_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_xmpp_login_UNIQUE` (`user_xmpp_login`),
  KEY `user_country_FK` (`user_country`),
  KEY `user_registration_timestamp` (`user_registration_timestamp`),
  CONSTRAINT `users_country_FK` FOREIGN KEY (`user_country`)
         REFERENCES `countries` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=33504745 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

таблица стран

 CREATE TABLE `countries` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_code` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`country_id`),
) ENGINE=InnoDB AUTO_INCREMENT=508 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

названия стран

 CREATE TABLE `country_names` (
  `country_name_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name_country` int(11) NOT NULL,
  `country_name_language` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `country_name_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`country_name_id`),
  UNIQUE KEY `country_name_country_language_UNIQUE`
            (`country_name_country`,`country_name_language`),
  KEY `country_name_language` (`country_name_language`),
  CONSTRAINT `country_name_country` FOREIGN KEY (`country_name_country`)
        REFERENCES `countries` (`country_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=45793 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Есть ли какие-либо рекомендации?

Ответ №1:

Если вы определяете время выполнения каждого подзапроса, я думаю, вы обнаружите users , что это самый медленный компонент.

Вероятно, purchase_log подзапрос можно улучшить с помощью этого «покрытия» INDEX(result, ts, user) .

Объедините две таблицы «country»!. Используйте CHAR(2) CHARACTER SET ascii для PRIMARY KEY и JOINs для других таблиц. Это всего 2 байта, в отличие INT от , который равен 4 байтам и VARCHAR... , который равен 3 байтам (в данном случае).

Вы упоминаете ts , но я не понимаю, откуда это берется. Если он purchase_log включен, то эта таблица нужна INDEX(user, ts) .

Какой процент users задействован в 2015 году? Если это больше, чем около 20%, INDEX(user_registration_timestamp) это не поможет.

Подумайте: избавьтесь от PRIMARY KEY ( country_name_id ) и повысьте UNIQUE ключ до PRIMARY .

1. спасибо, что вы подразумеваете под вашими первыми предложениями, что я должен сделать, чтобы решить? Вы имеете в виду, что я не использую DATE( user_registration_timestamp ) в подзапросе, а вместо этого использую само поле? о объединении двух таблиц стран, вы имеете в виду, что я использую одну таблицу, а не две? проблема здесь заключается в country_names, потому что некоторые страны имеют более 2 переводов для своего названия. Наконец, каково ваше решение для числа пользователей более 20%, это больше, чем это монтирование.

2. SELECT .. FROM users .. вероятно, это самая медленная часть; сначала убедитесь, что это самая медленная часть, затем подумайте, можно ли ее каким-либо образом переписать. Изменение DATE(...) не поможет. Много ли повторяющихся строк поступает из запроса?

3. Уникальным идентификатором для страны могут быть стандартные 2-буквенные коды (не зависящие от языка), что позволяет избежать INT и необходимости в первой из двух таблиц.

4. 20% — это «эмпирическое правило», определяющее, будет ли оптимизатор использовать индекс, а не сканировать таблицу. Более короткий временной диапазон, вероятно, продемонстрировал бы это.

5. У нас есть 7000 строк для названия страны, в которых невозможно использовать 2-буквенный код.

Ответ №2:

Похоже, самая большая проблема в вашей таблице users. Помните, что mysql может использовать только один индекс для каждой таблицы в большинстве ситуаций. В вашей таблице users user_xmpp_login_UNIQUE столбец использовался для присоединения его к таблице purchase_log. Таким образом, индекс user_registration_timestamp не используется при сравнении, включающем столбец timestamp.

Одно из предложений — создать составной индекс для столбцов user_xmpp_login and user_registration_timestamp .