Как оптимизировать SQL запросы с целью уменьшения нагрузки на сервер? 15 советов

« Предыдущая запись
 
  Следующая запись »
 

Порыскав на досуге по тырнету, удивился, что специальных статей-руководств по оптимизации SQL-запросов нет. Перелистав различную информацию и книги,  я постараюсь дать некоторое руководство к действию, которое поможет научиться писать правильные запросы. mysql axe Как оптимизировать SQL запросы с целью уменьшения нагрузки на сервер? 15 советов

  1. Оптимизация таблиц. Необходима, когда было произведено много изменений в таблице: либо удалена большая часть данных, либо много изменений со строками переменной длины - text, varchar, blob. Дело в том, что удалённые записи продолжают поддерживаться в индексном файле, и при последующей вставке новых записей используются позиции старых записей. Чтобы дефрагментировать файл с данными, используюется команда OPTIMIZE.
    OPTIMIZE TABLE `table1`, `table2`…

    Не стоит забывать, что во время выполнения оптимизации, доступ к таблице блокируется.

  2. Перестройка данных в таблице. После частых изменений в таблице, данная команда может повысить производительность работы с данными. Она перестраивает их в таблице и сортирует по определённому полю.
    ALTER TABLE `table1` ORDER BY `id`
  3. Тип данных. Лучше не индексировать поля, имеющие строковый тип, особенно поля типа TEXT. Для таблиц, данные которых часто изменяются, желательно избегать использования полей типа VARCHAR и BLOB, так как данный тип создаёт динамическую длину строки, тем самым увеличивая время доступа к данным. При этом советуют использовать поле VARCHAR вместо TEXT, так как с ним работа происходит быстрее.
  4. NOT NULL и поле по умолчанию. Лучше всего помечать поля как NOT NULL, так как они немного экономят место и исключают лишние проверки. При этом стоит задавать значение полей по умолчанию и новые данные вставлять только в том случае, если они от него отличаются. Это ускорит добавление данных и снизит время на анализ таблиц. И стоит помнить, что типы полей BLOB и TEXT не могут содержать значения по умолчанию.
  5. Постоянное соединение с сервером БД. Позволяет избежать потерь времени на повторное соединение. Однако стоит помнить, что у сервера может быть ограничение на количество соединений, и в том случае, если посещаемость сайта очень высокая, то постоянное соединение может сыграть злую шутку.
  6. Разделение данных. Длинные не ключевые поля советуют выделить в отдельную таблицу в том случае, если по исходной таблице происходит постоянная выборка данных и которая часто изменяется. Данный метод позволит сократить размер изменяемой части таблицы, что приведёт к сокращению поиска информации.
    Особенно это актуально в тех случаях, когда часть информации в таблице  предназначена только для чтения, а другая часть - не только для чтения, но и для модификации (не забываем, что при записи информации блокируется вся таблица). Яркий пример - счётчик посещений.
    Есть таблица (имя first) с полями id, content, shows. Первое ключевое с auto_increment, второе - текстовое, а третье числовое - считает количество показов. Каждый раз загружая страницу, к последнему полю прибавляется +1. Отделим последнее поле во вторую таблицу. Итак, первая таблица (first) будет с полями id, content, а вторая (second) с полями shows и first_id. Первое поле понятно, второе думаю тоже - отсыл к ключевому полю id из первой таблицы.
    Теперь постоянные обновления будут происходить во второй таблице. При этом изменять количество посещений лучше не программно, а через запрос:
    UPDATE second SET shows=shows+1 WHERE first_id=нужный_ид
    А выборка будет происходить усложнённым запросом, но одним, двух не нужно:
    SELECT first.id, first.content, second.first_id, second.shows FROM second INNER JOIN first ON (first.id = second.first_id)
    Стоит помнить, что всё это не актуально для сайтов с малой посещаемостью и малым количеством информации.
  7. Имена полей, по которым происходит связывание, к примеру, двух таблиц, желательно, чтобы имели одинаковое название. Тогда одновременное получение информации из разных таблиц через один запрос будет происходить быстрее. Например, из предыдущего пункта желательно, чтобы во второй таблице поле имело имя не first_id, а просто id, аналогично первой таблице. Однако при одинаковом имени становится внешне не очень наглядно что, куда и как. Поэтому совет на любителя.
  8. Требовать меньше данных. При возможности избегать запросов типа:
    SELECT * FROM `table1`
    Запрос не эффективен, так как скорее всего возвращает больше данных, чем необходимо для работы. Вариантом лучше будет конструкция:
    SELECT id, name FROM table1 ORDER BY id LIMIT 25
    Тут же сделаю добавление о желательности использования LIMIT. Данная команда ограничивает количество строк, возвращаемых запросом. То есть запрос становится "легче" и производительнее.
    Если стоит LIMIT 10, то после получения десяти строк запрос прерывается.
    Если в запросе применяется сортировка ORDER BY, то она происходит не по всей таблице, а только по выборке.
    Если использовать LIMIT совместно с DISTINCT, то запрос прервётся после того, как будет найдено указанное количество уникальных строк.
    Если использовать LIMIT 0, то возвращено будет пустое значение (иногда нужно для определения типа поля или просто проверки работы запроса).
  9. Ограничить использование DISTINCT.  Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.
    Есть маленькая хитрость. Если необходимо просмотреть две таблицы на тему соответствия, то приведённая команда остановится сразу же, как только будет найдено первое соответствие.
    SELECT DISTINCT table1.content FROM table1, table2 WHERE table1.content = table2.content
  10. Ограничить использование SELECT для постоянно изменяющихся таблиц.
  11. Не забывайте про временные таблицы типа HEAP. Несмотря на то, что таблица имеет ограничения, в ней удобно хранить промежуточные данные, особенно когда требуется сделать ещё одну выборку из таблицы без повторного обращения. Дело в том, что эта таблица хранится в памяти и поэтому доступ к ней очень быстрый.
  12. Поиск по шаблону. Зависит от размера поля и если уменьшить размер с 400 байтов до 300, то время поиска сократиться на 25%.
  13. Команда LOAD DATA INFILE позволяет быстро загружать большой объём данных из текстового файла.
  14. Хранение изображений в БД нежелательно. Лучше их хранить в папке на сервере, а в базе сохранять полный путь к ним. Дело в том, что веб-сервер лучше кэширует графические файлы, чем содержимое базы, что значит, что при последующем обращении к изображению, оно будет отображаться быстрее.
  15. Максимально число запросов при генерации страницы, как мне думается, должно быть не более 20 (+- 5 запросов). При этом оно не должно зависеть от переменных параметров.

, ,

1 звезда2 звезд3 звезд4 звезд5 звезд (голосов: 6, средний: 3.50 из 5)
Понравилась статья или журнал? Подписывайся на продолжение!
Отзывов: 13 на запись

"Как оптимизировать SQL запросы с целью уменьшения нагрузки на сервер? 15 советов"

  1. Спасибо за пост. Познавательно! :)

  2. Подскажите пожалуйста, как сделать Sql запрос на другой сервер на другом хосте? Очень нужно. Не найти!

  3. Пожалуйста!

    Насколько я знаю, этого нельзя сделать.

  4. Спасибо.
    Буду искать дальше. Если найду, отпишусь!

  5. окей, спасибо!

  6. А вот еще вопросик.

    У меня при регистрации нового пользователя на него создается новая таблица в БД (db2) там хранится пароль и доп. инфа. типа email и т.д.

    Хитрость в том, что имя таблицы генерируется так:
    Его Ник + генерируется код из нескольких символов. (nic_657849910)

    А этот код (657849910) записывается в БД (db1).

    Получается, что когда надо вытащить на юзера инфу, то сначала надо узнать код (657849910) по его Нику, тоесть соединится с БД (db1), а потом уже соединяться с БД (db2).

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

    Так вот суть вопроса такова: Как такая схема влияет на нагрузку на сервер!

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

    Если чесно, то меня давно мучает этот вопрос.

    Спасибо.

  7. Уххх, зачем так усложнять? не проще ли всех пользователей в одной таблице держать было?
    Мне кажется что система с разными БД сама по себе более ёмка. Но может быть в Вашем случае это и незначительно, а проявляться может при бешеных посещениях. Я бы потестил и тот и другой вариант на скорость загрузки.

  8. Суть в том что я не зря спрашивал про удаленный запрос к MySql на другом сервере.

    Я хотел сделать, чтобы на одном серваке хранилась только БД (db1)
    на втором только (db2) с таблицами на юзеров. Причем на этих серваках Будет только MySql сервер. Без PHP и т.д. Дабы дать простор для MySql.

    А на третьем (на основном к которому идут запрсы с браузеров) только PHP дабы дать простор PHP.

    Кстати про Sql запрос на удаленный сервер.

    Юзал юзал в Googl, так ни чего не наюзал.

    Решил обратится в суппорт к хостру. Из начально не стал, по скольку думал, что и так найду. Странно но ни где ни чего нет. Может юзальщик из меня не кудышный.

    Все оказалось проще пареной репы!

    В своей админ части, на хосте открываете доступ к MySql для определенного IP или по диапазону. (Если это доступно на Вашем хосте)
    На пример:
    217.107.34.180 или 217.107.34.*
    Делаешь запрос на этот хост с добавленного в доступ:

    $hostdb = 'ник_на_хосте.jino.ru'; // или ник и IP ник@81.177.139.76
    $userdb = 'Имя пользователя';
    $passdb = 'пароль пользователя';

    $db = mysql_connect($hostdb,$userdb,$passdb);
    if(!$db) exit('No!');
    mysql_query("SET NAMES 'utf8'",$db);
    $result = mysql_select_db('Имя БД',$db);

    Переменная $hostdb может назначаться по разному. Это надо уточнять на своем хосте.

    Вот и все!

    После прчтения Вашего топика, я решил углубиться в познание об оптимизации БД. Нарыл для себя кучу ценной информации и усек некоторые уроки.

    Теперь буду полностью переделывать свой проект!

    Спасибо. :)

  9. Хорошо, что всё разрешилось!
    Спасибо за отзыв! Удачи! =)
    И не забывайте подписываться на RSS ;)

  10. Андрей
    08/12/2011 at 11:46 Постоянная ссылка Цитировать

    Статья хорошая, но ничего нового для себя к сожалению не открыл.

  11. тогда основные вещи вы точно знаете :)

  12. Александр, такой вопрос по 15пункту: к примеру, генерируется личная страница пользователя и его данные хранятся в 12 таблицах(php+mysql). Я считаю количество его записей в каждой таблице и если >0 вывожу это количество на одну страницу пользователя. Т.е. получается, что у меня 12 запросов на страницу и все они зависят от переменной (user). Существует ли какое-то оптимальное решение для этой задачи? Спасибо.

  13. Александр Шуйский
    09/05/2012 at 8:40 Постоянная ссылка Цитировать

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

Добро пожаловать, коллега! Вы можете оставить свой отзыв:





Допустимые XHTML-теги:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Подписка на комментарии