Оптимизация запросов mysql. Удаляем ревизии постов

С 10 октября у владельцев андроидов появился выбор – бесплатный голосовой ассистент. Теперь вместо «О’кей гугл», можно сказать «OK, Алиса» и услышать в ответ приветствие, от нового голосового помощника Яндекс. За последний год – это самое впечатляющее событие компании.

В действительности, приложение Алиса от Яндекс, в отличии от Сири, работает не только на android, версия которого не ниже, чем 4.3, но и на других ОС. Дело в том, что именно для устройств, функционирующих на ОС андроид, по умолчанию, для пользователей сети интернет, установлен google. Соответственно, все пользователи вынуждены общаться с помощью голосовых команд именно с ним. Разработчики Яндекс предложили отличную альтернативу, можно скачать голосового ассистента от Яндекса.

Основные различия между голосовыми помощниками Yandex и Google

Программа от яндекс имеет огромный потенциал к развитию, за счет ее основы – нейронной сети. Голосовой ассистент Гугл также подключен к нейросети. Но Alice создана для России и российских пользователей, а Гугл ассистент только адаптирован. Ассистент алиса яндекс андроид умеет шутить на русском языке, а голосовой помощник от Гугл серьезен и все вопросы распознает только как запрос к поисковой системе. Чтобы оценить ассистента, можно его скачать через Google Play. Есть еще одно приложение на российском рынке – «Дуся», также использует русский язык, но устанавливается только платно. Делая сравнение между Алисой и прочими аналогами, даже на старте, приложение интересней.

Основные функции голосового помощника Yandex

Программа на телефон для андроид и iOS alice имеет одну отличительную особенность, она не «мыслит» и не «говорит» шаблонами, с ней можно просто разговаривать по русский, что отличает ее от аналога – Eva free. Запустить приложение просто, нужно нажать в приложении яндекс на значок микрофона и произнести приветствие, одно из которых – «ОК, яндекс» или – «привет, Алиса». После активации приложения, ответит хороший женский голос, который принадлежит Татьяне Шитовой.

Удобно пользоваться алиса яндекс помощник android тем, кто априори использует на телефоне или планшете yandex для поиска и его многочисленные функции. Именно Алиса полностью синхронизирована со всеми службами этой поисковой системы и легко отыщет нужный адрес и телефонный номер, а также другую информацию.

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

Если есть настроение смотреть видео или слушать музыку, можно оповестить об этом alice, она выберет на свой вкус треки в приложении яндекс. Чаще всего – это Queen или Status QUO, такие у нее предпочтения. Можно задать и определенный стиль. По словам разработчиков, Алиса может анализировать предпочтения на основании ранее выбранных и включать их в зависимости от времени суток.

Примеры запросов и команд

Найти нужное заведение – где посмотреть кино в Новосибирске?
Узнать ответ на вопрос – Численность населения в Токио?
Проложить маршрут и определить геолокацию – Доехать до Адлера?
Узнать загруженность на дорогах – Есть ли пробки в Москве?

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

В отличии от яблочной Siri, «Алиса» имеет собственный характер и маневрирует голосом. Может, как и любая девушка, обидится и не реагировать, когда ее будет звать пользователь, какое-то время. Голосовое сопровождение можно отключить или включить, вызвав меню программы и установив флажок в нужное положение.

Что еще может Alice на android?

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

Список функций:

Ответ на вопрос без помощи браузера;
Активация некоторых программ на андройде;
Ответ на вопрос о дате и времени;
Погода;
Курс валют;
Запуск страницы по запросу/открытие запроса в поиске;
Определение местонахождения на карте;
Вычислительные операции без задействования сторонних приложений;
Поддержка беседы.

Алиса находится только на старте своей карьеры, если можно так сказать, а значит только начинает «познавать мир». В рейтинге приложений alice в верхних строчках ТОП. Со временем приложение дополнится функциями, будет работать с большим количеством ОС и устройств. Чем больше пользователей захотят скачать и начать общаться с Алисой, тем скорее она восполнит свой словарный запас, а беседы будут не просто полезными, но и интересными.

Виртуальные голосовые помощники призваны упростить наше взаимодействие с устройствами и сервисами. Вместо того чтобы блуждать по графическому интерфейсу в поисках нужного пункта меню, вы можете просто скомандовать естественным языком «Включи музыку» или «Подскажи прогноз погоды». Идеальный помощник должен правильно понять команду и выполнить её.

Технологии, которые лежат в основе таких ассистентов, пока далеки от совершенства, но уже способны впечатлять. Вы могли наблюдать их в действии, если пользовались помощниками Google Assistant, Cortana или Siri. Теперь давайте посмотрим, чем нас может порадовать « », которая недавно поселилась в приложении «Яндекс».

Синтез и распознавание речи

Хотя в голосе помощника можно без труда расслышать искусственные нотки, он звучит на порядок естественнее своего ближайшего конкурента - русскоязычной версии Siri. Для озвучивания «Алисы» привлекли актрису Татьяну Шитову. К слову, именно её голосом говорила операционная система в фильме «Она».

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

Но промахи в интерпретации запросов пока встречаются у всех голосовых ассистентов, и «Алиса» здесь не исключение:

Интеграция со службами «Яндекса»

Другая важная особенность «Алисы», на которой делают акцент создатели, это удобная интеграция с остальными сервисами «Яндекса».

К примеру, попросите ассистента включить песню, и она заиграет в «Яндекс.Музыке». Запросите перевод фразы на другой язык - помощник откроет «Яндекс.Переводчик»:

«Алиса» также умеет отображать прогноз погоды и строить маршруты благодаря метеосервису и картам «Яндекса». А если вам понадобится найти что-либо в Сети, поможет «Яндекс.Поиск».

Взаимодействие со сторонними программами

Что касается интеграции со сторонними приложениями и службами на мобильных устройствах, то тут у «Алисы» не всё так хорошо.

Ассистент можно установить на Android и iOS, но пока «Алиса» слабо использует возможности этих платформ. Так, с её помощью вы даже не сможете быстро завести будильник, добавить напоминание или заметку. А вот Siri легко справится с этими задачами.

Хотя «Алиса» умеет открывать по запросу установленные на устройстве программы, эта функция срабатывает не всегда. Например, ассистент без проблем запускает «ВКонтакте» и Telegram, но если попросить его открыть Viber, то вместо приложения помощник направит вас на сайт этого мессенджера. На команду «открыть калькулятор» ассистент реагирует странно.

Кроме того, чтобы обратиться к «Алисе», нужно сначала войти в приложение «Яндекс» и нажать на кнопку помощника (или воспользоваться ярлыком для быстрого доступа, если у вас Android). Это не очень удобно, ведь одна из главных задач голосового ассистента - позволить вам управлять устройством без рук. Та же Siri за счёт глубокой интеграции с iOS может принимать команды, даже когда экран заблокирован.

«Алиса» на компьютере

Ассистент «Яндекса» также доступен для компьютеров под управлением Windows в виде отдельной программы. После её установки на панели задач появляется поисковая строка и кнопка для голосового взаимодействия с помощником.

Помимо функций, представленных в мобильной версии, «Алиса» для Windows умеет искать файлы на жёстком диске, запускать настольные программы, выключать компьютер или переводить его в спящий режим.

9 октября 2008 в 23:37

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

MySQL

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

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

Выборка всех полей
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

Читайте

Работа с базой данных зачастую самое слабое место в производительности многих web приложений. И об этом должны заботиться не только администраторы баз данных. Программисты должны выбирать правильную структуру таблиц, писать оптимизированные запросы и хороший код. Далее перечислены методы оптимизации работы с MySQL для программистов.

1. Оптимизируйте запросы для кэша запросов

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

// запрос не будет кэширован $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()" ); // а так будет! $today = date("Y-m-d" ); $r = mysql_query("SELECT username FROM user WHERE signup_date >= "$today"" );

Причина в том, что в первом запросе используется функция CURDATE(). Это относиться ко всем функциям, подобным NOW(), RAND() и другим, результат которых недетерминирован. Если результат функции может измениться, то MySQL не кэширует такой запрос. В данном примере это можно предотвратить вычислением даты до выполнения запроса.

2. Используйте EXPLAIN для ваших запросов SELECT

// создаем a prepared statement if ($stmt = $mysqli ->prepare("SELECT username FROM user WHERE state=?" )) { // привязываем значения $stmt ->bind_param("s" , $state ); // выполняем $stmt ->execute(); // привязываем результат $stmt ->bind_result($username ); // получаем данные $stmt ->fetch(); printf("%s is from %s\n" , $username , $state ); $stmt ->close(); }

13. Небуферизованные запросы

Обычно, делая запрос, скрипт останавливается и ждет результата его выполнения. Вы можете изменить это, используя небуферизованные запросы.
Хорошее описание есть в документации функции mysql_unbuffered_query() :

«mysql_unbuffered_query() отправляет SQL-запрос в MySQL, не извлекая и не автоматически буферизуя результирующие ряды, как это делает mysql_query() . С одной стороны, это сохраняет значительное количество памяти для SQL-запросов, дающих большие результирующие наборы. С другой стороны, вы можете начать работу с результирующим набором срезу после получения первого ряда: вам не нужно ожидать выполнения полного SQL-запроса»

Однако есть определенные ограничения. Вам придется считывать все записи или вызывать mysql_free_result() прежде, чем вы сможете выполнить другой запрос. Так же вы не можете использовать mysql_num_rows() или mysql_data_seek() для результата функции.

14. Храните IP в UNSIGNED INT

Многие программисты хранят IP адреса в поле типа VARCHAR(15), не зная что можно хранить его в целочисленном виде. INT занимает 4 байта и имеет фиксированный размер поля.
Убедитесь, что используете UNSIGNED INT, т.к. IP можно записать как 32 битное беззнаковое число.
Используйте в запросе INET_ATON() для конвертирования IP адреса в число, и INET_NTOA() для обратного преобразования. Такие же, такие функции есть и в PHP — ip2long() и long2ip() (в php эти функции могут вернуть и отрицательные значения. замечание от хабраюзера The_Lion).

$r = "UPDATE users SET ip = INET_ATON("{$_SERVER["REMOTE_ADDR"]}") WHERE user_id = $user_id" ;

15. Таблицы фиксированного размера (статичные) — быстрее

Если каждая колонка в таблице имеет фиксированный размер, то такая таблица называется «статичной» или «фиксированного размера». Пример колонок не фиксированной длины: VARCHAR, TEXT, BLOB. Если включить в таблицу такое поле, она перестанет быть фиксированной и будет обрабатываться MySQL по-другому.
Использование таких таблицы увеличит эффективность, т.к. MySQL может просматривать записи в них быстрее. Когда надо выбрать нужную строку таблицы, MySQL может очень быстро вычислить ее позицию. Если размер записи не фиксирован, ее поиск происходит по индексу.
Так же эти таблицы проще кэшировать и восстанавливать после падения базы. Например, если перевести VARCHAR(20) в CHAR(20), запись будет занимать 20 байтов, вне зависимости от ее реального содержания.
Используя метод «вертикального разделения», вы можете вынести столбцы с переменной длиной строки в отдельную таблицу.

16. Вертикальное разделение

Вертикальное разделение — означает разделение таблицы по столбцам для увеличения производительности.
Пример 1. Если в таблице пользователей хранятся адреса, то не факт что они будут нужны вам очень часто. Вы можете разбить таблицу и хранить адреса в отдельной таблице. Таким образом, таблица пользователей сократиться в размере. Производительность возрастет.
Пример 2. У вас есть поле «last_login» в таблице. Оно обновляется при каждом входе пользователя на сайт. Но все изменения в таблице очищают ее кэш. Храня это поле в другой таблице, вы сведете изменения в таблице пользователей к минимуму.
Но если вы будете постоянно использовать объединение этих таблиц, это приведет к ухудшению производительности.

17. Разделяйте большие запросы DELETE и INSERT

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

while (1 ) { mysql_query("DELETE FROM logs WHERE log_date <= "2009-10-01" LIMIT 10000" ); if (mysql_affected_rows() == 0 ) { // удалили break ; } // небольшая пауза usleep(50000 ); }

18. Маленькие столбцы быстрее

Для базы данных работа с жестким диском, возможно, является самым слабым местом. Маленькие и компактные записи обычно лучше с точки зрения производительности, т.к. уменьшают работу с диском.
В документации к MySQL есть список требований к хранилищам данных для всех типов данных.
Если ваша таблица будет хранить мало строк, то не имеет смысла делать основной ключ типом INT, возможно лучше будет сделать его MEDIUMINT, SMALLINT или даже TINYINT. Если вам не нужно хранить время, используйте DATE вместо DATETIME.
Однако будьте осторожны, что бы не вышло как с Slashdot .

19. Выбирайте правильный тип таблицы

20. Используте ORM

21. Будьте осторожны с постоянными соединениями

Постоянные соединения предназначены для уменьшения расходов на установление связи с MySQL. Когда соединение создается, оно остается открытым после завершения работы скрипта. В следующий раз, этот скрипт воспользуется этим же соединением.
mysql_pconnect() в PHP
Но это звучит хорошо только в теории. Из моего личного опыта (и опыта других), использование этой возможности не оправдывается. У вас будут серьезные проблемы с ограничением по числу подключений, памятью и так далее.
Apache создает много параллельных потоков. Это основная причина, почему постоянные соединения не работаю так хорошо, как бы хотелось. Перед использованием mysql_pconnect() посоветуйтесь с вашим сисадмином.