Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Оптимизация простого запроса SELECT в огромной базе.  [new]
opiums
Member

Откуда: Междуреченск
Сообщений: 50
Здравствуйте, у меня есть сайт со списком разных хэшей. Изначально запросы и ответы выполнялись достаточно быстро, но со временем, когда база разрослась до 2,5 млн строк возникла необходимость какой то оптимизации, потому что 1 запрос выполняется уже по 0,5 секунды, кроме того очень сильно возрастает нагрузка на ЦП, если эти запросы слать один за другим.
Структура одной из 3-х таблиц такова:
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| hash  | varchar(32)      | NO   |     |         |                |
| text  | varchar(2048)    | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+

Время выполнения 1-го запроса:
+----------+----------+------------------------------------------------------+
| Query_ID | Duration | Query                                                |
+----------+----------+------------------------------------------------------+
|        1 | 0,540957 | SELECT `Hash` FROM `deHasher_md5` WHERE `Text`='123' |
+----------+----------+------------------------------------------------------+

Подскажите пожалуйста, как возможно оптимизировать базу и запросы, чтобы снизить время и нагрузку на ЦП?
9 фев 19, 17:08    [21805307]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1254
opiums,
Индексировать поля, участвующие в условиях фильтрации, не пробовали?
9 фев 19, 21:43    [21805439]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
miksoft
Member

Откуда:
Сообщений: 37384
opiums,

Показывайте полноценный DDL таблицы и план запроса.

И для хранения хэша MD5 эффективнее использовать поле BINARY(16), желательно NOT NULL.
9 фев 19, 23:45    [21805480]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
opiums
Member

Откуда: Междуреченск
Сообщений: 50
А будет ли какой то эффект, если создать индексы? Ведь там же все значения уникальные и повторяются только 1 раз.

