Что такое кластерный индекс в mysql?
Изучаю вопрос различия между кластерным и некластерным индексом и не могу понять, что такое кластерный индекс в mysql innoDB? Выглядит так, как будто это просто физическая сортировка данных по индексируемому полю. Можете максимально понятно объяснить, что такое кластерный индекс? Создаётся ли отдельная таблица или просто упорядочивается хранение существующих данных? Если данные упорядочиваются этим индексом, допустим по ID, то почему при select без сортировки данные могут возвращаться в произвольном порядке, а не отсортированные по ID по-умолчанию?
Дополнительно:
Если данные упорядочиваются этим индексом, допустим по ID, то почему при select без сортировки данные могут возвращаться в произвольном порядке, а не отсортированные по ID по-умолчанию?
Потому что без order by возвращается не в отсортированном порядке, а в том, в каком удобнее читать.
А физически на диске данные вполне могут лежать не по порядку.
В каком - зависит от конкретного движка.
Innodb и myisan вроде по разному кладут
А по поводу остальных вопросов можете подсказать? Вообще описать, как этот кластерный индекс работает?
А в деталях сам не расскажу, тк слишком далёк от мускула
Кластерный индекс... это на самом деле понятие крайне виртуальное.
Что такое обычный некластерный индекс? берём выражение индекса, считаем его значение для каждой записи, сортируем и пишем на диск. Получаем отдельную структуру, в которой выражение индекса сортировано. Когда потребуется искать заданное значение этого выражения, мы вместо просмотра от записи к записи сразу половинным делением быстренько найдём нужное значение, возьмём из него уникальный идентификатор записи, и обратимся за записью. Если в таблице 1000 записей, то для поиска заданного значения без индекса нам в среднем пришлось бы просмотреть 500 записей, а с индексом - всего 10.
Теперь что такое кластерный индекс... сначала почти то же. Берём выражение индекса, считаем его значение для каждой записи, сортируем и... а вот теперь не записываем по порядку эти значения с номерами соответствующих записей в отдельную структуру, а сами записи располагаем в этом порядке. Теперь, когда потребуется искать заданное значение этого выражения, мы вместо просмотра от записи к записи, как это было, когда записи не сортированы, сразу половинным делением быстренько найдём нужное значение. Но нам уже не надо получать номер записи и обращаться за ней - мы нашли саму нужную запись.
В MySQL (точнее, в используемом по умолчанию движке InnoDB) первичный индекс, во-первых, существует ВСЕГДА, во-вторых, определяется так (в статье, на которую дали ссылку, имеются неточности в пункте 2):
- Если первичный ключ задан явно, то его выражение является также и выражением кластерного индекса. Или иначе - первичный ключ и есть кластерный индекс.
- Если первичный ключ явно не задан, но в таблице имеется индекс, отвечающий всем следующим требованиям:
- является уникальным
- не является функциональным, в т.ч. не использует в выражении вычисляемые поля
- не использует в выражении поля, которые определены как допускающие значение NULL
то именно такой индекс используется в качестве первичного. А если таких индексов несколько, то используется первый по тексту запроса на создание таблицы
- Если не имеется ни того, ни другого - генерируется синтетический скрытый 6-байтовый номер записи, который и используется как первичный ключ. Следует отметить, что штатных способов доступа к этому значению не существует.
Выглядит так, как будто это просто физическая сортировка данных по индексируемому полю.
Фактически - именно так.
Создаётся ли отдельная таблица или просто упорядочивается хранение существующих данных?
Не создаётся. Но при изменении первичного индекса таблица полностью пересоздаётся с новым физическим порядком записей.
Если данные упорядочиваются этим индексом, допустим по ID, то почему при select без сортировки данные могут возвращаться в произвольном порядке, а не отсортированные по ID по-умолчанию?
Если не задан явно ORDER BY, сервер имеет право вернуть записи в любом порядке, как ему удобнее. В большинстве случаев, но не всегда, он будет возвращать записи в порядке чтения с диска...
Представь такой (на самом деле невозможный, но не суть) случай - ты запросил таблицу. Вторая половина её ещё лежит в кэше, а первая уже выдавлена оттуда данными другой таблицы, нужными для выполнения запроса. Конечно, наиболее оптимальным будет начать передачу данных клиенту с этих записей, а пока они передаются, подчитать остальные, и передать их позже. Вот тебе порядок-то и поломался...
===
PS. Кстати, правило выбора индекса, который будет использоваться в качестве кластерного, имеет неприятный побочный эффект. Если у некоторых полей, входящих в какие-то индексы, изменяется свойство NULLability, то это может привести к изменению того, какой из имеющихся индексов станет использоваться в качестве первичного по пункту 2. В результате мы получим невозможность использования INSTANT / INPLACE методов, и будет использован длинный COPY. Впрочем, ситуация такая крайне редка.
- Большое спасибо за такой развернутый ответ
-
на самом деле невозможный, но не суть
Более чем возможный.
SELECT ... WHERE id > 100
SELECT ... WHERE id > 0
И, например, у постгреса дока про схему «сперва отдать лежащее в оперативке пока данные с диска тянутся» написано явно. Вполне возможно, что и мускуль так делает, хоть дока и не говорит об этом.
Ответы:
https://habr.com/ru/articles/141767/
- Уже читал эту статью. Всё равно вопросы остаются. Например, вот этот скрин:
Являются ли синие страницы, с индексами, отдельной таблицей или это часть самой таблицы t1? И серые страницы - это страницы непосредственно часть t1 или просто дублированные данные, которые хранятся в месте с индексом?
- MikhailTv, Тогда читайте официальную документацию.
https://dev.mysql.com/doc/refman/8.3/en/innodb-arc...
https://dev.mysql.com/doc/refman/8.3/en/innodb-ind...
В зависимости от параметра innodb_file_per_table каждая таблица с её индексами может храниться в отдельном файле или же все данные собираются в один файл. Отдельных файлов для индексов не создаётся, они используют ту же страничную организацию в файле, что и данные самой таблицы.
Голубым цветом показаны страницы, используемые для размещения индекса, серым - страницы с данными таблицы. - Прочитал, ничего не понял, написано кластерный индекс ссылается на данные которые хранятся "кучкой" не фрагментировано.
И в конце статьи написано:
Если в таблице задан PRIMARY KEY — это он
Иначе, если в таблице есть UNIQUE (уникальные) индексы — это первый из нихТак какая польза от уникального кластерного индекса, когда он и так ссылается на единственную строку с данными?
- psiklop, Пройдя по индексным записям B-tree мы получаем не конкретную строку, а страницу, в которой находится строка (кластер). Внутри страницы строки упорядочены по первичному индексу, что позволяет использовать двоичный поиск.
- Rsa97, ясно. А все-таки имеет смысл переназначить кластерный индекс и как? В статье ничего про это нет, получается, что mysql использует primary key и сам всем управляет и статья чисто познавательная.
- psiklop, За исключением innodb_fill_factor каких-то способов повлиять на работу индекса нет.
Из полезного, что можно найти в документации:
- кластерный индекс создаётся всегда, по первичному ключу, первому созданному уникальному ключу или по скрытому полю со служебным ID строки;
- при разделении страниц полученные две страницы заполнены на 1/2, то есть объём файла может в два раза превышать объём хранимых данных;
- вторичные индексы ссылаются не напрямую на данные, а содержат копию данных первичного индекса, так что чем длиннее запись первичного индекса, тем больше места занимают вторичные индексы.
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Кластерный индекс в MySQL это особый тип индекса, который определяет порядок хранения данных в таблице. Когда вы создаете кластерный индекс для таблицы, MySQL использует этот индекс для организации данных в таблице физически на диске. Из-за этого кластерный индекс влияет на производительность запросов, так как он определяет, как данные будут храниться и доступны для чтения.
Когда вы создаете кластерный индекс, MySQL использует его для сортировки данных в таблице по значениям этого индекса. Это означает, что строки данных в таблице будут упорядочены в соответствии с порядком значений кластерного индекса. Важно отметить, что в таблице может быть только один кластерный индекс.
Использование кластерного индекса может улучшить производительность запросов, особенно при выполнении операций поиска, сортировки и объединения данных. Поскольку данные в таблице будут отсортированы в соответствии с кластерным индексом, MySQL сможет быстрее находить нужные записи и уменьшать время выполнения запросов.
Пример создания кластерного индекса в MySQL:
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50) ) ENGINE=InnoDB; ALTER TABLE users ADD INDEX idx_name (name), ADD UNIQUE INDEX idx_email (email), ADD PRIMARY KEY (id);
В этом примере мы создаем таблицу "users" с полями "id", "name" и "email". Затем мы добавляем кластерный индекс по полю "id", который будет использоваться для сортировки данных в таблице.
Итак, кластерный индекс в MySQL играет важную роль в организации данных в таблице и оптимизации производительности запросов. При проектировании базы данных следует тщательно выбирать поля для кластерного индекса, чтобы обеспечить эффективную работу с данными.