Оптимизация всех таблиц БД MySQL


Содержание

OPTIMIZE TABLE для всех таблиц в базе данных

19 мая 2013, 17:51

Сначала вкратце, зачем нужна команда OPTIMIZE TABLE. Если прочитать описание, то увидим, что данная операция реорганизует физическое пространство занимаемое таблицей и индексами, что обеспечивает уменьшение занимаемого места и ускорение операций ввода/вывода. Конкретно производимые действия отличаются для разных движков таблиц (поддерживаются MyISAM, ARCHIVE и InniDB), подробнее об этом можно почитать в документации MySQL.

Возникает естественный вопрос: как автоматизировать данную операцию? Если для конкретной таблицы это легко сделать SQL-запросом OPTIMIZE TABLE table_name, то как это сделать сразу для всех таблиц в базе? Легко и непринуждённо, с помощью идущей в поставке сервера утилиты mysqlcheck.

Оптимизация всех таблиц в базе данных.

Оптимизация всех баз данных сразу.

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

Оптимизация базы данных MySQL на Joomla

На нормальном сайте надо оптимизировать всё – контент, скорость, структуру, внутренние ссылки и базу данных. Сегодня речь пойдёт о MySQL, точнее об очистке структуры и таблиц базы данных на сайте Joomla.

Зачем нужна оптимизация БД

Для работы сайта на Joomla нужны две составляющие – файловая система и база данных, к которой система управления (CMS) формирует запросы при каждом действие на интернет-ресурсе. Таблицы MySQL создаются для контента, модулей, категорий, плагинов и т д., соответственно, со временем объём базы данных увеличивается и она занимает больше места на диске сервера. Кроме этого, при росте MySQL растёт и количество запросов к ней, что увеличивает без необходимости нагрузку на сервер и может привести к милой просьбе хостера перейти на иной тариф.

Структура БД

Оптимизация структуры – это удаление ненужных таблиц в базе данных. Тут возникает логичный вопрос, а откуда эти таблицы берутся, если вы всё ненужное удаляете через административную панель? Всё, да не всё, кое-какие таблицы остаются, а структура БД разрастается, как плесень на овощной базе.

Для очистки структуры надо просто удалить ненужные таблицы. Для этого зайдите на localhost и перейдите в MySQL сайта. Далее нажимаете сверху кнопку «Структура» и получаете список всех таблиц БД. Теперь дело техники – смотрите, какие таблицы устарели, выделяете их и нажимаете внизу вид действия «удалить».

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

Таблицы MySQL

Оптимизировать таблицы MySQL проще, для этого есть системное решение на любом хостинге. Лично я без разбору ставлю галочки на всех таблицах и нажимаю внизу, во всплывающем окне, «оптимизировать».

На одном из сайтов после оптимизации таблиц размер БД сократился с 11,6 до 7,8 мб. Вроде бы немного, но тут двояковыпуклая выгода – сокращается не только объём MySQL, но и параллельно уменьшается число запросов к серверу при работе сайта.

Если сайт вырос до размеров Эвереста, то БД однозначно будет загажена и ещё вопрос, что больше даёт нагрузки на сервер – работающие таблицы MySQL или мусор. Выделить же таблицы и оптимизировать их: просто – раз, быстро – два.

Два важных момента – оптимизация структуры MySQL – это удаление ненужных таблиц, а оптимизация самих таблиц – это очистка их от мусорных записей.

Оптимизация запросов и таблиц MySQL и MariaDB

MySQL и MariaDB – это популярные системы управления базами данных (СУБД), использующие язык запросов SQL для ввода и извлечения данных.

В целом запросы SQL – простые команды, которые легко запомнить; однако не все запросы и функции БД работают с одинаковой продуктивностью. Этот вопрос становится все более важным по мере роста количества хранимой в БД информации.

Данное руководство охватывает некоторые простые способы оптимизации запросов MySQL и MariaDB.

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

Общая структура таблицы

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

При этом нужно задать себе несколько вопросов.

Как в основном будет использоваться данная таблица?

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

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

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

Какие типы данных будут храниться в таблице?

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

К примеру, если в БД предварительно установлен лимит валидных записей для определённого поля значений, используйте тип enum вместо varchar. Этот тип данных является более компактным и, таким образом, быстро извлекается в случае запроса.

Например, если в системе есть несколько различных видов пользователей, можно создать отдельный столбец, который обрабатывает данные enum с возможными значениями: admin, moderator, poweruser, user и т.п.

Какие столбцы будут чаще запрашиваться?

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

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

CREATE TABLE example_table (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
address VARCHAR(150),
username VARCHAR(16),
PRIMARY KEY (id),
INDEX (username)
);

