Оптимизация SQL-запросов

В данном разделе будут рассмотрены способы оптимизации SQL-запросов и таблиц базы данных MySQL. Для создания эффективных SQL-запросов, а следовательно, в дальнейшем и эффективного кода с использованием РНР, необходимо хорошо понимать тонкости работы с СУБД MySQL, типы данных и характеристики таблиц, с которыми она позволяет работать. Несмотря на то, что для работы с СУБД используется унифицированный язык запросов SQL, все СУБД поддерживают его с определенными оговорками. Говоря об SQL применительно к какой-либо базе данных, следует иметь в виду, что речь идет о диалекте SQL, поддерживаемом данной СУБД.

Такая ситуация связана с тем, что стандарт языка SQL был принят достаточно поздно, когда большинство современных баз данных уже широко использовались. Для того чтобы обеспечить преемственность старых версий баз данных, поставщикам баз данных приходится проектировать их, сохраняя поддержку оригинальных языков запросов, применявшихся до введения стандарта SQL. Кроме того, СУБД постоянно развиваются, приобретая с каждой новой версией новые особенности и операторы для работы с данными.

Как любая реляционная СУБД, MySQL оперирует таблицами, состоящими из одного или более столбцов и некоторого значения строк (записей), которое может быть и нулевым. Тип данных определяется столбцами таблицы.

Типы данных, применяемые в таблицах MySQL, условно можно поделить на три группы: числовые данные, строковые данные и календарные данные. Особняком среди них стоит так называемое "пустое значение" — NULL, не являющееся типом и обозначающее отсутствие каких бы то ни было данных.

Числовые данные.

Числовые данные делятся на целочисленные и вещественные. Основные типы числовых данных, занимаемый ими объем памяти и характеристики приведены в табл. 1.

Таблица 1. Числовые типы

Как видно из табл.1. СУБД MySQL имеет пять целых типов: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Различие между ними заключается в диапазоне величин, которые можно хранить в столбцах такого типа. Чем больше диапазон значений у типа данных, тем больше памяти для него требуется. Тип INTимеет синоним INTEGER. Целые типы данных могут быть объявлены как положительные. Для этого после объявления типа следует разместить ключевое слово UNSIGNED. В этом случае элементам данного столбца нельзя будет присвоить отрицательные значения.

При объявлении целого типа задается длина отображения М (от 1 до 255). Данный параметр определяет количество символов, которые будут выводиться при отображении значений столбца. Параметр М определяет только количество цифр, которое будет выводиться при запросе в клиентах типа MySQL, и не влияет на размер памяти, отводимый для хранения значения.

Для представления вещественных типов в СУБД MySQL имеются три типа: FLOAT, DOUBLE И DECIMAL.

ТипDOUBLE имеет два синонима: PRECISION и REAL. ТИПDECIMAL имеет синоним NUMERIC.

Диапазон вещественных чисел, помимо максимального значения, имеет минимальное значение, которое характеризует точность данного типа. Параметр М в табл. 1 задает число символов для отображения всего числа, а

D — для его дробной части. При выборе столбцов для формирования структуры таблицы необходимо обращать внимание на размер, занимаемый тем или иным типом данных: если значения, размещаемые в базе данных, никогда не будут выходить за пределы 100, не следует выбирать тип больше TINYINT. ЕСЛИ же в полях столбца предполагается хранить только целочисленные данные, то применение атрибута UNSIGNED ПОЗВОЛИТ увеличить диапазон в два раза.

Строковые данные.

Строковые типы данных, максимальный размер и требования к памяти приведены в табл.2. В таблице L — это длина хранимой в ячейке строки, а байты, приплюсованные к L, — накладные расходы для хранения длины строки.

Таблица 2. Строковые типы.

Тип CHAR позволяет хранить строку фиксированной длины М, его дополняет тип VARCHAR, позволяющий хранить переменные строки длиной L. Значение М может принимать значения от 1 до 255. При выборе строкового типа данных для столбца следует принимать во внимание, что для переменных строк VARCHAR требуется только число байт, равное длине строки плюс один байт, а тип CHAR(M) независимо от длины строки использует для ее хранения все М байт. В то же время тип CHAR обрабатывается эффективнее переменных типов. При создании таблицы нельзя смешивать столбцы типа CHAR и VARCHAR. Если такое происходит, СУБД MySQL изменяет тип столбцов согласно правилу: если в таблице присутствует хоть один столбец переменной длины, все столбцы типа CHAR приводятся к типу VARCHAR. Типы BLOB и TEXT в СУБД MySQL во всем аналогичны и отличаются только в деталях. Например, при выполнении операций над столбцами типа TEXT учитывается кодировка, а над столбцами типа BLOB — нет. Тип TEXT обычно используется для хранения больших объемов текста, в то время как BLOB — для больших двоичных объектов, таких как электронные документы, изображения, звуки и т. д.

К особым типам данных относятся ENUM и SET. Строки этих типов принимают значения из заранее заданного списка допустимых значений. Основное различие между ними заключается в том, что значение типа ENUM должно содержать точно одно значение из указанного множества, столбцы SET могут содержать любой (или все) элемент заранее заданного множества одновременно. Так, значения для столбца, объявленного как ENUM (‘ у ’ , ' n ') , могут принимать только два значения: либо ‘ у ’, либо ' n ' . Данный тип удобно использовать для хранения значений переключателей из HTML-формы. Для типа SET, как и для типа ENUM, при объявлении задается список возможных значений, но ячейка может принимать любое значение из списка, пустая строка означает, что ни один из элементов списка не выбран.

Этот тип данных удобно использовать при сохранении выбранных посетителем в HTML-форме флажков. Типы ENUM и SET можно назвать строковыми лишь отчасти, так как при объявлении они задаются списком строк. Но во внутреннем представлении базы данных элементы множества сохраняются в виде чисел. Элементы типа ENUM нумеруются последовательно начиная с 1. В зависимости от числа элементов в списке под столбец может отводиться 1 байт (до 256 элементов в списке) или 2 байта (от 257 до 65 536 элементов в списке).

Элементы множества SET обрабатываются как биты, размер типа при этом также определяется числом элементов в списке: 1 байт (от 1 до 8 элементов), 2 байта (от 9 до 16 элементов), 3 байта (от 17 до 24 элементов), 4 байта (от 25 до 32 элементов) и 8 байт (от 33 до 64 элементов).