20 советов и трюков для всех, кто занимается разработкой под MySQL

С момента выпуска, в 1995, MySQL быстро стала самой популярной в мире СУБД (Система Управления Базой Данных). С такой популярностью приходит и ответственность… а также бесчисленные улучшения, обновления и хитрые трюки.

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

1. НЕ РЕДАКТИРУЙТЕ ФАЙЛЫ ДАМПОВ (DUMP FILES)

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

Многие люди пытаются править файлы дампов стандартным текстовым редактором и повреждают их. Единственный гарантированный вариант обойтись без ошибок, это НЕ ПРАВИТЬ ФАЙЛЫ ДАМПОВ.

2. РАЗМЕР БЛОКА MYISAM (MYISAM BLOCK SIZE)

Одной из наиболее покрытых мраком настроек является размер блока индексов для таблиц MyISAM. Значение параметра myisam_block_size находится в. MYI-файлах в буфере ключей, а также на диске. Стандартное значение составляет 1 килобайт, что слишком мало для современных систем.

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

3. ВКЛЮЧЕНИЕ DELAY_KEY_WRITE

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

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

4. ОБЪЕДИНЕНИЯ: СОЗДАВАЯ СТОЛБЦЫ ОДИНАКОВЫХ ТИПОВ, НЕ ЗАБЫВАЙТЕ ПРО ИНДЕКСЫ

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

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

5. ОПРЕДЕЛЯЙТЕ WHERE С ПАРАМЕТРОМ LIMIT 1

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

6. ДОБАВЛЯЙТЕ КЛЮЧЕВОЕ СЛОВО EXPLAIN К ЗАПРОСАМ SELECT

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

Создайте сложный запрос SELECT с объединениями и добавьте EXPLAIN перед ним. Результат вы сможете оценить сразу же. Используйте этот трюк, чтобы сэкономить время.

7. КЭШ ЗАПРОСОВ: ОПТИМИЗИРУЙТЕ ВАШИ ЗАПРОСЫ

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

Один и тот же запрос может выполняться несколько раз. И гораздо быстрее запросить готовый результат из кэша. Однако, для запроса CURDATE (текущая дата) это недоступно. Чтобы реализовать кэширование для CURDATE, просто добавьте перед запросом PHP-строку:

//CURDATE не определена
$query = 'SELECT id FROM table WHERE publish_date = CURDATE()';

8. ИСПОЛЬЗУЙТЕ ТРАССИРОВКУ СТЕКА, ЧТОБЫ ИЗБЕЖАТЬ ОШИБОК

На сайте skysql.com имеется очень много трюков и секретов MySQL. Мы собираемся разобраться с одним из них в этом коротком подзаголовке, потому что это действительно очень просто, чтобы пройти мимо. Опция MySQL stack_trace может быть использована, чтобы выяснить, в чем проблема и устранить её.

Из всех возможных применений, наиболее интересным, скорее всего, будет знать как нулевой указатель (null pointer) легко может внести хаос в код. А также как просто это можно выявить, отследить и исправить, используя совет от Sky SQL. Ознакомьтесь с небольшой статьей здесь.

9. ПЕРЕЧИСЛЕНИЯ ENUMS И УСТАНОВКА SQL_MODE В ЗНАЧЕНИЕ TRADITIONAL

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

Конечно, вы получите предупреждение, если что-то неверно. К счастью это легко устраняется. Установите параметр mysql_mode в значение TRADITIONAL. Вуаля!

Чтобы изменить режим SQL (SQL Mode), используйте следующий код:

//Запуск mysqld в режиме
$--sql-mode=”modes”
//или
$sql-mode=”modes” (my.ini для Windows / my.cnf для Unix)
//Изменение во время выполнения, несколько режимов разделяются запятой 
$set [GLOBAL|SESSION] sql_mode='modes'
//TRADITIONAL эквивалентно следующим режимам:
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE,
ERROR_FOR_DIVISION_BY_ZERO и NO_AUTO_CREATE_USER

