The OpenNET Project / Index page

[ новости /+++ | форум | теги | ]

Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа

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

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

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

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

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

Наиболее общие узкие места:

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 строк по индексу Секунд
mysql367
mysql_odbc464
db2_odbc1206
informix_odbc121126
ms-sql_odbc1634
oracle_odbc20800
solid_odbc877
sybase_odbc17614
Вставка 350768 строк Секунд
mysql381
mysql_odbc619
db2_odbc3460
informix_odbc2692
ms-sql_odbc4012
oracle_odbc11291
solid_odbc1801
sybase_odbc4802

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

Результаты теста 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;

Например, предположим, что:

Первоначально, прежде, чем выполнились любые оптимизации, инструкция 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)". Некоторые общие советы:

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

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

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

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

Некоторые примеры запросов, которые являются очень быстрыми:

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

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

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

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

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

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 выполнено следующим образом:

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:

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

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

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

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

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

Чтобы дополнительно получить несколько большее быстродействие для операций 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 Другие советы по оптимизации

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, которые ждут таблицу, более высокий приоритет после специфического числа вставок на таблице.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Предположим, что Вы выдаете следующую инструкцию SELECT:

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

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

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

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

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

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

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

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

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

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

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

В первой инструкции значение LIKE начинается с группового символа. Во второй инструкции значение LIKE не константа.

Поиск, использующий 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 все равно не будет использовать индекс, даже если можно было бы это сделать. Это связано с особенностями логики СУБД. Некоторые из таких случаев:

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.

Другие советы и предупреждения:

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 компилируются с поддержкой всех наборов символов.

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

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

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

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

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 Использование символических ссылок

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

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 работает следующим образом:

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




Партнёры:
PostgresPro
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2024 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру