В чём заключается прикол объединения индексов в MySql и на что это влияет?

Question

[Игорь]

Пришло время узнать тайну.
Хотелось бы до конца осознать, как правильно строить индексы.

Например, два запроса.

SELECT * FROM contacts_history ch WHERE ch.owner_id = 17 AND ch.organization_id = 1;
SELECT * FROM contacts_history ch WHERE ch.owner_id = 17;

Стоит ли создавать несколько вариантов индексов в зависимости от условия.

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

И правильно я понимаю, что наличие большого набора (вариантов) индексов, влечёт за собой потерю производительности при вставке новой записи в таблицу?

Текущая ситуация

spoiler

CREATE TABLE `contacts_history` (
	`id` BIGINT(19) NOT NULL AUTO_INCREMENT,
	`contact_id` BIGINT(19) NULL DEFAULT NULL,
	`owner_id` INT(10) NULL DEFAULT NULL,
	`creator_id` INT(10) NULL DEFAULT NULL,
	`organization_id` INT(10) NULL DEFAULT NULL,
	`project_id` BIGINT(19) NULL DEFAULT NULL,
	`status_id` INT(10) NULL DEFAULT NULL,
	`session_start_time` DATETIME NULL DEFAULT NULL,
	`session_end_time` DATETIME NULL DEFAULT NULL,
	`call_start_time` DATETIME NULL DEFAULT NULL,
	`call_end_time` DATETIME NULL DEFAULT NULL,
	`direction` TINYINT(3) NULL DEFAULT NULL COMMENT '(DC2Type:contact_call_direction_type)',
	`originator` TINYINT(3) NULL DEFAULT NULL COMMENT '(DC2Type:contact_call_originator_type)',
	`cause` TINYINT(3) NULL DEFAULT '0' COMMENT '(DC2Type:contact_call_cause_type)',
	`target` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`comment` LONGTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`status_result` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`status_color` VARCHAR(9) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`type` TINYINT(3) NULL DEFAULT NULL COMMENT '(DC2Type:contact_history_type)',
	`created_at` DATETIME NULL DEFAULT NULL,
	`audio_recording_id` VARCHAR(36) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`executive_action` TINYINT(1) NULL DEFAULT '0',
	`executive_webhook` TINYINT(1) NULL DEFAULT '0',
	`user_group_id` INT(10) NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `IDX_AD127C16E7A1254A` (`contact_id`) USING BTREE,
	INDEX `IDX_AD127C167E3C61F9` (`owner_id`) USING BTREE,
	INDEX `IDX_AD127C1661220EA6` (`creator_id`) USING BTREE,
	INDEX `IDX_AD127C1632C8A3DE` (`organization_id`) USING BTREE,
	INDEX `IDX_AD127C16166D1F9C` (`project_id`) USING BTREE,
	INDEX `IDX_AD127C166BF700BD` (`status_id`) USING BTREE,
	INDEX `IDX_AD127C161ED93D47` (`user_group_id`) USING BTREE,
	INDEX `IDX_AD127C1632C8A3DEE7A1254A61220EA61ED93D478B8E8428` (`organization_id`, `contact_id`, `creator_id`, `user_group_id`, `created_at`) USING BTREE,
	INDEX `owner_id_organization_id` (`owner_id`, `organization_id`) USING BTREE,
	CONSTRAINT `FK_AD127C16166D1F9C` FOREIGN KEY (`project_id`) REFERENCES `rmok`.`projects` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `FK_AD127C161ED93D47` FOREIGN KEY (`user_group_id`) REFERENCES `rmok`.`users_group` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `FK_AD127C1632C8A3DE` FOREIGN KEY (`organization_id`) REFERENCES `rmok`.`organizations` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `FK_AD127C1661220EA6` FOREIGN KEY (`creator_id`) REFERENCES `rmok`.`users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `FK_AD127C166BF700BD` FOREIGN KEY (`status_id`) REFERENCES `rmok`.`statuses` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `FK_AD127C167E3C61F9` FOREIGN KEY (`owner_id`) REFERENCES `rmok`.`users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `FK_AD127C16E7A1254A` FOREIGN KEY (`contact_id`) REFERENCES `rmok`.`contacts` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=742972
;

Всё, что есть в индексах используется в условиях выборки.

Обратите внимание на индекс IDX_AD127C1632C8A3DEE7A1254A61220EA61ED93D478B8E8428
Означает ли это, что если условие максимально подходит под индекс, я получу большую производительность?

Comments

[Дмитрий]

Главное дамп сделать перед манипуляциями
Чтоб понять работает индекс или нет, можно посмотреть explain
Чем больше ключей, тем больше требуется времени на пересчёт индекса и больше свободного места.
Если запросы в основном на селект, то скорее всего будет выигрышь

[Игорь]

Если запросы в основном на селект, то скорее всего будет выигрышь

Каждую секунду новые данные, иногда даже чаще.

[FanatPHP]

Стоит ли создавать несколько вариантов индексов в зависимости от условия.

для этих двух запросов, разумеется, нет.
на этот вопрос можно было ответить самостоятельно, если подумать

Означает ли это, что для каждого варианта нужно создать свой индекс?

Если здесь ожидается простой однозначный ответ на все случаи жизни то вынужден разочаровать.

В чём заключается прикол объединения индексов в MySql и на что это влияет?

На скорость поиска нужных данных и также на скорость их чтения. Разумеется что из индекса данные читаются быстрее, чем с диска

Answer 1

[Slava Rozhnev]

Все сильно зависит от конкретной ситуации. Например в вашем случае составной индекс по полям (`owner_id`, `organization_id`) будет использоваться в обоих запросах.
Соответственно нет необходимости в отдельных индексах.

Обратите внимание, что если у вас есть составной индекс для (c1, c2, c3), у вас будут возможности индексированного поиска для одной из следующих комбинаций столбцов:

SELECT    *
FROM    table_name
WHERE    c1 = v1;

SELECT    *
FROM    table_name
WHERE    c1 = v1 AND c2 = v2;

SELECT      *
FROM    table_name
WHERE    c1 = v1 AND c2 = v2 AND c3 = v3;

Важно иметь в виду что каждый индекс замедляет операции добавления и изменения и не всегда помогает при выборках.
EXPLAIN Вам в помощь. И да прибудет с вами ACID

Answer 2

[rPman]

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

Так же большее влияние оказывает конфиг и железо, на котором все запускается и к примеру переход от hdd к ssd (кстати даже если индексы отдельно положить на ssd).

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