ПРОГРАММ: 654 | СТАТЕЙ: 125 | КОДОВ: 3434   

    главная        новости        программы        библиотека        коды        рейтинг       форум   

Афоризм
Назвался груздем - поминай как звали.
 
Поиск


 
Рассылка
| Soft-Ежедневник - обзор программ прямо к Вам на e-mail
 
 
Полезное!
 
7 лучших
  Фильм "Инвалиды России", прошлое и настоящее
  69 секретов
  Виртуальный cвинг клуб Израиля
  История Качи. Док. фильм
  More then varez...
  
  
 
  Библиотека > web - разработка > MySQLдобавить статью
Оптимизация запросов в MySQL

Повышаем быстродействие своих скриптов
Оптимизация запросов в MySQL 
Соколов Сергей
phpclub.ru
08-10-2004
Версия для печати
печать

Оптимизация – это изменение системы с целью повышения ее быстродействия.
Оптимизацию работы с БД можно разделить на 3 типа:

  • оптимизация запросов
  • оптимизация структуры
  • оптимизация сервера.

Рассмотрим подробнее оптимизацию запросов.

Оптимизация запросов - наиболее простой и приводящий к наиболее высоким результатам тип оптимизации.

SELECT

Запросами, которые чаще всего поддаются оптимизации, являются запросы на выборку.

Для того чтобы посмотреть как будет выполняться запрос на выборку используется оператор EXPLAIN:
http://www.mysql.com/doc/ru/EXPLAIN.html
С его помощью мы можем посмотреть, в каком порядке будут связываться таблицы и какие индексы при этом будут использоваться.

Основная ошибка начинающих - это отсутствие индексов на нужных полях или создание оных на ненужных полях. Если вы делаете простую выборку наподобие:
SELECT * FROM table WHERE field1 = 123
То вам нужно проставить индекс на поле field1, если вы используете в выборке условие по двум полям:
SELECT * FROM table WHERE field1 = 123 AND field2 = 234
То вам нужно создать составной индекс на поля field1, field2.

Если вы используете соединение 2 или более таблиц:
SELECT *
FROM a, b
WHERE a.b_id = b.id

Или в более общем виде:
SELECT *
FROM a
[LEFT] JOIN b ON b.id = a.b_id
[LEFT] JOIN с ON с.id = b.c_id

То вам следует создать индексы по полям, по которым будут присоединятся таблицы. В данном случае это поля b.id и c.id. Однако это утверждение верно только в том случае, если выборка будет происходить в том порядке, в котором они перечислены в запросе. Если, к примеру, оптимизатор MySQL будет выбирать записи из таблиц в следующем порядке: c,b,a, то нужно будет проставить индексы по полям: b.c_id и a.b_id. При связывании с помощью LEFT JOIN таблица, которая идет в запросе слева, всегда будет просматриваться первой.

Про синтаксис создания индексов можно прочитать в документации:
http://www.mysql.com/doc/ru/CREATE_INDEX.html

Более подробно про использовании индексов можно прочитать здесь:
http://www.mysql.com/doc/ru/MySQL_indexes.html

Иногда бывает такая ситуация, что нам постоянно приходится делать выборки из одной и той же части некоторой очень большой таблицы, например, во многих запросах происходит соединение с частью таблицы:
[LEFT] JOIN b ON b.id = a.b_id AND b.field1 = 123 AND b.field2 = 234

