колонтитул в word 2007, 0000002111
Использование индексов в MySQL
Индексы применяются для быстрого поиска строк с указанным значением
одного столбца. Без индекса чтение таблицы осуществляется по всей
таблице начиная с первой записи, пока не будут найдены соответствующие
строки. Чем больше таблица, тем больше накладные расходы. Если же
таблица содержит индекс по рассматриваемым столбцам, то MySQL может
быстро определить позицию для поиска в середине файла данных без
просмотра всех данных. Для таблицы, содержащей 1000 строк, это будет как
минимум в 100 раз быстрее по сравнению с последовательным перебором всех
записей. Однако в случае, когда необходим доступ почти ко всем 1000
строкам, быстрее будет последовательное чтение, так как при этом не
требуется операций поиска по диску.
Все индексы MySQL (PRIMARY
, UNIQUE
, и
INDEX
) хранятся в виде B-деревьев. Строки автоматически сжимаются
с удалением пробелов в префиксах и оконечных пробелов.
Индексы используются для того, чтобы:
-
Быстро найти строки, соответствующие выражению 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
, то данный ключ
читается в обратном порядке
-
В некоторых случаях запрос можно оптимизировать для
извлечения величин без обращения к файлу данных. Если все
используемые столбцы в некоторой таблице являются числовыми и
образуют крайний слева префикс для некоторого ключа, то чтобы
обеспечить большую скорость, искомые величины могут быть
извлечены непосредственно из индексного дерева:
SELECT key_part3 FROM table_name WHERE key_part1=1
Предположим, что вызывается следующий оператор SELECT
:
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
Если по столбцам col1
и col2
существует
многостолбцовый индекс, то соответствующие строки могут выбираться
напрямую. В случае, когда по столбцам col1
и col2
существуют раздельные индексы, оптимизатор пытается найти наиболее
ограничивающий индекс путем определения, какой индекс найдет меньше
строк, и использует данный индекс для выборки этих строк.
Если данная таблица имеет многостолбцовый индекс, то любой крайний
слева префикс этого индекса может использоваться оптимизатором для
нахождения строк. Например, если имеется индекс по трем столбцам (col1,col2,col3
),
то существует потенциальная возможность индексированного поиска по (col1
),
(col1,col2
) и (col1,col2,col3
).
В MySQL нельзя использовать частичный индекс, если столбцы не
образуют крайний слева префикс этого индекса. Предположим, что имеются
команды SELECT
, показанные ниже:
mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
Если индекс существует по (col1,col2,col3
), то только
первый показанный выше запрос использует данный индекс. Второй и третий
запросы действительно включают индексированные столбцы, но (col2
)
и (col2,col3
) не являются крайней слева частью префиксов (col1,col2,col3
).
MySQL применяет индексы также для сравнений LIKE
, если
аргумент в выражении LIKE
представляет собой постоянную
строку, не начинающуюся с символа-шаблона. Например, следующие команды
SELECT
используют индексы:
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";
В первой команде рассматриваются только строки с "Patrick" <=
key_col < "Patricl"
, а во второй - только строки с "Pat" <=
key_col < "Pau"
.
Следующие команды SELECT
не будут использовать индексы:
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;
В первой команде величина LIKE
начинается с шаблонного
символа. Во второй команде величина LIKE
не является
константой.
В версии MySQL 4.0 производится другая оптимизация на выражении
LIKE
. Если используется выражение ... LIKE "%string%"
и длина строки (string) больше, чем 3 символа, то MySQL будет применять
алгоритм Турбо Бойера-Мура для инициализации шаблона для строки и затем
использовать этот шаблон, чтобы выполнить поиск быстрее.
При поиске с использованием column_name IS NULL
будут
использоваться индексы, если column_name
является индексом.
MySQL обычно использует тот индекс, который находит наименьшее
количество строк. Индекс применяется для столбцов, которые сравниваются
с помощью следующих операторов: =, >, >=, <, <=, BETWEEN
и
LIKE
с префиксом, не содержащим шаблонного символа, такого
как something%
.
Если индекс не охватывает все уровни AND
в выражении
WHERE
, то он не применяется для оптимизации данного
запроса. Другими словами: чтобы индекс можно было использовать, префикс
этого индекса должен входить в каждую группу AND
.
Следующие выражения WHERE
используют индексы:
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2 /* индекс = 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
.
Максимальное количество ключей и максимальная длина индексов
определяется обработчиком таблиц. Можно иметь по меньшей мере 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
.
Индексация всегда производится для целого столбца, а частичная
индексация не поддерживается.
Многостолбцовые индексы
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
используются индексы для улучшения работы запросов,
Открытие и закрытие таблиц в 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=#
.
Максимальное количество таблиц в кэше открытых таблиц будет равно
количеству, указанному в 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
или сам
поток не уничтожится. Если это произойдет, то данная таблица
помещается обратно в кэш таблиц (если он не заполнен).
Чтобы узнать, не слишком ли мал кэш таблиц, следует проверить
переменную Opened_tables
. Если ее значение достаточно
велико, даже если вы не выполняли слишком часто команду FLUSH
TABLES
, то необходимо увеличить данный кэш таблиц.
колонтитулы в word 2007, 000000211111