{imgicourl}{zamok}
<< предыдущая заметкаследующая заметка >>
05 ноября 2019
А есть специалисты по MySQL?

Может, вы заметили, после переезда обратно в Канаду lleo.me стал иногда притормаживать — был выделенный сервер, теперь просто виртуалка. Беглое изучение вопроса показало, что притормаживает mysql MariaDB. А надо честно признаться, что оптимизацией запросов в движке я практически никогда и не занимался — так, более-менее интуитивно индексы создавал. Теперь впервые включил лог медленных запросов и хочу понять, что сделать для оптимизации нагрузки.

Но сразу конечно вопросы. Вот например, тормозит бывало такой запрос (лог цитирую полностью, потому что не понимаю половины):

# Time: 191105 22:23:48
# User@Host: root[root] @ localhost []
# Thread_id: 630  Schema: dnev  QC_hit: No
# Query_time: 0.010746  Lock_time: 0.000058  Rows_sent: 1  Rows_examined: 25281
# Rows_affected: 0
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
SET timestamp=1572981828;
SELECT `id` FROM `socialmedias` WHERE `num`='4063' AND `net`='lj:lleo';

Почему, кстати «Query_time: 0.010746", когда я просил в настройках логгировать slow-запросы в 5 секунд, — загадка, ну да небось он сам выбирает, какие у него проблемные самые.

Суть в том, что индексы у таблицы `socialmedias` я когда-то сделал такими:

PRIMARY KEY (`i`),
KEY `new` (`acn`,`num`,`net`(64)),
KEY `url` (`url`),
KEY `type` (`type`)

Правильно ли я понимаю, что запрос «WHERE `num`='' AND `net`=''» будет тормозить просто потому, что нет индекса «num+net», а есть только индекс «num+net+acn», но последний параметр не указан в запросе, поэтому индекса, считай, нету? И если я тупо добавлю в запросе « AND `acn`=0» (в однопользовательском движке он всегда 0), то всё начнёт летать? Мне почему-то раньше казалось, что MySQL сам разберется с недостающим параметром... Собственно, я так и сделал, и вроде правда эта строчка перестала вылезать в логах.

Или вот такое же:

SELECT `text` FROM `site` WHERE `name`='redirect';

`site` (
  `name` varchar(128) NOT NULL default '',
  `text` text NOT NULL default '',
  `acn` int(10) unsigned NOT NULL default '0' COMMENT 'Номер журнала',
  PRIMARY KEY (`acn`,`name`)
) ENGINE=MyISAM;

Мне добавить «WHERE `name`='redirect' AND `acn`=0», и всё начнет летать?

Или вот странное, тут-то вроде индекс есть ровно тот, что спрашивается, просто база распухла наверно за два года для такой сложной операции ORDER BY?

SELECT `time` FROM `bitcoin` ORDER BY `time` DESC LIMIT 1;

CREATE TABLE IF NOT EXISTS `bitcoin` (
  `time` int(11) unsigned NOT NULL default '0',
  `BTC` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`time`)
) ENGINE=XtraDB  DEFAULT CHARSET=cp1251;

В общем, если есть специалист по оптимизации MySQL, которому я иногда в Телеграме могу задавать подобные вопросы, отзовитесь.

UPD: Кирилл объяснил магию: оказывается, коллективный индекс (A,B,C) и впрямь может использоваться не только при запросе, содержащем A,B,C, но также A,B и просто A. Но не B,С, не B и не C! Потому что очень важен порядок.
Осознать эту мистику сложно, но я это вижу так: допустим, есть база с полями «страна», «город», температура»:
RU, Москва, +10
RU, Москва, +8
UK, Лондон, +10
UK, Манчестер, +10
RU, Новосибирск, -1
FR, Париж, +13

Видимо коллективный индекс mysql создает встык: «RUМосква+3», «UKЛондон+10»... и дальше он может найти все записи, чей индекс начинается с «RUМосква...» и даже с «RU...», но найти все города и страны с температурой «........+10» для него невозможно, так как не умеет сравнивать с конца.

<< предыдущая заметка следующая заметка >>
пожаловаться на эту публикацию администрации портала
архив понравившихся мне ссылок
Оставить комментарий
Windows Safari Chrome
 Louisville
2
0
avhu (#7861620)
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
>> Правильно ли я понимаю, что запрос «WHERE `num`='' AND `net`=''» будет тормозить просто потому, что нет индекса «num+net», а есть только индекс «num+net+acn»,

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

не используйте MyISAM, он говно.

тут можно кучу копий поломать, но все primary key должны быть суррогатными - натуральные ключи делают кучу геморроя, когда даные оказываются таки не уникальными. хотя тут еще и кластерные ключи играют роль иногда.
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Если не MyISAM, то что? innodb? Я использую innodb в своих последних таблицах, особенно для накопления огромной кучи всяких мелких измерений, но старые таблицы движка исторически MyISAM, и как их поменять на скаку, я не знаю.

Что такое суррогатный ключ, натуральный и кластерный - боюсь, я не знаю терминологии совершенно. Я очень мало работал с базами, только мелкие прикладные задачи из PHP.
Windows Safari Chrome
 Санкт-Петербург
1
0
Михайлов
Индексировать нужно только то, что запрашивается где-либо по ходу, и только "медленные" запросы. Анализировать при помощи настроек slow log и после отбора по их критериям сделанных настроек - с помощью explain. Что значит "медленные" - по умолчанию 1сек. Много это или мало, зависит от размера проекта, количества таблиц, сложности запросов.
Здесь подробнее - https://ruhighload.com/Индексы+в+mysql
Linux Firefox
 Пенза
1
0
qwerty блинн которого сайт забыл
не всё так однозначно: в зависимости от запросов рулить может и иннка и самка ;)
коротко: инка рулить при записи(транзакции + локи), самка - на чтение. впрочем это тоже неоднозначно
Linux Safari Chrome
 Санкт-Петербург
0
2
Валентин Давыдов (#7801858)
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Хотел было посоветовать вместо локальгого мускуля какую-нибудь sqlite использовать, благо её и дебажить легче, и в режиме преимущественно чтения производительность лучше — да переход на ходу дело малореальное, нужно хорошо знать и обе СУБд и php.
Linux Ubuntu Firefox
 Москва
0
0
Adamos
Скулят вместо мускуля на сайте?
Вы еще предложите хостинг сменить на домашний ПК с ОпенСервером.
Linux Safari Chrome
 Москва
0
0
Leonid Kaganov
А что плохого в домашнем хостинге, кстати? Тихий крошечный Intel NUK на домашнем канале 100 Мбит вполне потянет хостинг, который по ресурсам не отличается от 2005 года, когда такое железо и такой канал были нормой.

У меня часть проектов с тем же движком вообще крутится на home.lleo.me и даже spb.lleo.me, а это вообще роутеры arm.
Linux Ubuntu Firefox
 Москва
0
0
Adamos
Вопрос не мощности, а инфраструктуры. Впрочем, у москвичей уже и домашние провайдеры дают вполне приличный канал, редко забитый и почти без разрывов.
Linux Safari Chrome
 New York
0
0
Кто здесь?
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Это пока у вас innodb не заглючило и не потеряло Очень Важные Данные за несколько лет, из-за чего пришлось потом восстанавливать все из бекапов чуть ли не вручную.

Нафиг, нафиг балалайку.
Linux Ubuntu Firefox
 Telefonica Germany GmbH & Co. OHG
1
0
gehrmann
Не нужно ничего гадать. Там в mysql есть такая команда DESCRIBE: если поставить её перед запросом, то будет выведена таблица со всякой информацией, в том числе и какие ключи используются. Очень рекомендую.
Linux Firefox
 Пенза
0
0
qwerty блинн которого сайт забыл
наука имеет много гитек...

Я бы постучал в бубен на тему типа таблиц innodb vs myisam. + RAM на предмет количества.
Linux Firefox
 Boulder
5
0
Михаил (#1684620)
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
> наука имеет много гитек

наука умеет много гитик

Не то, чтобы это влияло на смысл фразы, но её свойства теряются при искажении.
Windows Firefox
 Киев
4
0
Fisher12345
«Осознать эту мистику сложно»

Осознать эту мистику очень просто. Представьте, что перед вами - огромный бумажный телефонный справочник, скажем, всей Москвы, отсортированный по Фамилии + Имени + Отчеству.

Если вам нужно найти по фамилии некоего Пупкина Василия, вы мгновенно находите посередине, где там П, затем где там "Пу", затем среди всех Пупкиных - где начинаются имена на В, и за пару секунд находите искомое. Это индексный поиск.

А если вам поставили задачу искать человека по имени Феофаниус, но без условия фамилии, то упорядоченность справочника вам никак не поможет - вам светить перечитать все имена от первой до последней страницы, то есть выполнить Full Scan.

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

Здесь можно заметить, что наиболее эффективным получается поиск, если первое же поле максимально селективно, то есть если наложение условия на него сразу отсекает хотя бы 90-95% ненужных записей, и оставляет нужные 5-10%. Другими словами, если телефонный справочник Москвы вы вздумаете сортировать по ключу "Город+Фамилия+Имя+Отчество", то первое поле "Город", заполненое для всех одинаковым значением "Москва" для всех записей, будет абсолютно бесполезным для поиска, а место и время для обработки занимать будет, как любое другое поле ключа. Это я к тому, что плохая идея помещать первым полем ключа поле acn, которое для всех равно 0. Это сильно убивает скорость поиска.
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Нет, acn равно 0 не всегда, а только в случае однопользовательского режима: у движка lleo.me/dnevnik нет других пользователей, которые бы вели свои дневники, типа oleg-kuzkin.lleo.me/dnevnik Но они могут быть, если изменить один параметр в конфиге. Например как на binoniq.net - там материалы разных пользователей типа lleo.binoniq.net или vasyapupkin.binoniq.net хранятся в одной таблице, различаясь именно номером acn. Поэтому от acn, увы, никуда не деться, если не ставить себе невыполнимую задачу содержать параллельный зоопарк нескольких разных движков для разных частных случаев ;)
Windows Firefox
 Киев
0
0
Fisher12345
Тогда можно это поле просто в хвост индексного ключа перенести:

KEY `new` (`num`,`net`, `acn`),
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Вы правы, так и надо сделать.
Мне осталось только вспомнить, как написать скрипт, меняющий индексы живых таблиц :)
Windows Safari Chrome
 Москва
1
0
_Иван_ (#7775716)
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
А что просто alter table ... add index не работает?
Оно вроде и на живой базе норм, причем даже к той таблице к коей сейчас идут толпой запросы с тем самым затыком проходит.
Linux Ubuntu Firefox
 Москва
0
0
Adamos
Сначала дропнуть старый индекс, потом уже добавлять новый.
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Ну да. Только мне надо будет это впилить в админку движка.
Там сами таблицы давно админятся - если хоть чуть изменился текстовый файл с описанием table.sql, админка сама предлагает внести каждое из изменений структуры в живую таблицу. А вот индексы так не обрабатывались, они мне вообще до последнего времени были не видны.

Я набросал пока макетку-смотрелку и ужасаюсь, чего там только понаросло в плане индексов за все эти годы. Вот например таблица dnevnik_zapisi:

Найдены индексы:
`num`(`num`) `Date`() `Access`() `DateDatetime`(`DateDatetime`) `DateDate`(`DateDate`) `acn`(`acn`) `PRIMARY`(`Date`) `sn0`(`Date`,`Access`) `sn1`(`Access`) `sn2`(`Access`,`Date`)

Должны быть:
`num`(`num`) `acn`(`acn`) `Date`(`Date`(128)) `Access`(`Access`) `DateDatetime`(`DateDatetime`) `DateDate`(`DateDate`)

Выполнить:
ALTER TABLE `dnevnik_zapisi` DROP KEY `num`
ALTER TABLE `dnevnik_zapisi` DROP KEY `Date`
ALTER TABLE `dnevnik_zapisi` DROP KEY `Access`
ALTER TABLE `dnevnik_zapisi` DROP PRIMARY KEY
ALTER TABLE `dnevnik_zapisi` DROP KEY `sn0`
ALTER TABLE `dnevnik_zapisi` DROP KEY `sn1`
ALTER TABLE `dnevnik_zapisi` DROP KEY `sn2`

ALTER TABLE `dnevnik_zapisi` ADD UNIQUE KEY `num` (`num`)
ALTER TABLE `dnevnik_zapisi` ADD KEY `Date` (`Date`(128))
ALTER TABLE `dnevnik_zapisi` ADD KEY `Access` (`Access`)


Ну вы поняли, от каких-то моих старых экспериментов многолетней давности в таблице висят индексы с автоматическими видимо названиями sn*, сами себя дублирующие:

`Access`()
`sn1`(`Access`)
`PRIMARY`(`Date`)
`sn0`(`Date`,`Access`)
`sn2`(`Access`,`Date`)

Ну не маразм ли? ;)
Windows Firefox
 Москва
2
0
Dimonius
Может скопировать однофайловый (php) web интерфейс управления базами Adminer и в нём это сделать парой кликов быстро и просто?
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Я про такой не слышал. Полезная вещь?

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

У меня в админке движка за все эти годы вырос свой интерфейс, который решает задачи администрирования баз движка:





Задача движка в общем-то одна: чтобы и мне на своих разных серверах и тем, кто использует мой движок, не пришлось разбираться с глубинной технической ерундой типа баз. А просто можно было ткнуть в админке, и все бы обновилось, как надо. Не будешь же каждому объяснять, что для обновления движка ему следует отныне установить такой-то еще софт, туда зайти и руками набирать команды DROP KEY потом ADD KEY, список команд прилагается отдельным письмом...

А вот администрирования индексов там пока не было, надо допилить просто, как будет время.
Windows Firefox
 Москва
3
0
Dimonius
https://www.adminer.org/

