Оптимизация запросов mysql. Оптимизация запросов к базе данных mySQL. Объединение нескольких запросов в один

Рассмотрим некоторый документ. На нем сформированы (отображены на экране в браузере) html-элементы. Они формируются на основании тегов. Вы расставляете теги, их обрабатывает парсер html (есть в любом браузере) и строит html-элементы. И как будут выглядеть эти элементы, определяет CSS.

У всех html-элементов есть 4 области: область внешних отступов, рамка, внутренние отступы и содержание элемента. Для чего они нужны?

• Внешние отступы (margin) - нужны, чтобы регулировать взаимодействие этого элемента с границами других элементов.
• Внутренние отступы (padding) - это расстояние от рамки до содержания данного элемента.
• Содержание самого элемента. Тут и так понятно. Если есть вложенные элементы, то они в этой области и находятся.
• Рамка (border). Очерчивает границы элемента. Схематично 4 области можно представить так:

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

Внешние отступы

Это когда наш элемент взаимодействует с границами контейнера и с границами соседних элементов (те, которые в коде расположены рядом с ним). Основные правила:

• margin-top: auto|величина|% - отступ сверху.
• margin-right: auto|величина|% - отступ справа.
• margin-bottom: auto|величина|% - отступ снизу.
• margin-left: auto|величина|% - отступ слева.
• margin: margin-top margin-right margin-bottom margin-left – сборное правило.

Значение “auto” - значит самостоятельный разбор браузером этих величин. Размер отступа можно указать в величинах: em, ex, px. Проценты (%) считаются от ширины контейнера (от области контента родительского элемента). Если указать отрицательное значение, то границы элементов внутри родительского контейнера наедут друг на друга.

Правило “margin” - сборное, здесь указываются значения всех 4-х отступов (начиная сверху и по часовой стрелке). Примеры:

Margin: 10px 20px 20px 30px; margin: 10px 20px 30px; - отступ слева справа одинаков margin: 10px 20px; - отступ сверху- снизу и слева- справа одинаков margin: 10px; - все отступы одинаковы

Внутренние отступы

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

• padding-top: величина|%.
• padding-right: величина|%.
• padding-bottom: величина|%.
• padding-left: величина|%.
• padding: padding-top padding-right padding-bottom padding-left - сборное правило. По аналогии с “margin”.

Рамки в CSS

У всех рамок есть следующие характеристики:

• Толщина - border-width: величина (thin|medium|thick). По умолчанию - medium.
• Цвет - border-color: цвет. По умолчанию - цвет шрифта в этом элементе.
• Тип - border-style: none|dotted|dashed|solid|double|groove|ridge|inset|outset. Типы: нет рамки | точками | пунктиром | сплошная | двойная | имитация рельефа.

Сами правила рамки:

• border-top- (width|color|style).
• border-right- (width|color|style).
• border-bottom- (width|color|style).
• border-left- (width|color|style).
• border: border-width border-style border-color. Сборное правило.

Свойство CSS border слажит для создания границы объекта, а именно за толщину рамки, за ее цвет и стиль. Это свойство широко используется в HTML. Можно создавать различные эффекты для лучшего восприятия контента на странице. Например, оформить сайдбар, шапку сайта, меню и т.п.

1. Синтаксис CSS border

border : border-width border-style border-color | inherit ;

• border-width - толщина рамки. Можно задавать в пикселях (px) или воспользоваться стандартными значениями thin, medium, thick (они отличаются только шириной в пикселях)
• border-style - стиль выводимой рамки. Может принимать следующие значения
  • none или hidden - отменяет границу
  • dotted - рамка из точек
  • dashed - рамка из тире
  • solid - простая линия (применяется чаще всего)
  • double - двойная рамка
  • groove - рифленая 3D граница
  • ridge , inset , outset - различные 3D эффекты рамки
  • inherit - применяется значение родительского элемента
• border-color - цвет рамки. Можно задавать с помощью конкретного названия цвета или в формате RGB (см. названия html цветов для сайта)

Примечание

Значения в свойстве CSS border можно задавать в любой последовательности. Чаще всего используют последовательность "толщина стиль цвет".

2. Примеры с различными границами рамок CSS border

2.1. Пример. Разные стили оформления границы рамки border-style

border-style: dashed

border-style: dashed

border-style: solid

border-style: double

border-style: groove

border-style: ridge

border-style: inset

border-style: outset

Четыре разных рамки

border-style: dotted

border-style: dashed

border-style: solid

border-style: double

border-style: groove

border-style: ridge

border-style: inset

border-style: outset

Четыре разных рамки

2.2. Пример. Изменения цвета рамки при наведении курсора мыши

Этот пример очень простой, но интересный. Он показывает, как можно использовать псевдокласс :hover и рамку CSS border для создания простых эффектов (например, для меню).

При наведении курсора мыши на блок цвет рамки изменится

Вот как это выглядит на странице:

2.3. Пример. Как сделать прозрачную рамку border

Рамку можно сделать прозрачной. Этот эффект редко, но иногда может быть очень полезен для веб-дизайнеров. Для задания прозрачности надо воспользоваться заданием цвета в виде RGBA (R, G, B, P) , где последним параметром задается прозрачность (вещественное число от 0.0 до 1.0)

Вот как это выглядит на странице:

3. Толщина границы: свойство border-width

Задает толщину линии. Ранее мы задавали ее в едином описании border.

Синтаксис CSS border-width

border-width : thin | medium | thick | значение ;

• thin - тонкая толщина линии
• medium - средняя толщина линии
• thick - толстая толщина линии

Ниже приведены несколько примеров. Самым необычным будет - это разная толщина границы у каждой стороны.

border-width: thin

border-width: medium

border-width: thick

Разная толщина у границ

Вот как это выглядит на странице:

border-width: thin

border-width: medium

border-width: thick

Разная толщина у границ

4. Как сделать рамку border только с одного края (границы)

У свойства CSS border есть производные свойства для задания односторонних границ у элемента:

• border-top - для задания рамки сверху (верхняя граница)
• border-bottom - для задания рамки снизу (нижняя граница)
• border-right - для задания рамки справа (правая граница)
• border-left - для задания рамки слева (левая граница)

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

Также есть свойства

• border-top-color - задание цвета верхний границы
• border-top-style - задание стиля верхней границы
• border-top-width - задание толщины верхней границы
• и т.д. для каждого направления

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

/* Описание двух одинаковых стилей: */

4.1. Пример. Красивая рамка для выделения цитат

Пример рамки для цитаты

Вот как это выглядит на странице:

Пример рамки для цитаты

Примечание
Можно задать отдельную границу для каждой из сторон.

5. Как сделать несколько границ border у элемента html

Иногда требуется сделать несколько границ. Приведем пример

5.1. Первый вариант с несколькими границами

Вот как это выглядит на странице:

Есть второй способ через наложение теней.

5.2. Наложение теней для создания нескольких границ

Вот как это выглядит на странице:

6. Скругление углов у границ (border-radius)

Для создания красивых рамок используют свойство CSS border-radius (доступно только в CSS3). С помощью него можно делать скругления углов, что создает совсем другой вид. Например

7. Вдавленная линия CSS

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

---

Вот как это выглядит на странице:

Для обращения к border из JavaScript нужно писать следующую конструкцию:

document.getElementById("elementID").style.border ="VALUE "

В этой главе:

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

• Margin (внешний отступ) - пустое пространство (поле), которое отделяет один элемент от другого. Самый простой пример внешнего отступа - промежуток между абзацами, идущими друг за другом. Область внешнего отступа прозрачна и не может иметь свой цвет и других эффектов оформления.
• Border (рамка элемента) - граница с любой стороны элемента. Рамка может быть установлена как со всех сторон элемента, так и с одной стороны. Используя рамку можно просто красиво оформить элемент или визуально отделить содержимое текущего элемента от других элементов страницы.
• Padding (внутренний отступ) - пустое пространство между содержимым элемента и его рамкой. Область внутреннего отступа прозрачна и не может иметь свой цвет или другие эффекты оформления.
• У большинства элементов есть область содержимого, в которой располагается все содержимое элемента (текст, вставляемые изображения или другие элементы).

Рамка

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

---

(горизонтальная линия), выступая в качестве разделителя.

Каждая рамка имеет три свойства, которыми мы можем управлять с помощью CSS: ширина, стиль и цвет, рассмотрим с помощью каких свойств можно их задавать и изменять:

Вместо указания всех трех свойств, можно указать всего одно - свойство border , которое позволяет одновременно указать ширину, стиль и цвет для рамки:

Первый абзац.

Второй абзац.

Третий абзац.

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

• border-top - верхняя рамка
• border-left - левая рамка
• border-right - правая рамка
• border-bottom - нижняя рамка

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

Внешний и внутренний отступы

Внутренний отступ - пустое пространство между содержимым элемента и его рамкой (если она установлена). Для добавления и управления шириной внутренних отступов со всех четырех сторон элемента используется свойство padding .

Внешний отступ - пустое пространство, отделяющее элемент от других элементов или краев окна браузера. Для добавления и управления шириной внешних отступов со всех четырех сторон элемента используется свойство margin .

Свойства padding и margin могут принимать от одного до четырех значений:

Где значения устанавливаются по часовой стрелке, начиная с верхнего:

Если у свойств указать всего одно значение, то отступы со всех сторон будут одинаковой ширины.

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

Обычный абзац.

Для отступов, так же как и для рамок, есть свойства позволяющие управлять размером отступа с каждой стороны отдельно. Внутренние отступы регулируются свойствами: padding-top , padding-right , padding-bottom и padding-left . Внешние отступы регулируются свойствами: margin-top , margin-right , margin-bottom и margin-left .

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

Ширина и высота элемента

По умолчанию для блочных элементов используется автоширина. Это означает, что элемент будет растянут ровно на столько, сколько есть свободного места. Высота блочных элементов по умолчанию устанавливается автоматически, т.е. браузер растягивает область содержимого в вертикальном направлении так, чтобы отобразилось все содержимое. Чтобы установить собственные размеры для области содержимого элемента, вы можете использовать свойства width и height.

Рамки - это такие линии, которые окружают элемент (содержащийся в нём контент и отступы вокруг него). Пример, с которым мы уже сталкивались - рамки ячеек внутри таблицы.

CSS предусматривает два типа рамок: внутренние границы и внешние линии. Свойства CSS, отвечающие за оформление рамок, начинаются со слова «border», которое как раз и можно перевести как «Рамка», «Граница». Наличие и формат внешнего контура задаются свойствами, начинающимися словом «outline». Outline, в отличие от border, не влияет на ширину и положение обрамляемого блока. Кроме того, его нельзя установить только с одной стороны, как border - только со всех сторон сразу.

Сначала поговорим об оформлении border, потом перейдём к outline .

border-width

Задаёт ширину границ. Понятно, что по умолчанию элемент окружён рамками с четырёх сторон. Свойство позволяет задать ширину границ как для всех сторон одинаковую, так и разную для каждой стороны. В зависимости от того, какую ширину каким границам нужно присвоить, в правиле можно указать от одного до четырёх значений.

Задавать ширину можно как с помощью привычных пикселей, процентов и других единиц длины CSS, так и зарезервированными словами thin (2px), medium (4px) и thick (6px).

Border-width: 16px 12px 4px 8px;

border-style

Определяет стиль рамки. Обратите внимание: если не задать это правило, но указать свойство border-width , то рамок вообще не будет, так что если хотите видимые границы, обязательно укажите border-style .

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

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

border-style: solid double dotted none

border-color

Работает так же, как и предыдущие свойства, но отвечает за цвет границ. Ему тоже можно задавать от одного до четырёх значений, и результат вы уже знаете. Если правило не установить, рамки будут иметь цвет текста текущего элемента или, если не указан и он, цвет текста элемента-родителя.

Border-color: #C85EFA;

border

Упрощает запись и экономит код, позволяя установить все перечисленные свойства для границ со всех сторон элемента одной строкой:

P { border: 2px solid green; }

Для настройки разных правил рамкам с разных сторон можно использовать следующие значения:

• border-top - верхняя граница.
• border-right - правая.
• border-bottom - нижняя.
• border-left - левая.

P { border-left: 6px dotted yellow; }

outline-width

То же самое, что и border-width , только для внешней, а не внутренней рамки. Задаёт толщину контура в тех же значениях, что и border-width . Кроме толщины обрамления элемента нужно указать его стиль, иначе контура не будет.

outline-style

Значения свойства дублируют значения border-style . Правило задаёт стиль внешнего контура.

Как оптимизировать MySQL запросы?

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

Почему MySQL?

Сегодня идет много разговоров о Dig Data и других новых технологиях. NoSQL и облачные решения это супер, но много популярного софта (такого как WordPress, phpBB, Drupal) до сих пор работает на MySQL. Миграция на новейшие решения может вылиться не только в изменении конфигурации на серверах. К тому же, эффективность MySQL до сих пор на уровне, особенно версия Percona.

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

Хорошее понимание SQL это важнейший инструмент для веб-разработчика, именно он позволит эффективно оптимизировать и использовать реляционные базы данных. В этой статье мы сфокусируемся на популярной открытой базе данных, часто используется в связке с PHP, и это MySQL.

Для кого эта статья?

Для веб-разработчиков, архитекторов и разработчиков баз данных и системных администраторов, хорошо знакомых с MySQL. Если раньше вы не использовали MySQL, эта статья может не принести вам пользы, но я все равно буду стараться быть как можно более информативным и полезным даже для новичков в MySQL.

Сначала бэкап

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

Делать бэкапы MySQL просто, используя утилиту mysqldump:

$ mysqldump myTab > myTab-backup.sql Вы можете узнать больше о mysqldump .

Что делает запрос медленным?

Вот общий список факторов, влияющих на скорость выполнения запросов и нагрузки сервера:

• индексы таблиц;
• условие WHERE(и использования внутренних функций MySQL, например, таких как IF или DATE);
• сортировка по ORDER BY;
• частое повторение одинаковых запросов;
• тип механизма хранения данных (InnoDB, MyISAM, Memory, Blackhole);
• не использование версии Percona;
• конфигурации сервера (my.cnf / my.ini);
• большие выдачи данных (более 1000 строк);
• нестойкое соединение;
• распределенная или кластерная конфигурация;
• слабое проектирование таблиц.

Далее мы обратимся ко всем этим проблемам. Также, установите Percona , если вы еще не используете эту встроенную замену стандартному MySQL - это придаст сильное увеличение мощности базы данных.

Что такое индексы?

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

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

Нестойкое соединение?

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

Уменьшаем частое повторение одинаковых запросов

Наиболее быстрый и эффективный способ, который я нашел для этого - это создание хранилища запросов и результатов их выполнения с помощью Memcached или Redis. С Memcache вы можете легко положить в кэш результат выполнения вашего запроса, например, следующим образом:

connect("localhost",11211); $cacheResult = $cache->get("key-name"); if($cacheResult){ //не нуждаемся в запросе $result = $cacheResult; } else { //запускаем ваш запрос $mysqli = mysqli("p:localhost","username","password","table"); //добавляйте p: для договременного хранения $sql = "SELECT * FROM posts LEFT JOIN userInfo using (UID) WHERE posts.post_type = "post" || posts.post_type = "article" ORDER BY column LIMIT 50"; $result = $mysqli->query($sql); $memc->set("key-name", $result->fetch_array(), MEMCACHE_COMPRESSED,86400); } //Пароль $cacheResult в шаблон $template->assign("posts", $cacheResult); ?> Теперь тяжелый запрос, использующий LEFT JOIN, будет выполняться только раз за каждые 86 400 секунд (то есть раз в сутки), что значительно уменьшит нагрузку MySQL сервера, оставив ресурсы для других соединений.

Примечание: Допишите p: в начале аргумента хоста MySQLi для создания постоянного соединения.

Распределенная или кластерная конфигурация

Когда данных становится все больше, и скорость вашего сервиса идет под уклон, паника может овладеть вами. Быстрым решением может стать распределения ресурсов (sharding). Однако я не рекомендую делать это, если вы не обладаете хорошим опытом, поскольку распределение по своей сути делает структуры данных сложнейшими.

Слабое проектирование таблиц

Создание схем баз данных не является сложной работой, если следовать таким золотым правилам, как работа с ограничениями и знание того, что будет эффективным. Например, хранение изображений в ячейках типа BLOB очень смущает - лучше храните путь к файлу в ячейке VARCHAR, это является гораздо лучшим решением.

Обеспечение правильного проектирования для нужного использования является первостепенным в создании вашего приложения. Храните различные данные в различных таблицах (например, категории и статьи) и убедитесь, что отношения к другу (many to one) и один ко многим (one to many) могут быть легко связаны с идентификаторами (ID). Использование FOREIGN KEY в MySQL идеально подходит для хранения каскадных данных в таблицах.

При создании таблицы помните следующее:

• Создавайте эффективные таблицы для решения ваших задач, а не заполняйте таблицы лишними данными и связями.
• Не ожидайте от MySQL выполнения вашей бизнес логики или програмности - данные должны быть готовы к вставке строки вашей скриптовым языком. Например, если вам нужно отсортировать список в случайном порядке, сделайте это в массиве PHP, не используя ORDER BY из арсенала MySQL.
• Используйте индексные типы UNIQUE для уникальных наборов данных и применяйте ON DUPLICATE KEY UPDATE, чтобы хранить дату обновленной, например, для того, чтобы знать, когда строка была в последний раз изменена.
• Используйте тип данных INT для сохранения целых чисел. Если вы не укажете размер типа данных, MySQL сделает это за вас.

Основы оптимизации

Для эффективной оптимизации мы должны применять три подхода к вашему приложению:

1. Анализ (логирование медленных запросов, изучение системы, анализ запросов и проектирование базы данных)
2. Требования к исполнению (сколько пользователей)
3. Ограничения технологий (скорость железа, неправильное использование MySQL)

Анализ может быть сделан несколькими путями. Сначала мы рассмотрим наиболее очевидные способы, чтобы заглянуть под капот вашей MySQL, в котором выполняются запросы. Самый первый инструмент оптимизации в вашем арсенале это EXPLAIN. Если добавить этот оператор перед вашим запросом по SELECT, результат запроса будет таким:

Колонки, вы видите, сохраняют важную информацию о запросе. Колонки, на которые вы должны обратить наибольшее внимание это possible_keys и Extra.

Колонка possible_keys покажет индексы, в которые MySQL имел доступ, чтобы выполнить запрос. Иногда нужно назначить индексы, чтобы запрос выполнялся быстрее. Колонка Extra покажет, были ли использованы дополнительные WHEREили ORDER BY. Наиболее важно обратить внимание, есть ли Using Filesort в выводе.

Что делает Using Filesort, указано в справке MySQL:

MySQL должен выполнить дополнительный проход, чтобы понять, как вернуть строки в отсортированном виде. Это сортировка происходит проходом по всем строкам в соответствии с типом объединения и сохраняет ключ к сортировке и указатель на строку для всех строк, совпадающих с условным выражением WHERE. Ключи сортируются и строки возвращаются в нужном порядке.

Лишний проход замедлит ваше приложение, этого нужно избегать, чего бы это ни стоило. Другой критический результат Extra, который мы должны избегать - это Using temporary. Он говорит о том, что MySQL пришлось создать временную таблицу для выполнения запроса. Очевидно, это ужасное использования MySQL. В таком случае результат запроса должен быть сохранен в Redis или Memcache и не выполняться пользователями лишний раз.

Чтобы избежать проблемы с Using Filesort мы должны увериться, что MySQL использует INDEX. Сейчас указано несколько ключей в possible_keys, из которых можно выбирать, но MySQL может выбрать только один индекс для финального запроса. Также индексы могут быть составлены из нескольких колонок, также вы можете ввести подсказки (хинты) для оптимизатора MySQL, указывая на индексы, что вы создали.

Хинтинг индексов

Оптимизатор MySQL будет использовать статистику, основанную на запросах таблиц, чтобы выбрать лучший индекс для выполнения запроса. Он действует достаточно просто, основываясь на встроенной статистической логике, поэтому имея несколько вариантов, не всегда делает правильный выбор без помощи хинтинга. Чтобы убедиться, что был использован правильный (или неправильный) ключ, воспользуйтесь ключевым словам FORCE INDEX, USE INDEX и IGNORE INDEX в вашем запросе. Вы можете прочитать больше о хинтинге индексов в справке MySQL .

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

В дополнение к EXPLAIN существует ключевое слово DESCRIBE. Вместе с DESCRIBE можно просматривать информацию из таблицы следующим образом:

Добавляем индекс

Для добавления индексов в MySQL надо использовать синтаксис CREATE INDEX. Есть несколько видов индексов. FULLTEXT Применяется для полнотекстового поиска, а UNIQUE - для хранения уникальных данных.

Чтобы добавить индекс в вашу таблицу, используйте следующий синтаксис:

Mysql> CREATE INDEX idx_bookname ON `books` (bookname(10)); Это создаст индекс на таблице books, которая будет использовать первые 10 букв из колонки, которая хранит названия книг и имеет тип varchar. В этом случае, любой поиск с запросом WHERE на название книги с совпадением до 10 символов будет давать такой же результат, как и просмотр всей таблицы от начала до конца.

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

Индексы имеют большое влияние на скорость выполнения запросов. Только назначения главного уникального ключа недостаточно - композитные ключи являются реальной областью применения в настройке MySQL, что иногда требует некоторых A/B проверок с использованием EXPLAIN.

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

Mysql> CREATE INDEX idx_composite ON users (username, active); Как только мы создали ключ на основе колонки username, в котором хранится имя пользователя и колонки active типа ENUM, определяющий, активен ли его аккаунт. Теперь все оптимизировано для запроса, который будет использовать WHERE для поиска валидного имени пользователя с активным аккаунтом (active = 1).

Насколько быстра ваша MySQL?

Включим профилирование, чтобы подробнее рассмотреть MySQL запросы. Это можно сделать, выполнив команду set profiling=1, после чего для просмотра результата надо выполнить show profiles.

Если вы используете PDO, выполните следующий код:

$db->query("set profiling=1"); $db->query("select headline, body, tags from posts"); $rs = $db->query("show profiles"); $db->query("set profiling=0"); // отключить профилирование после выполнения запроса $records = $rs->fetchAll(PDO::FETCH_ASSOC); // получить результаты профилирования $errmsg = $rs->errorInfo(); //Отлавливаем некоторые ошибки здесь То же самое можно сделать с помощью mysqli:

$db = new mysqli($host,$username,$password,$dbname); $db->query("set profiling=1"); $db->query("select headline, body, tags from posts"); if ($result = $db->query("SHOW profiles", MYSQLI_USE_RESULT)) { while ($row = $result->fetch_row()) { var_dump($row); } $result->close(); } if ($result = $db->query("show profile for query 1", MYSQLI_USE_RESULT)) { while ($row = $result->fetch_row()) { var_dump($row); } $result->close(); } $db->query("set profiling=0"); Это вернет вам профилированные данные, содержащие время выполнения запроса во втором элементе ассоциативного массива.

Array(3) { => string(1) "1" => string(10) "0.00024300" => string(17) "select headline, body, tags from posts" } Этот запрос выполнялся 0.00024300 секунд. Это довольно быстро, поэтому не будем беспокоиться. Но когда числа становятся большими, мы должны смотреть глубже. Перейдите к вашему приложению, чтобы потренироваться на рабочем примере. Проверьте константу DEBUG в конфигурации вашей базы данных, а затем начните изучать систему, включив вывод результатов профилирования с помощью функций var_dump или print_r. Так вы сможете переходить со страницы на страницу в вашем приложении, получив удобное профилирование системы.

Полный аудит работы базы вашего сайта

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

Чтобы включить логирование в MySQL 5.1.6 используйте глобальную переменную log_slow_queries, также вы можете отметить файл для логирования с помощью переменной slow_query_log_file. Это можно сделать, выполнив следующий запрос:

Set global log_slow_queries = 1; set global slow_query_log_file = /dev/slow_query.log; Также это можно указать в файлах конфигурации /etc/my.cnf или my.ini вашего сервера.

После внесения изменений не забудьте перезагрузить MySQL сервер необходимой командой, например service mysql restart, если вы используете Linux.

В версиях MySQL после 5.6.1 переменная log_slow_queries обозначена как устаревшая и вместо нее используется slow_query_log. Также для более удобного дебаггинга можно включить вывод в таблице, задав переменной log_output значение TABLE, однако эта функция доступна только с MySQL 5.6.1.

Log_output = TABLE; log_queries_not_using_indexes = 1; long_query_time = 1; Переменная long_query_time определяет количество секунд, после которых выполнение запроса считается медленным. Значение это 10, а минимум это 0. Также можно указать миллисекунды, используя дробь; сейчас я указал одну секунду. И теперь каждый запрос, который будет выполняться дольше 1 секунды, записывается в логи в таблице.

Логирование будет вестись в таблицах mysql.slow_log и mysql.general_log вашей MySQL базы данных. Чтобы выключить логирование, измените log_output на NONE.

Логирование на рабочем сервере

На рабочем сервере, который обслуживает клиентов, лучше применять логирование только на короткий период и для мониторинга нагрузки, чтобы не создавать лишней нагрузки. Если ваш сервис перегружен и необходимо безотлагательное вмешательство, попробуйте выделить проблему, выполнив SHOW PROCESSLIST, или обратитесь к таблице information_schema.PROCESSLIST, выполнив SELECT * FROM information_schema.PROCESSLIST;.

Логирование всех запросов на рабочем сервере может дать вам много информации и стать хорошим средством для исследовательских целей при проверке проекта, однако логи за большие периоды не дадут вам много полезной информации по сравнению с логами за период до 48 часов (старайтесь отслеживать пиковые нагрузки, чтобы иметь шанс лучше исследовать выполнение запросов).

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

Логирование множества запросов

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

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

Горячий и холодный кэш

Количество запросов и нагрузка сервера имеет сильное влияние на исполнение, также может повлиять на время выполнения запросов. При разработке вы должны взять за правило, что выполнение каждого запроса должно быть не более доли миллисекунды (0.0xx или быстрее) на свободном сервере.

Применение Memcache имеет сильный эффект на нагрузку серверов, освободит ресурсы, которые выполняют запросы. Убедитесь, что вы используете Memcached эффективно и протестовали ваше приложение с горячим кэшем (подгруженными данным) и с холодным кэшем.

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

Исправление медленных запросов

Теперь, когда логирование настроено, вы могли найти несколько медленных запросов на вашем сайте. Давайте исправим их! Для примера я покажу несколько распространенных проблем, вы можете встретить и логику их исправления.

Если вы пока не нашли медленного запроса, проверьте настройки long_query_time, если вы пользуетесь этим методом логирования. Иначе, проверив все ваши запросы профилирования (set profiling=1), составьте список запросов, отнимают больше времени, чем доля миллисекунд (0.000x секунд) и начнем из них.

Распространенные проблемы

Вот шесть самых распространенных проблем, которые я находил, оптимизируя MySQL запросы:

ORDER BY и filesort

Предотвращение filesort иногда невозможно из-за выражения ORDER BY. Для оптимизации сохраните результат в Memcache, или выполните сортировку в логике вашего приложения.

Использование ORDER BY вместе с WHERE и LEFT JOIN

ORDER BY очень замедляет выполнение запросов. Если это возможно, старайтесь не использовать ORDER BY. Если же вам необходима сортировка, то используйте сортировку по индексам.

Применение ORDER BY по временным колонками

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

Игнорирование индекса FULLTEXT

Использование LIKE это самый лучший способ сделать полнотекстовый поиск медленным.

Беспричинный выбор большого количества строк

Забыв о LIMIT в вашем запросе можно сильно увеличить время выполнения выборки из базы данных в зависимости от размера таблиц.

Чрезмерное использование JOIN вместо создания композитных таблиц или представления

Когда в одном запросе вы пользуетесь больше чем тремя-четырьмя операторами LEFT JOIN, спросите себя: все ли здесь верно? Продолжайте, если у вас есть на то веская причина, например - запрос используется не часто для вывода в панели администратора, или результат вывода может быть сохранен в кэше. Если же вам нужно выполнять запрос с большим количеством операций объединения таблиц, тогда лучше задуматься о создании композитных таблиц из необходимых столбиков или использовать представления.

Итак

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

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