SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 16.02.2011 09:48:54

SaSa
Участник
Зарегистрирован: 16.02.2011
Сообщений: 5

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

Добрый день!

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


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?
а может я вообще не так все делаю?
заранее благодарю)

Неактивен

 

#2 16.02.2011 11:06:44

SaSa
Участник
Зарегистрирован: 16.02.2011
Сообщений: 5

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

Появилась еще одна проблема, 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)

Неактивен

 

#3 21.02.2011 12:04:55

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

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

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

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

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

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

Неактивен

 

#4 21.02.2011 14:02:45

SaSa
Участник
Зарегистрирован: 16.02.2011
Сообщений: 5

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

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

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


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 тоже будет лучше?

Неактивен

 

#5 21.02.2011 17:31:39

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

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

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

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

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

Неактивен

 

#6 10.03.2011 09:45:48

SaSa
Участник
Зарегистрирован: 16.02.2011
Сообщений: 5

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

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


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)? о кешировании пока не думал..

Неактивен

 

#7 11.03.2011 13:48:52

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

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

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

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

Неактивен

 

#8 16.03.2011 10:29:18

SaSa
Участник
Зарегистрирован: 16.02.2011
Сообщений: 5

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

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

вот основная табличка..  она в общем то почти не изменилась, добавилось поле 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)

Неактивен

 

#9 17.03.2011 01:23:49

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson