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

задняя часть MySQL Байду алгоритм

Я читал блог об индексе mysql сегодня, и я чувствую, что содержание очень хорошо написано, но, честно говоря, верстка вызвала у меня головную боль, поэтому я перепечатал его и перепечатал, чтобы я мог вернуться к нему позже, если я забуду Это.
адрес блога:Блог Woohoo.cn на.com/Я очень эмоционален/боюсь/6179…

1. Что такое индекс? Зачем создавать индекс?

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

Например: есть таблица людей, в которой есть записи 2W, которые записывают информацию о 2W людях. Существует поле «Телефон» для записи номера телефона каждого человека, и теперь я хочу запросить информацию о человеке, чей номер телефона xxxx.

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

При наличии индекса поле Телефон будет храниться определенным способом, чтобы при запросе информации по полю можно было быстро найти соответствующие данные, не перебирая 2W фрагментов данных. В MySQL есть два типа хранения индексов: BTREE и HASH.

То есть для хранения этого поля используется дерево или значение Hash.Чтобы знать, как найти его в деталях, вам нужно знать алгоритм. Теперь нам нужно только знать, что делает индекс и какова функция.

Во-вторых, преимущества и недостатки и принципы использования индексов в MySQL.

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

  1. Все типы столбцов MySql (типы полей) могут быть проиндексированы, то есть любое поле может быть проиндексировано.
  2. Значительно ускорить скорость запроса данных

недостаток:

  1. Создание и поддержка индексов требует времени и увеличивается по мере увеличения объема данных.
  2. Индексы также должны занимать место. Мы знаем, что данные в таблице данных также будут иметь максимальную онлайн-настройку. Если у нас есть большое количество индексов, индексный файл может достичь онлайн-значения быстрее, чем файл данных.
  3. Когда данные в таблице добавляются, удаляются или изменяются, индекс также необходимо поддерживать динамически, что снижает скорость обслуживания данных.

Принципы использования:

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

  1. Избегайте индексирования слишком большого количества часто обновляемых таблиц и создавайте индексы для полей, которые часто используются для запросов.
  2. Лучше не использовать индекс для таблицы с небольшим объемом данных, потому что из-за небольшого объема данных запрос всех данных может занять меньше времени, чем обход индекса, а индекс может не иметь индекса. эффект оптимизации.
  3. Не создавайте индекс для столбца (поля) с несколькими значениями одновременно, например, в поле «пол» таблицы учеников есть только два разных значения для мужчин и женщин. И наоборот, поле с более различными значениями может быть проиндексировано. Вышеупомянутое лишь некоторые из вещей, которые очень однобоки.Там определенно есть много других преимуществ или недостатков индекса, а также принцип использования.Сначала в принципе понять индекс, а затем, когда он реально используется в будущем, вы будете постепенно знать другие функции. Обратите внимание, что очень важно изучить эту главу: вы должны сначала узнать, что такое индекс, для чего он нужен, что он делает, зачем нужен индекс и т. д. Если вы не знаете, просто прочитайте текст написан выше еще раз. Достаточно создать несколько индексов в одной таблице, и эти индексы будут храниться в индексном файле (место, где специально хранятся индексы)

В-третьих, классификация индекса

Примечание. Индекс реализован в механизме хранения, то есть разные механизмы хранения будут использовать разные индексы.

Механизмы хранения MyISAM и InnoDB:только поддержкаИндекс BTREE, то есть BTREE используется по умолчанию и не может быть заменен

Механизм хранения MEMORY/HEAP: поддержка индексов HASH и BTREE.

Индексы делятся на четыре категории

Одностолбцовый индекс (общий индекс, уникальный индекс, индекс первичного ключа), составной индекс, полнотекстовый индекс, пространственный индекс

Индекс одного столбца:

Индекс содержит только один столбец, но в таблице может быть несколько одностолбцовых индексов. Не запутайтесь здесь.

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

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

Индекс, созданный на основе комбинации нескольких полей в таблице, будет использоваться только в том случае, если левое поле этих полей используется в условии запроса.При использовании комбинированного индекса следует самый левый набор префиксов. Если вы все еще этого не понимаете, я объясню это подробно, когда объясню это на примере позже 

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

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

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

Пространственный индекс

Пространственный индекс — это индекс, построенный на полях типа пространственных данных.В MySQL существует четыре типа пространственных данных: GEOMETRY, POINT, LINESTRING и POLYGON. При создании пространственного индекса используйте ключевое слово SPATIAL. Требуется, чтобы механизм был MyISAM, а столбец для создания пространственного индекса должен быть объявлен как NOT NULL. Подробнее см. ниже

В-четвертых, операции с индексами (создание и удаление)

создать индекс

  1. Создавать индекс при создании таблицы

Формат: CREATE TABLE имя таблицы [имя поля тип данных] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [имя индекса] (имя поля [длина])   [ASC|DESC]

|--------------------------------------| --------------------------| |-------------| |--------- ---- -| |---------------|   |-------------|

Обычный оператор создания таблицы         Какой тип индекса установить (уникальный, полнотекстовый и т. д.)  Ключевое слово индекса  Имя индекса По какому полю установить индекс  Сортировать индекс 

  • Создайте нормальный индекс
CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication)
)

CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
KEY(year_publication)
)

Вышеупомянутые два метода могут быть созданы.Благодаря этому примеру вы можете сравнить формат, и вы можете почти понять, что означает формат.

Распечатав результаты, если мы не напишем имя индекса при создании индекса, это автоматически поможет нам использовать имя поля в качестве имени индекса. Тест: чтобы увидеть, используется ли индекс для запроса.

SELECT * FROM book WHERE year_publication = 1990\G;

Объяснение: Хотя в таблице нет данных, есть ключевое слово EXPLAIN, которое используется для проверки того, используется ли индекс, и вывода информации об используемом индексе.

id：SELECT识别符。这是SELECT的查询序列号,也就是一条语句中，该select是第几次出现
。在这条语句中，select就只有一个，所以是1.

select_type：所使用的SELECT查询类型，SIMPLE表示为简单的SELECT，不使用UNION或子
查询，就为简单的SELECT。也就是说在该SELECT查询时会使用索引。其他取值，

PRIMARY：最外面的SELECT.在拥有子查询时，就会出现两个以上的SELECT。UNION：union
(两张表连接)中的第二个或后面的select语句  SUBQUERY：在子查询中，第二SELECT。

table：数据表的名字。他们按被读取的先后顺序排列，这里因为只查询一张表，所以只
显示book

type: 指定本据表和其他数据表之间的关联关系，该表中所有符合检索值的记录都会被取
出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该
键不是 primary key 或unique索引（换句话说，就是连接程序无法根据键值只取得一条
记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型
。(注意，个人这里不是很理解，百度了很多资料，全是大白话，等以后用到了这类信息
时，在回过头来补充，这里不懂对后面的影响不大。)可能的取值有
system、const、eq_ref、index和All

possible_keys：MySQL在搜索数据记录时可以选用的各个索引，该表中就只有一个索引，y
ear_publication

key：实际选用的索引

key_len：显示了mysql使用索引的长度(也就是使用的索引个数)，当key字段的值为null
时，索引的长度就是null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用
了哪些索引。这里就使用了1个索引，所以为1，

ref:给出关联关系中另一个数据表中数据列的名字。常量（const），这里使用的是1990
，就是常量。

rows：MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。

extra：提供了与关联操作有关的信息，没有则什么都不写。

上面的一大堆东西能看懂多少看多少，我们最主要的是看possible_keys和key 这两个属性，上面显示了key为year_publication。说明使用了索引。
  • Создать уникальный индекс
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
)

Объяснение: В поле id используется индекс, и имя индекса — UniqIdx.

SHOW CREATE TABLE t1\G;

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

INSERT INTO t1 VALUES(1,'xxx');
EXPLAIN SELECT * FROM t1 WHERE id = 1\G;

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

  • Создать индекс первичного ключа
CREATE TABLE t2
(
id INT NOT NULL,
name CHAR(10),
PRIMARY KEY(id)
);

INSERT INTO t2 VALUES(1,'QQQ');
EXPLAIN SELECT * FROM t2 WHERE id = 1\G;

Через этот индекс первичного ключа мы должны реагировать.На самом деле ограничение первичного ключа, которое мы объявили ранее, является индексом первичного ключа, но мы не изучали его раньше и не знаем.

  • Создайте индекс с одним столбцом

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

*Создать составной индекс

Составной индекс — это создание индекса по нескольким полям.

Создайте таблицу t3 и создайте составной индекс для полей идентификатора, имени и возраста в таблице.

CREATE TABLE t3
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id,name,age)
);

SHOW CREATE t3\G;

Интерпретировать крайний левый префикс

Комбинированный индекс следует за крайним левым префиксом и использует крайний левый набор столбцов в индексе для сопоставления строк. Такой набор столбцов называется крайним левым префиксом. Не имеет значения, если вы его не понимаете. несколько примеров.Например, вот id, name Индекс, состоящий из полей age3, хранится в порядке id/name/age в строке индекса.Индекс может индексировать следующие комбинации полей (id, name, age), ( id, имя) или (id). Если запрашиваемое поле не составляет крайний левый префикс индекса, то индекс не будет использоваться, например, возраст или комбинация (имя, возраст) не будет использовать запрос индекса.

В таблице t3 запросите поля id и name

EXPLAIN SELECT * FROM t3 WHERE id = 1 AND name = 'joe'\G;

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

EXPLAIN SELECT * FROM t3 WHERE age = 3 AND name = 'bob'\G;
  • Создать полнотекстовый индекс

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

CREATE TABLE t4
(
id  INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
)ENGINE=MyISAM;

SHOW CREATE TABLE t4\G;

Используйте так называемый полнотекстовый поиск. Во многих словах запись можно найти по ключевым словам.

INSERT INTO t4 VALUES
(8,'AAA',3,'text is so good，hei，my name is bob')
,(9,'BBB',4,'my name is gorlr');

SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr');


EXPLAIN SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr');

Примечание. При использовании полнотекстового поиска вам необходимо использовать функцию ПОИСКПОЗ, а ее полнотекстовый поиск имеет много ограничений, например, только через движок MyISAM, например, только полнотекстовый индекс может быть установлен на CHAR, VARCHAR, ТЕКСТ. Например, ключевое слово для поиска по умолчанию должно содержать не менее 4 символов. Например, если ключевое слово для поиска слишком короткое, оно будет проигнорировано. Подождите, если вы экспериментируете, возможно, вы не сможете экспериментировать. Заинтересованные студенты могут прочитать эту статью,Использование полнотекстового поиска

  • Создание пространственного индекса

Пространственные индексы также должны использовать механизм MyISAM, а поле пространственного типа не должно быть нулевым.

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

CREATE TABLE t5
(
g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
) ENGINE = MyISAM;

SHOW CREATE TABLE t5\G;

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

Формат: ALTER TABLE имя таблицы ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [имя индекса] (имя поля индекса)[ASC|DESC] Имея вышеизложенное основание, здесь нет необходимости заявлять слишком много.

Команда первая:

SHOW INDEX FROM 表名\G

Просмотр индексов, созданных в таблице

SHOW INDEX FROM book\G;

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

Table:创建索引的表

Non_unique：表示索引非唯一，1代表非唯一索引，0代表唯一索引，意思就是该索引是不是唯一索引

Key_name：索引名称

Seq_in_index :表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序(这个只需要知道单列索引该值就为1，组合索引为别的)

Column_name：表示定义索引的列字段

Sub_part：表示索引的长度

Null：表示该字段是否能为空值

Index_type：表示索引类型
  • Добавить индекс в таблицу

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

ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

Глядя на вывод, вы можете знать, что индекс был успешно добавлен.

Вот только пример общего индекса, добавление других индексов аналогично. Просто покрасьте совок в соответствии с тыквой. Я не буду объяснять их здесь по одному.

  • СОЗДАТЬ ИНДЕКС С СОЗДАТЬ ИНДЕКС

Формат: CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] имя индекса ON имя таблицы (создать имя поля индекса [длина])[ASC|DESC]

Объяснение: На самом деле, это замена супа вместо лекарства. Формат немного изменился. Он делает то же самое, что и выше. Давайте возьмем пример.

Добавьте нормальный индекс к таблице книги, поле - авторы.

CREATE INDEX BkBookNameIdx ON book(bookname);


SHOW INDEX FROM book\G;  //查看book表中的索引

Объяснение: первый снимок экрана не был сделан, потому что изображение слишком большое. Пока мы видим здесь только что добавленный индекс, он считается успешным. . Таким же образом создаются и другие индексы.

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

Метод добавления и запроса индекса в таблице был описан ранее.

Два способа добавить

1 Как создать индекс при создании таблицы,

2 Существует два способа добавления индексов в таблицу после ее создания.

способ запроса

SHOW INDEX FROM 表名\G；

\ G Просто пусть формат вывода выглядит лучше

Теперь поговорим о двух операциях по удалению индексов из таблицы.

  1. Формат 1: ALTER TABLE имя таблицы DROP INDEX имя индекса

Очень простое утверждение, давайте сейчас рассмотрим пример или поработаем с таблицей book и удалим индекс, который мы только что добавили к ней.

1. Удалите индекс с именем BkBookNameIdx в таблице книг.

ALTER TABLE book DROP INDEX BkBookNameIdx;


SHOW INDEX FROM book\G;  //在查看book表中的索引，就会发现BkBookNameIdx这个索引已经不在了

  1. Формат 2: DROP INDEX имя индекса ON имя таблицы

Удалите индекс с именем BkNameIdx в таблице книг.

DROP INDEX BkNameIdx ON book;
SHOW INDEX FROM book\G;

Категории