Документация по запросам и структуре Текдок - версия 2

Материал из MstarProject Manual
Перейти к: навигация, поиск 
   Инструкция по установке и работе с базой TecDoc в MySQL
   =======================================================

Дополнительные страницы:


ВНИМАНИЕ: Перед загрузкой в MySQL базы данных такого большого размера обязательно оптимизируйте настройки 

         MySQL - в некоторых случаях это позволяет уменьшить время загрузки с нескольких суток до
         нескольких часов! См. ниже раздел "Оптимизация MySQL".

   Структура папок и файлов
   ------------------------
   base.full/ - SQL-дамп полной базы (для всех стран и на всех языках)
   base.noctm/ - SQL-дамп полной базы (для всех стран и на всех языках)
                 без колонки поставляемости запчастей в разные страны - по объёму после загрузки
                 в MySQL в 4 раза меньше базы base.full!
   base.europe/ - SQL-дамп базы, отфильтрованной по группе стран "Европа" (код 248) на всех языках -
                  по объёму после загрузки в MySQL в 4 раза меньше базы base.full!
   base.russia/ - SQL-дамп базы, отфильтрованной по группе стран "Страны бывшего СССР" (код 204) и
                  русскому языку (код 16) - по объёму после загрузки в MySQL в 5 раз меньше базы base.full!
   distributive/ - дистрибутив каталога TecDoc (в виде образов DVD-дисков), из которого выгружались данные,
                   таблетка к нему нормально работает под Windows XP/Vista/7/2003/2008/2008 R2.
   queries/ - примеры SQL-запросов к базе MySQL
   queries/Filter_Country_Language/ - Скрипт для уменьшения размера базы данных: фильтрует данные по стране и/или языку.
                                      Позволяет получить из базы base.full свой аналог базы base.russia по любой
                                      комбинации страны/языка.
   addons/count_rows - количество строк в каждой таблице всех версий базы данных (можно использовать для проверки: всё ли залилось?)
   addons/models.php - PHP-пример вывода всех моделей автомобилей марки ALFA ROMEO (на основе примера SQL-запроса MODELS.sql)
   addons/SEARCH_TREE4.xlsx - таблица с деревом категорий всех запчастей
   addons/tecdoc_schema.png - графическая схема связей между основными таблицами Текдока
   tecdoc_tables.txt - описание основных таблиц TecDoc'а
   useful_tables.txt - список таблиц, использованных в моих примерах SQL-запросах - позволит вам не загружать в базу данных ненужные таблицы.

   Порядок действий для загрузки базы в MySQL
   ------------------------------------------

1. Скачиваете нужные файлы 2. Проверяете целостность архивов по контрольным суммам MD5 3. Настраиваете и перезапускаете MySQL 4. Создаёте пустую базу данных 5. Создаёте пустые таблицы SQL-скриптом создания таблиц 6. Распаковываете и загружаете данные таблиц из SQL-дампа 

   Загрузка SQL-дампов в MySQL
   ---------------------------
   Структура таблиц и сами данные находятся в разных файлах. Данные тоже разделены на несколько

файлов - в соответствии с таблицами, в которые они загружаются: 

   tecdoc.Версия.ТипБазы.structure.mysql.ТипХранилища.sql - SQL-скрипты создания структуры всех таблиц

со всеми индексами, необходимыми для быстрого выполнения примеров SQL-запросов. 

   tecdoc.Версия.ТипБазы.structure.mysql.ТипХранилища.nokeys.sql - SQL-скрипты создания структуры

всех таблиц с минимальным набором индексов. 

   data/tecdoc.Версия.ТипБазы.data.ИмяТаблицы.7z - Архивы, содержащие данные таблиц.
   Здесь ТипХранилища - это InnoDB или MyISAM (подробнее они описаны ниже).

   Не пытайтесь загружать SQL-дампы такого размера через phpmyadmin - бесполезное занятие.

Используйте родную консольную утилиту mysql (или mysql.exe под Windows) - идёт в комплекте с СУБД MySQL. Под Windows работает в командной строке, под UNIX - в SSH-консоли. Если у вас нет SSH-доступа к хостингу, попробуйте PHP-скрипт bigdump: http://www.ozerov.de/bigdump/ (хотя через консольный mysql-клиент всё равно гораздо быстрее) 

   Чтобы создать пустые таблицы, выполните в папке с SQL-скриптом команду:

mysql -uЛогин -pПароль ИмяБазы <tecdoc.Версия.ТипБазы.structure.mysql.ТипХранилища.sql 

   Чтобы загрузить все .sql-файлы с дампом из текущей папки, выполните в этой папке команду:

Под UNIX: cat *.sql | mysql -uЛогин -pПароль ИмяБазы Под Windows: type *.sql | mysql -uЛогин -pПароль ИмяБазы 

   Если хотите загрузить в MySQL не все, а только некоторые таблицы, можете не скачивать

все архивы с данными, а выбрать только нужные вам. В SQL-скрипте создания таблиц также уберите ненужные таблицы, либо выполните его целиком и затем удалите ненужные пустые таблицы из MySQL. 

   Вам точно не понадобятся ВСЕ таблицы Текдока. Так для выполнения всех моих примеров

SQL-запросов достаточно 30 таблиц, перечисленных в файле useful_tables.txt, а всего в Текдоке их 77! Кроме того, если будете использовать не все примеры SQL-запросов, можете даже из этих 30 таблиц выбрать ещё меньший набор, глянув их краткие описания всё в том же файле useful_tables.txt. 

   Если вам нужны из Текдока только кроссы (поиск аналогов запчастей), то вам будет достаточно таблиц:

ART_LOOKUP ARTICLES BRANDS DES_TEXTS DESIGNATIONS SUPPLIERS 

   А из примеров SQL-запросов будет достаточно этих:

ART_LOOKUP.sql ART_LOOKUP2.sql 

   Распаковка архивов
   ------------------
   База упакована архиватором 7-Zip. Скачать его можно тут:

Под Windows: http://7-zip.org/download.html (с графическим интерфейсом + консольный вариант) Под Linux/UNIX (p7zip): http://sourceforge.net/projects/p7zip/files/ (только консольный вариант) 

   p7zip есть и в скомпилированном варианте (под Linux x86: p7zip_*_x86_linux_bin.tar.bz2),

и в виде исходников (p7zip_*_src_all.tar.bz2), которые можно скомпилировать под любую другую платформу. В стандартных пакетах большинства дистрибутивов операционных систем есть уже скомпилированный p7zip под конкретно эту ОС. Пакет обычно называется p7zip или p7zip-full (для распаковки архивов .7z достаточно любого). 

   В некоторых Linux-системах вместо имени 7z нужно использовать 7za - учтите это во всех примерах,

приведённых ниже. 

   Если будете распаковывать p7zip'ом не только SQL-дамп базы, но и картинки, советую использовать только

версии 4.58-4.65, т.к. они распаковывают большое количество мелких файлов значительно быстрее всех остальных версий (включая последние бета-версии 9.xx) - разница по скорости до 5 раз! В 7-Zip под Windows разные версии по скорости распаковки не отличаются. 

   Распаковывать из командной строки так:

7z x ИмяАрхива (или имя первой части архива - с расширением .001) 

   Загружать SQL-дампы в MySQL можно с распаковкой из 7-Zip "на лету" - советую делать именно так, ибо

это быстрее и требует меньше свободного места на диске. Для загрузки данных в MySQL сразу из всех скачанных архивов в текущей папке используйте команду: 7z x -so "*.7z" | mysql -uЛогин -pПароль ИмяБазы 

   Под Windows во всех командах нужно указать полные пути до файлов 7z.exe и mysql.exe, заключая их

в "двойные кавычки", если пути содержат пробелы. 

   Таблицы загружаются в MySQL в кодировке UTF-8.
   Загрузка всех таблиц занимает 3-12 часов с минимальным набором индексов и 6-24 часов с полным

набором индексов. Если выберете минимальный вариант, то все дополнительные индексы, необходимые для быстрого выполнения примеров SQL-запросов, можете потом добавить из файла queries/KEYS.sql, хотя в этом случае создание дополнительных индексов займёт те же 5 часов, что вы сэкономите изначально на загрузке таблиц. 

   Проверка целостности архивов (MD5)
   ----------------------------------
   Поскольку архивы крупные и распаковываться будут достаточно долго (особенно если при распаковке сразу

заливать данные в базу MySQL), чтобы не наткнуться на ошибку где-нибудь посреди этого процесса, советую предварительно проверить целостность скачанных файлов с помощью файла dir.md5. Там контрольные суммы MD5 для всех архивов. 

   Проверить MD5 можно:

1. Встроенными средствами Total Commander'а (под Windows) 2. Утилитой md5sum, работающей из командной строки. 

   md5sum под Windows можно скачать тут: http://etree.org/cgi-bin/counter.cgi/software/md5sum.exe
   md5sum под Linux/UNIX входит в набор GNU Coreutils: http://www.gnu.org/software/coreutils/

который есть изначально во всех дистрибутивах Linux/UNIX. 

   Запускать так:

md5sum.exe -c dir.md5 & pause 

   Если после запуска в конце не выдало строчку WARNING, значит всё OK.

   Оптимизация MySQL
   -----------------
   Рекомендую использовать хранилище InnoDB, т.к. оно позволяет кэшировать в памяти не только индексы,

но и данные таблиц. При желании вы можете загрузить всё и в MyISAM, воспользовавшись соответствующим SQL-скриптом создания структуры таблиц. 

   Все перечисленные ниже параметры указываются в разделе [mysqld] файла:

my.cnf (под Linux/UNIX) my.ini (под Windows) После изменения параметров в этом файле нужно перезапустить MySQL. 

   Оптимизация хранилища InnoDB
   ----------------------------
   Если заливаете в хранилище InnoDB, основная настройка, влияющая на скорость заливки (да и работы

базы потом тоже): innodb_buffer_pool_size = 1024M Выделяет 1024 МБ под общий кэш на данные и индексы в InnoDB - в т.ч. под операции создания индексов. Всего MySQL в этом случае съедает около 1.5 ГБ оперативной памяти. Если памяти мало, выделите под этот параметр хотя бы 512 МБ.

Также очень полезно в случае InnoDB добавить параметр: innodb_file_per_table = 1 

   Он заставляет MySQL хранить каждую таблицу хранилища InnoDB в отдельном файле с названием вида

"ИмяБазы/ИмяТаблицы.ibd". Иначе все таблицы размещаются в общем файле "ibdata1", который только увеличивается и никогда не уменьшается - даже при удалении таблиц и баз данных! 

   MySQL после изменения настроек нужно перезапустить.
   Если до этого у вас в MySQL уже были таблицы формата InnoDB, после изменения параметра

они останутся в файле "ibdata1" - в отдельные файлы попадут только таблицы, созданные позже. Если хотите также переместить старые таблицы InnoDB в отдельные файлы, примените SQL-команду: ALTER TABLE ИмяБазы.ИмяТаблицы ENGINE=InnoDB; для каждой из своих старых таблиц. После этого можно завершить работу MySQL, удалить файл "ibdata1", и при очередном запуске MySQL создаст стандартный пустой файл "ibdata1" размером 10 МБ, а все таблицы окажутся в отдельных файлах. 

   Оптимизация хранилища MyISAM
   ----------------------------
   Если заливаете в хранилище MyISAM, используйте такие настройки:

   Кэш индексов - возможно и не используется при создании индексов, но точно используется при дальнейшей

работе с таблицами: key_buffer_size = 128M 

   Буфер в оперативной памяти под операции создания индексов:

myisam_sort_buffer_size = 512M 

   Максимальный размер временного файла НА ВИНТЕ для создания индекса, лучше ставить довольно большим,

иначе индексы делаются как-то совсем медленно через keycache: myisam_max_sort_file_size = 10G 

   Разрыв SSH-соединения при загрузке данных под Linux/UNIX
   --------------------------------------------------------

Есть 2 способа решения этой проблемы:

1. Можно запускать команды SSH в фоновом режиме, добавив в конце: >/dev/null 2>&1 & Например: 7za x "*.7z" >/dev/null 2>&1 & Затем можно отделить этот фоновый процесс от текущего сеанса работы с SSH командой: disown %1 Подробнее управление фоновыми процессами из консоли Linux описано тут: http://clinuxworld.com/ubuntu/36-administration/278-running-processes-in-the-background-and-foreground

2. Есть в Linux'е такая консольная утилита: screen Она создаёт свой виртуальный сеанс работы с операционной системы отдельно от текущего сеанса SSH-подключения. А также позволяет создать много таких сеансов и переключаться между ними как между окнами. При этом если SSH-сеанс разрывается, все ваши обычные процессы продолжают выполняться под screen'ом. При следующем SSH-подключении вы можете вернуться к любому из открытых ранее сеансов и контролировать ход их работы, прерывать выполнение процессов и т.д. Главное не забывать про свои открытые screen-сеансы и закрывать их, когда они уже не нужны, командой exit. Если screen не установлен в вашей ОС, во всех дистрибутивах ОС его можно установить из стандартного комплекта "пакетов" или "портов". Подробнее эта утилита описана тут: http://help.ubuntu.ru/wiki/screen 

   Выбор хостинга
   --------------
   Рекомендую использовать под базу данных либо выделенный сервер, либо виртуальный выделенный сервер (VPS),

т.к. она сильно нагружает хостинг при заливке и обновлении данных, а также требует дополнительной настройки MySQL. 

   Наиболее критично количество оперативной памяти.

Рекомендую: 2 ГБ памяти, из которых 1.5 ГБ выделить под MySQL. Минимальный вариант: 1 ГБ памяти, из которых 700 МБ под MySQL. Работать будет, но долго заливать/обновлять данные. 

   Либо можете использовать базу данных удалённо с моего сервера MySQL - в этом случае упрощается процесс

первоначальной загрузки, будущих обновлений и уменьшаются требования к вашему хостингу: в большинстве случаев под сайт будет достаточно дешёвого виртуального хостинга на 1-5 ГБ. 


   Примеры SQL-запросов
   --------------------
   Если непонятно, как работают какие-то SQL-запросы из примеров, добавьте слово EXPLAIN перед ним, например:

EXPLAIN SELECT * FROM LANGUAGES; Это часто помогает разобраться в структуре сложного запроса: как таблицы связываются друг с другом и как именно MySQL этот запрос обрабатывает. Из этой же расшифровки запроса обычно видно, какими дополнительными индексами можно ускорить запрос, если он выполняется долго. 

   Примеры 22 популярных запросов к полученной базе MySQL с подробными комментариями можно посмотреть в

папке queries:

ART_LOOKUP.sql - Поиск изделия (от любых производителей) по заданному оригинальному/неоригинальному/торговому номеру 

                Используется для уточнения производителя, чтобы дальше искать аналоги уже по конкретному сочетанию:
                Номер + Производитель

ART_LOOKUP2.sql - Поиск аналогов (оригинальных/неоригинальных/торговых) к любому заданному номеру (оригинальному/неоригинальному/торговому) ARTICLES.sql - Вывод детальной информации по заданному неоригинальному изделию (ART_ID): 

   Общая информация об изделии
   Критерии (характеристики)
   Дополнительная информация об изделии
   Номера аналогов (оригинальных/неоригинальных/торговых)
   Пути к файлам картинок
   Пути к файлам логотипов поставщика изделия
   Пути к PDF-файлам
   Применимость к автомобилям
   Рекомендуемые цены

ARTICLES_SEARCH.sql - Поиск неоригинальных изделий из категории с заданным текстом в названии, подходящих для заданного автомобиля (TYP_ID) COUNTRIES.sql - Вывод списка всех стран и групп стран TecDoc'а MANUFACTURERS.sql - Вывод всех марок автомобилей (брэндов) MODELS.sql - Вывод списка моделей по заданной марке автомобиля (MFA_ID) SEARCH_TREE.sql - Построение дерева категорий изделий для заданного типа автомобиля (TYP_ID) SEARCH_TREE2.sql - Вывод списка неоригинальных изделий для заданного типа автомобиля (TYP_ID) и категории (STR_ID) SEARCH_TREE3.sql - Вывод списка неоригинальных изделий для заданной категории (STR_ID) SEARCH_TREE4.sql - Вывод полного дерева категорий всех запчастей SEARCH_TREE4.xlsx - Таблица с результатом выполнения одноимённого запроса для текущей версии TecDoc SUPPLIER_COUNTRIES.sql - Вывод страны заданного неоригинального производителя (SUP_ID) TYPE_NUMBERS.sql - Вывод списка типов автомобилей по заданному KBA-номеру (используется в Германии) TYPES.sql - Вывод списка типов автомобилей по заданной модели (MOD_ID) 

   В некоторых примерах стоит ограничение LIMIT 100, чтобы показывать только первые 100 найденных

строк. Если хотите увидеть все строки - уберите это ограничение. 

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

шаге, в исходные данные следующего: 

   1. Выбор через дерево автомобилей и категорий запчастей:

MANUFACTURERS.sql -> MODELS.sql -> TYPES.sql -> SEARCH_TREE.sql (несколько раз) -> SEARCH_TREE2.sql -> ARTICLES.sql 

   2. Прямой поиск артикула (в т.ч. по номеру аналога):

ART_LOOKUP.sql -> ART_LOOKUP2.sql -> ARTICLES.sql 

   Дополнения:

FUNCTIONS.sql - Дополнительные MySQL-функции и примеры их использования KEYS.sql - Добавление дополнительных индексов в таблицы для ускорения запросов из примеров. 

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


Источник — «http://manual.mstarproject.com/index.php?title=Документация_по_запросам_и_структуре_Текдок_-_версия_2&oldid=1421»