Такой синтаксис будет полезен, если пользователи часто запрашивают информацию по имени пользователя. Это создаст следующую таблицу:

explain example_table;
+———-+—————+——+——+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+—————+——+——+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| address | varchar(150) | YES | | NULL | |
| username | varchar(16) | YES | MUL | NULL | |
+———-+—————+——+——+———+—————-+
4 rows in set (0.00 sec)

Как видите, для таблицы создано два индекса. Первый – это первичный ключ (в данном случае idfield). Второй был добавлен для поля username. Это позволит ускорить запросы, которые используют это поле.

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

CREATE INDEX index_name ON table_name(column_name);

Также можно использовать синтаксис:

ALTER TABLE table_name ADD INDEX ( column_name );

Функция explain

Если запросы к БД довольно предсказуемы, проанализируйте запросы и создайте индексы для всех возможных столбцов. Для этого можно использовать функцию explain.

Для примера импортируйте образец БД MySQL:

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjvf employees_db-full-1.0.6.tar.bz2
cd employees_db
mysql -u root -p -t

Откройте сессию MySQL, чтобы создать несколько запросов:

mysql -u root -p
use employees;

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

SET GLOBAL query_cache_size = 0;
SHOW VARIABLES LIKE «query_cache_size»;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| query_cache_size | 0 |
+——————+——-+
1 row in set (0.00 sec)

Запустите простой запрос большого объёма данных:

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+———-+
| count(*) |
+———-+
| 588322 |
+———-+
1 row in set (0.60 sec)

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

Обратите внимание, поле key имеет значение NULL. Значит, для этого поля не используется индекс.

Добавьте индекс для key и снова запустите запрос:

ALTER TABLE salaries ADD INDEX ( salary );
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+———-+
| count(*) |
+———-+
| 588322 |
+———-+
1 row in set (0.14 sec)

Как видите, это значительно улучшает обработку запросов.

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

К примеру, у вас есть таблица по имени cheeses, и есть таблица ingredients. Эти таблицы можно объединить при помощи поля ingredient_id. Чтобы ускорить слияние таблиц, нужно создать индексы для этих полей.

Оптимизация запросов

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

В зависимости от цели использования запросов вам может понадобиться ограниченное количество результатов. К примеру, чтобы выяснить, зарабатывает ли кто-нибудь в компании меньше, чем $ 40000, можно использовать:

SELECT * FROM SALARIES WHERE salary

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

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

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

SELECT * FROM employees WHERE last_name like ‘Bre%’ OR first_name like ‘Bre%’;

Эту операцию можно ускорить, если создать отдельный запрос для имени, отдельный – для фамилии, а затем объединить выход. Это можно сделать с помощью оператора union:

SELECT * FROM employees WHERE last_name like ‘Bre%’ UNION SELECT * FROM employees WHERE first_name like ‘Bre%’;

Иногда MySQL использует оператор union автоматически (как в приведённом выше примере). Чтобы узнать, использует ли MySQL этот оператор, обратитесь к функции explain.

Заключение

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

Эти СУБД имеют подробную документацию, в которой также можно найти советы по оптимизации и тонкой настройке запросов. Специфика такой отладки в значительной степени зависит от того, какую функциональность нужно оптимизировать. Определившись с требованиями и выяснив, какие операции будут часто выполняться, вы можете подогнать параметры этих запросов.

Оптимизация всех таблиц БД MySQL

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

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

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

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

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

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

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

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

Вы можете узнать больше о 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 вы можете легко положить в кэш результат выполнения вашего запроса, например, следующим образом:

Илон Маск рекомендует:  Оглавление. Оптимизация, продвижение и раскрутка сайта своими руками.

Теперь тяжелый запрос, использующий 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 — для хранения уникальных данных.

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

Это создаст индекс на таблице books, которая будет использовать первые 10 букв из колонки, которая хранит названия книг и имеет тип varchar. В этом случае, любой поиск с запросом WHERE на название книги с совпадением до 10 символов будет давать такой же результат, как и просмотр всей таблицы от начала до конца.

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

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

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

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

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

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

То же самое можно сделать с помощью mysqli:

Это вернет вам профилированные данные, содержащие время выполнения запроса во втором элементе ассоциативного массива.

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

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

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

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

Также это можно указать в файлах конфигурации /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.

Переменная 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.

Проверка, восстановление и оптимизация баз MySQL

Программа mysqlcheck используется для проверки целостности (-c, -m, -C), восстановления (-r), анализа (-a) или оптимизации (-o) таблиц базы данных MySQL. Некоторые опции (например -e и -q) могут использоваться одновременно.

Не все опции поддерживаются различными движками MySQL. Опции -c, -r, -a и -o взаимоисключаемые, что означает, что будет применена последняя указанная опция.

