Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Mysql Json автоупорядочивание по ключу

Добрый день,

подскажите, почему в поле VARS типа JSON после запроса

Код

            UPDATE field 
            SET 
            name = 'Новое поле', 
            vars = '{"id":"33","variants":{"0":"... выберите ...","1495714233146":"Второй вариант","1495714228866":"Первый вариант"},"name":"f123","readonly":"0"}'
            WHERE id = '33'


данные выглядят как

Код

{"id": "33", "name": "f123", "readonly": "0", "variants": {"0": "... выберите ...", "1495714228866": "Первый вариант", "1495714233146": "Второй вариант"}}


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

спасибо

Ответ:
спасибо.

не, тип данных верен, просто думал, уж если не массив - то и не сортируй его ни по ключам ни по чему, не трогай, клади as is. Ан нет... Но не страшно, решение зависимой задачи все равно найдено. 

Спасибо
Вопрос: MySQL не использует ключ при inner join

Выявил ряд запросов в Битриксе, в которых в inner join не используется первичный ключ.
После значительного упрощения получились запросы (указаны ниже). Два из них используют первичный ключ при inner join, а два - нет. Отличаются возвращаемыми таблицами.
Поля "b_iblock_element_prop_s17.IBLOCK_ELEMENT_ID" и "b_iblock_element.ID" - первичные ключи соответствующих таблиц.

/*Ключ не используется 
select_type=SIMPLE; talble=b_iblock_element_prop_s17; type=ALL; possible_keys=RIMARY; key=null; key_len=null; ref=null; rows=3358; Extra=null*/
explain
select b_iblock_element_prop_s17.*
from b_iblock_element
INNER JOIN b_iblock_element_prop_s17 ON b_iblock_element_prop_s17.IBLOCK_ELEMENT_ID = b_iblock_element.ID


/*Ключ не используется
select_type=SIMPLE; talble=b_iblock_element_prop_s17; type=ALL; possible_keys=RIMARY; key=null; key_len=null; ref=null; rows=3358; Extra=null*/
explain
select b_iblock_element_prop_s17.*
from b_iblock_element_prop_s17
INNER JOIN b_iblock_element ON b_iblock_element.ID=b_iblock_element_prop_s17.IBLOCK_ELEMENT_ID 


/*Ключ используется
select_type=SIMPLE; talble=b_iblock_element_prop_s17; type=index; possible_keys=RIMARY; key=PRIMARY; key_len=4; ref=null; rows=51103; Extra=Using index*/
explain
select b_iblock_element.*
from b_iblock_element
INNER JOIN b_iblock_element_prop_s15 ON b_iblock_element_prop_s15.IBLOCK_ELEMENT_ID = b_iblock_element.ID



/*Ключ используется
select_type=SIMPLE; talble=b_iblock_element_prop_s17; type=index; possible_keys=RIMARY; key=PRIMARY; key_len=4; ref=null; rows=51103; Extra=Using index*/
explain
select b_iblock_element.*
from b_iblock_element_prop_s17
INNER JOIN b_iblock_element ON b_iblock_element.ID=b_iblock_element_prop_s17.IBLOCK_ELEMENT_ID 


analyze table на обе таблицы говорит, что все хорошо.

Какие есть идеи почему так может быть? В итоге нужны данные, вытаскиваемые первыми запросами.
Начало тут
Ответ:
netwind
Я вообще считают оптимизацию битриксовских запросов непрактичной.
В целом соглашусь. Но, однако, десяток-полтора индексов я себе уже насоздавал. Попыток было больше, но остальные пользы не принесли.
Вопрос: Выбор БД для оптимального хранения мусорки json-ов

Всем привет.

Есть у меня база отдающая по хешу файла его mediainfo.
99% запросы идут на получения краткой информации вот такого содержания:
Запрос
"array":
 [
 {
 
"size":"557993421",
 
"tth":"A3VSWSWKCVC4N6EP2GX47OEMGT5ZL52BOS2LAHA"
 
}
 ]
Ответ
"array":
 [
 {
 
"info":
 {
 
"count_media":"99",
 
"count_query":"8"
 
},
 
"media":
 {
 
"fly_audio":"1h 55mn AAC, 2.0, 128 Kbps",
 
"fly_audio_br":"128",
 
"fly_video":"AVC, 512 Kbps, 25.000 fps",
 
"fly_xy":"640x360"
 
},
 
"size":"557993421",
 
"tth":"A3VSWSWKCVC4N6EP2GX47OEMGT5ZL52BOS2LAHA"
 
}
 ]
Иногда пользователь может захотеть получить весь набор атрибутов файла
тут данных намного больше:
+
{
 
"media":
 {
 
"fly_audio":"4mn 7s MPEG, 2.0, 128 Kbps",
 
"fly_audio_br":128
 
},
 
"media-ext":
 {
 
"audio":
 {
 
"channel-all":
 {
 
"BitRate":"128000",
 
"BitRate_Mode":"CBR",
 
"Channel(s)":"2",
 
"Codec":"MPA1L3",
 
"Codec_Profile":"Joint stereo",
 
"Compression_Mode":"Lossy",
 
"Count":"222",
 
"Duration":"248398",
 
"Format":"MPEG Audio",
 
"Format_Commercial":"MPEG Audio",
 
"Format_Profile":"Layer 3",
 
"Format_Settings":"Joint stereo / MS Stereo",
 
"Format_Settings_Mode":"Joint stereo",
 
"Format_Settings_ModeExtension":"MS Stereo",
 
"Format_Version":"Version 1",
 
"FrameCount":"9509",
 
"Inform":"Format : MPEG Audio\r\nFormat version : Version 1\r\nFormat profile : Layer 3\r\nMode : Joint stereo\r\nMode extension : MS Stereo\r\nDuration : 4mn 8s\r\nBit rate mode : Constant\r\nBit rate : 128 Kbps\r\nChannel(s) : 2 channels\r\nSampling rate : 44.1 KHz\r\nCompression mode : Lossy\r\nStream size : 3.78 MiB (100%)\r\n",
 
"InternetMediaType":"audio/mpeg",
 
"SamplingCount":"10954368",
 
"SamplingRate":"44100",
 
"StreamCount":"1",
 
"StreamKind":"Audio",
 
"StreamKindID":"0",
 
"StreamSize":"3965178"
 
}
 },
 
"general":
 {
 
"Album":"Movin' Melodies",
 
"AudioCount":"1",
 
"Audio_Codec_List":"MPEG-1 Audio layer 3",
 
"Audio_Format_List":"MPEG Audio",
 
"Audio_Format_WithHint_List":"MPEG Audio",
 
"Codec":"MPEG Audio",
 
"Count":"284",
 
"Duration":"247823",
 
"FileExtension":"mp3",
 
"Format":"MPEG Audio",
 
"Format_Commercial":"MPEG Audio",
 
"Inform":"Format : MPEG Audio\r\nFile size : 3.78 MiB\r\nDuration : 4mn 7s\r\nAlbum : Movin' Melodies\r\nTrack name : Underwater World\r\nPerformer : ATB\r\n",
 
"InternetMediaType":"audio/mpeg",
 
"OverallBitRate":"128000",
 
"OverallBitRate_Mode":"CBR",
 
"Performer":"ATB",
 
"StreamCount":"1",
 
"StreamKind":"General",
 
"StreamKindID":"0",
 
"StreamSize":"128",
 
"Title":"Underwater World",
 
"Track":"Underwater World"
 
}
 },
 
"size":"3965306",
 
"tth":"6ZJCIER2ML6DDD2MKHN6CLBT5FA4DP2RQRUOI4Q"
 
}

Сейчас вся информация нормализованно лежит в бд sqlite в виде EAV модели.
и размер файла достиг 8.8 гиг - это напрягает
число записей
в основной таблице~12 млн.
для которых есть расширенная инфа1.1 млн

Решил провести эксперимент и перетащил редко-используемую вторую часть в LevelDB
в итоге sqlite файл похудел до 1.3 гига

Размеры levelDB с компрессией и без оказались такими
размер
media-db.leveldb4371 M
media-db-compress.leveldb1908 M

Пока в плане произвести такую миграцию...

Может порекомендуйте более другое оптимальное по диску хранилище для решения подобной задачи

Цель
  • быстро возвращать краткую информацию по файлам текущая нагрузка 5-30 запросов в секунду (на вход массив ключей)
  • Увеличивать счетчики и менять временную метку для файлов из пункта 1
    в sql я делаю так update fly_file set count_query=count_query+1, last_date=strftime('%s','now','localtime'where id in(?,?,...)
  • компактно хранить json с расширенной информацией mediainfo и отдавать по запросу - можно даже не очень быстро
    (т.к. такие запросы намного реже идут - 100-200 в сутки)
    в идеале может вообще для второй части можно обойтись без БД и скидывать инфу в виде файлов с уникальным именем
    но тут я не тестировал вижу возможные проблемы на лимит по inode и размер каталога.
    зато реплика будет делаться легко...

Заранее спасибо за критику и предложения.

--
~PPA() {} //
Ответ: PPA,

если смотреть на статистику munin, то там первым идет IO read/write.
Там хорошо видно, что в пике общее число запросов к диску доходит до 160.
При этом, если посмотреть на http://habrahabr.ru/post/164325/, то видно, что обычный hdd держит около 150 IOPS.

Т.е. задача - сильно уменьшить число обращений к диску (в первую очередь - операций позиционирования).
Задачу можно решать разными способами:
1) кэшировать все, что можно (явным образом).
2) за один раз считывать больше полезной информации.
Т.е. если запросы пользователей не совсем случайны, то можно попробовать организовать данные на диске так, что бы список из 30 хэшей лежал (скорее всего) на одной физической странице, а не на 30 разных.
Организация таблицы по какому-то ключу точно реализована в MySQL, про sqlite не знаю.
3) увеличивать количество данных на странице. Например, упаковывать блобы с json
4) увеличить число жестких дисков. Если диска два или три в raid, то уже будет полегче.
5) для каких-то данных поставить ssd
6) выстроить очередь записей и чтений, так, что бы нагрузка была равномерной )
При пиках на чтения запись можно и отложить )
И т.п.
Вопрос: Импорт БД ФИАС в MySQL. Практический опыт.

Ну вот потребовалось. Соответственно задача решена, поделюсь решением.

Полная БД выгружается налоговой в двух форматах - DBF и XML. Но с DBF работать неудобно, да и таблицы там нарублены на кучу частей. В общем, сразу решено было использовать исходные данные в XML, тем более что MySQL имеет средства загрузки из них напрямую.

Первым делом на основе XSD-схем данных были построены скрипты создания таблиц. Заодно выяснилось, что схемы даны не все, а их содержимое не всегда соответствует файлу с описанием формата выгрузки, размещённому на сайте ФИАС. Но задача несложная, работаем по факту.

+ скрипты создания таблиц
CREATE TABLE IF NOT EXISTS `ActualStatus` (
`ACTSTATID` INTEGER /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Идентификатор статуса (ключ)',
`NAME` VARCHAR(100) /* NOT NULL */ COMMENT 'Наименование'
) ENGINE=MyISAM COMMENT 'Статус актуальности ФИАС';

CREATE TABLE IF NOT EXISTS `AddressObject` (
`AOGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Глобальный уникальный идентификатор адресного объекта ',
`FORMALNAME` VARCHAR(120) /* NOT NULL */ COMMENT 'Формализованное наименование',
`REGIONCODE` VARCHAR(2) /* NOT NULL */ COMMENT 'Код региона',
`AUTOCODE` VARCHAR(1) /* NOT NULL */ COMMENT 'Код автономии',
`AREACODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код района',
`CITYCODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код города',
`CTARCODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код внутригородского района',
`PLACECODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код населенного пункта',
`STREETCODE` VARCHAR(4) COMMENT 'Код улицы',
`EXTRCODE` VARCHAR(4) /* NOT NULL */ COMMENT 'Код дополнительного адресообразующего элемента',
`SEXTCODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код подчиненного дополнительного адресообразующего элемента',
`OFFNAME` VARCHAR(120) COMMENT 'Официальное наименование',
`POSTALCODE` VARCHAR(6) COMMENT 'Почтовый индекс',
`IFNSFL` VARCHAR(4) COMMENT 'Код ИФНС ФЛ',
`TERRIFNSFL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ФЛ',
`IFNSUL` VARCHAR(4) COMMENT 'Код ИФНС ЮЛ',
`TERRIFNSUL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ЮЛ',
`OKATO` VARCHAR(11) COMMENT 'ОКАТО',
`OKTMO` VARCHAR(11) COMMENT 'ОКТМО',
`UPDATEDATE` DATE /* NOT NULL */ COMMENT 'Дата  внесения (обновления) записи',
`SHORTNAME` VARCHAR(10) /* NOT NULL */ COMMENT 'Краткое наименование типа объекта',
`AOLEVEL` INTEGER /* NOT NULL */ COMMENT 'Уровень адресного объекта ',
`PARENTGUID` VARCHAR(36) COMMENT 'Идентификатор объекта родительского объекта',
`AOID` VARCHAR(36) /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Уникальный идентификатор записи. Ключевое поле',
`PREVID` VARCHAR(36) COMMENT 'Идентификатор записи связывания с предыдушей исторической записью',
`NEXTID` VARCHAR(36) COMMENT 'Идентификатор записи  связывания с последующей исторической записью',
`CODE` VARCHAR(17) COMMENT 'Код адресного объекта одной строкой с признаком актуальности из КЛАДР 4.0. ',
`PLAINCODE` VARCHAR(15) COMMENT 'Код адресного объекта из КЛАДР 4.0 одной строкой без признака актуальности (последних двух цифр)',
`ACTSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус актуальности адресного объекта ФИАС. Актуальный адрес на текущую дату. Обычно последняя запись об адресном объекте.',
`CENTSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус центра',
`OPERSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус действия над записью – причина появления записи (см. описание таблицы OperationStatus)',
`CURRSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус актуальности КЛАДР 4 (последние две цифры в коде)',
`STARTDATE` DATE /* NOT NULL */ COMMENT 'Начало действия записи',
`ENDDATE` DATE /* NOT NULL */ COMMENT 'Окончание действия записи',
`NORMDOC` VARCHAR(36) COMMENT 'Внешний ключ на нормативный документ',
`LIVESTATUS` ENUM('0','1') /* NOT NULL */ COMMENT 'Признак действующего адресного объекта'
) ENGINE=MyISAM COMMENT 'Классификатор адресообразующих элементов';