10. СМЕНА ПАРОЛЯ ДЛЯ ROOT

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

//способ 1
$mysqladmin -u root password [новый пароль]
//способ 2
$mysqladmin -u root -p [старый пароль] newpass [нажмите Enter и введите новый пароль, затем опять нажмите Enter]
//способ 3
//Введите команду mysql 
$mysql -u root -p
//Дождитесь всплывающего окна и введите:
$use mysql;
//затем введите имя пользователя, для которого вы хотите изменить пароль
$update user set password=PASSWORD (здесь новый пароль) where User = 'username';
//далее обновление привилегий для пользователей
$flush privileges; 
$quit

11. РЕЗЕРВНОЕ КОПИРОВАНИЕ БАЗЫ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ MYSQLDUMP

Просто и понятно, как и все в мире PHP и MySQL. Практически нельзя обойтись без этой несложной, но очень важной для сохранения базы данных вещи. Если вы разобрались с трюком под номером 10, то, несомненно, поймете и этот – он очень важен! Чтобы сделать резервное копирование, выполните следующие команды:

$mysqldump –user [имя пользователя] –password=[пароль] [имя базы данных] > 
[имя файла дампа]
//вместо “user” и “password” можно написать коротко “--u” и “--p”
//Дамп для нескольких баз данных в одной строке:
mysqldump –user [имя пользователя] –password=[пароль] [имя первой базы данных] 
[имя второй базы данных] > [файл дампа]
//несколько баз данных может быть передано в одной строке.
//для резервирования всех баз используйте [--all-databases].
//либо воспользуйтесь программой 'automysqlbackup' от Sourceforge!

12. ПРАВКА КОНФИГУРАЦИОННЫХ ФАЙЛОВ

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

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

Ознакомиться с инструкцией можно с помощью команды —man db. Читать очень рекомендуется. Настройки можно найти на официальной странице проекта. Хотя вначале это может насторожить, но MySQL Tuner следует сделать вашим рабочим инструментом для ускорения MySQL и теста конфигурационных файлов.

13.ВЫЯВИТЕ МЕДЛЕННЫЕ ЗАПРОСЫ

То, что MySQL по умолчанию не отслеживает и не протоколирует медленные запросы, не значит, что вы не можете сделать это сами. Как вы могли увидеть, прочитав советы выше, MySQL имеет много функций, которые по умолчанию не включены, но могут быть очень полезны. Чтобы разрешить функцию slow-query-log отредактируйте конфигурационный файл my.cnf и затем запустите следующие команды, находясь в mysqld:

$long_query_time = 1
$log-slow-queries = /var/log/mysql/mysql-slow-query.log

Строка 1 означает, что любой запрос, длящийся более 1 секунды, будет занесен в лог. По умолчанию, параметр установлен в «0». После внесения данного изменения, сервер необходимо перезапустить, чтобы изменения вступили в силу.

Теперь, когда вы имеете возможность выявлять «долгие» запросы, используйте команду EXPLAIN, которая была обсуждена ранее (совет №6), чтобы выяснить, почему это происходит.

14. АВТОИНКРЕМЕНТ: ПРЯМОЙ СБРОС

Одной из замечательных стандартных возможностей MySQL является столбец индекса Autoincrement. При создании новой строки будет автоматически высчитываться и заноситься в строку её номер по порядку. Однако, этот стандартный отсчет иногда требуется сбросить. Несколькими командами это легко можно произвести:

Reset Autoincrement Directly
ALTER TABLE [table name] AUTO_INCREMENT =1;
Drop Table and Recreate
DROP TABLE [table_name];
CREATE TABLE [table_name] {….};

15. РАЗБИЕНИЕ ТАБЛИЦ MYSQL

