Форум пользователей MySQL

SaSa · 16.02.2011 09:48:54

Добрый день!

есть таблица с продуктами вот такого плана

CREATE TABLE `product` (

  `productId` int(11) NOT NULL AUTO_INCREMENT,

  `merchantId` int(11) NOT NULL,

  `categoryId` varchar(255) NOT NULL DEFAULT '0',

  `article` varchar(255) NOT NULL DEFAULT '',

  `name` varchar(150) NOT NULL,

  `cost` float NOT NULL,

  `url` varchar(255) NOT NULL,

  `imageUrl` varchar(255) DEFAULT NULL,

  `description` varchar(200) DEFAULT NULL,

  `deliveryCost` float DEFAULT NULL,

  `deliveryTime` varchar(100) DEFAULT NULL,

  `ean` varchar(255) DEFAULT NULL,

  `tms` datetime DEFAULT NULL,

  PRIMARY KEY (`productId`),

  UNIQUE KEY `NewIndex1` (`merchantId`,`article`),

  FULLTEXT KEY `name` (`name`),

  FULLTEXT KEY `name` (`name`,`description`)

) ENGINE=MyISAM AUTO_INCREMENT=28277690 DEFAULT CHARSET=utf8

в нее постоянно будет производиться запись и чтение, БД предполагает более 10 млн записей я бы сказал от 10 до 50

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

LOAD DATA LOCAL INFILE  

'$file'  

REPLACE INTO TABLE product...

таких операций в день предполагается более тысячи а то и больше

по этой таблице пользователи будут постоянно производить поиск по описанию и имени продукта (name,description)

С подобной задачей столкнулся впервые, уже несколько дней читаю информацию по данному вопросу, сначала хотел разделить таблицу на части, но явного признака разделения я не увидел к тому же я бы не смог использовать FULLTEXT , сгенерил таблицу из 10млн записей для теста, вроде как поиск проходит быстро от 0 до 5 сек, хотя хотелось бы исключить долгие отработки БД, таблица с индексами занимают на диске 10ГБ...

появилась проблема, COUNT(productId) из выборки поиском считает очень медленно

SELECT 

       COUNT(productId) 

FROM 

    product

WHERE 

    MATCH (name,description) AGAINST ('+$keyword' IN BOOLEAN MODE)

Хотелось бы узнать мнение экспертов, правильно ли я создал таблицу? как можно ее оптимизировать? как посчитать COUNT?
а может я вообще не так все делаю?
заранее благодарю)

SaSa · 16.02.2011 11:06:44

Появилась еще одна проблема, ORDER BY cost работает еще медленнее чем COUNT()

SELECT
   p.*,
   m.name merchantName,
   m.url merchantUrl,
   m.delivery
FROM
product p
LEFT JOIN merchant m USING(merchantId)
WHERE
p.productId IN (
SELECT
   productId
FROM
product
WHERE
MATCH (p.name,p.description) AGAINST ('+$keyword' IN BOOLEAN MODE)
)
AND m.status = 'ACTIVE'
ORDER BY p.cost
LIMIT 750

если проиндексировать этот столбец, это повлияет на производительность?

Отредактированно SaSa (16.02.2011 11:39:55)

paulus · 21.02.2011 12:04:55

Я бы побоялся делать такую большую табличку в MyISAM. Можно попробовать
использовать InnoDB + sphinx.

Теперь то, что касается производительности. Если делать COUNT(*), а не
COUNT(productId) — должно быть сильно быстрее, т.к. эту информацию можно
получить из полнотекстового индекса, а productId — нужно бежать за данными
в саму таблицу.

Что касается ORDER BY — тут Вам точно стоит пересмотреть архитектуру. MySQL
использует не более одного индекса, когда выбирает из таблицы. В Вашем слу-
чае Вы используете полнотекстовый. Он обладает приятной особенностью выби-
рать наиболее релевантные строки первыми, но если Вам нужна сортировка по
другому столбцу — будете сортировать в памяти.

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

SaSa · 21.02.2011 14:02:45

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

делаем поиск и записываем результат во временную таблицу

CREATE TEMPORARY TABLE 

product_ids_tmp SELECT 9730 AS user, productId 

FROM product 

WHERE MATCH (name,description) AGAINST ('+allgemeiner ' IN BOOLEAN MODE)

далее вычисляем количество результатов

SELECT COUNT(productId) FROM product_ids_tmp WHERE user='9730'

ну и наконец собираем всю информацию

SELECT p.*, m.name merchantName, m.url merchantUrl, m.delivery 

FROM product p 