CREATE TABLE IF NOT EXISTS `CenterStatus` (
`CENTERSTID` INTEGER /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Идентификатор статуса (ключ)',
`NAME` VARCHAR(100) /* NOT NULL */ COMMENT 'Наименование'
) ENGINE=MyISAM COMMENT 'Статус центра';

CREATE TABLE IF NOT EXISTS `CurrentStatus` (
`CURENTSTID` INTEGER /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Идентификатор статуса (ключ)',
`NAME` VARCHAR(100) /* NOT NULL */ COMMENT 'Наименование'
) ENGINE=MyISAM COMMENT 'Статус актуальности КЛАДР 4.0';

CREATE TABLE IF NOT EXISTS `EstateStatus` (
`ESTSTATID` INTEGER /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Признак владения (ключ)',
`NAME` VARCHAR(20) /* NOT NULL */ COMMENT 'Наименование',
`SHORTNAME` VARCHAR(20) COMMENT 'Краткое наименование'
) ENGINE=MyISAM COMMENT 'Признак владения';

CREATE TABLE IF NOT EXISTS `House` (
`POSTALCODE` VARCHAR(6) COMMENT 'Почтовый индекс',
`IFNSFL` VARCHAR(4) COMMENT 'Код ИФНС ФЛ',
`TERRIFNSFL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ФЛ',
`IFNSUL` VARCHAR(4) COMMENT 'Код ИФНС ЮЛ',
`TERRIFNSUL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ЮЛ',
`OKATO` VARCHAR(11) COMMENT 'ОКАТО',
`OKTMO` VARCHAR(11) COMMENT 'ОКTMO',
`UPDATEDATE` DATE /* NOT NULL */ COMMENT 'Дата время внесения (обновления) записи',
`HOUSENUM` VARCHAR(20) COMMENT 'Номер дома',
`ESTSTATUS` INTEGER /* NOT NULL */ COMMENT 'Признак владения',
`BUILDNUM` VARCHAR(10) COMMENT 'Номер корпуса',
`STRUCNUM` VARCHAR(10) COMMENT 'Номер строения',
`STRSTATUS` INTEGER COMMENT 'Признак строения',
`HOUSEID` VARCHAR(36) /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Уникальный идентификатор записи дома',
`HOUSEGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Глобальный уникальный идентификатор дома',
`AOGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Guid записи родительского объекта (улицы, города, населенного пункта и т.п.)',
`STARTDATE` DATE /* NOT NULL */ COMMENT 'Начало действия записи',
`ENDDATE` DATE /* NOT NULL */ COMMENT 'Окончание действия записи',
`STATSTATUS` INTEGER /* NOT NULL */ COMMENT 'Состояние дома',
`NORMDOC` VARCHAR(36) COMMENT 'Внешний ключ на нормативный документ',
`COUNTER` INTEGER /* NOT NULL */ COMMENT 'Счетчик записей домов для КЛАДР 4'
) ENGINE=MyISAM COMMENT 'Сведения по номерам домов улиц городов и населенных пунктов, номера земельных участков и т.п.';