Удивителен и поучителен тот факт, что многие программисты баз данных пытаются закрыть глаза, обойти или прямо отказываются от разделения таблиц. Ведь таблицы становятся меньше и удобнее в управлении. Иногда они разрастаются до тысяч строк и/или столбцов. А что будет, когда количество строк увеличится до нескольких сотен тысяч? Это очень актуальная проблема. Особенно при быстро растущей в размерах базе данных.

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

В конце концов, никто не любит долго ждать. Обратите внимание, что не все версии MySQL поддерживают разделение таблиц. Запустите команду SHOW PLUGINS; чтобы определить наличие этой функции в вашей версии.

Выделенные разделы следует ранжировать с помощью команды RANGE. Допустим, у нас есть таблица «Университеты» отсортированная, к примеру, по дате основания. При использовании команды RANGE они будут отсортированы и выведены по дате основания, вне зависимости от того, в каком порядке записаны в реальной таблице. Этот трюк можно проделать так:

CREATE TABLE founded (order_date DATETIME NOTNULL –omit other columns)
PARTITION BY RANGE (YEAR (order_date))
PARTITION p_1900 VALUES LESS THAN (1900),
PARTITION p_2013 VALUES LESS THAN (2013)
PARTITION p_all VALUES LESS THAN MAXVALUE ) ;

Строка 3 выделит в отдельную таблицу все университеты, основанные от нулевого до 1900 года. Второй столбец отделит учебные заведения, созданные до 2013 года. Последняя команда выделит все оставшиеся. Дайте представим, что мы забыли ввести год основания какого-нибудь из университетов в таблице. Он появится в третьей группе.

16 ПЕРЕЧИСЛЕНИЯ ENUMS И SQL

ENUMS или перечисления можно очень хитро применять в MySQL, но это требует от программиста аккуратности. Пользователи PostgreSQL могут использовать функции CREATE DOMAIN и CREATE TYPE. Но в MySQL этого нет. Примите во внимание особенности поведения ENUM в MySQL.

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

17. ХРАНИТЕ IP-АДРЕСА В ТИПЕ ДАННЫХ UNSIGNED INT

Чрезвычайно полезный совет. При таком подходе IP-адреса в MySQL хранятся в виде целочисленных значений. Используйте функцию INET_ATON(), чтобы преобразовать целевой IP в тип Integer. Любой, кто достаточно хорошо знаком с PHP, может вызвать аналогичную функцию ip2long.

John Bafford потратил много времени на наиболее полный анализ этой техники. Он исследовал каждый аспект этой проблемы, чтобы можно было сэкономить на стоимости дискового пространства.

18. ИСПОЛЬЗУЙТЕ ИНДЕКСЫ ДЛЯ СОЗДАНИЯ ТАБЛИЦ

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

CREATE TABLE records (
name VARCHAR(50), 
age INT,
id_num INT, INDEX (id)
)

//А так можно добавить индексы в несколько столбцов
ALTER TABLE (records ADD INDEX id(id_num), 
ADD INDEX name(name);

19. ВЕРТИКАЛЬНОЕ РАЗДЕЛЕНИЕ

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

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

20. ВЫБОР ДВИЖКА ХРАНЕНИЯ ДАННЫХ

В MySQL имеется два основных движка хранения данных: InnoDB и MyISAM. Мы коротко касались нескольких трюков, связанных с MyISAM, а вот InnoDB обошли стороной.

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

Заключение

MySQL без сомнений одна из самых важных и влиятельных программ, которые когда-либо появлялись в истории компьютеров. Мы выделили всего 20 советов, которые должен знать каждый. Но, чем больше вы будете использовать MySQL, тем больше будете находить своих «фишек» и, через какое-то время, создадите свой список из 20 самых полезных трюков .

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

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

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

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

Данная публикация представляет собой перевод статьи «20 Tips and Tricks Any MySQL Database Developer Should Consider» , подготовленной дружной командой проекта Интернет-технологии.ру

Меню