Оптимизация запросов к базе данных mySQL. Оптимизация MySQL запросов

Для управления границей элемента применяется универсальное свойство border . Это свойство позволяет установить в одном объявлении толщину, стиль и цвет границы элемента.

Эти три свойства (толщину, стиль и цвет границы) можно установить в одном объявлении. Вот пример:

Можно указать стиль границы только на одной стороны элемента. Для этого применяют свойства border-top (верхняя граница), border-right (правая граница), border-bottom (нижняя граница), border-left (левая граница).

В этом примере для каждой стороны блока задана своя толщина границы, стиль и цвет.

Подумайте, как при помощи CSS можно создать такую фигуру:

Значения границы - толщину, стиль и цвет - можно задать отдельно используя специальные свойства.

• border-style - стиль границы.
• border-width - ширина границы.
• border-color - цвет границы.

Рассмотрим каждое из значений по отдельности.

Свойство border-style. Стиль границы.

Свойство border-style устанавливает стиль рамки. В CSS, в отличии HTML, граница элемента может быть не только сплошной. Допустимы следующие значения для стиля границы:

1. none - граница отсутствует (по умолчанию).
2. solid - сплошная граница.
3. double - двойная граница.
4. dashed - пунктирная граница.
5. dotted - граница из ряда точек.
6. ridge - граница "гребень".
7. groove - граница "бороздка".
8. inset - вдавленная граница.
9. outset - выдавленная граница.

Примеры того, как они выглядят.

граница отсутствует (none)

сплошная граница (solid)

двойная граница (double)

граница из ряда точек (dotted)

пунктирная граница (dashed)

граница "бороздка" (groove)

граница "гребень" (ridge)

вдавленная граница (inset)

выдавленная граница (outset)

Кстати, если для рамки ridge задать цвет границы чёрный, то получится вот такой результат.

Блок div с рамкой чёрного цвета и стиля ridge.

Рамка выглядит как solid , но это потому что стиль ridge создаётся добавлением чёрного эффекта тени, а чёрный эффект на чёрной рамке не виден.

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

Одно значение (solid) - стиль границы установлен для всех сторон блока.

Два значения (solid double) - первое значение установило стиль для верхней и нижней сторон, второе для боковой.

Три значения (solid double dotted) - первое значение для верхней стороны, второе для боковых, стретье для нижней.

Четыре значения (solid double dotted dashed) - каждое значение для одной стороны по часовой стрелке начиная с верхней.

Свойство border-width. Толщина границы.

Для установки толщины границы элемента используется свойство border-width . Толщину границы можно задать в любых абсолютных единицах измерения, например в пикселах.

Как и для свойства border-style , для свойства также можно задавать от одного до четырёх значений. Рассмотрим примеры для каждого случая.

Код примера:

Также для свойства border-width существуют значения в виде ключевых слов. Всего их три:

• thin - тонкая граница;
• medium - средняя толщина;
• thick - толстая граница;

Толщина границы: thin.

Толщина границы: medium.

Толщина границы: thick.

Свойство border-color. Цвет границы.

Для управления цветом границы используется средство border-color . Цвета для этого свойства можно задать используя любой способ, описанный в статье "Цвета в CSS ", а именно:

