MySQL. Получить последний ID по параметру.

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
Доброй ночи.
Помогите оптимизировать запрос к БД.
Есть таблица с тремя полями:
1. id - автоинкремент
2. text - произвольный текст
3. int - цифра от 0 до 100 (индексация)

В таблице ~300 миллионов записей. Первая треть таблицы уже имеет рандомные значения в поле int от нуля до 100. Мне нужно получить последний ID, в котором int > 0

Сейчас юзаю:
Код:
SELECT `id` FROM `table` WHERE `int` != 0 ORDER BY `id` DESC LIMIT 1
Но запрос обрабатывается более трёх минут (из-за сортировки). Я прям чувствую что это можно оптимизировать каким нибудь подзапросом, но как?

Повторюсь, как можно быстро вытащить последнюю строку с int > 0?
 

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
id уникальный? )
тогда нажми PRIMARY в php admin рядом с ним... и запрос будет потом выполнятся моментально )
 

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
На нём висит примари.
 
Последнее редактирование:

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
На нём висит примари.
дочитал... 300 млн записей...
тип поля какой к стате и сколько памяти на сервере?
ну и int какой тип, его бы тоже в индекс - т.к. выборка по нему идёт, но не примари само собой?
но на таком кол-ве многое от памяти зависит!
 

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
int - TINYINT
Attributes - UNSIGNED
https://site-do.ru/db/sql2.php
1 байт на ячеку... 300 млн записей - это... 300 млн байт - сколько это мегабайт я чето посчитать не могу :(
 

ol1ver

Client
Регистрация
29.08.2013
Сообщения
332
Благодарностей
82
Баллы
28
а может сделать проще чем хранить 300млн строк? Что вы хотите сделать?
 

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
дочитал... 300 млн записей...
тип поля какой к стате и сколько памяти на сервере?
ну и int какой тип, его бы тоже в индекс - т.к. выборка по нему идёт, но не примари само собой?
но на таком кол-ве многое от памяти зависит!
На сервере 32гб озу.
Mysql никак не настраивался. Так что настройки там дефолтные.

Таблица InnoDB
id - bigint (примари)
name - varchar 255
pole - bigint (индекс)

Твой запрос с max(id) всё ещё не сработал.
 

Вложения

Последнее редактирование:

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
На сервере 32гб озу.
Mysql никак не настраивался. Так что настройки там дефолтные.

Таблица InnoDB
id - longint (примари)
name - varchar 255
number - longint (индекс)

Твой запрос с max(id) всё ещё не сработал.
да индекс в оперативу не помещается банально - поэтому толку от него ноль :(
глянь как у тебя хард хомячит там )
atop 2
потом D
pole - это то что от 0 до 100?
он жрет в 8 раз больше места чем ему надо )
https://site-do.ru/db/sql2.php
 

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
10 млн записей - по 1 байту - это 10Гб оперативы как раз если его сделать как я выше написал то хватит, а сейчас ему надо 80гб )
Но че делать с примари я хз...
Он у тебя в выборке участвует гденить вообще?
Диск ssd я так думаю т.к. на hdd сдохло бы вообще всё уже давно :-)
 

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
а может сделать проще чем хранить 300млн строк? Что вы хотите сделать?
В общем таблица с доменами. в поле с bigint хранится IPv4 в числовом виде.

NodeJS берёт с базы 10кк записей с WHERE pole=0, больше не тянет. Но на 3-4 цикле он упирается в мёртвые сайты, так как 30% доменов не имеют IP.
Сейчас я его руками перезапускаю с дополнительным условием типа: id > 123123123 && pole=0.
Хочу эту задачу перевести на скрипт. Вот и задался вопросом, как можно получить последний ID
 
Последнее редактирование:

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
10 млн записей - по 1 байту - это 10Гб оперативы как раз если его сделать как я выше написал то хватит, а сейчас ему надо 80гб )
Но че делать с примари я хз...
Он у тебя в выборке участвует гденить вообще?
Диск ssd я так думаю т.к. на hdd сдохло бы вообще всё уже давно :-)
Не ssd. Сервак старый :(
 

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
max(id) не сработал. выбило ошибку на 600 секунде.
 

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
max(id) не сработал. выбило ошибку на 600 секунде.
ну там по таймауту он вываливаться будет походу и вы еще не апдейтите как я понял базу )

Терь понятно...
Да задачу над поделить по таблицам )
Например
Таблицы
domain_a
domain_b
domain_с

тогда каждый домен у вас с началом на эту букву надо поместить в эти таблицы...
уже у вас выигрышь пойдет в 26 + 10 цифр, только я не помню можно домены на цифры начинать терь или нет )
Это уже у вас база уменьшится в идеале в 30 раз... как раз будет по 10млн записей - уже оно оживет если сразу их всех не открывать...
Ну и логику над переделать, но не сильно...
Мне одно не понятно как вы туда столько залил и оно не встало? )
 
  • Спасибо
Реакции: Dmffmd

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
16 gb оперативы
MyISAM
6,776,520 записей
SELECT max( id )FROM `table`WHERE `data` >25
Showing rows 0 - 0 ( 1 total, Query took 6.5786 sec)

SELECT `id` FROM `table` WHERE `data` > 25 ORDER BY `id` DESC

Showing rows 0 - 29 ( 6,775,730 total, Query took 0.0006 sec)

Блин а ваш запросец быстрее заметнее )
 

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
Да задачу над поделить по таблицам )
Например
Таблицы
domain_a
domain_b
domain_с
тогда каждый домен у вас с началом на эту букву надо поместить в эти таблицы...
Я от подобной схемы пытался уйти. Сотни таблиц с именами: com net ru... По идее это временная таблица, которая должна просто получить список всех IP а потом спокойно удалиться.
Пока у меня в альтернативных вариантах, создать временную таблицу, куда я буду раз в пару минут записывать ID, который парсится.


Мне одно не понятно как вы туда столько залил и оно не встало? )
Я не ожидал что будут проблемы. Понадобилось получить все IP. Решил собрать все домены и быстро спарсить IPшники. Мало того что по скорости упёрся в update, так ещё и перезапускать приходится вручную.

Есть ещё вариант брать строки с таблицы через LIMIT n, 1000000 и молиться что nodejs не упадёт. На крайняк записывать эту переменную n в отдельную таблицу
 

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
16 gb оперативы
MyISAM
6,776,520 записей
SELECT max( id )FROM `table`WHERE `data` >25
Showing rows 0 - 0 ( 1 total, Query took 6.5786 sec)

SELECT `id` FROM `table` WHERE `data` > 25 ORDER BY `id` DESC

Showing rows 0 - 29 ( 6,775,730 total, Query took 0.0006 sec)

Блин а ваш запросец быстрее заметнее )
Ну так, я ж профи с работой в mysql :-)

Хотя интуитивно первый запрос должен быть куда быстрее.
 

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
А конфиг дефолтный говорите... тогда пробуем так...
надеюсь если у вас рут попробуйте дать такую комманду...
SET global key_buffer_size=17073741824;#16gb

это увеличит длину буфера под индекс...
если подохнет вдруг попробуйте меньше из расчета на 1гб.
SET global key_buffer_size=1073741824;#1gb

параметр будет действовать до перезагрузки
если постоянка нужна то ищите и ставите в my.cnf
key_buffer_size=16gb
и рестарт
 

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
не эт не домены эт другие записи )
просто под ваш тест подходило идеально )
и это на hdd, те наглядно видно когда индексы работают в идеале у вас так же должно быть, т.к. параметр главный в индексе он сразу знает откуда искать (с конца) и там буквально 1-2 проверки....

к предыдущему посту
/phpmyadmin/server_variables.php
key buffer size - тут можно подкрутить его руками еще.
ну и размер я хз может и больше 16 надо )
Смысл в том, что бы его не читать с харда каждый раз...
 

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
А конфиг дефолтный говорите... тогда пробуем так...
надеюсь если у вас рут попробуйте дать такую комманду...
SET global key_buffer_size=17073741824;#16gb

это увеличит длину буфера под индекс...
если подохнет вдруг попробуйте меньше из расчета на 1гб.
SET global key_buffer_size=1073741824;#1gb

параметр будет действовать до перезагрузки
если постоянка нужна то ищите и ставите в my.cnf
key_buffer_size=16gb
и рестарт
А разве это не настройки MyISAM. У меня innodb. Я совершенно не разбираюсь в этих настройках. Вроде бы нашёл то что надо, но оно не редактируется. Буду пробовать с командами.

п.с. root есть, mysql висит на винде.
 

Вложения

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
А разве это не настройки MyISAM. У меня innodb. Я совершенно не разбираюсь в этих настройках. Вроде бы нашёл то что надо, но оно не редактируется. Буду пробовать с командами.

п.с. root есть, mysql висит на винде.
вот может и это надо почитать мануал если честно... :(
 
  • Спасибо
Реакции: Dmffmd

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
Ну вот а говорите не шарите :-))
Судя по всему то что надо, а я ошибся...
https://dev.1c-bitrix.ru/support/forum/forum6/topic76755/
Откуда у вас MyISAM-таблицы? Если это таблицы с данными, то нельзя ли их конвертировать в InnoDB?
И даже если нельзя, то key_buffer_size можно уменьшить до пары мегабайт, а освободившиеся 14 МБ добавить в innodb_buffer_pool_size.
и да еще многое чего может зависить временные таблицы например
max_heap_table_size = 32M
tmp_table_size = 32M

Как понять чего не хватает mysql самой много чего написано...
Через сервер статус посмотрите еще какой параметр красным светится и можно покопаться в инете как его оптимизировать, может и потащит 300кк записей )
 
Последнее редактирование:
  • Спасибо
Реакции: Dmffmd

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
p.s. нашел у себя innodb buffer pool size 3gb стояло )
 
  • Спасибо
Реакции: Dmffmd

Dmffmd

Client
Регистрация
08.04.2017
Сообщения
72
Благодарностей
29
Баллы
18
p.s. нашел у себя innodb buffer pool size 3gb стояло )
Спасибо тебе за помощь. Настройки конфига значительно ускорили работу скрипта. Только теперь мне не хватает ОЗУ :(
Запрос из первого поста теперь выполняется за 20 сек, вместо 300.

П.с. Конфиги взял отсюда: https://dba.stackexchange.com/questions/50941/the-best-mysql-settings-for-32gb-ram-on-a-dedicated-server
 

ol1ver

Client
Регистрация
29.08.2013
Сообщения
332
Благодарностей
82
Баллы
28
Я так понял тянете домен, затем повторно он не может участвовать в раздаче? дак присваивайте ему после отдачи used=1
SELECT id FROM table_name WHERE used=0
$id = // получаем id после запроса

UPDATE table_name
SET used=1
WHERE id= $id

ну и правильно сказали, разбейте на таблицы домены, зачем сразу всё грузить
 

Lord_Alfred

Client
Регистрация
09.10.2015
Сообщения
3 916
Благодарностей
3 852
Баллы
113
уже у вас выигрышь пойдет в 26 + 10 цифр, только я не помню можно домены на цифры начинать терь или нет )
Можно.


Тут скорее нужно не придумывать как оптимизировать текущую кашу, а продумать другую архитектуру + правильно построить индексы. То есть лучше свести задачу к переносу текущей структуры во что-то другое, а потом уже работать с ней.
 

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113
Спасибо тебе за помощь. Настройки конфига значительно ускорили работу скрипта. Только теперь мне не хватает ОЗУ :(
Запрос из первого поста теперь выполняется за 20 сек, вместо 300.

П.с. Конфиги взял отсюда: https://dba.stackexchange.com/questions/50941/the-best-mysql-settings-for-32gb-ram-on-a-dedicated-server
  • Some benchmarks indicate that actually switching off the query cache helps performance. You may want to experiment with this. To switch it off, use query_cache_size=0, query_cache_type=0.
Вот да наверное лучше вам это дело отключить, один фиг не поможет кеширование...
Сколько в итоге под innodb buffer pool size выделили может надо еще чуть чуть и будет еще быстрее? :-)

К стате советую 2 форума по mysql меня там выручают очень часто, можете описать проблему может подскажут.
http://www.cyberforum.ru/mysql/ - лучший имхо
http://www.sql.ru/forum/mysql - тоже пишу иногда
 
  • Спасибо
Реакции: Dmffmd

falke2

Новичок
Регистрация
17.06.2017
Сообщения
1
Благодарностей
0
Баллы
1

Gfoblin

Client
Регистрация
30.05.2013
Сообщения
4 510
Благодарностей
990
Баллы
113

Кто просматривает тему: (Всего: 1, Пользователи: 0, Гости: 1)