CREATE TABLE IF NOT EXISTS `HouseInterval` (
`POSTALCODE` VARCHAR(6) COMMENT 'Почтовый индекс',
`IFNSFL` VARCHAR(4) COMMENT 'Код ИФНС ФЛ',
`TERRIFNSFL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ФЛ',
`IFNSUL` VARCHAR(4) COMMENT 'Код ИФНС ЮЛ',
`TERRIFNSUL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ЮЛ',
`OKATO` VARCHAR(11) COMMENT 'ОКАТО',
`OKTMO` VARCHAR(11) COMMENT 'ОКTMO',
`UPDATEDATE` DATE /* NOT NULL */ COMMENT 'Дата время внесения (обновления) записи',
`INTSTART` INTEGER /* NOT NULL */ COMMENT 'Значение начала интервала',
`INTEND` INTEGER /* NOT NULL */ COMMENT 'Значение окончания интервала',
`HOUSEINTID` VARCHAR(36) /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Идентификатор записи интервала домов',
`INTGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Глобальный уникальный идентификатор интервала домов',
`AOGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Идентификатор объекта родительского объекта (улицы, города, населенного пункта и т.п.)',
`STARTDATE` DATE /* NOT NULL */ COMMENT 'Начало действия записи',
`ENDDATE` DATE /* NOT NULL */ COMMENT 'Окончание действия записи',
`INTSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус интервала (обычный, четный, нечетный)',
`NORMDOC` VARCHAR(36) COMMENT 'Внешний ключ на нормативный документ',
`COUNTER` INTEGER /* NOT NULL */ COMMENT 'Счетчик записей домов для КЛАДР 4'
) ENGINE=MyISAM COMMENT 'Интервалы домов';

CREATE TABLE IF NOT EXISTS `HouseStateStatus` (
`HOUSESTID` INTEGER /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Идентификатор статуса',
`NAME` VARCHAR(60) /* NOT NULL */ COMMENT 'Наименование'
) ENGINE=MyISAM COMMENT 'Статус состояния домов';

CREATE TABLE IF NOT EXISTS `IntervalStatus` (
`INTVSTATID` INTEGER /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Идентификатор статуса (обычный, четный, нечетный)',
`NAME` VARCHAR(60) /* NOT NULL */ COMMENT 'Наименование'
) ENGINE=MyISAM COMMENT 'Статус интервала домов';

CREATE TABLE IF NOT EXISTS `Landmark` (
`LOCATION` VARCHAR(500) /* NOT NULL */ COMMENT 'Месторасположение ориентира',
`POSTALCODE` VARCHAR(6) COMMENT 'Почтовый индекс',
`IFNSFL` VARCHAR(4) COMMENT 'Код ИФНС ФЛ',
`TERRIFNSFL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ФЛ',
`INFSUL` VARCHAR(4) COMMENT 'Код ИФНС ЮЛ',
`TERRIFNSUL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ЮЛ',
`OKATO` VARCHAR(11) COMMENT 'ОКАТО',
`OKTMO` VARCHAR(11) COMMENT 'ОКТМО',
`UPDATEDATE` DATE /* NOT NULL */ COMMENT 'Дата внесения (обновления) записи',
`LANDID` VARCHAR(36) /* NOT NULL */ COMMENT 'Уникальный идентификатор записи ориентира',
`LANDGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Глобальный уникальный идентификатор ориентира',
`AOGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Уникальный идентификатор родительского объекта (улицы, города, населенного пункта и т.п.)',
`STARTDATE` DATE /* NOT NULL */ COMMENT 'Начало действия записи',
`ENDDATE` DATE /* NOT NULL */ COMMENT 'Окончание действия записи',
`NORMDOC` VARCHAR(36) COMMENT 'Внешний ключ на нормативный документ'
) ENGINE=MyISAM COMMENT 'Описание мест расположения  имущественных объектов';

CREATE TABLE IF NOT EXISTS `NormativeDocumentType` (
`NDTYPEID` INTEGER /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Идентификатор записи (ключ)',
`NAME` VARCHAR(250) /* NOT NULL */ COMMENT 'Наименование типа нормативного документа'
) ENGINE=MyISAM COMMENT 'Тип нормативного документа';

CREATE TABLE IF NOT EXISTS `NormativeDocument` (
`NORMDOCID` VARCHAR(36) /* NOT NULL */ COMMENT 'Идентификатор нормативного документа',
`DOCNAME` VARCHAR(21783) COMMENT 'Наименование документа',
`DOCDATE` DATE COMMENT 'Дата документа',
`DOCNUM` VARCHAR(20) COMMENT 'Номер документа',
`DOCTYPE` INTEGER /* NOT NULL */ COMMENT 'Тип документа',
`DOCIMGID` INTEGER COMMENT 'Идентификатор образа (внешний ключ)'
) ENGINE=MyISAM COMMENT 'Сведения по нормативному документу, являющемуся основанием присвоения адресному элементу наименования';

CREATE TABLE IF NOT EXISTS `OperationStatus` (
`OPERSTATID` INTEGER /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Идентификатор статуса (ключ)',
`NAME` VARCHAR(100) /* NOT NULL */ COMMENT 'Наименование'
) ENGINE=MyISAM COMMENT 'Статус действия';

CREATE TABLE IF NOT EXISTS `AddressObjectType` (
`LEVEL` INTEGER /* NOT NULL */ COMMENT 'Тип адресного объекта',
`SCNAME` VARCHAR(10) COMMENT 'Краткое наименование типа объекта',
`SOCRNAME` VARCHAR(50) /* NOT NULL */ COMMENT 'Полное наименование типа объекта',
`KOD_T_ST` VARCHAR(4) /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Ключевое поле'
) ENGINE=MyISAM COMMENT 'Тип адресного объекта';

CREATE TABLE IF NOT EXISTS `StructureStatus` (
`STRSTATID` INTEGER /* NOT NULL */ COMMENT 'Признак строения',
`NAME` VARCHAR(20) /* NOT NULL */ COMMENT 'Наименование',
`SHORTNAME` VARCHAR(20) COMMENT 'Краткое наименование'
) ENGINE=MyISAM COMMENT 'Признак строения';

CREATE TABLE IF NOT EXISTS `DeletedAddressObject` (
`AOGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Глобальный уникальный идентификатор адресного объекта ',
`FORMALNAME` VARCHAR(120) /* NOT NULL */ COMMENT 'Формализованное наименование',
`REGIONCODE` VARCHAR(2) /* NOT NULL */ COMMENT 'Код региона',
`AUTOCODE` VARCHAR(1) /* NOT NULL */ COMMENT 'Код автономии',
`AREACODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код района',
`CITYCODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код города',
`CTARCODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код внутригородского района',
`PLACECODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код населенного пункта',
`STREETCODE` VARCHAR(4) COMMENT 'Код улицы',
`EXTRCODE` VARCHAR(4) /* NOT NULL */ COMMENT 'Код дополнительного адресообразующего элемента',
`SEXTCODE` VARCHAR(3) /* NOT NULL */ COMMENT 'Код подчиненного дополнительного адресообразующего элемента',
`OFFNAME` VARCHAR(120) COMMENT 'Официальное наименование',
`POSTALCODE` VARCHAR(6) COMMENT 'Почтовый индекс',
`IFNSFL` VARCHAR(4) COMMENT 'Код ИФНС ФЛ',
`TERRIFNSFL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ФЛ',
`IFNSUL` VARCHAR(4) COMMENT 'Код ИФНС ЮЛ',
`TERRIFNSUL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ЮЛ',
`OKATO` VARCHAR(11) COMMENT 'ОКАТО',
`OKTMO` VARCHAR(11) COMMENT 'ОКТМО',
`UPDATEDATE` DATE /* NOT NULL */ COMMENT 'Дата  внесения (обновления) записи',
`SHORTNAME` VARCHAR(10) /* NOT NULL */ COMMENT 'Краткое наименование типа объекта',
`AOLEVEL` INTEGER /* NOT NULL */ COMMENT 'Уровень адресного объекта ',
`PARENTGUID` VARCHAR(36) COMMENT 'Идентификатор объекта родительского объекта',
`AOID` VARCHAR(36) /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Уникальный идентификатор записи. Ключевое поле',
`PREVID` VARCHAR(36) COMMENT 'Идентификатор записи связывания с предыдушей исторической записью',
`NEXTID` VARCHAR(36) COMMENT 'Идентификатор записи  связывания с последующей исторической записью',
`CODE` VARCHAR(17) COMMENT 'Код адресного объекта одной строкой с признаком актуальности из КЛАДР 4.0. ',
`PLAINCODE` VARCHAR(15) COMMENT 'Код адресного объекта из КЛАДР 4.0 одной строкой без признака актуальности (последних двух цифр)',
`ACTSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус актуальности адресного объекта ФИАС. Актуальный адрес на текущую дату. Обычно последняя запись об адресном объекте.',
`CENTSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус центра',
`OPERSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус действия над записью – причина появления записи (см. описание таблицы OperationStatus)',
`CURRSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус актуальности КЛАДР 4 (последние две цифры в коде)',
`STARTDATE` DATE /* NOT NULL */ COMMENT 'Начало действия записи',
`ENDDATE` DATE /* NOT NULL */ COMMENT 'Окончание действия записи',
`NORMDOC` VARCHAR(36) COMMENT 'Внешний ключ на нормативный документ',
`LIVESTATUS` ENUM('0','1') /* NOT NULL */ COMMENT 'Признак действующего адресного объекта'
) ENGINE=MyISAM COMMENT 'Классификатор адресообразующих элементов';

