PHP5

При использовании обработчика таблиц MyISAM MySQL применяет очень быструю блокировку таблиц (несколько потоков чтения/один поток записи). Самая большая проблема при использовании этого типа таблиц возникает в случае непрерывного потока обновлений в сочетании с медленными выборками из одной и той же таблицы. Если эта проблема касается лишь некоторых таблиц, можно использовать вместо них таблицы другого типа. See Глава 7, Типы таблиц MySQL.

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

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

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

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

Чтобы сделать сложное приложение переносимым в области SQL, вам следует выбрать те SQL-серверы, с которыми оно должно работать.

Чтобы узнать, какие функции, типы и ограничения существуют в выбранных вами серверах, можно воспользоваться приложением MySQL crash-me. crash-me пока еще далека от того, чтобы тестировать все, что возможно, но тем не менее, является достаточно качественным сравнительным тестом по более чем 450 характеристикам.

Например, если вы хотите использовать Informix или DB2, имена полей не должны быть длиннее 18 символов.

И тесты MySQL (MySQL benchmarks), и программа crash-me являются достаточно независимыми от конкретной СУБД. Ознакомившись с тем, как мы решили этот вопрос, вы можете получить представление о том, как следует писать переносимые программы для работы с базами данных. Тесты можно найти в каталоге sql-bench в поставке исходных текстов MySQL. Они написаны на Perl с использованием интерфейса DBI (который, кстати, уже решает проблему получения доступа к разным базам данных).

См. http://www.mysql.com/information/benchmarks.html - там находятся результаты тестов.

Как можно видеть по этим результатам, у каждой СУБД есть свои слабые стороны. Все они построены по-разному и спроектированы с учетом различных компромиссов, что приводит к различиям в поведении этих систем.

Если независимость от СУБД для вас очень важна, вам нужно хорошо ощущать, где находятся слабые места в каждом сервере. MySQL - очень быстрый сервер, если речь идет о выборках/вставках, но у нас все еще есть проблемы, когда с одной таблицей в смешанном режиме работают медленные клиенты, делающие выборки и обновления. С другой стороны, при работе в Oracle возникают большие проблемы, когда вы хотите получить доступ к строке, которую только что обновили (до тех пор, пока она не будет сохранена на диске). Транзакционные базы данных обычно не очень подходят для генерации отчетов по файлам журналов, так как в этом случае блокировки совершенно бесполезны.

Чтобы сделать свое приложение действительно не зависящим от СУБД, вам следует создать некий быстро расширяемый интерфейс, через который происходит обработка данных. Поскольку C++ доступен на большинстве систем, имеет смысл создать соответствующие классы-интерфейсы к базам данных.

Если вы используете некоторые специфические функции СУБД (скажем, REPLACE в MySQL), вам следует написать код, реализующий этот метод для других серверов SQL. С MySQL вы можете использовать такой синтаксис для того, чтобы добавить некоторые специфические для MySQL ключевые слова в запрос: /*! */. Код внутри /* */ будет проигнорирован как комментарий большинством других SQL-серверов.

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

В этом случае структура таблицы должна содержать информацию об изначальном размере кэша и то, как часто таблица должна быть обновлена в общем случае.

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

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

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

Эта проблема была решена следующим образом. Мы сохраняем всю информацию за месяц в сжатых таблицах "транзакций". У нас есть набор простых макросов (сценарий), генерирующий итоговые таблицы, сгруппированные по различным критериям (группа изделий, идентификатор заказчика, хранилище...) из таблиц транзакций. Отчеты - это веб-страницы, динамически генерирующиеся небольшим сценарием на Perl, который просматривает веб-страницу, выполняет SQL-операторы, содержащиеся в ней и вставляет результаты. Для этих целей можно было бы использовать PHP или модуль mod_perl, но в то время этих средств еще не существовало.

Для графических данных мы написали простой инструмент на C, который может создавать GIF-файлы на основе результата SQL-запроса (определенным образом обработав результат). Это также динамически выполняется из создаваемой Perl'ом странички.

В большинстве случаев новый отчет может быть создан просто путем копирования существующего сценария и модифицирования SQL-запроса в нем. Иногда требуется дополнительно добавить поля в существующую итоговую таблицу или сгенерировать новую таблицу, но это также делается очень просто, поскольку у нас все транзакционные таблицы хранятся на диске (в настоящее время у нас имеется меньшей мере 50Гб транзакционных таблиц и 200Гб других клиентских данных.)

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

У нас не было каких-либо проблем при обработке этих данных на весьма скромном Sun Ultra SPARCstation (2x200 МГц). Недавно мы заменили один из наших серверов на двухпроцессорный UltraSPARC с тактовой частотой 400 МГц и теперь планируем начать обрабатывать транзакции на уровне продукта, что будет означать десятикратное увеличение объема данных. Мы полагаем, что сможем справиться с этим объемом лишь только добавлением соответствующего количества дисков.

