Что такое индекс в MySQL? Как оптимизировать?

задняя часть MySQL

Индекс аналогичен библиографическому указателю, установленному университетской библиотекой, что может повысить эффективность поиска данных и снизить затраты на ввод-вывод базы данных. Производительность MySQL начинает постепенно снижаться около 3 миллионов записей, хотя в официальном документе указано 500~800w записей, поэтому для больших объемов данных очень необходимо строить индексы. MySQL предоставляет объяснение, которое используется для отображения подробной информации о выполнении SQL и может выполнять оптимизацию индекса.

1. Причины медленного выполнения SQL:

1. Аппаратные проблемы. Например, низкая скорость сети, нехватка памяти, низкая пропускная способность ввода-вывода, полное дисковое пространство и т. д.

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

3. Слишком много данных (подбаза данных и подтаблица)

4. Настройка сервера и настройка различных параметров (настроить my.cnf)

2. При анализе причин мы должны найти точку входа:

1. Сначала понаблюдайте, откройте журнал медленных запросов, установите соответствующий порог (например, если он превышает 3 секунды, это медленный SQL), а после последнего дня работы в производственной среде посмотрите, какой SQL медленный.

2. Объясните и медленный анализ SQL. Например, оператор SQL написан плохо, индекс отсутствует или недействителен, слишком много связанных запросов (иногда это недостаток дизайна или необоснованное требование) и так далее.

3. Показать профиль на шаг ближе к деталям выполнения, чем объяснить.Вы можете запросить, что сделал каждый SQL и сколько секунд заняло каждое из этих действий.

4. Найти DBA или эксплуатации и обслуживания для настройки параметров сервера MySQL.

3. Что такое индекс?

Официальное определение индекса в MySQL: Индекс — это структура данных, которая помогает MySQL эффективно получать данные. Мы можем просто понять это как:Структура данных для быстрого поиска в отсортированном порядке.Индекс Mysql в основном имеет две структуры: индекс B+Tree и индекс Hash. Индекс, на который мы обычно ссылаемся, если не указано иное, обычно относится к индексу, организованному структурой B-дерева (индекс B+Tree). Индекс выглядит следующим образом:

             

Самый внешний светло-синий дисковый блок 1 содержит данные 17, 35 (темно-синий) и указатели P1, P2, P3 (желтый). Указатель P1 указывает на блоки диска меньше 17, P2 находится в диапазоне от 17 до 35, а P3 указывает на блоки диска больше 35. Реальные данные существуют в узлах семядолей, то есть в нижнем слое 3, 5, 9, 10, 13... Нелистовые узлы не хранят реальные данные, а хранят только элементы данных, определяющие направление поиска, такие как 17, 35 .

Процесс поиска: например, чтобы найти 28 элементов данных, сначала загрузите блок диска 1 в память, происходит ввод-вывод, и указатель P2 определяется двоичным поиском. Затем выясняется, что 28 находится между 26 и 30, и дисковый блок 3 загружается в память через адрес указателя P2, и происходит второй ввод-вывод. Дисковый блок 8 находится таким же образом, и происходит третий ввод-вывод.

Реальная ситуация такова, что B+деревья на верхних трех уровнях могут представлять миллионы данных, а миллионы данных имеют только три ввода-вывода вместо миллионов операций ввода-вывода, и сокращение времени огромно.

4. Объясните анализ

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

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);

CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

Первый опыт, эффект от выполнения объяснения:

Использование индекса находится в трех столбцах возможных_ключей, ключа и ключа_len, Далее мы объясним это слева направо.

1.id

--id相同,执行顺序由上而下
explain select u.*,o.* from user_info u,order_info o where u.id=o.user_id;


--id不同,值越大越先被执行
explain select * from  user_info  where id=(select user_id from order_info where  product_name ='p8');

2.select_type

Вы можете увидеть экземпляр выполнения id, существуют следующие типы:

  • SIMPLE: указывает, что этот запрос не содержит запросов UNION или подзапросов.
  • PRIMARY: указывает, что этот запрос является самым внешним запросом
  • ПОДЗАПРОС: первый SELECT в подзапросе
  • UNION: указывает, что этот запрос является вторым или последующим запросом UNION.
  • ЗАВИСИМОЕ ОБЪЕДИНЕНИЕ: Второй или последующий оператор запроса в ОБЪЕДИНЕНИИ, в зависимости от внешнего запроса.
  • UNION RESULT, результат UNION
  • ЗАВИСИМЫЙ ПОДЗАПРОС: первый SELECT в подзапросе, который зависит от внешнего запроса, то есть подзапрос зависит от результата внешнего запроса.
  • DERIVED: производный, представляющий SELECT производной таблицы (подзапрос предложения FROM)

3.table

table представляет таблицу, участвующую в запросе, или производную таблицу:

explain select tt.* from (select u.* from user_info u,order_info o where u.id=o.user_id and u.id=1) tt

с идентификатором 1 указывает, что таблицы u и o с идентификатором 2 являются производными.

4.type

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


Обычно используемые значения для типа:

  • system: Данные в таблице только одни, это специальный константный тип.
  • const: эквивалентное сканирование запроса для первичного ключа или уникального индекса возвращает не более одной строки данных. Константный запрос очень быстрый, потому что он считывается только один раз. Например, в приведенном ниже запросе используется индекс первичного ключа, поэтому тип const Тип: объясните, выберите * из user_info, где id = 2;
  • eq_ref: этот тип обычно появляется в запросах на соединение нескольких таблиц, указывая, что для каждого результата в предыдущей таблице может быть сопоставлена ​​только одна строка результатов в последней таблице.И операция сравнения запроса обычно =, что более Например: объясните select * from user_info, order_info, где user_info.id = order_info.user_id;
  • ref: этот тип обычно появляется в запросах на соединение нескольких таблиц, для индексов с неуникальным или непервичным ключом или запросов, использующих самый левый индекс правила префикса. Например, в следующем примере используется тип запроса ref: объяснение выберите * из user_info, order_info, где user_info.id = order_info.user_id AND order_info.user_id = 5
  • диапазон: указывает, что используется запрос диапазона индекса, и некоторые записи данных в таблице получены через диапазон поля индекса.Этот тип обычно появляется в =, , >, >=, , BETWEEN, IN( ).Например, следующий пример представляет собой запрос диапазона: объясните select * from user_info, где id между 2 и 8;
  • index: Представляет полное сканирование индекса, аналогично типу ALL, за исключением того, что тип ALL представляет собой полное сканирование таблицы, а тип индекса сканирует только все индексы без сканирования данных Тип индекса обычно появляется в: Запросе к запрашиваемым данным доступен непосредственно в дереве индексов без сканирования данных, в этом случае в поле Дополнительно будет указано Использование индекса.
  • ALL: указывает на полное сканирование таблицы. Этот тип запроса является одним из наименее производительных запросов. Вообще говоря, наш запрос не должен иметь тип запроса ALL, потому что такой запрос содержит большой объем данных. Производительность — это огромная катастрофа. , Если запрос является запросом типа ALL, его обычно можно избежать, добавив индекс в соответствующее поле.

Вообще говоря, отношения производительности различных типов выглядят следующим образом:
      ALL < index < range ~ index_merge < ref < eq_ref < const < system
Поскольку тип ALL представляет собой полное сканирование таблицы, он является самым медленным при тех же условиях запроса. Хотя запрос индексного типа не является полным сканированием таблицы, он сканирует все индексы, поэтому он немного быстрее, чем тип ALL.Последние типы используют индексы для запроса данных, поэтому они могут фильтровать часть или большую часть данных, поэтому эффективность запросов выше.

5.possible_keys

Он указывает индекс, который mysql может использовать при запросе.Обратите внимание, что даже если некоторые индексы появляются в возможных_ключах, это не означает, что индекс будет фактически использоваться mysql.Какие индексы использует mysql при запросе, определяется ключевым полем Decide.

6.key

Это поле является индексом, который mysql фактически использует в текущем запросе. Например, при угощении гостя обедом, возможные_ключи — это количество людей, которые должны быть охвачены, а ключ — это то, сколько людей на самом деле достигает. показатель:

explain select o.* from order_info o where  o.product_name= 'p1' and  o.productor='whh';
create index idx_name_productor on order_info(productor);
drop index idx_name_productor on order_info;

Создайте составной индекс, а затем запросите:

7.key_len

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

8.ref

Указывает, какой столбец отображаемого индекса используется, и, если возможно, является константой. В атрибуте type из предыдущей статьи также есть ref.Обратите внимание на разницу

9.rows

Строки также являются важным полем. Оптимизатор запросов mysql оценивает количество строк данных, которые sql должен просмотреть и прочитать, чтобы найти результирующий набор на основе статистической информации. Это значение очень интуитивно понятно, чтобы показать эффективность sql. В принципе, чем меньше строк тем лучше.Можете сравнить примеры в ключе,один без индексации денег,рядов 9,после установления индекса,рядов 4.

10.extra

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

  • using filesort : Указывает, что mysql требует дополнительных операций сортировки, и эффект сортировки не может быть достигнут с помощью порядка индекса.Как правило, использование файловой сортировки рекомендуется оптимизировать и удалить, поскольку такой запрос потребляет много ресурсов процессора.
  • использование индекса: сканирование индекса покрытия, что означает, что запрос может найти необходимые данные в дереве индекса без сканирования файла данных таблицы, что часто означает, что производительность хорошая
  • использование временной: запрос использует временную таблицу, что обычно происходит в случае сортировки, группировки и объединения нескольких таблиц.Эффективность запроса невысока, рекомендуется оптимизация.
  • using where : имя таблицы фильтруется с использованием where

V. Случай оптимизации

explain select u.*,o.* from user_info u LEFT JOIN  order_info o on u.id=o.user_id;

Результат выполнения, тип имеет ALL и не имеет индекса:

Запускаем оптимизацию, создаем индекс по связанному столбцу, очевидно видим, что столбец ALL типа становится ref, и используется индекс, а также сканируются строки с 9 строк на 1 строку:

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

六、是否需要创建索引？ 

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

              

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

 

Категории