Оптимизация - сложная задача, потому что она, в конечном счете, требует понимания системы в целом. В отдельных случаях для выполнения локальной оптимизации достаточно знать систему или приложение не в полном объеме, однако чтобы сделать систему более оптимальной, нужно разбираться в том, как она устроена.
В этой главе будут рассмотрены различные способы оптимизации MySQL и представлены некоторые примеры ее выполнения. Не следует забывать, однако, что всегда можно найти некоторые дополнительные возможности сделать систему еще быстрее (хотя каждый следующий шаг в этом направлении будет даваться все труднее и труднее).
Чтобы увеличить скорость системы, необходимо, разумеется, прежде всего разбираться в ее конструкции. Кроме того, нужно знать, какие функции будет выполнять система и какие "узкие места" в ней имеются.
Ниже приведен список наиболее часто встречающихся "узких мест":
При использовании обработчика таблиц MyISAM MySQL применяет очень быструю
блокировку таблиц (несколько потоков чтения/один поток записи). Самая
большая проблема при использовании этого типа таблиц возникает в случае
непрерывного потока обновлений в сочетании с медленными выборками из одной
и той же таблицы. Если эта проблема касается лишь некоторых таблиц, можно
использовать вместо них таблицы другого типа. See section 7 Типы таблиц MySQL.
MySQL может работать как с транзакционными так и с нетранзакционными таблицами. Чтобы обеспечить возможность нормальной работы с нетранзакционными таблицами (для которых невозможен откат, если что-нибудь произойдет не так, как надо), в MySQL существуют следующие правила:
NULL в
столбец NOT NULL или слишком большое числовое значение - в числовой
столбец), MySQL не будет выводить сообщение об ошибке, а просто
поместит в столбец "наиболее подходящее возможное значение". Для
числовых значений это 0, наименьшие возможные значения или наибольшее
возможное значение. Для строк это либо пустая строка, либо самая
длинная строка, которая может быть в столбце.
1/0 возвратит NULL
Существование приведенных выше правил объясняется тем, что перед началом выполнения запроса невозможно проверить, сможет ли он выполниться. Если проблема обнаружится после обновления нескольких строк, мы не можем выполнить полный откат, поскольку это может не поддерживаться типом таблицы. Остановиться в этот момент тоже нельзя, потому что тогда обновления будут выполнены наполовину, что является, вероятно, самым худшим возможным результатом. В данном случае лучше выбрать "наименьшее из зол", а затем продолжать, как будто ничего не произошло.
Отсюда следует, что 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):
| Чтение 2000000 строк по индексу | Секунды | Секунды |
| mysql | 367 | 249 |
| mysql_odbc | 464 | |
| db2_odbc | 1206 | |
| informix_odbc | 121126 | |
| ms-sql_odbc | 1634 | |
| oracle_odbc | 20800 | |
| solid_odbc | 877 | |
| sybase_odbc | 17614 |
| Вставка 350768 строк | Секунды | Секунды |
| mysql | 381 | 206 |
| mysql_odbc | 619 | |
| db2_odbc | 3460 | |
| informix_odbc | 2692 | |
| ms-sql_odbc | 4012 | |
| oracle_odbc | 11291 | |
| solid_odbc | 1801 | |
| sybase_odbc | 4802 |
В предыдущем тесте 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 пытается определить, какие функции поддерживаются СУБД,
и какие возможности и ограничения имеют эти функции при выполнении
запросов. Например, она определяет следующее:
VARCHAR
Результат, полученные crash-me для большого количества различных СУБД,
можно найти по адресу: http://www.mysql.com/information/crash-me.php.
Чтобы найти "узкие места" в своем приложении и базе данных, вы должны их тщательно протестировать. После устранения "узкого места" (или после замены его некой заглушкой) можно легко идентифицировать следующее "узкое место" (и так далее). Даже если общая производительность приложения достаточна, нужно по крайней мере выявить все "узкие места" и определиться с тем, как их устранять, - на будущее, если когда-нибудь потребуется дополнительная производительность.
Примеры переносимых программ программы для эталонного тестирования можно найти в наборе тестов MySQL. See section 5.1.4 Набор тестов MySQL (The MySQL Benchmark Suite). Можно взять любую программу из этого набора и модифицировать ее для своих потребностей. Таким образом можно испытывать различные решения проблемы и проверять, которое из них самое быстрое.
Зачастую некоторые проблемы проявляются только тогда, когда система очень сильно загружена. К нам часто обращаются клиенты, которые, запустив (протестированную) систему в производство, сталкиваются с проблемами, связанными с нагрузкой. На сегодня причиной каждого из этих случаев были либо проблемы, связанные с базовой конструкцией (при высокой нагрузке нехорошо выполняется сканирование таблиц), либо проблемы ОС/библиотек. И большинство таких проблем было бы намного легче устранить до начала промышленной эксплуатации систем.
Чтобы избежать подобных проблем, нужно постараться выполнить эталонное
тестирование всего приложения при самой плохой возможной нагрузке! Для
этого можно использовать программу Super Smack, которая доступна по
адресу: http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz.
Эта программа, как следует из ее имени (smack - шлепок - прим. пер.),
способна поставить систему на колени, так что используйте её только на
разрабатываемых системах (проще говоря, в девелопменте).
SELECT и других запросовСначала приведем одно правило, касающееся всех запросов: Чем сложнее ваша система привилений, тем больше издержек.
Если не было выполнено никаких операторов GRANT, MySQL каким-то образом
будет оптимизировать проверку полномочий. Таким образом при наличии очень
большого объема данных лучше, наверное, будет работать без привилегий. В
противном случае при большом количестве полномочий проверка результатов
будет происходить с увеличенными издержками.
Если проблема состоит в некоторой явной функции MySQL, всегда можно протестировать ее в клиенте:
mysql> SELECT BENCHMARK(1000000,1+1); +------------------------+ | BENCHMARK(1000000,1+1) | +------------------------+ | 0 | +------------------------+ 1 row in set (0.32 sec)
Из приведенного выше примера видно, что MySQL может выполнять более 1000000 операций сложения за 0,32 секунды на PentiumII 400MHz.
Все функции MySQL достаточно хорошо оптимизированы, но могут попадаться
некоторые исключения, и функция BENCHMARK(число_циклов,выражение) -
хороший инструмент для выяснения, присутствует ли проблема в запросе.
EXPLAIN (получение информации о SELECT)
EXPLAIN имя_таблицы
или EXPLAIN SELECT опции_выборки
EXPLAIN имя_таблицы является синонимом операторов DESCRIBE имя_таблицы и
SHOW COLUMNS FROM имя_таблицы.
Если оператор SELECT предваряется ключевым словом EXPLAIN, MySQL сообщит о
том, как будет производиться обработка SELECT, и предоставит информацию о
порядке и методе связывания таблиц.
При помощи EXPLAIN можно выяснить, когда стоит снабдить таблицы индексами,
чтобы получить более быструю выборку, использующую индексы для поиска
записей.
Вы должны почаще запускать ANALYZE TABLE с тем, чтобы обновлялась статистика по
таблицам, такая как кардинальность ключей, которые могут повлиять на выбор оптимизатора.
See section 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 включает следующие столбцы:
id
SELECT, последовательный номер этого конкретного SELECT в запросе.
select_type
SELECT, который может быть один из следующих:
SIMPLE
SELECT без UNION или подзапросов).
PRIMARY
SELECT.
UNION
UNION SELECT.
DEPENDENT UNION
UNION SELECT, зависящие от внешнего подзапроса.
SUBSELECT
SELECT в подзапросе.
DEPENDENT SUBSELECT
SELECT, зависящий от внешнего подзапроса.
DERIVED
SELECT.
table
type
system
const.
const
const являются очень быстрыми, поскольку они
читаются только однажды!
eq_ref
const. Данный тип применяется, когда все части индекса
используются для связывания, а сам индекс - UNIQUE или PRIMARY KEY.
ref
ref
применяется, если для связывания используется только крайний левый префикс
ключа, или если ключ не является UNIQUE или PRIMARY KEY (другими словами,
если на основании значения ключа для связывания не может быть выбрана одна
строка). Этот тип связывания хорошо работает, если используемый ключ
соответствует только нескольким строкам.
range
key. Столбец key_len содержит самую длинную часть ключа, которая
была использована. Столбец ref будет содержать значения NULL для этого
типа.
index
ALL, за исключением того, что просматривается только
дерево индексов. Этот тип обычно более быстрый чем ALL, поскольку
индексный файл, как правило, меньше файла данных.
ALL
const, и очень плохо во всех остальных случаях. Как
правило, можно избегать типа связывания ALL - путем добавления большего
количества индексов таким образом, чтобы строка могла быть найдена при
помощи константных значений или значений столбца из предыдущих таблиц.
possible_keys
possible_keys служит для указания индексов, которые может
использовать MySQL для нахождения строк в этой таблице. Обратите внимание:
этот столбец полностью независим от порядка таблиц. Это означает, что на
практике некоторые ключи в столбце possible_keys могут не годиться для
сгенерированного порядка таблиц.
Если данный столбец пуст, то никаких
подходящих индексов не имеется. В этом случае для увеличения
производительности следует исследовать выражение WHERE, чтобы увидеть,
есть ли в нем ссылки на какой-либо столбец (столбцы), которые подходили бы
для индексации. Если да, создайте соответствующий индекс и снова проверьте
запрос при помощи оператора EXPLAIN. See section 6.5.4 Синтаксис оператора ALTER TABLE.
Чтобы увидеть, какие
индексы есть в таблице, используйте команду SHOW INDEX FROM имя_таблицы.
key
key содержит ключ (индекс), который MySQL решил использовать в
действительности. Если никакой индекс не был выбран, ключ будет иметь значение
NULL. Чтобы заставить MySQL применить индекс из possible_keys,
следует использовать оператор USE INDEX/IGNORE INDEX в запросе.
See section 6.4.1 Синтаксис оператора SELECT.
Также, выполнение myisamchk --analyze (see section 4.4.6.1 Синтаксис запуска myisamchk) или
ANALYZE TABLE (see section 4.5.2 Синтаксис команды ANALYZE TABLE) по таблицам даст возможность
оптимизатору принимать более правильные решения.
key_len
key_len содержит длину ключа, которую решил использовать MySQL.
Если key имеет значение NULL, то длина ключа (key_len) тоже NULL. Обратите
внимание: по значению длины ключа можно определить, сколько частей
составного ключа в действительности будет использовать MySQL.
ref
ref показывает, какие столбцы или константы используются с ключом,
указанным в key, для выборки строк из таблицы.
rows
rows указывается число строк, которые MySQL считает нужным
проанализировать для выполнения запроса.
Extra
Distinct
Not exists
LEFT JOIN для запроса и после
нахождения одной строки, соответствующей критерию LEFT JOIN, не будет
искать в этой таблице последующие строки для предыдущей комбинации строк.
Например:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;Предположим, что столбец
t2.id определен как NOT NULL. В этом случае MySQL
просмотрит таблицу t1 и будет искать строки в t2 соответствующие t1.id.
Если MySQL находит в t2 нужную строку, он знает, что t2.id никогда не
может иметь значение NULL, и не будет искать в t2 оставшуюся часть строк,
имеющих тот же самый id. Другими словами, для каждой строки в t1 MySQL
должен выполнить только один поиск в t2, независимо от того, сколько
совпадающих строк содержится в t2.
range checked for each record (index map: #)Using filesort
join type) и сохраняются
ключ сортировки плюс указатель на строку для всех строк, удовлетворяющих
выражению WHERE. После этого ключи сортируются и строки извлекаются в
порядке сортировки.
Using index
Using temporary
ORDER BY
выполняется для набора столбцов, отличного от того, который используется в
предложении GROUP BY.
Using where
WHERE будет использоваться для выделения тех строк, которые
будут сопоставляться со следующей таблицей или тех, которые будут посланы
клиенту. Если этой информации нет, а таблица имеет тип ALL или index, то,
значит, в вашем запросе есть какая-то ошибка (если вы не собираетесь
делать выборку/тестирование всех строк таблицы).
Using filesort и Using temporary.
Существует неплохой способ определить, насколько хорошим является тип
связывания. Для этого нужно перемножить все значения столбца rows,
выводимого командой EXPLAIN. Результатом будет грубая оценка того, сколько
строк должен просмотреть MySQL для выполнения запроса. Это же число
используется для ограничения запросов в переменной max_join_size.
See section 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;
Для этого примера принимается, что:
| Таблица | Столбец | Тип столбца |
tt | ActualPC | CHAR(10)
|
tt | AssignedPC | CHAR(10)
|
tt | ClientID | CHAR(10)
|
et | EMPLOYID | CHAR(15)
|
do | CUSTNMBR | CHAR(15)
|
| Таблица | Индекс |
tt | ActualPC
|
tt | AssignedPC
|
tt | ClientID
|
et | EMPLOYID (primary key)
|
do | CUSTNMBR (primary key)
|
tt.ActualPC распределены не равномерно.
На начальном этапе перед выполнением какой-либо оптимизации оператор
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 section 5.5.2 Настройка параметров сервера.
SELECT
В общем случае для того, чтобы заставить медленный SELECT ... WHERE
работать быстрее, прежде всего нужно выяснить, можно ли добавить индекс.
Для всех ссылок между различными таблицами должны, как
правило, применяться индексы. Чтобы определить, какие индексы используются
для выборки SELECT, можно использовать EXPLAIN. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT).
Вот несколько общих советов:
myisamchk
--analyze для таблицы после того, как она загружена соответствующими
данными. Таким образом для каждой части индекса будет обновлено
значение, указывающее среднее число строк, имеющих одинаковые значения
(для уникальных индексов это всегда 1, разумеется). MySQL будет
использовать это число, чтобы решить, какой индекс следует выбрать для
связывания двух таблиц при помощи "неконстантного выражения".
Результат работы analyze можно увидеть в столбце Cardinality после
выполнения команды SHOW INDEX FROM имя_таблицы.
myisamchk --sort-index --sort-records=1 (если нужно
отсортировать по индексу 1). Если имеется уникальный индекс, по
которому вы хотите считывать все записи в порядке, соответствующем
данному индексу, это - хороший способ ускорить считывание записей.
Обратите внимание, однако, что эта сортировка написана не оптимально и
для большой таблицы будет выполняться долго!
WHERE
Описание оптимизации выражений WHERE помещено в раздел, посвященный
SELECT, потому что они главным образом используются в запросах SELECT, но
для выражений WHERE в операторах DELETE и UPDATE используются те же
способы оптимизации.
Отметим также, что данный раздел неполон. В MySQL реализовано много возможностей оптимизации, и у нас не было времени, чтобы задокументировать их все.
Ниже перечислены некоторые из оптимизации, выполняемых MySQL:
((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d)
(a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
HEAP и MyISAM функция COUNT(*), которая вызывается для
одной таблицы и не содержит предложения WHERE, берется непосредственно
из табличной информации. Это делается также для любого выражения NOT
NULL, в котором используется только одна таблица.
SELECT неосуществимы и не
возвращают строк.
HAVING с WHERE, если не используется
предложение GROUP BY или групповые функции (COUNT(), MIN()...).
WHERE, чтобы ускорить оценку WHERE для каждого
подчиненного связывания а также чтобы пропустить записи как можно
быстрее.
WHERE для индекса UNIQUE,
или PRIMARY KEY, где все части индекса используются с константными
выражениями и части индекса определены как NOT NULL.
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
-> WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
ORDER BY и GROUP BY принадлежат одной таблице, эта таблица
рассматривается первой при связывании.
ORDER BY и отличное от него выражение GROUP BY,
или если выражения ORDER BY или GROUP BY содержат столбцы не только из
первой таблицы в очереди на связывание, но и из других таблиц, то
тогда создается временная таблица.
SQL_SMALL_RESULT, MySQL будет применять временную
таблицу, которую разместит в памяти.
HAVING.
Вот некоторые примеры очень быстрых запросов:
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
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.
LEFT JOIN и RIGHT JOIN
Выражение "A LEFT JOIN B" в MySQL реализовано следующим образом:
LEFT JOIN.
LEFT JOIN перемещаются в предложение WHERE.
WHERE.
WHERE, но в
таблице B ни одна строка не удовлетворяет условию LEFT JOIN,
генерируется дополнительная строка B, в которой все значения столбцов
устанавливаются в NULL.
LEFT JOIN используется для поиска тех строк, которые отсутствуют
в некоторой таблице, и в предложении WHERE выполняется следующая
проверка: column_name IS NULL, где column_name - столбец, который
объявлен как NOT NULL, MySQL пререстанет искать строки (для отдельной
комбинации ключа) после того, как найдет строку, соответствующую
условию LEFT JOIN.
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
ORDER BY
В некоторых случаях 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):
ORDER BY делается по нескольким ключам:
SELECT * FROM t1 ORDER BY key1,key2
ORDER BY делается, при использовании непоследовательных
частей ключа: SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2
ASC и DESC.
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC
ORDER BY используются разные ключи:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1
ORDER BY, относятся не только к первой неконстантной
(const) таблице, используемой для выборки строк (это первая таблица в
выводе EXPLAIN, в которой не используется константный, const, метод
выборки строк).
ORDER BY и GROUP BY.
HASH в таблицах HEAP).
В тех случаях, когда MySQL должен сортировать результат, он использует следующий алгоритм:
sort_buffer)
qsort, результаты сохраняются во временном файле. Сохраняется
указатель на отсортированный блок (в том случае, когда все строки
умещаются в буфере сортировки, временный файл не создается).
MERGEBUFF (7) областей в один блок в другом
временном файле. Это действие повторяется, пока все блоки из первого
файла не окажутся во втором файле.
MERGEBUFF2 (15)
блоков.
record_rnd_buffer).
При помощи команды EXPLAIN SELECT ... ORDER BY можно проверить, может ли
MySQL использовать индексы для выполнения запроса. Если в столбце extra
содержится значение Using filesort, то MySQL не может использовать индексы
для выполнения сортировки ORDER BY. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT).
Чтобы сортировка ORDER BY выполнялась с большей скоростью, нужно сначала
посмотреть, можно ли заставить MySQL использовать индексы взамен
дополнительной фазы сортировки. Если это невозможно, то можно сделать
следующее:
sort_buffer.
record_rnd_buffer.
tmpdir, чтобы она указывала на выделенный диск с
большим количеством свободного пространства.
Начиная с MySQL 4.1, в tmpdir могут быть указаны несколько путей, разделенных
двоеточием : (точкой с запятой на Windows ;). Эти пути будут
использованы в ротации.
Внимание: Эти пути должны находится на разных физических
дисках, не на разных разделах одного и того же диска.
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
В некоторых случаях, когда используется LIMIT # и не используется HAVING,
MySQL будет выполнять запрос несколько иначе:
LIMIT выбираются только несколько строк, MySQL будет
использовать индексы в тех некоторых случаях, когда он обычно
предпочел бы делать полное сканирование таблицы.
LIMIT # используется с ORDER BY, MySQL закончит сортировку, как
только найдет первые # строк, вместо того, чтобы сортировать всю
таблицу.
LIMIT # с DISTINCT MySQL остановится, как только найдет
# уникальных строк.
GROUP BY может быть выполнена путем
упорядоченного считывания ключа (или путем выполнения сортировки по
ключу) и последующего вычисления итогового результата пока не
изменится значение ключа. В этом случае LIMIT # не будет вычислять
какие-либо ненужные предложения GROUP BY.
# строк клиенту, он прервет
выполнение запроса (если не используется SQL_CALC_FOUND_ROWS).
LIMIT 0 всегда будет быстро возвращать пустую выборку. Эта команда
полезна для проверки запроса и получения типов столбцов результата.
LIMIT
# применяется для вычисления того, сколько для них потребуется места.
INSERTВремя, необходимое для вставки записи, можно грубо разделить на такие промежутки:
где числа в скобках пропорциональны полному времени. При этом не учитывается время в начале вставки, требующееся для открытия таблиц (таблицы открываются один раз для каждого конкурентно выполняющегося запроса).
Размер таблицы замедляет вставку индексов в log N раз (B-деревья).
Некоторые способы ускорения вставки:
INSERT в форме, содержащей множество
записей. При этом вставка будет происходить намного быстрее (в
некоторых случаях в несколько раз), чем при использовании отдельных
операторов INSERT. При добавлении данных в непустую таблицу можно
настроить переменную bulk_insert_buffer_size так, чтобы это
делалось еще быстрее. See section 4.5.6.4 SHOW VARIABLES.
INSERT DELAYED. See section 6.4.3 Синтаксис оператора INSERT.
MyISAM можно вставлять
строки во время выполнения операторов SELECT, если в таблицах нет
удаленных строк.
LOAD DATA
INFILE. При этом обычно вставка будет происходить в 20 раз быстрее,
чем при использовании соответствующего количества операторов INSERT.
See section 6.4.9 Синтаксис оператора LOAD DATA INFILE.
LOAD DATA INFILE выполнялась еще
быстрее. Используйте следующую процедуру:
CREATE TABLE
(например, используя mysql или Perl-DBI).
FLUSH TABLES или команду оболочки:
mysqladmin flush-tables.
myisamchk --keys-used=0 -rq /path/to/db/tbl_name. После
этого индексы не будут использоваться для данной таблицы.
LOAD DATA INFILE. При этом
никакие индексы обновляться не будут и, следовательно, скорость будет
высокой весьма.
myisampack для этой таблицы, чтобы уменьшить ее размер. See section 4.7.4 myisampack, MySQL-генератор сжатых таблиц (только для чтения).
myisamchk -r -q
/path/to/db/tbl_name. Эта процедура создает индексное дерево в памяти,
перед тем как записать его на диск, что гораздо быстрее за счет
исключения большого количества дисковых операций. Индексное дерево,
получившееся в результате, к тому же отлично сбалансировано.
FLUSH TABLES или команду оболочки:
mysqladmin flush-tables.
LOAD DATA INFILE также выполняет вышеупомянутую
оптимизацию при вставках в пустую таблицу. Главное отличие этой команды от
вышеупомянутой процедуры заключается в том, что при помощи myisamchk можно
выделить намного больше временной памяти для создания индекса, чем MySQL,
по вашему мнению, должен выделять для каждого воссоздания индексов.
Начиная с MySQL 4.0 можно также использовать команду ALTER TABLE tbl_name
DISABLE KEYS вместо myisamchk --keys-used=0 -rq /path/to/db/tbl_name и
ALTER TABLE tbl_name ENABLE KEYS вместо myisamchk -r -q
/path/to/db/tbl_name. Таким образом можно также пропускать шаги FLUSH
TABLES.
mysql> LOCK TABLES a WRITE; mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33); mysql> INSERT INTO a VALUES (8,26),(6,29); mysql> UNLOCK TABLES;Главный фактор, влияющий на скорость, - то, что буфер индексов сбрасывается на диск только один раз, после завершения всех операторов
INSERT. Обычно содержимое индексных буферов сбрасывалось бы на диск
столько раз, сколько имеется различных операторов INSERT. Блокировка не
нужна, если можно вставить все строки при помощи одного оператора. Для
транзакционных таблиц, чтобы повысить скорость, следует использовать
BEGIN/COMMIT вместо LOCK TABLES. Блокировка также понизит полное время
проверки подсоединений (multi-connection tests), но максимальное время
ожидания для некоторых потоков повысится (потому что они ожидают снятия
блокировки). Например:
поток 1 делает 1000 вставок потоки 2, 3 и 4 делают 1 вставку поток 5 делает 1000 вставокЕсли блокировка не используется, 2, 3, и 4 завершат выполнение раньше, чем 1 и 5. Если блокировка используется, 2, 3 и 4, видимо, не закончат выполнение раньше, чем 1 или 5, но общее время должно приблизительно уменьшиться на 40%. Так как в MySQL операции
INSERT, UPDATE и DELETE очень
быстрые, общая производительность будет улучшаться, если добавлять
блокировки ко всем командам, делающим более 5 вставок или обновлений
подряд. Если делается очень много вставок строк, можно время от времени
сопровождать команду LOCK TABLES командой UNLOCK TABLES (после каждых 1000
строк), чтобы позволить другим потокам обращаться к таблице. Результатом
всего этого будет получение хорошей производительности. Конечно, для
загрузки данных намного более быстрой является команда LOAD DATA INFILE.
Чтобы дополнительно повысить скорость выполнения команд LOAD DATA INFILE и
INSERT, увеличьте буфер ключа (key buffer). See section 5.5.2 Настройка параметров сервера.
UPDATE
Запросы UPDATE оптимизируются как запрос SELECT с дополнительными
издержками на запись. Скорость записи зависит от размера обновляемых
данных и количества обновляемых индексов. Индексы, которые не были
изменены, обновлены не будут.
Существует и другой способ произвести операции обновления быстро: задерживать операции обновления, а потом делаеть сразу несколько обновлений. Несколько обновлений подряд выполняются намного быстрее, чем отдельные обновления если вы блокируете таблицу.
Обратите внимание: при использовании динамического формата записи, если
запись обновляется более длинной, может произойти "расслоение" записи.
Таким образом, если вы делаете это часто, очень важно время от времени
выполнять команду OPTIMIZE TABLE. See section 4.5.1 Синтаксис команды OPTIMIZE TABLE.
DELETE
Чтобы удалить все строки в таблице, нужно использовать команду TRUNCATE
TABLE table_name. See section 6.4.7 Синтаксис оператора TRUNCATE.
Время, необходимое для удаления записи, прямо пропорционально числу индексов. Чтобы записи удалялись быстрее, можно увеличить размер индексного кэша. See section 5.5.2 Настройка параметров сервера.
Несортированные советы для повышения скорости систем:
thread_cache_size.
See section 5.5.2 Настройка параметров сервера.
EXPLAIN. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT).
SELECT на часто обновляемых
таблицах типа MyISAM. Это помогает избежать проблем с блокировкой
таблиц.
OPTIMIZE TABLE после удаления большого
количества строк.
ALTER TABLE ... ORDER BY expr1,expr2... если вы большей
частью извлекаете записи в порядке expr1,expr2... Можно получить более
высокую производительность, используя эту опцию после больших
преобразований в таблице.
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.
UPDATE
table SET count=count+1 WHERE index_column=constant является очень
быстрым!
Это действительно важно при использовании типов таблиц MySQL, вроде MyISAM или
ISAM, имеющих только блокирование таблиц (многочисленные читающие/единственный
записывающий). Для многих баз данных это обеспечит также более высокую
производительность, поскольку программа управления блокировкой строк в этом
случае будет иметь меньше работы.
BLOB. В этом случае необходимо добавить дополнительный код для
запаковывания в BLOB и распаковывания обратно, но на некотором этапе
это может сэкономить много обращений. Это практично, когда ваши данные
не согласуются со структурой статической таблицы.
UDF (функции, определяемые пользователем) могут
быть хорошим способом получить большую производительность. В этом
случае, однако, следует иметь в запасе некоторый иной (более
медленный) путь, если используемая вами база данных не поддерживает
этих возможностей.
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.
name вместо
customer_name в таблице customer). Чтобы ваши имена были переносимыми
на другие SQL-серверы, они должны быть короче, чем 18 символов.
MyISAM в
MySQL напрямую, можно было бы получить увеличение скорости в 2-5 раз
по сравнению с использованием интерфейса SQL. Для возможности сделать
это, данные должны находиться на том же самом сервере, что и
приложение, и, обычно, должны иметь доступ только в одном процессе
обработки (поскольку внешняя файловая блокировка действительно
медленна). Можно было бы избавиться от вышеуказанных проблем введением
низко-уровневых команд MyISAM в сервере MySQL (это был бы один из
простых путей получить большую производительность, если необходимо).
Путем тщательного проектирования интерфейса базы данных было бы
достаточно просто поддерживать этот тип оптимизации.
DELAY_KEY_WRITE=1 сделает обновление индексов
более быстрым, так как они не записываются на диск, пока файл закрыт.
Обратная сторона этого заключается в том, что необходимо запускать
myisamchk на этих таблицах перед началом работы mysqld, для
уверенности, что все в порядке, если что-либо уничтожит mysqld в
середине работы. Поскольку ключевая информация всегда может быть
воспроизведена из данных, то вы не должны что-либо потерять при
использовании DELAY_KEY_WRITE.
Описание различных методов блокировки дается в приложении, в разделе section 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 section 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, ожидающим доступ к таблице, более высокий приоритет после
заданного числа вставок в таблицу.
Табличную блокировку, однако, нецелесообразно использовать в случае следующего сценария:
SELECT, требующий длительного времени для выполнения.
UPDATE на используемой таблице.
Этот клиент будет ожидать, пока SELECT не закончит свою работу.
SELECT на той же таблице.
Поскольку UPDATE имеет более высокий приоритет, чем SELECT, то эта
команда SELECT будет ждать, пока UPDATE не закончит свою работу. Кроме
того, вторая команда SELECT будет также ждать, пока не завершится
первая команда SELECT!
Ниже представлены некоторые возможные решения данной проблемы:
SELECT выполняться быстрее. Возможно,
для этого необходимо будет создать сводные таблицы.
mysqld с --low-priority-updates. Этим вы назначите всем
командам обновления таблицы более низкий приоритет, чем у команды
SELECT. Тогда последняя команда SELECT в предыдущем сценарии будет
выполняться перед командой INSERT.
INSERT, UPDATE или DELETE можно назначить более
низкий приоритет с помощью атрибута LOW_PRIORITY.
mysqld с небольшим значением max_write_lock_count, чтобы
разрешить блокировки чтения (READ) после определенного количества
блокировок записи (WRITE).
SET LOW_PRIORITY_UPDATES=1 можно указать, что все
обновления из конкретного потока должны выполняться с низким
приоритетом. See section 5.5.6 Синтаксис команды SET.
SELECT является очень важной, - с помощью
атрибута HIGH_PRIORITY. See section 6.4.1 Синтаксис оператора SELECT.
INSERT совместно с
SELECT, перейдите на новые таблицы MyISAM, которые поддерживают
одновременное выполнение команд SELECT и INSERT.
INSERT и SELECT, то
решить возникающие при этом проблемы иногда помогает атрибут DELAYED
для INSERT. See section 6.4.3 Синтаксис оператора INSERT.
SELECT и DELETE, то может помочь
опция LIMIT для DELETE. See section 6.4.6 Синтаксис оператора DELETE.
В MySQL данные и индексы хранятся отдельно, в разных файлах, в то время как во многих (практически во всех) базах данных данные и индексы помещаются вместе в одном и том же файле. Мы полагаем, что конструкция MySQL лучше подходит для очень широкого диапазона современных систем.
Существует еще один способ хранения исходных данных - когда данные для каждого столбца содержатся в отдельной области (примерами являются SDBM и Focus). При такой организации данных неизбежно снижение производительности для каждого запроса, затрагивающего более, чем один столбец. Поскольку при доступе к более чем одному столбцу ситуация очень быстро начинает ухудшаться, то мы полагаем, что эта модель не вполне годится для баз данных общего назначения.
Модель, когда индексы и данные хранятся вместе (как в Oracle/Sybase и других), встречается чаще. В этом случае запись будет находиться в странице соответствующего индекса. Преимущества такой схемы во многих случаях заключаются в том, что считывание с диска получается более экономным - в зависимости от того, насколько хорошо кэшируется индекс. Недостатки же здесь следующие:
Одна из основных задач оптимизации заключается в том, чтобы данные (и индексы) занимали как можно меньше места на диске (и в памяти). Это дает значительные преимущества в работе, поскольку ускоряется чтение диска, а оперативная память, как правило, используется меньше. Индексирование также требует меньших ресурсов, если оно выполняется на меньших столбцах.
MySQL поддерживает большое количество различных типов таблиц и форматов строк. Значительный выигрыш в производительности можно получить за счет правильного выбора формата таблицы (see section 7 Типы таблиц MySQL).
Чтобы увеличить производительность работы с таблицей и минимизировать требуемое пространство памяти, можно применять перечисленные ниже технические приемы:
MEDIUMINT часто
лучше, чем INT.
NOT NULL. Это позволяет
ускорить все операции и сэкономить по одному биту для каждого столбца.
Однако если для данного приложения действительно нужен NULL, то вы
все-таки его (NULL) используйте. Нужно просто избегать наличия NULL во
всех столбцах по умолчанию.
VARCHAR, TEXT или BLOB), то нужно применять формат записи
фиксированного размера. Такой метод дает увеличение скорости, но при
этом, к сожалению, может потребоваться лишнее место на диске.
See section 7.1.2 Форматы таблиц MyISAM.
Индексы применяются для быстрого поиска строк с указанным значением одного столбца. Без индекса чтение таблицы осуществляется по всей таблице начиная с первой записи, пока не будут найдены соответствующие строки. Чем больше таблица, тем больше накладные расходы. Если же таблица содержит индекс по рассматриваемым столбцам, то MySQL может быстро определить позицию для поиска в середине файла данных без просмотра всех данных. Для таблицы, содержащей 1000 строк, это будет как минимум в 100 раз быстрее по сравнению с последовательным перебором всех записей. Однако в случае, когда необходим доступ почти ко всем 1000 строкам, быстрее будет последовательное чтение, так как при этом не требуется операций поиска по диску.
Все индексы MySQL (PRIMARY, UNIQUE, и INDEX) хранятся в виде B-деревьев.
Строки автоматически сжимаются с удалением пробелов в префиксах и
оконечных пробелов (see section 6.5.7 Синтаксис оператора CREATE INDEX).
Индексы используются для того, чтобы:
WHERE.
MAX() или MIN() для заданного индексированного столбца.
Эта операция оптимизируется препроцессором, который проверяет, не
используете ли вы WHERE key_part_4 = константа, по всем частям
составного ключа < N. В этом случае MySQL сделает один просмотр ключа
и заменит выражение константой MIN(). Если все выражения заменяются
константой, запрос моментально вернет результат:
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
ORDER
BY key_part_1,key_part_2). Если за всеми частями ключа следует DESC,
то данный ключ читается в обратном порядке (see section 5.2.7 Как MySQL оптимизирует ORDER BY).
SELECT key_part3 FROM table_name WHERE key_part1=1
Предположим, что вызывается следующий оператор 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 не использует индекс, даже если это возможно. Несколько примеров таких ситуаций приведено ниже:
LIMIT по отношению только к извлекаемой части строк, то
MySQL будет применять индекс в любом случае, так как небольшое
количество строк можно найти намного быстрее, чтобы вернуть результат.
В MySQL могут быть проиндексированы столбцы всех типов. Использование
индексов на соответствующих столбцах представляет собой хороший способ
ускорения выполнения операций SELECT.
Максимальное количество ключей и максимальная длина индексов определяется обработчиком таблиц (see section 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 section 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 section 5.4.3 Использование индексов в MySQL.
При запуске mysqladmin status можно увидеть что-нибудь вроде этого:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
В чем же дело, ведь у вас всего 6 таблиц?
MySQL является многопоточной базой данных, поэтому для одной и той же
таблицы могут одновременно присутствовать несколько запросов. Чтобы
минимизировать эту проблему для двух потоков, имеющих различный статус на
одном и том же файле, таблица открывается независимо каждым конкурирующим
потоком. Это требует дополнительной памяти, но, как правило, повышает
производительность. Кроме того, для таблиц типа ISAM и MyISAM требуется
дополнительный дескриптор для файла данных. В таблицах этого типа
дескриптор индексного файла используется совместно всеми потоками.
В следующем разделе вы найдете более подробную информацию по этой теме (see section 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 section A.2.16 Не найден файл (File not found)).
Максимальное количество таблиц в кэше открытых таблиц будет равно
количеству, указанному в table_cache (по умолчанию - 64; это число можно
изменить с помощью опции -O table_cache=# для mysqld). Следует учитывать,
что для выполнения запросов MySQL может временно открыть и больше таблиц.
Неиспользуемая таблица закрывается и удаляется из кэша таблиц в следующих ситуациях:
table_cache входных величин, и поток
больше не использует таблицу.
mysqladmin refresh или mysqladmin
flush-tables.
FLUSH TABLES.
Когда табличный кэш заполняется, сервер использует следующую процедуру размещения входных данных кэша для их использования:
Таблица открывается для каждого одновременного доступа. Это означает, что,
если существуют два потока, получающие доступ к одной и той же таблице,
или происходит обращение к этой таблице дважды в одном и том же запросе (с
помощью AS), то данная таблица должна быть открыта дважды. Для первого
открытия любой таблицы требуется два файловых дескриптора; для каждого
дополнительного использования - только один. Дополнительный дескриптор для
первого открытия используется для индексного файла; этот дескриптор
используется совместно всеми потоками.
При открытии таблицы командой HANDLER table_name OPEN создается выделенный
табличный объект для данного потока. Этот табличный объект недоступен для
других потоков и не будет закрыт, пока данный поток не вызовет команду
HANDLER table_name CLOSE или сам поток не уничтожится (see section 6.4.2 Синтаксис оператора HANDLER). Если это произойдет, то данная таблица помещается обратно в кэш
таблиц (если он не заполнен).
Чтобы узнать, не слишком ли мал кэш таблиц, следует проверить переменную
Opened_tables. Если ее значение достаточно велико, даже если вы не
выполняли слишком часто команду FLUSH TABLES, то необходимо увеличить
данный кэш таблиц (see section 4.5.6.3 SHOW STATUS).
Если в каталоге присутствует большое количество файлов, то операции
открытия, закрытия и создания будут медленными. Выполнение значительного
количества команд SELECT на большом количестве разных таблиц приводит к
небольшим непроизводительным затратам при заполненном табличном кэше,
поскольку для открытия одной таблицы требуется закрыть другую. Чтобы
сократить эту перегрузку, следует увеличить табличный кэш.
Мы начинаем с вопросов системного уровня, поскольку некоторые из них требуют решения на самых ранних этапах. В других случаях может оказаться достаточно только беглого просмотра этого материала, поскольку значительного выигрыша в оптимизации он не обеспечивает. Однако всегда хорошо иметь представление о том, какую пользу можно получить при изменении параметров на этом уровне.
Используемая по умолчанию операционная система имеет действительно большое значение! Чтобы получить максимальную выгоду от применения многопроцессорных компьютеров, следует применять Solaris (так как под этой ОС потоки работают в самом деле хорошо) или Linux (поскольку ядро 2.2 обеспечивает действительно хорошую поддержку SMP). Однако на 32-разрядных компьютерах Linux по умолчанию имеет ограничение размера файлов в 2 Гб. Будем надеяться, что это ограничение в скором времени будет снято при выпуске новых файловых систем (XFS/Reiserfs). Но если вам действительно не обойтись без файлов с размерами более чем 2 Гб на 32-разрядном ПК с Linux-intel, то следует использовать патч LFS для файловой системы ext2.
На многих платформах MySQL еще не находился в промышленной эксплуатации, поэтому мы рекомендуем прежде, чем остановить свой выбор на какой-либо платформе, сначала ее протестировать.
--skip-external-locking. Следует учитывать, что пока работает только один
сервер, это не будет оказывать большого влияния на функциональные
возможности MySQL. Только не забудьте остановить сервер (или
блокировать соответствующие части) перед запуском myisamchk. В
некоторых системах такое переключение обязательно, поскольку внешнее
блокирование не работает в любом случае.
Опция --skip-external-locking включена
по умолчанию при компилировании с потоками MIT-pthreads, поскольку
функция flock() не полностью поддерживается потоками MIT-pthreads на
всех платформах. Для Linux также подразумевается, что блокирование
файлов пока еще ненадежно.
Нельзя использовать --skip-external-locking только в
одном случае - при запуске нескольких серверов (не клиентов) MySQL на
одних и тех же данных, или при запуске myisamchk на таблице без
предварительного сбрасывания на диск и блокирования демона mysqld для
сервера, содержащего эти таблицы. Можно также применять команду LOCK
TABLES/UNLOCK TABLES даже при использовании --skip-external-locking.
Размеры буферов, используемые по умолчанию сервером 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 section 4.5.6.4 SHOW VARIABLES).
Некоторые статистические данные по работающему серверу можно также
просмотреть с помощью команды SHOW STATUS (see section 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 скомпилирована с поддержкой всех кодировок.
Ниже приводится обзор некоторых действий, которые мы предпринимали для ускорения работы:
pgcc и компиляции всего кода с -O6 сервер mysqld на
1% быстрее, чем при gcc 2.95.2.
-static) результирующий
исполняемый файл сервера будет на 13% медленнее работать под
управлением Linux. Обратите внимание: вы спокойно можете использовать
динамическую библиотеку MySQL. Это касается только сервера и актуально
только там, где нужна высокая производительность.
mysqld с помощью strip libexec/mysqld
можно получить прирост скорости результирующего двоичного кода до 4%.
localhost MySQL по умолчанию будет
использовать сокеты).
--with-debug=full, то для
большинства запросов потери в производительности будут составлять до
20%, но некоторые запросы могут выполняться значительно дольше (тесты
производительности MySQL работают на 35% медленнее). При использовании
опции --with-debug теряется только 15% производительности. При запуске
mysqld, откомпилированного с --with-debug=full и --skip-safemalloc,
результат должен почти таким же, как и при компиляции с --with-debug.
gcc 3.2
gcc 2.95.2 для UltraSPARC с опцией -mcpu=v8
-Wa,-xarch=v8plusa дает прирост производительности на 4%.
--log-bin делает mysqld на 1% медленнее.
gcc без указателей
фреймов -fomit-frame-pointer или -fomit-frame-pointer -ffixed-ebp
делает mysqld на 1-4% быстрее.
Поставка MySQL под Linux, которую предоставляет MySQL AB, обычно
компилировалась с pgcc, но мы должны были вернуться к обычному компилятору
gcc из-за ошибок в pgcc, которая могут генерировать код, не исполняемый на
AMD. Пока эти ошибки не будут устранены, мы будем продолжать использовать
gcc, однако если ваш компьютер не относится к типу AMD, то можно получить
более быстрый двоичный код, компилируя его с pgcc. Стандартный двоичный
код MySQL для Linux слинкован статически, чтобы сделать его более быстрым
и более переносимым.
В следующем перечне дано описание некоторых аспектов использования памяти
сервером mysqld. Там, где это возможно, приводятся имена серверных
переменных, относящихся к использованию памяти:
key_buffer_size) используется совместно всеми
потоками; другие буферы, используемые данным сервером, выделяются при
необходимости (see section 5.5.2 Настройка параметров сервера).
thread_stack),
буфер соединения (переменная net_buffer_length) и буфер результата
(переменная net_buffer_length). Буфер соединения и буфер результата
при необходимости динамически расширяются вплоть до
max_allowed_packet. При выполнении запроса также выделяется память для
копии строки данного текущего запроса.
ISAM/MyISAM имеют распределенную память.
Это объясняется тем, что 4 Гб памяти (адресуемой в рамках 32-битной
разрядности) мало для достаточно больших таблиц. Когда системы с
64-разрядными адресным пространством получат более широкое
распространение, мы сможем добавить в сервер общую поддержку для
распределения памяти.
record_buffer).
record_rnd_buffer).
HEAP). Временные таблицы с записями большой длины
(вычисляемой как сумма длин всех столбцов) или таблицы, содержащие
столбцы BLOB, хранятся на диске. В версиях MySQL до 3.23.2 существует
проблема, заключающаяся в том, что если таблицы HEAP в динамически
выделяемой области превышают размер tmp_table_size, то возникает
ошибка The table tbl_name is full. В более новых версиях эта проблема
при необходимости решается путем автоматического преобразования
хранящихся в оперативной памяти HEAP-таблиц в таблицы MyISAM,
расположенные на диске. Чтобы обойти эту проблему, можно увеличить
размер временных таблиц установкой опции tmp_table_size в mysqld или
установкой SQL-опции SQL_BIG_TABLES в клиентской программе (see section 5.5.6 Синтаксис команды SET).
В версии MySQL 3.20 максимальный размер временной таблицы
был равен record_buffer*16, так что при использовании данной версии
необходимо увеличить значение record_buffer. Можно также запустить
mysqld с опцией --big-tables - для того, чтобы всегда хранить
временные таблицы на диске. Однако это будет влиять на скорость многих
сложных запросов.
malloc() и free()).
BLOB). Для столбца BLOB
используется от 5 до 8 байтов плюс длина данных BLOB. Обработчики
таблиц ISAM/MyISAM будут использовать один дополнительный буфер строки
для внутреннего представления.
BLOB, буфер динамически
увеличивается при чтении больших величин BLOB. При просмотре таблицы
выделяется буфер с размером, равным наибольшей величине BLOB.
mysqladmin flush-tables закрывает все неиспользуемые таблицы и
отмечает все используемые таблицы, которые необходимо закрыть после
окончания выполнения текущего потока. Такой алгоритм позволяет
эффективно освобождать большое количество используемой памяти.
Программа 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.
SETSET [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 section 6.1.5 Системные переменные.
Если вы используете режим SESSION (по умолчанию), то значения
переменных, которые вы устанавливаете, остаются в в действии до тех пор, пока
текущая сессия не будет завершена, или до тех пор, пока вы не установите
переменные в другое значение. Если вы используете GLOBAL, который
требует привилегии SUPER, это значение запоминается и используется для
всех новых соединений до тех пор, пока сервер не будет перезагружен. Если вы
хотите сделать какое-либо значение перманентным, то вам следует указать его в
одном из файлов конфигураций MySQL. See section 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 section 4.1.1 Параметры командной строки mysqld.
Список большинства переменных можно получить с помощью SHOW VARIABLES.
See section 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 section 6.1.5 Системные переменные. See section 4.1.1 Параметры командной строки mysqld. See section 4.5.6.4 SHOW VARIABLES.
CHARACTER SET character_set_name | DEFAULT
character_set_name является cp1251_koi8, но можно
легко добавить новые наборы символов, отредактировав файл sql/convert.cc в
дистрибутиве исходного кода MySQL. Чтобы восстановить установку по
умолчанию, следует установить значение character_set_name в DEFAULT.
Следует учитывать, что синтаксис установки опции CHARACTER SET отличается
от синтаксиса установки других опций.
PASSWORD = PASSWORD('некий пароль')
PASSWORD FOR user = PASSWORD('некий пароль')
mysql. Данный пользователь должен быть представлен в формате
user@hostname, где user и hostname в точности соответствуют записям этих
позиций в столбцах User и Host в таблице mysql.user. Например, если записи
в полях User и Host соответственно были bob и %.loc.gov, то необходимо
писать:
mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");
или
mysql> UPDATE mysql.user SET password=PASSWORD("newpass")
-> WHERE user="bob" AND host="%.loc.gov";
SQL_AUTO_IS_NULL = 0 | 1
1 (значение по умолчанию), то можно найти последнюю
внесенную строку для таблицы со столбцом AUTO_INCREMENT с помощью
следующей конструкции: WHERE auto_increment_column IS NULL. Эта
возможность используется некоторыми ODBC-программами, такими как Access.
AUTOCOMMIT = 0 | 1
1, то все изменения в таблицу будут вноситься
немедленно. Чтобы открыть многокомандную транзакцию, необходимо
использовать команду BEGIN (see section 6.7.1 Синтаксис команд BEGIN/COMMIT/ROLLBACK). Если
установить данную опцию в 0, то необходимо использовать COMMIT / ROLLBACK
для того, чтобы принять/отменить эту транзакцию (see section 6.7.1 Синтаксис команд BEGIN/COMMIT/ROLLBACK).
Следует учитывать, что при переходе из
режима работы без AUTOCOMMIT в режим AUTOCOMMIT MySQL автоматически
выполнит COMMIT для любой открытой транзакции.
BIG_TABLES = 0 | 1
SELECT, требующих обширных
временных таблиц, не будет выдаваться ошибка The table tbl_name is full.
Для нового соединения значение этой величины по умолчанию равно 0 (т.е.
использовать для временных таблиц оперативную память). Эта переменная раньше называлась
SQL_BIG_TABLES.
SQL_BIG_SELECTS = 0 | 1
SELECT может потребовать слишком много времени для
выполнения. Такая возможность полезна при нерационально написанном
выражении WHERE. Запрос классифицируется как слишком большой, если оператору
SELECT, видимо, пришлось бы обрабатывать больше строк, чем задано в
max_join_size. Для нового соединения значение по умолчанию равно 1 (т.е.
разрешаются любые команды SELECT).
SQL_BUFFER_RESULT = 0 | 1
SQL_BUFFER_RESULT будет заносить результат выполнения команд SELECT во
временную таблицу. Это поможет MySQL раньше освободить блокировки таблиц и
окажется полезным в случаях, когда требуется значительное время для
пересылки результирующего набора данных клиенту.
LOW_PRIORITY_UPDATES = 0 | 1
INSERT, UPDATE, DELETE и
LOCK TABLE WRITE будут ожидать, пока не будет ни одной ожидающей решения
команды SELECT или LOCK TABLE READ на обрабатываемой таблице.
Эта переменная раньше называлась SQL_LOW_PRIORITY_UPDATES.
MAX_JOIN_SIZE = значение | DEFAULT
SELECT, которым, возможно, придется обрабатывать более,
чем указанное значение комбинаций строк. Установив эту величину, можно определить
команды SELECT, в которых ключи используются неправильно и которые,
возможно, потребуют длительного времени для исполнения. При установке этой
опции в величину, отличную от DEFAULT, сбрасывается флаг SQL_BIG_SELECTS.
Если вновь установить флаг SQL_BIG_SELECTS, то переменная
SQL_MAX_JOIN_SIZE будет игнорироваться. Значение по умолчанию для этой
переменной можно установить, запустив mysqld с -O max_join_size=#.
Эта переменная раньше называлась SQL_MAX_JOIN_SIZE.
Следует
учитывать, что если результат запроса всегда находится в кэше запросов, то
упомянутая выше проверка выполняться не будет. Вместо этого MySQL будет
отсылать результат клиенту, поскольку результат запроса уже вычислен и
отсылка его клиенту не создаст нагрузки для сервера.
QUERY_CACHE_TYPE = OFF | ON | DEMAND
QUERY_CACHE_TYPE = 0 | 1 | 2
| Опция | Описание |
| 0 or OFF | Не кэшировать или не извлекать результаты |
| 1 or ON | Кэшировать все результаты за исключением запросов SELECT SQL_NO_CACHE ...
|
| 2 or DEMAND | Кэшировать только запросы SELECT SQL_CACHE ...
|
SQL_SAFE_UPDATES = 0 | 1
UPDATE или DELETE, в которых не используется ключ или LIMIT в
выражении WHERE. Это позволяет обнаружить ошибочные обновления при ручном
создании команд SQL.
SQL_SELECT_LIMIT = value | DEFAULT
SELECT. Если SELECT
содержит выражение LIMIT, то LIMIT превосходит по старшинству величину в
SQL_SELECT_LIMIT. Для нового соединения значение по умолчанию равно
"unlimited". Если предел был изменен, то его можно вернуть в значение по
умолчанию указанием величины DEFAULT в выражении SQL_SELECT_LIMIT.
SQL_LOG_OFF = 0 | 1
SUPER. Это не
относится к журналу обновлений!
SQL_LOG_UPDATE = 0 | 1
SUPER. Это не
относится к стандартному журналу!
SQL_QUOTE_SHOW_CREATE = 0 | 1
SHOW CREATE TABLE будет заключать в кавычки
имена таблиц и столбцов. Имеет значение Включено по умолчанию, чтобы
работала репликация таблиц с изощренными именами столбцов (see section 4.5.6.8 SHOW CREATE TABLE).
TIMESTAMP = timestamp_value | DEFAULT
timestamp_value должна представлять
системное время Unix, а не временную метку MySQL.
LAST_INSERT_ID = #
LAST_INSERT_ID(). Хранится в
журнале обновлений при использовании функции LAST_INSERT_ID() в команде,
обновляющей таблицу.
INSERT_ID = #
INSERT или ALTER TABLE при внесении величины AUTO_INCREMENT. В основном
используется с журналом обновлений.
noatime. Использование этой опции позволяет
пропускать обновление при последнем обращении в данном режиме и тем
самым избежать поиска по диску.
hdpram для конфигурации
интерфейса диска! Приведенные ниже опции для hdparm зарекомендовали
себя как очень полезные для MySQL (и, возможно, для многих других
приложений):
hdparm -m 16 -d 1Следует учитывать, что производительность/надежность при использовании приведенных выше рекомендаций зависит от конкретного оборудования, так что мы настоятельно рекомендуем вам основательно протестировать систему после использования
hdparm! Для получения более подробной информации о
применении hdparm, обращайтесь, пожалуйста, к соответствующей странице
руководства! Некомпетентное применение hdparm может привести к разрушению
файловой системы. Прежде чем экспериментировать, сделайте полную резервную
копию!
async - для того, чтобы данная файловая система могла обновляться
асинхронно. Если ваш компьютер достаточно стабилен, это должно
обеспечить повышение производительности без слишком большой потери
надежности (под Linux этот флаг включен по умолчанию).
noatime.
Таблицы и базы данных можно перемещать из каталога баз данных в другие места, заменив их символическими ссылками на новые адреса. Это можно сделать, например, для того, чтобы поместить базу данных в файловую систему с большим количеством свободного места или чтобы увеличить скорость системы путем распространения таблиц на иной диск.
Рекомендуется создавать символические ссылки на другой диск для баз данных, а символические ссылки для таблиц - только в крайних случаях.
Для создания символической ссылки для базы данных вначале следует создать каталог на некотором диске, где имеется свободное место, а затем создать символическую ссылку на него из каталога баз данных MySQL.
shell> mkdir /dr1/databases/test shell> ln -s /dr1/databases/test mysqld-datadir
MySQL не поддерживает ссылку из одного каталога на несколько баз данных.
Замещение каталога базы данных символической ссылкой будет хорошо работать
только в случае, если вы не создаете символическую ссылку между базами
данных. Предположим, имеется база данных db1 в каталоге данных MySQL и
создается символическая ссылка db2, указывающая на db1:
shell> cd /path/to/datadir shell> ln -s db1 db2
Теперь для любой таблицы tbl_a в db1 должна существовать таблица tbl_a в
db2. Если один поток обновляет db1.tbl_a, а другой поток - db2.tbl_a, то
возникнут проблемы.
Если описанная выше возможность действительно необходима, то нужно
изменить следующий код в mysys/mf_format.c:
if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))
на
if (1)
Под Windows можно использовать внутренние символические ссылки на каталоги
(путем компиляции MySQL с -DUSE_SYMDIR). Это позволяет размещать различные
базы данных на различных дисках (see section 2.6.2.5 Распределение данных в Windows между несколькими различными дисками).
Не следует использовать символические ссылки для таблиц в версиях до MySQL
4.0, где при работе с ними требуется особая тщательность. Проблема
заключается в том, что, если запускаются команды ALTER TABLE, REPAIR TABLE
или OPTIMIZE TABLE на таблице, связанной символической ссылкой, то
символические ссылки будут удалены и заменены исходными файлами. Это
происходит потому, что любая вышеназванная команда работает путем создания
временного файла в каталоге базы данных и по завершении команды происходит
замещение исходного файла временным.
Не следует связывать символическими ссылками таблицы в системах, где вызов
функции realpath() работает не полностью (по крайней мере,
realpath() поддерживают Linux и Solaris).
В MySQL 4.0 символические ссылки полностью поддерживаются только для
таблиц MyISAM. Для других типов таблиц при выполнении какой-либо из
вышеупомянутых команд могут возникать непонятные проблемы.
Обработка символических ссылок в MySQL 4.0 происходит следующим образом
(это в основном относится только к таблицам MyISAM).
mysqld) или с помощью команды INDEX/DATA
DIRECTORY="path-to-dir" в CREATE TABLE (see section 6.5.3 Синтаксис оператора CREATE TABLE).
myisamchk не замещает символическую ссылку на индекс/файл, а работает
напрямую с файлами, на которые указывает символическая ссылка. Все
временные файлы будут создаваться в том же каталоге, где находится
файл данных/индексов.
mysqld в качестве
суперпользователя (root) и не позволять другим иметь доступ к записи
в каталоги баз данных MySQL.
ALTER TABLE RENAME, и вы не переносите
таблицу в другую базу данных, то данная символическая ссылка в каталоге базы
данных будет переименована и файл данных/индексов соответственно будет
переименован.
ALTER TABLE RENAME используется для переноса таблицы в другую
базу данных, то эта таблица будет перенесена в другой каталог базы
данных, а старые символические ссылки и файлы, на которые они
указывают, будут удалены. Иными словами, новая таблица не будет ссылкой.
--skip-symlink в mysqld для уверенности, что никто не сможет
удалить или переименовать файл вне каталога данных этого потока.
Возможности, которые пока еще не поддерживаются:
ALTER TABLE игнорирует все опции INDEX/DATA DIRECTORY="path".
CREATE TABLE не сообщает, что данная таблица имеет символические
ссылки.
mysqldump не включает в вывод информацию о символических ссылках.
BACKUP TABLE и RESTORE TABLE не признают символические ссылки.
Go to the first, previous, next, last section, table of contents.