Работа с MyISAM таблицей с кол-вом записей от 10'000'000?

Question

[KOS_MOS]

Есть таблица, где хранятся записи о товарах, всего товарах около 10'000'000 — количество может вырасти до 20'000'000.

Вывод работает нормально, но заказчик хочет еще админку к этой базе для анализа товаров, поставщиков и прочего — это будет выглядеть как фильтры для каждого поля бд.


Запрос на создание таблицы:

CREATE TABLE `suppliers_store` (<br/>
 `id_suppliers_store` int(11) NOT NULL AUTO_INCREMENT,<br/>
 `id_suppliers` int(11) NOT NULL,<br/>
 `dt` date NOT NULL,<br/>
 `name` varchar(255) NOT NULL,<br/>
 `code` varchar(255) NOT NULL,<br/>
 `price` double NOT NULL,<br/>
 `code_suppliers` varchar(255) NOT NULL,<br/>
 `count` int(11) NOT NULL DEFAULT '0',<br/>
 `producer` varchar(255) NOT NULL,<br/>
 `weight` varchar(255) NOT NULL,<br/>
 PRIMARY KEY (`id_suppliers_store`),<br/>
 KEY `NewIndex1` (`id_suppliers`),<br/>
 KEY `NewIndex2` (`dt`)<br/>
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Вот примерный запрос, с условиями, которые может сгенерировать фильтр:

select ss.*, s.delivery<br/>
from suppliers_store as ss<br/>
join suppliers as s on ss.id_suppliers = s.id_suppliers<br/>
where (ss.dt &gt;= '2010-02-01 00:00:00' and ss.dt &lt;= '2011-02-01 23:59:59') and <br/>
 (ss.name like '%панель%' or ss.code like '%панель%') and<br/>
 (ss.price &gt; 1000) and<br/>
 (ss.count &gt; 0)<br/>
LIMIT 50

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


Раньше опыта работы с такими объемами не было, насколько mysql подходит для этих целей?

Как можно увеличить скорость поиска по табличке?

Comments

[niko83]

покажите show create table и пример запроса который будет «шерстить базу» как вы выразились, тогда может кто-то что-то более конкрентное подскажет

Answer 1

[mitnlag]

(опциональный шаг:
MyISAM — На свалку.
Базу — в InnoDB.
)
Про fulltext Забудьте — он вам столько геморроя доставит, не вылечитесь.

В качестве поискового индекса используйте мегабыстрый опенсорсный зрелый sphinx.

Answer 2

[pwlnw]

Как-то ты неправильно понимаешь СУБД.

Comments

[KOS_MOS]

и что же не так?

[pwlnw]

Дело то не в объеме, а в понимании механизма работы. В общем случае индексы помогают при выборе из таблиц независимо от их размера.
В определенных условиях могут помогать не очень.

[KOS_MOS]

Ну по числовым полям индекс понятно что поможет, меня больше всего смущает поиск по текстовым полям — объем индекса будет достаточно велик, если учитывать что все записи в текстовых полях практически уникальны, то непонятно каким образом в этих случаях индекс даст прирост.

[pwlnw]

И что? запрос where ss.name='панель' все равно будет ускорен с помощью индекса.
Индекс работает по любым полям, если они допускают сравнение и, как следствие, индекс укладывается в дерево — btree. Для текстовых поле в запросе с LIKE 'панель%' индекс все-таки будет работать.
А вот твоем LIKE '%панель%' не будет работать — это и есть специальный случай записи условия.
Тут лучше попробовать применить fulltext-индексы dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

Заметь, нигде я не упомянул размер таблиц.

[KOS_MOS]

спасибо за разъяснение!

Answer 3

[Valistar]

1. Поля с датами и ценами — индексы (тогда должно ходить по индексам)

2. Для полей со строками — два варианта — либо не искать с % в начале (не пойдет тогда по индексу)

либо делается отдельная табличка со словами. При INSERT/UPDATE в нее пишется все слова в строках. Например insert into words (id, type, word) select suppliers_store.id, 'name', 'панель' и делается индекс (type, word, id). Соотв. поиск производится по этой табличке без испрользования %.

Comments

[KOS_MOS]

Спасибо, особенно за второй пункт

Answer 4

[Алексей]

> индексы по полям, как я понимаю, особо не помогут

Это с чего это вдруг?

Comments

[KOS_MOS]

ну так как фильтр может использовать любое поле из таблицы, то индексы придется ставить на все поля.
Да и размер индексов будет достаточно велик, так что прироста производительности к примеру в поиске по текстовым полям скорее всего не будет.

Answer 5

[Анатолий]

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

Comments

[KOS_MOS]

понял, спасибо

[Алексей]

Для этих целей есть FULLTEXT INDEX… не изобретайте велосипедов.

[Анатолий]

Конечно есть, но даже FULL TEXT будет работать не очень быстро на очень больших данных + Места для ключа он будет занимать очень много. Вообще такими же темпами можно вставить и сфинкс или подобные

Answer 6

[bdmalex]

Есть таблица, где хранятся записи о товарах, всего товарах около 10'000'000 — количество может вырасти до 20'000'000.

Я бы задался, только одним вопросом, а что будет «если вдруг» количество возрастёт не 20000000, а скажем до 40000000? А судя «по постановке задачи» — это не так уж и нереально…