Оптимизация в MySQL


WebMoney: WMZ Z294115950220 WMR R409981405661 WME E134003968233	Visa 4274 3200 2453 6495

14 Оптимизация в MySQL

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

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

14.1 Обзор оптимизации

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

Наиболее общие узкие места:
Дисковый поиск. Это требует времени для диска, чтобы найти часть данных. С современными дисками среднее время для этого обычно ниже, чем 10ms, так что мы можем в теории делать приблизительно 1000 позиционирований. Способ оптимизировать это состоит в том, чтобы распространить данные на несколько дисков, работающих параллельно.
Чтение и запись на диск. Когда диск в правильной позиции, мы должны прочитать с него данные. С современными дисками один диск доставляет что-то около 70-80 Mb данных в секунду. Это проще для оптимизации, чем поиск потому, что Вы можете читать параллельно с нескольких дисков.
Циклы CPU. Когда мы имеем данные в основной памяти (или если они уже были там), мы должны обработать их, чтобы добраться до нашего результата. Наличие маленьких таблиц, сравнимых по размеру с памятью, наиболее общий фактор ограничения быстродействия системы.
Скорость памяти. Когда CPU нужны данные, пропускная способность памяти становится узким местом. Это бывает редко, но лучше всего про него помнить.

14.1.1 Ограничения в MySQL

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

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

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

14.1.2 Мобильность

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

Чтобы делать сложную прикладную программу переносной, Вы должны выбрать ряд серверов SQL, с которыми она должна работать.

Вы можете использовать MySQL программу crash-me/web-page http://www.mysql.com/information/crash-me.php, чтобы найти функции, типы и ограничения, которые Вы можете использовать с выбором сервера базы данных. Crash-me проверяет далеко не все возможное, но тест выполнен для 450 задач.

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

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

Результаты тестов есть на http://www.mysql.com/information/benchmarks.html.

Как Вы можете видеть в этих результатах, все базы данных имеют некоторые слабые пункты. То есть они имеют различные компромиссы проекта, которые ведут к различному поведению.

Если Вы боретесь за независимость базы данных, Вы должны получить данные по проблемным зонам каждого сервера SQL. MySQL ОЧЕНЬ быстр в поиске и модифицировании, но будет иметь проблему при смешивании медленных чтений и записей на той же самой таблице. Oracle, с другой стороны, имеет большую проблему, когда Вы пробуете обращаться к строкам, которые Вы недавно модифицировали (пока их не сбросят на диск). Базы данных с транзакциями вообще не очень хороши при производстве итоговых таблиц из таблиц файла регистрации, поскольку в этом случае блокировка строки почти бесполезна.

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

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

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

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

14.1.3 Для чего использовали MySQL?

Вот что пишут авторы пакета о том, как создавалась эта СУБД:

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

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

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

Мы решили это, сохраняя всю информацию за месяц в сжатых таблицах. Мы имеем набор простых макрокоманд (скриптов), который генерирует итоговые таблицы, сгруппированные в соответствии с различными критериями (product group, customer id, store, ...) из таблиц транзакции. Отчеты представляют собой Web-страницы, которые динамически генерируются маленьким скриптом на Perl, который анализирует Web-страницу, выполняет инструкции SQL и вставляет результаты. Мы использовали бы взамен PHP или mod_perl, но они не были доступны в то время.

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

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

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

Мы не имели проблем с обработкой данных на Sun Ultra SPARCstation (2x200 Mhz). Недавно был проведен апгрейд до 2 CPU 400 Mhz UltraSPARC, и мы теперь планируем запускать транзакции обработки на уровне изделия товара, что означает десятикратное увеличение данных.

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

14.1.4 Пакет тестов MySQL Benchmark Suite

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

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

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

Например, (запуск на машине с NT 4.0):

Чтение 2000000 строк по индексу Секунд

mysql 367

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

mysql_odbc 619

db2_odbc 3460

informix_odbc 2692

ms-sql_odbc 4012

oracle_odbc 11291

solid_odbc 1801

sybase_odbc 4802

В вышеупомянутом тесте MySQL был выполнен с 8M индексным кэшем.

Мы имеем несколько больше эталонных результатов на http://www.mysql.com/information/benchmarks.html.

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

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

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.

14.1.5 Использование ваших собственных эталонных тестов

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

Для примера переносных эталонных программ рассмотрите эталонный набор MySQL. Подробности в разделе "14.1.4 Набор тестов MySQL Benchmark Suite". Вы можете брать любую программу из этого набора и изменять ее для Ваших потребностей. Делая это, Вы можете опробовать различные решения Вашей проблемы и теста, который в самом деле является самым быстрым решением для Вас.

Некоторые проблемы происходят только тогда, когда система очень тяжело загружена. В каждом из этих случаев это обычно проблема с базисным проектом (просмотр таблицы НЕ хорош при высокой загрузке) или есть трудности с OS/Library. Большинство этих случаев было бы НАМНОГО проще исправить, если система еще не запущена в работу.

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

14.2 Оптимизация SELECT и разных запросов

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

Если Вы не имеете никаких выполненных инструкций GRANT, MySQL оптимизирует проверку разрешений.

Если Ваша проблема связана с некоторой явной функцией 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(loop_count,expression) представляет собой хороший инструмент, чтобы выяснить, связана ли эта проблема с Вашим запросом.

14.2.1 Синтаксис EXPLAIN (получение информации о SELECT)

EXPLAIN tbl_name or EXPLAIN SELECT select_options

EXPLAIN tbl_name представляет собой синоним для DESCRIBE tbl_name или SHOW COLUMNS FROM tbl_name.

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

С помощью EXPLAIN Вы можете видеть, когда Вы должны добавить индексы к таблицам, чтобы получить более быстрый SELECT, который использует индексы, чтобы найти записи. Вы можете также видеть, соединяет ли оптимизатор таблицы в оптимальном порядке. Чтобы вынуждать оптимизатор использовать специфический порядок объединения для инструкции SELECT, добавьте предложение STRAIGHT_JOIN.

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

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

table
Таблица, к которой строка вывода обращается.
type
Тип объединения. Информация относительно различных типов дана ниже.
possible_keys
Столбец possible_keys указывает, какие индексы MySQL мог бы использовать, чтобы найти строки в этой таблице. Обратите внимание, что этот столбец полностью не зависит от порядка таблиц. Это означает, что некоторые из ключей в possible_keys не могут быть пригодны для использования практически со сгенерированным порядком таблицы. Если этот столбец пуст, не имеется никаких релевантных индексов. В этом случае, Вы можете улучшить эффективность Вашего запроса, исследуя предложение WHERE, чтобы увидеть, обращается ли оно к некоторому столбцу или столбцам, которые были бы подходящими для индексации. Если так, создайте соответствующий индекс, и проверьте запрос с помощью EXPLAIN снова. Подробности в разделе "7.4 Синтаксис ALTER TABLE ". Чтобы увидеть, какие индексы таблица имеет, вызовите SHOW INDEX FROM tbl_name.
key
Столбец key указывает ключ, который MySQL фактически решил использовать. Если индекс не выбран, здесь будет NULL. Если MySQL выбирает неправильный индекс, Вы можете вынуждать MySQL использовать другой индекс, используя myisamchk --analyze. Можно применить и USE INDEX/IGNORE INDEX, подробности в разделе "8.1.1 Синтаксис JOIN".
key_len
Столбец key_len указывает длину ключа, который MySQL решил использовать. Длина равна NULL, если key равен NULL. Обратите внимание, что это сообщает нам, сколько частей составного ключа фактически использует MySQL.
ref
Столбец ref показывает, которые столбцы или константы используются с key, чтобы выбрать строки из таблицы.
rows
Столбец rows указывает число строк, которые MySQL должен исследовать, чтобы выполнить запрос.
Extra
Этот столбец содержит дополнительную информацию относительно того, как MySQL решит запрос. Имеется объяснение различных текстовых строк, которые могут быть найдены в этом столбце:

Distinct
MySQL не будет продолжать искать большее количество строк для текущей комбинации строк после того, как найдет первую строку соответствий.
Not exists
MySQL был способен делать оптимизацию 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: #)
MySQL не смог найти подходящий индекс для использования. Вместо этого для каждой комбинации строк в обрабатываемых таблицах будет выполняться поиск хорошего индекса для применения (если они есть). Найденный индекс будет использован для получения строк из таблицы. Это не особенно быстро, но все же быстрее, чем объединение без индексов.
Using filesort
MySQL должен сделать дополнительный проход, чтобы выяснить, как получить строки в сортируемом порядке. Сортировка выполнена, проходя все строки согласно типу объединения (join type) и сохраняя ключ сортировки+указатель на строку для всех строк, которые соответствуют WHERE. Затем ключи сортируются. В заключение строки будут получены в сортируемом порядке.
Using index
Информация столбца получена из таблицы с применением только индексного дерева без дополнительного поиска для чтения актуальной строки. Это может быть сделано только, если все столбцы для таблицы являются частью одного и того же индекса.
Using temporary
Чтобы решить запрос, MySQL должен создать временную таблицы для хранения промежуточных результатов вычислений и поиска. Это обычно происходит, если Вы выполнили ORDER BY на наборе столбцов, отличном от того, который был задан в предложении GROUP BY.
Where used
Предложение WHERE будет использоваться, чтобы ограничить то, которые строки будут согласованы для следующей таблицы или посланы пользователю. Если Вы не имеете эту информацию, и таблица имеет тип ALL или index, Вы можете иметь что-то неправильное в Вашем запросе (если Вы не предполагаете выбирать/исследовать все строки из таблицы). Если Вы хотите получать ответы на Ваши запросы с такой скоростью, с какой только возможно, Вы должны рассмотреть применение Using filesort и Using temporary.

Различные типы объединения перечислены ниже в порядке от лучших к худшим:

system
Таблица имеет только одну строку (= system table). Это особый случай объединения типа 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, добавляя большее количество индексов, так, чтобы строка могла быть найдена, основываясь на постоянных значениях или значениях столбца более ранних таблиц.

Вы можете получать хорошую индикацию относительно того, насколько хорошим является объединение, умножая все значения в столбце rows вывода EXPLAIN. Это должно сообщить Вам грубо, сколько строк MySQL должен исследовать, чтобы выполнить запрос. Это число также используется, когда Вы ограничиваете запросы переменной max_join_size. Подробности в разделе "14.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 (первичный ключ)

do CUSTNMBR (первичный ключ)

Значения 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 where used 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

Это еще не совершенно, но намного лучше. Эта версия выполнена быстрее.

Второе исправление может быть сделано, чтобы устранить несоответствия длин столбцов для сравнений 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,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used 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,ClientID,ActualPC NULL NULL NULL 3872 where used 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. Чтобы оптимизировать запрос, Вы должны проверить, являются ли числа близкими к истине. Если это не так, Вы можете получить лучшую эффективность, используя STRAIGHT_JOIN в Вашей инструкции SELECT и пробуя внести в список таблицы в различном порядке в предложении FROM запроса.

14.2.2 Ожидаемая производительность запросов

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

В MySQL блок индекса обычно равен 1024 байтам, а длина указателя данных составляет 4 байта. 500000 строк с индексом длиной в 3 (medium integer) обрабатываются за log(500000)/log(1024/3*2/(3+4))+1=4.

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

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

Обратите внимание, что вышеупомянутое не означает, что Ваша прикладная программа будет требовать сложности N log N! Пока все кэшируется OS или SQL-сервером, работа будет идти только незначительно медленнее в то время, как таблица становится большей. После того, как данные становятся слишком большими, чтобы кэшироваться, работа будет идти намного медленнее, пока Ваши прикладные программы ограничены дисковыми установками (которые растут как N log N). Чтобы избежать этого, увеличьте индексный кэш, поскольку данные растут. Подробности в разделе "14.5.2 Настройка параметров сервера ".

14.2.3 Скорость запросов SELECT

Вообще, когда Вы хотите сделать медленный SELECT ... WHERE быстрее, первое, что подлежит проверке, можете или нет Вы добавить индекс. Подробности в разделе "14.4.3 Как MySQL использует индексы". Все ссылки между различными таблицами должны обычно делаться с индексами. Вы можете использовать команду EXPLAIN, чтобы определить, которые индексы используются для SELECT. Подробности в разделе "14.2.1 Синтаксис EXPLAIN (получение информации о SELECT)". Некоторые общие советы:

Чтобы оптимизировать запросы лучше, выполните myisamchk --analyze на таблице после того, как она была загружена релевантными данными. Это модифицирует значение для каждой индексной части, которая указывает среднее число строк, которые имеют то же самое значение. Для уникальных индексов, это всегда 1, конечно. MySQL использует это, чтобы решить, которые индексы выбрать, когда Вы подключаете две таблицы с неконстантным выражением. Вы можете проверять результат из analyze выполнением SHOW INDEX FROM table_name и исследования столбца Cardinality.
Чтобы сортировать индекс и данные согласно индексу, используйте myisamchk --sort-index --sort-records=1 (если Вы хотите сортировать на индексе 1). Если Вы имеете уникальный индекс, из которого Вы хотите читать все записи, это хороший способ сделать процесс быстрее. Обратите внимание, однако, что эта сортировка не написана оптимально и будет брать довольно длительное время для большой таблицы!

14.2.4 Как MySQL оптимизирует предложение WHERE

Оптимизация WHERE помещена в часть SELECT потому, что она обычно используется с SELECT, но теми же самыми методами оптимизируются инструкции 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

Постоянные выражения, используемые индексами, оценены только однажды.
COUNT(*) на одиночной таблице без WHERE получен непосредственно из информации таблицы. Это также выполнено для любого выражения NOT NULL, когда используется только с одной таблицей.
Раннее обнаружение недопустимых постоянных выражений. MySQL быстро обнаруживает, что некоторые инструкции SELECT невозможны, и не возвращает никаких строк.
HAVING объединено с WHERE, если Вы не используете GROUP BY или групповые функции (COUNT(), MIN()...).
Для каждого подобъединения более простой WHERE будет создан, чтобы получить быструю оценку WHERE для каждого подобъединения, а также пропускать все записи как можно скорее.
Все постоянные таблицы читаются сначала, перед любыми другими таблицами в запросе. Постоянная таблица:
Пустая таблица или таблица с 1 строкой.
Таблица, которая используется с предложением 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 использует временную таблицу в памяти.
Каждый индекс таблицы делает запрос, и самый лучший индекс, который охватывает меньшее количество, чем 30% строк, используется. Если никакой такой индекс не может быть найден, применяется быстрый просмотр таблицы.
В некоторых случаях 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,... ;

14.2.5 Как MySQL оптимизирует 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 будет найдена.

14.2.6 Как MySQL оптимизирует LEFT JOIN и RIGHT JOIN

Действие A LEFT JOIN B в MySQL выполнено следующим образом:

Таблица B установлена, чтобы зависеть от таблицы A, и всех таблиц, которые зависят от A.
Таблица установлена A, чтобы зависеть от всех таблиц за исключением B, которые используются в условии LEFT JOIN.
Все условия LEFT JOIN перемещаются в WHERE.
Все стандартные оптимизации объединения выполнены за исключением того, что таблица всегда читается после всех таблиц, от которых она зависит. Если имеется круговая зависимость, то MySQL выдаст ошибку.
Все стандартные оптимизации WHERE выполнены.
Если имеется строка в A, которая соответствует предложению WHERE, но не имеется никакой строки в B, которая соответствует условию LEFT JOIN, то дополнительные строки B сгенерированы со всем набором столбцов в NULL.
Если Вы используете LEFT JOIN, чтобы найти строки, которые не существуют в некоторой таблице, и Вы имеете следующий тест: column_name IS NULL в части WHERE, где 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.

Исправление в этом случае должно изменить запрос на:

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

14.2.7 Как MySQL оптимизирует LIMIT

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

Если Вы выбираете только несколько строк с LIMIT, MySQL использует индексы в некоторых случаях, когда он обычно предпочел бы сделать полный просмотр таблицы.
Если Вы используете LIMIT # в сочетании с ORDER BY, MySQL закончит сортировку, как только найдет первые # строк вместо того, чтобы сортировать целую таблицу.
При объединении LIMIT # с DISTINCT MySQL остановится, как только найдет # уникальных строк.
В некоторых случаях GROUP BY может быть решено, читая ключ по порядку (или сортируя по ключу) с последующим вычислением результата до изменений значения ключа. В этом случае LIMIT # не будет вычислять никакую ненужную операцию GROUP BY.
Как только MySQL послал первые # строк пользователю, он прервет текущий запрос.
LIMIT 0 будет всегда быстро возвращать пустой набор. Это полезно, чтобы проверить запрос и получить типы столбцов результата.
Размер временных таблиц использует LIMIT #, чтобы вычислить, сколько места необходимо, чтобы решить этот запрос.

14.2.8 Скорость запросов INSERT

Время, нужное, чтобы вставить запись, состоит приблизительно из:

Установление связи: (3)
Отправка запроса на сервер: (2)
Обработка запроса: (2)
Вставка записей: (1 x число записей)
Вставка индексов: (1 x количество индексов)
Завершение сеанса: (1)

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

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

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

Если Вы вставляете много строк от того же самого пользователя в то же самое время, используйте многократные инструкции со списком значений INSERT. Это намного быстрее, чем использование отдельных инструкций INSERT. Грамотная настройка переменной myisam_bulk_insert_tree_size может сделать это даже быстрее. Подробности в разделе "4.10.4 SHOW VARIABLES".
Если Вы вставляете много строк из различных клиентов, Вы можете получить большее быстродействие, используя инструкцию INSERT DELAYED. Подробности в разделе "8.3 Синтаксис INSERT".
Обратите внимание, что с MyISAM Вы можете вставлять строки в то же самое время, когда работает SELECT, если не имеется никаких удаленных строк в таблицах.
При загрузке таблицы из текстового файла применяется LOAD DATA INFILE. Это примерно в 20 раз быстрее, чем использование набора команд INSERT. Подробности в разделе "8.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, чтобы сделать ее еще меньше.
Освежите индексы с помощью myisamchk -r -q /path/to/db/tbl_name. Это создаст индексное дерево в памяти перед записью его на диск, что является намного быстрее потому, что это избегает большого количества дисковых установок. Возникающее в результате индексное дерево также совершенно сбалансировано.
Выполните инструкцию FLUSH TABLES или команду оболочки mysqladmin flush-tables.
Эта процедура будет встроена в LOAD DATA INFILE в некоторой будущей версии 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. Блокировка не является необходимой, если Вы можете вставлять все строки с одиночной инструкцией. Блокировка будет также снижать общее время тестов с несколькими подключениями, но максимальное время ожидания для некоторых потоков повысится (потому, что они ждут блокировки). Например:
thread 1 does 1000 inserts thread 2, 3, and 4 does 1 insert thread 5 does 1000 inserts
Если Вы не используете блокировку, 2, 3 и 4 закончаться перед 1 и 14. Если Вы используете блокировку, 2, 3 и 4, вероятно, не будут заканчиваться прежде, чем 1 или 5, но общее время должно быть приблизительно на 40% лучше. Так как операции INSERT, UPDATE и DELETE очень быстры в MySQL, Вы получите лучшую полную эффективность, добавляя блокировки вокруг всего, что делает больше, чем приблизительно 5 вставок или модификаций в строке. Если Вы делаете очень много вставок в строке, Вы могли бы делать LOCK TABLES, сопровождаемые UNLOCK TABLES время от времени (примерно каждые 1000 строк), чтобы позволить другим потокам доступ к таблице. Конечно же, LOAD DATA INFILE все равно намного быстрее для загрузки данных.

Чтобы дополнительно получить несколько большее быстродействие для операций LOAD DATA INFILE и INSERT, увеличьте буфер ключа. Подробности в разделе "14.5.2 Настройка параметров сервера".

14.2.9 Скорость запросов UPDATE

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

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

Обратите внимание, что, при работе с динамическим форматом записи модификация записи на более длинную может привести к разделению ее на фрагменты. Так что, если Вы делаете это часто, очень важно иногда вызывать OPTIMIZE TABLE. Подробности в разделе "4.6 Синтаксис OPTIMIZE TABLE".

14.2.10 Скорость запросов DELETE

Если надо удалить все строки из таблицы, примените вызов TRUNCATE TABLE table_name. Подробности в разделе "8.7 Синтаксис TRUNCATE".

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

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

Используйте постоянные подключения к базе данных, чтобы избежать накладных расходов на текущие подключения. Если Вы не можете использовать постоянные подключения, и Вы делаете много новых подключений, стоит изменить значение переменной thread_cache_size. Подробности в разделе "14.5.2 Настройка параметров сервера ".
Обязательно проверьте, что все Ваши запросы в самом деле используют индексы, которые Вы создали в таблицах. В MySQL Вы можете сделать это командой EXPLAIN. Подробности в разделе "14.2.1 Синтаксис EXPLAIN (получение информации о SELECT)".
Избегайте сложных запросов SELECT на таблицах, которые часто модифицируются. Это должно помочь избежать проблем с блокировкой таблицы.
Новые таблицы MyISAM могут вставлять строки в таблицу без удаленных строк в то же самое время, когда другой запрос ведет чтение. Если это важно для Вас, Вы должны рассмотреть методы, где Вы не должны удалить строки или выполните OPTIMIZE TABLE после того, как Вы удалили много строк сразу.
Используйте вызов ALTER TABLE ... ORDER BY expr1,expr2... если Вы обычно получаете строки в порядке expr1,expr2,... . Используя эту опцию после внесения больших изменений для таблицы, Вы можете получить значительно более высокую эффективность.
В некоторых случаях может иметь смысл представлять столбец, который является хэшем, основанным на информации из других столбцов. Если этот столбец короткий и приемлемо уникальный, это может быть намного быстрее, чем большой индекс на многих столбцах. В MySQL это очень легко в использовании: SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND col_1='constant' AND col_2='constant'
Для таблиц, которые часто изменяются, Вы должен пробовать избежать любых столбцов типов VARCHAR или BLOB. Вы получите динамическую длину строки, как только Вы используете хоть один стобец VARCHAR или BLOB.
Если Вы очень часто должны вычислять значения, основанные на информации из большого количества строк (подобно количеству), вероятно, намного лучше представить новую таблицу и модифицировать счетчик в реальном времени. Модификация типа UPDATE table set count=count+1 where index_column=constant очень быстрая! Это действительно важно, когда Вы используете базы данных подобные MySQL, которые имеют только блокировку уровня таблицы. Это также даст лучшую эффективность с большинством баз данных, поскольку администратор блокировки строки в этом случае будет иметь куда меньше работы.
Если Вы должны собрать статистику из больших таблиц файла регистрации, используйте итоговые таблицы вместо того, чтобы просмотреть целую таблицу. Поддержание резюме должно быть намного быстрее, чем попытка сделать живую статистику. Намного быстрее получить новые итоговые таблицы из файлов регистрации, когда происходит изменение, чем менять работающее приложение!
Если возможно, нужно классифицировать отчеты как "живые" или "статистические", где данные, необходимые для статистических отчетов, сгенерированы только, исходя из итоговых таблиц, которые в свою очередь были сгенерированы из фактических данных.
Воспользуйтесь преимуществом того факта, что столбцы имеют значения по умолчанию. Вставляйте значения явно только, когда значение, которое будет вставлено, отличается от значения по умолчанию. Это уменьшает синтаксический анализ, который MySQL должен сделать, и улучшает быстродействие вставки.
В некоторых случаях удобно упаковывать и сохранить данные в blob. В этом случае Вы должны добавить некоторый дополнительный код к Вашей прикладной программе, чтобы упаковать/распаковать данные, но это может сохранить много времени доступа в некоторой стадии. Это удобно, когда Вы имеете данные, которые явно не соответствуют статической структуре таблицы.
Обычно Вы должны пробовать хранить все данные неизбыточными (что названо третьей нормальной формой в теории базы данных), но Вы не должны бояться дублирования или создания таблиц-резюме, если Вы нуждаетесь в них, чтобы получить большее быстродействие.
Сохраненные процедуры или UDF (определяемой пользователем функции) может быть хорошим способом получить большую эффективность. В этом случае Вы должны, однако, всегда иметь способ делать это некоторым другим (более медленным) путем, если Вы используете СУБД, которая не поддерживает это.
Вы можете всегда получать неплохие результаты, кэшируя запросы/ответы в Вашей прикладной программе и пробуя делать много вставок/модификаций в то же самое время. Если Ваша база данных поддерживает блокировки таблиц (подобно MySQL и Oracle), это должно помочь гарантировать, что индексный кэш сбрасывается только однажды после выполнения всех модификаций.
Используйте INSERT /*! DELAYED */, если Вы не должны знать, когда Ваши данные будут записаны. Это ускоряет дела потому, что много записей могут быть выполнены за один дисковый обмен.
Используйте INSERT /*! LOW_PRIORITY */, когда Вы хотите, чтобы Ваши вызовы select были более важными.
Используйте SELECT /*! HIGH_PRIORITY */, чтобы получить select, обходящий очередь. То есть select будет выполнен, даже если имеется кто-то ждущий, чтобы сделать запись в таблицу.
Используйте многострочную инструкцию INSERT, чтобы сохранить много строк одной командой SQL (многие серверы SQL поддерживают это).
Используйте LOAD DATA INFILE, чтобы загрузить большие количества данных. Это быстрее, чем нормальные вставки, а будет еще быстрее, когда myisamchk интегрирован в mysqld.
Используйте столбцы с поддержкой AUTO_INCREMENT, чтобы сделать уникальные значения.
Используйте OPTIMIZE TABLE время от времени, чтобы избежать фрагментации при использовании динамического формата таблицы. Подробности в разделе "4.6 Синтаксис OPTIMIZE TABLE".
При использовании нормальной установки Web-сервера, изображения должны быть сохранены как файлы. То есть сохраните только ссылку на файл в базе данных. Основная причина для этого в том, что нормальный Web-сервер намного лучше при кэшировании файлов, чем содержание базы данных. Так что намного проще получить быструю систему, если Вы используете файлы.
Используйте таблицы в памяти для некритических данных, к которым обращаются часто (подобно информации относительно последнего показанного баннера для пользователей, которые не имеют cookie).
Столбцы с идентичной информацией в различных таблицах должны быть объявлены идентично и иметь одинаковые имена. До Version 3.23 Вы получали медленные объединения в противном случае. Старайтесь делать имена проще (например, name вместо customer_name в таблице заказчиков). Чтобы сделать Ваши имена переносными на другие SQL-серверы, Вы должны озаботиться тем, чтобы они не превышали в длину 18 символов.
Если Вы нуждаетесь в ДЕЙСТВИТЕЛЬНО высокой скорости, Вы должны смотреть на интерфейсы низкого уровня для хранения данных, которые поддерживают различные серверы SQL! Например, обращаясь к MySQL MyISAM непосредственно, Вы могли бы получить увеличение быстродействия в 2-5 раз в сравнении с использованием интерфейса SQL. Чтобы сделать это, данные должны быть на том же самом сервере, что и прикладная программа, и обычно к ним можно обратиться только одним процессом (потому, что внешняя блокировка файла работает очень медленно). Можно было устранить вышеупомянутые проблемы, представляя команды MyISAM низкого уровня в сервере MySQL (это могло бы быть одним простым способом получить большее количество эффективности, если необходимо).
Во многих случаях быстрее обратиться к данным из базы данных (используя открытое подключение), чем к текстовому файлу только потому, что база данных, вероятно, будет более компактна, чем текстовый файл (если Вы используете числовые данные), и это потребует меньшее количество дисковых доступов. Вы также уменьшите код потому, что Вы не должны анализировать Ваши текстовые файлы, чтобы найти в них границы столбца и строки.
Объявление таблицы с DELAY_KEY_WRITE=1 будет делать модифицирование индексов быстрее, поскольку они не регистрируются на диске, пока файл не закрыт. Обратная сторона в том, что Вы должны выполнить myisamchk на этих таблицах прежде, чем Вы запускаете mysqld, чтобы гарантировать, что они правильные, если что-то уничтожило mysqld в середине запроса. Поскольку информация ключа может всегда генерироваться из данных, Вы не должны терять что-нибудь, используя DELAY_KEY_WRITE.

14.3 Проблемы блокировки

14.3.1 Как MySQL блокирует таблицы

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

Метод блокировки 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;

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

Использование SQL_BUFFER_RESULT может также сделать блокировку таблицы короче. Подробности в разделе "8.1 Синтаксис SELECT".

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

14.3.2 Советы по блокировке таблицы

Код блокировки таблиц в MySQL свободен от тупиков.

MySQL использует блокировку таблицы (вместо блокировки строки или столбца) на всех типах таблицы, за исключением BDB, чтобы достичь очень высокого быстродействия блокировки. Для больших таблиц блокировка таблицы НАМНОГО лучше, чем блокировка строки для большинства прикладных программ, но имеются, конечно, некоторые ловушки.

Для таблиц типов BDB и InnoDB MySQL использует блокировку таблицы только, если Вы скомандовали LOCK TABLES или выполняете команду, которая изменит каждую строку в таблице подобно ALTER TABLE. Для этих типов таблицы я рекомендую Вам не использовать LOCK TABLES вообще.

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

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

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

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

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

Пользователь выдает SELECT, который берет длительное время, чтобы выполниться.
Другой пользователь затем выдает UPDATE на используемой таблице. Этот клиент будет ждать, пока SELECT не закончится.
Другой пользователь выдает другую инструкцию SELECT относительно той же самой таблицы. Поскольку UPDATE имеет более высокий приоритет, чем SELECT, этот SELECT будет ждать UPDATE, чтобы закончиться. Это будет также ждать, когда закончится первый SELECT!
Поток ждет нечто подобное full disk, когда все потоки, которые хотят обращаться к прикладной таблице, будут также помещены в состояние ожидания до тех пор, пока большее количество дискового пространства не станет доступно.

Некоторые решения для этой проблемы:

Попытайтесь получить ускорение инструкции SELECT. Вам, вероятно, придется создавать некоторые итоговые таблицы, чтобы сделать это.
Запустите mysqld с опцией --low-priority-updates. Это даст всем инструкциям, которые модифицируют таблицу, более низкий приоритет, чем инструкции SELECT. В этом случае последняя инструкция SELECT в предыдущем сценарии выполнилась бы перед инструкцией INSERT.
Вы можете давать специфическую инструкцию INSERT, UPDATE или DELETE с более низким приоритетом, указав атрибут LOW_PRIORITY.
Запустите mysqld с низким значением для max_write_lock_count, чтобы дать блокировки READ после того, как отработает некоторое число блокировок WRITE.
Вы можете определить, что все модификации из специфического потока должны быть выполнены с низким приоритетом, используя команду SQL: SET SQL_LOW_PRIORITY_UPDATES=1. Подробности в разделе "14.5.6 Синтаксис SET".
Вы можете определять, что специфический SELECT очень важен с помощью атрибута HIGH_PRIORITY. Подробности в разделе "8.1 Синтаксис SELECT".
Если Вы имеете проблемы с INSERT, объединенной с SELECT, переключитесь, чтобы использовать новые таблицы типа MyISAM, поскольку они поддерживают параллельную работу вызовов SELECT и INSERT.
Если Вы главным образом смешиваете инструкции INSERT и SELECT, атрибут DELAYED в вызове INSERT, вероятно, решит Ваши проблемы. Подробности в разделе "8.3 Синтаксис INSERT".
Если Вы имеете проблемы с SELECT и DELETE, опция LIMIT в инструкции DELETE может помочь. Подробности в разделе "8.6 Синтаксис DELETE".

14.4 Оптимизация структуры базы данных

14.4.1 Выбор дизайна и интерьера

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

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

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

Просмотр таблицы намного медленнее потому, что Вы должны пролистать индексы, чтобы добраться до данных.
Вы не можете использовать только индексную таблицу, чтобы получить данные для решения запроса.
Вы теряете много места, поскольку Вы должны дублировать индексы из узлов (поскольку Вы не можете сохранять строку в узлах).
Удаление будет вырождать таблицу через какое-то время (поскольку индексы в узлах обычно не модифицируются на запросах удаления).
Тяжелее кэшировать ТОЛЬКО индексные данные.

14.4.2 Сделайте данные как можно меньше

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

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

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

Используйте наиболее эффективные (самые маленькие) возможные типы. MySQL имеет много разных специализированных типов, которые сохраняют дисковое пространство и память.
Используйте меньшие целочисленные типы, если возможно, чтобы получить меньшие таблицы. Например, MEDIUMINT часто бывает куда лучше, чем INT в чистом виде.
Объявите столбцы как NOT NULL, если это возможно. Это сделает все быстрее, и Вы сэкономите один бит на столбец. Обратите внимание, что, если Вы действительно нуждаетесь в NULL в Вашей прикладной программе, Вы должны определенно использовать это. Только не стоит иметь это свойство заданным на всех столбцах по умолчанию.
Если Вы не имеете столбцов переменной длины (VARCHAR, TEXT или BLOB), используется формат записи фиксированного размера. Это работает быстрее, но, к сожалению, может тратить впустую некоторое место.
Первичный индекс таблицы должен быть так короток, как только возможно. Это делает идентификацию одной строки простой и эффективной.
Для каждой таблицы Вы должны решить, который метод хранения и индексации надлежит в ней использовать.
Создайте только те индексы, в которых Вы действительно нуждаетесь. Индексы хороши для поиска, но плохи, когда Вы должны сохранить что-то быстро. Если Вы обычно обращаетесь к таблице, используя поиск на комбинации столбцов, сделайте индекс на них. Первая индексная часть должна быть наиболее используемым столбцом. Если Вы ВСЕГДА используете много столбцов, Вы должны применить столбец с большим количеством дубликатов первым, чтобы получить лучшее сжатие индекса.
Если вероятно, что столбец имеет уникальный префикс на некотором числе первых символов, лучше индексировать только этот префикс. MySQL поддерживает индекс на части символьного столбца. Короткие индексы быстрее не только потому, что они берут меньшее количество дискового пространства, но также и потому, что они дадут Вам большее количество попаданий в индексный кэш и таким образом меньшее количество дисковых установок. Подробности в разделе "14.5.2 Настройка параметров сервера ".
В некоторых обстоятельствах таблицу, которая бывает просмотрена очень часто, может быть полезно расчленить на две. Это особенно верно, если это таблица динамического формата, и можно использовать меньшую таблицу статического формата, которая может применяться, чтобы найти релевантные строки при просмотре этой таблицы.

14.4.3 Как MySQL использует индексы

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

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

Индексы используются для:
Быстрого поиска строк, которые соответствуют предложению WHERE.
Получения строк из других таблиц при выполнении объединений.
Поиска значений MAX() или MIN() для специфического индексированного столбца. Это оптимизировано препроцессором, который проверяет, используете ли Вы WHERE key_part_#=constant на всех частях ключа <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. Индекс может также использоваться, даже если ORDER BY не соответствует индексу точно, пока все неиспользуемые индексные части и все дополнительные столбцы в ORDER BY представляют собой константы в предложении WHERE. Следующие запросы используют индекс, чтобы решить часть ORDER BY:
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3; SELECT * FROM foo WHERE column=constant ORDER BY column, key_part1; SELECT * FROM foo WHERE key_part1=const GROUP BY key_part2;

В некоторых случаях запрос может быть оптимизирован, чтобы получить значения без того, чтобы консультироваться с файлом данных. Если все используемые столбцы для некоторой таблицы числовые и формируют крайний левый префикс для некоторого ключа, значения могут быть получены прямо из индексного дерева для большего быстродействия:
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 не константа.

Поиск, использующий 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 /* index = 1 OR index = 2 */ ... WHERE index_part1='hello' AND index_part_3=5 /* optimized like "index_part1='hello'" */ ... WHERE index1=1 and index2=2 or index1=3 and index3=3; /* Can use index on index1 but not on index2 or index 3 */

Эти предложения WHERE НЕ используют индексы:
... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */ ... WHERE index=1 OR A=10 /* Index is not used in both AND parts */ ... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */

Обратите внимание, что в некоторых случаях MySQL все равно не будет использовать индекс, даже если можно было бы это сделать. Это связано с особенностями логики СУБД. Некоторые из таких случаев:

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

14.4.4 Индексы столбцов

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

Максимальное число ключей и максимальная длина индекса определены в драйвере таблицы. Вы можете со всеми драйверами таблицы иметь по крайней мере 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 Version 3.23.23 или позже Вы можете также создавать специальные индексы FULLTEXT. Они используются для полнотекстового поиска. Только тип таблицы MyISAM поддерживает индексы FULLTEXT. Они могут быть созданы только из столбцов VARCHAR и TEXT. Индексация всегда выполняется над всем столбцом, частичная индексация пока не поддержана. Подробности в разделе "9.4 Полнотекстовый поиск в MySQL".

14.4.5 Многостолбцовые индексы

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";

Однако, индекс name НЕ будет использоваться в запросах:

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

За подробностями по улучшению работы с индексами в MySQL отсылаю Вас к разделу "14.4.3 Как MySQL использует индексы".

14.4.6 Как MySQL открывает и закрывает таблицы

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

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

Кэш открытых таблиц может расти до максимума, заданного table_cache (значение по умолчанию 64, это может быть изменено с помощью опции -O table_cache=# при вызове сервера mysqld). Таблица никогда не будет закрыта за исключением того случая, когда кэш заполняется, а другой поток пробует открыть таблицу, или если Вы используете команду mysqladmin refresh или mysqladmin flush-tables.

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

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

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

Вы можете проверять, является ли Ваш кэш таблицы слишком маленьким, проверяя переменную opened_tables в mysqld. Если это значение очень большое, даже если Вы не делали много вызовов FLUSH TABLES, Вы должны увеличить Ваш кэш таблицы. Подробности в разделе "4.10.3 SHOW STATUS ".

14.4.7 Проблемы с созданием большого количества таблиц в базе данных

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

14.4.8 Почему так много открытых таблиц?

Когда Вы выполняете mysqladmin status, Вы будете видеть нечто вроде этого вывода:

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

Это может несколько озадачить, если Вы имеете только 6 таблиц.

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

14.5 Оптимизация сервера MySQL

14.5.1 Настройка параметров компиляции и запуска

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

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

Другие советы и предупреждения:
Если Вы имеете достаточно RAM, Вы могли бы удалить все свопы. Некоторые операционные системы используют своп в некоторых контекстах, даже если Вы имеете свободную память.
Используйте опцию --skip-locking в MySQL, чтобы избежать внешней блокировки. Обратите внимание, что это не будет воздействовать на функциональные возможности MySQL, пока Вы выполняете только один сервер. Только не забудьте завершить сервер (или блокировать релевантные части) прежде, чем Вы выполните myisamchk. На некоторых системах эта опция обязательна потому, что внешняя блокировка не работает в любом случае. Опция --skip-locking по умолчанию включена при компиляции с MIT-pthreads потому, что flock() не полностью поддержан MIT-pthreads на всех платформах. Это также значение по умолчанию для Linux, поскольку блокировка файлов в Linux еще не безопасна. Единственный случай, когда Вы не можете использовать --skip-locking: если Вы выполняете много серверов MySQL (не клиентов) на тех же самых данных или выполняете myisamchk на таблице без предварительной блокировки сервера и сброса таблиц на диск. Вы можете применять LOCK TABLES/UNLOCK TABLES, даже если Вы используете опцию --skip-locking

14.5.2 Настройка параметров сервера

Вы можете получать заданные по умолчанию буферные размеры, используемые сервером 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 query_buffer_size current value: 0 record_buffer current value: 131072 record_rnd_buffer 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

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

shell> mysqladmin variables

Вы можете находить полное описание для всех переменных в разделе "4.10.4 SHOW VARIABLES ".

Вы можете также видеть некоторую статистику с сервера, выдавая команду SHOW STATUS. Подробности в разделе "4.10.3 SHOW STATUS".

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

При настройке сервера MySQL есть две наиболее важных переменных: key_buffer_size и table_cache.

Если Вы имеете много памяти (>=256M) и много таблиц и хотите получить максимальную эффективность с умеренным числом клиентов, Вы должны использовать нечто подобное этому:

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

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

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

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

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

Или вот это:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \ -O table_cache=32 -O record_buffer=8k \ -O net_buffer=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, которые Вы можете использовать как основу, чтобы оптимизировать Вашу систему.

Если имеется очень много подключений, может происходить ``swapping problem'', если mysqld не был конфигурирован, чтобы использовать очень небольшую память для каждого подключения. Конечно, mysqld работает много лучше, если Вы имеете достаточно памяти для всех подключений.

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

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

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

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

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

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

Вы получаете самую быструю выполнимую программу, когда Вы компонуете ее с опцией времени компоновки -static.

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

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

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

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

Имеется список некоторых измерений, которые сделали авторы пакета:
Если Вы используете pgcc и компилируете все с опцией -O6, сервер mysqld на 1% быстрее, чем с gcc 2.914.2.
Если Вы компонуете динамически (без -static), результат на 14% медленнее в Linux. Обратите внимание, что Вы все еще можете использовать динамическую библиотеку MySQL. Это критично только для сервера.
Если Вы обработаете mysqld командой strip libexec/mysqld, возникающий в результате двоичный код может быть до 4% быстрее.
Если Вы подключаетесь, используя TCP/IP вместо Unix-сокетов, результат на 7.5% медленнее на том же самом компьютере. Если Вы связываетесь с localhost, MySQL по умолчанию использует именно сокеты.
Если Вы подключаетесь, используя TCP/IP с другого компьютера по 100M Ethernet, это будет на 8-10% медленнее.
Если Вы компилируете с опцией --with-debug=full, теряете 20% скорости для большинства запросов, но некоторые запросы могут еще более замедлиться (эталонные тесты до 36%). Если Вы используете --with-debug, потери составят около 15%. Запуская mysqld, скомпилированный с опцией --with-debug=full, с --skip-safemalloc конечный результат должен быть близок к варианту --with-debug.
На Sun SPARCstation 20 SunPro C++ 4.2 на 5% быстрее, чем gcc 2.914.2.
Компиляция с помощью gcc 2.914.2 для ultrasparc с опциями -mcpu=v8 -Wa,-xarch=v8plusa дает еще 4% эффективности.
В Solaris 2.14.1 MIT-pthreads на 8-12% медленнее своих потоков Solaris на одном процессоре. С ростом загрузки и центральных процессоров различие должно стать больше.
Запуск с опцией --log-bin сделает MySQL примерно на 1% медленнее.
Компиляция на Linux-x86 с использованием gcc без frame pointers -fomit-frame-pointer или -fomit-frame-pointer -ffixed-ebp делает mysqld на 1-4% быстрее.

Дистрибутив MySQL-Linux, собранный в MySQL AB, использовал pgcc, но из-за ошибки при работе на процессорах AMD (причина в компиляторе, а не в процессорах, они-то как раз отменные!) теперь снова применяется обычный gcc, и так будет до тех пор, пока та ошибка не будет решена авторами транслятора. Тем временем, если Вы имеете не-AMD машину, Вы можете попробовать применить pgcc. Правда, не очень ясно, кто победит в этом споре: то ли более совершенный компилятор pgcc, то ли процессоры от AMD, которые просто по конструкции быстрее Intel... Стандартный двоичный дистрибутив MySQL для Linux скомпонован статически, чтобы получить большие быстродействие и переносимость.

14.5.4 Как MySQL использует память

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

Буфер ключей (переменная key_buffer_size) разделен всеми потоками. Другие буфера, используемые сервером, распределены как необходимо. Подробности в разделе "14.5.2 Настройка параметров сервера".
Каждое подключение использует некоторое специфическое для потока место: стек (заданный по умолчанию в 64КБ, переменная thread_stack), буфер подключений (переменная net_buffer_length) и буфер результатов (переменная net_buffer_length). Буфер подключений и буфер результатов будут динамически расширены до max_allowed_packet, когда это будет необходимо. Когда запрос обрабатывается, копия текущей строки запроса также распределена.
Все потоки совместно используют ту же самую основную память.
Только сжатые таблицы ISAM/MyISAM отображаются в памяти. Это потому, что 32-разрядное пространство памяти в 4GB недостаточно большое для наиболее крупных таблиц. Когда системы с 64-разрядным адресным пространством станут более распространенными, авторы пакета добавят общую поддержку для управления памятью по этому методу.
Каждый запрос, делающий последовательный просмотр таблицы, распределяет буфер чтений (переменная record_buffer).
При чтении строк в произвольном порядке (например, после сортировки) буфер для этого будет распределен, чтобы избежать дисковых установок (переменная record_rnd_buffer).
Все объединения выполнены в одном проходе, и большинство объединений может быть выполнено без применения временной таблицы. Временные таблицы с большой длиной записи или включающие столбцы BLOB сохранены на диске. Одна проблема в MySQL до Version 3.23.2 состоит в том, что, если таблица HEAP превышает размер tmp_table_size, Вы получите ошибку The table tbl_name is full. В более новых версиях это обработано автоматической заменой таблиц в памяти на дисковые по мере необходимости. Чтобы обойти эту проблему Вы можете увеличивать временный размер таблицы, устанавливая опцию tmp_table_size в mysqld или устанавливая в программе пользователя SQL-опцию SQL_BIG_TABLES. Подробности в разделе "14.5.6 Синтаксис SET". В MySQL Version 3.20 максимальный размер временной таблицы был record_buffer*16 так, что, если Вы используете эту версию, Вы должны увеличить значение record_buffer. Вы можете также запустить mysqld с опцией --big-tables, чтобы всегда сохранять временные таблицы на диске. Однако, это будет плохо воздействовать на быстродействие очень многих сложных запросов.
Большинство запросов, делающих сортировку, распределяет буфер сортировки и 0-2 временных файла в зависимости от размера набора результата. Подробности в разделе "11.4.4 Где MySQL сохраняет свои временные файлы.".
Почти весь синтаксический анализ и вычисления выполнены в локальной памяти. Никакая память не требуется для маленьких элементов. Память будет распределена только для неожиданно больших строк (это выполнено с помощью вызовов malloc() и free()).
Каждый индексный файл открыт однажды, а файл данных открыт однажды для каждого одновременно выполняемого потока. Для каждого параллельного потока распределены: структура таблицы, структура для каждого столбца и буфера размером 3*n (где n максимальная длина строки, не считая столбцы типа BLOB). BLOB использует от 5 до 8 байт плюс длина данных BLOB. Драйвер таблиц ISAM/MyISAM распределяет один буфер строк дополнительно для внутреннего использования.
Для каждой таблицы, имеющей столбцы BLOB, буфер будет расширен динамически, чтобы читать большие значения BLOB. Если Вы просматриваете таблицу, распределяется буфер такого размера, как самое большое значение BLOB.
Драйверы таблицы для всех используемых таблиц сохранены в кэше и управляются как FIFO. Обычно кэш имеет 64 записи. Если таблица использовалась двумя потоками в то же самое время, кэш содержит две записи для таблицы. Подробности в разделе "14.4.6 Как MySQL открывает и закрывает таблицы".
Команда mysqladmin flush-tables закрывает все таблицы, которые не находятся в использовании, и отмечает все таблицы, находящиеся в использовании, чтобы они были закрыты, когда работающий в настоящее время поток завершится. Это действительно освободит много памяти.

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

14.5.5 Как MySQL использует DNS

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

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

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

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

Вы можете отключать кэш hostname с помощью опции --skip-host-cache. Вы можете очищать кэш hostname с помощью команд FLUSH HOSTS или mysqladmin flush-hosts.

Если Вы не хотите позволять подключения по TCP/IP, Вы можете сделать это, запуская mysqld с опцией --skip-networking.

14.5.6 Синтаксис SET

SET [OPTION] SQL_VALUE_OPTION=value, ...

SET OPTION устанавливает различные параметры, которые воздействуют на работу клиента или сервера. Любая опция, которую Вы устанавливаете, остается в силе до завершения текущего сеанса, или пока Вы не установите ее в другое значение.

CHARACTER SET character_set_name|DEFAULT
Это отображает все строки для клиента по заданной карте символов. В настоящее время единственной опцией для character_set_name является cp1251_koi8, но Вы можете легко добавлять новые отображения, редактируя файл sql/convert.cc в дистрибутиве исходных текстов MySQL. Отображение значения по умолчанию может быть восстановлено, используя значение DEFAULT для переменной character_set_name. Обратите внимание, что синтаксис для установки опции CHARACTER SET отличается от синтаксиса для установки других параметров.
PASSWORD=PASSWORD('some password')
Устаналивает пароль для текущего пользователя. Любой неанонимный пользователь может изменять свой собственный пароль!
PASSWORD FOR user=PASSWORD('some 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. Подробности в разделе "9.2.1 Синтаксис BEGIN/COMMIT/ROLLBACK". Если установлено в 0, Вы должны использовать COMMIT/ROLLBACK, чтобы принять или отменить эту транзакцию. Подробности в разделе "9.2.1 Синтаксис BEGIN/COMMIT/ROLLBACK". Обратите внимание, что, когда Вы изменяете режим с не-AUTOCOMMIT на AUTOCOMMIT, MySQL автоматически будет делать COMMIT на открытых транзакциях.
SQL_BIG_TABLES=0|1
Если установлено в 1, все временные таблицы сохранены на диске, а не в памяти. Это будет немного медленнее, но Вы не будете получать ошибку The table tbl_name is full для больших операций SELECT, которые требуют большой временной таблицы. Значение по умолчанию для нового подключения 0 (то есть использовать временные таблицы в памяти).
SQL_BIG_SELECTS=0|1
Если установлено в 0, MySQL прервется, если SELECT, вероятно, будет брать очень длительное время. Это полезно, когда была выдана нецелесообразная инструкция WHERE. Большой запрос определен как SELECT, которому, вероятно, придется исследовать больше, чем max_join_size строк. Значение по умолчанию для нового подключения: 1 (это позволяет выполнять все инструкции SELECT).
SQL_BUFFER_RESULT=0|1
SQL_BUFFER_RESULT вынудит результат SELECT попасть во временную таблицу. Это поможет MySQL освободить блокировку таблицы пораньше и поможет в случаях, где требуется длительное время, чтобы послать набор результатов пользователю.
SQL_LOW_PRIORITY_UPDATES=0|1
Если установлено в 1, все инструкции INSERT, UPDATE, DELETE и LOCK TABLE WRITE ждут до тех пор, пока не останется ни одного ждущего обработки запроса SELECT или LOCK TABLE READ на данной таблице.
SQL_MAX_JOIN_SIZE=value|DEFAULT
Не позволять SELECT, которые, вероятно, будут должны исследовать больше, чем value комбинаций строк. Устанавливая это значение, Вы можете захватывать SELECT, где ключи не используются правильно. Установка этого к иному значению, чем DEFAULT сбросит флажок SQL_BIG_SELECTS. Если Вы снова устанавливаете флажок SQL_BIG_SELECTS, переменная SQL_MAX_JOIN_SIZE будет игнорироваться. Вы можете устанавливать значение по умолчанию для этой переменной, запуская mysqld с опцией -O max_join_size=#.
SQL_SAFE_UPDATES=0|1
Если установлено в 1, MySQL прервется, если UPDATE или DELETE не использует ключ или LIMIT в предложении WHERE. Это делает возможным захватить неправильные модификации при создании SQL-команды вручную.
SQL_SELECT_LIMIT=value|DEFAULT
Максимальное число записей для возвращения из инструкций SELECT. Если SELECT имеет предложение LIMIT, то LIMIT имеет приоритет над значением в SQL_SELECT_LIMIT. Значение по умолчанию для нового подключения: ``unlimited''. Если Вы изменили ограничение, значение по умолчанию может быть восстановлено, используя значение DEFAULT.
SQL_LOG_OFF=0|1
Если установлено в 1, никакая регистрация не будет выполнена в стандартный файл регистрации для этого пользователя, если пользователь имеет привилегию process. Это не воздействует на файл регистрации модификаций!
SQL_LOG_UPDATE=0|1
Если установлено в 0, никакая регистрация не будет выполнена в файл регистрации модификаций для этого пользователя, если пользователь имеет привилегию process. Это не воздействует на стандартный файл регистрации!
SQL_QUOTE_SHOW_CREATE=0|1
Если установлено в 1, SHOW CREATE TABLE цитирует имена столбцов и таблиц. Это включено по умолчанию для репликации таблиц с нестандартными именами столбцов. Подробности в разделе "4.10.8 SHOW CREATE TABLE".
TIMESTAMP=timestamp_value|DEFAULT
Установить время для этого пользователя. Это применяется, чтобы получить оригинал timestamp, если Вы используете файл регистрации модификаций, чтобы восстановить строки. timestamp_value должен быть в формате UNIX Epoch timestamp, а не MySQL timestamp.
LAST_INSERT_ID=#
Установить значение, которое будет возвращено из LAST_INSERT_ID(). Это будет сохранено в файле регистрации модификаций, когда Вы используете LAST_INSERT_ID() в команде, которая модифицирует таблицу.
INSERT_ID=#
Установить значение, которое нужно использовать следующей командой INSERT или ALTER TABLE при вставке значения AUTO_INCREMENT. Это главным образом используется вместе с файлом для регистрации модификаций.

14.6 Дисковые проблемы

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

Использование символических ссылок
Это означает, что Вы переносите индекс и/или файлы данных с нормального каталога данных на другой диск. Это делает время поиска и чтения меньше (если эти диски не используются для других вещей). Подробности в разделе "14.6.1 Использование символических ссылок ".
Striping
Striping означает, что Вы имеете много дисков и помещаете первый блок на первый диск, второй блок на второй диск и N-й на (N mod number_of_disks) диск. Это означает, что если нормальный размер данных меньше, чем размер блока, Вы получите намного лучшую эффективность. Обратите внимание, что striping очень зависит от OS и размера блока. Обратите внимание, что различие быстродействия для striping очень зависит от параметров. В зависимости от того, как Вы устанавливаете параметры и число дисков, Вы можете получать различие в несколько раз. Обратите внимание, что Вы должны выбрать как оптимизировать: для произвольного или последовательного доступа.

Для пущей надежности Вы можете использовать RAID 0+1 (striping+mirroring, данные равномерно размазываются на физически разные диски), но в этом случае Вы будете нуждаться в 2*N дисках для N дисков данных. Это, вероятно, самый лучшый вариант, если Вы имеете деньги для его реализации! Вам, вероятно, также придется поставить некоторое программное обеспечение управления местом на дисках, чтобы работать эффективно.
Хорошая система должна иметь не очень важные данные (которые могут быть восстановлены) на диске RAID 0 при сохранении по-настоящему важных данных (подобно информации хостов и файлам регистрации) на диске RAID 0+1 или RAID N. RAID N может быть проблемой, если Вы имеете много записей из-за времени, нужного, чтобы модифицировать биты четности.
Вы можете также устанавливать параметры для файловой системы, которую база данных использует. Одно простое изменение должно установить файловую систему в режим noatime. Это заставляет систему пропустить модифицирование последнего времени доступа в inode и этим избежать ряда дисковых установок.
В Linux Вы можете получить намного большую эффективность (до 100% при некоторых видах загрузки), используя hdpram, чтобы конфигурировать интерфейс вашего диска! Достаточно хорошие параметры hdparm для MySQL (и, вероятно, для многих других прикладных программ):
hdparm -m 16 -d 1
Обратите внимание, что эффективность и надежность при использовании вышеупомянутого зависят от Ваших аппаратных средств, так что я настоятельно советую, чтобы Вы проверили Вашу систему полностью после использования hdparm. Пожалуйста, проконсультируйтесь с man-страницей на hdparm для получения большего количества информации. Если hdparm не используется грамотно, можно прихлопнуть все данные на диске. Резервируйте все перед экспериментированием.
На многих операционных системах Вы можете монтировать диски с флажком async, чтобы установить файловую систему, которую нужно модифицировать асинхронно. Если Ваш компьютер более-менее стабилен, это должно дать Вам большую эффективность без того, чтобы сильно жертвовать надежностью. Этот флажок включен по умолчанию в Linux.
Если Вы не должны знать, когда к файлу в последний раз обращались (а толку-то это знать на сервере?), Вы можете монтировать Ваши файловые системы с указанием флажка noatime.

14.6.1 Использование символических ссылок

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

14.6.1.1 Использование символических ссылок для баз данных

Надо сначала создать каталог на некотором диске, где Вы имеете свободное пространство, и затем создать ссылку туда из каталога баз данных 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. Это позволяет Вам размещать различные базы данных на разных дисках.

14.6.1.2 Использование символических ссылок для таблиц

До MySQL 4.0 не стоило пользоваться ссылками без их тщательнейшего контроля. Проблема состоит в том, что, если Вы выполняете ALTER TABLE, REPAIR TABLE или OPTIMIZE TABLE на таблице со связью, ссылка будет удалена и заменена первоначальными файлами. Это случается потому, что вышеупомянутая команда работает, создавая временный файл в каталоге баз данных, а когда команда завершится, первоначальный файл она заменит на временный файл.

Вы не должны связывать таблицы на системе, которая не имеет полностью рабочее обращение realpath(). По крайней мере Linux и Solaris точно поддерживают realpath().

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

Обработка символических связей в MySQL 4.0 работает следующим образом:

В каталоге данных Вы будете всегда иметь файл определения таблицы и файлы данных/индекса.
Вы можете перенаправить индексный файл и файл данных к различным каталогам, независимо друг от друга.
Установка ссылки может быть выполнена из операционной системы (если mysqld не запущен) или с помощью команды INDEX/DATA DIRECTORY="path-to-dir" в CREATE TABLE. Подробности в разделе "7.3 Синтаксис CREATE TABLE".
myisamchk теперь уже не будет заменять ссылки на реальные файлы, а работает непосредственно на нужных файлах. Любые временные файлы будут созданы в том же самом каталоге, где лежит файл данных или индекса.
Когда Вы удаляете таблицу, которая использует ссылки, удалится ссылка и файл, на который она указывает. Это хорошая причина не выполнять mysqld как root, и не позволять кому попало иметь доступ для записи к каталогам баз данных MySQL.
Если Вы переименуете таблицу с помощью ALTER TABLE RENAME и не измените базу данных, ссылка в каталоге базы данных будет переименована, как и файл данных/индекса.
Если Вы используете ALTER TABLE RENAME, чтобы переместить таблицу в другую базу данных, то таблица будет перемещаться в другой каталог баз данных, а старая ссылка и файл, на который она указывала, удалятся.
Если Вы не используете ссылки, Вы должны использовать опцию --skip-symlink при запуске mysqld, чтобы гарантировать, что никто не может удалять или переименовывать файл вне каталога данных MySQL.

Свойства, которые еще не поддержаны:

ALTER TABLE игнорирует все опции INDEX/DATA DIRECTORY="path".
CREATE TABLE не сообщает, имеет ли таблица ссылки.
mysqldump не включает информацию ссылок в вывод.
Команды BACKUP TABLE и RESTORE TABLE не обрабатывают ссылки вообще.

Поиск

На сайте
В Яндексе

Найди своих коллег!