• Шестнадцатеричная запись (#ff00aa ) цвета.
• Формат RGB - rgb(255,12,110) . Формат RGBA для CSS3.
• Форматы HSL и HSLA для CSS3.
• Название цвета, например black (чёрный). Полный список названий цветов приведён в таблице названий цветов CSS .

Свойство border-color также может иметь от одного до четырёх значений и обрабатывает их аналогично предыдущим свойствам.

Одно значение (red).

Два значения (red black).

Три значения (red black yellow).

Четыре значения (red black yellow blue).

Теперь вернёмся к задаче, озвученной выше, и нарисуем фигуру:

Вот код, который рисует такую фигуру, только побольше размером:

Установка значений для сторон отдельно

Выше упоминалось, что можно указать значения свойств границы только для одной стороны блока. Для этих целей есть свойства:

• border-top (верхняя граница)
• border-right (правая граница)
• border-bottom (нижняя граница)
• border-left (левая граница)

Напомню, для всех свойств указывается три значения (толщина, стиль и цвет) в любом порядке. Но существуют свойства, которые позволяют устанавливать толщину, цвет и стиль для каждой стороны отдельно. Написание этих свойст производно от вышеуказанных.

Параметры верхней границы (border-top ).

• border-top-color - задаёт цвет верхней границы элемента.
• border-top-width - задаёт толщину верхней границы элемента.
• border-top-style - задаёт стиль верхней границы элемента.

Параметры правой границы (border-right ).

• border-right-color - задаёт цвет правой границы элемента.
• border-right-width - задаёт толщину правой границы элемента.
• border-right-style - задаёт стиль правой границы элемента.

Параметры нижней границы (border-bottom ).

• border-bottom-color - задаёт цвет нижней границы элемента.
• border-bottom-width - задаёт толщину нижней границы элемента.
• border-bottom-style - задаёт стиль нижней границы элемента.

Параметры левой границы (border-left ).

• border-left-color - задаёт цвет левой границы элемента.
• border-left-width - задаёт толщину левой границы элемента.
• border-left-style - задаёт стиль левой границы элемента.

Пример использования этих свойств:

Свойство border-radius. Округление углов границы.

Свойство border-radius предназначено для округления углов границ элемента. Это свойство появилось в CSS3 и корректно работает во всех современных браузерах, за исключением Internet Explorer 8 (и более старых версий).

Значениями могут быть любые числа, используемые в CSS.

Свойство border-radius: 15px.

Если рамка блока не задана, то скругление происходит с фоном. Вот пример закругления блока без рамки, но с фоновым цветом:

Свойство border-radius: 15px.

Существуют свойства для скругленя каждого отдельно угла элемента. В этом примере использованы они все:

Border-top-left-radius: 15px; border-top-right-radius: 0; border-bottom-right-radius: 15px; border-bottom-left-radius: 0;

Свойство border-radius: 15px.

Хотя этот код можно записать одним объявлением: border-radius : 15px 0 15px 0 . Дело в том, что для свойства border-radius можно задавать от одного до четырёх значений. В таблице ниже приведены правила, которые определяют такие объявления.

Внимательно изучив эту таблицу можно понять, что самая короткая запись нужного стиля будет такой: border-radius : 15px 0 . Всего два значения.

Немного практики

Рисуем лимон средствами CSS.

Вот код такого блока:

Margin: 0 auto; /* Размещаем блок по центру */ width: 200px; height: 200px; background: #F5F240; border: 1px solid #F0D900; border-radius: 10px 150px 30px 150px;

Мы уже рисовали фигуру:

Теперь оставим от неё треугольник:

Код треугольника такой:

Margin: 0 auto; /* Размещаем блок по центру */ padding: 0px; width: 0px; height: 0; border: 30px solid white; border-bottom-color: red;

В повседневной работе приходится сталкиваться с довольно однотипными ошибками при написании запросов.

В этой статье хотелось бы привести примеры того, как НЕ надо писать запросы.

• Выборка всех полей
  SELECT * FROM table

  При написании запросов не используйте выборку всех полей - "*". Перечислите только те поля, которые вам действительно нужны. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вам на самом деле необходимы все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочки невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, со временем количество столбцов в вашей таблице может изменяться, и если сегодня это пять INT столбцов, то через месяц могут добавиться TEXT и BLOB поля, которые будут замедлять выборку.

• Запросы в цикле.
  Нужно четко представлять себе, что SQL - язык, оперирующий множествами. Порой программистам, привыкшим думать терминами процедурных языков, трудно перестроить мышление на язык множеств. Это можно сделать довольно просто, взяв на вооружение простое правило - «никогда не выполнять запросы в цикле». Примеры того, как это можно сделать:

  1. Выборки
  $news_ids = get_list("SELECT news_id FROM today_news ");
  while($news_id = get_next($news_ids))
  $news = get_row("SELECT title, body FROM news WHERE news_id = ". $news_id);

  Правило очень простое - чем меньше запросов, тем лучше (хотя из этого, как и из любого правила, есть исключения). Не забывайте про конструкцию IN(). Приведенный код можно написать одним запросом:
  SELECT title, body FROM today_news INNER JOIN news USING(news_id)

  2. Вставки
  $log = parse_log();
  while($record = next($log))
  query("INSERT INTO logs SET value = ". $log["value"]);

  Гораздо более эффективно склеить и выполнить один запрос:
  INSERT INTO logs (value) VALUES (...), (...)

  3. Обновления
  Иногда бывает нужно обновить несколько строк в одной таблице. Если обновляемое значение одинаковое, то все просто:
  UPDATE news SET title="test" WHERE id IN (1, 2, 3).

  Если изменяемое значение для каждой записи разное, то это можно сделать таким запросом:
  UPDATE news SET
  title = CASE
  WHEN news_id = 1 THEN "aa"
  WHEN news_id = 2 THEN "bb" END
  WHERE news_id IN (1, 2)

  Наши тесты показывают, что такой запрос выполняется в 2-3 раза быстрее, чем несколько отдельных запросов.

• Выполнение операций над проиндексированными полями
  SELECT user_id FROM users WHERE blogs_count * 2 = $value

  В таком запросе индекс использоваться не будет, даже если столбец blogs_count проиндексирован. Для того, чтобы индекс использовался, над проиндексированным полем в запросе не должно выполняться преобразований. Для подобных запросов выносите функции преобразования в другую часть:
  SELECT user_id FROM users WHERE blogs_count = $value / 2;

  Аналогичный пример:
  SELECT user_id FROM users WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(registered) <= 10;

  Не будет использовать индекс по полю registered, тогда как
  SELECT user_id FROM users WHERE registered >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
  будет.

• Выборка строк только для подсчета их количества
  $result = mysql_query(«SELECT * FROM table», $link);
  $num_rows = mysql_num_rows($result);
  Если вам нужно выбрать количество строк, удовлетворяющих определенному условию, используйте запрос SELECT COUNT(*) FROM table, а не выбирайте все строки лишь для того, чтобы подсчитать их количество.
• Выборка лишних строк
  $result = mysql_query(«SELECT * FROM table1», $link);
  while($row = mysql_fetch_assoc($result) && $i < 20) {
  …
  }
  Если вам нужны только n строк выборки, используйте LIMIT, вместо того, чтобы отбрасывать лишние строки в приложении.
• Использование ORDER BY RAND()
  SELECT * FROM table ORDER BY RAND() LIMIT 1;

  Если в таблице больше, чем 4-5 тысяч строк, то ORDER BY RAND() будет работать очень медленно. Гораздо более эффективно будет выполнить два запроса:

  Если в таблице auto_increment"ный первичный ключ и нет пропусков:
  $rnd = rand(1, query("SELECT MAX(id) FROM table"));
  $row = query("SELECT * FROM table WHERE id = ".$rnd);

  Либо:
  $cnt = query("SELECT COUNT(*) FROM table");
  $row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
  что, однако, так же может быть медленным при очень большом количестве строк в таблице.

• Использование большого количества JOIN"ов
  SELECT
  v.video_id
  a.name,
  g.genre
  FROM
  videos AS v
  LEFT JOIN
  link_actors_videos AS la ON la.video_id = v.video_id
  LEFT JOIN
  actors AS a ON a.actor_id = la.actor_id
  LEFT JOIN
  link_genre_video AS lg ON lg.video_id = v.video_id
  LEFT JOIN
  genres AS g ON g.genre_id = lg.genre_id

  Нужно помнить, что при связи таблиц один-ко многим количество строк в выборке будет расти при каждом очередном JOIN"е. Для подобных случаев более быстрым бывает разбить подобный запрос на несколько простых.

• Использование LIMIT
  SELECT… FROM table LIMIT $start, $per_page

  Многие думают, что подобный запрос вернет $per_page записей (обычно 10-20) и поэтому сработает быстро. Он и сработает быстро для нескольких первых страниц. Но если количество записей велико, и нужно выполнить запрос SELECT… FROM table LIMIT 1000000, 1000020, то для выполнения такого запроса MySQL сначала выберет 1000020 записей, отбросит первый миллион и вернет 20. Это может быть совсем не быстро. Тривиальных путей решения проблемы нет. Многие просто ограничивают количество доступных страниц разумным числом. Также можно ускорить подобные запросы использованием покрывающих индексов или сторонних решений (например sphinx).

• Неиспользование ON DUPLICATE KEY UPDATE
  $row = query("SELECT * FROM table WHERE id=1");

  If($row)
  query("UPDATE table SET column = column + 1 WHERE id=1")
  else
  query("INSERT INTO table SET column = 1, id=1");

  Подобную конструкцию можно заменить одним запросом, при условии наличия первичного или уникального ключа по полю id:
  INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1

Читайте

От автора: один мой знакомый решил оптимизировать свой автомобиль. Сначала одно колесо снял, потому крышу спилил, затем мотор… В общем, сейчас он пешком ходит. Это все последствия неправильного подхода! Поэтому, чтобы ваша СУБД продолжала «ездить», оптимизация MySQL должна проходить правильно.

Когда оптимизировать и зачем?

Лишний раз лезть в настройки сервера и изменять значения параметров (особенно, если не знаете, чем это может закончиться) не стоит. Если рассматривать данную тему с «колокольни» улучшения производительности веб-ресурсов, то она настолько обширная, что ей нужно посвящать целое научное издание в 7 томах.

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

Увеличить скорость выполнения запросов.

Повысить общую производительность сервера.

Уменьшить время ожидания загрузки страниц ресурса.

Снизить потребление серверных мощностей хостинга.

Снизить объем занимаемого дискового пространства.

Постараемся всю тематику оптимизации разбить на несколько пунктов, чтоб было более-менее понятно, от чего «котелок» закипает .

Зачем настраивать сервер

В MySQL оптимизацию производительности следует начинать с сервера. Прежде всего, следует ускорить его работу и уменьшить время обработки запросов. Универсальным средством для достижения всех перечисленных целей является включения кэширования. Не знаете, «what is it»? Сейчас все поясню.

Если на вашем экземпляре сервера включено кэширование, то система MySQL автоматически «запоминает» введенный пользователем запрос. И в следующий раз при его повторении данный результат запроса (на выборку) будет не обработан, а взят из памяти системы. Получается, что таким образом сервер «экономит» время на выдачу ответа, и вследствие чего скорость реагирования сайта повышается. В том числе это касается и общей скорости загрузки.

В MySQL оптимизация запросов применима к тем движкам и CMS, которые работают на основе данной СУБД и PHP. При этом код, написанный на языке программирования, для генерации динамической веб-страницы запрашивает некоторые ее структурные части и содержимое (записи, архивы и другие таксономии) из БД.

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

Включаем и настраиваем кэширование

Но давайте вернемся от «скучной» теории к интересной практике. Дальнейшую оптимизацию базы MySQL продолжим с проверки состояния кэширования на вашем сервере БД. Для этого с помощью специального запроса мы выведем значения всех системных переменных:

Совсем другое дело.

Сделаем маленький обзор полученных значений, которые пригодятся нам для оптимизации баз данных MySQL:

have_query_cache – значение показывает «ВКЛ» кэширование запросов или нет.

query_cache_type – отображает активный тип кэша. Нам нужно значение «ON». Это говорит о том, что кэширование включено для всех видов выборки (команда SELECT). Кроме тех, в которых используется параметр SQL_NO_CACHE (запрещает сохранение информации об этом запросе).

У нас все настройки заданы правильно.

Отмеряем кэш под индексы и ключи

Теперь нужно проверить, сколько отведено оперативной памяти под индексы и ключи. Рекомендуется устанавливать этот важный для оптимизации БД MySQL параметр на 20-30% от объема оперативки, доступной для сервера. Например, если под экземпляр СУБД выделено 4 «гектара», то смело ставьте 32 «метра». Но все зависит от особенностей определенной базы и ее структуры (типов) таблиц.

Для установки значения параметра нужно отредактировать содержимое конфигурационного файла my.ini, который в Денвере находится по следующему пути: F:\Webserver\usr\local\mysql-5.5

Файл открываем с помощью Блокнота. Затем находим в нем параметр key_buffer_size и устанавливаем оптимальный для вашей системы ПК (в зависимости от «гектаров» оперативки) размер. После этого нужно перезапустить сервер БД.

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

Измеряем уровень индексов

Использование индексов в таблицах значительно повышает скорость обработки и формирования ответа СУБД на введенный запрос. MySQL постоянно «измеряет» уровень применения индексов и ключей в каждой БД. Для получения данного значения используйте запрос:

SHOW STATUS LIKE "handler_read%"

SHOW STATUS LIKE "handler_read%"

В полученном результате нас интересует значение в строке Handler_read_key. Если указанное там число маленькое, то это говорит о том, что индексы почти не используются в данной базе. А это плохо (как у нас ).

→ Оптимизация запросов MySQL

MySQL располагает большим набором функций для различных сортировок (ORDER BY ), группировок (GROUP BY ), объединений (LEFT JOIN или RIGHT JOIN ) и так далее. Все они безусловно удобны, но в условиях одноразовых запросов. К примеру, если лично Вам требуется что-то откопать в базе используя кучу таблиц и связок, то кроме вышеперечисленных функций можно и даже нужно применять условный операторы IF . Главная ошибка начинающих программистов это стремление применить такие запросы в рабочем коде сайта. В данном случае сложный запрос безусловно красив, но вреден. Все дело в том, что любые операторы сортировок, группировок, объединений или вложенных запросов, не могут выполняться в оперативной памяти, и используют жесткий диск для создания временных таблиц. А хард, как известно - самое узкое место сервера.

Правила оптимизации mysql запросов

1. Избегайте вложенных запросов

Это самая серьезная ошибка. Родительский процесс всегда будет ждать завершения дочернего и в это время держать коннект к базе, использовать диск и нагружать iowait. Два параллельных запроса в базу и выполнения нужных фильтраций в серверном интерпретаторе (Perl , PHP и т. д.), выполнятся на порядок быстрее чем вложенный.

Примеры на perl , как делать не следует:

My $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)"); $sth->execute(); while (my @row = $sth->fetchrow_array()) { my $groupNAME = $dbh->selectrow_array("SELECT groupNAME FROM groups WHERE groupID = $row"); ### Допустим нужно собрать названия групп ### и добавить их в конец массива с данными push @row => $groupNAME; ### Делаем еще что-нибудь... }