Если не указано ничего, то будет применена опция -c. Альтернативами (синонимами) являются:

mysqlrepair: опция по умолчанию -r
mysqlanalyze: опция по умолчанию -a
mysqloptimize: опция по умолчанию -o

Проверить все базы данных. Аналогична опции –databases, если указать все базы данных.

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

Анализировать данные таблицы.

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

Выводит информацию журнала отладки. Часто используется следующий набор параметров: ‘d:t:o,filename’

Директория, где находятся установки символов.

Проверить таблицу на наличие ошибок.

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

Использовать сжатие данных в протоколе сервер/клиент.

Вывести данную вспомогательную информацию и выйти из программы.


Проверить несколько баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных.

Установить набор символов по умолчанию.

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

Продолжать даже при получении ошибки SQL.

При использовании данного параметра совместно с CHECK TABLE можно быть уверенным в целостности таблицы. Если же использовать этот параметр с REPAIR TABLE, запустится расширенное восстановление таблицы.

Хост базы данных.

Быстрее, чем –extended-check, но находит только 99,99 процентов всех ошибок.

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

Номер порта, используемого для подключения по TCP/IP.

Для указания протокола соединения, который надлежит использовать.

При использовании данной опции совместно с CHECK TABLE предотвращается сканирование строк для корректировки неправильных связей. Это наиболее быстрый метод проверки. Если же использовать этот параметр с REPAIR TABLE, программа попытается восстановить только систему индексов. Это наиболее быстрый метод восстановления таблицы.

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

Выводить только сообщения об ошибках.

Файл сокета, используемый для подсоединения.

Илон Маск рекомендует:  Что такое код gmp_pow

Перекрывает опцию –databases (-B).

Имя пользователя MySQL, если этот пользователь в данное время не является активным.

Вывести информацию о различных этапах.

Вывести информацию о версии и выйти из программы.

Оптимизация баз данных MySQL

Оптимизацию работы с БД можно разделить на 3 типа:

  • оптимизация запросов;
  • оптимизация структуры;
  • оптимизация сервера.

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

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

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

Запросами, которые чаще всего поддаются оптимизации, являются запросы на выборку SELECT.

SELECT

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

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

Основная ошибка начинающих — это отсутствие индексов на нужных полях или создание оных на ненужных полях.

Если вы делаете простую выборку наподобие: то вам нужно проставить индекс на поле field1.

Если вы используете в выборке условие по двум полям: то вам нужно создать составной индекс на поля field1, field2:

Если вы используете соединение 2 или более таблиц: или в более общем виде: То вам следует создать индексы по полям, по которым будут присоединятся таблицы.

В данном случае это поля b. >c. >c,b,a, то нужно будет проставить индексы по полям: b.c_ >a.b_id. При связывании с помощью LEFT JOIN таблица, которая идет в запросе слева, всегда будет просматриваться первой.

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

Про временные таблицы также есть статья в справочном руководстве по MySQL.

Для подсчета количества строк используйте функцию COUNT(*), c указанием «звездочки» в качестве аргумента, в этом случае прирост производительности будет выше в разы.

UPDATE, INSERT

Условия в запросах на обновления оптимизируются так же, как и в случае с выборками.

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

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

В справочном руководстве также есть дополнительная информация по поводу оптимизации.

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

Таблица генов человеческого генома невелика, всего 60434 записей:

Таблица повторов человеческого генома, уже хуже — более 5 с половиной миллионов записей:

Это — две служебных таблицы. Из них нам, по большому счёту, важны лишь chr — название хромосомы, left и right — левая и правая координата целого гена/повтора или его части (частей может быть несколько, в этом случае одному name соответствует несколько наборов ) и name — название гена/повтора.

Теперь данные экспериментов на тканях онкологических больных. Формат таблицы таков:

он одинаков для каждого эксперимента. Каждая запись описывает паттерн ДНК, принадлежащий хромосоме chr, с координатами left и right, количеством штук count. Количество записей разное, от 4 до 7 с половиной миллиона на эксперимент. Каждая запись — уникальный набор по координатам

И финальная таблица, в которую нужно собрать данные 4х экспериментов:

Фактически, всё просто: нужно найти только те паттерны, которые левым краем попадают на ген, а правым — на повтор, посчитать их количество и вывести в сводную таблицу. С запросом вроде бы проблем не возникло, 4 раза повторяю такой запрос, меняя лишь count_k51 на count_p51 и саму таблицу-источник :

В первый запуск on duplicate key можно не использовать. Наверное, можно составить и единственный запрос вместо 4х, но он будет чрезвычайно громоздким, и я пока решил пробовать так.

