Postgres UPDATE

For concurrent write load, add FOR UPDATE to the subquery to lock and avoid race conditions:

Concurrent SELECT. FOR UPDATE block until the first transaction releases the lock.

If the first was rolled back, the next transaction takes the lock, the rest keeps waiting.

If the first committed, the WHERE conditions is not TRUE any more ( status has changed) and the subquery (somewhat surprisingly) returns no row after re-testing the condition. Nothing happens. That would be the desired behavior if all transactions want to update the same row .

But not if each transaction wants to update the next row . And since we just want to update an arbitrary (or random) row . there is no point in waiting at all.

We can unblock the situation with the help of advisory locks :

This way, the next row that is not locked yet will be updated. Each transaction gets a fresh row to work with. I had help from Czech Postgres Wiki for this trick.

id being any unique bigint column (or any type with an implicit cast: int4. int2 ).

_{If advisory locks are in use for multiple tables in your database concurrently, disambiguate with pg_try_advisory_xact_lock(tableoid::int, id) - id being a unique integer here.}

Since tableoid is a bigint quantity, it can theoretically overflow integer. If you are paranoid enough, use (tableoid::bigint % 2147483648)::int instead - leaving a theoretical "hash collision" for the truly paranoid.

Also, Postgres is free to test WHERE conditions in any order. It could test pg_try_advisory_xact_lock() (and acquire a lock) before status = 'standby'. which could result in additional advisory locks on unrelated rows, where status = 'standby' is not true. Related question on SO:

Typically, you can just ignore this. To guarantee that only qualifying rows are locked, you could nest the predicate(s) in a CTE or a subquery with the OFFSET 0 hack (prevents inlining). Example:

Or (cheaper for sequential scans) nest the conditions in a CASE statement like:

However the CASE trick would also keep Postgres from using an index on status. If such an index is available, you don't need extra nesting to begin with: only qualifying rows will be locked in an index scan.

If the command is part of a long transaction, consider session-level locks that can be (and have to be) released manually. So you can unlock as soon as you are done with the locked row: pg_try_advisory_lock() and pg_advisory_unlock() .

Once acquired at session level, an advisory lock is held until explicitly released or the session ends.

With SKIP LOCKED. any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table.

So the statement becomes:

If there is no unlocked row left, nothing happens in this query and you get an empty result. All relevant rows are being processed. For uncritical operations that means you are done here.

Wile that doesn't return true. one or more rows are still being processed and transactions could still be rolled back. Wait a bit, then loop the two steps: UPDATE till you get no row back, SELECT. until you get TRUE .

Одобрено лучшими российскими программистами

Оператор SELECT имеет следующую структуру:

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

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