или не в коем случае вот так:

My $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(SELECT groupID FROM groups WHERE groupNAME = "Первая" OR groupNAME = "Вторая" OR groupNAME = "Седьмая")");

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

Пример на perl, как делаю обычно я:

My %groups; my $sth = $dbh->prepare("SELECT groupID,groupNAME FROM groups WHERE groupID IN(2,3,7)"); $sth->execute(); while (my @row = $sth->fetchrow_array()) { $groups{$row} = $row; } ### А теперь выполням основную выборку без вложенного запроса my $sth2 = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)"); $sth2->execute(); while (my @row = $sth2->fetchrow_array()) { push @row => $groups{$row}; ### Делаем еще что-нибудь... }

2. Не сортируйте, не группируйте и не фильтруйте в базе

По возможности не применяйте в своих запросах операторы ORDER BY, GROUP BY, JOIN. Все они используют временные таблицы. Если сортировка или группировка необходима только для вывода элементов, например по алфавиту, лучше выполнить эти действия в переменных интерпретатора.

Примеры на perl, как сортировать не следует:

My $sth = $dbh->prepare("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7) ORDER BY elementNAME"); $sth->execute(); while (my @row = $sth->fetchrow_array()) { print qq{$row => $row}; }

Пример на perl, как сортирую обычно я:

My $list = $dbh->selectall_arrayref("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7)"); foreach (sort { $a-> cmp $b-> } @$list){ print qq{$_-> => $_->}; }

Так намного быстрее. Особенно заметна разница если данных много. В случае, если нужно отсортировать в perl по нескольким полям, можно применить сортировку Шварца . Если требуется произвольная сортировка ORDER BY RAND() - используйте сортировку random в perl .

3. Используйте индексы

Если от сортировки в базе можно отказаться в некоторых случаях, то от WHERE навряд ли удастся. Поэтому, для полей, по которым будет идти сравнение, необходимо устанавливать индексы. Делаются они просто.

Таким запросом:

ALTER TABLE `any_db`.`any_tbl` ADD INDEX `text_index`(`text_fld`(255));

Где 255 - длина ключа. Для некоторых типов данных он не требуется. Подробности в документации к MySQL.

Управление индексами, то есть как они создаются и поддерживаются — может значительно повлиять на производительность sql запросов.

Очень часто можно применить следующие оптимизации:

• удалить неиспользуемые индексы
• определить неиспользуемые вообще и неэффективные индексы
• улучшить индексы
• избегать вообще sql запросов!
• упрощать sql запросы
• и магия варианты кеширования

Объединение DDL запросов

Запросы, меняющие структуру данных как правило являются блокирующими таблицу. Исторически, выполнение запроса ALTER требовало создания новой копии таблицы, что может быть очень затратным по времени и по объему данных на диске. Поэтому вместо трех запросов с маленькими альтерами намного выгоднее выполнять один объединенный. Это может сэкономить значительное количество времени на задачах по администрированию баз.

