MySQL — Детали индекса

索引Используется для быстрого получения записей с определенным значением. Без индекса базе данных пришлось бы выполнять полное сканирование таблицы от первой записи до тех пор, пока не будет найдена соответствующая строка. Чем больше данных, тем выше стоимость поиска.Если во время поиска есть индекс по столбцу таблицы, тоMySQLВы можете быстро перейти в указанное место для поиска файлов данных, не просматривая все данные.

<!-- more -->

Обзор

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

В MySQL существует два типа хранения индексов:BTREE和HASH, Специфические и табличные двигатели хранения;

Механизм хранения MyISAM и InnoDBПоддерживает только индексы BTREE, Механизм хранения MEMORY/HEAPМожет поддерживать индексы HASH и BTREE..

преимущество

• Ускорить запрос данных
• Уникальный индекс, который может обеспечить уникальность каждой строки данных в таблице базы данных.
• С точки зрения достижения ссылочной целостности данных, это может ускорить объединение таблиц.
• При запросе данных с использованием предложений группировки и сортировки это также может значительно сократить время группировки и сортировки в запросе.

недостаток

• Занимает место на диске. В дополнение к пространству данных, занимаемому таблицей данных, каждый индекс также занимает определенный объем физического пространства. При наличии большого количества индексов файл индекса может достигать максимального размера файла быстрее, чем файл данных (Разумное использование, без проблем)
• Производительность потери (добавить, изменить, удалить) индекс необходимо поддерживать динамически

Классификация

Нормальные и уникальные индексы

• Нормальный индекс:Базовый тип индекса в базе данных, который позволяет вставлять повторяющиеся и нулевые значения в столбец, определяющий индекс.
• Уникальный индекс:Значение столбца индекса должно быть уникальным, но допускаются нулевые значения.Индекс первичного ключа — это специальный уникальный индекс, который не допускает нулевые значения (например, самоувеличивающиеся идентификаторы).

Одностолбцовые и составные индексы

• Индекс одного столбца:То есть индекс содержит только один столбец, а таблица может иметь несколько индексов с одним столбцом.
• Комбинированный индекс:Относится к индексу, созданному на основе комбинации нескольких полей в таблице,Индекс будет использоваться только в том случае, если левое поле этих полей используется в условии запроса

полный текстовый указатель

• Полнотекстовый индекс:ТипFULLTEXT, который поддерживает полнотекстовый поиск значений в столбцах, определяющих индекс, позволяя вставлять повторяющиеся и нулевые значения в эти индексированные столбцы. Полнотекстовые индексы могут быть созданы для столбцов типа CHAR, VARCHAR или TEXT,Только механизм хранения MyISAM в MySQL поддерживает полнотекстовое индексирование.

Принципы дизайна

Неправильный дизайн индекса или отсутствие индексов может снизить производительность баз данных и приложений.Эффективные индексы очень важны для хорошей производительности.

Меры предосторожности

1. Чем больше индексов, тем лучше, если в таблице большое количество индексов, это не только займет место на диске, но и повлияет наINSERT、DELETE、UPDATEи другие заявления, потому что индексы корректируются и обновляются по мере изменения данных в таблице.
2. Избегайте создания слишком большого количества индексов для часто обновляемых таблиц и держите в индексе как можно меньше столбцов и создавайте индексы для полей, которые часто используются для запросов, но избегайте добавления ненужных полей.
3. Лучше не использовать индекс для таблицы с небольшим объемом данных, так как из-за небольшого объема данных запрос может занять меньше времени, чем обход индекса, и индекс может не дать эффекта оптимизации.
4. Создавайте индексы для столбцов с большим количеством различных значений, которые часто используются в условных выражениях, и не создавайте индексы для столбцов с меньшим количеством различных значений.Например, если в поле гендера есть только мужчины и женщины, нет необходимости строить показатель. Если индексация не улучшит эффективность запросов, она серьезно снизит скорость обновления.
5. Когда определенной характеристикой уникальности являются сами данные, укажите уникальный индекс. Используйте уникальный индекс, который необходимо определить, чтобы обеспечить целостность столбцов данных для повышения скорости запросов.
6. Индексирование частого сортировки или группы (I.E., Group by или order ording) в столбце, если столбец будет отсортирован с множеством, вы можете создать композитный индекс на этих столбцах

использовать

