MySQL ускорение работы

[finista]

Есть таблица 5,5 млн. строк, 12 столбцов.
Регуляркой ищу совпадение по домену в одном столбце..

На запрос уходит 25-30 сек., что не реально долго.

Вопрос. Как ускорить работу?

Варианты:
- увеличить оперативки на сервере (сейчас 1GB);
- разбить базу на отдельные таблицы и искать совпадение, а потом по ID смотреть другие параметры;
- не правильно составил SQL запрос (сейчас простой слект и условие по регулярке)
- другие варианты

Первый раз сталкиваюсь с такой ситуацией, явно есть решение у людей и по 200 млн. строк в базе, как то работают ....

Спасибо

 

[luk911]

Оперативки явно мало. В идеале всю базу в кешь класть. SSD надеюсь ? Создать индексы. Если оперативка позволит они лягут в оперативку. Это существенно ускорит выборки.
Таблицы в inodb ?

 

[finista]

Тип базы inodb. Диск SSD.

По оперативке понял. На перспективу база будет 50GB - на первый взгляд дорого если вопрос решать оперативной.

Вопрос по индексам остался, сейчас есть Autoindex - или нужны еще какие индексы использовать?

Спасибо

 

[kagorec]

Вопрос по индексам остался, сейчас есть Autoindex - или нужны еще какие индексы использовать?

ALTER TABLE xtable ADD INDEX `idx` (`id` ASC);

 

[uuw]

первое
по дефолту mysql встает с конфигом для mysam, innodb на нем еле шевелится
если в дебри не лезть - этого хватит

Оптимальная настройка Mysql - Highload.today

Правильная настройка Mysql под нагрузки и не только. Обновлено.

ruhighload.com

по индексу без регулярки никак?
индексацию по регуляркам mysql не поддерживает
upd:
1г оперативки - ни о чем
структуру таблицы сбрось, видно будет что сделать можно
индекс по id никак не относится к твоему запросу

 

[backoff]

сейчас 1GB

всего 1гб на машине?
это что за машина то такая

 

[Gfoblin]

Оперативку и конфиг под inno - всё что в выборках должно быть индексировано - сразу ускоряет в 1000 раз )
Приведи пример своего запроса ты ищешь как where domain='blabla.com'; или where domain like '%blabla.com%';

 

[finista]

Проиндексировал по колонке email, решил на ней тренироваться

SELECT *
FROM `ym2020`
WHERE `email`
REGEXP '@vseinstrumenti.ru'
LIMIT 0 , 30

Отображает строки 0 - 29 ( 649 всего, запрос занял 2.0218 сек.)

SELECT *
FROM `ym2020`
WHERE `email` LIKE '[email protected]'
LIMIT 0 , 30

Отображает строки 0 - 29 ( 30 всего, запрос занял 0.0071 сек.)

Чуть позже настрою mysql для innodb и что-то должно получиться

Выводы:
- при формировании структуры базы лучше не использовать данные которые потом регуляркой искать
- по возможности базу разбить на несколько таблиц (для каждой группы данных своя таблица) и связать по ID
- настройка базы
- сервер не 1GB оперативки

Хостинг был стандартный reg.ru там 1 GB не самый лайт тариф

 

[luk911]

Вот тут куда интереснее тарифы на вдски и цены намного дешевле.
6 гигов и 4 ядра за 859 р. это ssd

 

[ZULI]

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

 

[luk911]

И да еще совет ставить не стандартную mysql, а лучше percona server (та же mysql только оптимизированная) ну или маша дб (mariadb) , тоже не плохо.

 

[uuw]

- по возможности базу разбить на несколько таблиц (для каждой группы данных своя таблица) и связать по ID

Называется - нормализация
Первое что делают когда база раком - денормализуют, т.е. обратную операцию
не майся херней - будет тормознее

SELECT *
FROM `ym2020`
WHERE `email` LIKE '[email protected]'
LIMIT 0 , 30

Правильно будет
SELECT * FROM `ym2020` WHERE `email` = '[email protected]' LIMIT 0 , 30;
LIKE это про другое и не надо захламлять

- при формировании структуры базы лучше не использовать данные которые потом регуляркой искать

выше писал - mysql НЕ поддерживает регулярки в индексах, да и без индексов ты уже увидел

 

[uuw]

маша дб (mariadb)

вроде как на всех хостингах она и стоит стандартная, с ораклом не связываются
да и не хуже он если че
upd: Уже заговариваться начал, не на хостингах, а в дистрибутивах по дефолту
хотя на хостингах она тож

 

[luk911]

sudo apt install mariadb-server как бы отличается от sudo apt install mysql-server

 

[luk911]

с ораклом не связываются
да и не хуже он если че

Не понял причем здесь Оракл.
я про Percona

How To Install a Fresh Percona Server or Replace MySQL | DigitalOcean

Percona Server is an open-source replacement for MySQL and MariaDB. This tutorial shows you how to replace an existing database installation, or start from s…

www.digitalocean.com

Installing Percona Server for MySQL 5.7 on Debian and Ubuntu - Percona Server for MySQL

Ready-to-use packages are available from the Percona Server for MySQL software repositories and the download page.

www.percona.com

 

[Hartwell]

изменить подход к структуре, отказаться от регулярок. если основная задача селективность по доменам, при невозможности разбить строку, все это дело классно реверсом делается.
[email protected]
[email protected]

разворачиваем строки, селектим сначало tld .com, отсекаем в случае с com не большую часть, но существенную. Переходим к домену, тут на корп доменах вообще будет молниеносно так как их довольно не много, ну посложнее будет со всякими gmail и другими популярными доменами. Все же уже будут более адекватные таймы по выборкам

Troy Hunt: Working with 154 million records on Azure Table Storage – the story of “Have I been pwned?”

These real world experiences with Azure are now available in the Pluralsight course "Modernizing Your Websites with Azure Platform as a Service" [http://www.pluralsight.com/courses/modernizing-websites-microsoft-azure]I’m one of these people that must learn by doing. Yes, I’m sure all those...

www.troyhunt.com

 

[ZULI]

sudo apt install mariadb-server как бы отличается от sudo apt install mysql-server

В последних дистрибутивах вроде как при apt install mysql-server все равно мария ставится насколько помнится.

 

[finista]

изменить подход к структуре, отказаться от регулярок. если основная задача селективность по доменам, при невозможности разбить строку, все это дело классно реверсом делается.
[email protected]
[email protected]

разворачиваем строки, селектим сначало tld .com, отсекаем в случае с com не большую часть, но существенную. Переходим к домену, тут на корп доменах вообще будет молниеносно так как их довольно не много, ну посложнее будет со всякими gmail и другими популярными доменами. Все же уже будут более адекватные таймы по выборкам

Troy Hunt: Working with 154 million records on Azure Table Storage – the story of “Have I been pwned?”

These real world experiences with Azure are now available in the Pluralsight course "Modernizing Your Websites with Azure Platform as a Service" [http://www.pluralsight.com/courses/modernizing-websites-microsoft-azure]I’m one of these people that must learn by doing. Yes, I’m sure all those...

www.troyhunt.com

спасибо - ведь действительно )

 

[uuw]

Не понял причем здесь Оракл.

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

 

[Hartwell]

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

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

 

[uuw]

можно и так сказать - суть не меняется