Помимо этого мы экспериментируем с Intel-Linux, чтобы получить больше производительности по низшей цене. Теперь, имея бинарно-переносимый формат базы данных (появившийся в версии 3.23), мы начнем использовать его для некоторых частей приложения.

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

В данном разделе будет находиться техническое описание набора эталонных тестов MySQL (и crash-me), но оно пока еще не написано. В настоящее время можно получить хорошее представление об эталонном тесте, глядя на код и результаты в каталоге sql-bench любого исходного дистрибутива MySQL.

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

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

Например (выполнено на одной машине под NT 4.0):

В предыдущем тесте MySQL запускался с 8-мегабайтным индексным кэшем.

Гораздо больше результатов тестов вы сможете найти по адресу http://www.mysql.com/information/benchmarks.html.

Обратите внимание: данные об Oracle отсутствуют - по просьбе компании Oracle вся информация по их продукту была удалена. Все эталонные тесты для Oracle должны быть пропущены через компанию Oracle! Мы считаем, однако, что при таком способе тестирования результаты эталонных тестов для Oracle будут в очень высокой степени различаться, поскольку приведенные на сайте результаты призваны показывать на что способна стандартная инсталляция для одного клиента.

Чтобы выполнить набор эталонных тестов, необходимо загрузить исходный дистрибутив MySQL, установить драйвер perl DBI, драйвер perl DBD для той базы данных, которую нужно проверить, а затем выполнить:

cd sql-bench
perl run-all-tests --server=#

где # - один из поддерживаемых серверов. Список всех опций и поддерживаемых серверов можно получить, выполнив run-all-tests --help.

Программа crash-me пытается определить, какие функции поддерживаются СУБД, и какие возможности и ограничения имеют эти функции при выполнении запросов. Например, она определяет следующее:

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

Примеры переносимых программ программы для эталонного тестирования можно найти в наборе тестов MySQL. See Раздел 5.1.4, «Набор тестов MySQL (The MySQL Benchmark Suite)». Можно взять любую программу из этого набора и модифицировать ее для своих потребностей. Таким образом можно испытывать различные решения проблемы и проверять, которое из них самое быстрое.

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

Чтобы избежать подобных проблем, нужно постараться выполнить эталонное тестирование всего приложения при самой плохой возможной нагрузке! Для этого можно использовать программу Super Smack, которая доступна по адресу: http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz. Эта программа, как следует из ее имени (smack - шлепок - прим. пер.), способна поставить систему на колени, так что используйте её только на разрабатываемых системах (проще говоря, в девелопменте).

    EXPLAIN имя_таблицы
или EXPLAIN SELECT опции_выборки

EXPLAIN имя_таблицы является синонимом операторов DESCRIBE имя_таблицы и SHOW COLUMNS FROM имя_таблицы.

Если оператор SELECT предваряется ключевым словом EXPLAIN, MySQL сообщит о том, как будет производиться обработка SELECT, и предоставит информацию о порядке и методе связывания таблиц.

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

Вы должны почаще запускать ANALYZE TABLE с тем, чтобы обновлялась статистика по таблицам, такая как кардинальность ключей, которые могут повлиять на выбор оптимизатора. See Раздел 4.5.2, «Синтаксис команды ANALYZE TABLE».

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

Для непростых соединений EXPLAIN возвращает строку информации о каждой из использованных в работе оператора SELECT таблиц. Таблицы перечисляются в том порядке, в котором они будут считываться. MySQL выполняет все связывания за один проход (метод называется "single-sweep multi-join"). Делается это так: MySQL читает строку из первой таблицы, находит совпадающую строку во второй таблице, затем - в третьей, и так далее. Когда обработка всех таблиц завершается, MySQL выдает выбранные столбцы и обходит в обратном порядке список таблиц до тех пор, пока не будет найдена таблица с наибольшим совпадением строк. Следующая строка считывается из этой таблицы и процесс продолжается в следующей таблице.

В MySQL 4.1, вывод EXPLAIN был изменен с тем, чтобы работать лучше с конструкциями типа UNION, подзапросами, и наследованными (вторичными, derived) таблицами. Наиболее заметным изменением стало введение двух новых столбцов: id и select_type.

Вывод команды EXPLAIN включает следующие столбцы:

Существует неплохой способ определить, насколько хорошим является тип связывания. Для этого нужно перемножить все значения столбца rows, выводимого командой EXPLAIN. Результатом будет грубая оценка того, сколько строк должен просмотреть MySQL для выполнения запроса. Это же число используется для ограничения запросов в переменной max_join_size. See Раздел 5.5.2, «Настройка параметров сервера».

В следующем примере показано, как можно постепенно оптимизировать JOIN при помощи информации, выводимой оператором EXPLAIN.

Предположим, что имеется представленный ниже оператор SELECT, который нужно исследовать при помощи команды EXPLAIN:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

Для этого примера принимается, что:

На начальном этапе перед выполнением какой-либо оптимизации оператор EXPLAIN выведет следующую информацию:

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)