Разумеется, запрос не выполнился, отвалился по таймауту, который я и так сильно увеличил. limit 0,1000; limit 1001,2000 и так далее, как я понимаю, использовать бесполезно, поскольку каждый следующий этап сервер всё равно будет проходить предыдущие.
Решил итерировать запросы по id , добавляя ограничение 20000*i в запрос, но ситуация не улучшилась, видимо, id надо переопределить, либо заставить сервер проводить данную проверку первой.

Как итог, нужны идеи, как можно оптимизировать запрос, перестроить таблицы или поменять подход, чтобы решить эту задачу (не обязательно чистым SQL-запросом, работать с базой из языков программирования я умею). Скажу спасибо и за советы по физическому ускорению сервера: памяти на машине 32Гб, сервер использует мало, может, какие-то переменные подкрутить?

Update 1. Привожу результаты EXPLAIN для запроса:

Добавлены индексы (chr, left, right) по совету @Mike:

Update 2. Заставить mysqld работать в несколько потоков.

Взглянул на загрузку CPU во время запроса. Поскольку сейчас я работаю в монопольном режиме, я один обращаюсь в локальному серверу. Можно ли как-то заставить mysqld обрабатывать один запрос в несколько потоков? А то 8 ядер/16 потоков в его распоряжении, а он пользует только один.

Кстати, раскидывание разных таблиц в папки на разных физических жестких дисках даёт, пусть небольшое, но ускорение работы.

Update 3 На данный момент я программно разбил все исходные таблицы в зависимости от того, какая хромосома и скрипт (точнее, серия скриптов, тоже вызываются программно) сейчас выглядит так ( допустим, обрабатывается 7я хромосома):

Но особого прогресса не отмечаю.

4 ответа 4

Обычные методы оптимизации, применяемые СУБД обычно берут 1 запись из первой таблицы и ищут по всей второй таблице подходящие записи. При наличии индекса это происходит довольно быстро, за log2(m) , но все таки это обращения к десятку страниц индекса . Ваш запрос при обычном Join должен выполняться в лучшем случае за O=n*(log2(m)+log2(k)) (Где n,m,k количество записей в 3х таблицах в запросе) (хотя если посмотреть статьи по оптимизации MySQL там вообще выйдет O=n*log2(m)*log2(k) )

С другой стороны, ваши данные довольно специфичны (жаль СУБД не способна это оценить). В таблицах genes и repeats лежат не пересекающиеся интервалы, следовательно при сортировке по полю left записи оказываются отсортированы и по right. В таблицах с паттернами же интервалы временами пересекаются, в итоге при сортировке по left поле right иногда немного «возвращается назад», но таких ситуаций порядка 8%.

Сравнивать один отсортированный список с отсортированным же списком возможных интервалов можно гораздо проще, идя параллельно по обоим спискам одновременно, сдвигая указатель вперед в том списке, в котором записи еще меньше, чем в другом. Причем так можно проходить параллельно по более чем по 2 спискам одновременно. При этом сложность поиска оказывается O=n+m+k .

К сожалению из за того, что список паттернов не может быть одновременно отсортирован и по left и по right нам иногда надо откатывать указатель в repeats немного назад. В MySQL при работе с курсорами это невозможно, по этой причине я решил запоминать те записи, которые выпали из последовательности возрастающих right и при этом могли попасть в интервал предыдущей записи repeats (таких оказалось совсем мало 2k из 438k). Этого можно избежать помня несколько последних записей repeats, но на MySQL это будет слишком громоздко. Сохраненные записи приходится обрабатывать за второй проход, проходя по repeats отсортированной по полю right.

На основе вышеизложенного у меня вышла следующая хранимая процедура:

Время выполнения данной процедуры на контрольном примере из 6k / 444k / 438k записей, составило 45 секунд . Правда в контрольном примере не было поля chr, когда оно есть, оно конечно должно так же участвовать в сортировке списков и сравниваться во всех условиях. Таблицы 47, 51 и т.п. стоит обрабатывать за отдельные проходы. Если бы была возможность возвращаться назад, можно было бы попробовать обработать все за один проход. Аналогичный алгоритм на perl, беря данные из отсортированных csv файлов работает 4 секунды.

В связи с тем, что в repeats все таки могут быть пересечения интервалов, все немного сложнее и для получения всех вариантов (коих примерно на 200 записей больше изначального варианта (в 130k записей)) лучше все таки использовать массивы, что бы можно было заглядывать немного вперед. на perl это выглядит так

Классная тема вопроса! Только на чистом SQL такую процедуру смерти-подобно делать. Гораздо проще скрипт (например PHP) накатать — который пусть и дольше, но всё выполнит — не единым INSERT-SELECT-ом, а вставляя по одиночке, или в транзакциях пачками. Так понимаю проблема не в производительности, а в том что выполнить до конца не удаётся в принципе. То лучше есть менять подход.