CREATE TABLE IF NOT EXISTS `DeletedHouse` (
`POSTALCODE` VARCHAR(6) COMMENT 'Почтовый индекс',
`IFNSFL` VARCHAR(4) COMMENT 'Код ИФНС ФЛ',
`TERRIFNSFL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ФЛ',
`IFNSUL` VARCHAR(4) COMMENT 'Код ИФНС ЮЛ',
`TERRIFNSUL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ЮЛ',
`OKATO` VARCHAR(11) COMMENT 'ОКАТО',
`OKTMO` VARCHAR(11) COMMENT 'ОКTMO',
`UPDATEDATE` DATE /* NOT NULL */ COMMENT 'Дата время внесения (обновления) записи',
`HOUSENUM` VARCHAR(20) COMMENT 'Номер дома',
`ESTSTATUS` INTEGER /* NOT NULL */ COMMENT 'Признак владения',
`BUILDNUM` VARCHAR(10) COMMENT 'Номер корпуса',
`STRUCNUM` VARCHAR(10) COMMENT 'Номер строения',
`STRSTATUS` INTEGER COMMENT 'Признак строения',
`HOUSEID` VARCHAR(36) /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Уникальный идентификатор записи дома',
`HOUSEGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Глобальный уникальный идентификатор дома',
`AOGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Guid записи родительского объекта (улицы, города, населенного пункта и т.п.)',
`STARTDATE` DATE /* NOT NULL */ COMMENT 'Начало действия записи',
`ENDDATE` DATE /* NOT NULL */ COMMENT 'Окончание действия записи',
`STATSTATUS` INTEGER /* NOT NULL */ COMMENT 'Состояние дома',
`NORMDOC` VARCHAR(36) COMMENT 'Внешний ключ на нормативный документ',
`COUNTER` INTEGER /* NOT NULL */ COMMENT 'Счетчик записей домов для КЛАДР 4'
) ENGINE=MyISAM COMMENT 'Сведения по номерам домов улиц городов и населенных пунктов, номера земельных участков и т.п.';

CREATE TABLE IF NOT EXISTS `DeletedHouseInterval` (
`POSTALCODE` VARCHAR(6) COMMENT 'Почтовый индекс',
`IFNSFL` VARCHAR(4) COMMENT 'Код ИФНС ФЛ',
`TERRIFNSFL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ФЛ',
`IFNSUL` VARCHAR(4) COMMENT 'Код ИФНС ЮЛ',
`TERRIFNSUL` VARCHAR(4) COMMENT 'Код территориального участка ИФНС ЮЛ',
`OKATO` VARCHAR(11) COMMENT 'ОКАТО',
`OKTMO` VARCHAR(11) COMMENT 'ОКTMO',
`UPDATEDATE` DATE /* NOT NULL */ COMMENT 'Дата время внесения (обновления) записи',
`INTSTART` INTEGER /* NOT NULL */ COMMENT 'Значение начала интервала',
`INTEND` INTEGER /* NOT NULL */ COMMENT 'Значение окончания интервала',
`HOUSEINTID` VARCHAR(36) /* NOT NULL */ /* PRIMARY KEY */ COMMENT 'Идентификатор записи интервала домов',
`INTGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Глобальный уникальный идентификатор интервала домов',
`AOGUID` VARCHAR(36) /* NOT NULL */ COMMENT 'Идентификатор объекта родительского объекта (улицы, города, населенного пункта и т.п.)',
`STARTDATE` DATE /* NOT NULL */ COMMENT 'Начало действия записи',
`ENDDATE` DATE /* NOT NULL */ COMMENT 'Окончание действия записи',
`INTSTATUS` INTEGER /* NOT NULL */ COMMENT 'Статус интервала (обычный, четный, нечетный)',
`NORMDOC` VARCHAR(36) COMMENT 'Внешний ключ на нормативный документ',
`COUNTER` INTEGER /* NOT NULL */ COMMENT 'Счетчик записей домов для КЛАДР 4'
) ENGINE=MyISAM COMMENT 'Интервалы домов';

CREATE TABLE IF NOT EXISTS `DeletedNormativeDocument` (
`NORMDOCID` VARCHAR(36) /* NOT NULL */ COMMENT 'Идентификатор нормативного документа',
`DOCNAME` VARCHAR(21783) COMMENT 'Наименование документа',
`DOCDATE` DATE COMMENT 'Дата документа',
`DOCNUM` VARCHAR(20) COMMENT 'Номер документа',
`DOCTYPE` INTEGER /* NOT NULL */ COMMENT 'Тип документа',
`DOCIMGID` INTEGER COMMENT 'Идентификатор образа (внешний ключ)'
) ENGINE=MyISAM COMMENT 'Сведения по нормативному документу, являющемуся основанием присвоения адресному элементу наименования';
+ вспомогательные скрипты
Могут потребоваться при обновлении, в текущей работе не использовались
-- Пересоздание базы данных

DROP DATABASE IF EXISTS fias;
CREATE DATABASE IF NOT EXISTS fias;
USE fias;

-- Скрипты удаления таблиц

DROP TABLE IF EXISTS `ActualStatus`;
DROP TABLE IF EXISTS `AddressObject`;
DROP TABLE IF EXISTS `AddressObjectType`;
DROP TABLE IF EXISTS `CenterStatus`;
DROP TABLE IF EXISTS `CurrentStatus`;
DROP TABLE IF EXISTS `EstateStatus`;
DROP TABLE IF EXISTS `House`;
DROP TABLE IF EXISTS `HouseInterval`;
DROP TABLE IF EXISTS `HouseStateStatus`;
DROP TABLE IF EXISTS `IntervalStatus`;
DROP TABLE IF EXISTS `Landmark`;
DROP TABLE IF EXISTS `NormativeDocument`;
DROP TABLE IF EXISTS `NormativeDocumentType`;
DROP TABLE IF EXISTS `OperationStatus`;
DROP TABLE IF EXISTS `StructureStatus`;
DROP TABLE IF EXISTS `DeletedAddressObject`;
DROP TABLE IF EXISTS `DeletedHouse`;
DROP TABLE IF EXISTS `DeletedHouseInterval`;
DROP TABLE IF EXISTS `DeletedNormativeDocument`;

-- Скрипты очистки таблиц

TRUNCATE TABLE `ActualStatus`;
TRUNCATE TABLE `AddressObject`;
TRUNCATE TABLE `AddressObjectType`;
TRUNCATE TABLE `CenterStatus`;
TRUNCATE TABLE `CurrentStatus`;
TRUNCATE TABLE `EstateStatus`;
TRUNCATE TABLE `House`;
TRUNCATE TABLE `HouseInterval`;
TRUNCATE TABLE `HouseStateStatus`;
TRUNCATE TABLE `IntervalStatus`;
TRUNCATE TABLE `Landmark`;
TRUNCATE TABLE `NormativeDocument`;
TRUNCATE TABLE `NormativeDocumentType`;
TRUNCATE TABLE `OperationStatus`;
TRUNCATE TABLE `StructureStatus`;
TRUNCATE TABLE `DeletedAddressObject`;
TRUNCATE TABLE `DeletedHouse`;
TRUNCATE TABLE `DeletedHouseInterval`;
TRUNCATE TABLE `DeletedNormativeDocument`;


В качестве движка выбран MyISAM - это ускоряет импорт. Также были отключены (в скриптах - закомментированы) ограничения и первичные индексы - с той же целью.

Имея готовые структуры, несложно выполнить импорт данных. Первым делом имена XML-файлов для удобства работы были укорочены. Затем построены запросы импорта данных (исходные файлы были расположены по пути b:\fias).

+ запросы импорта данных
LOAD XML LOCAL INFILE 'b:\\fias\\ACTSTAT.XML' INTO TABLE `ActualStatus` ROWS IDENTIFIED BY '<ActualStatus>';
LOAD XML LOCAL INFILE 'b:\\fias\\ESTSTAT.XML' INTO TABLE `EstateStatus` ROWS IDENTIFIED BY '<EstateStatus>';
LOAD XML LOCAL INFILE 'b:\\fias\\INTVSTAT.XML' INTO TABLE `IntervalStatus` ROWS IDENTIFIED BY '<IntervalStatus>';
LOAD XML LOCAL INFILE 'b:\\fias\\STRSTAT.XML' INTO TABLE `StructureStatus` ROWS IDENTIFIED BY '<StructureStatus>';
LOAD XML LOCAL INFILE 'b:\\fias\\CENTERST.XML' INTO TABLE `CenterStatus` ROWS IDENTIFIED BY '<CenterStatus>';
LOAD XML LOCAL INFILE 'b:\\fias\\DEL_HOUSEINT.XML' INTO TABLE `DeletedHouseInterval` ROWS IDENTIFIED BY '<HouseInterval>';
LOAD XML LOCAL INFILE 'b:\\fias\\OPERSTAT.XML' INTO TABLE `OperationStatus` ROWS IDENTIFIED BY '<OperationStatus>';
LOAD XML LOCAL INFILE 'b:\\fias\\NDOCTYPE.XML' INTO TABLE `NormativeDocumentType` ROWS IDENTIFIED BY '<NormativeDocumentType>';
LOAD XML LOCAL INFILE 'b:\\fias\\HSTSTAT.XML' INTO TABLE `HouseStateStatus` ROWS IDENTIFIED BY '<HouseStateStatus>';
LOAD XML LOCAL INFILE 'b:\\fias\\CURENTST.XML' INTO TABLE `CurrentStatus` ROWS IDENTIFIED BY '<CurrentStatus>';
LOAD XML LOCAL INFILE 'b:\\fias\\DEL_NORMDOC.XML' INTO TABLE `DeletedNormativeDocument` ROWS IDENTIFIED BY '<NormativeDocument>';
LOAD XML LOCAL INFILE 'b:\\fias\\SOCRBASE.XML' INTO TABLE `AddressObjectType` ROWS IDENTIFIED BY '<AddressObjectType>';
LOAD XML LOCAL INFILE 'b:\\fias\\DEL_HOUSE.XML' INTO TABLE `DeletedHouse` ROWS IDENTIFIED BY '<House>';
LOAD XML LOCAL INFILE 'b:\\fias\\DEL_ADDROBJ.XML' INTO TABLE `DeletedAddressObject` ROWS IDENTIFIED BY '<Object>';
-- порядка 10к записей
LOAD XML LOCAL INFILE 'b:\\fias\\LANDMARK.XML' INTO TABLE `Landmark` ROWS IDENTIFIED BY '<Landmark>';
-- порядка 150к записей
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSEINT.XML' INTO TABLE `HouseInterval` ROWS IDENTIFIED BY '<HouseInterval>';
-- порядка 4кк записей
LOAD XML LOCAL INFILE 'b:\\fias\\NORMDOC.XML' INTO TABLE `NormativeDocument` ROWS IDENTIFIED BY '<NormativeDocument>';
LOAD XML LOCAL INFILE 'b:\\fias\\ADDROBJ.XML' INTO TABLE `AddressObject` ROWS IDENTIFIED BY '<Object>';


Импорт всех файлов, кроме самого большого, прошёл быстро и хорошо (большие файлы NORMDOC.XML и ADDROBJ.XML импортировались 2 и 4 минуты соответственно, все остальные практически мгновенно).

А вот с самым большим, 16-гигабайтным HOUSE.XML возникла проблема. Сервер импортировал порядка четверти файла, после чего потребление памяти возрастало с исходных 450 Мбайт до 2 Гбайт и процесс обрывался по ошибке недостатка памяти.

Было принято решение поделить файл на части и выполнить их импорт. Для нарезки было быстро накидано приложение на VB6, файл поделен на 9 частей по 1,8 Гбайт.

+
Программа нарезки XML-файла. На форме размещены 2 кнопки с именами Start и Exit, и надпись с именем Protocol. Код модуля:

Dim flag As Boolean

Private Sub Start_Click()
Dim fso As Scripting.FileSystemObject
Dim src As Scripting.TextStream
Dim dst As Scripting.TextStream
Dim buffer As String
Dim i As Long, j As Long, k As Long
Dim header As String
Const portion As Integer = 16384
Const rec_cnt As Long = 4567890
Const fname As String = "b:\fias\house"
Const fext As String = ".xml"

i = 1
j = 0
flag = False
Set fso = New Scripting.FileSystemObject
Set src = fso.OpenTextFile(fname & fext, ForReading)
Set dst = fso.CreateTextFile(fname & CStr(i) & fext)
Do Until src.AtEndOfStream
    buffer = buffer & src.Read(portion)
    Do Until InStr(buffer, ">") = 0
        If i = 1 And j < 2 Then
            header = header & Left(buffer, InStr(buffer, ">")) & vbNewLine
        End If
        dst.WriteLine Left(buffer, InStr(buffer, ">"))
        buffer = Mid(buffer, 1 + InStr(buffer, ">"))
        j = j + 1
        k = k + 1
    Loop
    Protocol.Caption = "Part: " & CStr(i) & vbNewLine & "Record: " & CStr(j) & vbNewLine & "Total: " & CStr(k)
    DoEvents
    If flag Then Exit Do
    If j > rec_cnt Then
        dst.WriteLine "</Houses>"
        dst.Close
        i = i + 1
        j = 0
        Set dst = fso.CreateTextFile(fname & CStr(i) & fext)
        dst.Write header
    End If
Loop
src.Close
dst.Close
Protocol.Caption = Protocol.Caption & vbNewLine & "Done!"
End Sub

Private Sub Exit_Click()
flag = True
Unload Me
End Sub

Процесс деления на часты выполнялся прямо из среды VB6Nano и продолжался порядка 20 минут.

Затем полученные части были импортированы в базу.
+ Скрипт импорта таблицы по частям
-- порядка 40кк записей
/* Отключено - процесс загрузки завершается ошибкой
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';
*/
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE1.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE2.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE3.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE4.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE5.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE6.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE7.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE8.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';
LOAD XML LOCAL INFILE 'b:\\fias\\HOUSE9.XML' INTO TABLE `House` ROWS IDENTIFIED BY '<House>';