Удаление дублирующихся индексов

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

Несколько простых условий могут привести к дублированию индексов. Например, mysql не нужен индекс на полях PRIMARY.

Дублирующий индекс также может существовать, если левая часть одного из индексов полностью совпадает с другим индексом.

Утилита pt-duplicate-key-checker из perkona-toolkit — это простой и быстрый способ проверить свою структуру базы на наличие лишних индексов.

Удаление неиспользуемых индексов

Кроме индексов, которые не используются никогда, поскольку являются дублями, могут быть недублирующиеся индексы, которые просто никогда не используются. Такие индексы влияют также, как и дублирующиеся индексы. В стандартном mysql нет никаких способов определить какие индексы неиспользуются, однако в некоторых версиях есть подобная возможность, например при использовании Google MySQL patch.

В этом патче была введена фишка: SHOW INDEX_STATISTICS.

А в обычном mysql сначала необходимо собрать все используемые sql запросы, прогонять их и смотреть план выполнения, собирать при этом информацию о используемых в каждом случае индексах и сводить это в единую таблицу. В любом случае, это полезный опыт.

Оптимизация индексных полей.

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

Типы данных

Некоторые типы могут быть заменены безболезненно на текущей существующих базах.

BIGINT vs INT

Когда PRIMARY ключ определён как BIGINT AUTO INCREMENT — как правило нет никаких причин использовать именно его. Тип данных INT UNSIGNED AUTO_INCREMENT может хранить максимум числа до 4294967295. Если у вас реально будет больше записей чем это число, вам скорее всего понадобится другая архитектура.