То есть советую из таблицы 51k-80-80-ignore-random-noreverse выбирать для каждой её записи сопоставления по таблицам genes-g38-201505 и repeats-g38-201505 . Если сопоставление найдено, то проводить INSERT .

Лимитировать можно и без скрипта. Для начала — чтобы выбирать по одной записи — таблице 51k-80-80-ignore-random-noreverse нужно добавить первичный ключ:

А дальше вашим же запросом теперь можно брать любой отрезок по таблице 51k-80-80-ignore-random-noreverse :

И не будет такой ситуации как вы написали limit 0,1000; limit 1001,2000 и так далее, как я понимаю, использовать бесполезно, поскольку каждый следующий этап сервер всё равно будет проходить предыдущие так как теперь работает индекс id .

Второе — нужны правильные индексы специально под запрос:

Индексы под запрос создавать очень просто — нужно добавить в индексы все столбцы(по одному мульти-индексу на таблицу), которые участвуют в WHERE кроме первичного ключа, порядок: от столбца с наименьшим кол-вом вариантов, к наибольшему чтобы оптимально работал BTREE.

Ну и последнее — вы используете SQL для очень нетипичной задачи, последний раз видел когда перемножают таблицы в рабочем коде (делают JOIN не оператором = ) 5 лет назад: подсказать что-то, что попадёт в яблочко сложно, кроме того что бить один «толстый» запрос на выполнение множества более «тонких» запросов. Совсем в яблочко — это проверять совпадения в цикле скрипта, который подключается к SQL — но очевидно нужно уметь сделать и запустить такой скрипт, либо обращаться к фрилансерам.

Начал с рассмотрения запроса, который приведён в тексте вопроса. Несмотря на годный с виду EXPLAIN PLAN, запрос работает так медленно, будто читает всю таблицу вместо обращений к индексу. Правка индексов мало что меняет.

Потом пришло понимание вот какого момента: при обращении к таблице генов, мы указываем только одну границу для колонки g.left , а именно: a.left > g.left . Таким образом, мы требуем, чтобы граница g.left у гена была меньше, чем граница a.left у эксперимента. И всё. А если граница a.left у эксперимента имеет большое значение, например 100 миллионов? Ну да, чтобы обработать одну строку таблицы эксперимента, понадобится прочитать почти всю таблицу генов. Так уж устроены индексы, что если нет знака равенства, то дело плохо. Таблица генов – относительно невелика, но данные рассуждения относятся и к работе с весьма большой таблицей повторов: a.right > r.left . И это главная проблема запроса.

Предоставленный автором фрагмент реальных данных показал, что для генов и повторов разница между left и right в одной строке может достигать 1,5 млн и 0,5 млн. Это слишком много, но я всё же попробовал оптимизировать условия запроса, и никакого выигрыша не получил. Почему-то даже наоборот.

Как уже было упомянуто другими участниками, гены, повторы и эксперименты можно рассматривать в качестве диапазонов на оси координат, от left до right . Однако, это только половина дела :) Можно же и саму ось координат тоже поделить на диапазоны.
Поделив ось на диапазоны, мы можем затем для каждого из этих диапазонов найти все гены и повторы, которые туда попадают хотя бы одним краем.

Пара примерчиков
Пример 1:
Ген с координатами left =10, right =50.
Ось делится на диапазоны по 100 единиц, т.е. 0-99, 100-199, 200-299, .
В данном случае ген попадает только на один диапазон: 0-99.
Пример 2:
Ген с координатами left =140, right =360.
Ось делится на диапазоны так же: 0-99, 100-199, 200-299, 300-399, .
В данном случае ген попадает в три диапазона: 100-199, 200-299, 300-399. Первый и третий диапазоны ген покрывает частично, а второй (средний) – полностью.

Зачем нужна эта возня с диапазонами на оси координат?

Чтобы понять, давайте вернёмся к условиям задачи: найти эксперименты, которые левым краем left попадают на ген, а правым краем right попадают на повтор.

Изначально мы искали попадание на гены и повторы, используя неравенства в условиях проверки, и при этом SQL-сервер просматривал гораздо больше строк в таблицах, чем хотелось бы.

Теперь же, зная в какой диапазон оси координат попадает значение левой границы эксперимента a.left , мы могли бы сразу проверить именно этот диапазон на наличие в нём (в диапазоне) каких-либо генов. Аналогично с повторами, только для них проверяем по правой границе a.right .

Есть ещё одна тонкость: может оказаться так, что ген лежит в координатах left =10, right =20, а эксперимент в координатах left =25, right =45. В таком случае они лежат в одном диапазоне 0-99, но они не удовлетворяют условиям задачи. Получается, что проверив диапазон, мы сделали только предварительную выборку более-менее подходящих генов, но для полной уверенности надо применить условия из первоначального запроса.
В итоге, если ось координат разбить по десяткам тысяч, и пронумеровать диапазоны, деля координату на 10000, то запрос для решения задачи будет выглядеть как-то так:

В данном запросе таблицы unwind-genes и unwind-repeats содержат сведения обо всех диапазонах оси координат, поделённой на отрезки в 10000 единиц, куда попадают, соответственно гены и повторы.

Прикладываю скрипт на обе таблицы, и на хранимые процедуры для их заполнения. https://pastebin.com/72Rp6Pfy

Время выполнения на моем компьютере (под виртуальной машиной) на полученном фрагменте реальных данных:

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

Плюсы данного метода решения:
— высокая скорость
— чистый SQL, без использования дополнительных инструментов
— нечувствительность к наличию пересечений по интервалам в таблицах генов и повторов, в том смысле что ничего не «сломается» и никакие данные не будут упущены из рассмотрения
— если таблицы генов и повторов не меняются, то промежуточные данные не нужно пересчитывать для каждого нового эксперимента — экономия времени обработки для каждого нового эксперимента
Минусы:
— база увеличивается в размерах из-за хранения промежуточных данных по диапазонам

Возможные пути для дальнейшей оптимизации решения:
— разбивка оси координат по диапазонам другого размера; при уменьшении размера диапазонов можно получить ускорение в выполнении запроса, но увеличится объём промежуточных данных, их объём может стать в десятки раз больше объёма исходных таблиц
— можно попробовать в таблицы с промежуточными данными закинуть имена генов и повторов, чтобы избавиться от соединения с исходными таблицами (вместо 5-и таблиц запрос будет обходиться 3-мя)

Илон Маск рекомендует:  Что такое код winexe

Оптимизация MySQL (просто о сложном)

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

Делайте запросы MySQL удобными для кэширования

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

Дело в том, что в первом запросе была использована функция CURDATE(), особенность её работы не позволяет помещать результаты запроса в кэш. Значение даты можно предварительно записать в строку запроса, это позволит исключить использование функции CURDATE() в запросе.
По аналогии есть и другие функции, которые не кэшируются самим сервером MySQL, среди них RAND(), NOW() а так же другие функции, результат которых недетерминирован.

Просмотрите как выполняется ваш запрос с помощью синтаксиса EXPLAIN

Посмотреть, как MySQL выполняет ваш запрос можно с помощью синтаксиса EXPLAIN. Его использование может помочь определить слабые места в производительности запроса, а так же в структуре таблиц. В качестве результата запроса EXPLAIN возвратит данные, которые покажут, какие используются индексы, каким образом выбираются данные из таблиц, как сортируются, и т.д. Для этого достаточно добавить вначале SELECT-запроса ключевое слово EXPLAIN, после чего будет показана таблица, с данными.

Когда вам нужна одна запись, выставляйте LIMIT 1

Не мало случаев, когда из таблицы вам требуется проверить наличие хотябы одной записи, в этом случае рекомендуется добавить к запросу параметр LIMIT 1. Это сделает его более оптимальным, т.к. механизм базы данных после нахождения первой записи остановит выборку данных, вместо того чтобы выбирать все данные. Вы экономите ресурсы.

Индексируйте поля по которым производится поиск

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

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

Индексируйте поля по которым объединяются таблицы

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

Найдите альтернативу вместо ORDER BY RAND()

Использование рандомной сортировки действительно является весьма удобным, и об этом такого же мнения многие начинающие программисты. Однако тут есть подводные камни, и очень весомые, используя подобный метод выборки в своих запросах, вы оставляете узкое место в производительности. Здесь же рекомендуется прибегнуть к дополнительному коду вместо использования ORDER BY RAND(), в качестве альтернативы, чтобы избавиться от слабого места в производительности, которое напомнит о себе при увеличении объема данных.

Используйте выборку конкретных полей, вместо SELECT *

Не ленитесь указывать конкретные нужные поля в запросе при выборке, вместо использования «*» — выборка всех полей, дело в том, что чем больше данных считывается из таблицы, тем медленнее становиться ваш запрос.

Добавляйте поле ID для всех таблиц

Каждая таблица в хорошем её исполнении должна иметь поле id типа INT, которое является первичным ключом (PRIMARY_KEY), и AUTO_INCREMENT. Кроме того, для поля нужно указать параметр UNSIGNED, который означает то, что значение всегда будет положительным.
В MySQL есть внутренние операции, которые могут использовать первичный ключ, это играет роль для сложных конфигураций баз данных, таких как кластеры, распараллеливание, и т.д.
Кроме того, если есть несколько таблиц, и необходимо выполнить объединенный запрос, то тут ID таблиц окажется весьма кстати.

