Оптимизация MySQL запроса (заполняет весь mysql-slow.log). Оптимизация mysql запроса

optimization - оптимизация запроса mysql

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

SELECT CASE WHEN t.i = 1 THEN t.dnode WHEN t.i = 2 AND t.dnode = c.fromnode THEN c.tonode WHEN t.i = 2 AND t.dnode = c.tonode THEN c.fromnode ELSE NULL END AS node FROM ( SELECT d.i , m.root , CASE WHEN m.root = n.fromnode THEN n.tonode ELSE n.fromnode END AS dnode FROM (SELECT 'node1' AS root) m CROSS JOIN (SELECT 1 AS i UNION ALL SELECT 2) d LEFT JOIN conn n ON m.root IN (n.fromnode,n.tonode) ) t LEFT JOIN conn c ON t.i = 2 AND t.dnode IN (c.fromnode,c.tonode) GROUP BY node ORDER BY node

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

(SELECT 'node1' AS root) m

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

CROSS JOIN (SELECT 1 AS i UNION ALL SELECT 2) d

При том, что я готов присоединиться к conn столу, и я хочу все строки, которые имеют либо Fromnode или значение tonode, соответствующий корневой узел.

LEFT JOIN conn n ON m.root IN (n.fromnode,n.tonode)

С этим набором результатов я хочу "перевернуть" fromnode и tonode на некоторых из этих строк, чтобы у нас в основном всегда был "корневой" узел с одной стороны. Я делаю это с помощью выражения CASE, которое проверяет, какая сторона соответствует корню:

CASE WHEN m.root = n.fromnode THEN n.tonode ELSE n.fromnode END AS dnode

Поэтому теперь я обертываю этот набор результатов как встроенный просмотр с псевдонимом t. Этот подзапрос можно запускать отдельно, чтобы увидеть, что мы возвращаем то, что ожидаем:

SELECT d.i , m.root , CASE WHEN m.root = n.fromnode THEN n.tonode ELSE n.fromnode END AS dnode FROM (SELECT 'node1' AS root) m CROSS JOIN (SELECT 1 AS i UNION ALL SELECT 2) d LEFT JOIN conn n ON m.root IN (n.fromnode,n.tonode)

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

LEFT JOIN conn c ON t.i = 2 AND t.dnode IN (c.fromnode,c.tonode)

И снова, мне все равно, какой боковый узел включен, на данный момент мне просто нужно сделать матч.

На этом этапе вы можете запустить весь запрос и вытащить t.*, c.* Чтобы посмотреть, что у нас есть, но я собираюсь пропустить эту часть и перейти прямо к "магии".

На этом этапе мы можем использовать выражение CASE для "выделения" нужного значения узла из этого беспорядка.

Если значение уровня (к сожалению, обозначено i) равно 1, то мы смотрим на первый уровень, поэтому нам просто нужно получить значение "nodeX", которое было на "другой" стороне "root". Это доступно из источника t качестве выражения, псевдонимого как dnode.

В противном случае мы рассмотрим строки для "второго" уровня, i = 2. (В этом конкретном случае тест на i = 2 можно опустить, но он включает в себя слух для полноты, и на всякий случай мы собираемся расширить этот подход, чтобы получить три уровня (gasp!) Или более (oh my!).

Здесь нам нужно знать, какая сторона (от или до) соответствует первому уровню, и мы просто тянем другую сторону. Если t.dnode совпадает со стороной, мы вытаскиваем другую сторону.

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

Поскольку нам все равно, на каком уровне они были, мы опускаем возвращающийся ti, который дал бы нам уровень.

РЕЗЮМЕ

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

qaru.site

Оптимизация MySQL запроса (заполняет весь mysql-slow.log)

FlyingRaven Весь /var/log/mysql/mysql-slow.log забит одним и тем-же запросом:

SELECT m_news.id, caption, annotation, theme, title, type FROM m_news INNER JOIN m_sections ON (m_sections.name = m_news.theme) WHERE attr IN (0, 2, 4, 6) ORDER BY dt DESC LIMIT 0, 12;

Структура таблицы m_news: Структура таблицы m_sections:

Из-за чего такое может происходить и как оптимизировать?

Дополнено (1). Извините, временно перебросили на другой проект - к этой проблеме вернусь на следующей неделе.Большое спасибо принимающим участие в решении.

da-eto Для начала создайте индексы на полях m_sections.name и m_news.theme. Потом посмотрите вывод explain для вашего селекта (EXPLAIN SELECT…).

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

WwWork Сделайте LOCK table перед выполнением запроса + проверьте таблицы все на ошибки.--ещё вариант если есть доступ к конфигурации mysql увеличте параметры переменных для хранения и обработки данных.--Общие рекомендации по повышению производительности.

1. Запускать mysqld с правильно подобранными опциями (. Настройка переменных ).2. Для ускорения SELECT запросов построить индексы для тех полей, которые участвуют в условии WHERE.3. Оптимизировать типы полей. По возможности использовать NOT NULL. (. Работу с таблицами ).4. В MySQL применяется два способа блокировки таблиц (lock table) - внутренняя и внешняя блокировки. Внутренняя блокировка позволяет делать операции по изменению / данных атомарными ( конфликтующими с другими пользователями ). Внешняя блокировка применяется для одновременного доступа нескольких MySQL серверов к одним и тем же базам данных, а также внешняя блокировка позволяет запускать isamchk без остановки MySQL. Чтобы запретить использование внешней блокировки нужно запускать mysqld с опцией -skip-locking. Запрет внешней блокировки существенно повысит скорость работы, но при этом перед запуском isamchk нужно предварительно сбросить все данные на диск командой mysqladmin flush-tables. Также при запрете внешней блокировки нельзя будет использовать несколько серверов для работы с теми же базами данных. Задание прав доступа на конкретную таблицу или поле снижает производительность. --http://www.citforum.ru/database/articles/mysql.shtml

mysqlru.com

Оптимизация работы MySQL: кеширование запросов

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

После установки MySQL уже поддерживает механизм кеширования запросов, однако по умолчанию он выключен. Параметры по умолчанию следующие:

mysql> show variables like 'query%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +------------------------------+---------+ 7 rows in set (0.00 sec)

Размер кеша равен нулю. Для того, чтобы задать кеш размером 32 МБ, необходимо добавить следующую строку в my.conf (в секцию [mysqld]):

Чтобы не перезапускать MySQL-сервер, изменим также и текущую конфигурацию, выполнив следующий запрос с правами суперпользователя:

mysql> set @ [at] global [dot] query_cache_size=32*1024*1024;Query OK, 0 rows affected (0.00 sec)

Еще один параметр, на который следует обратить внимание - это query_cache_limit - он задает максимальный объем результата выполнения запроса, который может быть помещен в кэш.

Проверить состояние кеша можно следующим запросом:

mysql> show global status like 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 21 | | Qcache_free_memory | 30943456 | | Qcache_hits | 3659 | | Qcache_inserts | 2409 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 75 | | Qcache_queries_in_cache | 841 | | Qcache_total_blocks | 1863 | +-------------------------+----------+ 8 rows in set (0.00 sec)

Значение параметров:

Qcache_free_memory - объем свободной памяти, отведенной под кэш.
Qcache_hits - количество запросов, отработанных из кэша.
Qcache_inserts - количество вставок запросов в кэш.
Qcache_lowmem_prunes - количество высвобождений памяти из-за наполненности кэша.

Qcache_not_cached - количество запросов, не подлежащих кэшированию.
Qcache_queries_in_cache - количество запросов, находящихся в кэше в настоящее время.

Рассчитать эфективность кеширования можно по следующей формуле: Qcache_hits / (Qcache_inserts + Qcache_not_cached).

Собственно пора задаться вопросом, как именно работает кеш. Все очень просто. При каждом запросе типа SELECT вычисляется хэш-сумма строки запроса и ищется в кэше. Если находится - возвращается рузельтат из кэша, если нет - выполняется запрос, а результат заносится в кэш (при условии, что результат не больше значения query_cache_limit).При каждом запросе типа UPDATE, REPLACE, INSERT, DELETE, TRUNCATE или ALTER, удаляются из кэша все запросы, использующие таблицу, подвергшуюся обновлению.

Стоит также отметить некоторые особенности кеширования, а именно:

Различие запросов определяется буквально, сравнение чувствительно к реестру. Поэтому SELECT * FROM news и select * FROM news будут для кэша двумя разными запросами.
В кэш всегда попадает результат выполнения запроса целиком, результаты выполнения подзапросов не кэшируются.

Кэш работает одинаково для запросов к таблицам с различными механизмами хранения.
Ряд запросов не подлежит кэшированию:
- Запросы, содержащие одну из недетерминированных функций: NOW(), SLEEP(), RAND(), CURTIME(), LAST_INSERT_ID() и.др.
- Запросы, использующие функции или хранимые процедуры, определенные пользователем.
- Запросы, использующие значения локальных переменных.
- Запросы, обращающиеся к базам данных mysql или INFORMATION_SCHEMA.
- Запросы типа SELECT ... FOR UPDATE, SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE, SELECT ... INTO DUMPFILE, SELECT * FROM ... WHERE autoincrement_col IS NULL.
- Запросы, использующие временные таблицы.
- Запросы, не обращающиеся к таблицам.
- Запросы, которые генерируют предупреждения (warnings).
- В случае, если пользователь имеет права не на всю таблицу, а только на определенные колонки таблицы. Это исключение — следствие того, что кэш запросов один для всех пользователей, а права доступа средствами кэша проверяются лишь на уровне таблиц.

Если необходимо, чтобы запрос не попадал в кеш, используется директива SQL_NO_CACHE, которая размещается сразу после оператора SELECT. Пример выполнения такого запроса:

mysql> SELECT SQL_NO_CACHE username FROM mail_users;

www.muff.kiev.ua

: MySQL :: Базы данных :: Справочник

SHOW STATUS

FLUSH STATUS — данная каманда сбросит(обнулит) все сесионные переменные, глобальные переменные не будут затронуты.

SELECT bla-bla-bla FROM test_table as tt WHERE tt.bla = 'test' LIMIT 10

mysql> SHOW STATUS LIKE 'Key_read%';+--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | Key_read_requests | 96882 | | Key_reads | 36200 | +--------------------+----------+

Что может дать подобная информация? И какие решения принимать, основываясь на этой информации?

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

Key_read_requests — Количество запросов на чтение индексных блоков из кеша.

Key_reads — Количество физического чтения индексных блоков с диска. Если число Key_reads большое, тогда, вероятно, ваше key_buffer_size достаточно мало и требует увеличения. Отношение непопаданий в кэш можно посчитать как Key_reads/Key_read_requests.

mysql> SHOW STATUS LIKE 'select_%';

+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 2 | +------------------------+-------+

Select_scan — The number of joins that did a full scan of the first table.

Последнее поле показывает, что MySQL делал FULL TABLE SCAN, на самом деле эту инфоррмацию подтверждает EXPLAIN, поле Extra, которого содержит Using temporary; Using filesort. Если у нас в запросе фигурирует несколько таблиц, которые склеиваются во время запроса, то и другие значения в этом списке могут быть не нулевыми.

Опять же привиду описание данных полей из мануала.

Sort_rows — The number of sorted rows.
Sort_scan — The number of sorts that were done by scanning the table.

Также полезно знать создавались ли временные(temporary) таблицы, которые использовались во время выполнения запроса на диске или в памяти.

Здесь MySQL показывает, что использовались инмемори tables, только почему-то показывает, что было их создано 3 :-) В действительности для данного запроса создается всего одна.

PROFILING

С версии 5.0.37 MySQL включает возможность профайлинга запросов. Данная утилита записывает статистику выполнения запросов в служебную БД information_schema

Для того, чтобы MySQL начал записывать статистику по запросу следует установить значение переменное profiling в 1

mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec)

После этого выполняем интересующие нас SQL запрос(ы).

Далее выполняем следующий запрос

mysql> show profiles; +----------+------------+-----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------+ | 0 | 0.00005100 | set profiling=1 | | 1 | 0.80246730 | SELECT SQL_NO_CACHE fe.username, COUNT(*) as `count` FROM `tx_images` as ti INNER JOIN `fe_users` as fe ON ti.cruser_id = fe.uid GROUP BY fe.uid ORDER BY `count` desc | +----------+------------+-----------------------------------------------+ 2 rows in set (0.00 sec)

Как я говорил ранее, данная статистика записывается в БД — information_schema, таблицу — profiling, поэтому мы можем получить статистику по выполнению запроса сделав запрос к этой таблице.

mysql> select sum(duration) from information_schema.profiling where query_id=1; +---------------+ | sum(duration) | +---------------+ | 0.80246730 | +---------------+ 1 row in set (0.00 sec)

Далее можно посмотреть статистику всех стадий выполнения запроса, делается это с помощью команды

mysql> show profile for query 1; +--------------------+------------+ | Status | Duration | +--------------------+------------+ | (initialization) | 0.00007300 | | Opening tables | 0.00005100 | | System lock | 0.00000600 | | Table lock | 0.00002000 | | init | 0.00002200 | | optimizing | 0.00003400 | | statistics | 0.00010600 | | preparing | 0.00014800 | | executing | 0.50000700 | | Sending data | 0.30226800 | | end | 0.00000700 | | query end | 0.00000500 | | freeing items | 0.00001300 | | closing tables | 0.00000700 | | logging slow query | 0.00000400 | +--------------------+------------+ 15 rows in set (0.00 sec)

Также с помощью профайлинга можно смотреть статистику не только по SELECT запросам. Вы можете просматривать статистику даже по запросам, которые изменяют структуру таблиц ALTER TABLE и изменяют/удаляют данные. Соответственно и стадии выполнения у этих запросов будут отличаться.

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

ref.ysite.org

mysql - Оптимизация запроса mysql. Избегайте создания временной таблицы?

Это запрос, который я использую для таблиц: products, reviews, replies, review_images.

Запрос:

SELECT products.id, reviews.*, GROUP_CONCAT(DISTINCT CONCAT_WS('~',replies.reply, replies.time)) AS Replies, GROUP_CONCAT(DISTINCT CONCAT_WS('~',review_images.image_title, review_images.image_location)) AS ReviewImages FROM products LEFT JOIN reviews on products.id = reviews.product_id LEFT JOIN replies on reviews.id = replies.review_id LEFT JOIN review_images on reviews.id = review_images.review_id WHERE products.id = 1 GROUP BY products.id, reviews.id;

Схема:

Продукты :

id | name | product_details....

Отзывы:

Ответов:

Обзор изображений:

id | review_id | image_title | image_location | ...

Индексы:

Продукты :

ПЕРВИЧНЫЙ КЛЮЧ - id

Отзывы:

ПЕРВИЧНЫЙ КЛЮЧ - id

FOREIGN KEY - product_id (таблица продуктов ID IN)

FOREIGN KEY - имя пользователя (имя пользователя в таблице пользователей)

Ответов:

ПЕРВИЧНЫЙ КЛЮЧ - id

FOREIGN KEY - review_id (таблица отзывов ID)

FOREIGN KEY - имя пользователя (имя пользователя в таблице пользователей)

Обзор изображений:

ПЕРВИЧНЫЙ КЛЮЧ - id

FOREIGN KEY - review_id (таблица отзывов ID)

Объяснить запрос:

1 | ПРОСТОЙ | отзывы | ВСЕ | product_id | 4 | Использование где; Использование буфера соединения (Block Nested Loop)

1 | ПРОСТОЙ | ответы | ref | review_id | 1 | Ноль

1 | ПРОСТОЙ | review_images | ВСЕ | review_id | 5 | Использование где; Использование буфера соединения (Block Nested Loop)

Я не знаю, что здесь не так, что ему нужно использовать filesort и создать временную таблицу?

Вот несколько результатов профилирования:

Таблицы открытия 140 мкс

Инициировать 139 мкс

Блокировка системы 34 мкс

Оптимизация 21 мкс

Статистика 106 мкс

Получение 146 мкс

Создание таблицы Tmp 13.6 мс

Результат сортировки 27 мкс

Выполнение 11 мкс

Отправка данных 11,6 мс

Создание индекса сортировки 1,4 мс

Конец 89 мкс

Удаление таблицы Tmp 8.9 мс

Конец 34 мкс

Окончание запроса 25 мкс

Таблицы закрытия 66 мкс

Освобождающие предметы 41 мкс

Удаление таблицы Tmp 1.4 мс

Свободные предметы 46 мкс

Удаление таблицы Tmp 1.2 мс

Освобождающие предметы 203 мкс

Очистка 55 мкс

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

Помощь была бы оценена. Заранее спасибо.

РЕДАКТИРОВАТЬ

Создание таблиц

CREATE TABLE 'products' ( 'id' int(11) NOT NULL AUTO_INCREMENT, 'name' varchar(100) NOT NULL, 'description' varchar(100) NOT NULL, 'items' int(11) NOT NULL, 'price' int(11) NOT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB CREATE TABLE 'reviews' ( 'id' int(11) NOT NULL AUTO_INCREMENT, 'username' varchar(30) NOT NULL, 'product_id' int(11) NOT NULL, 'review' text NOT NULL, 'time' datetime NOT NULL, 'ratings' int(11) NOT NULL, PRIMARY KEY ('id'), KEY 'product_id' ('product_id'), KEY 'username' ('username') ) ENGINE=InnoDB CREATE TABLE 'replies' ( 'id' int(11) NOT NULL AUTO_INCREMENT, 'review_id' int(11) NOT NULL, 'username' varchar(30) NOT NULL, 'reply' text NOT NULL, 'time' datetime NOT NULL, PRIMARY KEY ('id'), KEY 'review_id' ('review_id') ) ENGINE=InnoDB CREATE TABLE 'review_images' ( 'id' int(11) NOT NULL AUTO_INCREMENT, 'review_id' int(11) NOT NULL, 'image_title' text NOT NULL, 'image_location' text NOT NULL, PRIMARY KEY ('id'), KEY 'review_id' ('review_id') ) ENGINE=InnoDB

EDIT:

Я упростил запрос выше, и теперь он не создает временные таблицы. Единственная причина, о которой говорил @Bill Karwin, заключалась в том, что я использовал GROUP BY на второй таблице в объединениях.

Упрощенный запрос:

SELECT reviews. * , GROUP_CONCAT( DISTINCT CONCAT_WS( '~', replies.reply, replies.time ) ) AS Replies, GROUP_CONCAT( DISTINCT CONCAT_WS( '~', review_images.image_title, review_images.image_location ) ) AS ReviewImages FROM reviews LEFT JOIN replies ON reviews.id = replies.review_id LEFT JOIN review_images ON reviews.id = review_images.review_id WHERE reviews.product_id = 1 GROUP BY reviews.id

Теперь ПРОБЛЕМА, с которой я сталкиваюсь:

Поскольку я использую GROUP_CONCAT, существует ограничение на данные, которые он может содержать, который находится в переменной GROUP_CONCAT_MAX_LEN, так как я объединяю ответы, заданные пользователями, это может быть очень длинным и может превышать определенную память, Я знаю, что могу изменить значение GROUP_CONCAT_MAX_LEN для текущего сеанса, но все же есть ограничение на это, что в какой-то момент времени запрос может выйти из строя или не сможет получить полные результаты.

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

ВОЗМОЖНОЕ РЕШЕНИЕ :

Просто используя LEFT JOINS, который создает повторяющиеся строки для каждого нового результата в последнем столбце и который затрудняет перемещение в php? Какие-либо предложения?

Я вижу, что этот вопрос не получает достаточного ответа от членов SO. Но я искал решение и искал понятия с последней до прошлой недели. Еще не повезло. Надеюсь, некоторые из вас могут помочь мне. Заранее спасибо.

qaru.site