Поскольку каждая таблица имеет тип (type) ALL, из приведенного выше вывода видно, что MySQL будет делать полное связывание всех таблиц! Это займет долгое время, поскольку для выполнения такого связывания должно быть рассмотрено произведение числа строк в каждой таблице! Для нашего случая такое произведение - 74 * 2135 * 74 * 3872 = 45268558720 строк. Если таблицы большие, трудно даже представить себе, как долго они будут связываться.

Одна проблема здесь состоит в том, что MySQL не может (пока еще) эффективно применять индексы к столбцам, если они объявлены по-разному. В этом контексте тип VARCHAR и тип CHAR - одинаковы, если они не объявлены с различной длиной. Поскольку столбец tt.ActualPC объявлен как CHAR(10), а et.EMPLOYID - как CHAR(15), имеется несоответствие по длине значений.

Чтобы устранить это несоответствие между длинами столбцов, следует использовать команду ALTER TABLE для удлинения столбца ActualPC от 10 символов до 15 символов:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Теперь оба столбца tt.ActualPC и et.EMPLOYID имеют тип VARCHAR(15). При повторном выполнении оператора EXPLAIN будет выведен следующий результат:

table type   possible_keys   key     key_len ref         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    Using where
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1

Это не идеально, но уже намного лучше (произведение значений строк (rows) теперь уменьшилось в 74 раза). Такое связывание выполнится за пару секунд.

Можно сделать еще одно изменение - чтобы устранить несоответствие длин столбцов для сравнений tt.AssignedPC = et_1.EMPLOYID и tt.ClientID = do.CUSTNMBR.

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);

Теперь оператор EXPLAIN будет выводить такую информацию:

table type   possible_keys   key      key_len ref           rows Extra
et    ALL    PRIMARY         NULL     NULL    NULL          74
tt    ref    AssignedPC,     ActualPC 15      et.EMPLOYID   52   Using where
             ClientID,
             ActualPC
et_1  eq_ref PRIMARY         PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY         PRIMARY  15      tt.ClientID   1

Это почти идеально.

Осталась еще одна проблема. Она заключается в том, что по умолчанию MySQL принимает, что значения в столбце tt.ActualPC распределены равномерно, но в таблице tt это не так. К счастью, проинформировать MySQL об этом можно очень просто:

shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

Теперь связывание совершенно, и оператор EXPLAIN выведет такой результат:

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using where
             ClientID,
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

Обратите внимание: столбец rows в выводе оператора EXPLAIN - опытное предположение оптимизатора связей MySQL. Чтобы оптимизировать запрос, нужно проверить, являются ли числа близкими к действительным. Если нет, можно получить лучшую производительность, используя в операторе SELECT соединение STRAIGHT_JOIN и попытаться задать другой порядок таблиц в выражении FROM.

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

log(количество_строк) / log(длина_индексного_блока / 3 * 2 / (длина_индекса + длина_указателя_на_данные)) + 1

дисковая операция для получения строки.

Обычно в MySQL индексный блок занимает 1024 байта, а указательн - 4 байта. Для таблицы, содержащей 500000 строк и имеющей длину индекса 3 (medium integer) потребуется log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 дисковых операции поиска.

Поскольку вышеупомянутый индекс будет занимать приблизительно 500000 * 7 * 3/2 = 5,2Mб (если учитывать, что индексные буфера обычно заполняются на 2/3), большая часть индекса, скорее всего, окажется в памяти, и для того, чтобы найти строку, потребуется лишь 1-2 обращения к ОС для чтения.

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

Обратите внимание: сказанное выше не означает, что производительность приложения будет ухудшаться в log N раз! Поскольку все кэшируется в OС или на SQL-сервере, замедление работы при увеличении таблицы будет незначительным. И лишь после того, как данных станет так много, что они перестанут помещаться в кэш, замедление работы там, где работа приложения сводится только к операциям дискового поиска (количество которых растет в log N), станет гораздо ощутимей. Чтобы избежать этого, следует увеличить индексный кэш так, чтобы он вмещал возросшее количество данных. See Раздел 5.5.2, «Настройка параметров сервера».

В общем случае для того, чтобы заставить медленный SELECT ... WHERE работать быстрее, прежде всего нужно выяснить, можно ли добавить индекс. Для всех ссылок между различными таблицами должны, как правило, применяться индексы. Чтобы определить, какие индексы используются для выборки SELECT, можно использовать EXPLAIN. See Раздел 5.2.1, «Синтаксис оператора EXPLAIN (получение информации о SELECT)».

Вот несколько общих советов:

Описание оптимизации выражений WHERE помещено в раздел, посвященный SELECT, потому что они главным образом используются в запросах SELECT, но для выражений WHERE в операторах DELETE и UPDATE используются те же способы оптимизации.

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

Ниже перечислены некоторые из оптимизации, выполняемых MySQL:

Вот некоторые примеры очень быстрых запросов:

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
    ->        WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
    ->        ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
    ->        ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

Для выполнения следующих запросов используется только индексное дерево (предполагается, что индексированные столбцы числовые):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
    ->        WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

Следующие запросы используют индексацию, чтобы получить отсортированные строки без дополнительного прохода для сортировки:

mysql> SELECT ... FROM tbl_name
    ->            ORDER BY key_part1,key_part2,... ;
mysql> SELECT ... FROM tbl_name
    ->            ORDER BY key_part1 DESC,key_part2 DESC,... ;

DISTINCT преобразовывается к GROUP BY для всех столбцов, для DISTINCT в сочетании с ORDER BY, помимо этого, во многих случаях также требуется временная таблица.

Если LIMIT # указывается совместно с DISTINCT, MySQL остановится, как только найдет # уникальных строк.

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

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

В случае, если, предположим, таблица t1 используется перед t2 (это проверяется при помощи EXPLAIN), MySQL прекратит чтение в t2 (для каждой отдельной строки из t1), после того как найдет первую строку в t2.

Выражение "A LEFT JOIN B" в MySQL реализовано следующим образом:

RIGHT JOIN реализован аналогично LEFT JOIN.

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

Обратите внимание: отсюда следует, что если выполняется запрос типа

SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
         WHERE b.key=d.key

MySQL будет делать полный просмотр таблицы b, поскольку LEFT JOIN заставит его читать эту таблицу перед d.

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

SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
         WHERE b.key=d.key

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

Индекс может также использоваться и тогда, когда предложение ORDER BY не соответствует индексу в точности, если все неиспользуемые части индекса и все столбцы, не указанные в ORDER BY - константы в выражении WHERE. Следующие запросы будут использовать индекс, чтобы выполнить ORDER BY / GROUP BY.

SELECT * FROM t1 ORDER BY key_part1,key_part2,...
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC

Ниже приведены некоторые случаи, когда MySQL не может использовать индексы, чтобы выполнить ORDER BY (обратите внимание, что MySQL тем не менее будет использовать индексы, чтобы найти строки, соответствующие выражению WHERE):

В тех случаях, когда MySQL должен сортировать результат, он использует следующий алгоритм:

При помощи команды EXPLAIN SELECT ... ORDER BY можно проверить, может ли MySQL использовать индексы для выполнения запроса. Если в столбце extra содержится значение Using filesort, то MySQL не может использовать индексы для выполнения сортировки ORDER BY. See Раздел 5.2.1, «Синтаксис оператора EXPLAIN (получение информации о SELECT)».

Чтобы сортировка ORDER BY выполнялась с большей скоростью, нужно сначала посмотреть, можно ли заставить MySQL использовать индексы взамен дополнительной фазы сортировки. Если это невозможно, то можно сделать следующее:

MySQL по умолчанию сортирует все GROUP BY x,y[,...] запросы так, как если бы вы указали ORDER BY x,y[,...]. MySQL будет оптимизировать любой ORDER BY как сказано выше, без всяких потерь производительности. Если, как в некоторых случаях, вы не хотите иметь результат отсортированным, вы можете указать ORDER BY NULL:

INSERT INTO foo SELECT a,COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

В некоторых случаях, когда используется LIMIT # и не используется HAVING, MySQL будет выполнять запрос несколько иначе:

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

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

Размер таблицы замедляет вставку индексов в log N раз (B-деревья).

Некоторые способы ускорения вставки:

Чтобы дополнительно повысить скорость выполнения команд LOAD DATA INFILE и INSERT, увеличьте буфер ключа (key buffer). See Раздел 5.5.2, «Настройка параметров сервера».

Запросы UPDATE оптимизируются как запрос SELECT с дополнительными издержками на запись. Скорость записи зависит от размера обновляемых данных и количества обновляемых индексов. Индексы, которые не были изменены, обновлены не будут.

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

Обратите внимание: при использовании динамического формата записи, если запись обновляется более длинной, может произойти "расслоение" записи. Таким образом, если вы делаете это часто, очень важно время от времени выполнять команду OPTIMIZE TABLE. See Раздел 4.5.1, «Синтаксис команды OPTIMIZE TABLE».

Чтобы удалить все строки в таблице, нужно использовать команду TRUNCATE TABLE table_name. See Раздел 6.4.7, «Синтаксис оператора TRUNCATE».

Время, необходимое для удаления записи, прямо пропорционально числу индексов. Чтобы записи удалялись быстрее, можно увеличить размер индексного кэша. See Раздел 5.5.2, «Настройка параметров сервера».

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

Описание различных методов блокировки дается в приложении, в разделе Раздел E.4, «Методы блокировки».

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

В таблицах типа InnoDB устанавливается блокировка строк, а в таблицах типа BDB - блокировка страниц в процессе выполнения SQL-команд, а не в начале транзакции.

Метод блокирования, используемый в MySQL для блокировок записи (WRITE), работает следующим образом:

Метод блокирования, используемый в MySQL для блокировок чтения (READ), работает следующим образом:

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

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

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

Для этого можно применить следующий код:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

В некоторых случаях, когда необходимо установить приоритеты извлечения данных, для команд INSERT, UPDATE или DELETE можно указывать опцию LOW_PRIORITY, а для команды SELECT - HIGH_PRIORITY. Тот же результат можно получить, запустив mysqld с --low-priority-updates.

Использование SQL_BUFFER_RESULT тоже способствует уменьшению времени блокировок таблиц. See Раздел 6.4.1, «Синтаксис оператора SELECT».

Можно также изменить блокировочный код в mysys/thr_lock.c - чтобы использовать только одну очередь блокировок. В этом случае блокировки записи и чтения будут иметь одинаковый приоритет, что может оказаться полезным для некоторых приложений.

Код блокирования таблиц в MySQL не создает тупиковых ситуаций.

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

Для таблиц типов InnoDB и BDB в MySQL табличная блокировка используется только в случае, если данная таблица явно блокируется командой LOCK TABLES. Мы вообще не рекомендуем применять LOCK TABLES для упомянутых типов таблиц, поскольку для таблицы InnoDB используется автоматическая блокировка строкового уровня, а для таблиц BDB - блокировка страничного уровня. Это делается, чтобы гарантировать изоляцию транзакций.

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

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

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

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

Табличную блокировку, однако, нецелесообразно использовать в случае следующего сценария:

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

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

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

Модель, когда индексы и данные хранятся вместе (как в Oracle/Sybase и других), встречается чаще. В этом случае запись будет находиться в странице соответствующего индекса. Преимущества такой схемы во многих случаях заключаются в том, что считывание с диска получается более экономным - в зависимости от того, насколько хорошо кэшируется индекс. Недостатки же здесь следующие:

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

MySQL поддерживает большое количество различных типов таблиц и форматов строк. Значительный выигрыш в производительности можно получить за счет правильного выбора формата таблицы (see Глава 7, Типы таблиц MySQL).

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

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

Все индексы MySQL (PRIMARY, UNIQUE, и INDEX) хранятся в виде B-деревьев. Строки автоматически сжимаются с удалением пробелов в префиксах и оконечных пробелов (see Раздел 6.5.7, «Синтаксис оператора CREATE INDEX»).

Индексы используются для того, чтобы:

Предположим, что вызывается следующий оператор SELECT:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

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

Если данная таблица имеет многостолбцовый индекс, то любой крайний слева префикс этого индекса может использоваться оптимизатором для нахождения строк. Например, если имеется индекс по трем столбцам (col1,col2,col3), то существует потенциальная возможность индексированного поиска по (col1), (col1,col2) и (col1,col2,col3).

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

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Если индекс существует по (col1,col2,col3), то только первый показанный выше запрос использует данный индекс. Второй и третий запросы действительно включают индексированные столбцы, но (col2) и (col2,col3) не являются крайней слева частью префиксов (col1,col2,col3).

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

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

В первой команде рассматриваются только строки с "Patrick" <= key_col < "Patricl", а во второй - только строки с "Pat" <= key_col < "Pau".

Следующие команды SELECT не будут использовать индексы:

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

В первой команде величина LIKE начинается с шаблонного символа. Во второй команде величина LIKE не является константой.

В версии MySQL 4.0 производится другая оптимизация на выражении LIKE. Если используется выражение ... LIKE "%string%" и длина строки (string) больше, чем 3 символа, то MySQL будет применять алгоритм Турбо Бойера-Мура для инициализации шаблона для строки и затем использовать этот шаблон, чтобы выполнить поиск быстрее.

При поиске с использованием column_name IS NULL будут использоваться индексы, если column_name является индексом.

MySQL обычно использует тот индекс, который находит наименьшее количество строк. Индекс применяется для столбцов, которые сравниваются с помощью следующих операторов: =, >, >=, <, <=, BETWEEN и LIKE с префиксом, не содержащим шаблонного символа, такого как something%.

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

Следующие выражения WHERE используют индексы:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2 /* индекс = 1 ИЛИ индекс = 2 */
... WHERE index_part1='hello' AND index_part_3=5

/* оптимизировано как "index_part1='hello'" */

... WHERE index1=1 and index2=2 or index1=3 and index3=3;

/* Можно использовать индекс по index1, но не по index2 или index 3 */

Следующие выражения WHERE не используют индексы:

... WHERE index_part2=1 AND index_part3=2 
/* index_part_1 не используется */

... WHERE index=1 OR A=10 
/* Индекс не используется в обеих частях AND */

... WHERE index_part1=1 OR index_part2=10 
/* Нет индекса, покрывающего все строки*/

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

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

Максимальное количество ключей и максимальная длина индексов определяется обработчиком таблиц (see Глава 7, Типы таблиц MySQL). Можно иметь по меньшей мере 16 ключей на всех обработчиках таблиц и общую длину индексов по меньшей мере 256 байтов.

Для столбцов типов CHAR и VARCHAR можно индексировать префикс столбца. Это намного быстрее и требует меньше дискового пространства, чем индексация всего столбца. Используемый в команде CREATE TABLE синтаксис для индексации префикса столбца выглядит примерно так:

KEY index_name (col_name(length))

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

mysql> CREATE TABLE test (
    ->        name CHAR(200) NOT NULL,
    ->        KEY index_name (name(10)));

Для столбцов типа BLOB и TEXT индексировать необходимо префикс столбца. Нельзя индексировать столбец целиком.

В версии MySQL 3.23.23 и более поздних можно также создавать специальные индексы FULLTEXT. Они используются для полнотекстового поиска. Полнотекстовые индексы FULLTEXT поддерживают только таблицы типа MyISAM. Они могут создаваться только по столбцам VARCHAR и TEXT. Индексация всегда производится для целого столбца, а частичная индексация не поддерживается. See Раздел 6.8, «Полнотекстовый поиск в MySQL».

MySQL может создавать индексы по нескольким столбцам. Индекс может включать в себя до 15 столбцов (на столбцах CHAR и VARCHAR можно также использовать префикс столбца в качестве части индекса)

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

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

Предположим, создается следующая таблица:

mysql> CREATE TABLE test (
    ->       id INT NOT NULL,
    ->       last_name CHAR(30) NOT NULL,
    ->       first_name CHAR(30) NOT NULL,
    ->       PRIMARY KEY (id),
    ->       INDEX name (last_name,first_name));

Индекс name является индексом по столбцам last_name и first_name. Этот индекс будет применяться для запросов, указывающих величины в известной области для last_name или для обоих столбцов last_name и first_name. Таким образом, индекс name будет использоваться в следующих запросах:

mysql> SELECT * FROM test WHERE last_name="Widenius";

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    AND first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    AND (first_name="Michael" OR first_name="Monty");

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    AND first_name >="M" AND first_name < "N";

Чтобы получить более подробную информацию о том, как в MySQL используются индексы для улучшения работы запросов, See Раздел 5.4.3, «Использование индексов в MySQL».

При запуске mysqladmin status можно увидеть что-нибудь вроде этого:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

В чем же дело, ведь у вас всего 6 таблиц?

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

В следующем разделе вы найдете более подробную информацию по этой теме (see Раздел 5.4.7, «Открытие и закрытие таблиц в MySQL»).

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

Значения table_cache и max_connections взаимосвязаны. Например, для 200 одновременно работающих соединений необходимо иметь кэш для таблиц размером по меньшей мере 200 * n, где n - максимальное количество связанных таблиц. Необходимо также зарезервировать несколько дополнительных файловых дескрипторов для временных таблиц и файлов.

Следует удостовериться, что ваша операционная система способна обрабатывать такое количество открытых файловых дескрипторов, какое предполагает данная установка table_cache. Если устанавливается слишком высокое значение table_cache, то MySQL может выйти за пределы допустимого количества файловых дескрипторов, прервать соединение, не выполнять запросы и стать очень ненадежным. Необходимо также принять во внимание, что для обработчика таблиц MyISAM требуется по два файловых дескриптора для каждой уникальной открытой таблицы. Допустимое для MySQL количество файловых дескрипторов можно увеличить с помощью опции запуска --open-files-limit=# (see Раздел A.2.16, «Не найден файл (File not found)»).

Максимальное количество таблиц в кэше открытых таблиц будет равно количеству, указанному в table_cache (по умолчанию - 64; это число можно изменить с помощью опции -O table_cache=# для mysqld). Следует учитывать, что для выполнения запросов MySQL может временно открыть и больше таблиц.

Неиспользуемая таблица закрывается и удаляется из кэша таблиц в следующих ситуациях:

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

Таблица открывается для каждого одновременного доступа. Это означает, что, если существуют два потока, получающие доступ к одной и той же таблице, или происходит обращение к этой таблице дважды в одном и том же запросе (с помощью AS), то данная таблица должна быть открыта дважды. Для первого открытия любой таблицы требуется два файловых дескриптора; для каждого дополнительного использования - только один. Дополнительный дескриптор для первого открытия используется для индексного файла; этот дескриптор используется совместно всеми потоками.

При открытии таблицы командой HANDLER table_name OPEN создается выделенный табличный объект для данного потока. Этот табличный объект недоступен для других потоков и не будет закрыт, пока данный поток не вызовет команду HANDLER table_name CLOSE или сам поток не уничтожится (see Раздел 6.4.2, «Синтаксис оператора HANDLER»). Если это произойдет, то данная таблица помещается обратно в кэш таблиц (если он не заполнен).

Чтобы узнать, не слишком ли мал кэш таблиц, следует проверить переменную Opened_tables. Если ее значение достаточно велико, даже если вы не выполняли слишком часто команду FLUSH TABLES, то необходимо увеличить данный кэш таблиц (see Раздел 4.5.6.3, «SHOW STATUS»).

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

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

Используемая по умолчанию операционная система имеет действительно большое значение! Чтобы получить максимальную выгоду от применения многопроцессорных компьютеров, следует применять Solaris (так как под этой ОС потоки работают в самом деле хорошо) или Linux (поскольку ядро 2.2 обеспечивает действительно хорошую поддержку SMP). Однако на 32-разрядных компьютерах Linux по умолчанию имеет ограничение размера файлов в 2 Гб. Будем надеяться, что это ограничение в скором времени будет снято при выпуске новых файловых систем (XFS/Reiserfs). Но если вам действительно не обойтись без файлов с размерами более чем 2 Гб на 32-разрядном ПК с Linux-intel, то следует использовать патч LFS для файловой системы ext2.

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

Другие советы:

Размеры буферов, используемые по умолчанию сервером mysqld, можно узнать с помощью следующей команды:

shell> mysqld --help

Эта команда выдает список всех опций mysqld и конфигурируемых переменных. Вывод включает в себя величины по умолчанию и выглядит примерно следующим образом:

Possible variables for option --set-variable (-O) are:
back_log                 current value: 5
bdb_cache_size           current value: 1048540
binlog_cache_size        current value: 32768
connect_timeout          current value: 5
delayed_insert_timeout   current value: 300
delayed_insert_limit     current value: 100
delayed_queue_size       current value: 1000
flush_time               current value: 0
interactive_timeout      current value: 28800
join_buffer_size         current value: 131072
key_buffer_size          current value: 1048540
lower_case_table_names   current value: 0
long_query_time          current value: 10
max_allowed_packet       current value: 1048576
max_binlog_cache_size    current value: 4294967295
max_connections          current value: 100
max_connect_errors       current value: 10
max_delayed_threads      current value: 20
max_heap_table_size      current value: 16777216
max_join_size            current value: 4294967295
max_sort_length          current value: 1024
max_tmp_tables           current value: 32
max_write_lock_count     current value: 4294967295
myisam_sort_buffer_size  current value: 8388608
net_buffer_length        current value: 16384
net_retry_count          current value: 10
net_read_timeout         current value: 30
net_write_timeout        current value: 60
read_buffer_size         current value: 131072
record_rnd_buffer_size   current value: 131072
slow_launch_time         current value: 2
sort_buffer              current value: 2097116
table_cache              current value: 64
thread_concurrency       current value: 10
tmp_table_size           current value: 1048576
thread_stack             current value: 131072
wait_timeout             current value: 28800

Не забывайте, что --set-variable не используется в MySQL 4.0. Просто указывайте --var=option.

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

shell> mysqladmin variables

Полное описание всех переменных можно найти в разделе SHOW VARIABLES этого руководства (see Раздел 4.5.6.4, «SHOW VARIABLES»).

Некоторые статистические данные по работающему серверу можно также просмотреть с помощью команды SHOW STATUS (see Раздел 4.5.6.3, «SHOW STATUS»).

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

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

Если имеется большая память (>=256 Mб) и много таблиц, то для обеспечения максимальной производительности путем регулирования количества клиентов следует использовать что-нибудь вроде этого:

shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
                   -O sort_buffer=4M -O read_buffer_size=1M &

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

shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M

При малой памяти и большом количестве соединений следует использовать что-нибудь вроде следующего:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
                   -O read_buffer_size=100k &

или даже:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
                   -O table_cache=32 -O read_buffer_size=8k \
                   -O net_buffer_length=1K &

Если выполняются операции GROUP BY или ORDER BY на файлах, которые намного больше, чем доступная память, то следует увеличить величину record_rnd_buffer для ускорения чтения строк после выполнения сортировки.

После установки MySQL каталог support-files будет содержать несколько различных файлов-примеров my.cnf, а именно: my-huge.cnf, my-large.cnf, my-medium.cnf и my-small.cnf, которые можно использовать как основу для оптимизации вашей системы.

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

Следует учитывать, что при изменении какой-либо опции для mysqld это изменение действительно только для данного экземпляра сервера.

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

shell> mysqld -O key_buffer=32m --help

Следует удостовериться, что опция --help расположена последней; в противном случае влияние любой опции, следующей после нее в командной строке, в данном выводе отражено не будет.

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

Самый быстрый исполняемый код получается при линковании с помощью -static.

Под Linux наиболее быстрый код можно получить при компилировании pgcc с опицей -O3. Чтобы скомпилировать sql_yacc.cc с этой опцией, требуется около 200 Mб памяти, поскольку компилятор gcc/pgcc забирает много памяти. При конфигурировании MySQL следует также установить CXX=gcc - чтобы не линковалась библиотека libstdc++ (в этом нет необходимости). Следует учитывать, что при некоторых версиях компилятора pgcc результирующий код будет работать только на настоящих процессорах Pentium, даже если использовать возможность компилятора выдавать результирующий код, работоспособный на всех процессорах типа x586 (например AMD).

Используя просто лучший компилятор и/или лучшую опцию компилятора, можно получить для приложения увеличение скорости на 10-30%. Это особенно важно, если вы компилируете сервер SQL самостоятельно!

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

При компилировании MySQL необходимо включать только наборы кодировок, которые вы собираетесь использовать (опция --with-charset=xxx). Стандартная поставка MySQL скомпилирована с поддержкой всех кодировок.

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

Поставка MySQL под Linux, которую предоставляет MySQL AB, обычно компилировалась с pgcc, но мы должны были вернуться к обычному компилятору gcc из-за ошибок в pgcc, которая могут генерировать код, не исполняемый на AMD. Пока эти ошибки не будут устранены, мы будем продолжать использовать gcc, однако если ваш компьютер не относится к типу AMD, то можно получить более быстрый двоичный код, компилируя его с pgcc. Стандартный двоичный код MySQL для Linux слинкован статически, чтобы сделать его более быстрым и более переносимым.

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

Программа ps и другие программы контроля состояния системы могут сообщать, что mysqld использует слишком много памяти. Это может быть вызвано расположением стеков памяти потоков по различным адресам в памяти. Например, версия программы ps для Solaris интерпретирует неиспользуемую память между стеками как используемую. Это можно проверить путем выполнения допустимой перестановки с помощью swap -s. Мы тестировали mysqld при помощи коммерческих детекторов утечки памяти, так что никаких потерь памяти быть не должно.

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

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

Можно заблокировать поиск DNS хоста, запустив mysqld с параметром --skip-name-resolve. В этом случае, однако, в таблицах привилегий MySQL можно использовать только IP-адреса.

Если процесс установления DNS очень медленный и хостов очень много, то можно получить более высокую производительность либо путем блокировки поиска DNS при помощи --skip-name-resolve, либо увеличив размер определения HOST_CACHE_SIZE (по умолчанию: 128) и перекомпилировав mysqld.

Заблокировать кэш имен хостов можно с помощью --skip-host-cache. Можно также очистить этот кэш с помощью команды FLUSH HOSTS или mysqladmin flush-hosts.

Можно запретить соединения по протоколу TCP/IP, запустив mysqld с опцией --skip-networking.

SET [GLOBAL | SESSION] sql_variable=expression, [[GLOBAL | SESSION] sql_variable=expression...]

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

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

В старых версиях MySQL мы допускали использование SET OPTION, но этот синтакс теперь считается морально устаревшим.

В MySQL 4.0.3 мы также добавили режимы GLOBAL и SESSION и реализовали доступ к наиболее важным переменным запуска.

LOCAL может использоваться как синоним для SESSION.

Если вы устанавливаете несколько переменных в одной команде, то последний указанный режим GLOBAL | SESSION будет использован.

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

Синтаксис @@variable_name поддерживается с тем, чтобы сделать MySQL более совместимым с другими СУБД.

Различные системные переменные, которые вы можете установить, описаниы в этом руководстве. See Раздел 6.1.5, «Системные переменные».

Если вы используете режим SESSION (по умолчанию), то значения переменных, которые вы устанавливаете, остаются в в действии до тех пор, пока текущая сессия не будет завершена, или до тех пор, пока вы не установите переменные в другое значение. Если вы используете GLOBAL, который требует привилегии SUPER, это значение запоминается и используется для всех новых соединений до тех пор, пока сервер не будет перезагружен. Если вы хотите сделать какое-либо значение перманентным, то вам следует указать его в одном из файлов конфигураций MySQL. See Раздел 4.1.2, «Файлы параметров my.cnf».

Чтобы избежать неправильного использования, MySQL будет сообщать об ошибке, если вы будете выполнять SET GLOBAL на переменной, которую только можно изменять в SET SESSION или если вы не выбираете режим GLOBAL для глобальной переменной.

Если вы хотите установить сессионную (SESSION) переменную в значение глобальной (GLOBAL) или просто в значение по умолчанию - вы можете установить это значение в DEFAULT.

SET max_join_size=DEFAULT;

Это аналогично:

SET @@session.max_join_size=@@global.max_join_size;

Если вам необходимо ограничить максимальное значение, которое может принимать стартовая переменная с помощью SET, вы можете указать это с помощью использования опции командной строки --maximum-variable-name. See Раздел 4.1.1, «Параметры командной строки mysqld».

Список большинства переменных можно получить с помощью SHOW VARIABLES. See Раздел 4.5.6.4, «SHOW VARIABLES». Вы можете получить конкретное значение с помощью синтаксиса @@[global.|local.]variable_name:

SHOW VARIABLES like "max_join_size";
SHOW GLOBAL VARIABLES like "max_join_size";
SELECT @@max_join_size, @@global.max_join_size;

Далее следует описание переменных, использующих нестандартный синтаксис SET и некоторых других. Описание других переменных можно найти в секции, описывающих системные переменные, вместе с описанием стартовых опций или в описании команды SHOW VARIABLES. See Раздел 6.1.5, «Системные переменные». See Раздел 4.1.1, «Параметры командной строки mysqld». See Раздел 4.5.6.4, «SHOW VARIABLES».

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

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

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test mysqld-datadir

shell> cd /path/to/datadir
shell> ln -s db1 db2

if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

if (1)