ENUM как альтернатива VARCHAR

Давайте представим, вы хотите добавить поле в таблице, которое должно содержать определенный набор значений. Традиционно многие программисты выставляют тип VARCHAR для полей. Однако есть и другой тип поля, который гораздо быстрей и компактнее. Значения в данном типе хранятся так же, как и TINYINT, но отображаются как в строковом типе.

Используйте значение NOT NULL вместо NULL

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

Пользуйтесь Prepared Statements

Prepared Statements (подготовленные выражения, их так же называют связываемыми переменными) — это часть функциональности SQL-баз данных, предназначенная для отделения данных запроса и собственно выполняемого SQL-запроса. Его использование имеет преимущества в плане безопасности, и производительности. Фильтруя значения данных, добавляемых в запрос, prepared statements таким образом защищает базу данных от SQL инъекций. Разумеется, делать подобные проверки можно и в ручную, однако в этом случае есть вероятность допустить ошибку из-за невнимательности. Последние версии MySQL компилируют prepared statements в бинарную форму, это позволяет повысить эффективность его работы. Выполняя множество однотипных запросов в приложении, MySQL будет разбирать запрос только один раз. На первых этапах prepared statements не имело возможности кэшироваться в MySQL, это являлось веской причиной для его игнорирования и отсутсвия желания использовать в своих проектах. Однако начиная с версии MySQL 5.1 ситуация кардинально поменялась.

Пользуйтесь mysql_unbuffered_query

mysql_unbuffered_query() посылает запрос MySQL query без автоматической обработки и буферизации её результата, в отличие от функции mysql_query(). Это позволяет сохранить достаточно большое количество памяти для SQL-запросов, возвращающих большое количество данных. Кроме того, вы можете начать работу с полученными данными сразу после того, как первый ряд был получен: вам не приходится ждать до конца SQL-запроса.

Вы можете хранить IP-адреса в поле с типом INT (UNSIGNED)

Для хранения IP-адресов в привычном виде многие хранят в таблице с полем типа VARCHAR(15), и лишь не многие используют целочисленный тип для этого. Плюсы в том, тип INT занимает 4 байта и имеет фиксированный размер поля. Поле типа INT должно быть UNSIGNED, т.е. целочисленным, в запросе следует использовать функцию INET_ATON(), которая будет конвертировать IP-адрес в число. Обратное преобразование выполняется с помощью функции INET_NTOA().

Используйте статичные таблицы

Статичная таблица это обычная таблица в базе, за исключеним того, что каждое поле в таблице имеет фиксированный размер. Если в таблице есть колонки, не фиксированной длины, к примеру, это могут быть: VARCHAR, TEXT, BLOB, она перестает быть статичной, и будет обрабатываться MySQL немного иначе. Статичные таблицы, или их можно ещё назвать таблицами фиксированного размера работают быстрее не статичных. Записи из таких таблицах будут просматриваться быстрее, при необходимости выбора нужной строки MySQL быстро вычислит её позицию. Если поле имеет не фиксированный размер, то в этом случае поиск производиться по индексу. Есть и другие плюсы использования статических таблиц, дело в том, что эти таблицы проще кэшируются, а так же восстанавливаются после падения базы данных.

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

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

Разделяйте объемные запросы INSERT и DELETE

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

Стремитесь использовать поля небольшого размера

Как известно данные базы хранятся на жестком диске, это зачастую это может оказаться одним из слабых мест в веб-приложении. Дело в том, что записи небольшого размера являются более предпочтительными, т.к. использование их уменьшает работу с жестким диском. Если вы уверенны, что конкретная таблица будет хранить мало строк, то рациональным решением будет использование типов полей, с минимальными возможными значениями. К примеру, если основной ключ имеет тип INT, и вы будете хранить в таблице лишь небольшое кол-во данных, то лучше сделать его типа MEDIUMINT, SMALLINT или даже TINYINT.

Выбирайте тип таблиц под свои задачи

Два широко известных типа таблиц на сегодняшний день, это MyISAM и InnoDB, каждый из них имеет свои положительные и отрицательные стороны. К примеру, MyISAM хорошо считывает данные из таблиц в большом объеме, одно он более медлителен при записи. Он так же хорошо выполняет запросы вида SELECT COUNT(*).
Механизм хранения данных у InnoDB более сложный, чем у MyISAM, однако, он поддерживает блокировку строк, что является положительной стороной при масштабировании. Поэтому сказать, что одно лучше другого нельзя, да и не правильно, нужно выбирать тип исходя из своих потребностей.

Оптимизация MySQL – основы правильной реализации

Дата публикации: 2020-06-13