При использовании CREATE TABLE для создания таблицы, помимо определения типа данных столбца, вы также можете определить ограничения первичного ключа, ограничения внешнего ключа или ограничения уникальности, индекс.

Основной синтаксис для создания индекса при создании таблицы следующий:

CREATE TABLE table_name[col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY]
[index_name](col_name[length])
[ASC|DESC]

Парафраз

1. UNIQUE, FULLTEXT и SPATIAL являются необязательными параметрами, представляющими уникальный индекс, полнотекстовый индекс и пространственный индекс соответственно.
2. INDEX и KEY являются синонимами, оба имеют одинаковый эффект для создания указанного индекса.
3. col_name — это столбец поля, который необходимо проиндексировать, и столбец должен быть выбран из нескольких столбцов, определенных в таблице данных.
4. index_name — это имя указанного индекса, который является необязательным параметром.Если он не указан, MySQL по умолчанию использует col_name в качестве значения индекса.
5. длина - необязательный параметр, указывающий длину индекса. Длина индекса может быть указана только в полях строкового типа.
6. ASC или DESC задает хранение значений индекса в порядке возрастания или убывания

нормальный индекс

-- 这句作用是,如果 customer1 存在就删除
DROP TABLE IF EXISTS customer1;
CREATE TABLE `customer1` (
  `customer_id` bigint(20) NOT NULL COMMENT '客户ID',
  `customer_name` varchar(30) DEFAULT NULL COMMENT '客户姓名',
  INDEX `idx_customer_id` (`customer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表';

контрольная работа

-- 查看当前表的索引情况
SHOW INDEX FROM customer1; 

-- 使用 EXPLAIN 分析 SQL语句 是否使用了索引
EXPLAIN SELECT * FROM customer1 WHERE customer_id = 1;

Парафраз

EXPLAINГрамматика будет подробно объяснена в следующей главе, в центре внимания этой главы является индекс

• select_type:Указывает тип используемого запроса SELECT. Значение здесь — SIMPLE, что означает простой SELECT без использования UNION или подзапросов. Другие значенияПЕРВИЧНЫЙ, ОБЪЕДИНЕНИЕ, ПОДЗАПРОС и т.д.
• Таблица:Указывает имена таблиц данных, читаемых базой данных, они располагаются в том порядке, в котором были прочитаны
• тип:Указывает связь между этой таблицей данных и другими таблицами данных, другие значениясистема, константа, eq_ref, ссылка, диапазон, индекс и все
• возможные_ключи:Различные индексы, которые MySQL может использовать при поиске записей данных
• ключ:Фактический индекс, используемый MySQL
• key_len:Длина индекса в байтах дана. Чем меньше значение key_len, тем быстрее выражение
• ссылка:Предоставляет имя столбца данных в другой таблице данных в отношении
• ряды:Ссылаться наMySQLОжидаемое количество строк данных, которые будут считаны из текущей таблицы данных при выполнении запроса.
• Дополнительный:Предоставляет информацию о связанных операциях

SHOW INDEX FROMграмматика

• Таблица:Представляет таблицу, для которой нужно создать индекс
• Не_уникальный:Указывает, что индекс не является уникальным индексом, 1 означает неуникальный индекс, 0 означает уникальный индекс
• Key_name:имя индекса
• Seq_in_index:Указывает, что поле находится в индексе, индекс одного столбца изменен на значение 1, комбинированный индекс — это порядок, определенный в каждом поле в индексе.
• Имя_столбца:Представляет поле столбца, определяющее индекс
• Под_часть:Указывает длину индекса
• Нулевой:Указывает, может ли поле быть нулевым
• Тип_индекса:Указывает тип индекса

когдаpossible_keysиkeyкак дляidx_customer_id, что указывает на то, что индекс использовался в запросе

уникальный индекс

Индекс с одним столбцом – это индекс, созданный для поля в поле таблицы данных. В таблице можно создать несколько индексов с одним столбцом. Индексы, созданные в первых двух примерах, представляют собой один индекс coli, например:

DROP TABLE
IF EXISTS customer1;

CREATE TABLE `customer1` (
    `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客户姓名',
    UNIQUE INDEX `idx_customer_id` (`customer_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';

Это означает, что таблицаcustomer_idсоздал поле с именемidx_customer_idуникальный индекс

Композитный индекс

Составной индекс создает индекс для нескольких полей, таких как:

DROP TABLE
IF EXISTS customer1;

CREATE TABLE `customer1` (
    `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客户姓名',
     INDEX `idx_group_customer` (`customer_id`,`customer_name`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';

SHOW INDEX FROM customer1;

Это дляcustomer_id、customer_nameДва поля успешно создалиidx_group_customerсоставной индекс , черезSHOW INDEX FROM customer1;Вы увидите две записи (с фотографиями)

полный текстовый указатель

Полнотекстовое индексирование может выполнять полнотекстовый поиск. Только механизм хранения MyISAM поддерживает полнотекстовое индексирование и только для столбцов CHAR, VARCHAR и TEXT. Индекс всегда выполняется для всего столбца и не поддерживает локальные индексы, такие как :

DROP TABLE
IF EXISTS customer1;

CREATE TABLE `customer1` (
    `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR (255) DEFAULT NULL COMMENT '客户姓名',
     FULLTEXT INDEX `idx_fulltext_customer_name` (`customer_name`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';

SHOW INDEX FROM customer1;

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

Увидев это творение, успешно создал файл с именем в информационном поле.idx_fulltext_customer_nameПолнотекстовый индекс FULLTEXT, полнотекстовый индекс очень подходит для больших баз данных, а для небольших наборов данных может быть менее полезным

Создать индекс для существующей таблицы

Чтобы создать индекс для существующей таблицы, вы можете использоватьALTER TABLEЗаявление илиCREATE INDEXОператор, так, объясните, как использовать таблицу ALTER и создавать индексные операторы для создания индексов на известных полях таблицы.

Синтаксис ИЗМЕНИТЬ ТАБЛИЦУ

Основной синтаксис ALTER TABLE для создания индекса:

ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL]
[INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

нормальный индекс

ALTER TABLE customer1 ADD INDEX idx_customer_id(`customer_id`);

ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));

Это означает, что при запросе необходимо получить только первые 50 символов. Здесь специально упоминается, что если вы можете указать максимально возможную длину префикса для индексации поля строкового типа, например, столбца CHAR(255), если он находится в пределах первых 10 или первых 30 символов, большинство значение уникально , ​​вам не нужно индексировать весь столбец.Короткие индексы могут не только повысить скорость запросов, но и сэкономить место на диске и сократить количество операций ввода-вывода.

уникальный индекс

ALTER TABLE customer1 ADD UNIQUE INDEX `idx_customer_id` (`customer_id`);

составной индекс

ALTER TABLE customer1 ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`);

Синтаксис СОЗДАТЬ ТАБЛИЦУ

Оператор CREATE INDEX может добавить индекс к существующей таблице.В MySQL CREATE INDEX отображается на оператор ALTER TABLE.Основная структура синтаксиса:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]

видеть иСинтаксис оператора ALTER INDEX практически такой же., следующееcustomer1Таблица будет удалена и создана заново. Все поля не имеют индексов. Используйте оператор CREATE INDEX, чтобы один раз создать индекс:

CREATE INDEX idx_customer_id ON customer1(`customer_id`);

CREATE UNIQUE INDEX idx_customer_id ON customer1(`customer_id`);

CREATE INDEX idx_group_customer ON customer1(`customer_id`,`customer_name`);

падение индекса

Последнее задание — удалить индекс, вы можете использоватьALTER TABLE和DROP INDEXУдалить индекс.

Синтаксис ИЗМЕНИТЬ ТАБЛИЦУ

Основной синтаксис ALTER TABLE:

ALTER TABLE table_name DROP EXISTS index_name;

ALTER TABLE table_name DROP INDEX IF EXISTS index_name;

Рекомендуется использовать второй

Синтаксис DROP INDEX

Основной синтаксис DROP INDEX:

DROP INDEX index_name ON table_name

DROP INDEX IF EXISTS  index_name ON table_name

Рекомендуется использовать второй

Обратите внимание на деталь: при удалении столбца в таблице, если удаляемый столбец является частью всего индекса, столбец также будет удален из индекса; если удалены все столбцы, составляющие индекс, весь индекс будет сброшен

скажите что-то

Обратите внимание на публичный аккаунт WeChat:battcnФоновый ответmysqlполучить《打造扛得住的MySQL数据库架构》

• Личный QQ: 1837307557
• battcn open source группа (подходит для новичков): 391619659