LEFT JOIN merchant m USING(merchantId) 

INNER JOIN product_ids_tmp ptmp ON ptmp.productId = p.productId AND ptmp.user='9730' WHERE m.status = 'ACTIVE' ORDER BY p.cost asc LIMIT 7500

А вообще как вы считаете, подобная табличка может существовать как единая? ведь это достаточно большой объем для Mysql.

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

CREATE TABLE `product` (

  `keyword` varchar(11),

  `ids` text,  

  UNIQUE KEY (`keyword`) 

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Где каждому кейворду будет соответствовать набор айдишников продуктов, в которых встречается данное слово, сколько же строк тут будет, думаю что очень много, но эту таблицу уже можно хоть как-то партиционировать хотя бы по алфавиту..

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

paulus · 21.02.2011 17:31:39

InnoDB более устойчива ко всякого рода отказам, поэтому я ее люблю больше.

Если делать через временную табличку, возможно, в ней стоит добавить ключик
на user — сразу сможете искать нужные строки

Что касается оптимизации в целом — да, нужно придумывать какие-то сложные
методы, т.к. простые не работают: Вы используете полнотекстовые индексы не
так, как это делают все остальные.

SaSa · 10.03.2011 09:45:48

Ну вот, изменил архитектуру, избавился от полнотекстовых индексов, теперь таблица с продуктами имеет вид

CREATE TABLE `product` (

  `productId` int(11) NOT NULL AUTO_INCREMENT,

  `merchantId` int(11) NOT NULL,

  `categoryId` varchar(255) NOT NULL DEFAULT '0',

  `article` varchar(255) NOT NULL DEFAULT '',

  `name` varchar(300) NOT NULL,

  `cost` float NOT NULL,

  `url` varchar(255) NOT NULL,

  `imageUrl` varchar(255) DEFAULT NULL,

  `description` varchar(500) DEFAULT NULL,

  `deliveryCost` float DEFAULT NULL,

  `deliveryTime` varchar(100) DEFAULT NULL,

  `ean` varchar(255) DEFAULT NULL,

  `tms` datetime DEFAULT NULL,

  PRIMARY KEY (`productId`),

  UNIQUE KEY `uniqueMerchantProduct` (`merchantId`,`article`),

  KEY `NewIndex1` (`cost`),

  KEY `NewIndex2` (`categoryId`),

  KEY `NewIndex3` (`ean`)

) ENGINE=InnoDB AUTO_INCREMENT=1032768 DEFAULT CHARSET=utf8

поиск осуществляется с помощью вспомогательных таблиц с кейвордами

CREATE TABLE `keywords_ПРЕФИКС` (

  `keywordId` int(7) NOT NULL AUTO_INCREMENT,

  `word` varchar(30) DEFAULT NULL,

  `article` varchar(20) DEFAULT NULL,

  `merchantId` smallint(5) DEFAULT NULL,

  `type` enum('NAME','DESCR') DEFAULT NULL,

  PRIMARY KEY (`keywordId`),

  UNIQUE KEY `artMerch` (`article`,`merchantId`,`word`),

  KEY `word` (`word`)

) ENGINE=InnoDB AUTO_INCREMENT=32768 DEFAULT CHARSET=utf8

таблицы с кейвордами имеют вид keywords_ПРЕФИКС где префикс это первые две буквы кейворда, таим образом я получаю более 1000 таблиц с кейвордами... все таблицы INNODB

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

но все равно недостаточно быстро идет поиск.. у меня получилась самая большая таблица кейвордов в 3 500 000 записей. При поиске по 2м ключевым словам по этой таблице и по таблице в 500 000 строк, запрос выполняется 30 сек..

SELECT     

    t1.word,

    t2.word,

    t3.*

FROM 

    keywords_de t1 LEFT JOIN keywords_al t2 ON (t1.merchantId=t2.merchantId AND t1.article = t2.article)

LEFT JOIN product t3 ON (t1.merchantId=t3.merchantId AND t1.article=t3.article)

WHERE 

    t1.word='deutsche' AND t2.word='alpenvorland'

ORDER BY t3.cost

товарищи знающие люди, может есть совет по оптимизации? может как то индексы нужно расставить, или может стоить разбивать кейворды на таблицы по первым 3м буквам, но в таком случае количество таблиц вырастет до неимоверных высот (около 30 000)? о кешировании пока не думал..

paulus · 11.03.2011 13:48:52

А зачем Вы пишете LEFT JOIN там, где ожидаете INNER JOIN? И почему
Вы объединяете по VARCHAR, а не по INT?

И EXPLAIN показывайте всё-таки

SaSa · 16.03.2011 10:29:18

извиняюсь..
я поторопился.. еще немного переделал структуру..

вот основная табличка.. она в общем то почти не изменилась, добавилось поле parse

CREATE TABLE `product` (

  `productId` int(11) NOT NULL AUTO_INCREMENT,

  `merchantId` int(11) NOT NULL,

  `categoryId` varchar(255) NOT NULL DEFAULT '0',

  `article` varchar(255) NOT NULL DEFAULT '',

  `name` varchar(300) NOT NULL,

  `cost` float NOT NULL,

  `url` varchar(255) NOT NULL,

  `imageUrl` varchar(255) DEFAULT NULL,

  `description` varchar(500) DEFAULT NULL,

  `deliveryCost` float DEFAULT NULL,

  `deliveryTime` varchar(100) DEFAULT NULL,

  `ean` varchar(255) DEFAULT NULL,

  `tms` datetime DEFAULT NULL,

  `parse` binary(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`productId`),

  UNIQUE KEY `uniqueMerchantProduct` (`merchantId`,`article`),

  KEY `cost` (`cost`),

  KEY `categoryId` (`categoryId`),

  KEY `ean` (`ean`),

  KEY `parse` (`parse`)

) ENGINE=InnoDB AUTO_INCREMENT=131102 DEFAULT CHARSET=utf8

изменил табличку с кейвордами

CREATE TABLE `keywords_zw` (

  `word` varchar(45) DEFAULT NULL,

  `productId` longtext,

  `type` enum('NAME','DESCR') DEFAULT NULL,

  UNIQUE KEY `word_type` (`word`,`type`),

  KEY `word` (`word`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

вместо множества записей по каждому кейворду-продукту, я сделал одну запись по кейворду со списком продуктов разделёнными по типам (в имени или в описании), так мне существенно удалось уменьшить объем данных в таблицах с кейвордами и уменьшить количество строк в каждой из таблиц.. также поиск и выборка продуктов стали очень быстро работать.. однако опять же проблема возникла с ORDER BY, а также есть проблема с очень длинными запросами

сначала я делаю вот такой запрос

SELECT productId 

FROM keywords_de 

WHERE word='der' AND TYPE='DESCR'

если ищутся 2 и более ключевых слова то используем UNION, также регулировать выборку по типу тоже легко.. после получения результата я уже на стороне апача исключаю все дублирующие ключи во такой функцией array_unique(), и делаю следующий запрос

SELECT     

    *    

FROM 

    product

WHERE 

    productId = 234

    OR productId = 3456

    OR productId = 765

    .........                            /*таких  вставок столько сколько найденных продуктов*/

    OR productId = 34535

ORDER BY cost 

LIMIT 750

вот explain

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE product index PRIMARY cost 4 4914 Using where

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

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

Отредактированно SaSa (16.03.2011 10:32:31)

paulus · 17.03.2011 01:23:49

А почему не написать обычный JOIN? Кажется, Вы гоняете данные лишний раз
туда-сюда. Ну и есть ощущение, что EXPLAIN не от этого запроса (нет using filesort).
От filesort избавиться нельзя, но влияние его можно уменьшить, если выбирать
id продуктов в нужном порядке, а потом уже все нужные поля. * нельзя выбирать
никогда.

Форум пользователей MySQL

#1 16.02.2011 09:48:54

Структура Базы Данных с более чем 20млн записей, и поиск по ней, оптимизация

#2 16.02.2011 11:06:44

Re: Структура Базы Данных с более чем 20млн записей, и поиск по ней, оптимизация

#3 21.02.2011 12:04:55

Re: Структура Базы Данных с более чем 20млн записей, и поиск по ней, оптимизация

#4 21.02.2011 14:02:45

Re: Структура Базы Данных с более чем 20млн записей, и поиск по ней, оптимизация

#5 21.02.2011 17:31:39

Re: Структура Базы Данных с более чем 20млн записей, и поиск по ней, оптимизация

#6 10.03.2011 09:45:48

Re: Структура Базы Данных с более чем 20млн записей, и поиск по ней, оптимизация

#7 11.03.2011 13:48:52

Re: Структура Базы Данных с более чем 20млн записей, и поиск по ней, оптимизация

#8 16.03.2011 10:29:18

Re: Структура Базы Данных с более чем 20млн записей, и поиск по ней, оптимизация

#9 17.03.2011 01:23:49

Re: Структура Базы Данных с более чем 20млн записей, и поиск по ней, оптимизация

Board footer