Опубликован: 16.04.2007 | Доступ: свободный | Студентов: 5320 / 857 | Оценка: 4.18 / 4.08 | Длительность: 16:03:00
Лекция 6:

Виды таблиц и способ их хранения

< Лекция 5 || Лекция 6: 1234 || Лекция 7 >

Блокировки таблиц

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

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

Можно заблокировать таблицу таким образом, чтобы разрешить другим потокам обращаться к ней для чтения. Это называется блокировкой чтения. Блокировка записи гарантирует текущему потоку монопольный доступ к таблице. Запросы на чтение откладываются до тех пор, пока не будут сняты все блокировки записи. Эту установку можно изменить с помощью флагов инструкций либо путем задания специальных серверных переменных. Для SQL-инструкций создаются две очереди. Чтобы программа MySQL начала извлекать инструкции из очереди на чтение, очередь на запись должна быть пуста. При наличии флага LOW_PRIORITY инструкции DELETE, INSERT и UPDATE помещаются в очередь на чтение, т.е. они получают такой же приоритет, что и инструкции SELECT. Флаг HIGH_PRIORITY переводит инструкцию SELECT в очередь на запись.

Индексы

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

Определения индексов хранятся в frm-файле а сами индексируемые значения — в файле с расширением .MYI. Если индексный файл отсутствует на момент запуска сервера, он будет автоматически воссоздан. Таким образом, при создании резервных копий можно не заботиться об индексах в целях экономии места. Позднее, в процессе восстановления базы данных, программа MySQL создаст индексы заново на основании схемы таблицы.

Индексы способны повысить производительность инструкций, связанных с поиском записей. Они ускоряют процесс сравнения столбцов при выполнении операций объединения. Кроме того, они помогают находить минимальное и максимальное значения столбца и ускоряют выполнение инструкций SELECT с предложением ORDER BY.

Чтобы индекс был задействован, он должен быть указан во всех частях предложения WHERE. Если используется лишь часть индекса, то должен соблюдаться порядок обращения к индексируемым столбцам: слева направо. Для примера рассмотрим таблицу, определение которой приведено в листинге 6.2.

CREATE TABLE car (
Make CHAR(32) NOT NULL,
Model CHAR(32) NOT NULL,
Introduced YEAR,
PRIMARY KEY (MAKE,Model)
);
Листинг 6.2.

У таблицы car имеется составной первичный ключ. В запросе, который показан в листинге 6.3, индекс будет использован, так как столбец Make является самым левым компонентом индекса.

SELECT * FROM car WHERE Make='Ford';
Листинг 6.3.

А вот в следующем запросе (листинг 6.4) этого не произойдет, поскольку правило очередности столбцов не соблюдается.

SELECT * FROM car WHERE Model='Pinto';
Листинг 6.4.

В листинге 6.5 индекс также не используется, из-за того, что самый левый компонент индекса нельзя применить к каждой записи. Если бы в предложении WHERE стоял оператор AND, а не OR, все было бы наоборот.

SELECT * FROM car WHERE Make='Ford' OR Model='Impala';
Листинг 6.5.

Следующий запрос (листинг 6.6) является правильным с точки зрения использования индекса. В данном случае просмотр значений столбца осуществляется слева направо.

SELECT * FROM car WHERE Make LIKE 'F%';
Листинг 6.6.

В листинге 6.7 индекс не используется, потому что просмотр значений столбца осуществляется справа налево (метасимвол % стоит вначале).

SELECT * FROM car WHERE Make LIKE '%d';
Листинг 6.7.

Дескрипторы файлов

Сервер MySQL представляет собой один процесс со множеством потоков. Для каждого сеанса подключения к серверу создается свой поток. Каждому потоку требуется один или несколько дескрипторов файлов, чтобы он мог осуществлять чтение и запись таблиц. Операционная система ограничивает количество файловых дескрипторов, доступных процессу. Это число может быть самым разным. Например, в AIX оно равно 2000 по умолчанию, а в Solaris — всего лишь 64. В Linux лимит по умолчанию составляет 1024 дескриптора. В Windows NT и 2000 видимый предел отсутствует.

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

Размер кэша дескрипторов можно задать другим, но не забывайте об ограничении, которое накладывается операционной системой. Правда, ее собственный лимит тоже можно изменить. Для этого существует, например, команда unlimit. Еще один способ — перекомпиляция ядра.

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

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

Системная память

В MySQL специальные буферы и кэши применяются для самых разных целей. Их размеры можно задавать в конфигурационном файле или в командной строке запуска сервера. У каждого потока есть свой стек, буфер приема входных данных от клиента и буфер результатов запроса. Размер стека задается серверной переменной thread_stack, а размеры обоих буферов — переменной net_buffer_length. Последняя определяет начальные размеры буферов, так как они могут увеличиваться в случае необходимости, например, при обработке столбцов типа BLOB или TEXT.

Все потоки совместно используют индексный буфер. Его размер определяется переменной key_buffer_size. В операциях объединения, проходящих без участия индексных столбцов, используется отдельный буфер (переменная join_buffer_size ), как и в операциях сканирования таблиц (переменная record_buffer ).

Если для выполнения операции объединения требуется временная таблица, она создается как резидентная (тип Heap ). Максимальный размер таких таблиц определяется переменной tmp_table_size. После превышения этого предела таблица преобразуется в формат MyISAM. В любом случае временные таблицы удаляются по окончании операции.

< Лекция 5 || Лекция 6: 1234 || Лекция 7 >
Александра Каева
Александра Каева
Дмитрий Черепенин
Дмитрий Черепенин

Какого года данный курс?