Недостаток производительности Composite Primary Key в MySQL

У нас есть таблица с составным Первичным ключом, состоящим из трех полей (и это в MySQL 5.1). В этой таблице около 200 вставок и 200 выборок в секунду, а размер таблицы составляет около 1 миллиона строк и увеличивается.

Мой вопрос: "Сложный первичный ключ" снижает производительность вложений и выделений в этой таблице?

Должен ли я использовать простое поле "Автоматическое увеличение INT ID" вместо составного первичного ключа? (Я думаю, что ответ очень сильно связан с тем, как MySQL обрабатывает индексы на нескольких столбцах)

Ответы

Ответ 1

INSERT и UPDATE производительность мало меняется: она будет почти такой же для клавиш (INT) и (INT, INT).

SELECT производительность композитного PRIMARY KEY зависит от многих факторов.

Если ваша таблица InnoDB, тогда таблица неявно кластеризована по значению PRIMARY KEY.

Это означает, что поиск обоих значений будет быстрее, если оба значения содержат ключ: никакого дополнительного поиска ключа не потребуется.

Предполагая, что ваш запрос выглядит примерно так:

SELECT  *
FROM    mytable
WHERE   col1 = @value1
        AND col2 = @value2

и расположение таблицы таково:

CREATE TABLE mytable (
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        data VARCHAR(200) NOT NULL,
        PRIMARY KEY pk_mytable (col1, col2)
) ENGINE=InnoDB

движок просто должен будет найти точное значение ключа в самой таблице.

Если вы используете поле автоинкремента в качестве поддельного идентификатора:

CREATE TABLE mytable (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        data VARCHAR(200) NOT NULL,
        UNIQUE KEY ix_mytable_col1_col2 (col1, col2)
) ENGINE=InnoDB

тогда движок, во-первых, будет искать значения (col1, col2) в индексе ix_mytable_col1_col2, извлекает указатель строки из индекса (значение id) и выполняет другой поиск id в самой таблице.

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

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

Ответ 2

Если это InnoDB, составной первичный ключ будет включен в каждую запись в каждом из вторичных индексов.

Это означает, что

  • Ваши вторичные индексы занимают столько места, сколько эти столбцы + все столбцы первичного ключа
  • Вы можете использовать вторичный индекс в качестве индекса покрытия, если все необходимые столбцы содержатся во вторичном индексе + pk

Это, конечно, недостаток и преимущество соответственно.

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

Конечно, если у вас есть внешние ключи в других таблицах, они более широкие, а также они должны включать весь ключ из вашей основной таблицы.

Но я бы сказал, что на балансе вообще нет. Наличие составного первичного ключа НЕ вызывает проблему самостоятельно. Однако иметь "большой" первичный ключ (например, большие varchars) может, если это перевешивает преимущества кластеризации и возможности использовать индексы покрытия.

Ответ 3

  • Наличие этого составного первичного ключа замедляет SELECT крошечный бит, хотя эффект практически ничтожен и не стоит беспокоиться.
  • Если те столбцы, индексированные вообще, замедляют ваши INSERT s, и вы, безусловно, делаете достаточно INSERT, чтобы беспокоиться об этом. Это гораздо больше беспокоит, если это таблица MyISAM, где INSERT блокирует таблицу, чем если она является таблицей InnoDB. Если, перейдя с первичным ключом auto_increment, вы сможете оставить эти столбцы неиндексированными, вы выиграете от изменения. Если вам все равно нужно будет индексировать эти три столбца (например, если вам нужно обеспечить уникальность их комбинации), он не будет делать ничего для вас по производительности.