В таких случаях может быть разумным вынести эту часть в отдельную временную таблицу:
CREATE TEMPORARY TABLE tmp_b TYPE=HEAP SELECT * FROM b WHERE b.field1 = 123 AND b.field2 = 234
И работать уже с ней ( про временные таблицы читайте в документации http://www.mysql.com/doc/ru/CREATE_TABLE.html).

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

Также бывают тормоза, когда люди пытаются в одном запросе «поймать сразу 2-х зайцев», например, на форуме phpclub’а автор следующего запроса спрашивал, почему он тормозит:

SELECT f_m. *, MAX( f_m_v_w.date ) AS last_visited, COUNT( DISTINCT f_b.id ) AS books_num,
IF ( f_m.region != 999, f_r.name, f_m.region_other ) AS region_name
FROM fair_members f_m
LEFT JOIN fair_members_visits_week f_m_v_w ON f_m_v_w.member_id = f_m.id
LEFT JOIN fair_regions AS f_r ON f_m.region = f_r.id
LEFT JOIN fair_books AS f_b ON f_b.pub_id = f_m.id
GROUP BY f_m.id

Автор запроса пытается в одном запросе посчитать максимальное значение атрибута из одной таблицы и кол-во записей в другой таблице. В результате к запросу приходится присоединять 2 разные таблицы, которые сильно замедляют выборку. Для увеличения быстродействия такой выборки необходимо вынести подсчет MAX’а или COUNT’а в отдельный запрос.

Для подсчета кол-ва строк используйте функцию COUNT(*), c указанием "звездочки" в качестве аргумента.

Почему COUNT(*) обычно быстрее COUNT(id), поясню на примере:

Есть таблица message: id | user_id | text
с индексом PRIMARY(id), INDEX(user_id)

Нам надо подсчитать сообщения пользователя с заданым $user_id

Сравним 2 запроса:

SELECT COUNT(*) FROM message WHERE user_id = $user_id

и

SELECT COUNT(id) FROM message WHERE user_id = $user_id

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

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

В итоге получаем, что при большом кол-ве записей скорость первого запроса будет выше в разы.

UPDATE, INSERT

Скорость вставок и обновлений в базе зависит от размера вставляемой (обновляемой) записи и от времени вставки индексов. Время вставки индексов в свою очередь зависит от количества вставляемых индексов и размера таблицы. Эту зависимость можно выразить формулой:
[Время вставки индексов] = [кол-во индексов] * LOG2( [Размер таблицы] )
При операциях обновления под [кол-во индексов] понимаются только те индексы, в которых присутствуют обновляемые поля.
Условия в запросах на обновления оптимизируются так же, как и в случае с выборками.

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

«Чтобы удалить все строки в таблице, нужно использовать команду TRUNCATE TABLE table_name.» © документация MySQL.

Ответы на многие вопросы по оптимизации запросов можно найти в мануале: http://www.mysql.com/doc/ru/Query_Speed.html




Комментарии
Оставить комментарий
Имя:
E-mail:
Сообщение:


Число=
укажите сумму чисел на картинке

   очистить

популярные статьи по теменовые статьи
Создание видео из фотографий в программе "ФотоШОУ"
Разное / Обработка фото и видео
Больница для компьютера
железо / Система
Чем так популярны социальные сети?
Разное / разное
Синхронизация данных с сотового телефона на ПК
железо / Мобильные устройства
Куплю хостинг
web - разработка / Хостинг

Опрос
На что Вы обращаете внимание в первую очередь при покупке товара в интернет-магазине?  
 
Комментарии и оценку пользователей
Наличие нескольких фотографий
Подробное и качественное описание
Условия доставки
Возможность покупки в кредит
 
Самое свежее  
 ПРОГРАММЫ

ICQ 8.0.5990
ICQ 8.0.5990

Mozilla Firefox 18.0.2
Mozilla Firefox 18.0.2

ArtMoney 7.40.4
ArtMoney 7.40.4

 НОВОСТИ

Хакерская атака на Google из Китая
Хакерская атака на Google из Китая

Мы победили! Олимпиада 2014 пройдет в Сочи
Мы победили! Олимпиада 2014 пройдет в Сочи

 БИБЛИОТЕКА

Создание видео из фотографий в программе "ФотоШОУ"
Создание видео из фотографий в программе "ФотоШОУ"

Больница для компьютера

 
Рекомендую!



Copyright © 2004-2020 MostInfo.net  | связь с админом
При использовании материалов сайта ссылка на MostInfo обязательна.