Недостаток производительности композитного первичного ключа в MySQL

Question

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

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

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

Answer 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) и будет использоваться таким же образом.

Answer 2

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

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

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

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

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

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

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

Answer 3

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