Импорт прошёл без проблем, каждая часть импортировалась чуть больше 3 минут.

Итоговый размер базы данных составил около 9 Гбайт.

PS. При импорте было выявлено, что 4 записи таблицы NORMDOC.XML содержат некорректные относительно схемы данные (размер данных превышает размер поля). Но это мелочи...
Ответ: Дополнительные сведения по среде выполнения.

Весь процесс выполнялся локально на рабочей станции.

Процессор Е2160 1,8 ГГц.
Физической памяти 2 Гбайт.
ОС Windows 7 Max SP1 Rus.
Server version: 5.6.14-log MySQL Community Server (GPL).
Все кодировки (сервера, БД и пр.) - UTF8.
Все операции выполнялись непосредственно из UTF-8 консоли.
Вопрос: работа с json-индексами

Доброго времени суток!

Есть таблица t, в которой много записей, несколько миллионов.
В ней есть поле d типа json (jsonb пока, увы, низя использовать).
В очень малом количестве записей в поле d лежит неNULL'овое значение,
еще в меньшем количестве записей в поле d есть ключ f.

create table if not exists t(id bigserial, d json);
INSERT INTO t(d) SELECT NULL FROM generate_series(1,1000000) i;
update t set d = '{"f":"val"}' WHERE id = 999999;

Собственно, задача быстро найти те записи, в которых есть этот ключ f.

Пробовал созданием индекса
CREATE INDEX json_simple_idx ON t((d->>'f'));

запрос
explain select * from t where (d->>'f') = 'val'
показывает Bitmap Index Scan

но мне куда более подходит запрос
select * from t where (d->>'f') is not null;
а у него seq scan и работает куда как медленнее.

Прошу подсказать - можно ли как-то в рамках моей задачи использовать json-индекс или другим путем идти нужно (если так, то каким?)
Ответ:
deadka
Alexius, честно говоря думал, что редко появляющееся значение - просто идеальный кандидат для того, чтобы в композитное поле его положить. Если нет, то для каких же данных предназначен json/jsonb? Для тех, по которым нет нужды индексы использовать?


наверное для тех, которые нельзя положить в массивы и hstore.
Вопрос: MySQL Workbench 6.3,ошибка 1005

Всем привет,кто подскажет может,что не так с FK здесь??
Код ошибки: Error Code: 1005. Can't create table 'rasp_kkmt.kkmt_auditory' (errno: 150)

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE IF NOT EXISTS `rasp_kkmt`.`kkmt_auditory` (
  `auditory_id` INT NOT NULL AUTO_INCREMENT,
  `Zdanie` VARCHAR(45) NOT NULL,
  `Etazh` VARCHAR(45) NOT NULL,
  `Nomer_aud` VARCHAR(45) CHARACTER SET 'latin1' NOT NULL,
  PRIMARY KEY (`auditory_id`),
  INDEX `fk_nomer_idx` USING BTREE (`Nomer_aud` ASC)  KEY_BLOCK_SIZE=30,
  CONSTRAINT `fk_nomer_aud`
    FOREIGN KEY (`Nomer_aud`)
    REFERENCES `rasp_kkmt`.`kkmt_raspisanie` (`kkmt_auditory_auditory_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
-- MySQL Script generated by MySQL Workbench
-- 10/04/16 21:35:18
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering
 
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
 
-- -----------------------------------------------------
-- Schema rasp_kkmt
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `rasp_kkmt` ;
 
-- -----------------------------------------------------
-- Schema rasp_kkmt
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `rasp_kkmt` DEFAULT CHARACTER SET utf8 ;
USE `rasp_kkmt` ;
 
-- -----------------------------------------------------
-- Table `rasp_kkmt`.`kkmt_raspisanie`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rasp_kkmt`.`kkmt_raspisanie` (
  `rasp_id` INT NOT NULL AUTO_INCREMENT,
  `Data` VARCHAR(45) NOT NULL,
  `kkmt_pair_id_pair` INT NOT NULL,
  `kkmt_auditory_auditory_id` VARCHAR(45) NOT NULL,
  `kkmt_prepod_prepod_id` VARCHAR(45) NOT NULL,
  `kkmt_group_group_id` INT NOT NULL,
  `kkmt_subjects_subject_id` INT NOT NULL,
  PRIMARY KEY (`rasp_id`, `kkmt_pair_id_pair`, `kkmt_auditory_auditory_id`, `kkmt_prepod_prepod_id`, `kkmt_group_group_id`, `kkmt_subjects_subject_id`))
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `rasp_kkmt`.`kkmt_auditory`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rasp_kkmt`.`kkmt_auditory` (
  `auditory_id` INT NOT NULL AUTO_INCREMENT,
  `Zdanie` VARCHAR(45) NOT NULL,
  `Etazh` VARCHAR(45) NOT NULL,
  `Nomer_aud` VARCHAR(45) CHARACTER SET 'latin1' NOT NULL,
  PRIMARY KEY (`auditory_id`),
  INDEX `fk_nomer_idx` USING BTREE (`Nomer_aud` ASC)  KEY_BLOCK_SIZE=30,
  CONSTRAINT `fk_nomer_aud`
    FOREIGN KEY (`Nomer_aud`)
    REFERENCES `rasp_kkmt`.`kkmt_raspisanie` (`kkmt_auditory_auditory_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `rasp_kkmt`.`kkmt_pair`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rasp_kkmt`.`kkmt_pair` (
  `id_pair` INT NOT NULL AUTO_INCREMENT,
  `Nachalo` VARCHAR(45) NOT NULL,
  `Konec` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_pair`),
  INDEX `id_pair_idx` USING BTREE (`id_pair` ASC)  KEY_BLOCK_SIZE=30)
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `rasp_kkmt`.`kkmt_prepod`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rasp_kkmt`.`kkmt_prepod` (
  `prepod_id` INT NOT NULL AUTO_INCREMENT,
  `Prepod_FIO` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`prepod_id`),
  INDEX `fk_fio` (`Prepod_FIO` ASC))
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `rasp_kkmt`.`kkmt_group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rasp_kkmt`.`kkmt_group` (
  `group_id` INT NOT NULL AUTO_INCREMENT,
  `Facultet` VARCHAR(45) NOT NULL,
  `Specialnost` VARCHAR(45) NOT NULL,
  `Kurs` VARCHAR(45) NOT NULL,
  `Nomer_group` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`group_id`),
  INDEX `fk_nomer_group` (`Nomer_group` ASC))
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `rasp_kkmt`.`kkmt_subjects`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rasp_kkmt`.`kkmt_subjects` (
  `subject_id` INT NOT NULL AUTO_INCREMENT,
  `Nazvanie_predmeta` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`subject_id`),
  INDEX `fk_nazvanie_predmeta` (`Nazvanie_predmeta` ASC))