• Используя ключевое слово AS. выражению в SELECT можно присвоить псевдоним. Псевдоним используется в качестве имени столбца в данном выражении и может применяться в ORDER BY или HAVING. Например:
• Псевдонимы столбцов нельзя использовать в выражении WHERE. поскольку находящиеся в столбцах величины на момент выполнения WHERE могут быть еще не определены. See section A.5.4 Проблемы с alias.
• Выражение FROM table_references задает таблицы, из которых надлежит извлекать строки. Если указано имя более чем одной таблицы, следует выполнить объединение. Информацию о синтаксисе объединения можно найти в разделе section 6.4.1.1 Синтаксис оператора JOIN. Для каждой заданной таблицы по желанию можно указать псевдоним. В версии MySQL 3.23.12 можно указывать, какие именно индексы (ключи) MySQL должен применять для извлечения информации из таблицы. Это полезно, если оператор EXPLAIN (выводящий информацию о структуре и порядке выполнения запроса SELECT ), показывает, что MySQL использует ошибочный индекс. Если нужно. чтобы для поиска записи в таблице применялся только один из указанных индексов, следует задать значение этого индекса в USE INDEX ( key_list ). Альтернативное выражение IGNORE INDEX (key_list) запрещает использование в MySQL данного конкретного индекса. Выражения USE/IGNORE KEY являются синонимами для USE/IGNORE INDEX.
• Ссылки на столбцы могут задаваться в виде col_name. tbl_name.col_name или db_name.tbl_name.col_name. В выражениях tbl_name или db_name.tbl_name нет необходимости указывать префикс для ссылок на столбцы в команде SELECT. если эти ссылки нельзя истолковать неоднозначно. See section 6.1.2 Имена баз данных, таблиц, столбцов, индексы псевдонимы. где приведены примеры неоднозначных случаев, для которых требуются более четкие определения ссылок на столбцы.
• Ссылку на таблицу можно заменить псевдонимом, используя tbl_name [AS] alias_name.
• В выражениях ORDER BY и GROUP BY для ссылок на столбцы, выбранные для вывода информации, можно использовать либо имена столбцов, либо их псевдонимы, либо их позиции (местоположения). Нумерация позиций столбцов начинается с 1. Для того чтобы сортировка производилась в обратном порядке, в утверждении ORDER BY к имени заданного столбца, в котором производится сортировка, следует добавить ключевое слово DESC (убывающий). По умолчанию принята сортировка в возрастающем порядке, который можно задать явно при помощи ключевого слова ASC.
• В выражении WHERE можно использовать любую из функций, которая поддерживается в MySQL. See section 6.3 Функции, используемые в операторах SELECT и WHERE. Выражение HAVING может ссылаться на любой столбец или псевдоним, упомянутый в выражении select_expression. HAVING отрабатывается последним, непосредственно перед отсылкой данных клиенту, и без какой бы то ни было оптимизации. Не используйте это выражение для определения того, что должно быть определено в WHERE. Например, нельзя задать следующий оператор: Вместо этого следует задавать: В версии MySQL 3.22.5 или более поздней можно также писать запросы, как показано ниже: В более старых версиях MySQL вместо этого можно указывать:
• Параметры (опции) DISTINCT. DISTINCTROW и ALL указывают, должны ли возвращаться дублирующиеся записи. По умолчанию установлен параметр ( ALL ), т.е. возвращаются все встречающиеся строки. DISTINCT и DISTINCTROW являются синонимами и указывают, что дублирующиеся строки в результирующем наборе данных должны быть удалены.
• Все параметры, начинающиеся с SQL_. STRAIGHT_JOIN и HIGH_PRIORITY. представляют собой расширение MySQL для ANSI SQL.
• При указании параметра HIGH_PRIORITY содержащий его оператор SELECT будет иметь более высокий приоритет, чем команда обновления таблицы. Нужно только использовать этот параметр с запросами, которые должны выполняться очень быстро и сразу. Если таблица заблокирована для чтения, то запрос SELECT HIGH_PRIORITY будет выполняться даже при наличии команды обновления, ожидающей, пока таблица освободится.
• Параметр SQL_BIG_RESULT можно использовать с GROUP BY или DISTINCT. чтобы сообщить оптимизатору, что результат будет содержать большое количество строк. Если указан этот параметр, MySQL при необходимости будет непосредственно использовать временные таблицы на диске, однако предпочтение будет отдаваться не созданию временной таблицы с ключом по элементам GROUP BY. а сортировке данных.
• При указании параметра SQL_BUFFER_RESULT MySQL будет заносить результат во временную таблицу. Таким образом MySQL получает возможность раньше снять блокировку таблицы; это полезно также для случаев, когда для посылки результата клиенту требуется значительное время.
• Параметр SQL_SMALL_RESULT является опцией, специфической для MySQL. Данный параметр можно использовать с GROUP BY или DISTINCT. чтобы сообщить оптимизатору, что результирующий набор данных будет небольшим. В этом случае MySQL для хранения результирующей таблицы вместо сортировки будет использовать быстрые временные таблицы. В версии MySQL 3.23 указывать данный параметр обычно нет необходимости.
• Параметр SQL_CALC_FOUND_ROWS возвращает количество строк, которые вернул бы оператор SELECT. если бы не был указан LIMIT. Искомое количество строк можно получить при помощи SELECT FOUND_ROWS(). See section 6.3.6.2 Разные функции.
• Параметр SQL_CACHE предписывает MySQL сохранять результат запроса в кэше запросов при использовании SQL_QUERY_CACHE_TYPE=2 ( DEMAND ). See section 6.9 Кэш запросов в MySQL.
• Параметр SQL_NO_CACHE запрещает MySQL хранить результат запроса в кэше запросов. See section 6.9 Кэш запросов в MySQL.
• При использовании выражения GROUP BY строки вывода будут сортироваться в соответствии с порядком, заданным в GROUP BY. - так, как если бы применялось выражение ORDER BY для всех полей, указанных в GROUP BY. В MySQL выражение GROUP BY расширено таким образом, что для него можно также указывать параметры ASC и DESC.
• Расширенный оператор GROUP BY в MySQL обеспечивает, в частности, возможность выбора полей, не упомянутых в выражении GROUP BY. Если ваш запрос не приносит ожидаемых результатов, прочтите, пожалуйста, описание GROUP BY. See section 6.3.7 Функции, используемые в операторах GROUP BY.
• При указании параметра STRAIGHT_JOIN оптимизатор будет объединять таблицы в том порядке, в котором они перечислены в выражении FROM. Применение данного параметра позволяет увеличить скорость выполнения запроса, если оптимизатор производит объединение таблиц неоптимальным образом. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT ).
• Выражение LIMIT может использоваться для ограничения количества строк, возвращенных командой SELECT. LIMIT принимает один или два числовых аргумента. Эти аргументы должны быть целочисленными константами. Если заданы два аргумента, то первый указывает на начало первой возвращаемой строки, а второй задает максимальное количество возвращаемых строк. При этом смещение начальной строки равно 0 (не 1 ): Если задан один аргумент, то он показывает максимальное количество возвращаемых строк: Другими словами, LIMIT n эквивалентно LIMIT 0,n.
• Оператор SELECT может быть представлен в форме SELECT. INTO OUTFILE 'file_name'. Эта разновидность команды осуществляет запись выбранных строк в файл, указанный в file_name. Данный файл создается на сервере и до этого не должен существовать (таким образом, помимо прочего, предотвращается разрушение таблиц и файлов, таких как `/etc/passwd' ). Для использования этой формы команды SELECT необходимы привилегии FILE. Форма SELECT. INTO OUTFILE главным образом предназначена для выполнения очень быстрого дампа таблицы на серверном компьютере. Команду SELECT. INTO OUTFILE нельзя применять, если необходимо создать результирующий файл на ином хосте, отличном от серверного. В таком случае для генерации нужного файла вместо этой команды следует использовать некоторую клиентскую программу наподобие mysqldump --tab или mysql -e "SELECT. " > outfile. Команда SELECT. INTO OUTFIL E является дополнительной по отношению к LOAD DATA INFIL E; синтаксис части export_options этой команды содержит те же выражения FIELDS и LINES. которые используются в команде LOAD DATA INFIL E. See section 6.4.9 Синтаксис оператора LOAD DATA INFILE. Следует учитывать, что в результирующем текстовом файле оператор ESCAPED BY экранирует только следующие символы:
  • Символ оператора ESCAPED BY
  • Первый символ оператора FIELDS TERMINATED BY
  • Первый символ оператора LINES TERMINATED BY
  Помимо этого ASCII-символ 0 конвертируется в ESCAPED BY. за которым следует символ `0' (ASCII 48). Это делается потому, что необходимо экранировать любые символы операторов FIELDS TERMINATED BY. ESCAPED BY или LINES TERMINATED BY. чтобы иметь надежную возможность повторить чтение этого файла. ASCII 0 экранируется, чтобы облегчить просмотр файла с помощью программ вывода типа pager. Поскольку результирующий файл не должен удовлетворять синтаксису SQL, нет необходимости экранировать что-либо еще. Ниже приведен пример того, как получить файл в формате, который используется многими старыми программами.
• Если вместо INTO OUTFILE использовать INTO DUMPFILE. то MySQL запишет в файл только одну строку без символов завершения столбцов или строк и без какого бы то ни было экранирования. Это полезно для хранения данных типа BLOB в файле.
• Следует учитывать, что любой файл, созданный с помощью INTO OUTFILE и INTO DUMPFILE. будет доступен для чтения всем пользователям! Причина этого заключается в следующем: сервер MySQL не может создавать файл, принадлежащий только какому-либо текущему пользователю (вы никогда не можете запустить mysqld от пользователя root ), соответственно, файл должен быть доступен для чтения всем пользователям. При использовании FOR UPDATE с обработчиком таблиц, поддерживающим блокировку страниц/строк, выбранные строки будут заблокированы для записи.

Поиск по документации:

MySQL: 6

Оператор SELECT имеет следующую структуру:

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

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

Используя ключевое слово AS. выражению в SELECT можно присвоить псевдоним. Псевдоним используется в качестве имени столбца в данном выражении и может применяться в ORDER BY или HAVING. Например:

Псевдонимы столбцов нельзя использовать в выражении WHERE. поскольку находящиеся в столбцах величины на момент выполнения WHERE могут быть еще не определены. See Раздел A.5.4, «Проблемы с alias ».

Выражение FROM table_references задает таблицы, из которых надлежит извлекать строки. Если указано имя более чем одной таблицы, следует выполнить объединение. Информацию о синтаксисе объединения можно найти в разделе Раздел 6.4.1.1, «Синтаксис оператора JOIN ». Для каждой заданной таблицы по желанию можно указать псевдоним.

В версии MySQL 3.23.12 можно указывать, какие именно индексы (ключи) MySQL должен применять для извлечения информации из таблицы. Это полезно, если оператор EXPLAIN (выводящий информацию о структуре и порядке выполнения запроса SELECT ), показывает, что MySQL из списка возможных индексов выбрал неправильный. Если нужно. чтобы для поиска записи в таблице применялся только один из возможных индексов, следует задать значение этого индекса в USE INDEX ( key_list ). Альтернативное выражение IGNORE INDEX (key_list) запрещает использование в MySQL данного конкретного индекса.

В MySQL 4.0.9 можно также указывать FORCE INDEX. Это работает также, как и USE INDEX (key_list) но в дополнение дает понять серверу что полное сканирование таблицы будет ОЧЕНЬ дорогостоящей операцией. Другими словами, в этом случае сканирование таблицы будет использовано только тогда, когда не будет найдено другого способа использовать один из данных индексов для поиска записей в таблице.

Выражения USE/IGNORE KEY являются синонимами для USE/IGNORE INDEX.

Ссылки на таблицы могут даваться как tbl_name (в рамках текущей базы данных), или как dbname.tbl_name с тем, чтобы четко указать базу данных.

Ссылки на столбцы могут задаваться в виде col_name. tbl_name.col_name или db_name.tbl_name.col_name. В выражениях tbl_name или db_name.tbl_name нет необходимости указывать префикс для ссылок на столбцы в команде SELECT. если эти ссылки нельзя истолковать неоднозначно. See Раздел 6.1.2, «Имена баз данных, таблиц, столбцов, индексы псевдонимы». где приведены примеры неоднозначных случаев, для которых требуются более четкие определения ссылок на столбцы.

Ссылку на таблицу можно заменить псевдонимом, используя tbl_name [AS] alias_name.

В выражениях ORDER BY и GROUP BY для ссылок на столбцы, выбранные для вывода информации, можно использовать либо имена столбцов, либо их псевдонимы, либо их позиции (местоположения). Нумерация позиций столбцов начинается с 1.

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

В выражении WHERE можно использовать любую из функций, которая поддерживается в MySQL. See Раздел 6.3, «Функции, используемые в операторах SELECT и WHERE ».

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

Вместо этого следует задавать:

В версии MySQL 3.22.5 или более поздней можно также писать запросы, как показано ниже:

В более старых версиях MySQL вместо этого можно указывать:

Параметры (опции) DISTINCT. DISTINCTROW и ALL указывают, должны ли возвращаться дублирующиеся записи. По умолчанию установлен параметр ( ALL ), т.е. возвращаются все встречающиеся строки. DISTINCT и DISTINCTROW являются синонимами и указывают, что дублирующиеся строки в результирующем наборе данных должны быть удалены.

Все параметры, начинающиеся с SQL_. STRAIGHT_JOIN и HIGH_PRIORITY. представляют собой расширение MySQL для ANSI SQL.

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

Параметр SQL_BIG_RESULT можно использовать с GROUP BY или DISTINCT. чтобы сообщить оптимизатору, что результат будет содержать большое количество строк. Если указан этот параметр, MySQL при необходимости будет непосредственно использовать временные таблицы на диске, однако предпочтение будет отдаваться не созданию временной таблицы с ключом по элементам GROUP BY. а сортировке данных.

При указании параметра SQL_BUFFER_RESULT MySQL будет заносить результат во временную таблицу. Таким образом MySQL получает возможность раньше снять блокировку таблицы; это полезно также для случаев, когда для посылки результата клиенту требуется значительное время.

Параметр SQL_SMALL_RESULT является опцией, специфической для MySQL. Данный параметр можно использовать с GROUP BY или DISTINCT. чтобы сообщить оптимизатору, что результирующий набор данных будет небольшим. В этом случае MySQL для хранения результирующей таблицы вместо сортировки будет использовать быстрые временные таблицы. В версии MySQL 3.23 указывать данный параметр обычно нет необходимости.

Параметр SQL_CALC_FOUND_ROWS (MySQL 4.0.0 и более новый) возвращает количество строк, которые вернул бы оператор SELECT. если бы не был указан LIMIT. Искомое количество строк можно получить при помощи SELECT FOUND_ROWS(). See Раздел 6.3.6.2, «Разные функции».

Заметьте, что в версиях MySQL до 4.1.0 это не работает с LIMIT 0. который оптимизирован для того, чтобы немедленно вернуть нулевой результат. See Раздел 5.2.8, «Как MySQL оптимизирует LIMIT ».

Параметр SQL_CACHE предписывает MySQL сохранять результат запроса в кэше запросов при использовании QUERY_CACHE_TYPE=2 ( DEMAND ). See Раздел 6.9, «Кэш запросов в MySQL».

Параметр SQL_NO_CACHE запрещает MySQL хранить результат запроса в кэше запросов. See Раздел 6.9, «Кэш запросов в MySQL».

При использовании выражения GROUP BY строки вывода будут сортироваться в соответствии с порядком, заданным в GROUP BY. - так, как если бы применялось выражение ORDER BY для всех полей, указанных в GROUP BY. В MySQL выражение GROUP BY расширено таким образом, что для него можно также указывать параметры ASC и DESC.

Расширенный оператор GROUP BY в MySQL обеспечивает, в частности, возможность выбора полей, не упомянутых в выражении GROUP BY. Если ваш запрос не приносит ожидаемых результатов, прочтите, пожалуйста, описание GROUP BY. See Раздел 6.3.7, «Функции, используемые в операторах GROUP BY ».

При указании параметра STRAIGHT_JOIN оптимизатор будет объединять таблицы в том порядке, в котором они перечислены в выражении FROM. Применение данного параметра позволяет увеличить скорость выполнения запроса, если оптимизатор производит объединение таблиц неоптимальным образом. See Раздел 5.2.1, «Синтаксис оператора EXPLAIN (получение информации о SELECT )».

Выражение LIMIT может использоваться для ограничения количества строк, возвращенных командой SELECT. LIMIT принимает один или два числовых аргумента. Эти аргументы должны быть целочисленными константами. Если заданы два аргумента, то первый указывает на начало первой возвращаемой строки, а второй задает максимальное количество возвращаемых строк. При этом смещение начальной строки равно 0 (не 1 ):

Для совместимости с PostgreSQL MySQL также поддерживает синтаксис LIMIT # OFFSET #.

Для того, чтобы выбрать все строки с определенного смещения и до конца результата, вы можете использовать значение -1 в качестве второго параметра:

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

Другими словами, LIMIT n эквивалентно LIMIT 0,n.

Оператор SELECT может быть представлен в форме SELECT. INTO OUTFILE 'file_name'. Эта разновидность команды осуществляет запись выбранных строк в файл, указанный в file_name. Данный файл создается на сервере и до этого не должен существовать (таким образом, помимо прочего, предотвращается разрушение таблиц и файлов, таких как /etc/passwd ). Для использования этой формы команды SELECT необходимы привилегии FILE. Форма SELECT. INTO OUTFILE главным образом предназначена для выполнения очень быстрого дампа таблицы на серверном компьютере. Команду SELECT. INTO OUTFILE нельзя применять, если необходимо создать результирующий файл на ином хосте, отличном от серверного. В таком случае для генерации нужного файла вместо этой команды следует использовать некоторую клиентскую программу наподобие mysqldump --tab или mysql -e "SELECT. " > outfile. Команда SELECT. INTO OUTFILE является дополнительной по отношению к LOAD DATA INFILE ; синтаксис части export_options этой команды содержит те же выражения FIELDS и LINES. которые используются в команде LOAD DATA INFILE. See Раздел 6.4.9, «Синтаксис оператора LOAD DATA INFILE ». Следует учитывать, что в результирующем текстовом файле оператор ESCAPED BY экранирует только следующие символы:

Символ оператора ESCAPED BY

Первый символ оператора FIELDS TERMINATED BY

Первый символ оператора LINES TERMINATED BY

Помимо этого ASCII-символ 0 конвертируется в ESCAPED BY. за которым следует символ ' 0 ' (ASCII 48). Это делается потому, что необходимо экранировать любые символы операторов FIELDS TERMINATED BY. ESCAPED BY или LINES TERMINATED BY. чтобы иметь надежную возможность повторить чтение этого файла. ASCII 0 экранируется, чтобы облегчить просмотр файла с помощью программ вывода типа pager. Поскольку результирующий файл не должен удовлетворять синтаксису SQL, нет необходимости экранировать что-либо еще. Ниже приведен пример того, как получить файл в формате, который используется многими старыми программами.

Если вместо INTO OUTFILE использовать INTO DUMPFILE. то MySQL запишет в файл только одну строку без символов завершения столбцов или строк и без какого бы то ни было экранирования. Это полезно для хранения данных типа BLOB в файле.

Следует учитывать, что любой файл, созданный с помощью INTO OUTFILE и INTO DUMPFILE. будет доступен для записи всем пользователям! Причина этого заключается в следующем: сервер MySQL не может создавать файл, принадлежащий только какому-либо текущему пользователю (вы никогда не можете запустить mysqld от пользователя root ), соответственно, файл должен быть доступен для записи всем пользователям.

При использовании FOR UPDATE с обработчиком таблиц, поддерживающим блокировку страниц/строк, выбранные строки будут заблокированы для записи.

20 советов по оптимальному использованию MySQL

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

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

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

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

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

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

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

После добавления индекса для поля group_id:

Теперь вместо сканирования 7883 строк, будут сканироваться только 9 и 16 строк из двух таблиц. Хорошим методом оценки производительности является умножение всех чисел в столбце “rows”. Результат примерно пропорционален прорабатываемому объему данных.

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

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

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

Как вы можете видеть, данное правило применимо и к поиску по части строки, например, “last_name LIKE ‘a%’”. Когда для поиска используется начало строки, MySQL может использовать индекс столбца, по которому проводится поиск.

Вам также следует разобраться, для каких видов поиска нельзя использовать обычное индексирование. Например, при поиске слова ( “WHERE post_content LIKE ‘%apple%’”) преимущества индексирования будут не доступны. В таких случая лучше использовать полнотекстовый поиск mysql или построение собственных решений на основе индексирования.

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

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

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

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

Так вы получаете случайное число, которое меньше, чем количество строк в результате запроса, и используете его как смещение в предложении LIMIT.

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

Хорошей привычкой является указание столбца при выполнении SELECT.

Хорошей практикой является использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип из семейства INT. Предпочтительно - UNSIGNED, так как в этом случае значение не может быть отрицательным.

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

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

Одним возможным исключением из данного правила являются “ассоциативные таблицы”, которые используются для отношений многие-ко-многим между двумя другими таблицами. Например, таблица “posts_tags” содержит 2 столбца: post_id, tag_id. Они используются для описания отношений между двумя таблицами “post” и “tags”. Описанная таблица может иметь основной ключ, который содержит оба поля id.

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

Если у вас есть поля, которые содержат только несколько различных видов значений, используйте для них ENUM вместо VARCHAR. Например, может быть столбец с именем “status”, который будет содержать только такие значения как “active”, “inactive”, “pending”, “expired” и так далее.

MySQL может “предложить” способ изменения структуры вашей таблицы. Когда вы создаете поле VARCHAR, то наверняка "предложение" будет содержать рекомендацию сменить тип столбца на ENUM. "Предложения" получаются в ходе выполнения вызова PROCEDURE ANALYSE().

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

Например, если вы создали поле типа INT для основного ключа, но в таблице не так много записей, то "предложение" может содержать рекомендацию сменить тип поля на MEDIUMINT. Или если вы используете поле типа VARCHAR, то можете получить "предложение" конвертировать его в ENUM, если в нем содержится только несколько значений.

Вы также можете получить рекомендации, если нажмете ссылку “Propose table structure” (Анализ структуры таблицы) в PhpMyAdmin на закладке структуры таблицы.

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

Если нет особых причин использовать значение NULL, нужно всегда использовать для столбца свойство NOT NULL.

Спросите себя, есть ли разница между пустой строкой и значением NULL (для полей типа INT: 0 и NULL). Если нет причин использовать оба значения, то нет необходимости иметь поле NULL. (Вы знаете, что Oracle рассматривает NULL и пустую строку как одинаковые величины?)

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

Из документации MySQL:

“Столбец NULL требует дополнительного пространства в строке для записи о возможном значении NULL. Для таблиц MyISAM каждый столбец NULL использует дополнительный бит, округление проводится до ближайшего байта.”

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

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

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

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

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

Для использования подготовленных выражений в PHP можно использовать расширение mysqli или PDO.

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

Отличное объяснение функции mysql_unbuffered_query() из документации PHP:

“mysql_unbuffered_query() отправляет SQL запрос на сервер MySQL без автоматического получения и буферирования строк результата, как это делает функция mysql_query(). Таким образом, сохраняется определенный объем памяти запросами SQL, которые выдают большой набор результата, и можно начинать работать с набором результата сразу же после получения первой строки, не дожидаясь пока запрос SQL будет полностью выполнен.”

Однако существует несколько ограничений. Вы должны либо прочитать все строки либо вызвать mysql_free_result() перед тем, как выполнить следующий запрос. Также нельзя использовать mysql_num_rows() или mysql_data_seek() для набора результата.

Многие программисты создают поле VARCHAR(15) для хранения IP адреса, даже не задумываясь о том, что будут хранить в этом поле целочисленное значение. Если использовать INT, то размер поля сократится до 4 байт, и оно будет иметь фиксированную длину.

Нужно использовать тип UNSIGNED INT, так как IP адрес задействует все 32 бита беззнакового целого.

В запросах можно использовать функцию INET_ATON() для конвертации IP адреса в целое, и INET_NTOA() для обратного процесса. Также есть схожие функции PHP: ip2long() и long2ip().

Когда каждый отдельный столбец в таблице имеет фиксированную длину, то вся таблица в целом рассматривается как “static” или “с фиксированной длиной записи”. Примеры типов столбцов, которые не имеют фиксированной длины: VARCHAR, TEXT, BLOB. Если вы включите хотя бы один столбец с таким типом, то таблица перестает рассматриваться как "static" и будет по-другому обрабатываться механизмом MySQL.

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

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

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

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

Пример 1. У вас есть таблица, которая содержит домашние адреса, редко используемые в приложении. Вы можете разделить вашу таблицу и хранить адреса в отдельной таблице. Таким образом основная таблица пользователей сократится в размере. А как известно, меньшая таблица обрабатывается быстрее.

Пример 2. У вас в таблице есть поле “last_login”. Оно обновляется каждый раз, когда пользователь регистрируется на сайте. Но каждое обновление таблицы вызывает кэширование запроса, что может создать перегрузку системы. Вы можете выделить данное поле в другую таблицу, чтобы сделать обновления таблицы пользователей не такими частыми.

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

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

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

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

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

Для механизма базы данных диск является наиболее важным узким местом. Стремление сделать все более компактным и маленьким обычно хорошо сказывается в сфере производительности за счет сокращения объема перемещаемых данных.

Документация MySQL содержит список норм хранения данных для всех типов.

Если таблица будет содержать всего несколько строк, то нет причин делать основной ключ типа INT, а не MEDIUMINT, SMALLINT или даже TINYINT. если вам нужна только дата, используйте DATE вместо DATETIME.

Нужно только помнить о возможностях роста.

Есть два основных механизма хранения данных для MySQL: MyISAM и InnoDB. Каждый имеет свои достоинства и недостатки.

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

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

Использование объектно-реляционного отображения (ORM - Object Relational Mapper) дает ряд преимуществ. Все, что можно сделать в ORM. можно сделать вручную, но с большими усилиями и более высокими требованиями к уровню разработчика.

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

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

Для PHP можно использовать ORM Doctrine.

Постоянные соединения предназначены для сокращения потерь на восстановление соединений к MySQL. Когда создается постоянное соединение, то оно остается открытым даже после завершения скрипта. Так как Apache повторно использует дочерние процессы, то процесс выполняется для нового скрипта, и он использует тоже соединение с MySQL.

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

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

Данный урок подготовлен для вас командой сайта ruseller.com

Перевел: Сергей Фастунов