Вообще отличный. Морда - ajax/легковесный html (который можно к тому же css'кой настроить, если надо), работает мгновенно, и всё очень удобно. Ну и в целом это просто один php файл. Можно положить (если есть опасение в безопасности, то с правами на доступ только со своего ИП), сделать что надо, а потом файл этот убрать - это не 100500 файлов PMA. В своё время как увидел этот проект, вообще не понимаю зачем везде ставят PMA - смысл в целом тот же, но скорость, удобство как "установки" так и использования - просто несравнимы.

Ну и при выполнении разных задач по изменению таблиц - там можно кликнуть на ссылку "SQL" и он развернёт плашку с запросом, которым эту операцию сделал.

Так же удобно отлаживать запросы - SQL—вставляем и выполняем запрос, нажимаем Explain и он выводит информацию по плану этого запроса, какие индексы использовал и тд (данные из MySQL ессно, просто в более читаемом виде)
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Да, отлично. Буду всем рекомендовать, кто хочет PMA.

PMA меня с самого начала неприятно изумлял - терпеть не могу развесистого говна на 10 мегов и 10000 файлов PHP. Поэтому я от него отказался в свое время раз и навсегда, принципиально.
Linux Ubuntu Firefox
 Москва
1
0
Adamos
Рекомендацию стоит начинать с демонстрации выдачи Гугля по запросу "adminer vulnerability", например. Во избежание розовых очков.
Я бы, кстати, для этих задач рекомендовал не PMA, не Adminer и вообще не панели на сайте, а нормальную IDE вроде PhpStorm, где в том числе и инструмент работы с БД имеется. При этом он не создает потенциальных дыр, поскольку позволяет подключиться к той БД через ssh, например.
Windows Firefox
 Москва
0
0
Dimonius
Ну 1) ошибки есть везде 2) никто не мешает кинуть скрипт в рандомную папку и поставить туда ещё 401 авторизацию...
Windows Safari Chrome
 Домодедово
0
0
id
А кто заставляет держать PMA вечно на месте и вечно включенным? Поставил/запустил/поработал/выключил/снес(переместил)...
Linux Ubuntu Firefox
 Москва
1
0
Leonid Kaganov
Ну так я его и снес ;)

Это вопрос идеологический. Я себе в движке написал микроадминку, которая приводит таблицы к структуре, заданной в файлах *.sql На этом мои потребности лазить в MySQL полностью закончились, если что-то надо - правлю в файле структуру или прописываю новые таблицы и нажимаю кнопку "обновить".

Считаю, что если тебе понадобилось специальным софтом лезть и листать содержимое таблиц или править их структуру руками через софт - это очень четкий и очень верный признак, что ты делаешь что-то не то.
Windows Safari Chrome
 Домодедово
0
0
id
Ну, просто задачи "исправить сайт, чтобы все работало у пользователей и не тормозило" и "научиться делать нетормозящие запросы к SQL" - они хоть и перекликаются, но по сути своей - разные, так как возникают из разных целей. О приоритетах спорить права голоса у меня нет :) но разным задачам - разный оптимум инструментов, и PMA - имхо, конечно - совсем не так плох для быстрого решения первой из задач.
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
И какие же у PMA есть волшебные инструменты, чтобы "научиться делать нетормозящие запросы"? Может, я чего не знаю?

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

Потому что, например, по логике индекса `acn` должен стоять последним в индексе (`A`,`B`,`acn`), потому что он в запросах не указывается, если у движка всего один пользователь. Но по логике работы MySQL `acn` должен стоять на первом месте - ведь он самый сужающий направление поиска, когда пользователей несколько. Поэтому тут не индекс надо править, а код - вставлять принудительное указание acn=0 для однопользовательского режима.

Это просто один из примеров оптимизации - единственный пока, который я разобрал, остальные даже не анализировал, там надо много думать - и над структурой, и над кодом, и над статистикой. И как в этом поможет PMA - загадка. По-моему бессмысленная штука для этих задач.
Mac Firefox
 Дания
3
0
Дмитрий Инкогнитович (#7871929)
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Про индексы уже всё объяснили, да, порядок важен. Кстати, необязательно использовать точное значение поля, иногда достаточно добавить тривиальное условие вроде "AND user_id > 0", чтобы индекс начал использоваться.

Для всех подозрительных запросов стоит запустить EXPLAIN и посмотреть, что происходит.

>> SELECT `time` FROM `bitcoin` ORDER BY `time` DESC LIMIT 1;

Да просто SELECT MAX(`time`) FROM `bitcoin`;
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
О, спасибо!
Windows Safari Chrome
 Якутск
0
0
vmenshov
С запросом SELECT `time` FROM `bitcoin` ORDER BY `time` DESC LIMIT 1; проблема в том, что PRIMARY KEY (`time`) у нас ASC, а просят DESC. Тут планировщик почешет репу, выберет все time из индекса, и потом возьмет последний с конца.

Если бы было PRIMARY KEY (`time`, DESC), то он бы сразу взял первый элемент из индекса и все работало бы быстро.
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Мне понравилась очень ваша идея, но попытка создать такой индекс выдает ошибку:

mysqli_query( "ALTER TABLE `bitcoin` ADD PRIMARY KEY (`time`, DESC)" )

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DESC)'

Возможно, это для каких-то других баз данных работает?
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Или я должен сперва удалить существующий PRIMARY?
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Видимо не PRIMARY KEY (`time`, DESC)
а так: PRIMARY KEY (`time`DESC)
Windows Safari Chrome
 Москва
0
0
vmenshov
Да, запятая там лишняя совершенно, прошу прощения, опечатался. И существующий первичный ключ тоже надо будет удалить сначала.

То есть полный скрипт должен выглядеть как-то так:
ALTER TABLE `bitcoin` DROP PRIMARY KEY, ADD PRIMARY KEY('time' desc);

Однако, если на поле time таблицы bitcoin ссылаются поля каких-нибудь других таблиц, то первичный ключ так просто удалить не получиться. В этом случае сначала нужно будет удалить все foreign keys констреинты, ссылающиеся на поле time, потом пересоздать первичный ключ скриптом выше, а потом заново пересоздать удаленные констреинты.
Linux Safari Chrome
 Москва
0
0
Leonid Kaganov
А что такое констрейнты? Подозреваю, у меня вообще такого нет.
Windows Safari Chrome
 Москва
0
0
vmenshov
Констреинты, это такая штука, которая нужна для согласованности данных. Например, у нас есть две таблицы:
CREATE TABLE posts (
id INT NOT NULL,
post_text varchar(2048) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE comments (
id INT,
post_id INT,
comment_text varchar(1024) NOT NULL,
PRIMARY KEY (id),
INDEX ix_post_id (post_id),
);

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

CREATE TABLE comments (
id INT,
post_id INT,
comment_text varchar(1024) NOT NULL,
PRIMARY KEY (id),
INDEX ix_post_id (post_id),
FOREIGN KEY (post_id )
REFERENCES posts(id)
ON UPDATE RESTRICT ON DELETE RESTRICT

);

То теперь мы не сможем добавить в таблицу comments запись с post_id, которого нет в таблице posts. И при попытке удалить из таблицы posts запись с таким post_id, для которого существуют комментарии в таблице comments, мы получим ошибку. Если же мы заменим ON UPDATE RESTRICT ON DELETE RESTRICT на ON UPDATE CASCADE ON DELETE CASCADE, то при удалении записи из таблицы posts (например, delete from posts where id=100), ошибки никакой не будет, но за компанию удалятся и все комментарии из таблицы comments, соответствующие удаленным постам.
Linux Safari Chrome
 Москва
0
0
Leonid Kaganov
Спасибо, но чота страшновато. Я там удаляю сам в движке всё связанное. Радость получить лишнюю ошибку пока не очень привлекает.

Можно я вам лучше пришлю строчку запросе самого сложного, а вы может посоветуете, как его оптимизировать?
Windows Safari Chrome
 Москва
0
0
vmenshov
Обычно констреинты ставят, чтобы случайно не удалить чего-нибудь лишнего, хотя они и привносят некоторый геморрой с обслуживанием базы (как например, при замене первичного ключа). А каскадное удаление как правило ставят только истинные самураи с железными бейцами, ну или студенты :)

Запрос присылайте, посмотрю что там можно сделать. И еще, если можно, скрипты оп созданию таблиц, которые участвуют в запросе.
Windows Safari Chrome
 Москва
0
0
vmenshov
Пока что-то никуда ничего не пришло.
Linux Safari Chrome
 Москва
0
0
Leonid Kaganov
Да, я нашел вчера одну длинную, которая комментарии формирует, но прошло понимание, что она не самая проблемная. А где самая - пока ищу. Видимо, которая оглавление.
Windows Firefox
 Киев
1
0
Fisher12345
«SELECT `time` FROM `bitcoin` ORDER BY `time` DESC LIMIT 1;»

Здесь надо смотреть план, но хрустальный шар подсказывает, что если возникли проблемы с этим запросом, то, видимо, MySQL сперва извлекает все значения из этой таблицы, затем сортирует их - индекс он может и использует, но на таких объемах это не сильно спасает, а затем берет одно первое значение. Тяжелое задание.

Если интересует максимальное время из таблицы, можно попробовать select max(time) from bitcoin. Документация обещает, что будет использоваться индекс по time.
Mac Safari Chrome
 Новая Зеландия
0
0
lasc
Самое простое, не создавать комбинированные индексы пока совсем не припрет, создавай по полям по которым ищешь. те два отдельных индекса

EXPLAIN в начале покажет что использовалось, а еще лучше EXPLAIN EXTENDED

Тип можно сменить налету ALTER TABLE tablename ENGINE=InnoDB; там у тебя fulltext search нигде не используется?
Linux Firefox
 Италия
0
0
Heisenberg2
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Давно не пользовался MySQL, но в нормальных базах есть разные индексы для "=" и "<", ">".

PKEY, как тут уже сказали, лучше делать суррогатным.
Windows Safari Chrome
 Якутск
0
0
vmenshov
Вообще-то все индексы работают для = < и >. Единственные, которые не работают - это хэш индексы. Но они есть только в некоторых in-memory базах и для обычных таблиц их не построить вообще никак.

UPD. Я нагнал тут. В MySQl HASH-индексы есть, и их можно создать для чего угодно. Но надо специально указывать, что хотим именно HASH.
Windows Firefox
 Тула
1
0
stream
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
log_queries_not_using_indexes отключить для начала. Сначала разберись с настоящими тормозами, а потом уже можно и остальное полировать.

Сейчас у тебя в этот лог сыпятся вообще все запросы, к которым не нашлось никакого индекса, пусть даже в этой таблице 2 значения и выполняются они за 0.000000001 сек.
Linux Ubuntu Firefox
 Москва
1
0
Leonid Kaganov
Спасибо, вписал:

long_query_time = 1
log-queries-not-using-indexes = 0

Попробую собрать статистику по запросам, превышающим 1 секунду.
Windows Safari Chrome
 Москва
0
0
_Иван_ (#7775716)
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Вообще с точки зрения общей теории БД (пресвятой дейтл не даст соврать), то есть индекс «num+net+acn», то «num+net» уже не нужен.
Хотя я больше работал с оракулом и немного с постгрисом, но таковое поведение MySql странно.
Дочел до UPD, ну да, Кирилл прав ))
Чтоб анализировать что происходит надо свободное время (с ним сейчас совсем плохо*) полную структуру БД прорисовать в виде ER-диаграммы и после смотреть какие есть запросы и какие ключи, там станет ясно чего не хватает.

* -- если будет совсем плохо, могу попробовать выбрать время в следующие выходные. В принципе я люблю такую работу и постарался бы выбрать время.
Windows Firefox
 Сумы
0
0
Fisher123
ORACLE поставить уже советовали?
Windows Safari Chrome
 Киев
0
0
Evgenij
Незачем.
Тут всё легко, в минуты, правится переписыванием запросов.
Неудачный код на оракле будет точно так же тормозить.

По сути уже все замечания и советы дали.
Linux Ubuntu Firefox
 Москва
4
0
Leonid Kaganov
И пропатчить KDE!
Mac Safari Chrome
 Нижний Новгород
1
0
tartaglia
Влом читать подряд -- кажется, про одно ещё не писали.

Чтобы действительно быстро извлекать 'id' по 'num' и 'net', мало создать индекс, где в начале (в любом порядке) идут 'num' и 'net', лучше создать индекс типа:
num+net+...+id+...
или
net+num+...+id+...

Тогда id будет извлекаться прямо из индекса. Иначе по индексу будет искаться запись, а 'id' извлекаться уже из записи. Это лишнее обращение к базе.

Это всё самоочевидно, на самом деле. Тут уже объясняли на пальцах.
Mac Safari Chrome
 Нижний Новгород
0
0
tartaglia
> SELECT `id` FROM `socialmedias` WHERE `num`='4063' AND `net`='lj:lleo';

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

r = prepare("SELECT `id` FROM `socialmedias` WHERE `num`=? AND `net`=?");
r.execute('4063', 'lj:lleo');
...
r.execute('1917', 'lenin');

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

Если движку поступает два идентичных, по сути, запроса, но текстуально разные из-за того, что в запросы вклеены разные параметры, то движок может и не суметь вычленить идентичность и будет разбирать запрос два раза, что весьма неэффективно.

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

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

P.S. Пользуясь случаем, поздравляю всех с праздником 7 ноября! Не все тут коренные москвичи в десятом поколении, а потому многие, как и я, могут сказать, что не случись Великой Октябрьской Социалистической Революции, у них не было бы шанса появиться на белый свет. Мой папаша так и сидел бы на карельском болоте, а мама крестьянствовала в деревне под Кстово. Не получили бы они высшего образования и не встретились бы никогда.
Linux Safari Chrome
 Москва
0
0
Leonid Kaganov
Я извиняюсь, а это на каком языке r = prepare("SELECT...") и r.execute()? Мне кажется это похоже на фреймворк, который решает свои задачи, не связанные с базой и поверх ее протоколов.
Mac Safari Chrome
 Нижний Новгород
0
0
tartaglia
Это был псевдокод, как я и написал. Для PHP (я знавал PHP3, но это было очень давно, так что тупо копипейст, которого я не понимаю, но подозреваю, что это оно самое, вы лучше поймёте) гуглится:

https://stackoverflow.com/questions/44396690/using-php-varia[...]

$query = "SELECT value FROM database.table WHERE field_name = 'hospital_name' AND value = ?";

$statement = mysqli_prepare($conn, $query);

//Bind parameter for $q; substituted for first ? in $query
//first parameter: 's' -> string
mysqli_stmt_bind_param($statement, 's', $q);

//execute the statement
mysqli_stmt_execute($statement);
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Что-то я вас не понимаю.
То ли это какие-то пляски на языке высокого уровня вокруг данных, которые отдает база, но тогда зачем они?
Либо вы из языка разработки обращаетесь к MySQL не один, а два раза - первый раз описываете задачу и просите подготовиться к запросу, конкретные данные которого будут сообщены позже, а второй раз - передаете в MySQL данные? В этом случае уверены ли вы, что два запроса точно будут быстрее, чем один?
Mac Safari Chrome
 Нижний Новгород
0
0
tartaglia
Ещё раз подчеркну: суть не в базе данных (даже не в типе базы данных), и не в языке программирования. Суть в том, чтобы каждый раз подавался один и тот же запрос, чтобы скомпилированный вариант кэшировался где-нибудь в движке базы данных.

Собственно, я всё это изучал когда-то для Oracle 8. Примерно четверть века назад. В документации был отдельный том по оптимизации. Не думаю, что много в этом отношении изменилось. Разве что базы данных могли стать умнее и, может быть, они теперь сами разбирают SQL-запросы, отделяя структуру от переменных. Не знаю, так глубоко мне теперь разбираться не нужно.

Если сделать по-старому: отдельно запрос, отдельно переменные -- то хуже точно не будет. Особенно если есть вариант со сменой базы данных.
Linux Ubuntu Firefox
 Москва
0
0
Leonid Kaganov
Вы хотите сказать, что парсинг самой строчки запроса в MySQL занимает ощутимое время?
Windows Safari Chrome
 Москва
1
0
Негениальный программист (#7732005)
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Скорее не парсинг, а построение плана запроса - т.е. конкретного алгоритма, которым этот запрос будет выполняться.

Ну как выше был пример "сначала выгребу все, потом отсортирую и наконец возьму первый".

Если текст запроса повторяется в точности, этот алгоритм составляется не при каждом выполнении запроса.
Windows Safari Chrome
 Якутск
1
0
vmenshov
Скажем так, prepared statements кэшируют план запроса. То есть в следующий раз сервер не думает, как ему крутить и вертеть данные, а сразу пойдет по придуманному заранее алгоритму (пока статистика данных не обновится).

Если перенести это в жизнь, то у вас есть куча дров. Из них 3 - с монетами внутри. И есть запрос на поиск монет внутри дров. prepared statements - это подготовка топора. То есть он у вас уже лежит готовый. А без них придется идти в сарай за ним. Но дрова-то все равно придется перерубить все. И это намного-намного дольше чем сходить за топором. С другой стороны правильный индекс - это поленница где лежат только дрова с монетами внутри. И если его построить, то разрубить придется только три полена и их не придется искать, они готовые лежат. Тут поход за топором уже может заметно влиять на общее время выполнения задачи. Но это все равно миллисекунды в реальности, так что заморачиваться имеет смысл только если таких запросов приходит хотя бы несколько десятков в секунду и более.
Linux Ubuntu Firefox
 Москва
0
0
Adamos
Бывает и обратная ситуация, когда вынести всю поленницу оптимальнее, чем уменьшать количество разрубаемого за раз.
Если на сайт заходят по десять человек в секунду и получают примерно одни и те же данные, но чуть по-разному - получение всех этих данных тривиальным запросом оказывается быстрее просто потому, что они уже с предыдущего запроса лежат в кэше БД, так что вовсе не требуется ходить ни за "топором", ни за "поленьями".
Windows Safari Chrome
 Москва
0
0
vmenshov
Насколько я знаю, sql-сервера не кэшируют результаты запроса (там целый роман можно написать, почему это чертовски сложно сделать). Они кэшируют страницы данных и индексов, поднятых с диска на предыдущих запросах. Но перебирают эти данные в памяти все равно каждый раз заново. И если у вас в таблице несколько миллионов записей, и все это по несколько десятков раз в секунду полностью перебирается, то тормоза уже будут заметны на глаз, даже если все данные в оперативной памяти закэшированы. Поэтому у "тривиальных" запросов преимуществ в этом плане никаких нет.

Другое дело, что индексы порой, действительно, могут даже мешать. Обычно такое бывает когда количество данных по какому-то значению ключа индекса сравнимо с общим количеством данных в таблице. А все запросы идут именно по этому значению ключа. Ну или еще какие-то более экзотические ситуации.
Linux Safari Chrome
 Москва
0
0
Leonid Kaganov
У меня мемкеш кэширует большинство запросов.
Windows Safari Chrome
 Москва
0
0
vmenshov
Да, приходится придумывать какой-нибудь внешний (по отношению к sql-серверу) кэш. Сам sql-сервер должен обеспечивать железобетонную согласованность данных, то есть грубо говоря, он не может выдать данные, которые запросу в рамках транзакции уже не релевантны. И в условиях массовой параллельной нагрузки следить устареванием этих данных по всем пришедшим на сервер запросам - это практически нереализуемая задача. Поэтому сервер кэширует только сами данные (он знает когда изменялась та или иная страница данных и может их грамотно инвалидировать), а задачу кэширования результатов запроса перекладывает на пользователя. Так как только сам пользователь знает, насколько устаревшие данные ему еще годятся, а какие уже никак не годятся, и кэш пора обновлять.
Windows Firefox
 Москва
0
0
Dimonius
Ну вообще парсинг запроса и подготовка плана запроса составляют весьма значительное время из всего времени выполнения. Плюс, при использовании того же PDO там ещё и будет автоматическое экранирование параметров (для того, чтобы избежать SQL Injections).

Вопрос, что на ваших нагрузках это всё экономия на спичках. Нормальные индексы (только нужные и правильные) дадут отличную скорость. Ну а экранирование параметров в нужных местах - избавит от injections.
Windows Safari Chrome
 Москва
0
0
vmenshov
Да, экранирование параметров это важная фича, согласен.
Mac Safari
 Emirates Integrated Telecommunications
0
0
я забыл падписацца, асел (#7872105)
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Столько народу, и еще никто не посоветовал оптимизироваться, тупо перейдя на PostgreSQL? Вот nginx же лучше, чем apache для статики? Намного. Вот а PostgreSQL лучше для нагруженного LAMP-сервера в продакшне.

Очень рекомендую!
Mac Safari
 Австралия
1
1
Царь (#5735126)
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Действительно, осел.
Windows Safari Chrome
 Якутск
0
0
vmenshov
Давай объясню магию с индексами. Индекс по полям строится как дерево.

Например, есть данные:
RU, Москва, +10
RU, Москва, +8
FR, Марсель, +20
UK, Лондон, +10
UK, Манчестер, +10
UK, Ливерпуль, +11
RU, Новосибирск, -1
FR, Париж, +13
FR, Париж, +11

Строим индекс по (country, town, temperature). Тогда в системе будет, грубо говоря, вот такое дерево:
FR-
    Марсель
        +20
    Париж
        +11
        +13
RU-
    Москва
        +8
        +10
    Новосибирск
        -1
UK-
    Ливерпуль
        +11
    Лондон
        +10
    Манчестер
        +10

А теперь мы делаем запрос select * from table where coutry='RU'
В этом случае систем может взять из индекса "папку" ru, и сразу выдать все ее содержимое с подпапками. Папки FR и UK она даже смотреть не будет.

Если мы делаем запрос select * from table where coutry='FR' and town = 'Марсель' система сразу зайдет в папку FR и выдаст все что в подпапке 'Марсель'

Если же мы зададим запрос select * from table where temperature='+10', то эти папки никак системе не помогут. Все равно придется перебирать все города и страны. Но если мы сделаем индекс по (temperature, country, town), то индекс будет такой:

-1
    RU
        Новосибирск
+8
    RU
        Москва
+10
    RU
        Москва
    UK
        Лондон
        Манчестер
+11
    FR
        Париж
    UK
        Ливерпуль
+13
    FR
        Париж
+20
    FR
        Марсель

И запрос select * from table where temperature='+10' сразу возьмет из индекса папку +10 и выдаст все что там есть со всеми подпапками, перебирать все уже не нужно.

Так же отмечу, что все папки и подпапки осортированы, так что на каждом уровне можно искать их бинарным поиском, что намного быстрее чем просто перебирать.

Это весьма упрощенное описание, на самом деле там се намного сложнее, но он дает хорошее общее понимание, как это все работает.

всего комментариев: 73

<< предыдущая заметка следующая заметка >>