Добрый день!
Загрузка справочника КЛАДР.
Таблица kladr (классификатор адресов - перечень всех республик, регионов, городов, деревень.... и т.д. с указанием уникального кода)
Таблица kladr
CREATE TABLE `kladr` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`Name` varchar(40) NOT NULL,
`SocrName` varchar(10) NOT NULL,
`Code` varchar(15) NOT NULL,
`Index_` varchar(7) NOT NULL,
`Gninmb` varchar(4) NOT NULL,
`Uno` varchar(4) DEFAULT NULL,
`Ocatd` varchar(11) NOT NULL,
`Status` varchar(1) NOT NULL,
`code_1` varchar(40) DEFAULT NULL,
`code_2` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`) USING BTREE,
UNIQUE KEY `Code_UNIQUE` (`Code`),
) ENGINE=MyISAM AUTO_INCREMENT=581692 DEFAULT CHARSET=cp1251;
Таблица cat_district (перечень регионов)CREATE TABLE `cat_district` (
`id` (11) NOT NULL,
`name` varchar(51) NOT NULL,
`predefined` char(1) NOT NULL DEFAULT 'N',
`socr` varchar(10) DEFAULT NULL,
`kladr` char(5) DEFAULT NULL,
`country_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `country_id` (`country_id`,`subject_id`,`name`),
KEY `country_id_2` (`country_id`),
KEY `cat_district_ibfk_2` (`subject_id`),
CONSTRAINT `cat_district_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `cat_country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `cat_district_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `cat_subject` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Таблица cat_city (перечень городов)CREATE TABLE `cat_city` (
`id` int(11) NOT NULL,
`name` varchar(51) NOT NULL,
`predefined` char(1) NOT NULL DEFAULT 'N',
`socr` varchar(10) DEFAULT NULL,
`kladr` char(8) DEFAULT NULL,
`country_id` int(11) NOT NULL,
`subject_id` int(11) DEFAULT NULL,
`district_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `country_id` (`country_id`,`subject_id`,`district_id`,`name`),
KEY `cat_city_ibfk_2` (`subject_id`),
KEY `cat_city_ibfk_3` (`district_id`),
CONSTRAINT `cat_city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `cat_country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `cat_city_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `cat_subject` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `cat_city_ibfk_3` FOREIGN KEY (`district_id`) REFERENCES `cat_district` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Таблица Cat_Village (перечень деревень)CREATE TABLE `cat_village` (
`id` int(11) NOT NULL,
`name` varchar(51) NOT NULL,
`predefined` char(1) NOT NULL DEFAULT 'N',
`socr` varchar(10) DEFAULT NULL,
`kladr` varchar(11) DEFAULT NULL,
`country_id` int(11) NOT NULL,
`subject_id` int(11) DEFAULT NULL,
`district_id` int(11) DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `country_id` (`country_id`,`subject_id`,`district_id`,`city_id`,`name`),
KEY `subject_id` (`subject_id`),
KEY `district_id` (`district_id`),
KEY `city_id` (`city_id`),
CONSTRAINT `cat_village_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `cat_country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `cat_village_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `cat_subject` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `cat_village_ibfk_3` FOREIGN KEY (`district_id`) REFERENCES `cat_district` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `cat_village_ibfk_4` FOREIGN KEY (`city_id`) REFERENCES `cat_city` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Задача - загрузить данные в таблицу cat_village. Информация берется из полей таблиц cat_city, cat_district, kladr
Запросinsert into cat_village (id, name, socr, kladr, country_id, subject_id, district_id, city_id)
select k.id, k.name, trim(concat(coalesce(k.name, '') , ' ' , coalesce(k.socrname, ''))), k.socrname, substr(k.code, 1, 11), 1, cast(substr(k.code, 1, 2)as signed integer), d.id, c.id
from kladr k
left join cat_district d on substr(k.code, 1, 5) = d.kladr
left join cat_city c on substr(k.code, 1, 8) = c.kladr
where k.code not like '________000__' and (k.code like '___________00' or k.code like '___________99');
комментарии к запросу 1.
trim(concat(coalesce(k.name, '') , ' ' , coalesce(k.socrname, '')))
- записывает в поле "название деревни" + "тип деревни" (тип деревни - хутор, село, деревня, аул и прочее)
2.
substr(k.code, 1, 5)
и
substr(k.code, 1, 8)
- из общей таблицы адресов.поля с полным адресом code
kladr.code
берем только часть адреса которая идентифицирует регион и населенный пункт соответственно.
Объем таблицkladr - 193 897
cat_city - 3 629
cat_district - 1 871
Параметры MySqldefault-character-set=cp1251
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size = 100M
table_cache=256
tmp_table_size=9M
myisam_max_sort_file_size=100G
myisam_sort_buffer_size = 50M
key_buffer_size = 300M
read_buffer_size = 1024K
read_rnd_buffer_size = 2048K
sort_buffer_size = 2048K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=35M
innodb_log_file_size=18M
innodb_thread_concurrency=8
interactive_timeout = 28800
table_open_cache = 32
query_prealloc_size = 8192
range_alloc_block_size = 2048
preload_buffer_size = 32768
query_alloc_block_size = 8192
optimizer_search_depth = 62
Проблема - ооооочень долго выполняется запрос (так и не дождался завершения). На серваке с DB2 выполнение запроса заняло несколько минут. Помогите пжлста, может быть както можно оптимизировать запрос или поковырять настройки mysql'а.
