Что нового
  • Что бы вступить в ряды "Принятый кодер" Вам нужно:
    Написать 10 полезных сообщений или тем и Получить 10 симпатий.
    Для того кто не хочет терять время,может пожертвовать средства для поддержки сервеса, и вступить в ряды VIP на месяц, дополнительная информация в лс.

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

  • Гость, Что бы Вы хотели увидеть на нашем Форуме? Изложить свои идеи и пожелания по улучшению форума Вы можете поделиться с нами здесь. ----> Перейдите сюда
  • Все пользователи не прошедшие проверку электронной почты будут заблокированы. Все вопросы с разблокировкой обращайтесь по адресу электронной почте : info@guardianelinks.com . Не пришло сообщение о проверке или о сбросе также сообщите нам.

PHP Повышение производительности MySQL с помощью индексов и объяснений

Lomanu4

Команда форума
Администратор
Регистрация
1 Мар 2015
Сообщения
1,202
Баллы
155
Методы повышения производительности приложений могут происходить из разных мест, но обычно первое, на что мы обращаем внимание - самое распространенное узкое место - это база данных. Можно ли это улучшить? Как мы можем измерить и понять, что нужно и что можно улучшить?
Одним из очень простых, но очень полезных инструментов является профилирование запросов. Включение профилирования - это простой способ получить более точную оценку времени выполнения запроса. Это двухступенчатый процесс. Во-первых, мы должны включить профилирование. Затем мы вызываем, show profilesчтобы фактически получить время выполнения запроса.
Давайте представим, что в нашей базе данных есть следующая вставка (и давайте предположим, что Пользователь 1 и Галерея 1 уже созданы):
INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Очевидно, что этот объем данных не вызовет никаких проблем, но давайте использовать его для создания простого профиля. Давайте рассмотрим следующий запрос:
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Этот запрос является хорошим примером того, который может стать проблематичным в будущем, если мы получим много записей фотографий.
Чтобы получить точное время выполнения этого запроса, мы использовали бы следующий SQL:
set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;

Результат будет выглядеть следующим образом:
Query_Idпродолжительностьзапрос
10.00016950ПОКАЗАТЬ ПРЕДУПРЕЖДЕНИЯ
20.00039200ВЫБРАТЬ * ИЗ homestead. imagesКАК ГДЕ i.description LIKE \ '% street% \' \ nLIMIT 0, 1000
30.00037600ПОКАЗАТЬ КЛЮЧИ ОТ homestead.images
40.00034625ПОКАЗЫВАЙТЕ БАЗЫ ДАННЫХ, КАК ДОМА
50.00027600ПОКАЗЫВАЙТЕ ТАБЛИЦЫ ИЗ homesteadНРАВИТСЯ \ 'images \'
60.00024950ВЫБРАТЬ * ИЗ homestead. imagesГДЕ 0 = 1
70.00104300ПОКАЗАТЬ ПОЛНЫЕ КОЛОННЫ ИЗ homestead. imagesНРАВИТСЯ \ 'id \'
Как мы видим, команда дает нам время не только для исходного запроса, но и для всех других запросов. Таким образом, мы можем точно профилировать наши запросы.show profiles;
Но как мы можем улучшить их?
Мы можем либо положиться на наши знания SQL и импровизировать, либо мы можем положиться на команду MySQL explainи улучшить производительность наших запросов на основе фактической информации.
Объяснение используется для получения плана выполнения запроса или того, как MySQL будет выполнять наш запрос. Он работает с SELECT, DELETE, INSERT, REPLACE, и UPDATEзаявлением, и отображает информацию от оптимизатора о плане выполнения оператора.

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

, делает очень хорошую работу по описанию , как explainможет помочь нам:
С помощью EXPLAIN вы можете увидеть, куда следует добавлять индексы в таблицы, чтобы оператор выполнялся быстрее, используя индексы для поиска строк. Вы также можете использовать EXPLAIN, чтобы проверить, объединяет ли оптимизатор таблицы в оптимальном порядке.
В качестве примера использования explain, мы будем использовать запрос, чтобы найти пользователя по электронной почте:UserManager.php
SELECT * FROM `homestead`.`users` WHERE email = 'claudio.ribeiro@examplemail.com';

Чтобы использовать explainкоманду, мы просто добавляем ее перед запросами выбора типа:


EXPLAIN SELECT * FROM `homestead`.`users` WHERE email = 'claudio.ribeiro@examplemail.com';

Это результат (прокрутите вправо, чтобы увидеть все):
Я быSELECT_TYPEстолперегородкитипpossible_keysключkey_lenссылкастрокиотфильтрованныйдополнительный
1ПРОСТО«пользователей»ЗНАЧЕНИЕ NULL'Const''UNIQ_1483A5E9E7927C74''UNIQ_1483A5E9E7927C74''182''Const'100,00ЗНАЧЕНИЕ NULL
Эти результаты нелегко понять с первого взгляда, поэтому давайте подробнее рассмотрим каждый из них:
  • id: это просто последовательный идентификатор для каждого из запросов в SELECT.
  • select_type: тип запроса SELECT. Это поле может принимать различные значения, поэтому мы сосредоточимся на наиболее важных:
    • SIMPLE: простой запрос без подзапросов или объединений
    • PRIMARY: выбор находится в самом внешнем запросе в соединении
    • DERIVED: select является частью подзапроса в пределах от
    • SUBQUERY: первый выбор в подзапросе
    • UNION: выбор является вторым или последующим утверждением объединения.
  • Полный список значений, которые могут появиться в select_typeполе, можно найти

    Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

    .
  • table: таблица, на которую указывает строка.
  • type: в этом поле MySQL объединяет используемые таблицы. Это, вероятно, самое важное поле в выводе объяснения. Это может указывать на отсутствующие индексы, а также на то, как запрос должен быть переписан. Возможные значения для этого поля следующие (упорядочены от лучшего типа к худшему):
    • system: таблица имеет ноль или одну строку.
    • const: таблица имеет только одну соответствующую строку, которая проиндексирована. Это самый быстрый тип соединения.
    • eq_ref: все части индекса используются соединением, и индекс либо PRIMARY_KEY, либо UNIQUE NOT NULL.
    • ref: все соответствующие строки столбца индекса считываются для каждой комбинации строк из предыдущей таблицы. Этот тип объединения обычно отображается для индексированных столбцов по сравнению с операторами =или <=>.
    • fulltext: соединение использует таблицу FULLTEXT index.
    • ref_or_null: это то же самое, что и ref, но также содержит строки со значением NULL из столбца.
    • index_merge: соединение использует список индексов для получения результирующего набора. Столбец KEY explainбудет содержать используемые ключи.
    • unique_subquery: подзапрос IN возвращает только один результат из таблицы и использует первичный ключ.
    • range: индекс используется для поиска подходящих строк в определенном диапазоне.
    • index: сканируется все дерево индексов, чтобы найти соответствующие строки.
    • all: вся таблица сканируется, чтобы найти подходящие строки для объединения. Это наихудший тип объединения и часто указывает на отсутствие соответствующих индексов в таблице.
  • possible_keys: показывает ключи, которые могут быть использованы MySQL для поиска строк в таблице. Эти ключи могут или не могут быть использованы на практике.
  • keys: указывает фактический индекс, используемый MySQL. MySQL всегда ищет оптимальный ключ, который можно использовать для запроса. При объединении многих таблиц он может определить некоторые другие ключи, которые не перечислены в списке, possible_keysно являются более оптимальными.
  • key_len: указывает длину индекса, который оптимизатор запросов выбрал для использования.
  • ref: Показывает столбцы или константы, которые сравниваются с индексом, указанным в ключевом столбце.
  • rows: перечисляет количество записей, которые были проверены, чтобы произвести вывод. Это очень важный показатель; чем меньше проверенных записей, тем лучше.
  • Extra: содержит дополнительную информацию. Значения, такие как Using filesortили в этом столбце, могут указывать на проблемный запрос.Using temporary

Полная документация по explainвыходному формату может быть найдена

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

.
Возвращаясь к нашему простому запросу: это SIMPLE тип выбора с константным типом соединения. Это лучший случай запроса, который мы можем иметь. Но что происходит, когда нам нужны более крупные и сложные запросы?
Возвращаясь к нашей схеме приложения, мы можем получить все изображения галереи. Мы также могли бы хотеть иметь только фотографии, которые содержат слово «кошка» в описании. Это определенно тот случай, который мы могли бы найти в требованиях проекта. Давайте посмотрим на запрос:
SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`users` AS users
LEFT JOIN `homestead`.`galleries` AS gal ON users.id = gal.user_id
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

В этом более сложном случае у нас должно быть больше информации для анализа explain:
EXPLAIN SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`users` AS users
LEFT JOIN `homestead`.`galleries` AS gal ON users.id = gal.user_id
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

Это дает следующие результаты (прокрутите вправо, чтобы увидеть все ячейки):
Я быSELECT_TYPEстолперегородкитипpossible_keysключkey_lenссылкастрокиотфильтрованныйдополнительный
1ПРОСТО«пользователей»ЗНАЧЕНИЕ NULL'показатель''PRIMARY, UNIQ_1483A5E9BF396750''UNIQ_1483A5E9BF396750''108'ЗНАЧЕНИЕ NULL100,00«Использование индекса»
1ПРОСТО«Гал»ЗНАЧЕНИЕ NULL«Ссылка»'PRIMARY, UNIQ_F70E6EB7BF396750, IDX_F70E6EB7A76ED395''UNIQ_1483A5E9BF396750''108''Homestead.users.id'100,00ЗНАЧЕНИЕ NULL
1ПРОСТО'IMG'ЗНАЧЕНИЕ NULL«Ссылка»'IDX_E01FBE6A4E7AF8F''IDX_E01FBE6A4E7AF8F''109''Homestead.gal.id''25 .00'«Использование где»
Давайте внимательнее посмотрим, что мы можем улучшить в нашем запросе.
Как мы видели ранее, основными столбцами, на которые мы должны обратить внимание вначале, являются typeстолбец и rowsстолбцы. Цель должна получить лучшее значение в typeстолбце и уменьшить как можно больше в rowsстолбце.
Наш результат по первому запросу - indexэто совсем не хороший результат. Это означает, что мы, вероятно, можем улучшить его.
Глядя на наш запрос, есть два пути к нему. Во-первых, Usersтаблица не используется. Мы либо расширяем запрос, чтобы убедиться, что мы нацеливаемся на пользователей, либо мы должны полностью удалить usersчасть запроса. Это только добавляет сложности и времени к нашей общей производительности.
SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`galleries` AS gal
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

Так что теперь у нас точно такой же результат. Давайте посмотрим на explain:
Я быSELECT_TYPEстолперегородкитипpossible_keysключkey_lenссылкастрокиотфильтрованныйдополнительный
1ПРОСТО«Гал»ЗНАЧЕНИЕ NULL'ВСЕ''PRIMARY, UNIQ_1483A5E9BF396750'ЗНАЧЕНИЕ NULLЗНАЧЕНИЕ NULLЗНАЧЕНИЕ NULL100,00ЗНАЧЕНИЕ NULL
1ПРОСТО'IMG'ЗНАЧЕНИЕ NULL«Ссылка»'IDX_E01FBE6A4E7AF8F''IDX_E01FBE6A4E7AF8F''109''Homestead.gal.id''25 .00'«Использование где»
Мы остались с ALLтипом. Хотя это ALLможет быть худший тип соединения, возможны также случаи, когда это единственный вариант. В соответствии с нашими требованиями, мы хотим, чтобы все изображения галереи, поэтому нам нужно просмотреть всю таблицу галерей. Хотя индексы действительно хороши при поиске конкретной информации в таблице, они не могут помочь нам, когда нам нужна вся информация в ней. Когда у нас есть такой случай, мы должны прибегнуть к другому методу, например, кешированию.
Последнее, что мы можем сделать, поскольку мы имеем дело с a LIKE, - это добавить индекс FULLTEXT в наше поле описания. Таким образом, мы могли бы изменить LIKEк и повысить производительность. Подробнее о полнотекстовых индексах

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

.match()
Есть также две очень интересные случаи , мы должны смотреть по адресу: newestи relatedфункциональность в нашем приложении. Они относятся к галереям и касаются некоторых угловых случаев, о которых нам следует знать:
EXPLAIN SELECT * FROM `homestead`.`galleries` AS gal
LEFT JOIN `homestead`.`users` AS u ON u.id = gal.user_id
WHERE u.id = 1
ORDER BY gal.created_at DESC
LIMIT 5;

Выше для связанных галерей.
EXPLAIN SELECT * FROM `homestead`.`galleries` AS gal
ORDER BY gal.created_at DESC
LIMIT 5;

Выше для новейших галерей.
На первый взгляд, эти запросы должны быть быстрыми, потому что они используют LIMIT. И это относится к большинству запросов с использованием LIMIT. К сожалению для нас и нашего приложения, эти запросы также используются . Поскольку нам нужно упорядочить все результаты, прежде чем ограничивать запрос, мы теряем преимущества использования .ORDER BYLIMIT
Поскольку мы знаем, что это может быть сложно, давайте применим наши верные .ORDER BYexplain
Я быSELECT_TYPEстолперегородкитипpossible_keysключkey_lenссылкастрокиотфильтрованныйдополнительный
1ПРОСТО«Гал»ЗНАЧЕНИЕ NULL'ВСЕ''IDX_F70E6EB7A76ED395'ЗНАЧЕНИЕ NULLЗНАЧЕНИЕ NULLЗНАЧЕНИЕ NULL100,00«Используя где; Использование filesort '
1ПРОСТО«И»ЗНАЧЕНИЕ NULL'Eq_ref''PRIMARY, UNIQ_1483A5E9BF396750'«PRIMARY'108''Homestead.gal.id'«100,00»ЗНАЧЕНИЕ NULL
И,
Я быSELECT_TYPEстолперегородкитипpossible_keysключkey_lenссылкастрокиотфильтрованныйдополнительный
1ПРОСТО«Гал»ЗНАЧЕНИЕ NULL'ВСЕ'ЗНАЧЕНИЕ NULLЗНАЧЕНИЕ NULLЗНАЧЕНИЕ NULLЗНАЧЕНИЕ NULL100,00«Использование файловой сортировки»
Как мы видим, у нас наихудший случай типа соединения: ALLдля обоих наших запросов.
Исторически реализация MySQL , особенно вместе с ней , часто являлась причиной проблем с производительностью MySQL. Эта комбинация также используется в большинстве интерактивных приложений с большими наборами данных. Функциональные возможности, такие как недавно зарегистрированные пользователи и топ-теги, обычно используют эту комбинацию.ORDER BYLIMIT
Поскольку это общая проблема, существует также небольшой список общих решений, которые мы должны применять для решения проблем с производительностью.
  • Убедитесь, что мы используем индексы . В нашем случае, created_atэто отличный кандидат, так как это поле, по которому мы упорядочиваем. Таким образом, мы выполняем и то и другое без сканирования и сортировки полного набора результатов.ORDER BYLIMIT
  • Сортировать по столбцу в ведущей таблице . Обычно, если поле идет по полю из таблицы, которая не является первой в порядке объединения, индекс не может быть использован.ORDER BY
  • Не сортируйте по выражениям . Выражения и функции не позволяют использовать индексы .ORDER BY
  • Остерегайтесь большого LIMITзначения . Большие LIMITзначения заставят сортировать большее количество строк. Это влияет на производительность.ORDER BY
Вот некоторые из мер, которые мы должны предпринять, когда у нас есть оба, LIMITи чтобы минимизировать проблемы с производительностью.ORDER BY
Вывод
Как мы видим, это explainможет быть очень полезно для выявления проблем в наших запросах на ранней стадии. Есть много проблем, которые мы замечаем только тогда, когда наши приложения находятся в производстве и имеют большие объемы данных или большое количество посетителей, попадающих в базу данных. Если эти вещи можно будет обнаружить на ранних
 
Вверх