От такого изменения с BIGINT на INT UNSIGNED каждая строка таблицы начинает занимать в 2 раза меньше места на диске, кроме того с 8 байт до 4 снижается размер, занимаемый PRIMARY ключом.

Это пожалуй одно из самых ощутимых простых улучшений, которые можно делать достаточно безболезненно.

DATETIME vs TIMESTAMP

Тут все просто: timestamp — 4 байта, datetime — 8 байт.

По возможности надо использовать, потому что:

• дополнительная проверка целостности данных
• такое поле будет использовать всего 1 байт для хранения 255 уникальных значений
• такие поля удобнее читать:)

Исторически, использование enum полей приводило к зависимости базы от изменений возможных значений в enum. Это был блокирующий DDL запрос. Начиная с версии MySQL 5.1 добавление новых вариантов к enum очень быстрое и не связано с размером таблицы.

NULL vs NOT NULL

Если вы не уверены, что колонка может содержать неопределенное значение (NULL), лучше определять ее как NOT NULL. Индекс на такой колонке будет меньше по размеру и будет легче обрабатываться.

Автоматичесие конвертации типов

Когда вы выбираете тип данных для джойнящихся полей, бывает, что тип данных в поле неопределен. Встроенная конверсия может быть абсолютно лишним оверхедом.

Для целочисленных полей, убедитесь что SIGNED и UNSIGNED совпадают, для переменных типов полей, лишней работой может быть конвертация кодировки при джоине, поэтому их тоже обязательно проверять. Частая проблема это автоконвертация между кодировками latin1 и utf8.

Типы колонок

Некоторые типы данных часто хранятся в неправильных колонках. Изменение типа при этом может привести к более эффективному хранению, особенно когда эти колонки включаются в индекс. Рассмотрим несколько типичных примеров.

IP адрес

IPv4 адрес может храниться в поле INT UNSIGNED, которое займет всего 4 байта. Часто встречается ситуация, когда ip адрес хранят в поле VARCHAR(15), которое занимает 12 байт. Одно это изменение может сократить размер на 2/3. Функции INET_ATON() и INET_NTOA служат для конвертации между строкой с ip адресом и числовым значением.

Для IPv6 адресов, которые все сильнее наступают, важно хранить их 128битное цифровое значение в полях BINARY(16) и не использовать VARCHAR для человекочитаемого формата.

Хранение md5 полей как CHAR(32) является повсеместной практикой. Если вы используете поле VARCHAR(32) вы еще дополнительно добавляете лишний оверхед длины строки для каждого значения. Однако md5 строка — это шестнадцатиричное значение — и его можно хранить эффективнее используя функции UNHEX() и HEX(). В этом случае данные можно хранить в полях BINARY(16). Такое простое действие снизит размер поля с 32 байт до 16 байт. Подобный принцип можно применять к любым шестнадцатиричным значениям.

Основано на книге Рональда Брэдфорда.