Как организовать индексы в MySQL на конкретном примере

27 Окт
2011

Имеется постоянно растущая база товаров, в которой данные удаляются и добавляются ежедневно, практически заменяя все строки. Сейчас в базе 470 тыс. записей. Если в поиске участвует что-либо кроме поиска по ключевым словам, то запросы становятся тяжелыми — около 6 секунд. Имеется такая вот таблица: CREATE TABLE IF NOT EXISTS `goods` ( `main_id` bigint(20) NOT NULL AUTO_INCREMENT, `id` bigint(20) NOT NULL DEFAULT '0', `e_shop` char(25) NOT NULL DEFAULT '', `brand` char(50) NOT NULL DEFAULT '', `model` char(100) NOT NULL DEFAULT '', `view` char(50) NOT NULL DEFAULT '', `brand_model_snip` char(50) NOT NULL DEFAULT '', `type` enum('1','2','3') NOT NULL DEFAULT '1', `state` enum('4','5','6','7','8','9','10') NOT NULL DEFAULT '4', `type_price` enum('204','205') NOT NULL DEFAULT '204', `price` float(18,2) NOT NULL DEFAULT '0.00', `price_uniq` float(18,2) NOT NULL DEFAULT '0.00', `cur_id` int(11) NOT NULL DEFAULT '0', `rent_time` enum('206','207','208','209','210','211') NOT NULL DEFAULT '206', `retail` enum('212','213') NOT NULL DEFAULT '212', `auction` enum('0','1') NOT NULL DEFAULT '0', `eshop_url` char(255) NOT NULL DEFAULT '', `add_text` char(255) NOT NULL DEFAULT '', `goods_reputation` bigint(20) NOT NULL DEFAULT '0', `sold` bigint(20) NOT NULL DEFAULT '0', `user_id` bigint(20) NOT NULL DEFAULT '0', `user_reputation` bigint(20) NOT NULL DEFAULT '0', `delivery_country` bigint(20) NOT NULL DEFAULT '0', `delivery_region` bigint(20) NOT NULL DEFAULT '0', `delivery_city` bigint(20) NOT NULL DEFAULT '0', `first_photo_url` char(255) NOT NULL DEFAULT '', `first_video_url` char(20) NOT NULL DEFAULT '', `goods_str` bigint(20) NOT NULL DEFAULT '0', `goods_ideal` bigint(20) NOT NULL DEFAULT '0', `user_str` bigint(20) NOT NULL DEFAULT '0', `user_ideal` bigint(20) NOT NULL DEFAULT '0', `user_name` char(30) NOT NULL DEFAULT '', `user_avatar` char(70) NOT NULL DEFAULT '', `actualize` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `num_photos` int(11) NOT NULL DEFAULT '0', `num_videos` int(11) NOT NULL DEFAULT '0', `word_1` char(25) NOT NULL DEFAULT '', `word_2` char(25) NOT NULL DEFAULT '', `word_3` char(25) NOT NULL DEFAULT '', `word_4` char(25) NOT NULL DEFAULT '', `word_5` char(25) NOT NULL DEFAULT '', `word_6` char(25) NOT NULL DEFAULT '', `word_7` char(25) NOT NULL DEFAULT '', `word_8` char(25) NOT NULL DEFAULT '', `word_9` char(25) NOT NULL DEFAULT '', `word_10` char(25) NOT NULL DEFAULT '', `word_11` char(25) NOT NULL DEFAULT '', `word_12` char(25) NOT NULL DEFAULT '', `word_13` char(25) NOT NULL DEFAULT '', `word_14` char(25) NOT NULL DEFAULT '', `word_15` char(25) NOT NULL DEFAULT '', `word_16` char(25) NOT NULL DEFAULT '', `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`main_id`), KEY `shop_index_idx` (`e_shop`), KEY `type_index_idx` (`type`), KEY `state_index_idx` (`state`), KEY `tprice_index_idx` (`type_price`), KEY `p_uniq_index_idx` (`price_uniq`), KEY `rent_t_index_idx` (`rent_time`), KEY `retail_index_idx` (`retail`), KEY `auction_index_idx` (`auction`), KEY `rep_index_idx` (`goods_reputation`), KEY `sold_index_idx` (`sold`), KEY `u_rep_index_idx` (`user_reputation`), KEY `d_country_index_idx` (`delivery_country`), KEY `d_region_index_idx` (`delivery_region`), KEY `d_city_index_idx` (`delivery_city`), KEY `id_idx` (`id`), KEY `brand_model_snip_idx` (`brand_model_snip`), KEY `user_id_idx` (`user_id`), KEY `cur_id_idx` (`cur_id`), KEY `word_1` (`word_1`), KEY `word_2` (`word_2`), KEY `word_3` (`word_3`), KEY `word_4` (`word_4`), KEY `word_5` (`word_5`), KEY `word_6` (`word_6`), KEY `word_7` (`word_7`), KEY `word_8` (`word_8`), KEY `word_9` (`word_9`), KEY `word_10` (`word_10`), KEY `word_11` (`word_11`), KEY `word_12` (`word_12`), KEY `word_13` (`word_13`), KEY `word_14` (`word_14`), KEY `word_15` (`word_15`), KEY `word_16` (`word_16`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4609148 ; Поиск осуществляется по: e_shop AND user_id id brand_model_snip Эти поля в поиске могут использоваться в самых различных вариациях: type AND state AND price_uniq AND rent_time AND retail AND auction AND (delivery_country AND delivery_region AND delivery_city) AND (word_1 OR… word_16) В скобках выделены целостные структуры, то бишь, то, что используется как одно целое. word_1 OR… word_16 — всегда используются вместе и с OR. delivery_country AND delivery_region AND delivery_city — из этих трех полей используется в поиске либо никакое либо какое-то одно. Сделать совместные индексы для всех вариаций поиска невозможно хотя бы по той причине, что максимальное число полей в одном индексе — 16. Плюс, чем больше индексов, тем дольше удаление, а база удаляет и добавляет записи постоянно таким образом, что на следующий день от вчерашний базы остается строк 400. А всего в базе пол миллиона. И это только начало. Вот пример: SELECT g.main_id AS g__main_id, g.id AS g__id, g.user_reputation AS g__user_reputation, g.e_shop AS g__e_shop, g.brand AS g__brand, g.model AS g__model, g.view AS g__view, g.type AS g__type, g.state AS g__state, g.type_price AS g__type_price, g.price AS g__price, g.price_uniq AS g__price_uniq, g.cur_id AS g__cur_id, g.rent_time AS g__rent_time, g.retail AS g__retail, g.auction AS g__auction, g.user_id AS g__user_id, g.first_photo_url AS g__first_photo_url, g.first_video_url AS g__first_video_url, g.goods_str AS g__goods_str, g.goods_ideal AS g__goods_ideal, g.user_str AS g__user_str, g.user_ideal AS g__user_ideal, g.user_name AS g__user_name, g.user_avatar AS g__user_avatar, g.actualize AS g__actualize, g.add_text AS g__add_text FROM goods g WHERE (g.price_uniq >= 1 AND (g.word_1 = "фотоаппарат" OR g.word_2 = "фотоаппарат" OR g.word_3 = "фотоаппарат" OR g.word_4 = "фотоаппарат" OR g.word_5 = "фотоаппарат" OR g.word_6 = "фотоаппарат" OR g.word_7 = "фотоаппарат" OR g.word_8 = "фотоаппарат" OR g.word_9 = "фотоаппарат" OR g.word_10 = "фотоаппарат" OR g.word_11 = "фотоаппарат" OR g.word_12 = "фотоаппарат" OR g.word_13 = "фотоаппарат" OR g.word_14 = "фотоаппарат" OR g.word_15 = "фотоаппарат" OR g.word_16 = "фотоаппарат")) LIMIT 11 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE g range p_uniq_index_idx,word_1,word_2,word_3,word_4,word_... p_uniq_index_idx 4 NULL 236945 Using where Этот запрос обрабатывается чуть больше 6 секунд. Если без g.price_uniq >= 1 AND то 0,3 секунды.
По материалам Хабрахабр.



загрузка...

Комментарии:

Наверх