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

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

Это поможет вам понять номер строки SQL, верхнюю часть SQL, смещение лимита MySQL и онлайн-практику SQL.

Если вы ищете удобный и простой способ отобразить большое количество строк и столбцов для посетителей вашего веб-сайта WordPress с красочными, информативными и отзывчивыми интерактивными таблицами и диаграммами, вы можете использовать его с плагином wpDataTables, проверить цены и все функциональные возможности, которые он дает здесь.

1 Всегда используйте правильный тип данных

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

Например: использование varchar (20) вместо типа данных DATETIME для хранения значений даты и времени приведет к ошибкам в вычислениях, связанных с датой и временем. Также возможно, что будут сохранены неверные данные.

2 Используйте CHAR (1) вместо VARCHAR (1)

VARCHAR (1) занимает дополнительные байты для хранения информации, поэтому, если вы набираете один символ, лучше использовать CHAR (1).

3 Используйте тип данных CHAR для хранения только данных фиксированной длины.

Например: если длина данных меньше 1000, использование char (1000) вместо varchar (1000) займет больше места.

4 Избегайте использования региональных форматов даты

При использовании типа данных DATETIME или DATE всегда используйте формат даты ГГГГ-ММ-ДД или формат даты ISO, подходящий для вашего SQL Engine. Региональные форматы, такие как ДД-ММ-ГГГГ или ММ-ДД-ГГГГ, не будут сохранены должным образом.

5 ключевых столбцов индекса

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

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

6 Не используйте функции над индексированными столбцами…

…потому что тогда индекс теряет свою цель.

Например, предположим, что вы хотите получить данные, в которых первые два символа кода клиента — AK. Напишите:

SELECT columns FROM table WHERE customer_code like 'AK%'

и не пиши

SELECT columns FROM table WHERE left (customer_code,2)='AK’

Почему? В первом примере будет использоваться индекс, что приведет к более быстрому времени отклика.

**7 Используйте SQL SELECT * только при необходимости**

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

Вместо этого явно введите имена столбцов, которые действительно необходимы.

Примечание: помните, что все операторы SELECT требуют предложения WHERE.

8 Используйте предложение ORDER BY только в случае необходимости

Если вы хотите показать результат во внешнем приложении, позвольте ему ЗАКАЗАТЬ набор результатов. Если вы сделаете это в SQL, время отклика может увеличиться в многопользовательской среде.

9 Выберите правильный движок базы данных

Если вы разрабатываете приложение, которое читает данные чаще, чем записывает (например, поисковая система), выберите механизм хранения MyISAM.

Выбор неправильного механизма хранения повлияет на производительность.

10 Используйте предложение EXISTS везде, где это необходимо

Для проверки существования данных используйте предложение EXISTS, которое быстрее по времени отклика. Например, используйте:

If EXISTS(SELECT * from Table WHERE col=’some value’)

Не используй :

If (SELECT count(*) from Table WHERE col=’some value’)>0

11 ОБЪЯСНИТЕ ваши запросы SELECT

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

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

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

12 Используйте LIMIT 1 при получении уникальной строки

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

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

// do I have anyusersfrom Alabama?

// что НЕЛЬЗЯ делать:
$r = mysql_query("SELECT * FROM user WHERE state = ‘Alabama’");
if (mysql_num_rows($r) > 0) {
// …
}

// намного лучше:
$r = mysql_query("SELECT 1 FROM user WHERE state = ‘Alabama’ LIMIT 1");
если (mysql_num_rows($r) > 0) {
// …
}

13 Индексируйте и используйте те же типы столбцов для объединений

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

Кроме того, присоединяемые столбцы должны быть одного типа. Например, если вы соедините столбец DECIMAL со столбцом INT из другой таблицы, MySQL не сможет использовать ни один из индексов. Даже кодировки символов должны быть одного типа для столбцов строкового типа.

// looking for companies in my state $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id");

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

14 Скрыть MySQL от Интернета

Опытные администраторы баз данных и специалисты по безопасности знают: никогда не размещайте базу данных в корневом каталоге веб-сервера.

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

Другой вариант — использовать пропускную сеть MySQL. Когда он включен, MySQL прослушивает только локальные соединения сокетов и игнорирует все TCP-порты.

15 Используйте наименьшие возможные типы данных

Позвольте мне рассказать вам историю. Когда я учился в колледже, философия заключалась в том, что «памяти мало». Это были дни жестких дисков емкостью 256 МБ. В настоящее время, похоже, никого не волнует ни йота памяти, ни место на жестком диске. Новая философия заключается в том, что «память стоит дешево». Это может быть правдой в долларовом выражении, но чтение больших типов данных по-прежнему занимает больше времени, чем чтение меньших. Большие типы данных требуют, чтобы в память считывалось больше секторов диска.

Мораль такова: не поддавайтесь искушению сразу перейти к самому большому типу данных при проектировании таблиц. Подумайте об использовании int вместо bigint.

Кроме того, избегайте больших текстовых полей (255), когда достаточно varchar или меньшего char.

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

16 Воспользуйтесь кэшированием запросов

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

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

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

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

К счастью, есть простое решение: вы можете сохранить результаты функции в переменной.

17 Не редактируйте файлы дампа

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

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

18 Используйте настройку размера блока MyISAM

Настройка размеров блоков в индексах таблиц MyISAM — одна из лучших практик MySQL. Его можно найти в файлах .MYI в ключевом буфере, а также на диске. Настройка выглядит так: myisam_block_size.

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

19 Включите delay_key_write

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

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

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

20 Используйте трассировку стека для изоляции ошибок

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

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

С помощью этого совета Sky SQL обнаружение, отслеживание и исправление становятся намного проще.

21 MySQL меняет пароль ROOT

Изменение пароля ROOT может показаться простым, но знание того, как это сделать, одинаково важно как для вашей домашней операционной системы, так и для серверов MySQL.

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

Вот как установить пароль ROOT и как изменить пароль пользователя ROOT:

//Straightforward MySQL 101 $mysqladmin -u rootpassword [Type in selectedpassword] //Changingusers ROOT password $mysqladmin -u root -p [type oldpassword] newpass [hit enter and type new password. Pressenter] //Use mysqlsqlcommand $mysql -u root -p //prompt "mysql>" pops up. Enter: $use MySQL; //Enter usernameyouwant to change the password for $update user set password=PASSWORD (Type new PasswordHere) where User = 'username'; //Don'tforget the previoussemicolon, nowreload the settings for the user'sprivileges $flush privileges; $quit

22 Исправьте файлы конфигурации

MySQL Tuner — это сценарий Perl, который может каким-то образом оптимизировать вашу производительность, предлагая изменения в ваших файлах конфигурации.

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

Это то, что может быть использовано как новичками, так и профессионалами. MySQL Tuner не является специальным советом для MySQL, поэтому существует множество настроек и модов, которые можно применить. Чем больше вы его используете, тем больше настроек вы можете научиться применять для собственного использования.

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

Завершение размышлений о лучших практиках MySQL

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

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

Если вам понравилось читать эту статью о лучших практиках MySQL, вы также должны прочитать это:

Источник записи: wpdatatables.com