Более полная информация о таблице:
+--------------+-----------------------------------------------------------------------------------------+
| Table        | Create Table                                                                            |
+--------------+-----------------------------------------------------------------------------------------+
| dehasher_md5 | CREATE TABLE `dehasher_md5` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hash` varchar(32) NOT NULL DEFAULT '',
  `text` varchar(2048) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2434069 DEFAULT CHARSET=utf8 |
+--------------+-----------------------------------------------------------------------------------------+
10 фев 19, 07:51    [21805540]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1254
opiums
А будет ли какой то эффект, если создать индексы? Ведь там же все значения уникальные и повторяются только 1 раз.


Так вы попробуйте.... И нам рассказать не забудьте - есть эффект от индекса или нет.
10 фев 19, 13:34    [21805635]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
opiums
Member

Откуда: Междуреченск
Сообщений: 50
Щукина Анна,

Когда создавал индексы (надеюсь правильно) для таблицы dehasher_md5:
CREATE INDEX hash ON dehasher_md5(hash);
CREATE INDEX text ON dehasher_md5(text); 


То получил сообщение об ошибке:
1071 - Specified key was too long; max key length is 1000 bytes

Попытался перевести в InnoDB и получил аналогичное сообщение:
1071 - Specified key was too long; max key length is 3072 bytes


Перенёс базу на другой сервер, где MySQL 10.1.26-MariaDB-0+deb9u1, индексы создались, особо изменений не почувствовал и не могу посмотреть время...
SET profiling = 1;
Запросы...
SHOW PROFILES;

Результат выполнения: Empty set
Сравнил таблицы, оказалось что на более новой версии создаётся индекс text(333), применил данное к основному серверу, получилось, но и там теперь так же не могу посмотреть время выполнения.
10 фев 19, 14:28    [21805648]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 47264
opiums
SELECT `Hash` FROM `deHasher_md5` WHERE `Text`='123'

Этот запрос смысла не имеет, его лучше вообще выкинуть. Имеет смысл запрос
SELECT `Text` FROM `deHasher_md5` WHERE `hash`='010203040506070809'
и для него нужен индекс по полю hash.
10 фев 19, 14:37    [21805656]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
Дегтярев Евгений
Member

Откуда: Барнаул
Сообщений: 1529
Dimitry Sibiryakov
и для него нужен индекс по полю hash.

зачем, оно же уникальное
10 фев 19, 18:38    [21805774]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
miksoft
Member

Откуда:
Сообщений: 37384
opiums
То получил сообщение об ошибке:
1071 - Specified key was too long; max key length is 1000 bytes
А всего-то надо было префикс указать.
10 фев 19, 19:02    [21805779]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
miksoft
Member

Откуда:
Сообщений: 37384
Дегтярев Евгений
Dimitry Sibiryakov
и для него нужен индекс по полю hash.

зачем, оно же уникальное
Из определения таблицы это никак не следует.
И чем более уникально значение в поле, тем эффективнее будет индекс по нему.
10 фев 19, 19:05    [21805781]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 759
Дегтярев Евгений
Dimitry Sibiryakov
и для него нужен индекс по полю hash.

зачем, оно же уникальное

А база об этом знает?

Ну и почему hash уникален? Любой hash по своей идее не уникален в общем случае. Может быть уникален только в частном случае
10 фев 19, 19:14    [21805784]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
opiums
Member

Откуда: Междуреченск
Сообщений: 50
Да там вообще все значения уникальные, нет повторяющихся
11 фев 19, 04:16    [21806011]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
Дегтярев Евгений
Member

Откуда: Барнаул
Сообщений: 1529
Melkij
Дегтярев Евгений
пропущено...

зачем, оно же уникальное

А база об этом знает?

то был сарказм
11 фев 19, 06:18    [21806024]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34421
[quot opiums]Здравствуйте, у меня есть сайт со списком разных хэшей. Изначально запросы и ответы выполнялись достаточно быстро, но со временем, когда база разрослась до 2,5 млн строк возникла необходимость какой то оптимизации, потому что 1 запрос выполняется уже по 0,5 секунды,

полсекунды -- это НЕ МНОГО.

кроме того очень сильно возрастает нагрузка на ЦП, если эти запросы слать один за другим.

СУБД не может выполнять запросы не используя CPU.
Как правило, чем больше загрузка CPU, тем __ЛУЧШЕ__ работает СУБД.


Структура одной из 3-х таблиц такова:

У тебя только одна таблица в запросе используется...


+----------+----------+------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+----------+------------------------------------------------------+
| 1 | 0,540957 | SELECT `Hash` FROM `deHasher_md5` WHERE `Text`='123' |
+----------+----------+------------------------------------------------------+

Подскажите пожалуйста, как возможно оптимизировать базу и запросы, чтобы снизить время и нагрузку на ЦП?

Создать индекс на поле Text.
11 фев 19, 11:27    [21806183]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34421
[quot opiums]Щукина Анна,

Когда создавал индексы (надеюсь правильно) для таблицы dehasher_md5:
CREATE INDEX hash ON dehasher_md5(hash);
CREATE INDEX text ON dehasher_md5(text); 


То получил сообщение об ошибке:
1071 - Specified key was too long; max key length is 1000 bytes

ТЕбе надо задать размер префикса в поле индекса. Погляди как это делается в документации.
будет что-то типа
[SRC sql]CREATE INDEX hash ON dehasher_md5(hash(100));


Префикс поля индекса не может иметь длину больше некоторой величины, которая зависит от версии MySQL, например, что-то типа 512 байт.
Размер префикса МАЛО влияет на эффективность индекса, если он достаточно большой. Ну задай тупо по максимуму на твоей версии, и всё.
11 фев 19, 11:31    [21806187]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
полудух
Member

Откуда: планета орков, г.Зверополис
Сообщений: 506
MasterZiv
Подскажите пожалуйста, как возможно оптимизировать базу и запросы, чтобы снизить время и нагрузку на ЦП?

Создать индекс на поле Text.

кстати, вот хотелось бы уточнить один момент - размер индекса
надо ЛИ делать индекс на все 32 байта (16 binary) ?
кол-во хешей уже после 5 байт останется минимальным
скажем, с миллиарда хешей останется 10-20-50 - их уже и без индекса можно прошерстить мгновенно

и отдельно вопрос про постгрес (там нельзя указывать размер индекса)
отсюда вопрос, если поле text 2000 символов, то это же дичь в индексе получится?
есть ли смысл создать отдельную колонку с маленьким текстом и индексировать её?
11 фев 19, 15:20    [21806517]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 759
полудух
и отдельно вопрос про постгрес (там нельзя указывать размер индекса)

Можете проиндексировать любую immutable функцию. Например, индекс по left(text, 5) и искать по нему же.
Ну или начиная с 10 просто hash индекс построить.
11 фев 19, 15:44    [21806559]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация простого запроса SELECT в огромной базе.  [new]
transpose
Member

Откуда:
Сообщений: 188
А не быстрее будет считать хэш на лету? Зачем доставать его из базы данных? Я ещё понимаю искать текст по хешу, но не наоборот.
13 фев 19, 16:01    [21808639]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить