ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация простого запроса SELECT в огромной базе.
18 сообщений из 18, страница 1 из 1
Оптимизация простого запроса SELECT в огромной базе.
    #39771615
opiums
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
opiums
Гость
Здравствуйте, у меня есть сайт со списком разных хэшей. Изначально запросы и ответы выполнялись достаточно быстро, но со временем, когда база разрослась до 2,5 млн строк возникла необходимость какой то оптимизации, потому что 1 запрос выполняется уже по 0,5 секунды, кроме того очень сильно возрастает нагрузка на ЦП, если эти запросы слать один за другим.
Структура одной из 3-х таблиц такова:
Код: sql
1.
2.
3.
4.
5.
6.
7.
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| hash  | varchar(32)      | NO   |     |         |                |
| text  | varchar(2048)    | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+


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


Подскажите пожалуйста, как возможно оптимизировать базу и запросы, чтобы снизить время и нагрузку на ЦП?
...
Рейтинг: 0 / 0
09.02.2019, 17:08
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771679
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
opiums,
Индексировать поля, участвующие в условиях фильтрации, не пробовали?
...
Рейтинг: 0 / 0
09.02.2019, 21:43
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771710
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
opiums,

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

И для хранения хэша MD5 эффективнее использовать поле BINARY(16), желательно NOT NULL.
...
Рейтинг: 0 / 0
09.02.2019, 23:45
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771752
opiums
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
opiums
Гость
А будет ли какой то эффект, если создать индексы? Ведь там же все значения уникальные и повторяются только 1 раз.

Более полная информация о таблице:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
+--------------+-----------------------------------------------------------------------------------------+
| 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 |
+--------------+-----------------------------------------------------------------------------------------+
...
Рейтинг: 0 / 0
10.02.2019, 07:51
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771806
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
opiumsА будет ли какой то эффект, если создать индексы? Ведь там же все значения уникальные и повторяются только 1 раз.

Так вы попробуйте.... И нам рассказать не забудьте - есть эффект от индекса или нет.
...
Рейтинг: 0 / 0
10.02.2019, 13:34
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771813
opiums
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
opiums
Гость
Щукина Анна,

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



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


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



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


Результат выполнения: Empty set
Сравнил таблицы, оказалось что на более новой версии создаётся индекс text(333), применил данное к основному серверу, получилось, но и там теперь так же не могу посмотреть время выполнения.
...
Рейтинг: 0 / 0
10.02.2019, 14:28
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771815
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
opiums
Код: sql
1.
SELECT `Hash` FROM `deHasher_md5` WHERE `Text`='123'


Этот запрос смысла не имеет, его лучше вообще выкинуть. Имеет смысл запрос
Код: sql
1.
SELECT `Text` FROM `deHasher_md5` WHERE `hash`='010203040506070809'

и для него нужен индекс по полю hash.
...
Рейтинг: 0 / 0
10.02.2019, 14:37
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771894
Фотография Дегтярев Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дегтярев Евгений
Участник
Dimitry Sibiryakov и для него нужен индекс по полю hash.
зачем, оно же уникальное
...
Рейтинг: 0 / 0
10.02.2019, 18:38
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771897
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
opiumsТо получил сообщение об ошибке:
Код: sql
1.
1071 - Specified key was too long; max key length is 1000 bytes

А всего-то надо было префикс указать.
...
Рейтинг: 0 / 0
10.02.2019, 19:02
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771898
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Дегтярев ЕвгенийDimitry Sibiryakov и для него нужен индекс по полю hash.
зачем, оно же уникальноеИз определения таблицы это никак не следует.
И чем более уникально значение в поле, тем эффективнее будет индекс по нему.
...
Рейтинг: 0 / 0
10.02.2019, 19:05
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771901
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Дегтярев ЕвгенийDimitry Sibiryakov и для него нужен индекс по полю hash.
зачем, оно же уникальное
А база об этом знает?

Ну и почему hash уникален? Любой hash по своей идее не уникален в общем случае. Может быть уникален только в частном случае
...
Рейтинг: 0 / 0
10.02.2019, 19:14
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771975
opiums
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
opiums
Гость
Да там вообще все значения уникальные, нет повторяющихся
...
Рейтинг: 0 / 0
11.02.2019, 04:16
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39771981
Фотография Дегтярев Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дегтярев Евгений
Участник
MelkijДегтярев Евгенийпропущено...

зачем, оно же уникальное
А база об этом знает?

то был сарказм
...
Рейтинг: 0 / 0
11.02.2019, 06:18
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39772088
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
[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.
...
Рейтинг: 0 / 0
11.02.2019, 11:27
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39772091
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
[quot opiums]Щукина Анна,

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



То получил сообщение об ошибке:
Код: sql
1.
2.
3.
4.
5.
1071 - Specified key was too long; max key length is 1000 bytes

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



Префикс поля индекса не может иметь длину больше некоторой величины, которая зависит от версии MySQL, например, что-то типа 512 байт.
Размер префикса МАЛО влияет на эффективность индекса, если он достаточно большой. Ну задай тупо по максимуму на твоей версии, и всё.
...
Рейтинг: 0 / 0
11.02.2019, 11:31
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39772301
Фотография полудух
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
полудух
Участник
MasterZivПодскажите пожалуйста, как возможно оптимизировать базу и запросы, чтобы снизить время и нагрузку на ЦП?

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

и отдельно вопрос про постгрес (там нельзя указывать размер индекса)
отсюда вопрос, если поле text 2000 символов, то это же дичь в индексе получится?
есть ли смысл создать отдельную колонку с маленьким текстом и индексировать её?
...
Рейтинг: 0 / 0
11.02.2019, 15:20
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39772326
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
полудухи отдельно вопрос про постгрес (там нельзя указывать размер индекса)
Можете проиндексировать любую immutable функцию. Например, индекс по left(text, 5) и искать по нему же.
Ну или начиная с 10 просто hash индекс построить.
...
Рейтинг: 0 / 0
11.02.2019, 15:44
| Ответить | Цитировать | Написать  
Оптимизация простого запроса SELECT в огромной базе.
    #39773466
transpose
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
transpose
Участник
А не быстрее будет считать хэш на лету? Зачем доставать его из базы данных? Я ещё понимаю искать текст по хешу, но не наоборот.
...
Рейтинг: 0 / 0
13.02.2019, 16:01
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация простого запроса SELECT в огромной базе.
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=40&tid=1829310]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 37ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 1ms
others: 300ms
total:  431ms
созд. / загр.: 431ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]