Таблица | Индекс |
tt | ActualPC |
tt | AssignedPC |
tt | ClientID |
et | EMPLOYID (primary key) |
do | CUSTNMBR (primary key) |
Значения tt.ActualPC распределены не равномерно.
На начальном этапе перед выполнением какой-либо оптимизации оператор EXPLAIN выведет следующую информацию:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 do ALL PRIMARY NULL NULL NULL 2135 et_1 ALL PRIMARY NULL NULL NULL 74 tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 range checked for each record (key map: 35)
Поскольку каждая таблица имеет тип (type) ALL, из приведенного выше вывода видно, что MySQL будет делать полное связывание всех таблиц! Это займет долгое время, поскольку для выполнения такого связывания должно быть рассмотрено произведение числа строк в каждой таблице! Для нашего случая такое произведение - 74 * 2135 * 74 * 3872 = 45268558720 строк. Если таблицы большие, трудно даже представить себе, как долго они будут связываться.
Одна проблема здесь состоит в том, что MySQL не может (пока еще) эффективно применять индексы к столбцам, если они объявлены по-разному. В этом контексте тип VARCHAR и тип CHAR - одинаковы, если они не объявлены с различной длиной. Поскольку столбец tt.ActualPC объявлен как CHAR(10), а et.EMPLOYID - как CHAR(15), имеется несоответствие по длине значений.
Чтобы устранить это несоответствие между длинами столбцов, следует использовать команду ALTER TABLE для удлинения столбца ActualPC от 10 символов до 15 символов:
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
Теперь оба столбца tt.ActualPC и et.EMPLOYID имеют тип VARCHAR(15). При повторном выполнении оператора EXPLAIN будет выведен следующий результат:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 Using where do ALL PRIMARY NULL NULL NULL 2135 range checked for each record (key map: 1) et_1 ALL PRIMARY NULL NULL NULL 74 range checked for each record (key map: 1) et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
Это не идеально, но уже намного лучше (произведение значений строк (rows) теперь уменьшилось в 74 раза). Такое связывание выполнится за пару секунд.
Можно сделать еще одно изменение - чтобы устранить несоответствие длин столбцов для сравнений tt.AssignedPC = et_1.EMPLOYID и tt.ClientID = do.CUSTNMBR.
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), -> MODIFY ClientID VARCHAR(15);
Теперь оператор EXPLAIN будет выводить такую информацию:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using where ClientID, ActualPC et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
Это почти идеально.
Осталась еще одна проблема. Она заключается в том, что по умолчанию MySQL принимает, что значения в столбце tt.ActualPC распределены равномерно, но в таблице tt это не так. К счастью, проинформировать MySQL об этом можно очень просто:
shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh
Теперь связывание совершенно, и оператор EXPLAIN выведет такой результат:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC NULL NULL NULL 3872 Using where ClientID, ActualPC et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1 et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
Обратите внимание: столбец rows в выводе оператора EXPLAIN - опытное предположение оптимизатора связей MySQL. Чтобы оптимизировать запрос, нужно проверить, являются ли числа близкими к действительным. Если нет, можно получить лучшую производительность, используя в операторе SELECT
соединение STRAIGHT_JOIN и попытаться задать другой порядок таблиц в выражении FROM.
Оценка производительности запроса
В большинстве случаев можно оценивать производительность путем подсчета дисковых операций. Для маленьких таблиц можно обычно принимать 1 строку за 1 операцию дискового поиска (поскольку индекс, скорее всего, в кэше). Для больших таблиц можно считать, что (при использовании индексов типа B++ деревьев) для нахождения строки потребуется
log(количество_строк) / log(длина_индексного_блока / 3 * 2 / (длина_индекса + длина_указателя_на_данные)) + 1
дисковая операция для получения строки.
Обычно в MySQL индексный блок занимает 1024 байта, а указательн - 4 байта. Для таблицы, содержащей 500000 строк и имеющей длину индекса 3 (medium integer) потребуется log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 дисковых операции поиска.
Поскольку вышеупомянутый индекс будет занимать приблизительно 500000 * 7 * 3/2 = 5,2Mб (если учитывать, что индексные буфера обычно заполняются на 2/3), большая часть индекса, скорее всего, окажется в памяти, и для того, чтобы найти строку, потребуется лишь 1-2 обращения к ОС для чтения.
Для записи, однако, потребуется 4 дисковых запроса (таких, какие рассматривались выше) чтобы найти место для помещения нового индекса, и обычно 2 дисковых операции, чтобы обновить индекс и вставить строку.
Обратите внимание: сказанное выше не означает, что производительность приложения будет ухудшаться в log N раз! Поскольку все кэшируется в OС или на SQL-сервере, замедление работы при увеличении таблицы будет незначительным. И лишь после того, как данных станет так много, что они перестанут помещаться в кэш, замедление работы там, где работа приложения сводится только к операциям дискового поиска (количество которых растет в log N), станет гораздо ощутимей. Чтобы избежать этого, следует увеличить индексный кэш так, чтобы он вмещал возросшее количество данных. See section 5.5.2 Настройка параметров сервера.
Скорость выполнения запросов SELECT
В общем случае для того, чтобы заставить медленный SELECT ... WHERE
работать быстрее, прежде всего нужно выяснить, можно ли добавить индекс. Для всех ссылок между различными таблицами должны, как правило, применяться индексы. Чтобы определить, какие индексы используются для выборки SELECT, можно использовать EXPLAIN. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT).
Вот несколько общих советов:
Чтобы MySQL лучше оптимизировал запросы, можно выполнить myisamchk
--analyze для таблицы после того, как она загружена соответствующими данными. Таким образом для каждой части индекса будет обновлено значение, указывающее среднее число строк, имеющих одинаковые значения (для уникальных индексов это всегда 1, разумеется). MySQL будет использовать это число, чтобы решить, какой индекс следует выбрать для связывания двух таблиц при помощи "неконстантного выражения". Результат работы analyze можно увидеть в столбце Cardinality после выполнения команды SHOW INDEX FROM имя_таблицы.
Чтобы отсортировать индекс и данные в соответствии с индексом,
используйте myisamchk --sort-index --sort-records=1 (если нужно отсортировать по индексу 1). Если имеется уникальный индекс, по которому вы хотите считывать все записи в порядке, соответствующем данному индексу, это - хороший способ ускорить считывание записей. Обратите внимание, однако, что эта сортировка написана не оптимально и для большой таблицы будет выполняться долго!
Как MySQL оптимизирует выражения WHERE
Описание оптимизации выражений WHERE помещено в раздел, посвященный SELECT, потому что они главным образом используются в запросах SELECT, но для выражений WHERE в операторах DELETE и UPDATE используются те же способы оптимизации.
Отметим также, что данный раздел неполон. В MySQL реализовано много возможностей оптимизации, и у нас не было времени, чтобы задокументировать их все.
Ниже перечислены некоторые из оптимизации, выполняемых MySQL:
Удаляются ненужные скобки:
((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d)
Константы заменяются значениями:
(a b>5 AND b=c AND a=5
Удаляются условия для констант (требуется при замене констант
значением):
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
Константные выражения, используемые индексами, оцениваются только один
раз.
Для таблиц HEAP и MyISAM функция COUNT(*), которая вызывается для
одной таблицы и не содержит предложения WHERE, берется непосредственно из табличной информации. Это делается также для любого выражения NOT
NULL, в котором используется только одна таблица.
Недопустимые константные выражения выявляются на ранних этапах. MySQL
быстро обнаруживает, что некоторые операторы SELECT неосуществимы и не возвращают строк.
Выполняется слияние выражения HAVING с WHERE, если не используется
предложение GROUP BY или групповые функции (COUNT(), MIN()...).
Для каждого подчиненного связывания создается более простое
предложение WHERE, чтобы ускорить оценку WHERE для каждого подчиненного связывания а также чтобы пропустить записи как можно быстрее.
Все константные таблицы считываются в первую очередь, перед любыми
другими таблицами в запросе. К константным таблицам относятся следующие:
Пустая таблица или таблица с 1 строкой.
Таблица, которая используется с выражением WHERE для индекса UNIQUE,
или PRIMARY KEY, где все части индекса используются с константными выражениями и части индекса определены как NOT NULL.
Все эти таблицы используются как константные таблицы:
mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2 -> WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
Лучшая комбинацию связывания для связывания таблиц находится путем
испытания всех возможных вариантов. Если все столбцы в предложениях ORDER BY и GROUP BY принадлежат одной таблице, эта таблица рассматривается первой при связывании.
Если имеется выражение ORDER BY и отличное от него выражение GROUP BY,
или если выражения ORDER BY или GROUP BY содержат столбцы не только из первой таблицы в очереди на связывание, но и из других таблиц, то тогда создается временная таблица.
Если используется SQL_SMALL_RESULT, MySQL будет применять временную
таблицу, которую разместит в памяти.
Запрашивается каждый индекс таблицы, и используется лучший,
охватывающий менее 30% строк. Если такой индекс найти нельзя, используется быстрое сканирование таблицы.
В некоторых случаях MySQL может читать данные из индекса даже без
обращения к файлу данных. Если все столбцы, используемые в индексе, числовые, то для выполнения запроса будет использоваться только индексное дерево.
Перед выводом каждой записи пропускаются те, которые не соответствуют
выражению HAVING.
Вот некоторые примеры очень быстрых запросов:
mysql> SELECT COUNT(*) FROM tbl_name; mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name; mysql> SELECT MAX(key_part2) FROM tbl_name -> WHERE key_part_1=constant; mysql> SELECT ... FROM tbl_name -> ORDER BY key_part1,key_part2,... LIMIT 10; mysql> SELECT ... FROM tbl_name -> ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;
Для выполнения следующих запросов используется только индексное дерево (предполагается, что индексированные столбцы числовые):
mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; mysql> SELECT COUNT(*) FROM tbl_name -> WHERE key_part1=val1 AND key_part2=val2; mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;
Следующие запросы используют индексацию, чтобы получить отсортированные строки без дополнительного прохода для сортировки:
mysql> SELECT ... FROM tbl_name -> ORDER BY key_part1,key_part2,... ; mysql> SELECT ... FROM tbl_name -> ORDER BY key_part1 DESC,key_part2 DESC,... ;
Как 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.
Как MySQL оптимизирует LEFT JOIN и RIGHT JOIN
Выражение "A LEFT JOIN B" в MySQL реализовано следующим образом:
Таблица B устанавливается как зависимая от таблицы A и от всех таблиц,
от которых зависит A.
Таблица A устанавливается как зависимая ото всех таблиц (кроме B),
которые используются в условии LEFT JOIN.
Все условия LEFT JOIN перемещаются в предложение WHERE.
Выполняются все стандартные способы оптимизации соединения, за
исключением того, что таблица всегда читается после всех таблиц, от которых она зависит. Если имеется циклическая зависимость, MySQL выдаст ошибку.
Выполняются все стандартные способы оптимизации WHERE.
Если в таблице A имеется строка, соответствующая выражению WHERE, но в
таблице B ни одна строка не удовлетворяет условию LEFT JOIN, генерируется дополнительная строка B, в которой все значения столбцов устанавливаются в NULL.
Если LEFT JOIN используется для поиска тех строк, которые отсутствуют
в некоторой таблице, и в предложении WHERE выполняется следующая проверка: column_name IS NULL, где column_name - столбец, который объявлен как NOT NULL, MySQL пререстанет искать строки (для отдельной комбинации ключа) после того, как найдет строку, соответствующую условию LEFT JOIN.
RIGHT JOIN реализован аналогично LEFT JOIN.
При указании жесткого порядка чтения таблиц в LEFT JOIN и STRAIGHT JOIN
оптимизатор связей (который определяет, в каком порядке таблицы должны быть связаны) будет выполнять работу намного быстрее, так как ему потребуется проверять меньшее количество перестановок таблиц.
Обратите внимание: отсюда следует, что если выполняется запрос типа
SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key
MySQL будет делать полный просмотр таблицы b, поскольку LEFT JOIN заставит его читать эту таблицу перед d.
В этом случае, чтобы предотвратить полный просмотр таблицы b, нужно изменить запрос таким образом:
SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key
Как MySQL оптимизирует ORDER BY
В некоторых случаях MySQL может использовать индекс, чтобы выполнить запрос ORDER BY или GROUP BY без выполнения дополнительной сортировки.
Индекс может также использоваться и тогда, когда предложение ORDER BY не соответствует индексу в точности, если все неиспользуемые части индекса и все столбцы, не указанные в ORDER BY - константы в выражении WHERE. Следующие запросы будут использовать индекс, чтобы выполнить ORDER BY / GROUP BY.
SELECT * FROM t1 ORDER BY key_part1,key_part2,... SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2 SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC
Ниже приведены некоторые случаи, когда MySQL не может использовать индексы, чтобы выполнить ORDER BY (обратите внимание, что MySQL тем не менее будет использовать индексы, чтобы найти строки, соответствующие выражению WHERE):
Сортировка ORDER BY делается по нескольким ключам: SELECT * FROM t1 ORDER BY key1,key2
Сортировка ORDER BY делается, при использовании непоследовательных частей ключа: SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2
Смешиваются ASC и DESC. SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC
Для выборки строк и для сортировки ORDER BY используются разные ключи: SELECT * FROM t1 WHERE key2=constant ORDER BY key1
Связываются несколько таблиц, и столбцы, по которым делается сортировка ORDER BY, относятся не только к первой неконстантной (const) таблице, используемой для выборки строк (это первая таблица в выводе EXPLAIN, в которой не используется константный, const, метод выборки строк).
Имеются различные выражения ORDER BY и GROUP BY.
Используемый индекс таблицы имеет такой тип, который не обеспечивает сортированного хранения строк (как индекс HASH в таблицах HEAP).
В тех случаях, когда MySQL должен сортировать результат, он использует следующий алгоритм:
Считываются все строки согласно ключу или путем сканирования таблицы. Строки, которые не соответствует предложению WHERE, пропускаются.
Ключ сортировки сохраняется в буфере сортировки (размера sort_buffer)
Когда буфер заполняется, содержимое буфера сортируется алгоритмом qsort, результаты сохраняются во временном файле. Сохраняется указатель на отсортированный блок (в том случае, когда все строки умещаются в буфере сортировки, временный файл не создается).
Вышеупомянутое действие повторяется, пока не будут считаны все строки.
Делается мультислияние до MERGEBUFF (7) областей в один блок в другом временном файле. Это действие повторяется, пока все блоки из первого файла не окажутся во втором файле.
Предыдущий пункт повторяется, пока не останется менее MERGEBUFF2 (15) блоков.
При последнем мультислиянии в результирующий файл записывается только указатель на строку (последняя часть ключа сортировки).
Теперь код в файле `sql/records.cc' будет использоваться для чтения данных в отсортированном порядке, с использованием указателей на строки из результирующего файла. Чтобы оптимизировать этот процесс, мы считываем большой блок указателей на строки, сортируем их, и затем считываем строки в отсортированном порядке в буфер строк (record_rnd_buffer).
При помощи команды EXPLAIN SELECT ... ORDER BY можно проверить, может ли MySQL использовать индексы для выполнения запроса. Если в столбце extra
содержится значение Using filesort, то MySQL не может использовать индексы для выполнения сортировки ORDER BY. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT).
Чтобы сортировка ORDER BY выполнялась с большей скоростью, нужно сначала посмотреть, можно ли заставить MySQL использовать индексы взамен дополнительной фазы сортировки. Если это невозможно, то можно сделать следующее:
Увеличить значение переменной sort_buffer.
Увеличить значение переменной record_rnd_buffer.
Изменить переменную tmpdir, чтобы она указывала на выделенный диск с большим количеством свободного пространства. Начиная с MySQL 4.1, в tmpdir могут быть указаны несколько путей, разделенных двоеточием : (точкой с запятой на Windows ;). Эти пути будут использованы в ротации.
Внимание: Эти пути должны находится на разных физических
дисках, не на разных разделах одного и того же диска.
MySQL по умолчанию сортирует все GROUP BY x,y[,...] запросы так, как если бы вы указали ORDER BY x,y[,...]. MySQL будет оптимизировать любой ORDER BY как сказано выше, без всяких потерь производительности. Если, как в некоторых случаях, вы не хотите иметь результат отсортированным, вы можете указать ORDER BY NULL:
INSERT INTO foo SELECT a,COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
Как MySQL оптимизирует LIMIT
В некоторых случаях, когда используется LIMIT # и не используется HAVING, MySQL будет выполнять запрос несколько иначе:
Если при помощи LIMIT выбираются только несколько строк, MySQL будет использовать индексы в тех некоторых случаях, когда он обычно предпочел бы делать полное сканирование таблицы.
Если LIMIT # используется с ORDER BY, MySQL закончит сортировку, как только найдет первые # строк, вместо того, чтобы сортировать всю таблицу.
При сочетании LIMIT # с DISTINCT MySQL остановится, как только найдет # уникальных строк.
В некоторых случаях группировка GROUP BY может быть выполнена путем упорядоченного считывания ключа (или путем выполнения сортировки по ключу) и последующего вычисления итогового результата пока не изменится значение ключа. В этом случае LIMIT # не будет вычислять какие-либо ненужные предложения GROUP BY.
После того как MySQL пошлет первые # строк клиенту, он прервет выполнение запроса (если не используется SQL_CALC_FOUND_ROWS).
LIMIT 0 всегда будет быстро возвращать пустую выборку. Эта команда полезна для проверки запроса и получения типов столбцов результата.
Если сервер для выполнения запроса использует временные таблицы, LIMIT # применяется для вычисления того, сколько для них потребуется места.
Скорость выполнения запросов INSERT
Время, необходимое для вставки записи, можно грубо разделить на такие промежутки:
Подсоединение: (3)
Посылка запроса на сервер: (2)
Синтаксический анализ запроса: (2)
Вставка записи: (1 * размер записи)
Вставка индексов: (1 * число индексов)
Закрытие: (1)
где числа в скобках пропорциональны полному времени. При этом не учитывается время в начале вставки, требующееся для открытия таблиц (таблицы открываются один раз для каждого конкурентно выполняющегося запроса).
Размер таблицы замедляет вставку индексов в log N раз (B-деревья).
Некоторые способы ускорения вставки:
Если с одного клиента одновременно вставляется большое количество строк, используйте операторы INSERT в форме, содержащей множество записей. При этом вставка будет происходить намного быстрее (в некоторых случаях в несколько раз), чем при использовании отдельных операторов INSERT. При добавлении данных в непустую таблицу можно настроить переменную bulk_insert_buffer_size так, чтобы это делалось еще быстрее. See section 4.5.6.4 SHOW VARIABLES.
При вставке нескольких строк с различных клиентов можно повысить скорость, используя оператор INSERT DELAYED. See section 6.4.3 Синтаксис оператора INSERT.
Обратите внимание: при использовании таблиц MyISAM можно вставлять строки во время выполнения операторов SELECT, если в таблицах нет удаленных строк.
При загрузке таблицы из текстового файла используйте команду LOAD DATA INFILE. При этом обычно вставка будет происходить в 20 раз быстрее, чем при использовании соответствующего количества операторов INSERT. See section 6.4.9 Синтаксис оператора LOAD DATA INFILE.
Если таблица имеет много индексов, можно проделать некоторую дополнительную работу, чтобы команда LOAD DATA INFILE выполнялась еще быстрее. Используйте следующую процедуру:
При необходимости создайте таблицу при помощи оператора CREATE TABLE
(например, используя mysql или Perl-DBI).
Выполните оператор FLUSH TABLES или команду оболочки: mysqladmin flush-tables.
Используйте myisamchk --keys-used=0 -rq /path/to/db/tbl_name. После этого индексы не будут использоваться для данной таблицы.
Вставьте данные в таблицу при помощи LOAD DATA INFILE. При этом никакие индексы обновляться не будут и, следовательно, скорость будет высокой весьма.
Если вы собираетесь в будущем только лишь читать таблицу, выполните myisampack для этой таблицы, чтобы уменьшить ее размер. See section 4.7.4 myisampack, MySQL-генератор сжатых таблиц (только для чтения).
Воссоздайте индексы при помощи команды myisamchk -r -q /path/to/db/tbl_name. Эта процедура создает индексное дерево в памяти, перед тем как записать его на диск, что гораздо быстрее за счет исключения большого количества дисковых операций. Индексное дерево, получившееся в результате, к тому же отлично сбалансировано.
Выполните оператор FLUSH TABLES или команду оболочки: mysqladmin flush-tables.
Обратите внимание: команда LOAD DATA INFILE также выполняет вышеупомянутую оптимизацию при вставках в пустую таблицу. Главное отличие этой команды от вышеупомянутой процедуры заключается в том, что при помощи myisamchk можно выделить намного больше временной памяти для создания индекса, чем MySQL, по вашему мнению, должен выделять для каждого воссоздания индексов. Начиная с MySQL 4.0 можно также использовать команду ALTER TABLE tbl_name DISABLE KEYS вместо myisamchk --keys-used=0 -rq /path/to/db/tbl_name и ALTER TABLE tbl_name ENABLE KEYS вместо myisamchk -r -q /path/to/db/tbl_name. Таким образом можно также пропускать шаги FLUSH TABLES.
Можно ускорять операции вставки, выполняемые несколькими операторами, путем установки блокировки таблиц:
mysql> LOCK TABLES a WRITE; mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33); mysql> INSERT INTO a VALUES (8,26),(6,29); mysql> UNLOCK TABLES;
Главный фактор, влияющий на скорость, - то, что буфер индексов сбрасывается на диск только один раз, после завершения всех операторов INSERT. Обычно содержимое индексных буферов сбрасывалось бы на диск столько раз, сколько имеется различных операторов INSERT. Блокировка не нужна, если можно вставить все строки при помощи одного оператора. Для транзакционных таблиц, чтобы повысить скорость, следует использовать BEGIN/COMMIT вместо LOCK TABLES. Блокировка также понизит полное время проверки подсоединений (multi-connection tests), но максимальное время ожидания для некоторых потоков повысится (потому что они ожидают снятия блокировки). Например:
поток 1 делает 1000 вставок потоки 2, 3 и 4 делают 1 вставку поток 5 делает 1000 вставок
Если блокировка не используется, 2, 3, и 4 завершат выполнение раньше, чем 1 и 5. Если блокировка используется, 2, 3 и 4, видимо, не закончат выполнение раньше, чем 1 или 5, но общее время должно приблизительно уменьшиться на 40%. Так как в MySQL операции INSERT, UPDATE и DELETE очень быстрые, общая производительность будет улучшаться, если добавлять блокировки ко всем командам, делающим более 5 вставок или обновлений подряд. Если делается очень много вставок строк, можно время от времени сопровождать команду LOCK TABLES командой UNLOCK TABLES (после каждых 1000 строк), чтобы позволить другим потокам обращаться к таблице. Результатом всего этого будет получение хорошей производительности. Конечно, для загрузки данных намного более быстрой является команда LOAD DATA INFILE.
Чтобы дополнительно повысить скорость выполнения команд LOAD DATA INFILE и INSERT, увеличьте буфер ключа (key buffer). See section 5.5.2 Настройка параметров сервера.
Скорость выполнения запросов UPDATE
Запросы UPDATE оптимизируются как запрос SELECT с дополнительными издержками на запись. Скорость записи зависит от размера обновляемых данных и количества обновляемых индексов. Индексы, которые не были изменены, обновлены не будут.
Существует и другой способ произвести операции обновления быстро: задерживать операции обновления, а потом делаеть сразу несколько обновлений. Несколько обновлений подряд выполняются намного быстрее, чем отдельные обновления если вы блокируете таблицу.
Обратите внимание: при использовании динамического формата записи, если запись обновляется более длинной, может произойти "расслоение" записи. Таким образом, если вы делаете это часто, очень важно время от времени выполнять команду OPTIMIZE TABLE. See section 4.5.1 Синтаксис команды OPTIMIZE TABLE.
Скорость выполнения запросов DELETE
Чтобы удалить все строки в таблице, нужно использовать команду TRUNCATE TABLE table_name. See section 6.4.7 Синтаксис оператора TRUNCATE.
Время, необходимое для удаления записи, прямо пропорционально числу индексов. Чтобы записи удалялись быстрее, можно увеличить размер индексного кэша. See section 5.5.2 Настройка параметров сервера.
Другие советы по оптимизации
Несортированные советы для повышения скорости систем:
Используйте постоянные соединения с базой данных, чтобы избежать издержек на подключения. Если невозможно использовать постоянные соединения и осуществляется большое количество новых подключений к базе данных, то можно изменить значение переменной thread_cache_size. See section 5.5.2 Настройка параметров сервера.
Всегда проверяйте, чтобы все ваши запросы действительно использовали созданные вами в таблицах индексы. В MySQL это можно сделать с помощью команды EXPLAIN. See section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT).
Старайтесь избегать сложных запросов SELECT на часто обновляемых таблицах типа MyISAM. Это помогает избежать проблем с блокировкой таблиц.
Новые таблицы MyISAM могут вносить записи в таблицу без удаляемых записей, которые в то же самое время читает иная таблица. Если это имеет для вас значение, то следует рассмотреть методы, не требующие удаления записей или запускать OPTIMIZE TABLE после удаления большого количества строк.
Используйте ALTER TABLE ... ORDER BY expr1,expr2... если вы большей частью извлекаете записи в порядке expr1,expr2... Можно получить более высокую производительность, используя эту опцию после больших преобразований в таблице.
В некоторых случаях может оказаться целесообразным введение столбца, базирующегося на "хэшированной" информации из других столбцов. Если этот столбец невелик и достаточно уникален, то он может быть намного быстрее, чем большой индекс на многих столбцах. В MySQL очень просто использовать подобный дополнительный столбец:
SELECT * FROM table_name WHERE hash=MD5(CONCAT(col1,col2)) AND col_1='constant' AND col_2='constant'
Для часто изменяющихся таблиц следует избегать типов VARCHAR или BLOB
для всех столбцов. При использовании единичного столбца VARCHAR или BLOB вы получите динамическую длину строки. See section 7 Типы таблиц MySQL.
Разделение таблицы на несколько различных таблиц просто потому, что строки получаются "большими", обычно не приносит пользы. Чтобы получить доступ к строке, наиболее трудоемким оказывается поиск по диску для нахождения первого байта этой строки. После нахождения этих данных большинство новых дисков могут прочесть всю строку достаточно быстро для большинства приложений. Разделение таблицы имеет значение только в следующих случаях: когда это таблица с динамическим размером строки (смотрите выше), которую можно изменить на строку фиксированного размера, или когда необходимо просматривать таблицу очень часто и нет необходимости в большинстве столбцов. See section 7 Типы таблиц MySQL.
Если очень часто приходится производить вычисления, базирующиеся на информации из большого количества строк (такие как подсчет предметов), то, вероятно, намного лучше ввести новую таблицу и обновлять счетчик в режиме реального времени. Обновление вида UPDATE table SET count=count+1 WHERE index_column=constant является очень быстрым!
Это действительно важно при использовании типов таблиц MySQL, вроде MyISAM или ISAM, имеющих только блокирование таблиц (многочисленные читающие/единственный записывающий). Для многих баз данных это обеспечит также более высокую производительность, поскольку программа управления блокировкой строк в этом случае будет иметь меньше работы.
Если необходимо собирать статистические данные из больших журнальных таблиц, то используйте сводные таблицы вместо сканирования целой таблицы. Поддерживать сводные таблицы должно быть намного быстрее, чем пытаться сделать ``живую'' статистику. Намного быстрее воспроизвести новые сводные таблицы из журналов, когда что-либо изменяется (в зависимости от деловых решений), чем изменять работающее приложение!
Если возможно, необходимо классифицировать отчеты как "реальные" или "статистические", где данные, необходимые для статистических отчетов, генерируются только на основе сводных таблиц, которые формируются из реальных данных.
Воспользуйтесь преимуществом того факта, что столбцы имеют значения по умолчанию. Вносите величины явно только тогда, когда значения вносимых величин отличаются от установленных по умолчанию. Это уменьшает объем анализа, который необходимо произвести в MySQL, и улучшает скорость внесения.
В некоторых случаях удобно упаковывать и хранить данные в столбцах BLOB. В этом случае необходимо добавить дополнительный код для запаковывания в BLOB и распаковывания обратно, но на некотором этапе это может сэкономить много обращений. Это практично, когда ваши данные не согласуются со структурой статической таблицы.
Обычно следует стремиться сохранять все данные в безизбыточной форме (которая называется 3-й нормальной формой в теории баз данных), но не следует опасаться дублирования данных или создания сводных таблиц, если это необходимо для достижения большей скорости.
Хранимые процедуры или UDF (функции, определяемые пользователем) могут быть хорошим способом получить большую производительность. В этом случае, однако, следует иметь в запасе некоторый иной (более медленный) путь, если используемая вами база данных не поддерживает этих возможностей.
Вы всегда можете кое-чего достичь путем кэширования запросов/ответов в своем приложении и стараясь выполнить много вставок/обновлений в одно и то же время. Если ваша база данных поддерживает блокировку таблиц (как MySQL и Oracle), то это должно помочь гарантировать, что кэш индексов сбрасывается только однажды после всех обновлений.
Используйте INSERT /*! DELAYED */, если нет необходимости знать, когда ваши данные записываются. Это повысит скорость работы, поскольку многие табличные записи могут быть внесены с помощью одной дисковой записи.
Используйте INSERT /*! LOW_PRIORITY */, если хотите сделать ваши выборки более важными.
Используйте SELECT /*! HIGH_PRIORITY */, чтобы получить выборки, которые перепрыгивают очередь. То есть, выборка выполняется, даже если кто-либо ожидает, чтобы сделать запись.
Используйте многострочную команду INSERT для хранения многих строк в одной SQL-команде (многие SQL-серверы поддерживают это).
Используйте LOAD DATA INFILE для загрузки больших количеств данных. Это быстрее, чем обычные вставки и будет еще быстрее при интеграции myisamchk в mysqld.
Используйте столбцы AUTO_INCREMENT, чтобы сделать величины уникальными.
Используйте время от времени OPTIMIZE TABLE, чтобы избежать фрагментации при использовании динамического табличного формата. See section 4.5.1 Синтаксис команды OPTIMIZE TABLE.
Используйте таблицы HEAP, чтобы получить более высокую скорость, когда это возможно. See section 7 Типы таблиц MySQL.
При использовании нормальной установки веб-сервера рисунки должны храниться как файлы. То есть, храните в базе данных только ссылку на файл. Главная причина этого состоит в том, что обычный веб-сервер намного лучше кэширует файлы, чем содержание базы данных. Таким образом, при использовании файлов намного легче получить быструю систему.
Используйте в памяти таблицы для неответственных данных, к которым часто обращаются (таким, как информация о последнем показанном баннере для пользователей, не имеющих cookies).
Столбцы с идентичной информацией в различных таблицах должны объявляться одинаково и иметь одинаковые имена. До версии 3.23 в противном случае получались медленные соединения (slow joins). Старайтесь сохранять имена простыми (используйте name вместо customer_name в таблице customer). Чтобы ваши имена были переносимыми на другие SQL-серверы, они должны быть короче, чем 18 символов.
Если вам действительно нужна высокая скорость, вы должны взглянуть на интерфейсы нижнего уровня для хранения данных, поддерживаемые различными SQL-серверами! Например, обращаясь к таблицам MyISAM в MySQL напрямую, можно было бы получить увеличение скорости в 2-5 раз по сравнению с использованием интерфейса SQL. Для возможности сделать это, данные должны находиться на том же самом сервере, что и приложение, и, обычно, должны иметь доступ только в одном процессе обработки (поскольку внешняя файловая блокировка действительно медленна). Можно было бы избавиться от вышеуказанных проблем введением низко-уровневых команд MyISAM в сервере MySQL (это был бы один из простых путей получить большую производительность, если необходимо). Путем тщательного проектирования интерфейса базы данных было бы достаточно просто поддерживать этот тип оптимизации.
Во многих случаях быстрее получить доступ к данным из базы данных (используя действующее соединение), чем обращаться к текстовому файлу, просто из-за того, что база данных, вероятно, более компактна, чем текстовый файл (если вы используете числовые данные), и это приведет к меньшему количеству обращений к диску. Вы также сэкономите на коде, поскольку не должны анализировать текстовые файлы, чтобы найти границы строк и столбцов.
Для увеличения скорости можно также использовать репликацию. See section 4.10 Репликация в MySQL.
Объявление таблицы с DELAY_KEY_WRITE=1 сделает обновление индексов более быстрым, так как они не записываются на диск, пока файл закрыт. Обратная сторона этого заключается в том, что необходимо запускать myisamchk на этих таблицах перед началом работы mysqld, для уверенности, что все в порядке, если что-либо уничтожит mysqld в середине работы. Поскольку ключевая информация всегда может быть воспроизведена из данных, то вы не должны что-либо потерять при использовании DELAY_KEY_WRITE.
Оптимизация SELECT и других запросов
Сначала приведем одно правило, касающееся всех запросов: Чем сложнее ваша система привилений, тем больше издержек.
Если не было выполнено никаких операторов GRANT, MySQL каким-то образом будет оптимизировать проверку полномочий. Таким образом при наличии очень большого объема данных лучше, наверное, будет работать без привилегий. В противном случае при большом количестве полномочий проверка результатов будет происходить с увеличенными издержками.
Если проблема состоит в некоторой явной функции MySQL, всегда можно протестировать ее в клиенте:
mysql> SELECT BENCHMARK(1000000,1+1); +------------------------+ | BENCHMARK(1000000,1+1) | +------------------------+ | 0 | +------------------------+ 1 row in set (0.32 sec)
Из приведенного выше примера видно, что MySQL может выполнять более 1000000 операций сложения за 0,32 секунды на PentiumII 400MHz.
Все функции MySQL достаточно хорошо оптимизированы, но могут попадаться некоторые исключения, и функция BENCHMARK(число_циклов,выражение) - хороший инструмент для выяснения, присутствует ли проблема в запросе.
Как MySQL блокирует таблицы
Описание различных методов блокировки дается в приложении, в разделе section E.4 Методы блокировки.
В MySQL все блокировки, кроме блокировок таблиц типов InnoDB и BDB, не создают тупиковых ситуаций. Это обеспечивается за счет того, что все необходимые блокировки всегда запрашиваются единожды в начале запроса и блокировка таблиц всегда происходит в одном и том же порядке.
В таблицах типа InnoDB устанавливается блокировка строк, а в таблицах типа BDB - блокировка страниц в процессе выполнения SQL-команд, а не в начале транзакции.
Метод блокирования, используемый в MySQL для блокировок записи (WRITE), работает следующим образом:
Если на данной таблице нет никаких блокировок, то на нее накладывается блокировка записи.
В противном случае запрос на данную блокировку ставится в очередь блокировок записи.
Метод блокирования, используемый в MySQL для блокировок чтения (READ), работает следующим образом:
Если на данной таблице нет блокировок записи, то на нее накладывается блокировка чтения.
В противном случае запрос на данную блокировку ставится в очередь блокировок чтения.
При освобождении текущей блокировки становится возможной следующая блокировка для потоков из очереди блокировок записи, затем для потоков из очереди блокировок чтения.
Это означает, что, если таблица подвергается многочисленным обновлениям, то команды SELECT будут ожидать, пока обновления не закончатся.
Чтобы обойти это в случае, когда для таблицы требуется выполнить много операций INSERT и SELECT, можно внести строки во временную таблицу и время от времени обновлять реальную таблицу записями из временной.
Для этого можно применить следующий код:
mysql> LOCK TABLES real_table WRITE, insert_table WRITE; mysql> INSERT INTO real_table SELECT * FROM insert_table; mysql> TRUNCATE TABLE insert_table; mysql> UNLOCK TABLES;
В некоторых случаях, когда необходимо установить приоритеты извлечения данных, для команд INSERT, UPDATE или DELETE можно указывать опцию LOW_PRIORITY, а для команды SELECT - HIGH_PRIORITY. Тот же результат можно получить, запустив mysqld с --low-priority-updates.
Использование SQL_BUFFER_RESULT тоже способствует уменьшению времени блокировок таблиц. See section 6.4.1 Синтаксис оператора SELECT.
Можно также изменить блокировочный код в `mysys/thr_lock.c' - чтобы использовать только одну очередь блокировок. В этом случае блокировки записи и чтения будут иметь одинаковый приоритет, что может оказаться полезным для некоторых приложений.
Вопросы блокирования таблиц
Код блокирования таблиц в MySQL не создает тупиковых ситуаций.
Чтобы получить высокую скорость блокирования, в MySQL на таблицах всех типов, кроме InnoDB и BDB,используется табличная блокировка (вместо блокирования строк или столбцов). Если таблицы большие, то для большинства приложений табличная блокировка намного лучше, чем строковая, но существуют, конечно, и определенные подводные камни.
Для таблиц типов InnoDB и BDB в MySQL табличная блокировка используется только в случае, если данная таблица явно блокируется командой LOCK TABLES. Мы вообще не рекомендуем применять LOCK TABLES для упомянутых типов таблиц, поскольку для таблицы InnoDB используется автоматическая блокировка строкового уровня, а для таблиц BDB - блокировка страничного уровня. Это делается, чтобы гарантировать изоляцию транзакций.
В версии MySQL 3.23.7 и выше можно вставлять строки в таблицы MyISAM в то время, когда другие потоки производят чтение из этой таблицы. Следует учитывать, что в настоящее время эта функция работает только при условии, что в таблице в момент вставки отсутствуют какие-либо пустые пространства, оставшиеся после удаленных из нее записей. Как только все пустые места будут заполнены новыми данными, автоматически будет восстановлена возможность делать одновременные вставки.
Табличная блокировка обеспечивает возможность одновременного выполнения чтения из таблицы несколькими потоками, но если какой-нибудь поток попробует произвести запись в таблицу, то вначале он должен получить исключительный доступ. Во время обновления таблицы все другие потоки, стремящиеся получить доступ к этой конкретной таблице, будут ожидать, пока данное обновление не будет завершено.
Поскольку обновление обычно считается более важной операцией, чем SELECT, то все команды, производящие обновления таблицы, имеют более высокий приоритет, чем команды извлечения данных. Такой алгоритм гарантирует, что обновления не зависнут в случае, если для некоторой таблицы выполняется большое количество тяжелых запросов (этот порядок действий можно изменить, используя LOW_PRIORITY с командой обновления или HIGH_PRIORITY с командой SELECT).
Начиная с версии MySQL 3.23. 7 можно использовать переменную max_write_lock_count, чтобы заставить MySQL временно предоставить всем командам SELECT, ожидающим доступ к таблице, более высокий приоритет после заданного числа вставок в таблицу.
Табличную блокировку, однако, нецелесообразно использовать в случае следующего сценария:
Клиент запускает SELECT, требующий длительного времени для выполнения.
Затем другой клиент запускает команду UPDATE на используемой таблице. Этот клиент будет ожидать, пока SELECT не закончит свою работу.
Другой клиент запускает еще одну команду SELECT на той же таблице. Поскольку UPDATE имеет более высокий приоритет, чем SELECT, то эта команда SELECT будет ждать, пока UPDATE не закончит свою работу. Кроме того, вторая команда SELECT будет также ждать, пока не завершится первая команда SELECT!
Поток ждет ситуации заполненного диска. В таком случае все потоки, которые хотят получить доступ к проблемной таблице, будут переведены в состояние ожидания до тех пор, пока не освободится немного дискового пространства.
Ниже представлены некоторые возможные решения данной проблемы:
Постарайтесь заставить команды SELECT выполняться быстрее. Возможно, для этого необходимо будет создать сводные таблицы.
Запустите mysqld с --low-priority-updates. Этим вы назначите всем командам обновления таблицы более низкий приоритет, чем у команды SELECT. Тогда последняя команда SELECT в предыдущем сценарии будет выполняться перед командой INSERT.
Конкретным командам INSERT, UPDATE или DELETE можно назначить более низкий приоритет с помощью атрибута LOW_PRIORITY.
Запустите mysqld с небольшим значением max_write_lock_count, чтобы разрешить блокировки чтения (READ) после определенного количества блокировок записи (WRITE).
SQL-командой: SET LOW_PRIORITY_UPDATES=1 можно указать, что все обновления из конкретного потока должны выполняться с низким приоритетом. See section 5.5.6 Синтаксис команды SET.
Можно указать, что команда SELECT является очень важной, - с помощью атрибута HIGH_PRIORITY. See section 6.4.1 Синтаксис оператора SELECT.
Если имеются проблемы при выполнении команд INSERT совместно с SELECT, перейдите на новые таблицы MyISAM, которые поддерживают одновременное выполнение команд SELECT и INSERT.
Если совместно выполняются преимущественно команды INSERT и SELECT, то решить возникающие при этом проблемы иногда помогает атрибут DELAYED
для INSERT. See section 6.4.3 Синтаксис оператора INSERT.
Если имеются проблемы с командами SELECT и DELETE, то может помочь опция LIMIT для DELETE. See section 6.4.6 Синтаксис оператора DELETE.
Конструктивные особенности MySQL
В MySQL данные и индексы хранятся отдельно, в разных файлах, в то время как во многих (практически во всех) базах данных данные и индексы помещаются вместе в одном и том же файле. Мы полагаем, что конструкция MySQL лучше подходит для очень широкого диапазона современных систем.
Существует еще один способ хранения исходных данных - когда данные для каждого столбца содержатся в отдельной области (примерами являются SDBM и Focus). При такой организации данных неизбежно снижение производительности для каждого запроса, затрагивающего более, чем один столбец. Поскольку при доступе к более чем одному столбцу ситуация очень быстро начинает ухудшаться, то мы полагаем, что эта модель не вполне годится для баз данных общего назначения.
Модель, когда индексы и данные хранятся вместе (как в Oracle/Sybase и других), встречается чаще. В этом случае запись будет находиться в странице соответствующего индекса. Преимущества такой схемы во многих случаях заключаются в том, что считывание с диска получается более экономным - в зависимости от того, насколько хорошо кэшируется индекс. Недостатки же здесь следующие:
Сканирование таблиц выполняется намного медленнее, поскольку необходимо прочитать индексы, чтобы добраться до данных.
Нельзя использовать только таблицу индексов для извлечения данных по запросу.
Теряется много дискового пространства, поскольку необходимо дублировать индексы из узлов (так как нельзя хранить строку в узлах)
Удаления со временем приводят к вырождению таблицы (так как индексы в узлах обычно не обновляются при операции удаления).
Более сложно выполняется кэширование только данных об индексах.
Сделайте объем данных как можно меньше
Одна из основных задач оптимизации заключается в том, чтобы данные (и индексы) занимали как можно меньше места на диске (и в памяти). Это дает значительные преимущества в работе, поскольку ускоряется чтение диска, а оперативная память, как правило, используется меньше. Индексирование также требует меньших ресурсов, если оно выполняется на меньших столбцах.
MySQL поддерживает большое количество различных типов таблиц и форматов строк. Значительный выигрыш в производительности можно получить за счет правильного выбора формата таблицы (see section 7 Типы таблиц MySQL).
Чтобы увеличить производительность работы с таблицей и минимизировать требуемое пространство памяти, можно применять перечисленные ниже технические приемы:
Используйте по возможности наиболее эффективные (наименьшие по объему) типы данных. В MySQL имеется много специализированных типов данных, применение которых позволяет экономить пространство на диске и в памяти.
Используйте, если это возможно, целочисленные типы меньшей длины, чтобы получить таблицы меньшего размера. Например, MEDIUMINT часто лучше, чем INT.
Объявляйте везде, где возможно, столбцы как NOT NULL. Это позволяет ускорить все операции и сэкономить по одному биту для каждого столбца. Однако если для данного приложения действительно нужен NULL, то вы все-таки его (NULL) используйте. Нужно просто избегать наличия NULL во всех столбцах по умолчанию.
Если отсутствуют какие-либо столбцы переменной длины (столбцы типов VARCHAR, TEXT или BLOB), то нужно применять формат записи фиксированного размера. Такой метод дает увеличение скорости, но при этом, к сожалению, может потребоваться лишнее место на диске. See section 7.1.2 Форматы таблиц MyISAM.
Первичные индексы в таблице должны быть как можно короче. Это делает идентификацию конкретной записи простой и эффективной.
Метод хранения/индексации нужно выбрать для каждой таблицы. See section 7 Типы таблиц MySQL.
Создавайте только те индексы, которые действительно необходимы. Индексы хороши для извлечения данных, но плохи при необходимости быстрого сохранения информации. Если работа с таблицей большей частью сводится к поиску на некотором сочетании столбцов, то следует сделать по ним индекс. Первая часть этого индекса должна представлять собой наиболее используемый столбец. Если всегда задействовано много столбцов, то следует первым использовать столбец с большим количеством повторений, чтобы получить лучшее сжатие этого индекса.
Если в столбце с большой степенью вероятности в начальной части символов присутствует уникальный префикс, то лучше индексировать только этот префикс. MySQL поддерживает индекс по части символов столбца. Более короткие индексы работают быстрее не только за счет того, что они занимают меньше дискового пространства, но также и потому, что они обеспечивают больше попаданий в кэш индексов, благодаря чему уменьшается количество операций поиска на диске. See section 5.5.2 Настройка параметров сервера.
Иногда целесообразно разбить очень часто просматриваемую таблицу на две, особенно если таблица имеет динамический формат и при просмотре данной таблицы для поиска соответствующих строк можно использовать таблицу с меньшим статическим форматом.
Использование индексов в MySQL
Индексы применяются для быстрого поиска строк с указанным значением одного столбца. Без индекса чтение таблицы осуществляется по всей таблице начиная с первой записи, пока не будут найдены соответствующие строки. Чем больше таблица, тем больше накладные расходы. Если же таблица содержит индекс по рассматриваемым столбцам, то MySQL может быстро определить позицию для поиска в середине файла данных без просмотра всех данных. Для таблицы, содержащей 1000 строк, это будет как минимум в 100 раз быстрее по сравнению с последовательным перебором всех записей. Однако в случае, когда необходим доступ почти ко всем 1000 строкам, быстрее будет последовательное чтение, так как при этом не требуется операций поиска по диску.
Все индексы MySQL (PRIMARY, UNIQUE, и INDEX) хранятся в виде B-деревьев. Строки автоматически сжимаются с удалением пробелов в префиксах и оконечных пробелов (see section 6.5.7 Синтаксис оператора CREATE INDEX).
Индексы используются для того, чтобы:
Быстро найти строки, соответствующие выражению WHERE.
Извлечь строки из других таблиц при выполнении объединений.
Найти величины MAX() или MIN() для заданного индексированного столбца. Эта операция оптимизируется препроцессором, который проверяет, не используете ли вы WHERE key_part_4 = константа, по всем частям составного ключа < N. В этом случае MySQL сделает один просмотр ключа и заменит выражение константой MIN(). Если все выражения заменяются константой, запрос моментально вернет результат:
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
Производить сортировку или группирование в таблице, если эти операции делаются на крайнем слева префиксе используемого ключа (например ORDER BY key_part_1,key_part_2). Если за всеми частями ключа следует DESC, то данный ключ читается в обратном порядке (see section 5.2.7 Как MySQL оптимизирует ORDER BY).
В некоторых случаях запрос можно оптимизировать для извлечения величин без обращения к файлу данных. Если все используемые столбцы в некоторой таблице являются числовыми и образуют крайний слева префикс для некоторого ключа, то чтобы обеспечить большую скорость, искомые величины могут быть извлечены непосредственно из индексного дерева:
SELECT key_part3 FROM table_name WHERE key_part1=1
Предположим, что вызывается следующий оператор SELECT:
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
Если по столбцам col1 и col2 существует многостолбцовый индекс, то соответствующие строки могут выбираться напрямую. В случае, когда по столбцам col1 и col2 существуют раздельные индексы, оптимизатор пытается найти наиболее ограничивающий индекс путем определения, какой индекс найдет меньше строк, и использует данный индекс для выборки этих строк.
Если данная таблица имеет многостолбцовый индекс, то любой крайний слева префикс этого индекса может использоваться оптимизатором для нахождения строк. Например, если имеется индекс по трем столбцам (col1,col2,col3), то существует потенциальная возможность индексированного поиска по (col1), (col1,col2) и (col1,col2,col3).
В MySQL нельзя использовать частичный индекс, если столбцы не образуют крайний слева префикс этого индекса. Предположим, что имеются команды SELECT, показанные ниже:
mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
Если индекс существует по (col1,col2,col3), то только первый показанный выше запрос использует данный индекс. Второй и третий запросы действительно включают индексированные столбцы, но (col2) и (col2,col3) не являются крайней слева частью префиксов (col1,col2,col3).
MySQL применяет индексы также для сравнений LIKE, если аргумент в выражении LIKE представляет собой постоянную строку, не начинающуюся с символа-шаблона. Например, следующие команды SELECT используют индексы:
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";
В первой команде рассматриваются только строки с "Patrick" , а во второй - только строки с "Pat" .
Следующие команды SELECT не будут использовать индексы:
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;
В первой команде величина LIKE начинается с шаблонного символа. Во второй команде величина LIKE не является константой.
В версии MySQL 4.0 производится другая оптимизация на выражении LIKE. Если используется выражение ... LIKE "%string%" и длина строки (string) больше, чем 3 символа, то MySQL будет применять алгоритм Турбо Бойера-Мура для инициализации шаблона для строки и затем использовать этот шаблон, чтобы выполнить поиск быстрее.
При поиске с использованием column_name IS NULL будут использоваться индексы, если column_name является индексом.
MySQL обычно использует тот индекс, который находит наименьшее количество строк. Индекс применяется для столбцов, которые сравниваются с помощью следующих операторов: =, >, >=, и LIKE с префиксом, не содержащим шаблонного символа, такого как something%.
Если индекс не охватывает все уровни AND в выражении WHERE, то он не применяется для оптимизации данного запроса. Другими словами: чтобы индекс можно было использовать, префикс этого индекса должен входить в каждую группу AND.
Следующие выражения WHERE используют индексы:
... WHERE index_part1=1 AND index_part2=2 AND other_column=3 ... WHERE index=1 OR A=10 AND index=2 /* индекс = 1 ИЛИ индекс = 2 */ ... WHERE index_part1='hello' AND index_part_3=5
/* оптимизировано как "index_part1='hello'" */
... WHERE index1=1 and index2=2 or index1=3 and index3=3;
/* Можно использовать индекс по index1, но не по index2 или index 3 */
Следующие выражения WHERE не используют индексы:
... WHERE index_part2=1 AND index_part3=2 /* index_part_1 не используется */
... WHERE index=1 OR A=10 /* Индекс не используется в обеих частях AND */
... WHERE index_part1=1 OR index_part2=10 /* Нет индекса, покрывающего все строки*/
В некоторых случаях MySQL не использует индекс, даже если это возможно. Несколько примеров таких ситуаций приведено ниже:
Если использование индекса требует от MySQL прохода более чем по 30% строк в данной таблице (в таких случаях просмотр таблицы, по всей видимости, окажется намного быстрее, так как потребуется выполнить меньше операций поиска). Следует учитывать, что если подобный запрос использует LIMIT по отношению только к извлекаемой части строк, то MySQL будет применять индекс в любом случае, так как небольшое количество строк можно найти намного быстрее, чтобы вернуть результат.
Индексы столбцов
В MySQL могут быть проиндексированы столбцы всех типов. Использование индексов на соответствующих столбцах представляет собой хороший способ ускорения выполнения операций SELECT.
Максимальное количество ключей и максимальная длина индексов определяется обработчиком таблиц (see section 7 Типы таблиц MySQL). Можно иметь по меньшей мере 16 ключей на всех обработчиках таблиц и общую длину индексов по меньшей мере 256 байтов.
Для столбцов типов CHAR и VARCHAR можно индексировать префикс столбца. Это намного быстрее и требует меньше дискового пространства, чем индексация всего столбца. Используемый в команде CREATE TABLE синтаксис для индексации префикса столбца выглядит примерно так:
KEY index_name (col_name(length))
В следующем примере создается индекс для первых 10 символов в столбце name:
mysql> CREATE TABLE test ( -> name CHAR(200) NOT NULL, -> KEY index_name (name(10)));
Для столбцов типа BLOB и TEXT индексировать необходимо префикс столбца. Нельзя индексировать столбец целиком.
В версии MySQL 3.23.23 и более поздних можно также создавать специальные индексы FULLTEXT. Они используются для полнотекстового поиска. Полнотекстовые индексы FULLTEXT поддерживают только таблицы типа MyISAM. Они могут создаваться только по столбцам VARCHAR и TEXT. Индексация всегда производится для целого столбца, а частичная индексация не поддерживается. See section 6.8 Полнотекстовый поиск в MySQL.
Многостолбцовые индексы
MySQL может создавать индексы по нескольким столбцам. Индекс может включать в себя до 15 столбцов (на столбцах CHAR и VARCHAR можно также использовать префикс столбца в качестве части индекса)
Многостолбцовый индекс может рассматриваться как упорядоченный массив, содержащий величины, созданные конкатенацией величин проиндексированных столбцов.
MySQL использует многостолбцовые индексы таким образом, что запросы выполняются быстро, когда указывается известная часть для первого столбца в индексе в выражении WHERE, даже если не заданы величины для других столбцов.
Предположим, создается следующая таблица:
mysql> CREATE TABLE test ( -> id INT NOT NULL, -> last_name CHAR(30) NOT NULL, -> first_name CHAR(30) NOT NULL, -> PRIMARY KEY (id), -> INDEX name (last_name,first_name));
Индекс name является индексом по столбцам last_name и first_name. Этот индекс будет применяться для запросов, указывающих величины в известной области для last_name или для обоих столбцов last_name и first_name. Таким образом, индекс name будет использоваться в следующих запросах:
mysql> SELECT * FROM test WHERE last_name="Widenius";
mysql> SELECT * FROM test WHERE last_name="Widenius" -> AND first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius" -> AND (first_name="Michael" OR first_name="Monty");
mysql> SELECT * FROM test WHERE last_name="Widenius" -> AND first_name >="M" AND first_name < "N";
Чтобы получить более подробную информацию о том, как в MySQL используются индексы для улучшения работы запросов, See section 5.4.3 Использование индексов в MySQL.
Почему так много открытых таблиц?
При запуске mysqladmin status можно увидеть что-нибудь вроде этого:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
В чем же дело, ведь у вас всего 6 таблиц?
MySQL является многопоточной базой данных, поэтому для одной и той же таблицы могут одновременно присутствовать несколько запросов. Чтобы минимизировать эту проблему для двух потоков, имеющих различный статус на одном и том же файле, таблица открывается независимо каждым конкурирующим потоком. Это требует дополнительной памяти, но, как правило, повышает производительность. Кроме того, для таблиц типа ISAM и MyISAM требуется дополнительный дескриптор для файла данных. В таблицах этого типа дескриптор индексного файла используется совместно всеми потоками.
В следующем разделе вы найдете более подробную информацию по этой теме (see section 5.4.7 Открытие и закрытие таблиц в MySQL).
Открытие и закрытие таблиц в MySQL
Параметры table_cache, max_connections и max_tmp_tables задают максимальное количество файлов, которые сервер держит открытыми. Если увеличить один или оба этих параметра, то можно столкнуться с ограничением, накладываемым данной операционной системой на количество открытых файловых дескрипторов для одного процесса. Во многих системах, однако, этот предел можно увеличить. Поскольку способы изменения данного значения для разных систем могут быть совершенно различными, в каждом конкретном случае вам следует обращаться к документации по своей операционной системе.
Значения table_cache и max_connections взаимосвязаны. Например, для 200 одновременно работающих соединений необходимо иметь кэш для таблиц размером по меньшей мере 200 * n, где n - максимальное количество связанных таблиц. Необходимо также зарезервировать несколько дополнительных файловых дескрипторов для временных таблиц и файлов.
Следует удостовериться, что ваша операционная система способна обрабатывать такое количество открытых файловых дескрипторов, какое предполагает данная установка table_cache. Если устанавливается слишком высокое значение table_cache, то MySQL может выйти за пределы допустимого количества файловых дескрипторов, прервать соединение, не выполнять запросы и стать очень ненадежным. Необходимо также принять во внимание, что для обработчика таблиц MyISAM требуется по два файловых дескриптора для каждой уникальной открытой таблицы. Допустимое для MySQL количество файловых дескрипторов можно увеличить с помощью опции запуска --open-files-limit=# (see section A.2.16 Не найден файл (File not found)).
Максимальное количество таблиц в кэше открытых таблиц будет равно количеству, указанному в table_cache (по умолчанию - 64; это число можно изменить с помощью опции -O table_cache=# для mysqld). Следует учитывать, что для выполнения запросов MySQL может временно открыть и больше таблиц.
Неиспользуемая таблица закрывается и удаляется из кэша таблиц в следующих ситуациях:
Когда кэш заполнен и поток старается открыть таблицу, отсутствующую в этом кэше.
Когда кэш содержит более, чем table_cache входных величин, и поток больше не использует таблицу.
Когда кто-либо выполняет mysqladmin refresh или mysqladmin flush-tables.
Когда кто-либо выполняет FLUSH TABLES.
Когда табличный кэш заполняется, сервер использует следующую процедуру размещения входных данных кэша для их использования:
Не используемые в данное время таблицы освобождаются в порядке наиболее давнего использования.
Если кэш заполнен и ни одна таблица не может быть высвобождена, а необходимо открыть новую таблицу, то кэш временно расширяется настолько, насколько необходимо.
Если кэш находится во временно расширенном состоянии и таблица переходит из используемого в неиспользуемое состояние, то такая таблица закрывается и освобождается из кэша.
Таблица открывается для каждого одновременного доступа. Это означает, что, если существуют два потока, получающие доступ к одной и той же таблице, или происходит обращение к этой таблице дважды в одном и том же запросе (с помощью AS), то данная таблица должна быть открыта дважды. Для первого открытия любой таблицы требуется два файловых дескриптора; для каждого дополнительного использования - только один. Дополнительный дескриптор для первого открытия используется для индексного файла; этот дескриптор используется совместно всеми потоками.
При открытии таблицы командой HANDLER table_name OPEN создается выделенный табличный объект для данного потока. Этот табличный объект недоступен для других потоков и не будет закрыт, пока данный поток не вызовет команду HANDLER table_name CLOSE или сам поток не уничтожится (see section 6.4.2 Синтаксис оператора HANDLER). Если это произойдет, то данная таблица помещается обратно в кэш таблиц (если он не заполнен).
Чтобы узнать, не слишком ли мал кэш таблиц, следует проверить переменную Opened_tables. Если ее значение достаточно велико, даже если вы не выполняли слишком часто команду FLUSH TABLES, то необходимо увеличить данный кэш таблиц (see section 4.5.6.3 SHOW STATUS).
Недостатки создания множества таблиц в одной базе данных
Если в каталоге присутствует большое количество файлов, то операции открытия, закрытия и создания будут медленными. Выполнение значительного количества команд SELECT на большом количестве разных таблиц приводит к небольшим непроизводительным затратам при заполненном табличном кэше, поскольку для открытия одной таблицы требуется закрыть другую. Чтобы сократить эту перегрузку, следует увеличить табличный кэш.
Настройка параметров системы, компляции и запуска
Мы начинаем с вопросов системного уровня, поскольку некоторые из них требуют решения на самых ранних этапах. В других случаях может оказаться достаточно только беглого просмотра этого материала, поскольку значительного выигрыша в оптимизации он не обеспечивает. Однако всегда хорошо иметь представление о том, какую пользу можно получить при изменении параметров на этом уровне.
Используемая по умолчанию операционная система имеет действительно большое значение! Чтобы получить максимальную выгоду от применения многопроцессорных компьютеров, следует применять Solaris (так как под этой ОС потоки работают в самом деле хорошо) или Linux (поскольку ядро 2.2 обеспечивает действительно хорошую поддержку SMP). Однако на 32-разрядных компьютерах Linux по умолчанию имеет ограничение размера файлов в 2 Гб. Будем надеяться, что это ограничение в скором времени будет снято при выпуске новых файловых систем (XFS/Reiserfs). Но если вам действительно не обойтись без файлов с размерами более чем 2 Гб на 32-разрядном ПК с Linux-intel, то следует использовать патч LFS для файловой системы ext2.
На многих платформах MySQL еще не находился в промышленной эксплуатации, поэтому мы рекомендуем прежде, чем остановить свой выбор на какой-либо платформе, сначала ее протестировать.
Другие советы:
Если оперативной памяти достаточно, то можно было бы удалить все внешние запоминающие устройства. Существуют операционные системы, которые при некоторых обстоятельствах будут использовать внешние запоминающие устройства даже при наличии свободной памяти.
Чтобы избежать внешнего блокирования, используйте опцию MySQL --skip-external-locking. Следует учитывать, что пока работает только один сервер, это не будет оказывать большого влияния на функциональные возможности MySQL. Только не забудьте остановить сервер (или блокировать соответствующие части) перед запуском myisamchk. В некоторых системах такое переключение обязательно, поскольку внешнее блокирование не работает в любом случае.
Опция --skip-external-locking включена по умолчанию при компилировании с потоками MIT-pthreads, поскольку функция flock() не полностью поддерживается потоками MIT-pthreads на всех платформах. Для Linux также подразумевается, что блокирование файлов пока еще ненадежно.
Нельзя использовать --skip-external-locking только в одном случае - при запуске нескольких серверов (не клиентов) MySQL на одних и тех же данных, или при запуске myisamchk на таблице без предварительного сбрасывания на диск и блокирования демона mysqld для сервера, содержащего эти таблицы. Можно также применять команду LOCK TABLES/UNLOCK TABLES даже при использовании --skip-external-locking.
Настройка параметров сервера
Размеры буферов, используемые по умолчанию сервером mysqld, можно узнать с помощью следующей команды:
shell> mysqld --help
Эта команда выдает список всех опций mysqld и конфигурируемых переменных. Вывод включает в себя величины по умолчанию и выглядит примерно следующим образом:
Possible variables for option --set-variable (-O) are: back_log current value: 5 bdb_cache_size current value: 1048540 binlog_cache_size current value: 32768 connect_timeout current value: 5 delayed_insert_timeout current value: 300 delayed_insert_limit current value: 100 delayed_queue_size current value: 1000 flush_time current value: 0 interactive_timeout current value: 28800 join_buffer_size current value: 131072 key_buffer_size current value: 1048540 lower_case_table_names current value: 0 long_query_time current value: 10 max_allowed_packet current value: 1048576 max_binlog_cache_size current value: 4294967295 max_connections current value: 100 max_connect_errors current value: 10 max_delayed_threads current value: 20 max_heap_table_size current value: 16777216 max_join_size current value: 4294967295 max_sort_length current value: 1024 max_tmp_tables current value: 32 max_write_lock_count current value: 4294967295 myisam_sort_buffer_size current value: 8388608 net_buffer_length current value: 16384 net_retry_count current value: 10 net_read_timeout current value: 30 net_write_timeout current value: 60 read_buffer_size current value: 131072 record_rnd_buffer_size current value: 131072 slow_launch_time current value: 2 sort_buffer current value: 2097116 table_cache current value: 64 thread_concurrency current value: 10 tmp_table_size current value: 1048576 thread_stack current value: 131072 wait_timeout current value: 28800
Не забывайте, что --set-variable не используется в MySQL 4.0. Просто указывайте --var=option.
Если сервер mysqld в настоящее время работает, то для того, чтобы увидеть, какие величины реально используются для переменных, необходимо выполнить следующую команду:
shell> mysqladmin variables
Полное описание всех переменных можно найти в разделе SHOW VARIABLES этого руководства (see section 4.5.6.4 SHOW VARIABLES).
Некоторые статистические данные по работающему серверу можно также просмотреть с помощью команды SHOW STATUS (see section 4.5.6.3 SHOW STATUS).
В MySQL используются алгоритмы, масштабируемые в широких пределах, так что обычно можно работать с очень небольшой памятью. Однако если выделить для MySQL больше памяти, то и производительность, как правило, будет выше.
При настройке сервера MySQL наиболее важными из используемых являются две переменные key_buffer_size и table_cache. Но прежде чем пытаться изменить ту или иную переменную, вначале следует убедиться, что вы обладаете необходимыми для этого правами.
Если имеется большая память (>=256 Mб) и много таблиц, то для обеспечения максимальной производительности путем регулирования количества клиентов следует использовать что-нибудь вроде этого:
shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \ -O sort_buffer=4M -O read_buffer_size=1M &
Если память составляет только 128 Mб и количество таблиц невелико, но тем не менее, выполняется много сортировок, то можно использовать что-нибудь вроде:
shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M
При малой памяти и большом количестве соединений следует использовать что-нибудь вроде следующего:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \ -O read_buffer_size=100k &
или даже:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \ -O table_cache=32 -O read_buffer_size=8k \ -O net_buffer_length=1K &
Если выполняются операции GROUP BY или ORDER BY на файлах, которые намного больше, чем доступная память, то следует увеличить величину record_rnd_buffer для ускорения чтения строк после выполнения сортировки.
После установки MySQL каталог `support-files' будет содержать несколько различных файлов-примеров `my.cnf', а именно: `my-huge.cnf', `my-large.cnf', `my-medium.cnf' и `my-small.cnf', которые можно использовать как основу для оптимизации вашей системы.
Если демон mysqld не отконфигурирован для использования очень малой памяти для каждого соединения, то в условиях очень большого количества соединений могут возникнуть проблемы с подкачкой виртуальной памяти. При наличии достаточной памяти для всех соединений mysqld, конечно, будет функционировать лучше.
Следует учитывать, что при изменении какой-либо опции для mysqld это изменение действительно только для данного экземпляра сервера.
Чтобы увидеть воздействие изменения параметра, нужно выполнить что-нибудь вроде этого:
shell> mysqld -O key_buffer=32m --help
Следует удостовериться, что опция --help расположена последней; в противном случае влияние любой опции, следующей после нее в командной строке, в данном выводе отражено не будет.
Как компиляция и линкование влияет на скорость MySQL
Большинство из последующих тестов выполняются под Linux с использованием тестов производительности MySQL, но они должны дать некоторое представление и для других операционных систем и рабочих нагрузок.
Самый быстрый исполняемый код получается при линковании с помощью -static.
Под Linux наиболее быстрый код можно получить при компилировании pgcc с опицей -O3. Чтобы скомпилировать sql_yacc.cc с этой опцией, требуется около 200 Mб памяти, поскольку компилятор gcc/pgcc забирает много памяти. При конфигурировании MySQL следует также установить CXX=gcc - чтобы не линковалась библиотека libstdc++ (в этом нет необходимости). Следует учитывать, что при некоторых версиях компилятора pgcc результирующий код будет работать только на настоящих процессорах Pentium, даже если использовать возможность компилятора выдавать результирующий код, работоспособный на всех процессорах типа x586 (например AMD).
Используя просто лучший компилятор и/или лучшую опцию компилятора, можно получить для приложения увеличение скорости на 10-30%. Это особенно важно, если вы компилируете сервер SQL самостоятельно!
Мы протестировали такие компиляторы как Cygnus CodeFusion и Fujitsu, но ни тот, ни другой не были достаточно свободны от ошибок, чтобы можно было скомпилировать MySQL с оптимизирующими параметрами.
При компилировании MySQL необходимо включать только наборы кодировок, которые вы собираетесь использовать (опция --with-charset=xxx). Стандартная поставка MySQL скомпилирована с поддержкой всех кодировок.
Ниже приводится обзор некоторых действий, которые мы предпринимали для ускорения работы:
При использовании pgcc и компиляции всего кода с -O6 сервер mysqld на 1% быстрее, чем при gcc 2.95.2.
При динамическом связывании (без опции -static) результирующий исполняемый файл сервера будет на 13% медленнее работать под управлением Linux. Обратите внимание: вы спокойно можете использовать динамическую библиотеку MySQL. Это касается только сервера и актуально только там, где нужна высокая производительность.
При сокращении двоичного кода mysqld с помощью strip libexec/mysqld можно получить прирост скорости результирующего двоичного кода до 4%.
При соединении с использованием протокола TCP/IP, а не сокетов Unix работа будет на 7,5% медленнее на том же самом компьютере (при подключении к localhost MySQL по умолчанию будет использовать сокеты).
При соединении с использованием протокола TCP/IP с другим компьютером по сети Ethernet с пропускной способностью 100Mбит/сек скорость будет на 8-11% ниже.
При запуске наших тестов производительности с использованием безопасных соединений (все данные зашифрованы с поддержкой протокола SSL) скорость была на 55% ниже.
Если код компилируется с параметром --with-debug=full, то для большинства запросов потери в производительности будут составлять до 20%, но некоторые запросы могут выполняться значительно дольше (тесты производительности MySQL работают на 35% медленнее). При использовании опции --with-debug теряется только 15% производительности. При запуске mysqld, откомпилированного с --with-debug=full и --skip-safemalloc, результат должен почти таким же, как и при компиляции с --with-debug.
На компьютере Sun UltraSPARC-IIe, Forte 5.0 дает на 4% более быстрый код чем gcc 3.2
На компьютере Sun UltraSPARC-IIe, Forte 5.0 дает на 4% более быстрый код в 32-разрядном режиме чем в 64-разрядном.
Компилирование посредством gcc 2.95.2 для UltraSPARC с опцией -mcpu=v8 -Wa,-xarch=v8plusa дает прирост производительности на 4%.
Под операционной системой Solaris 2.5.1 потоки MIT-pthreads на 8-12% медленнее, чем собственные потоки Solaris для единичного процессора. При возрастании нагрузки на процессоры разница должна получиться больше.
Запуск с --log-bin делает mysqld на 1% медленнее.
Компилирование под Linux-x86 с использованием gcc без указателей фреймов -fomit-frame-pointer или -fomit-frame-pointer -ffixed-ebp
делает mysqld на 1-4% быстрее.
Поставка MySQL под Linux, которую предоставляет MySQL AB, обычно компилировалась с pgcc, но мы должны были вернуться к обычному компилятору gcc из-за ошибок в pgcc, которая могут генерировать код, не исполняемый на AMD. Пока эти ошибки не будут устранены, мы будем продолжать использовать gcc, однако если ваш компьютер не относится к типу AMD, то можно получить более быстрый двоичный код, компилируя его с pgcc. Стандартный двоичный код MySQL для Linux слинкован статически, чтобы сделать его более быстрым и более переносимым.
Как MySQL использует память
В следующем перечне дано описание некоторых аспектов использования памяти сервером mysqld. Там, где это возможно, приводятся имена серверных переменных, относящихся к использованию памяти:
Буфер ключей (переменная key_buffer_size) используется совместно всеми потоками; другие буферы, используемые данным сервером, выделяются при необходимости (see section 5.5.2 Настройка параметров сервера).
Каждое соединение использует определенное пространство в памяти для конкретного потока: стек (по умолчанию 64Kб, переменная thread_stack), буфер соединения (переменная net_buffer_length) и буфер результата (переменная net_buffer_length). Буфер соединения и буфер результата при необходимости динамически расширяются вплоть до max_allowed_packet. При выполнении запроса также выделяется память для копии строки данного текущего запроса.
Все потоки совместно используют одну и туже базовую память.
Только сжатые таблицы типа ISAM/MyISAM имеют распределенную память. Это объясняется тем, что 4 Гб памяти (адресуемой в рамках 32-битной разрядности) мало для достаточно больших таблиц. Когда системы с 64-разрядными адресным пространством получат более широкое распространение, мы сможем добавить в сервер общую поддержку для распределения памяти.
Каждый запрос, выполняющий последовательный просмотр таблицы, размещается в буфере чтения (переменная record_buffer).
При чтении строк в "случайном" порядке (например, после сортировки) выделяется буфер "случайного чтения", чтобы избежать поиска по диску (переменная record_rnd_buffer).
Все объединения выполняются за одну операцию, и большинство объединений может производиться даже без временных таблиц. Большинство временных таблиц располагаются в оперативной памяти (в динамически выделяемой области HEAP). Временные таблицы с записями большой длины (вычисляемой как сумма длин всех столбцов) или таблицы, содержащие столбцы BLOB, хранятся на диске. В версиях MySQL до 3.23.2 существует проблема, заключающаяся в том, что если таблицы HEAP в динамически выделяемой области превышают размер tmp_table_size, то возникает ошибка The table tbl_name is full. В более новых версиях эта проблема при необходимости решается путем автоматического преобразования хранящихся в оперативной памяти HEAP-таблиц в таблицы MyISAM, расположенные на диске. Чтобы обойти эту проблему, можно увеличить размер временных таблиц установкой опции tmp_table_size в mysqld или установкой SQL-опции SQL_BIG_TABLES в клиентской программе (see section 5.5.6 Синтаксис команды SET). В версии MySQL 3.20 максимальный размер временной таблицы был равен record_buffer*16, так что при использовании данной версии необходимо увеличить значение record_buffer. Можно также запустить mysqld с опцией --big-tables - для того, чтобы всегда хранить временные таблицы на диске. Однако это будет влиять на скорость многих сложных запросов.
Большинство запросов, выполняющих сортировку, размещаются в буфере сортировки и в 0-2 временных файлах, в зависимости от размера результирующего набора данных (see section A.4.4 Где MySQL хранит временные файлы).
Почти все операции, связанные с анализом и вычислениями, выполняются в пространстве локальной памяти. Для небольших задач не требуется никаких дополнительных затрат памяти и удается избежать обычно медленных процессов выделения и освобождения памяти. Память выделяется только для непредвиденно больших строк (это делается с помощью функций malloc() и free()).
Каждый файл индексов и файл данных открываются сразу для каждого параллельно работающего потока. Для каждого параллельного потока выделяется место в памяти для структуры таблицы, структур столбцов для каждого столбца и буфер размером 3 * n (где n представляет максимальную длину строки без учета столбцов BLOB). Для столбца BLOB
используется от 5 до 8 байтов плюс длина данных BLOB. Обработчики таблиц ISAM/MyISAM будут использовать один дополнительный буфер строки для внутреннего представления.
Для каждой таблицы, имеющей столбцы BLOB, буфер динамически увеличивается при чтении больших величин BLOB. При просмотре таблицы выделяется буфер с размером, равным наибольшей величине BLOB.
Обработчики всех находящихся в употреблении таблиц хранятся в кэше и обрабатываются в порядке их поступления (режим FIFO). Обычно этот кэш содержит 64 элемента. Если данная таблица была использована двумя работающими потоками в одно и то же время, то кэш содержит два элемента для такой таблицы (see section 5.4.7 Открытие и закрытие таблиц в MySQL).
Команда mysqladmin flush-tables закрывает все неиспользуемые таблицы и отмечает все используемые таблицы, которые необходимо закрыть после окончания выполнения текущего потока. Такой алгоритм позволяет эффективно освобождать большое количество используемой памяти.
Программа ps и другие программы контроля состояния системы могут сообщать, что mysqld использует слишком много памяти. Это может быть вызвано расположением стеков памяти потоков по различным адресам в памяти. Например, версия программы ps для Solaris интерпретирует неиспользуемую память между стеками как используемую. Это можно проверить путем выполнения допустимой перестановки с помощью swap -s. Мы тестировали mysqld при помощи коммерческих детекторов утечки памяти, так что никаких потерь памяти быть не должно.
Как MySQL использует DNS
Когда к mysqld подключается новый клиент, mysqld выделяет новый поток для обработки данного запроса. Этот поток вначале проверяет, имеется ли в кэше имен хостов имя требуемого хоста. Если нет, то поток вызовет функции gethostbyaddr_r() и gethostbyname_r(), чтобы определить имя хоста.
Если операционная система не обеспечивает вышеописанные вызовы с поддержкой потоков, то данный поток заблокирует флаг и вызовет вместо этого функции gethostbyaddr() и gethostbyname(). Следует учитывать, что в таком случае никакой другой поток не сможет определять имена других хостов, отсутствующих в кэше имен хостов, пока первый поток не будет готов.
Можно заблокировать поиск DNS хоста, запустив mysqld с параметром --skip-name-resolve. В этом случае, однако, в таблицах привилегий MySQL можно использовать только IP-адреса.
Если процесс установления DNS очень медленный и хостов очень много, то можно получить более высокую производительность либо путем блокировки поиска DNS при помощи --skip-name-resolve, либо увеличив размер определения HOST_CACHE_SIZE (по умолчанию: 128) и перекомпилировав mysqld.
Заблокировать кэш имен хостов можно с помощью --skip-host-cache. Можно также очистить этот кэш с помощью команды FLUSH HOSTS или mysqladmin flush-hosts.
Можно запретить соединения по протоколу TCP/IP, запустив mysqld с опцией --skip-networking.
Синтаксис команды SET
SET [GLOBAL | SESSION] sql_variable=expression, [[GLOBAL | SESSION] sql_variable=expression...]
Команда SET устанавливает различные опции, влияющие на работу сервера или клиента.
Следующие примеры иллюстрируют различный синтаксис, который можно использовать для установки переменных.
В старых версиях MySQL мы допускали использование SET OPTION, но этот синтакс теперь считается морально устаревшим.
В MySQL 4.0.3 мы также добавили режимы GLOBAL и SESSION и реализовали доступ к наиболее важным переменным запуска.
LOCAL может использоваться как синоним для SESSION.
Если вы устанавливаете несколько переменных в одной команде, то последний указанный режим GLOBAL | SESSION будет использован.
SET sort_buffer_size=10000; SET @@local.sort_buffer_size=10000; SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
Синтаксис @@variable_name поддерживается с тем, чтобы сделать MySQL более совместимым с другими СУБД.
Различные системные переменные, которые вы можете установить, описаниы в этом руководстве. See section 6.1.5 Системные переменные.
Если вы используете режим SESSION (по умолчанию), то значения переменных, которые вы устанавливаете, остаются в в действии до тех пор, пока текущая сессия не будет завершена, или до тех пор, пока вы не установите переменные в другое значение. Если вы используете GLOBAL, который требует привилегии SUPER, это значение запоминается и используется для всех новых соединений до тех пор, пока сервер не будет перезагружен. Если вы хотите сделать какое-либо значение перманентным, то вам следует указать его в одном из файлов конфигураций MySQL. See section 4.1.2 Файлы параметров `my.cnf'.
Чтобы избежать неправильного использования, MySQL будет сообщать об ошибке, если вы будете выполнять SET GLOBAL на переменной, которую только можно изменять в SET SESSION или если вы не выбираете режим GLOBAL для глобальной переменной.
Если вы хотите установить сессионную (SESSION) переменную в значение глобальной (GLOBAL) или просто в значение по умолчанию - вы можете установить это значение в DEFAULT.
SET max_join_size=DEFAULT;
Это аналогично:
SET @@session.max_join_size=@@global.max_join_size;
Если вам необходимо ограничить максимальное значение, которое может принимать стартовая переменная с помощью SET, вы можете указать это с помощью использования опции командной строки --maximum-variable-name. See section 4.1.1 Параметры командной строки mysqld.
Список большинства переменных можно получить с помощью SHOW VARIABLES. See section 4.5.6.4 SHOW VARIABLES. Вы можете получить конкретное значение с помощью синтаксиса @@[global.|local.]variable_name:
SHOW VARIABLES like "max_join_size"; SHOW GLOBAL VARIABLES like "max_join_size"; SELECT @@max_join_size, @@global.max_join_size;
Далее следует описание переменных, использующих нестандартный синтаксис SET и некоторых других. Описание других переменных можно найти в секции, описывающих системные переменные, вместе с описанием стартовых опций или в описании команды SHOW VARIABLES. See section 6.1.5 Системные переменные. See section 4.1.1 Параметры командной строки mysqld. See section 4.5.6.4 SHOW VARIABLES.
CHARACTER SET character_set_name | DEFAULT
Преобразует все строки, передающиеся от клиента, и строки передающиеся клиенту, в соответствии с заданным набором символов. В настоящее время единственной опцией для character_set_name является cp1251_koi8, но можно легко добавить новые наборы символов, отредактировав файл sql/convert.cc в дистрибутиве исходного кода MySQL. Чтобы восстановить установку по умолчанию, следует установить значение character_set_name в DEFAULT.
Следует учитывать, что синтаксис установки опции CHARACTER SET отличается от синтаксиса установки других опций.
PASSWORD = PASSWORD('некий пароль')
Устанавливает пароль для текущего пользователя. Любой не анонимный пользователь может изменить свой собственный пароль!
PASSWORD FOR user = PASSWORD('некий пароль')
Устанавливает пароль для особого пользователя для текущего серверного хоста. Это может сделать только пользователь, имеющий доступ к базе данных mysql. Данный пользователь должен быть представлен в формате user@hostname, где user и hostname в точности соответствуют записям этих позиций в столбцах User и Host в таблице mysql.user. Например, если записи в полях User и Host соответственно были bob и %.loc.gov, то необходимо писать:
mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");
или
mysql> UPDATE mysql.user SET password=PASSWORD("newpass") -> WHERE user="bob" AND host="%.loc.gov";
SQL_AUTO_IS_NULL = 0 | 1
Если установить в 1 (значение по умолчанию), то можно найти последнюю внесенную строку для таблицы со столбцом AUTO_INCREMENT с помощью следующей конструкции: WHERE auto_increment_column IS NULL. Эта возможность используется некоторыми ODBC-программами, такими как Access.
AUTOCOMMIT = 0 | 1
Если установить в 1, то все изменения в таблицу будут вноситься немедленно. Чтобы открыть многокомандную транзакцию, необходимо использовать команду BEGIN (see section 6.7.1 Синтаксис команд BEGIN/COMMIT/ROLLBACK). Если установить данную опцию в 0, то необходимо использовать COMMIT / ROLLBACK
для того, чтобы принять/отменить эту транзакцию (see section 6.7.1 Синтаксис команд BEGIN/COMMIT/ROLLBACK). Следует учитывать, что при переходе из режима работы без AUTOCOMMIT в режим AUTOCOMMIT MySQL автоматически выполнит COMMIT для любой открытой транзакции.
BIG_TABLES = 0 | 1
Если установить в 1, то предпочтительным местом хранения всех временных таблиц будет диск, а не оперативная память. Это вызовет некоторое замедление работы, зато для больших операций SELECT, требующих обширных временных таблиц, не будет выдаваться ошибка The table tbl_name is full. Для нового соединения значение этой величины по умолчанию равно 0 (т.е. использовать для временных таблиц оперативную память). Эта переменная раньше называлась SQL_BIG_TABLES.
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 раньше освободить блокировки таблиц и окажется полезным в случаях, когда требуется значительное время для пересылки результирующего набора данных клиенту.
LOW_PRIORITY_UPDATES = 0 | 1
При установке в 1 все команды INSERT, UPDATE, DELETE и LOCK TABLE WRITE будут ожидать, пока не будет ни одной ожидающей решения команды SELECT или LOCK TABLE READ на обрабатываемой таблице. Эта переменная раньше называлась SQL_LOW_PRIORITY_UPDATES.
MAX_JOIN_SIZE = значение | DEFAULT
Запрещает команды SELECT, которым, возможно, придется обрабатывать более, чем указанное значение комбинаций строк. Установив эту величину, можно определить команды SELECT, в которых ключи используются неправильно и которые, возможно, потребуют длительного времени для исполнения. При установке этой опции в величину, отличную от DEFAULT, сбрасывается флаг SQL_BIG_SELECTS. Если вновь установить флаг SQL_BIG_SELECTS, то переменная SQL_MAX_JOIN_SIZE будет игнорироваться. Значение по умолчанию для этой переменной можно установить, запустив mysqld с -O max_join_size=#. Эта переменная раньше называлась SQL_MAX_JOIN_SIZE.
Следует учитывать, что если результат запроса всегда находится в кэше запросов, то упомянутая выше проверка выполняться не будет. Вместо этого MySQL будет отсылать результат клиенту, поскольку результат запроса уже вычислен и отсылка его клиенту не создаст нагрузки для сервера.
QUERY_CACHE_TYPE = OFF | ON | DEMAND
QUERY_CACHE_TYPE = 0 | 1 | 2
Определяет установку кэша запросов для данного потока.
Опция | Описание |
0 or OFF | Не кэшировать или не извлекать результаты |
1 or ON | Кэшировать все результаты за исключением запросов SELECT SQL_NO_CACHE ... |
2 or DEMAND | Кэшировать только запросы SELECT SQL_CACHE ... |
SQL_SAFE_UPDATES = 0 | 1
Если установить в 1, то MySQL будет прерывать выполнение поступивших команд UPDATE или DELETE, в которых не используется ключ или LIMIT в выражении WHERE. Это позволяет обнаружить ошибочные обновления при ручном создании команд SQL.
SQL_SELECT_LIMIT = value | DEFAULT
Максимальное количество записей, возвращаемых командой SELECT. Если SELECT
содержит выражение LIMIT, то LIMIT превосходит по старшинству величину в SQL_SELECT_LIMIT. Для нового соединения значение по умолчанию равно "unlimited". Если предел был изменен, то его можно вернуть в значение по умолчанию указанием величины DEFAULT в выражении SQL_SELECT_LIMIT.
SQL_LOG_OFF = 0 | 1
При установке в 1 для данного клиента в стандартный журнал не будут заноситься никакие записи, если клиент имеет привилегии SUPER. Это не относится к журналу обновлений!
SQL_LOG_UPDATE = 0 | 1
При установке в 0 для данного клиента в журнал обновлений не будут заноситься никакие записи, если клиент имеет привилегии SUPER. Это не относится к стандартному журналу!
SQL_QUOTE_SHOW_CREATE = 0 | 1
При установке этой опции в 1 SHOW CREATE TABLE будет заключать в кавычки имена таблиц и столбцов. Имеет значение Включено по умолчанию, чтобы работала репликация таблиц с изощренными именами столбцов (see section 4.5.6.8 SHOW CREATE TABLE).
TIMESTAMP = timestamp_value | DEFAULT
Устанавливает время для данного клиента. Применяется для получения первоначальной временной метки при использовании журнала обновлений для восстановления строк. Переменная timestamp_value должна представлять системное время Unix, а не временную метку MySQL.
LAST_INSERT_ID = #
Устанавливает величину, возвращаемую функцией LAST_INSERT_ID(). Хранится в журнале обновлений при использовании функции LAST_INSERT_ID() в команде, обновляющей таблицу.
INSERT_ID = #
Устанавливает величину, которую следует использовать в следующей команде INSERT или ALTER TABLE при внесении величины AUTO_INCREMENT. В основном используется с журналом обновлений.
Использование символических ссылок для баз данных
Для создания символической ссылки для базы данных вначале следует создать каталог на некотором диске, где имеется свободное место, а затем создать символическую ссылку на него из каталога баз данных MySQL.
shell> mkdir /dr1/databases/test shell> ln -s /dr1/databases/test mysqld-datadir
MySQL не поддерживает ссылку из одного каталога на несколько баз данных. Замещение каталога базы данных символической ссылкой будет хорошо работать только в случае, если вы не создаете символическую ссылку между базами данных. Предположим, имеется база данных db1 в каталоге данных MySQL и создается символическая ссылка db2, указывающая на db1:
shell> cd /path/to/datadir shell> ln -s db1 db2
Теперь для любой таблицы tbl_a в db1 должна существовать таблица tbl_a в db2. Если один поток обновляет db1.tbl_a, а другой поток - db2.tbl_a, то возникнут проблемы.
Если описанная выше возможность действительно необходима, то нужно изменить следующий код в mysys/mf_format.c:
if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))
на
if (1)
Под Windows можно использовать внутренние символические ссылки на каталоги (путем компиляции MySQL с -DUSE_SYMDIR). Это позволяет размещать различные базы данных на различных дисках (see section 2.6.2.5 Распределение данных в Windows между несколькими различными дисками).
Не следует использовать символические ссылки
Не следует использовать символические ссылки для таблиц в версиях до MySQL 4.0, где при работе с ними требуется особая тщательность. Проблема заключается в том, что, если запускаются команды ALTER TABLE, REPAIR TABLE
или OPTIMIZE TABLE на таблице, связанной символической ссылкой, то символические ссылки будут удалены и заменены исходными файлами. Это происходит потому, что любая вышеназванная команда работает путем создания временного файла в каталоге базы данных и по завершении команды происходит замещение исходного файла временным.
Не следует связывать символическими ссылками таблицы в системах, где вызов функции realpath() работает не полностью (по крайней мере, realpath() поддерживают Linux и Solaris).
В MySQL 4.0 символические ссылки полностью поддерживаются только для таблиц MyISAM. Для других типов таблиц при выполнении какой-либо из вышеупомянутых команд могут возникать непонятные проблемы.
Обработка символических ссылок в MySQL 4.0 происходит следующим образом (это в основном относится только к таблицам MyISAM).
В каталоге данных всегда будет находиться файл определения таблицы и файлы данных/индексов.
Можно связывать символическими ссылками файл индексов и файл данных с различными каталогами независимо друг от друга.
Связывание символическими ссылками можно выполнить из операционной системы (если не запущен mysqld) или с помощью команды INDEX/DATA DIRECTORY="path-to-dir" в CREATE TABLE (see section 6.5.3 Синтаксис оператора CREATE TABLE).
myisamchk не замещает символическую ссылку на индекс/файл, а работает напрямую с файлами, на которые указывает символическая ссылка. Все временные файлы будут создаваться в том же каталоге, где находится файл данных/индексов.
При удалении таблицы, в которой используются символические ссылки, как ссылка, так и файл, на который ссылка указывает, удаляются. Это веская причина для того, чтобы не запускать mysqld в качестве суперпользователя (root) и не позволять другим иметь доступ к записи в каталоги баз данных MySQL.
Если таблица переименовывается с помощью ALTER TABLE RENAME, и вы не переносите таблицу в другую базу данных, то данная символическая ссылка в каталоге базы данных будет переименована и файл данных/индексов соответственно будет переименован.
Если ALTER TABLE RENAME используется для переноса таблицы в другую базу данных, то эта таблица будет перенесена в другой каталог базы данных, а старые символические ссылки и файлы, на которые они указывают, будут удалены. Иными словами, новая таблица не будет ссылкой.
Если символические ссылки не применяются, то необходимо использовать опцию --skip-symlink в mysqld для уверенности, что никто не сможет удалить или переименовать файл вне каталога данных этого потока.
Возможности, которые пока еще не поддерживаются:
ALTER TABLE игнорирует все опции INDEX/DATA DIRECTORY="path".
CREATE TABLE не сообщает, что данная таблица имеет символические ссылки.
mysqldump не включает в вывод информацию о символических ссылках.
BACKUP TABLE и RESTORE TABLE не признают символические ссылки.
Использование символических ссылок
Таблицы и базы данных можно перемещать из каталога баз данных в другие места, заменив их символическими ссылками на новые адреса. Это можно сделать, например, для того, чтобы поместить базу данных в файловую систему с большим количеством свободного места или чтобы увеличить скорость системы путем распространения таблиц на иной диск.
Рекомендуется создавать символические ссылки на другой диск для баз данных, а символические ссылки для таблиц - только в крайних случаях.
Вопросы, относящиеся к диску
Как уже упоминалось ранее, наиболее узким местом для производительности является поиск на диске. Эта проблема становится все более и более очевидной по мере того, как объем данных увеличивается настолько, что эффективное кэширование становится невозможным. Для крупных баз данных, где доступ к данным осуществляется более или менее случайным образом, можно с уверенностью сказать, что потребуется по меньшей мере один поиск по диску для чтения и пара поисков по диску для записи некоторой информации. Чтобы свести эту проблему к минимуму, следует использовать диски с малыми временами поиска.
Можно увеличить количество доступных дисковых "блоков" (и, таким образом, уменьшить нагрузку на диски). Это делается либо путем установления символических ссылок на разные диски, либо использованием RAID (stripe).
Использование символических ссылок
Это означает, что создаются символические ссылки индекса и/или файла/файлов данных из обычного каталога данных на иной диск (для которого, помимо этого, можно использовать RAID (stripe)). Применение символических ссылок улучшает как время поиска, так и время чтения (если эти диски не используются для других операций). See section 5.6.1 Использование символических ссылок.
Использование RAID (stripe)
Использование RAID (stripe) подразумевает, что при наличии нескольких дисков первый блок данных помещается на первом диске, второй блок - на втором диске, N-ный блок на диске с номером, равным остатку от целочисленного деления количества блоков N на число дисков, и т.д. При этом подразумевается, что если нормальный размер данных меньше, чем размер RAID-блока (или в точности равен ему), то производительность будет намного лучше. Следует учитывать, что процесс разделения дисков на RAID-блоки в значительной степени зависит от операционной системы и размера RAID-блока. Поэтому тесты производительности конкретного приложения необходимо производить для разных размеров RAID-блока (see section 5.1.5 Использование собственных тестов).
Следует также учитывать, что разница в скорости при разделении дисков на RAID-блоки сильно зависит от заданных параметров. В зависимости от того, как установлены параметры разделения на RAID-блоки и каково количество дисков, можно получить величины с разницей в несколько порядков. Следует помнить, что необходимо выбрать оптимизацию отдельно для случайного и отдельно для последовательного доступа.
Для надежности можно использовать режим RAID 0+1 (разделение на RAID-блоки + зеркальное отображение), но в этом случае будет необходимо 2*N дисководов для хранения информации N дисководов данных. Если возможности позволяют, то такой вариант выбора - наилучший! Однако при этом могут понадобиться также инвестиции и в программное обеспечение для эффективного управления этим объемом оборудования.
Существует еще одна неплохая возможность: хранить не слишком важные данные (которые могут быть воспроизведены) на диске RAID 0, а действительно важные данные (такие как информация о хостах и журналы) - на диске RAID 0+1 или диске RAID N. Использование RAID N может оказаться проблемой, если у вас много операций записи, потому что обновление битов четности занимает время.
Можно также задать требуемые параметры для используемой базой данных файловой системы. Легко поддается изменению монтирование файловой системы с опцией noatime. Использование этой опции позволяет пропускать обновление при последнем обращении в данном режиме и тем самым избежать поиска по диску.
Под Linux можно получить намного большую производительность (под нагрузкой нередко вплоть до 100%), используя hdpram для конфигурации интерфейса диска! Приведенные ниже опции для hdparm зарекомендовали себя как очень полезные для MySQL (и, возможно, для многих других приложений):
hdparm -m 16 -d 1
Следует учитывать, что производительность/надежность при использовании приведенных выше рекомендаций зависит от конкретного оборудования, так что мы настоятельно рекомендуем вам основательно протестировать систему после использования hdparm! Для получения более подробной информации о применении hdparm, обращайтесь, пожалуйста, к соответствующей странице руководства! Некомпетентное применение hdparm может привести к разрушению файловой системы. Прежде чем экспериментировать, сделайте полную резервную копию!
Во многих операционных системах можно монтировать диски с флагом async - для того, чтобы данная файловая система могла обновляться асинхронно. Если ваш компьютер достаточно стабилен, это должно обеспечить повышение производительности без слишком большой потери надежности (под Linux этот флаг включен по умолчанию).
Если нет необходимости знать, к какому файлу было последнее обращение (что реально не используется на сервере баз данных), можно смонтировать файловые системы с флагом noatime.