ENGINE = InnoDB;
 
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Ответ: Зачем от него избавляться - создайте новый, только по одному полю kkmt_auditory_auditory_id.

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

И вообще у вас странная логика.

Почему АУДИТОРИЯ ссылается внешним ключом на расписание занятий?
Должно быть в точности наоборот.
В аудитории должен быть PRIMARY KEY по полю auditory_id, а уже расписание ссылаться внешним ключом на это поле.

Причем ссылочное поле kkmt_auditory_auditory_id должно быть типа INT и ссылаться не на строковый номер аудитории, а на её гарантированно уникальный идентификатор (auditory_id)! (у нас, например, в институте были аудитории с одинаковым номером, такие в вашу схему не ложатся никак).

Вдумайтесь, зачем вообще внешний ключ-то нужен - нужен он для того, чтобы нельзя было создать расписание в несуществующей аудитории. И чтобы нельзя было удалить аудиторию, пока в ней есть занятия (ну или автоматом удалить все занятия, вместе с удаляемой аудиторией - ON DELETE CASCADE).

А у вас сейчас аудитория на расписание ссылается - и какому бизнес кейсу это отвечает?
Удалили занятие и вместе с ним автоматом удалилась аудитория? Красота
Вопрос: Записать JSON в MySQL

Нужно записать файл этого типа в таблицу MySQL. Основной проблемой является возможность бесконечного вкладывания.

Javascript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[
  {
    "name": "Alphabet",
    "earnings": "500b",
    "logo": "https://lh3.googleusercontent.com/--Gf6MTSKo3M/Ve9Hxp-79nI/AAAAAAAAL_Y/-KL3cslmFrUf2ldbbZPxCjESCW6R3gjfQ/s379-p/A.png",
    "subsidiaries": [
      {
        "name": "Google",
        "earnings": "400b",
        "logo": "https://upload.wikimedia.org/wikipedia/commons/thumb/5/53/Google_%22G%22_Logo.svg/1024px-Google_%22G%22_Logo.svg.png",
        "subsidiaries": [
          {
            "name": "Gmail",
            "earnings": "50b",
            "logo": "http://vignette1.wikia.nocookie.net/logopedia/images/8/80/Gmaillogo2014.png/revision/latest?cb=20150413012150"
          },
          {
            "name": "Google Drive",
            "earnings": "20b",
            "logo": "http://img.stackshare.io/service/148/5qxhymf609p5sizjjqvz.png"
          }
        ]
      },
      {
        "name": "YouTube",
        "earnings": "100b",
        "logo": "https://cdn3.iconfinder.com/data/icons/social-icons-5/607/YouTube_Play.png"
      }
    ]
  },
  {
    "name": "Oracle",
    "earnings": "200b",
    "logo": "http://austininvestmentrealty.com/wp-content/uploads/oracle-logo.jpg"
  }
]
Ответ: в 5.7.7 добавлен тип данных JSON
см статьи Рика Хиллегаса про его использование
Вопрос: Внешние ключи в связке Аксесс2003-MySQL

Форумчане, доброго времени суток!

Вопрос наверно очень простой, а именно:
Перенес данные из таблиц Аксесс 2003 на MySQL. Оказалось что программа для переноса ( Bullzip ) не переносит в MySQL внешние ключи.
Поправил, используя консоль HeidiSQL. т.е. сформировал в таблице внешний ключ.
Но открываю через интерфейс Аксесс линк этой таблицы изменений не вижу (что-бы в поле раскрывался список-этого нет)
Может я не то смотрю, или не то сделал? Или что-то не доделал?
Подскажите, пожалуйста!
Ответ: Нашел проблему, и, соответственно? ее решил.
Дело было в том что в таблице аксесс поле, которое я хотел сделать ключевым, по факту таковым не было. т.е. тип поля был счетчик и все.(не ключевое)
При перекачке таблиц с их содержимых в MySQL товарищ Bullzip сделал это поле не первичным ключом (PRIMARY KEY) а просто ключом (KEY). Отсюда и проблемы с не обновляемостью таблицы. Вот такая история... мммда....
Вопрос: денормализация mysql OLTP на базе MongoDB

Есть MySQL OLTP система со множеством отношений между таблицами. Например есть таблица products и вокруг нее десятки связанных таблиц.. различные типы, описания, скриншоты и так далее. Отношения между ними как 1 к 1 так и 1 ко многим, много ко многоим. Для отображения на клиенте каталога продукции необходимо делать запросы к mysql используя сложные joins что очень негативно сказывается на производительности.

Возникла идея денормализировать сущность product. Тоетсь при наступлении любого из CRUD событий с продуктом, мы будем формировать сообщение об этом в gearman queue а на другой стороне gearman worker будет создавать полное денормализированное представление продукта со всеми зависимостями и формировать документ на базе JSON и сохранять его в колекции products MongoDB.

С этим все более менее понятно, но вопрос возникает по след поводу - у продукта могут быть комментарии от пользователя. У каждого продукта их может быть разное число. Когда мы отображаем каталог продуктов возле каждого продукта хотелось бы показывать это число комментариев. Понятно что на лету просчитывать count для каждой записи в реляционной структуре это не самый лучший вариант. Здесь тоже напрашивается решение с отложенной обработкой через gearman queue который или будет заново пересчитывать через count общее число коментов для продукта и писать в mongodb либо делать +1 на числе из mongodb и обновлять там значение.

Вопрос - где лучше в Монго держать значение comments_number ? в той же коллекции products в отдельном филде или полностью в отдельной коллекции ?

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

Посоветуйте плиз как лучше организовать данные в Монго для описанных мной сценариев. Спасибо !
Ответ: поддерживаю тех, кто щитает, что прежде чем думать о построении звездолёта, обгоняю
щего аналогинчые системы гугла или амазона, нужно подумать - а будет ли такая нагрузка.

лично я не увидел причин для монго...

ну надо денормализованое хранить - чем мускл не подходит(или отдельная копия мускла на отдельном сервере раз переживаете за производительность).

монго хороша когда нет чёткой структуры... пока структура чёткая...я бы не парился...

ЗЫ
на конференциях бигдата, часто слышно - ноускл хорошо, вот для той задачи той конторы где родилась та или иная ноускл субд... а вот другим...

ЗЫЗЫ
мне советовали(ещо не пробовал) когда речь идёт о том что нам надо мускл и чучуть монго, посмотреть на постгри, где в новой версии есть возможность хранить json значения.
Вопрос: JSON и индексы

Версия БД 9.3.
В этой версии впервые появился JSON Type.
Вопрос - есть ли возможность накладывать индекс на JSON type, чтобы быстро получать значения по ключу (в документации по этому поводу ничего не сказано)?
Может быть есть, какие-нибудь расширения для этого типа.
Поделитесь опытом.
Спасибо.
Ответ: Нашел такое решение.

CREATE TABLE users
(
  "user" json
)
CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int))


По пробывал, работает.