Mysql Как создать кластеризованный индекс?

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

т.е. хранилище данных для сообщений пользователей. Каждое сообщение имеет идентификатор, но также имеет идентификатор пользователя, но поскольку пользователи могут отправлять сообщения несколько раз, идентификатор пользователя не является первичным ключом. Как бы вы добавили кластерный индекс в идентификатор пользователя, и это даже хорошая идея?

Ответ 1

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

Все индексы, кроме кластеризованного, называются вторичными.

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

Если вы не определили PRIMARY KEY для своей таблицы, MySQL находит первый индекс UNIQUE, в котором все ключевые столбцы имеют значение NOT NULL, а InnoDB использует его в качестве кластеризованного индекса.

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

Ответ 2

Как описывает @Olaf, InnoDB выбирает, какая комбинация столбцов или столбцов будет кластеризованным индексом (первичный ключ или первый уникальный индекс, если нет первичного ключа, или скрытый столбец, если нет ни одного из них),

Если вы хотите иметь уникальный столбец в качестве кластерного индекса, вы можете определить post_id как уникальный ключ и сделать комбинацию user_id и post_id первичного ключа, который будет выбран как кластеризованный индекс:

CREATE TABLE Post
( post_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
--- other columns
, CONSTRAINT Post_PK
    PRIMARY KEY (user_id, post_id)     -- your clustered index
, CONSTRAINT post_id_UQ
    UNIQUE (post_id)             -- you still want uniqueness for the `post_id`
) ENGINE = InnoDB ;

Будет ли это хорошей идеей или нет, зависит от вашего приложения, томов данных и запросов, которые у вас есть. В общем, лучшие свойства кластерного ключа уникальны, узки, статичны и постоянно растут. Вот почему лучше всего использовать автоинкрементные столбцы. Читайте об этом в статьях Kimberly L. Tripp: Постоянно растущий кластерный ключ - обсуждение кластерного индекса.......... снова! и Дискуссия с кластерным индексом продолжается... (не останавливайтесь, потому что они предназначены для SQL-Server, то же самое относится к кластерной индексации InnoDB на 99%)

Кластеризованный ключ, такой как (user_id, post_id), имеет первые 3 свойства, но он никогда не увеличивается. Это приведет к фрагментации CI и, возможно, более медленных вставок в таблицу.

Однако это приведет к более эффективным запросам, которые имеют условия WHERE user_id = ? или условия диапазона WHERE user_id BETWEEN ? AND ? или GROUP BY user_id, поскольку требуемые данные будут найдены в кластерном индексе в одном месте и в требуемом порядке.

Я предлагаю вам сделать тесты, чтобы выбрать, что лучше в вашем случае.

Существует также вариант MySQL, TokuDB, который позволяет использовать несколько кластеризованных индексов в таблице. Подробности в их статье: Представление нескольких индексов кластеризации

Ответ 3

Я предлагаю вам задать неправильный вопрос.

Один альтернативный вопрос: "Могу ли я избавиться от своего текущего PRIMARY KEY, чтобы я мог сделать эту другую вещь" кластеризованной "?" Часто AUTO_INCREMENT можно удалить или превратить в простой INDEX.

Более вероятный вопрос: "Каков оптимальный индекс для этого SELECT ...?". Другие отметили, что второй кластеризованный индекс не может быть и для базового MySQL, так что следующий выбор? Я не могу ответить на это, не зная SELECT. Однако моя Index Cookbook отвечает на вопрос о большом значении SELECTs.

Ответ 4

Когда вы определяете первичный ключ для таблицы InnoDB, MySQL использует первичный ключ в качестве кластеризованного индекса.

Если у вас нет первичного ключа для таблицы, MySQL будет искать первый индекс UNIQUE, где все ключевые столбцы имеют значение NOT NULL, и использовать этот индекс UNIQUE в качестве кластеризованного индекса.

В случае, если таблица InnoDB не имеет первичного ключа или подходящего индекса UNIQUE, MySQL внутренне создает скрытый кластеризованный индекс с именем GEN_CLUST_INDEX в синтетическом столбце, который содержит значения идентификатора строки.