Основы PHP
  Что такое PHP?
  Возможности PHP
  Преимущества PHP
  История развития
  Что нового в PHP5?
  «Движок» PHP
  Переход на PHP 5.3
New Переход на PHP 5.6
  Введение в PHP
  Изучение PHP
  Основы CGI
  Синтаксис PHP
  Типы данных PHP
  Переменные в PHP
  Константы PHP
  Выражения PHP
  Операторы PHP
  Конструкции PHP
  Ссылки в PHP
  PHP и ООП
  Безопасность
  Функции PHP
  Функции по категориям
  Функции по алфавиту
  Стандартные функции
  Пользовательские
  PHP и HTTP
  Работа с формами
  PHP и Upload
  PHP и Cookies
  PHP и базы данных
  PHP и MySQL
  Документация MySQL
  Учебники
  Учебники по PHP
  Учебники по MySQL
  Другие учебники
  Уроки PHP
  Введение
  Самые основы
  Управление
  Функции
  Документация
  Математика
  Файлы
  Основы SQL
  Дата и время
  CURL
  Изображения
  Стили
  Безопасность
  Установка
  Проектирование БД
  Регулярные выражения
  Подготовка к работе
  Быстрый старт
  Установка PHP
  Установка MySQL
  Конфигурация PHP
  Download / Скачать
  Скачать Apache
  Скачать PHP
  Скачать PECL
  Скачать PEAR
  Скачать MySQL
  Редакторы PHP
  Полезные утилиты
  Документация
  PHP скрипты
  Скачать скрипты
  Инструменты
  PHP в примерах
  Новости портала
 Главная   »  Сборник статей
 
 

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

Автор: Соколов Сергей

Источник: detail.phpclub.net

Оптимизация – это изменение системы с целью повышения ее быстродействия.
Оптимизацию работы с БД можно разделить на 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

 
 » Обсудить эту статью на форуме

 
 Сборник статей 
 Содержание раздела 
Есть еще вопросы или что-то непонятно - добро пожаловать на наш  форум портала PHP.SU 
 

 
Powered by PHP  Powered By MySQL  Powered by Nginx  Valid CSS