MySQL: оптимизация запросов хранилища ключей / значений
Я ищу помощь в настройке правильные показатели (Я пробовал слишком много, и теперь я немного потерян), правильный движок MySQL (MyIsam, InnoDB …) и помогите с мои запросы (Присоединяется, …). Также у меня болит голова, когда я думаю, что эти запросы должны вернуться кол-(*).
Мое время составляло более 5–10 секунд на запрос без учета, но я не уверен, смогу ли я получить лучшее время для этой большой базы данных.
Я пытаюсь оптимизировать таблицы MySQL:
Предметы (~ 600 тыс. Строк):
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| type | varchar(255) | NO | PRI | NULL | |
+-------+------------------+------+-----+---------+----------------+
Items_Relationships (~ 1M строк):
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| lft_item_id | int(11) unsigned | NO | PRI | NULL | |
| rgt_item_id | int(11) unsigned | NO | PRI | NULL | |
| rel_type | varchar(255) | NO | PRI | NULL | |
+-------------+------------------+------+-----+---------+-------+
Items_Values (~ 4M строк):
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| item_id | int(11) unsigned | NO | PRI | 0 | |
| name | varchar(255) | YES | MUL | NULL | |
| value | longtext | YES | | NULL | |
| lang | varchar(2) | YES | | NULL | |
+---------+------------------+------+-----+---------+----------------+
Я выполняю в основном эти общие запросы:
1. запрос — все элементы со значением «статус»> 1:
SELECT `company`.`id` AS `id`,
`company`.`type` AS `type`
FROM `items` AS `company`
INNER JOIN `items_values` AS `value_name` ON (`company`.`id` = `value_name`.`item_id`)
WHERE `company`.`type` = 'company'
AND `value_name`.`name` = 'status'
AND CONVERT(`value_name`.`value`, SIGNED) > 1
GROUP BY `company`.`id`
ORDER BY `company`.`id` DESC
LIMIT 0, 30
2. запрос — все элементы с некоторыми значениями, связанными с другими элементами:
SELECT `company`.`id` AS `id`,
`company`.`type` AS `type`
FROM `items` AS `company`
INNER JOIN `items_values` AS `value_status` ON (`value_status`.`item_id` = `company`.`id`)
INNER JOIN `items_relationships` AS `companies_categories` ON (`companies_categories`.`lft_item_id` = `company`.`id`)
INNER JOIN `items_values` AS `category_rgt` ON (`category_rgt`.`item_id` = `companies_categories`.`rgt_item_id`)
WHERE `company`.`type` = 'company'
AND `company`.`type` = 'company'
AND `value_status`.`name` = 'status'
AND CONVERT(`value_status`.`value`, SIGNED) >= 1
AND `category_rgt`.`name` = 'rgt'
AND (CONVERT(category_rgt.value, UNSIGNED) BETWEEN 2805 AND 4222)
AND `companies_categories`.`rel_type` = 'company_category'
GROUP BY `company`.`id`
ORDER BY `company`.`id` DESC LIMIT 10
OFFSET 0
Спасибо заранее!
Решение
Если вы спросите об индексах, значит, у вас уже есть почти все, что вам нужно, уже проиндексировано.
У меня есть только вопрос о
| name | varchar(255) | YES | MUL | NULL | |
Поэтому я бы предпочел установить его, а также первичный ключ.
У меня есть только одно предложение по поводу структур таблиц.
Если вы смешали строку и цифры в вашем
| value | longtext | YES | | NULL | |
создать другой столбец int_value SIGNED или без знака еще лучше.
И вы должны установить этот столбец как индекс (как только вам понадобится этот столбец в качестве фильтра и / или критериев поиска)
И заполните это поле при вставке / обновлении, где это применимо.
Эта модификация увеличит производительность запроса, когда вы не должны использовать CAST и / или CONVERT для миллионов записей, как здесь:
AND CONVERT(`value_status`.`value`, SIGNED) >= 1
AND (CONVERT(category_rgt.value, UNSIGNED) BETWEEN 2805 AND 4222)
Так что у меня больше нет комментариев о структуре.
Но я бы попросил вас попробовать мой запрос, как эксперимент, если он быстрее вашего. К сожалению, у меня нет возможности отлаживать какие-либо данные. Если вы предоставите некоторые sqlfiddle, это очень поможет.
SELECT `company`.`id` AS `id`,
`company`.`type` AS `type`
FROM `items` AS `company`
INNER JOIN (
SELECT
item_id,
FROM items_values
WHERE name = 'status'
AND CONVERT(value, SIGNED) >= 1
) AS value_status
ON value_status.item_id = company.id
INNER JOIN
(
SELECT
lft_item_id
FROM
items_relationships
INNER JOIN (
SELECT
item_id
FROM
items_values
WHERE name = 'rgt'
AND (CONVERT(value, UNSIGNED) BETWEEN 2805 AND 4222)
) AS category_rgt
ON category_rgt.item_id = items_relationships.rgt_item_id
WHERE items_relationships.rel_type = 'company_category'
) as companies_categories
ON (`companies_categories`.`lft_item_id` = `company`.`id`)
WHERE `company`.`type` = 'company'
GROUP BY `company`.`id`
ORDER BY `company`.`id` DESC
LIMIT 10
Другие решения
Других решений пока нет …