5.2.12 Другие советы по оптимизации

Несортированные советы для повышения скорости систем:

  • Используйте постоянные соединения с базой данных, чтобы избежать издержек на подключения. Если невозможно использовать постоянные соединения и осуществляется большое количество новых подключений к базе данных, то можно изменить значение переменной thread_cache_size. See section 5.5.2 Настройка параметров сервера.
  • Всегда проверяйте, чтобы все ваши запросы действительно использовали созданные вами в таблицах индексы. В MySQL это можно сделать с помощью команды EXPLAIN. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT).
  • Старайтесь избегать сложных запросов SELECT на часто обновляемых таблицах типа MyISAM. Это помогает избежать проблем с блокировкой таблиц.
  • Новые таблицы MyISAM могут вносить записи в таблицу без удаляемых записей, которые в то же самое время читает иная таблица. Если это имеет для вас значение, то следует рассмотреть методы, не требующие удаления записей или запускать OPTIMIZE TABLE после удаления большого количества строк.
  • Используйте ALTER TABLE ... ORDER BY expr1,expr2... если вы большей частью извлекаете записи в порядке expr1,expr2... Можно получить более высокую производительность, используя эту опцию после больших преобразований в таблице.
  • В некоторых случаях может оказаться целесообразным введение столбца, базирующегося на "хэшированной" информации из других столбцов. Если этот столбец невелик и достаточно уникален, то он может быть намного быстрее, чем большой индекс на многих столбцах. В MySQL очень просто использовать подобный дополнительный столбец: SELECT * FROM table_name WHERE hash=MD5(CONCAT(col1,col2)) AND col_1='constant' AND col_2='constant'
  • Для часто изменяющихся таблиц следует избегать типов VARCHAR или BLOB для всех столбцов. При использовании единичного столбца VARCHAR или BLOB вы получите динамическую длину строки. See section 7 Типы таблиц MySQL.
  • Разделение таблицы на несколько различных таблиц просто потому, что строки получаются "большими", обычно не приносит пользы. Чтобы получить доступ к строке, наиболее трудоемким оказывается поиск по диску для нахождения первого байта этой строки. После нахождения этих данных большинство новых дисков могут прочесть всю строку достаточно быстро для большинства приложений. Разделение таблицы имеет значение только в следующих случаях: когда это таблица с динамическим размером строки (смотрите выше), которую можно изменить на строку фиксированного размера, или когда необходимо просматривать таблицу очень часто и нет необходимости в большинстве столбцов. See section 7 Типы таблиц MySQL.
  • Если очень часто приходится производить вычисления, базирующиеся на информации из большого количества строк (такие как подсчет предметов), то, вероятно, намного лучше ввести новую таблицу и обновлять счетчик в режиме реального времени. Обновление вида UPDATE table set count=count+1 where index_column=constant является очень быстрым! Это действительно важно при использовании баз данных вроде MySQL, имеющих только блокирование таблиц (многочисленные читающие/единственный записывающий). Для многих баз данных это обеспечит также более высокую производительность, поскольку программа управления блокировкой строк в этом случае будет иметь меньше работы.
  • Если необходимо собирать статистические данные из больших журнальных таблиц, то используйте сводные таблицы вместо сканирования целой таблицы. Поддерживать сводные таблицы должно быть намного быстрее, чем пытаться сделать ``живую'' статистику. Намного быстрее воспроизвести новые сводные таблицы из журналов, когда что-либо изменяется (в зависимости от деловых решений), чем изменять работающее приложение!
  • Если возможно, необходимо классифицировать отчеты как "реальные" или "статистические", где данные, необходимые для статистических отчетов, генерируются только на основе сводных таблиц, которые формируются из реальных данных.
  • Воспользуйтесь преимуществом того факта, что столбцы имеют значения по умолчанию. Вносите величины явно только тогда, когда значения вносимых величин отличаются от установленных по умолчанию. Это уменьшает объем анализа, который необходимо произвести в MySQL, и улучшает скорость внесения.
  • В некоторых случаях удобно упаковывать и хранить данные в столбцах BLOB. В этом случае необходимо добавить дополнительный код для запаковывания в BLOB и распаковывания обратно, но на некотором этапе это может сэкономить много обращений. Это практично, когда ваши данные не согласуются со структурой статической таблицы.
  • Обычно следует стремиться сохранять все данные в безизбыточной форме (которая называется 3-й нормальной формой в теории баз данных), но не следует опасаться дублирования данных или создания сводных таблиц, если это необходимо для достижения большей скорости.
  • Хранимые процедуры или UDF (функции, определяемые пользователем) могут быть хорошим способом получить большую производительность. В этом случае, однако, следует иметь в запасе некоторый иной (более медленный) путь, если используемая вами база данных не поддерживает этих возможностей.
  • Вы всегда можете кое-чего достичь путем кэширования запросов/ответов в своем приложении и стараясь выполнить много вставок/обновлений в одно и то же время. Если ваша база данных поддерживает блокировку таблиц (как MySQL и Oracle), то это должно помочь гарантировать, что кэш индексов сбрасывается только однажды после всех обновлений.
  • Используйте INSERT /*! DELAYED */, если нет необходимости знать, когда ваши данные записываются. Это повысит скорость работы, поскольку многие табличные записи могут быть внесены с помощью одной дисковой записи.
  • Используйте INSERT /*! LOW_PRIORITY */, если хотите сделать ваши выборки более важными.
  • Используйте SELECT /*! HIGH_PRIORITY */, чтобы получить выборки, которые перепрыгивают очередь. То есть, выборка выполняется, даже если кто-либо ожидает, чтобы сделать запись.
  • Используйте многострочную команду INSERT для хранения многих строк в одной SQL-команде (многие SQL-серверы поддерживают это).
  • Используйте LOAD DATA INFILE для загрузки больших количеств данных. Это быстрее, чем обычные вставки и будет еще быстрее при интеграции myisamchk в mysqld.
  • Используйте столбцы AUTO_INCREMENT, чтобы сделать величины уникальными.
  • Используйте время от времени OPTIMIZE TABLE, чтобы избежать фрагментации при использовании динамического табличного формата. See section 4.5.1 Синтаксис команды OPTIMIZE TABLE.
  • Используйте таблицы HEAP, чтобы получить более высокую скорость, когда это возможно. See section 7 Типы таблиц MySQL.
  • При использовании нормальной установки веб-сервера рисунки должны храниться как файлы. То есть, храните в базе данных только ссылку на файл. Главная причина этого состоит в том, что обычный веб-сервер намного лучше кэширует файлы, чем содержание базы данных. Таким образом, при использовании файлов намного легче получить быструю систему.
  • Используйте в памяти таблицы для неответственных данных, к которым часто обращаются (таким, как информация о последнем показанном баннере для пользователей, не имеющих cookies).
  • Столбцы с идентичной информацией в различных таблицах должны объявляться одинаково и иметь одинаковые имена. До версии 3.23 в противном случае получались медленные соединения (slow joins). Старайтесь сохранять имена простыми (используйте name вместо customer_name в таблице customer). Чтобы ваши имена были переносимыми на другие SQL-серверы, они должны быть короче, чем 18 символов.
  • Если вам действительно нужна высокая скорость, вы должны взглянуть на интерфейсы нижнего уровня для хранения данных, поддерживаемые различными SQL-серверами! Например, обращаясь к таблицам MyISAM в MySQL напрямую, можно было бы получить увеличение скорости в 2-5 раз по сравнению с использованием интерфейса SQL. Для возможности сделать это, данные должны находиться на том же самом сервере, что и приложение, и, обычно, должны иметь доступ только в одном процессе обработки (поскольку внешняя файловая блокировка действительно медленна). Можно было бы избавиться от вышеуказанных проблем введением низко-уровневых команд MyISAM в сервере MySQL (это был бы один из простых путей получить большую производительность, если необходимо). Путем тщательного проектирования интерфейса базы данных было бы достаточно просто поддерживать этот тип оптимизации.
  • Во многих случаях быстрее получить доступ к данным из базы данных (используя действующее соединение), чем обращаться к текстовому файлу, просто из-за того, что база данных, вероятно, более компактна, чем текстовый файл (если вы используете числовые данные), и это приведет к меньшему количеству обращений к диску. Вы также сэкономите на коде, поскольку не должны анализировать текстовые файлы, чтобы найти границы строк и столбцов.
  • Для увеличения скорости можно также использовать репликацию. See section 4.10 Репликация в MySQL.
  • Объявление таблицы с DELAY_KEY_WRITE=1 сделает обновление индексов более быстрым, так как они не записываются на диск, пока файл закрыт. Обратная сторона этого заключается в том, что необходимо запускать myisamchk на этих таблицах перед началом работы mysqld, для уверенности, что все в порядке, если что-либо уничтожит mysqld в середине работы. Поскольку ключевая информация всегда может быть воспроизведена из данных, то вы не должны что-либо потерять при использовании DELAY_KEY_WRITE.