От автора: один мой знакомый решил оптимизировать свой автомобиль. Сначала одно колесо снял, потому крышу спилил, затем мотор… В общем, сейчас он пешком ходит. Это все последствия неправильного подхода! Поэтому, чтобы ваша СУБД продолжала «ездить», оптимизация MySQL должна проходить правильно.

Когда оптимизировать и зачем?

Лишний раз лезть в настройки сервера и изменять значения параметров (особенно, если не знаете, чем это может закончиться) не стоит. Если рассматривать данную тему с «колокольни» улучшения производительности веб-ресурсов, то она настолько обширная, что ей нужно посвящать целое научное издание в 7 томах.

Но такого писательского терпения у меня явно нет, да и у вас читательского тоже. Мы поступим проще, и постараемся лишь слегка углубиться в чащи оптимизации MySQL сервера и его составляющих. С помощью оптимальной установки всех параметров СУБД можно достигнуть нескольких целей:

Увеличить скорость выполнения запросов.

Повысить общую производительность сервера.

Как создать сайт самому?

Какие технологии и знания необходимы сегодня, чтобы создавать сайты самостоятельно? Узнайте на интенсиве!

Уменьшить время ожидания загрузки страниц ресурса.

Снизить потребление серверных мощностей хостинга.

Снизить объем занимаемого дискового пространства.

Постараемся всю тематику оптимизации разбить на несколько пунктов, чтоб было более-менее понятно, от чего «котелок» закипает .

Зачем настраивать сервер

В MySQL оптимизацию производительности следует начинать с сервера. Прежде всего, следует ускорить его работу и уменьшить время обработки запросов. Универсальным средством для достижения всех перечисленных целей является включения кэширования. Не знаете, «what is it»? Сейчас все поясню.

Если на вашем экземпляре сервера включено кэширование, то система MySQL автоматически «запоминает» введенный пользователем запрос. И в следующий раз при его повторении данный результат запроса (на выборку) будет не обработан, а взят из памяти системы. Получается, что таким образом сервер «экономит» время на выдачу ответа, и вследствие чего скорость реагирования сайта повышается. В том числе это касается и общей скорости загрузки.

В MySQL оптимизация запросов применима к тем движкам и CMS, которые работают на основе данной СУБД и PHP. При этом код, написанный на языке программирования, для генерации динамической веб-страницы запрашивает некоторые ее структурные части и содержимое (записи, архивы и другие таксономии) из БД.

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

Включаем и настраиваем кэширование

Но давайте вернемся от «скучной» теории к интересной практике. Дальнейшую оптимизацию базы MySQL продолжим с проверки состояния кэширования на вашем сервере БД. Для этого с помощью специального запроса мы выведем значения всех системных переменных:

MySQL ОПТИМИЗАЦИЯ всех таблиц?

MySQL имеет команду OPTIMIZE TABLE, которая может использоваться для восстановления неиспользуемого пространства в установке MySQL. Есть ли способ (встроенная команда или обычная хранимая процедура) для запуска этой оптимизации для каждой таблицы в базе данных и/или серверной установке, или это то, что вам нужно было бы script самостоятельно?

Вы можете использовать mysqlcheck , чтобы сделать это в командной строке.

Одна база данных:

Все базы данных:

Я сделал этот «простой» скрипт:

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

Обратите внимание: этот код НЕ работает на phpmyadmin.

Как это работает

Он запускает оператор show tables и сохраняет его в подготовленном операторе. Затем он запускает optimize table в выбранном наборе.

Вы можете контролировать, какие таблицы оптимизировать, установив другое значение в переменной @tables_like (например: set @tables_like = ‘%test%’; ).

В следующем примере php script может помочь вам оптимизировать все таблицы в вашей базе данных

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

для всех баз данных:

Для одной оптимизации базы данных:

Из командной строки:

затем введите пароль

Из phpMyAdmin и других источников вы можете использовать:

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

Вы можете оптимизировать/проверить и восстановить все таблицы базы данных с помощью клиента mysql.

Сначала вы должны получить список всех таблиц, разделенных символом ‘,’:

Теперь, когда у вас есть список всех таблиц для оптимизации:

MySQL Administrator (часть MySQL GUI Tools) может сделать это для вас на уровне базы данных.

Просто выберите свою схему и нажмите кнопку Maintenance в правом нижнем углу.

Так как GUI Tools достигли статуса конца жизни, их трудно найти на странице mysql. Нашли их через Google: http://dev.mysql.com/downloads/gui-tools/5.0.html

Я не знаю, может ли это сделать и MySQL Workbench.

И вы можете использовать инструмент командной строки mysqlcheck , который тоже должен это сделать.

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

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

Если вы не знаете пароль root и используете WHM, вы можете изменить его из WHM, перейдя к: Главная > Службы SQL > Пароль корня MySQL

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL