Оптимизация представляет собой сложную задачу потому, что в конечном счете требуется понимание целой системы. В то время, как можно сделать некоторую локальную оптимизацию с небольшими знаниями относительно Вашей системы или прикладной программы, серьезная оптимизация требует значительных знаний.
Эта глава пробует объяснить и дать некоторые примеры различных способов оптимизировать MySQL. Помните, однако, что всегда имеются некоторые дополнительные способы сделать Вашу систему еще чуть быстрее.
Наиболее важная часть для получения быстрой системы: базисный проект. Вы также должны знать то, какие дела Ваша система будет делать, каковы Ваши узкие места на этом пути.
Наиболее общие узкие места:
Поскольку MySQL использует чрезвычайно быструю блокировку таблицы (много чтений и одна запись), самая большая остающаяся проблема: смесь устойчивого потока вставок и медленного выбора данных в той же самой таблице.
Считается, что для огромного числа систем чрезвычайно быстрая эффективность в других случаях делает этот выбор правильным. Этот случай обычно также решаем при наличии нескольких копий таблицы, но требуется большее количество усилий и аппаратных средств.
Авторы также работают над некоторыми расширениями, чтобы решить эту проблему для некоторых общих ниш прикладных программ.
Потому, что все серверы 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.
Если высокая эффективность более важна, чем точность, как в некоторых прикладных программах для интернета, можно создать уровень прикладной программы, который кэширует все результаты, чтобы дать Вам даже более высокую эффективность. Позволяя старым результатам выдыхаться через некоторое время, Вы можете хранить кэш, приемлемо свежим. Это совершенно хорошо в случае чрезвычайно высокой загрузки, когда Вы можете динамически увеличивать кэш и повышать время ожидания, пока ситуация не вернется к норме.
В этом случае информация для создания новой таблицы должна содержать еще и информацию относительно начального размера кэша и то, как часто таблица обычно должна обновиться.
Вот что пишут авторы пакета о том, как создавалась эта СУБД:
В ходе начальной разработки, свойства 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), мы начнем использовать это для некоторых частей прикладной программы.
Этот раздел должен содержать техническое описание эталонного набора тестов
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.
Вы должны использовать эталонный тест, чтобы выяснять, где находятся узкие места. Исправляя это (или заменяя узкое место фиктивным модулем), Вы можете затем легко идентифицировать следующее узкое место и так далее. Даже если полная эффективность для Вашей прикладной программы достаточна, Вы должны по крайней мере сделать план каждого узкого места и решить, как его расширить, если возникнет такая потребность.
Для примера переносных эталонных программ рассмотрите эталонный набор MySQL. Подробности в разделе "5.1.4 Пакет тестов MySQL Benchmark Suite". Вы можете брать любую программу из этого набора и изменять ее для Ваших потребностей. Делая это, Вы можете опробовать различные решения Вашей проблемы и теста, который в самом деле является самым быстрым решением для Вас.
Некоторые проблемы происходят только тогда, когда система очень тяжело загружена. В каждом из этих случаев это обычно проблема с базисным проектом (просмотр таблицы НЕ хорош при высокой загрузке) или есть трудности с OS/Library. Большинство этих случаев было бы НАМНОГО проще исправить, если система еще не запущена в работу.
Чтобы избежать подобных проблем, Вы должны тестировать прикладную программу при самой плохой возможной загрузке! Вы можете использовать для этого Super Smack, который доступен по адресу http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz.
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) представляет собой хороший
инструмент, чтобы выяснить, связана ли эта проблема с Вашим запросом.
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 снова. Чтобы увидеть, какие
индексы таблица имеет, вызовите SHOW INDEX FROM tbl_name.
key
key указывает ключ, который MySQL фактически решил
использовать. Если индекс не выбран, здесь будет NULL. Если
MySQL выбирает неправильный индекс, Вы можете вынуждать MySQL использовать
другой индекс, используя myisamchk --analyze. Подробности в
разделе "4.4.6.1 Синтаксис обращения
к myisamchk". Можно применить и
USE INDEX/IGNORE INDEX.
key_len
key_len указывает длину ключа, который MySQL решил
использовать. Длина равна NULL, если key равен
NULL. Обратите внимание, что это сообщает нам, сколько частей
составного ключа фактически использует MySQL.
ref
ref показывает, которые столбцы или константы
используются с key, чтобы выбрать строки из таблицы.
rows
rows указывает число строк, которые MySQL должен
исследовать, чтобы выполнить запрос.
Extra
Distinct
Not exists
LEFT JOIN на запросе и
не будет исследовать большее количество строк в этой таблице для предыдущей
комбинации строк после того, как найдет одну строку, которая соответствует
критериям LEFT JOIN. Имеется пример этого:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;Считается, что
t2.id определен как NOT NULL.
В этом случае MySQL просмотрит t1 и будет искать строки в
t2 через t1.id. Если MySQL находит строку
соответствий в t2, он знает, что t2.id никогда не
может быть NULL, и не будет просматривать остальную часть строк
в t2, которые имеют тот же самый идентификатор id.
Другими словами, для каждой строки в t1 MySQL должен делать
только одиночную поисковую таблицу в t2 независимо от того,
сколько соответствий строк находится в t2.
range checked for each record (index map: #)Using filesort
join type) и сохраняя ключ
сортировки+указатель на строку для всех строк, которые соответствуют
WHERE. Затем ключи сортируются. В заключение строки будут
получены в сортируемом порядке.
Using index
Using temporary
ORDER BY на наборе столбцов, отличном от того, который
был задан в предложении GROUP BY.
Where used
WHERE будет использоваться, чтобы ограничить то,
которые строки будут согласованы для следующей таблицы или посланы
пользователю. Если Вы не имеете эту информацию, и таблица имеет тип
ALL или index, Вы можете иметь что-то неправильное
в Вашем запросе (если Вы не предполагаете выбирать/исследовать все строки из
таблицы). Если Вы хотите получать ответы на Ваши запросы с такой скоростью, с
какой только возможно, Вы должны рассмотреть применение Using
filesort и Using temporary.Различные типы объединения перечислены ниже в порядке от лучших к худшим:
system
const.
const
const очень быстры, поскольку они доступны
для чтения только однажды!
eq_ref
const. Это используется, когда все части индекса применяются
объединением, и индекс UNIQUE или PRIMARY KEY.
ref
ref используется, если объединение использует только крайний
левый префикс ключа, или если ключ не UNIQUE или PRIMARY
KEY (другими словами, если объединение не может выбирать одиночную
строку, основанную на значении ключа).
range
key указывает,
который индекс используется. Столбец key_len содержит самую
длинную часть ключа, которая использовалась. Столбец ref
для этого типа будет NULL.
index
ALL, за исключением того, что только индексное
дерево будет просмотрено. Это обычно быстрее, чем ALL, поскольку
индексный файл обычно меньше, чем файл данных.
ALL
const, и обычно
очень плохо во всех других случаях. Вы обычно можете
избежать ALL, добавляя большее количество индексов, так, чтобы
строка могла быть найдена, основываясь на постоянных значениях или значениях
столбца более ранних таблиц.Вы можете получать хорошую индикацию относительно того, насколько хорошим
является объединение, умножая все значения в столбце rows вывода
EXPLAIN. Это должно сообщить Вам грубо, сколько строк MySQL
должен исследовать, чтобы выполнить запрос. Это число также используется,
когда Вы ограничиваете запросы переменной max_join_size.
Подробности в разделе "5.5.2
Настройка параметров сервера".
Следующий пример показывает, как JOIN может быть
оптимизирован, прогрессивно используя информацию, предоставляемую
EXPLAIN.
Предположим, что Вы имеете инструкцию SELECT, показанную
ниже, и что Вы исследуете ее, используя EXPLAIN:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference,
tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID, tt.CurrentProcess,
tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted,
et.COUNTRY, et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID AND tt.AssignedPC=et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
Например, предположим, что:
| Таблица | Столбец | Тип столбца |
tt | ActualPC |
CHAR(10) |
tt | AssignedPC |
CHAR(10) |
tt | ClientID |
CHAR(10) |
et | EMPLOYID |
CHAR(15) |
do | CUSTNMBR |
CHAR(15) |
| Таблица | Индекс |
tt | ActualPC |
tt | AssignedPC |
tt | ClientID |
et | EMPLOYID (первичный ключ) |
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 запроса.
В большинстве случаев Вы можете оценивать эффективность, считая дисковые
установки. Для маленьких таблиц Вы можете обычно находить строку за одну
дисковую установку (поскольку индекс, вероятно, кэшируется). Для больших
таблиц Вы можете оценивать (используя индексы дерева 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=5.2M (считается, что индексные буфера заполнены на 2/3, что является типичным), Вы будете, вероятно, иметь многое из индекса в памяти, и Вы будете, вероятно, нуждаться только в 1-2 обращениях, чтобы читать данные из OS, чтобы найти строку.
Для записи, однако, Вы будете нуждаться в 4 запросах установки (как показано выше), чтобы найти, где поместить новый индекс, и обычно в 2 установках, чтобы модифицировать индекс и записать строку данных.
Обратите внимание, что вышеупомянутое не означает, что Ваша прикладная программа будет требовать сложности N log N! Пока все кэшируется OS или SQL-сервером, работа будет идти только незначительно медленнее в то время, как таблица становится большей. После того, как данные становятся слишком большими, чтобы кэшироваться, работа будет идти намного медленнее, пока Ваши прикладные программы ограничены дисковыми установками (которые растут как N log N). Чтобы избежать этого, увеличьте индексный кэш, поскольку данные растут. Подробности в разделе "5.5.2 Настройка параметров сервера ".
SELECT
Вообще, когда Вы хотите сделать медленный SELECT ... WHERE
быстрее, первое, что подлежит проверке, можете или нет Вы добавить индекс.
Подробности в разделе "5.4.3 Как MySQL
использует индексы". Все ссылки между различными таблицами должны обычно
делаться с индексами. Вы можете использовать команду EXPLAIN,
чтобы определить, которые индексы используются для SELECT.
Подробности в разделе "5.2.1 Синтаксис
EXPLAIN (получение информации о SELECT)".
Некоторые общие советы:
myisamchk
--analyze на таблице после того, как она была загружена релевантными
данными. Это модифицирует значение для каждой индексной части, которая
указывает среднее число строк, которые имеют то же самое значение. Для
уникальных индексов, это всегда 1, конечно. MySQL использует это, чтобы
решить, которые индексы выбрать, когда Вы подключаете две таблицы с
неконстантным выражением. Вы можете проверять результат из
analyze выполнением SHOW INDEX FROM table_name и
исследования столбца Cardinality.
myisamchk --sort-index --sort-records=1 (если Вы хотите
сортировать на индексе 1). Если Вы имеете уникальный индекс, из которого Вы
хотите читать все записи, это хороший способ сделать процесс быстрее.
Обратите внимание, однако, что эта сортировка не написана оптимально и будет
брать довольно длительное время для большой таблицы!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, когда используется только с одной таблицей.
SELECT невозможны, и не
возвращает никаких строк.
HAVING объединено с WHERE, если Вы не
используете GROUP BY или групповые функции
(COUNT(), MIN()...).
WHERE будет создан,
чтобы получить быструю оценку WHERE для каждого подобъединения,
а также пропускать все записи как можно скорее.
WHERE на
индексе UNIQUE или PRIMARY KEY, где все индексные
части используются с постоянными выражениями и индексными частями, определена
как NOT NULL.
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
ORDER BY и в
GROUP BY исходят из той же самой таблицы, то эта таблица
использована сначала при объединении.
ORDER BY и отличное от него
предложение GROUP BY, или если ORDER BY или
GROUP BY содержит столбцы из таблиц, иных, чем первая таблица в
очереди объединений, создается временная таблица.
SQL_SMALL_RESULT, MySQL использует
временную таблицу в памяти.
HAVING, будут пропущены.Некоторые примеры запросов, которые являются очень быстрыми:
mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
LIMIT 10;
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2
DESC,... LIMIT 10;
Следующие запросы решены, используя только индексное дерево (все индексированные столбцы числовые):
mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND
key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;
Следующие запросы применяют индексы, чтобы получить строки в сортируемом порядке без отдельного прохода сортировки:
mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... ;
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2
DESC,... ;
DISTINCTDISTINCT преобразован в GROUP BY на всех
столбцах, DISTINCT объединенный с ORDER BY тоже
будет во многих случаях нуждаться во временной таблице.
При объединении LIMIT # с DISTINCT, MySQL
остановится, как только успешно найдет # уникальных строк.
Если Вы не используете столбцы из всех применяемых таблиц, MySQL остановит просмотр неиспользуемых таблиц, как только найдет первое соответствие.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
В этом случае t1 используется прежде t2 (проверьте с помощью
EXPLAIN), затем MySQL перестанет читать из t2 (для той
специфической строки в t1), когда первая строка в t2 будет найдена.
LEFT JOIN и RIGHT JOIN
Действие A LEFT JOIN B в MySQL выполнено следующим образом:
B установлена, чтобы зависеть от таблицы
A, и всех таблиц, которые зависят от A.
A, чтобы зависеть от всех таблиц за
исключением B, которые используются в
условии LEFT JOIN.
LEFT JOIN перемещаются в WHERE.
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
LIMITВ некоторых случаях MySQL обработает запрос по-другому, когда Вы
используете LIMIT # и не используете HAVING:
LIMIT, MySQL
использует индексы в некоторых случаях, когда он обычно предпочел бы сделать
полный просмотр таблицы.
LIMIT # в сочетании с ORDER
BY, MySQL закончит сортировку, как только найдет первые #
строк вместо того, чтобы сортировать целую таблицу.
LIMIT # с DISTINCT MySQL
остановится, как только найдет # уникальных строк.
GROUP BY может быть решено, читая ключ
по порядку (или сортируя по ключу) с последующим вычислением результата до
изменений значения ключа. В этом случае LIMIT # не будет
вычислять никакую ненужную операцию GROUP BY.
# строк пользователю, он
прервет текущий запрос.
LIMIT 0 будет всегда быстро возвращать пустой набор. Это
полезно, чтобы проверить запрос и получить типы столбцов результата.
LIMIT #, чтобы вычислить,
сколько места необходимо, чтобы решить этот запрос.INSERT
Время, нужное, чтобы вставить запись, состоит приблизительно из:
Здесь числа пропорциональны полному времени. Это не учитывает время, нужное, чтобы открыть таблицы (это выполнено лишь однажды для каждого одновременно выполняемого запроса).
Размер таблицы замедляет вставку индексов на N log N (B-деревья).
Некоторые способы ускорить вставки:
INSERT. Это намного быстрее, чем использование отдельных
инструкций INSERT. Грамотная настройка переменной
myisam_bulk_insert_tree_size может сделать это даже быстрее.
Подробности в разделе "4.5.5.4
Синтаксис SHOW VARIABLES".
INSERT DELAYED.
MyISAM Вы можете вставлять строки
в то же самое время, когда работает SELECT, если не имеется
никаких удаленных строк в таблицах.
LOAD DATA
INFILE. Это примерно в 20 раз быстрее, чем использование
набора команд INSERT.
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, чтобы сделать ее еще меньше. Подробности в разделе
7.1.2.3 Характеристики сжатых таблиц
".
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 и 5. Если Вы используете блокировку, 2, 3 и 4, вероятно, не будут заканчиваться прежде, чем 1 или 5, но общее время должно быть приблизительно на 40% лучше. Так как операции
INSERT, UPDATE и DELETE очень
быстры в MySQL, Вы получите лучшую полную эффективность, добавляя блокировки
вокруг всего, что делает больше, чем приблизительно 5 вставок или модификаций
в строке. Если Вы делаете очень много вставок в строке, Вы могли бы делать
LOCK TABLES, сопровождаемые UNLOCK TABLES время от
времени (примерно каждые 1000 строк), чтобы позволить другим потокам доступ к
таблице. Конечно же, LOAD DATA INFILE все равно намного быстрее
для загрузки данных.Чтобы дополнительно получить несколько большее быстродействие для операций
LOAD DATA INFILE и INSERT, увеличьте буфер ключа.
Подробности в разделе "5.5.2
Настройка параметров сервера".
UPDATE
Запросы модификации оптимизированы как запрос SELECT.
Быстродействие дополнительной записи зависит от размера данных, которые
изменяются, и числа индексов, в которые вносятся поправки. Индексы, которые
не изменены, не будет модифицироваться.
Также, другой способ получать быстрые модификации состоит в том, чтобы задержать модификации, а затем сделать много модификаций в строке позже. Выполнение многих модификаций в строке намного более быстрое, чем выполнение их по одной, если Вы блокируете таблицу.
Обратите внимание, что, при работе с динамическим форматом записи
модификация записи на более длинную может привести к разделению ее на
фрагменты. Так что, если Вы делаете это часто, очень важно иногда вызывать
OPTIMIZE TABLE. Подробности в разделе
"4.5.1 Синтаксис OPTIMIZE TABLE
".
DELETE
Если надо удалить все строки из таблицы, примените вызов
TRUNCATE TABLE table_name.
Время, нужное, чтобы удалить запись, точно пропорционально числу индексов. Чтобы удалять записи быстрее, Вы можете увеличивать размер индексного кэша. Подробности в разделе "5.5.2 Настройка параметров сервера".
thread_cache_size. Подробности в разделе
"5.5.2 Настройка параметров сервера
".
EXPLAIN. Подробности в разделе
"5.2.1 Синтаксис EXPLAIN
(получение информации о SELECT)".
SELECT на таблицах, которые часто
модифицируются. Это должно помочь избежать проблем с блокировкой таблицы.
MyISAM могут вставлять строки в таблицу без
удаленных строк в то же самое время, когда другой запрос ведет чтение. Если
это важно для Вас, Вы должны рассмотреть методы, где Вы не должны удалить
строки или выполните OPTIMIZE TABLE после того, как Вы удалили
много строк сразу.
ALTER TABLE ... ORDER BY expr1,expr2...
если Вы обычно получаете строки в порядке expr1,expr2,... . Используя эту
опцию после внесения больших изменений для таблицы, Вы можете получить
значительно более высокую эффективность.
SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND
col_1='constant' AND col_2='constant'
VARCHAR или BLOB. Вы получите
динамическую длину строки, как только Вы используете хоть один стобец
VARCHAR или BLOB. Подробности в разделе
"7 Типы таблиц MySQL".
UPDATE table set count=count+1 where
index_column=constant очень быстрая! Это действительно важно, когда Вы
используете базы данных подобные MySQL, которые имеют только блокировку
уровня таблицы. Это также даст лучшую эффективность с большинством баз
данных, поскольку администратор блокировки строки в этом случае будет иметь
куда меньше работы.
INSERT /*! DELAYED */, если Вы не должны знать,
когда Ваши данные будут записаны. Это ускоряет дела потому, что много записей
могут быть выполнены за один дисковый обмен.
INSERT /*! LOW_PRIORITY */, когда Вы хотите,
чтобы Ваши вызовы select были более важными.
SELECT /*! HIGH_PRIORITY */, чтобы получить
select, обходящий очередь. То есть select будет выполнен, даже если имеется
кто-то ждущий, чтобы сделать запись в таблицу.
INSERT, чтобы сохранить
много строк одной командой SQL (многие серверы SQL поддерживают это).
LOAD DATA INFILE, чтобы загрузить большие
количества данных. Это быстрее, чем нормальные вставки, а будет еще быстрее,
когда myisamchk интегрирован в mysqld.
AUTO_INCREMENT, чтобы
сделать уникальные значения.
OPTIMIZE TABLE время от времени, чтобы избежать
фрагментации при использовании динамического формата таблицы. Подробности в
разделе "4.5.1
Синтаксис OPTIMIZE TABLE".
HEAP, чтобы получить большее
быстродействие, когда это возможно. Подробности в разделе
"7 Типы таблиц MySQL".
name вместо customer_name в таблице
заказчиков). Чтобы сделать Ваши имена переносными на другие SQL-серверы, Вы
должны озаботиться тем, чтобы они не превышали в длину 18 символов.
MyISAM
непосредственно, Вы могли бы получить увеличение быстродействия в 2-5 раз в
сравнении с использованием интерфейса SQL. Чтобы сделать это, данные должны
быть на том же самом сервере, что и прикладная программа, и обычно к ним
можно обратиться только одним процессом (потому, что внешняя блокировка файла
работает очень медленно). Можно было устранить вышеупомянутые проблемы,
представляя команды MyISAM низкого уровня в сервере MySQL (это
могло бы быть одним простым способом получить большее количество
эффективности, если необходимо).
DELAY_KEY_WRITE=1 будет делать
модифицирование индексов быстрее, поскольку они не регистрируются на диске,
пока файл не закрыт. Обратная сторона в том, что Вы должны выполнить
myisamchk на этих таблицах прежде, чем Вы запускаете
mysqld, чтобы гарантировать, что они правильные, если что-то
уничтожило mysqld в середине запроса. Поскольку информация ключа
может всегда генерироваться из данных, Вы не должны терять что-нибудь,
используя DELAY_KEY_WRITE.Вы можете найти обсуждение различных методов блокировки в приложении. Подробности в разделе "6.4 Методы для блокировки".
Вся блокировка в 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 может также сделать
блокировку таблицы короче.
Вы могли бы также изменять код блокировки в mysys/thr_lock.c, чтобы использовать одиночную очередь. В этом случае блокировки записи и чтения имели бы тот же самый приоритет, что может помочь некоторым программам.
Код блокировки таблиц в 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.
SET SQL_LOW_PRIORITY_UPDATES=1. Подробности в разделе
"5.5.6 Синтаксис SET".
SELECT очень важен с
помощью атрибута HIGH_PRIORITY.
INSERT, объединенной с
SELECT, переключитесь, чтобы использовать новые таблицы типа
MyISAM, поскольку они поддерживают параллельную работу вызовов
SELECT и INSERT.
INSERT и
SELECT, атрибут DELAYED в вызове
INSERT, вероятно, решит Ваши проблемы.
SELECT и DELETE,
опция LIMIT в инструкции DELETE может помочь.MySQL хранит данные строк и индексные данные в отдельных файлах. Многие (почти все) другие базы данных смешивают строки и индексные данные в том же самом файле. Я полагаю, что выбор MySQL лучше для очень широкого диапазона современных систем.
Другой способ сохранять данные строк состоит в том, чтобы хранить информацию для каждого столбца в отдельной области (примеры: SDBM и Focus). Это вызовет падение эффективности для каждого запроса, который обращается больше, чем к одному столбцу. Эта модель далеко не хороша для построения универсальных баз данных.
Более общий случай: индекс и данные сохранены вместе (подобно Oracle/Sybase). В этом случае Вы найдете информацию строк в листе страниц индекса. Хорошо с этим размещением то, что во многих случаях, в зависимости от того, как хорошо индекс кэшируется, экономится медленное дисковое чтение. Плохо с этим размещением то, что:
Одна из наиболее распространенных оптимизаций должна получить Ваши данные и индексы, чтобы брать так мало места на диске, насколько возможно. Это может давать огромные улучшения потому, что диск читается меньше, и оперативная память экономится. Индексация также берет меньшее количество ресурсов, если выполнена на меньших столбцах.
MySQL поддерживает много различных типов таблиц и форматов строк. Выбор правильного формата таблицы может давать Вам большое усиление эффективности. Подробности в разделе "7 Типы таблиц MySQL ".
Вы можете получать лучшую эффективность на таблице и минимизировать "складские площади" на диске, используя методы, перечисленные ниже:
MEDIUMINT часто бывает куда лучше,
чем INT в чистом виде.
NOT NULL, если это возможно. Это
сделает все быстрее, и Вы сэкономите один бит на столбец. Обратите внимание,
что, если Вы действительно нуждаетесь в NULL в Вашей прикладной
программе, Вы должны определенно использовать это. Только не стоит иметь это
свойство заданным на всех столбцах по умолчанию.
VARCHAR,
TEXT или BLOB), используется формат записи
фиксированного размера. Это быстрее, но, к сожалению, может тратить впустую
некоторое место. Подробности в разделе
"7.1.2 Форматы таблиц MyISAM".
Индексы используются, чтобы быстро найти строки со специфическим значением одного столбца. Без индекса MySQL должен начать с первой записи и затем пролистывать целую таблицу, пока не найдет релевантные строки. Если таблица имеет индекс для рассматриваемых столбцов, MySQL может быстро получить позицию, чтобы позиционироваться на середину файла данных без того, чтобы иметь необходимость рассматривать все данные. Если таблица имеет 1000 строк, это по крайней мере в 100 раз быстрее, чем последовательное чтение. Обратите внимание, что если Вы должны обратиться почти ко всем 1000 строкам, быстрее читать файл последовательно потому, что мы сэкономим дисковые установки.
Все индексы в MySQL (PRIMARY, UNIQUE и
INDEX) сохранены в B-деревьях. Строки автоматически сжимаются с
использованием конечных пробелов и префикса.
Индексы используются для:
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 все равно не будет использовать индекс, даже если можно было бы это сделать. Это связано с особенностями логики СУБД. Некоторые из таких случаев:
LIMIT,
чтобы получить только часть строк, MySQL использует индекс в любом случае,
поскольку может намного быстрее найти строки, возвращаемые в результате.Все типы столбцов MySQL могут быть индексированы. Использование индексов
на релевантных столбцах представляет собой самый лучший способ улучшить
эффективность операций SELECT.
Максимальное число ключей и максимальная длина индекса определены в драйвере таблицы. Подробности в разделе "7 Типы таблиц MySQL". Вы можете со всеми драйверами таблицы иметь по крайней мере 16 ключей и общую индексную длину по крайней мере в 256 байт.
Для столбцов типов CHAR и VARCHAR Вы можете
индексировать префикс столбца. Это намного быстрее и требует меньшего
количества дискового пространства, чем индексация целого столбца. Синтаксис,
который надо использовать в инструкции CREATE TABLE, чтобы
индексировать префикс для столбца, выглядит следующим образом:
KEY index_name (col_name(length))
Пример ниже создает индекс первых 10 символов столбца name:
mysql> CREATE TABLE test (name CHAR(200) NOT NULL,
KEY index_name (name(10)));
Для столбцов BLOB и TEXT Вы должны индексировать
именно префикс столбца. Там Вы не можете индексировать весь столбец.
В MySQL Version 3.23.23 или позже Вы можете также создавать специальные
индексы FULLTEXT. Они используются для полнотекстового
поиска. Только тип таблицы MyISAM поддерживает индексы
FULLTEXT. Они могут быть созданы только из столбцов
VARCHAR и TEXT. Индексация всегда выполняется над
всем столбцом, частичная индексация пока не поддержана.
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 отсылаю Вас к разделу "5.4.3 Как 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.5.5.3 SHOW STATUS
".
Если Вы имеете много файлов в каталоге, операции открытия, закрытия и
создания будут медленными. Если Вы выполняете инструкции SELECT
относительно многих различных таблиц, будет иметься некоторое замедление,
когда кэш таблицы заполнится потому, что для каждой таблицы, которая должна
быть открыта, другая закроется. Вы можете уменьшить задержку, делая больше
кэш для таблиц.
Когда Вы выполняете mysqladmin status, Вы будете видеть нечто
вроде этого вывода:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
Это может несколько озадачить, если Вы имеете только 6 таблиц.
MySQL представляет собой многопоточное приложение, так что он может иметь много запросов к той же самой таблице одновременно. Чтобы минимизировать проблему с двумя потоками, имеющими различные состояния на том же самом файле, таблица будет открыта независимо каждым параллельным потоком. Это берет некоторую дополнительную память и один описатель файла для файла данных. Описатель индексного файла разделен между всеми потоками.
Начнем обзор с уровня системы, так как некоторые из этих решений должны быть сделаны очень рано. В других случаях рассмотрение будет не очень глубоким потому, что не сможет сильно повлиять на результаты. Однако, всегда хорошо иметь представление относительно того, сколько можно извлечь пользы, меняя настройки на этом уровне.
Используемая OS очень важна! Чтобы с толком использовать многопроцессорные системы, нужно применять Solaris (потому, что потоки там работают очень хорошо) или Linux (потому, что ядро 2.2 имеет очень приличную поддержку SMP). Также на 32-разрядных машинах Linux имеет ограничение в 2G для размера файла по умолчанию. Но это исправлено в файловых системах XFS/Reiserfs. Если Вы имеете потребность в файлах больше, чем 2G, на Linux-intel 32 bit, Вы должны получить заплату LFS для файловой системы ext2.
Другие советы и предупреждения:
--skip-locking в MySQL, чтобы избежать
внешней блокировки. Обратите внимание, что это не будет воздействовать на
функциональные возможности MySQL, пока Вы выполняете только один сервер.
Только не забудьте завершить сервер (или блокировать релевантные части)
прежде, чем Вы выполните myisamchk. На некоторых системах эта
опция обязательна потому, что внешняя блокировка не работает в любом случае.
Опция --skip-locking по умолчанию включена при компиляции с
MIT-pthreads потому, что flock() не полностью поддержан
MIT-pthreads на всех платформах. Это также значение по умолчанию для Linux,
поскольку блокировка файлов в Linux еще не безопасна. Единственный случай,
когда Вы не можете использовать --skip-locking: если Вы
выполняете много серверов MySQL (не клиентов) на тех же самых данных или
выполняете myisamchk на таблице без предварительной блокировки
сервера и сброса таблиц на диск. Вы можете применять
LOCK TABLES/UNLOCK TABLES, даже если Вы используете
опцию --skip-lockingВы можете получать заданные по умолчанию буферные размеры, используемые
сервером mysqld, этой командой:
shell> mysqld --help
Эта команда производит список всех параметров mysqld и
переменных с перестраиваемой конфигурацией. Вывод включает значения по
умолчанию и выглядит так:
Possible variables for option --set-variable (-O) are: back_log current value: 5 bdb_cache_size current value: 1048540 binlog_cache_size current_value: 32768 connect_timeout current value: 5 delayed_insert_timeout current value: 300 delayed_insert_limit current value: 100 delayed_queue_size current value: 1000 flush_time current value: 0 interactive_timeout current value: 28800 join_buffer_size current value: 131072 key_buffer_size current value: 1048540 lower_case_table_names current value: 0 long_query_time current value: 10 max_allowed_packet current value: 1048576 max_binlog_cache_size current_value: 4294967295 max_connections current value: 100 max_connect_errors current value: 10 max_delayed_threads current value: 20 max_heap_table_size current value: 16777216 max_join_size current value: 4294967295 max_sort_length current value: 1024 max_tmp_tables current value: 32 max_write_lock_count current value: 4294967295 myisam_sort_buffer_size current value: 8388608 net_buffer_length current value: 16384 net_retry_count current value: 10 net_read_timeout current value: 30 net_write_timeout current value: 60 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.5.5.4 SHOW VARIABLES".
Вы можете также видеть некоторую статистику с сервера, выдавая команду
SHOW STATUS. Подробности в разделе
"4.5.5.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 последняя; иначе эффект
любых перечисленных на командной строке после нее параметров не будет
отражен в выводе.
Большинство следующих тестов выполнено на 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.95.2.
-static), результат на
14% медленнее в Linux. Обратите внимание, что Вы все еще можете использовать
динамическую библиотеку MySQL. Это критично только для сервера.
mysqld командой strip
libexec/mysqld, возникающий в результате двоичный код может
быть до 4% быстрее.
localhost, MySQL по умолчанию использует именно сокеты.
--with-debug=full, теряете 20%
скорости для большинства запросов, но некоторые запросы могут еще более
замедлиться (эталонные тесты до 36%). Если Вы используете
--with-debug, потери составят около 15%. Запуская
mysqld, скомпилированный с опцией
--with-debug=full, с --skip-safemalloc конечный
результат должен быть близок к варианту --with-debug.
gcc
2.95.2.
gcc 2.95.2 для ultrasparc с опциями
-mcpu=v8 -Wa,-xarch=v8plusa дает еще 4% эффективности.
--log-bin сделает MySQL
примерно на 1% медленнее.
-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 скомпонован статически,
чтобы получить большие быстродействие и переносимость.
Список ниже указывает некоторые из путей, которыми сервер
mysqld использует память. Там, где это нужно и важно, указаны
имена соответствующих переменных сервера.
key_buffer_size) разделен всеми
потоками. Другие буфера, используемые сервером, распределены как необходимо.
Подробности в разделе "5.5.2
Настройка параметров сервера".
thread_stack),
буфер подключений (переменная net_buffer_length) и буфер
результатов (переменная net_buffer_length). Буфер подключений и
буфер результатов будут динамически расширены до
max_allowed_packet, когда это будет необходимо. Когда запрос
обрабатывается, копия текущей строки запроса также распределена.
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.
Подробности в разделе "5.5.6 Синтаксис
SET". В MySQL Version 3.20 максимальный размер временной
таблицы был record_buffer*16 так, что, если Вы используете эту
версию, Вы должны увеличить значение record_buffer. Вы можете
также запустить mysqld с опцией --big-tables,
чтобы всегда сохранять временные таблицы на диске. Однако, это будет плохо
воздействовать на быстродействие очень многих сложных запросов.
malloc() и free()).
3*n (где n максимальная длина строки, не
считая столбцы типа BLOB). BLOB использует от 5 до
8 байт плюс длина данных BLOB. Драйвер таблиц
ISAM/MyISAM распределяет один буфер строк
дополнительно для внутреннего использования.
BLOB, буфер будет
расширен динамически, чтобы читать большие значения BLOB.
Если Вы просматриваете таблицу, распределяется буфер такого размера, как
самое большое значение BLOB.
mysqladmin flush-tables закрывает все таблицы,
которые не находятся в использовании, и отмечает все таблицы, находящиеся в
использовании, чтобы они были закрыты, когда работающий в настоящее время
поток завершится. Это действительно освободит много памяти.ps и другие программы состояния системы может сообщать, что
mysqld использует много памяти. Это может быть вызвано стеками
потоков на различных адресах памяти. Например, Solaris-версия ps
считает неиспользуемую память между стеками как используемую память! Вы
можете выяснить это, проверяя доступный своп командой swap -s.
Сервер mysqld проверен с коммерческими детекторами утечки
памяти, так что в нем не должно иметься никаких утечек памяти.
Когда новый процесс соединяется с 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.
SETSET [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. Если установлено в 0,
Вы должны использовать 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.5.5.8
SHOW CREATE TABLE".
TIMESTAMP=timestamp_value|DEFAULT
timestamp_value должен быть в формате UNIX
Epoch timestamp, а не MySQL timestamp.
LAST_INSERT_ID=#
LAST_INSERT_ID(). Это будет сохранено в файле регистрации
модификаций, когда Вы используете LAST_INSERT_ID() в команде,
которая модифицирует таблицу.
INSERT_ID=#
INSERT или ALTER TABLE при вставке значения
AUTO_INCREMENT. Это главным образом используется вместе с файлом
для регистрации модификаций.hdparm -m 16 -d 1Обратите внимание, что эффективность и надежность при использовании вышеупомянутого зависят от Ваших аппаратных средств, так что я настоятельно советую, чтобы Вы проверили Вашу систему полностью после использования
hdparm. Пожалуйста, проконсультируйтесь с man-страницей на
hdparm для получения большего количества информации. Если
hdparm не используется грамотно, можно прихлопнуть все данные на
диске. Резервируйте все перед экспериментированием.
Вы можете перемещать таблицы и базы данных из каталога баз данных куда хотите и заменять их на символические связи с новыми расположениями. Вы могли бы сделать это для решения проблем с диском, например, переместить базу данных на файловую систему с большим количеством свободного пространства или увеличить быстродействие Вашей системы, распространяя Ваши таблицы на различные диски.
Надо сначала создать каталог на некотором диске, где Вы имеете свободное пространство, и затем создать ссылку туда из каталога баз данных 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. Это
позволяет Вам размещать различные базы данных на разных дисках. Подробности в
разделе "2.6.2.5 Размещение
данных на разных дисках в Windows".
До 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.
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 не
обрабатывают ссылки вообще.