Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 142
при написании запросов не используйте выборку всех полей — "*".
об этом много споров…
некоторые говорят что иногда, при малом количестве полей в таблице, select * быстрее…
об этом много споров…
некоторые говорят что иногда, при малом количестве полей в таблице, select * быстрее…
Думаю, что про то, что перечесление полей или звездочка работают быстрее друг друга говорить не стоит. Отличия если и есть, то они будут минимальными. А вот то, что вы получите замедление при выборке ненужных данных — это факт.
Вообще, переписывать все поля занятие довольно унылое, особенно если их больше 50. Хорошо бы придумать что-то, чтобы об этом не думать. Например автоматизировать, используя один из объектых SQL мапперов или вынести BLOB и TEXT в отдельные таблицы и подключать только при необходимости.
Но перечисление полей — это непозволительная растрата времени программиста :)
Но перечисление полей — это непозволительная растрата времени программиста :)
Вы не правы потому, что если так не делать — позже при изменениях в структуре базы данных/таблиц программист затратит _гораздо_ больше времени на то, сначала понять, что проблема в неправильном запросе, а затем еще на то чтобы переписать его правильно.
Не следует путать понятия «лень» и «бессмысленная трата времени». В данном совете трата времени весьма осмысленна и полезна.
Не следует путать понятия «лень» и «бессмысленная трата времени». В данном совете трата времени весьма осмысленна и полезна.
50 и более столбцов в таблице
я, конечно, не исключаю, что где то это необходимо, но в общем случае это повод для изменения архитектуры БД.
P.S.
Раскрывать звездочки налету такая же сомнительная идея…
Это надо делать раз — при написании запроса, я это так и делаю :) (Delphi+FIBPlus)
я, конечно, не исключаю, что где то это необходимо, но в общем случае это повод для изменения архитектуры БД.
C чего бы? Взять хотя бы человека — сколько у него может быть «свойств» = колонок в таблице? имя, фамилия, адрес, почтовый индекс, номер ИНН, телефон, icq, skype… дальше продолжать?
Вот это тот самый показательный пример.
Адрес — это отдельная сущность. Есть адрес регистрации, а есть адрес проживания.
Будем сразу делать нормальную структуру или дальше лепить поля street1 b street2?
Адрес — это отдельная сущность. Есть адрес регистрации, а есть адрес проживания.
Будем сразу делать нормальную структуру или дальше лепить поля street1 b street2?
Ну как вам сказать. Если в моем проекте адрес выступает исключительно в привязке к человеку, а не как самостоятельная единица — то он — свойство, а не сущность. Конечно — можно во все проекты вгружать КЛАДР и еще устраивать проверки на валидность + делать объединения (не нужные совершенно).
Я никогда не считал, что разделение на сущности должно быть бездумным. «Имя» — это тоже отдельная сущность в каком то смысле, да и icq тоже — где грань? Можно все их разбить посущностно и вместо одной таблицы с 50 полями, получить 50 таблиц с кучей JOIN-ов.
Я никогда не считал, что разделение на сущности должно быть бездумным. «Имя» — это тоже отдельная сущность в каком то смысле, да и icq тоже — где грань? Можно все их разбить посущностно и вместо одной таблицы с 50 полями, получить 50 таблиц с кучей JOIN-ов.
Предлагаю вам решение))
CREATE TABLE userinfo (
id INTEGER AUTO_INCREMENT,
user_id INTEGER, /* наверно надо сделать ключом, поправьте меня*/
field_type INTEGER, /* тип поля, 1=имя, 2=фамилия, и т. д. */
field_value TEXT
);
Как насчет такого?
CREATE TABLE userinfo (
id INTEGER AUTO_INCREMENT,
user_id INTEGER, /* наверно надо сделать ключом, поправьте меня*/
field_type INTEGER, /* тип поля, 1=имя, 2=фамилия, и т. д. */
field_value TEXT
);
Как насчет такого?
гемор с разворачиванием в строку, например, надо получить «Иванов И. И.»
Можно. Мне не нравится, потому что работать будет медленнее, чем 1 таблица :)
а вообще рассматривать общие подходы без привязки к конкретным данным и задачам — квинтэссенция бреда, имхо :)
Я просто считаю утверждение, что если полей в таблице 50 — то нужно перепроектировать БД, не видя данных и не зная задач — неверным (очень мягко говоря)
а вообще рассматривать общие подходы без привязки к конкретным данным и задачам — квинтэссенция бреда, имхо :)
Я просто считаю утверждение, что если полей в таблице 50 — то нужно перепроектировать БД, не видя данных и не зная задач — неверным (очень мягко говоря)
Торомозить на выборках будет жутко. А для веб-приложений это критично (mysql в основном же в веб-приложениях используется).
Как учили нас в университетах процессу «нормализации» БД, так сейчас приходится выдумывать способы «денормализации», бо очень важной становится скорость выборок.
Как учили нас в университетах процессу «нормализации» БД, так сейчас приходится выдумывать способы «денормализации», бо очень важной становится скорость выборок.
Ну, там где это повод для изменения архитектуры к чему мы прийдем? К 20 полям вместо 50 и к 30 именам таблиц в джоинах вместо 3-х? :) Да и поля из подключаемх таблиц перечислять всеравно придется… итого. вместо 50 получаем получим все 80…
Понятно, что я утрирую, но я и начинал не о том, что записывать поля не нужно, а о том, что в некоторых разросшихся проектах, писать запросы вручную целиком нужно только в очень редких случаях.
А в остальных случях рациональнее использовать автоматические рендеры (их много разных), и править только их критичные куски. Это экономит _очень_ много времени, в том числе и при изменениях в структуре, о которой говорил предыдущий оратор.
Понятно, что я утрирую, но я и начинал не о том, что записывать поля не нужно, а о том, что в некоторых разросшихся проектах, писать запросы вручную целиком нужно только в очень редких случаях.
А в остальных случях рациональнее использовать автоматические рендеры (их много разных), и править только их критичные куски. Это экономит _очень_ много времени, в том числе и при изменениях в структуре, о которой говорил предыдущий оратор.
изза таких как ты админы мучаются с внредрением новых аппаратных средств, а канторы тратят лишние десятки тысяч долларов. Ты столько за год не зарабатываешь. Подумай что дороже. 5 минут твоего драгоценного времени или новый сервак под БД?
Выносить блобы в отдельные таблицы нужно по другим причинам… хотя и по этой тоже. Это слишком разнородные данные, обрабатываемые по-разному.
Касательно * — а работайте через phpMyAdmin, он лимит ставит :) В коде же работающей программы такое допустимо только для небольших справочников.
Касательно * — а работайте через phpMyAdmin, он лимит ставит :) В коде же работающей программы такое допустимо только для небольших справочников.
В основном все простое, но некоторые вещи для себя переосмыслил.
Подозреваю что в "$cnt = query('SELECT COUNT(*) FROM table');" вкралась ошибка, в таком варианте всегда будет выводиться одна и та же запись.
И вопрос на туже тему, «Если в таблице auto_increment'ный первичный ключ и нет пропусков» — вы же понимаете что это единичные случаи. Всегда есть пропуски. Что делать? Имеет ли смысл держать отдельную таблицу с `id` только для выборки случайного значения по ней? Есть другие варианты?
Подозреваю что в "$cnt = query('SELECT COUNT(*) FROM table');" вкралась ошибка, в таком варианте всегда будет выводиться одна и та же запись.
И вопрос на туже тему, «Если в таблице auto_increment'ный первичный ключ и нет пропусков» — вы же понимаете что это единичные случаи. Всегда есть пропуски. Что делать? Имеет ли смысл держать отдельную таблицу с `id` только для выборки случайного значения по ней? Есть другие варианты?
imho, так будет лучше:
Если в таблице auto_increment'ный первичный ключ и ВОЗМОЖНЫ пропуски:
$rnd = rand(1, query('SELECT MAX(id) FROM table'));
$row = query('SELECT * FROM table WHERE id >= '.$rnd.' LIMIT 1');
у меня есть свой вопрос: а как эфективно выбрать случайные M из N записей? например 10 случайных статей из 10 000? я не знаю как это сделать за меньше чем M+1 запросов :(
Если в таблице auto_increment'ный первичный ключ и ВОЗМОЖНЫ пропуски:
$rnd = rand(1, query('SELECT MAX(id) FROM table'));
$row = query('SELECT * FROM table WHERE id >= '.$rnd.' LIMIT 1');
у меня есть свой вопрос: а как эфективно выбрать случайные M из N записей? например 10 случайных статей из 10 000? я не знаю как это сделать за меньше чем M+1 запросов :(
Сгенерируйте M случайных id в пределах N. Если есть пропуски, то чуть больше: K=M*2 (больше или меньше, если пропусков много). Тогда запрос будет один:
select * from table where id in (1, 2, 3,… K) limit M;
select * from table where id in (1, 2, 3,… K) limit M;
это подходит, если меня устроит негарантированные M записей.
т. к. IN () ищет точное совпадение, реально на выходе я могу получить пустое множество
т. к. IN () ищет точное совпадение, реально на выходе я могу получить пустое множество
Тут как раз наиболее оптимальный наверно мой способ :) Держать таблицу чисто под `id` для выборки. rand ее провернет быстрее чем «рабочую», а по полученному результату мы гарантированно выберем то что нам нужно. Конечно главное нигде не забывать соблюдать целостность связи между таблицами
*Это получается 2 запроса…
Хотя тут еще надо гонять, чтобы найти ту «точку» где такой способ будет быстрее, чем банальный «order by rand» по основной таблице
Хотя тут еще надо гонять, чтобы найти ту «точку» где такой способ будет быстрее, чем банальный «order by rand» по основной таблице
Непроверено, но нутром чую, что ORDER BY rand() будет одинаково медленным что на полной таблице, что на таблице_из_id. В обоих случаях «на лету» будет создан новый индекс по номерам записей. Только добавится дополнительная работа по зеркалированию этих id.
Или вы имели в виду что-то более хитрое? Я не понял.
Или вы имели в виду что-то более хитрое? Я не понял.
SELECT user_id FROM users WHERE MD5(pass) = 'pwd';
SELECT user_id FROM user WHERE pass = MD5('pwd');Вообще говоря — это не одно и тоже.
Я уж не говорю, что первый запрос в принципе смысла не имеет. Если пароли в базе хэшированные, то что такое MD5(pass)? А если в открытом виде хранятся, то зачем вообще MD5() делать если можно явно
SELECT user_id FROM users WHERE pass = 'pwd'.
Удивился, что узнал для себя новое — CASE WHEN… THEN в UPDATE, пойду почитаю, что это такое :)
Да, пример совсем не кассу получился. Спасибо, исправил
Ну первый вариант можно использовать, если мы хотим хранить в базе пароли в чистом виде, НО не хотим слать запросы с паролями, а шлем сразу MD5 хеш… Смысл я себе конечно очень смутно представляю.
Второй вариант, да, совершенно противоположный, именно он имеет смысл на практике, его всегда и используют
Второй вариант, да, совершенно противоположный, именно он имеет смысл на практике, его всегда и используют
Сначала советуете не использовать SELECT *, потом в последнем примере сами же и приводите ) Непорядок!
Кстати, могу поделиться наблюдением: большое количество INNER JOIN-ов, в отличие от LEFT-ов, запрос сильно не замедляет. Кто-нибудь мог бы объяснить, почему?
INNER JOIN обычно возвращает куда меньший набор строк.
я ба сказал объединяет.
Ну так не на порядок же. Ровно на столько, сколько строк не удовлетворяют условию JOIN-а, а таковых может и вовсе не быть.
Без конкретных условий тут не о чем дискутировать. Inner join объединяет только строки с совпадающим ключем в обеих таблицах. Left join всегда объединяет левую таблицу со всей правой целиком. При некоторых раскладах разница в производительности может быть гораздо больше чем на порядок, но при некоторых ее может и не быть вовсе.
Сам буквально вчера столкнулся!
LEFT JOIN жестко задает порядок выполнения запроса: пробегаемся по всем записям первой таблицы, и для каждой ищем во второй соответствующие им. При этом количество записей может оказаться большим, и в EXPLAIN можно увидеть Using temporary или filesort (а это в данном случе плохо)
INNER JOIN оставляет MySQL возможность выбора порядка объединения по своим критериям, и она может выбрать более правильный порядок.
Если вы используете LEFT JOIN, вы должны вручную расставить таблицы в оптимальном порядке.
Это легко проверяется созданием 2 таблиц, например «Компании» и «Вакансии», каждая вакансия принадлежит какой-то компании. После чего делаем EXPLAIN SELECT companies LEFT JOIN vacancies WHERE… и EXPLAIN SELECT vacancies LEFT JOIN companies и сравниваем.
LEFT JOIN жестко задает порядок выполнения запроса: пробегаемся по всем записям первой таблицы, и для каждой ищем во второй соответствующие им. При этом количество записей может оказаться большим, и в EXPLAIN можно увидеть Using temporary или filesort (а это в данном случе плохо)
INNER JOIN оставляет MySQL возможность выбора порядка объединения по своим критериям, и она может выбрать более правильный порядок.
Если вы используете LEFT JOIN, вы должны вручную расставить таблицы в оптимальном порядке.
Это легко проверяется созданием 2 таблиц, например «Компании» и «Вакансии», каждая вакансия принадлежит какой-то компании. После чего делаем EXPLAIN SELECT companies LEFT JOIN vacancies WHERE… и EXPLAIN SELECT vacancies LEFT JOIN companies и сравниваем.
Автор просто переаутал inner join и outer join
Многие советы очевидны. Я бы назвал статью не «Оптимизация запросов», а «Какие ошибки не нужно допускать».
Но все равно спасибо. Статья будет полезна новичкам.
Но все равно спасибо. Статья будет полезна новичкам.
Порой программистам, привыкшим думать терминами функциональных языков
Возможно вы имели ввиду процедурных языков? Функциональными обычно называют языки по типу Lisp, Haskell, OCaml
Статья улыбнула. БОльшая часть пунктов из серии «программист Боб Кривые Руки».
> а ваши примеры — на смех
рад, что развесилил вас.
> не бывает таблиц без удаленных строк
Очень даже бывают. Если удалений не много, то строки можно не удалять, а помечать как удаленные.
рад, что развесилил вас.
> не бывает таблиц без удаленных строк
Очень даже бывают. Если удалений не много, то строки можно не удалять, а помечать как удаленные.
Ну или возьмите например хабр. Блоги удалять нельзя. Их можно только делать закрытыми. Получаем таблицу без дыр с id — первичным ключом.
В теории все может быть, но на практите таких таблиц практически не бывает.
Даже если в процессе работы программы удаления нет, то они могут появится через ручное изменение. Например, я всегда после тестирования очищаю таблицы, но не обнуляю auto_increment. И любая такая дырка, влечет за собой неверную работу обсуждаемого запроса.
Даже если в процессе работы программы удаления нет, то они могут появится через ручное изменение. Например, я всегда после тестирования очищаю таблицы, но не обнуляю auto_increment. И любая такая дырка, влечет за собой неверную работу обсуждаемого запроса.
Нууу. Если вам _действительно_ нужно, чтобы id были монотонно возрастающими, то можно позаботиться о том, чтобы руками никто из таблицы не удалял и делать truncate table вместо delete from table
Разумеется! А если вдруг что-то нужно срочно удалить, то пересчитать ключи всех последующих записей и апдейтить auto_increment.
Это настолько надуманно, искуственно и чревато огромными проблемами в дальнейшем, что такой способ в жизни работать не будет.
Вот приведите пример из своей жизни, где у вас была подобная таблица?
Это настолько надуманно, искуственно и чревато огромными проблемами в дальнейшем, что такой способ в жизни работать не будет.
Вот приведите пример из своей жизни, где у вас была подобная таблица?
(цитата)
— select * from table where id in (select id from table order by rand()) limit 1,
вот это выполнится перфектно при условии что ид = первичный ключ, поспорите? ;)
(/цитаты)
Поспорю.
order by rand приведет к тому, что оптимизатор даже не сообразит, какой индекс можно использовать. То есть он выберет filesort, temporary file для вычисления списка id-ов в случайном порядке в сабселекте…
далее сервер соберет резултсет из всех записей (поскольку все id есть в разультатах сабселекта) и затем первая строка из него (limit 1) будет возвращена пользователю.
Не слишком похоже на оптимальный план.
— select * from table where id in (select id from table order by rand()) limit 1,
вот это выполнится перфектно при условии что ид = первичный ключ, поспорите? ;)
(/цитаты)
Поспорю.
order by rand приведет к тому, что оптимизатор даже не сообразит, какой индекс можно использовать. То есть он выберет filesort, temporary file для вычисления списка id-ов в случайном порядке в сабселекте…
далее сервер соберет резултсет из всех записей (поскольку все id есть в разультатах сабселекта) и затем первая строка из него (limit 1) будет возвращена пользователю.
Не слишком похоже на оптимальный план.
Офигеть! Спасибо за реализацию случайной выборки, долго искал как это сделать.
Если не надо очень часто делать случайную сортировку, то я делал так:
1. Вводил колонку `randomindex` для хранения случайного числа и индекс по ней
2. Периодически (в фоновом процессе или по TTL) выполнял запрос
UPDATE `%TABLENAME%` SET `randomindex`=RAND();
3. Выполнял выборку с сортировкой по колонке `randomindex`
SELECT `%COLUMNNAME%` FROM `%TABLENAME%` ORDER BY `randomindex`
Второй запрос в этом примере работает очень быстро
1. Вводил колонку `randomindex` для хранения случайного числа и индекс по ней
2. Периодически (в фоновом процессе или по TTL) выполнял запрос
UPDATE `%TABLENAME%` SET `randomindex`=RAND();
3. Выполнял выборку с сортировкой по колонке `randomindex`
SELECT `%COLUMNNAME%` FROM `%TABLENAME%` ORDER BY `randomindex`
Второй запрос в этом примере работает очень быстро
Выполнение операций над проиндексированными полями
SELECT user_id FROM users WHERE blogs_count * 2 = $value
В таком запросе индекс использоваться не будет, даже если столбец blogs_count проиндексирован. Для того, чтобы индекс использовался, над проиндексированным полем в запросе не должно выполняться преобразований. Для подобных запросов выносите функции преобразования в другую часть:
SELECT user_id FROM user WHERE blogs_count = $value * 2;
Может, так правильнее?
SELECT user_id FROM user WHERE blogs_count = $value / 2;
Поменяйте SELECT user_id FROM user WHERE blogs_count = $value * 2;
на SELECT user_id FROM user WHERE blogs_count = $value / 2;
на SELECT user_id FROM user WHERE blogs_count = $value / 2;
"# Использование LIMIT
SELECT… FROM table LIMIT $start, $per_page"
В таких случаях выбирать стоит по auto_increment id — WHERE `id` > 1 000 000 AND `id` < 1 000 020
SELECT… FROM table LIMIT $start, $per_page"
В таких случаях выбирать стоит по auto_increment id — WHERE `id` > 1 000 000 AND `id` < 1 000 020
Конечно же такая выборка по индексному полю, к примеру, INT(8) будет быстрее лимитов с шести- и семи- значными цифрами.
К сожалению, цифр я никаких предоставить не могу — сейчас валяюсь с температурой 39 уже несколько дней и к компу подхожу изредка. Чесслово, нет никакого желания экспериментировать.
Если кто-либо проведёт испытания — буду премного благодарен.
ps. задолбали эти ограничения на комментирование!
К сожалению, цифр я никаких предоставить не могу — сейчас валяюсь с температурой 39 уже несколько дней и к компу подхожу изредка. Чесслово, нет никакого желания экспериментировать.
Если кто-либо проведёт испытания — буду премного благодарен.
ps. задолбали эти ограничения на комментирование!
«INT(8) будет быстрее лимитов с шести- и семи- значными цифрами.»
число в скобках обозначает совсем не разрядность целого.
число в скобках обозначает совсем не разрядность целого.
для любителей докапываться по мелочам: вверху у меня опечатка.
«шести- и семи- значными цифрами.»
конечно же, «числами».
удивительно, как на это не обратил внимание сей комментатор: habrahabr.ru/blogs/mysql/41968/#comment_1031978
«шести- и семи- значными цифрами.»
конечно же, «числами».
удивительно, как на это не обратил внимание сей комментатор: habrahabr.ru/blogs/mysql/41968/#comment_1031978
А если id начинается с числа 1000000?
Не понял вопроса.
Какую роль играет с какого числа начинается содержимое полей?
Мы же просто делаем выборку где id приблизительно попадает в нужный нам диапазон с использованием индекса.
Какую роль играет с какого числа начинается содержимое полей?
Мы же просто делаем выборку где id приблизительно попадает в нужный нам диапазон с использованием индекса.
Такую, что в этом случае ваш запрос вернет не те строки — он вернет первые 20 (18 если быть точным, с первой по девятнадцатую), а не с 1000000 по 10000020, как было бы, если бы мы написали LIMIT 1000000,1000020. Да и то, только в случае, если между ними нет пропусков
про пропуски ниже.
про < >= habrahabr.ru/blogs/mysql/41968/#comment_1031996 второй абзац.
зачем оставлять подобные комменты я не въезжаю.
или это наша национальная черта проявляется «в чужом глазу соринку....»?!
про < >= habrahabr.ru/blogs/mysql/41968/#comment_1031996 второй абзац.
зачем оставлять подобные комменты я не въезжаю.
или это наша национальная черта проявляется «в чужом глазу соринку....»?!
Ничего не понял. Если вы болеете и плохо соображаете — болейте, но не пишите ерунды.
Нельзя заменить один запрос на другой, так как вы написали. Только в очень ограниченном количестве случаев — если id без пропусков, если не наложено никакое условие WHERE. Так как вы взяли конкретные цифры из конкретного примера, то ко всему прочему ваш совет вообще неверен, потому что вы предлагаете вместо получения 20 записей со смещением в миллион вытащить записи у которых id лежит в некотором диапазоне (т. е. предлагаете полагаться на содержимое полей). Это принципиально разные вопросы в общем случае.
При чем тут национальность — не въехал совсем, выздоравливайте
Нельзя заменить один запрос на другой, так как вы написали. Только в очень ограниченном количестве случаев — если id без пропусков, если не наложено никакое условие WHERE. Так как вы взяли конкретные цифры из конкретного примера, то ко всему прочему ваш совет вообще неверен, потому что вы предлагаете вместо получения 20 записей со смещением в миллион вытащить записи у которых id лежит в некотором диапазоне (т. е. предлагаете полагаться на содержимое полей). Это принципиально разные вопросы в общем случае.
При чем тут национальность — не въехал совсем, выздоравливайте
// почему коммент отправился-то? я ж не дописал. случайно.
так вот, для этого безобразия потом можно приделать проверку на количество возвращённых записей в ответе (в случае если меньше 20, то добрать недостающего количества или заведомо набирать больше, чем нужно).
так сделано, например, на xap.ru. в результате на одной и той же странице постоянно разные проиндексированные урлы.
так вот, для этого безобразия потом можно приделать проверку на количество возвращённых записей в ответе (в случае если меньше 20, то добрать недостающего количества или заведомо набирать больше, чем нужно).
так сделано, например, на xap.ru. в результате на одной и той же странице постоянно разные проиндексированные урлы.
Вариант не катит, т. к. опять же удаленные строки в таблице
если там всего 1 запись 1 000 001?
если там всего 1 запись 1 000 001?
Ну ясное дело что по первичному ключу лучше. Только
1) нет гарантии что вы выберите именно 19 значений, как Вы предолагаете
2) Нет гарантии, что в записи где $start = 1000 000, и первичный ключ тоже = 1 000 000
Вообще, если речь идет об выборке по страницам, я бы предпочел в hidden полях хранить граничные значения первичного ключа для этой страницы, а при переходе (скажем) на след. страницу, делать
select что_надо from таблица where keyfield>сохраненныйпервичныйключ limit 20;
1) нет гарантии что вы выберите именно 19 значений, как Вы предолагаете
2) Нет гарантии, что в записи где $start = 1000 000, и первичный ключ тоже = 1 000 000
Вообще, если речь идет об выборке по страницам, я бы предпочел в hidden полях хранить граничные значения первичного ключа для этой страницы, а при переходе (скажем) на след. страницу, делать
select что_надо from таблица where keyfield>сохраненныйпервичныйключ limit 20;
А если у меня сортировка может меняться. Вот в списке товаров интернет-магазина я могу поставить сортировку по цене или по названию. И какием запросом мне выводить товары для третьей страницы?
значит web страница должна хранить в скрытых полях значения и цены и названия для первой (на странице) и последней (на странице) записи…
Переключили сортировку, значит прсто от другого ключа танцуем…
Переключили сортировку, значит прсто от другого ключа танцуем…
а кто вам сказал, что цена это ключ? И что она уникально? Может у меня на 5 страниц товаров с одинаковой ценой? Да еще и выводятся не все, а с определенным условием — только указанного раздела например.
Приведите мне пример из вашей практики, где ваше решение сработало бы.
Приведите мне пример из вашей практики, где ваше решение сработало бы.
А какая разница, есть ключ по цене или нет? Если есть, то будет использоваться, нет — будет медленнее работать.
А насчет 3 страниц с ценой — если поле не уникально, используйте
where цена >= граничное_значение_цены and pkey >граничное_значение_pkey order by цена, pkey
Мне кажется это очевидно…
А насчет 3 страниц с ценой — если поле не уникально, используйте
where цена >= граничное_значение_цены and pkey >граничное_значение_pkey order by цена, pkey
Мне кажется это очевидно…
>and pkey >граничное_значение_pkey
а теперь подумайте, почему это не будет работать
а теперь подумайте, почему это не будет работать
Да, очевидно не будет работать. Как -то в жизни всегда хватало ключей с нормльной селективностью. А не было — создавал искусственные.
Забавно. Вы отвечаете на конкретно поставленный вопрос, потом признаете, что ваш ответ не будет работать и еще пытаетесь что-то доказать.
Напоминаю вопрос. Он вполне жизненный, я с такой ситуацией постоянно сталкиваюсь
А если у меня сортировка может меняться. Вот в списке товаров интернет-магазина я могу поставить сортировку по цене или по названию. И какием запросом мне выводить товары для третьей страницы?
Напоминаю вопрос. Он вполне жизненный, я с такой ситуацией постоянно сталкиваюсь
А если у меня сортировка может меняться. Вот в списке товаров интернет-магазина я могу поставить сортировку по цене или по названию. И какием запросом мне выводить товары для третьей страницы?
И что Вам забавно? то что я признал свою неправоту? Тут так не принято?
Ну чтож… попытался упростить ответ, получилось плохо… Если Вам так нужно, могу привести решение без упрощения.
Итак.
У нас есть таблица, скажем prices,
create table goods (ix int auto_increment primary key, cost integer);
И есть составной индекс
create index ix1 (cost,ix)
У нас есть страница, в которой в hidden полях есть pkey и cost последней записи и этой страницы. Скажем они будут в переменных $ix и $cost когда пользователь тыркнет кнопку next.
При отрисовке страницы первая запись будет
handler goods open;
handler goods read ix1 = ($cost,$ix);
следующие N записей (длина страницы N+1)
handler goods read ix1 next limit $N;
handler goods close;
У последней записи естественно ix и cost сохраняем в hidden полях.
Интересно, а Вас то какое решение?
Ну чтож… попытался упростить ответ, получилось плохо… Если Вам так нужно, могу привести решение без упрощения.
Итак.
У нас есть таблица, скажем prices,
create table goods (ix int auto_increment primary key, cost integer);
И есть составной индекс
create index ix1 (cost,ix)
У нас есть страница, в которой в hidden полях есть pkey и cost последней записи и этой страницы. Скажем они будут в переменных $ix и $cost когда пользователь тыркнет кнопку next.
При отрисовке страницы первая запись будет
handler goods open;
handler goods read ix1 = ($cost,$ix);
следующие N записей (длина страницы N+1)
handler goods read ix1 next limit $N;
handler goods close;
У последней записи естественно ix и cost сохраняем в hidden полях.
Интересно, а Вас то какое решение?
«query('INSERT INTO table SET column = 1, id=1');»
но при этом строкой ниже:
«при условии наличия первичного или уникального ключа по полю id:»
какой смысл указывать явно id, если он PK?
«Для подобных случаев более быстрым бывает разбить подобный запрос на несколько простых.»
не нужно так категорично. оптимизация это вообще очень нечёткая штука, к каждой ситуации нужно подходить индивидуально и внимательно всё изучать
в целом — не понравилась слишком категоричная манера изложения «если так — то делайте так». ведь люди прочитают и будут бездумно следовать советам…
но при этом строкой ниже:
«при условии наличия первичного или уникального ключа по полю id:»
какой смысл указывать явно id, если он PK?
«Для подобных случаев более быстрым бывает разбить подобный запрос на несколько простых.»
не нужно так категорично. оптимизация это вообще очень нечёткая штука, к каждой ситуации нужно подходить индивидуально и внимательно всё изучать
в целом — не понравилась слишком категоричная манера изложения «если так — то делайте так». ведь люди прочитают и будут бездумно следовать советам…
UPDATE news SET
title = CASE
WHEN news_id = 1 THEN 'aa'
WHEN news_id = 2 THEN 'bb' END
WHERE news_id IN (1, 2)
порадовало. возьму на заметку такой способ. Спасибо
title = CASE
WHEN news_id = 1 THEN 'aa'
WHEN news_id = 2 THEN 'bb' END
WHERE news_id IN (1, 2)
порадовало. возьму на заметку такой способ. Спасибо
еще вариант, в InnoDB:
START TRANSACTION
BEGIN
… тут в цикле куча запросов update
COMMIT
в MyISAM, наверное, вместо транзакции можно использовать LOCK TABLE.
работает тоже в разы быстрее, чем просто куча update. С предложенным выше вариантом не сравнивал.
START TRANSACTION
BEGIN
… тут в цикле куча запросов update
COMMIT
в MyISAM, наверное, вместо транзакции можно использовать LOCK TABLE.
работает тоже в разы быстрее, чем просто куча update. С предложенным выше вариантом не сравнивал.
еще напишите для него обертку, типа function bulk_update(), чтобы не склеивать каждый раз строчку
Эта статья полезная, конечно, но какую версию mysql она описывает? четвёртую, наверно. В пятой версии mysql и php практически все приведённые примеры использовать вредно и опасно, например
row = query('SELECT * FROM table WHERE id = '.$rnd);
подвержен sql injections. Правильным будет запрос примерно такой:
row = query('SELECT * FROM table WHERE id = ?', [$rnd]);
и ещё более правильный — использовать
$sth = prepare('SELECT * FROM table WHERE id = ?'); и далее
$sth->execute($rnd);
Это защищает от ошибок и работает быстрее.
row = query('SELECT * FROM table WHERE id = '.$rnd);
подвержен sql injections. Правильным будет запрос примерно такой:
row = query('SELECT * FROM table WHERE id = ?', [$rnd]);
и ещё более правильный — использовать
$sth = prepare('SELECT * FROM table WHERE id = ?'); и далее
$sth->execute($rnd);
Это защищает от ошибок и работает быстрее.
В форме с '?' некоторые запросы выполняются в разы быстрее. Это не оптимизация?
Почему именно 5 — «Before MySQL 5.1.17, prepared statements do not use the query cache.» Неизвестно что лучше было — компилировать и placeholderы или по старинке.
Почему именно 5 — «Before MySQL 5.1.17, prepared statements do not use the query cache.» Неизвестно что лучше было — компилировать и placeholderы или по старинке.
если запрос на insert/delete/update то query cache в любом случае по барабану. Так что выигрыш от prepared statements может быть заметным, начиная, если мне не изменяет склероз, с 4.1.
другое дело что некоторые клиенсткие бибки работают (как это будет по русски) коряво с prepared statements. Можно на засаду нарваться.
другое дело что некоторые клиенсткие бибки работают (как это будет по русски) коряво с prepared statements. Можно на засаду нарваться.
prepared statements как раз в 4.1 и появились в полном объёме. Если пользоваться PDO — то никаких засад не будет. Perl (DBI) имеет предупреждение о засаде и её причинах и правилах обхода. Но работать надо именно с ними, иначе тратится время на компиляцию и оптимизацию. У меня опыт работы с mysql маленький, но в postgres и oracle изменения скорости просто огромные, и я не вижу почему бы того же не ожидать от mysql. (кстати, используя prepared statements с DBASE IV получаем десятикратный выигрыш). Анализ запросов форумов и CMS'ок показывает, что используются очень простые запросы, но каждый раз уникальные с точки зрения mysql.
Несколько сумбурно все.
Вообще правило написание нормальных запросов одно — смотрите explain.
Вообще правило написание нормальных запросов одно — смотрите explain.
спасибо, очень полезно, особенно ON DUPLICATE KEY всегда с этим мучался и думал как лучше сделать.
А что на счёт вставки нескольких строк одним запросом, как узнать все insert_id?
А что на счёт вставки нескольких строк одним запросом, как узнать все insert_id?
SELECT COUNT(field) FROM table
разве не будет быстрее чем
SELECT COUNT(*) FROM table
при условии, что field — уникальный ключ таблицы?
разве не будет быстрее чем
SELECT COUNT(*) FROM table
при условии, что field — уникальный ключ таблицы?
Дополнение(процитирую себя):
«Зачастую при добавление в таблицу, имеющей UNIQUE индекс или PRIMARY KEY, новой строки, очень полезным бывает синтаксис
INSERT IGNORE. Использование данного синтаксиса удобно в случае случайного дублирования ключа при вставке,
то есть сама вставка не будет произведена, при этом не будет прекращенно выполнение.
Это очень выручает при оптимизации приложения, когда мы вместо двух запросов:
1- проверить наличие строки в таблице по ключу (SELECT)
2- вставить строку в случае отсутствия дублирования ключа (INSERT)
будем использовать только один запорс INSERT IGNORE.»
2 %tuta_larson%:
Юджин, опять забыл про это написать=)
«Зачастую при добавление в таблицу, имеющей UNIQUE индекс или PRIMARY KEY, новой строки, очень полезным бывает синтаксис
INSERT IGNORE. Использование данного синтаксиса удобно в случае случайного дублирования ключа при вставке,
то есть сама вставка не будет произведена, при этом не будет прекращенно выполнение.
Это очень выручает при оптимизации приложения, когда мы вместо двух запросов:
1- проверить наличие строки в таблице по ключу (SELECT)
2- вставить строку в случае отсутствия дублирования ключа (INSERT)
будем использовать только один запорс INSERT IGNORE.»
2 %tuta_larson%:
Юджин, опять забыл про это написать=)
Для меня полезными оказались:
1. Аналогичный пример:
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);
будет.
и исправьте плиз в предыдущем примере на «blogs_count = $value / 2;»
2. Но если количество записей велико, и нужно выполнить запрос SELECT… FROM table LIMIT 1000000, 1000020, то для выполнения такого запроса MySQL сначала выберет 1000020 записей, отбросит первый миллион и вернет 20. Это может быть совсем не быстро. Тривиальных путей решения проблемы нет.
Согласен с habrahabr.ru/blogs/mysql/41968/#comment_1031853, что статью надо назвать не «Оптимизация запросов» (таких тысячи), а как-то по-оригинальнее, типа «Плохие запросы и как их сделать хорошими».
Успехов.
1. Аналогичный пример:
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);
будет.
и исправьте плиз в предыдущем примере на «blogs_count = $value / 2;»
2. Но если количество записей велико, и нужно выполнить запрос SELECT… FROM table LIMIT 1000000, 1000020, то для выполнения такого запроса MySQL сначала выберет 1000020 записей, отбросит первый миллион и вернет 20. Это может быть совсем не быстро. Тривиальных путей решения проблемы нет.
Согласен с habrahabr.ru/blogs/mysql/41968/#comment_1031853, что статью надо назвать не «Оптимизация запросов» (таких тысячи), а как-то по-оригинальнее, типа «Плохие запросы и как их сделать хорошими».
Успехов.
Переделка плохих запросов в хорошие это и есть оптимизация, только одним словом ;-)
Так мы всю жизнь занимаемся оптимизацией: делаем мир лучше :-p
Я про то, что каждой статье полезно иметь более-менее уникальный и запоминающийся заголовок, чтобы потом было легче найти.
Если я потом захочу вернутсья сюда и введу в гугле «Оптимизация запросов», то вряд ли я дойду до этой статьи. А вот если я введу «Плохие запросы и как их сделать хорошими», то статья вероятно будет в первых строчках.
Я про то, что каждой статье полезно иметь более-менее уникальный и запоминающийся заголовок, чтобы потом было легче найти.
Если я потом захочу вернутсья сюда и введу в гугле «Оптимизация запросов», то вряд ли я дойду до этой статьи. А вот если я введу «Плохие запросы и как их сделать хорошими», то статья вероятно будет в первых строчках.
(сорри, ваш коммент шёл при просмотре страницы последним и я нажал не туда)
Наконец-то хорошая техническая статья на хабре!
Половину знал, половина — новое. Зачитался мануалами =)
Спасибо!
Половину знал, половина — новое. Зачитался мануалами =)
Спасибо!
не MySQL — тормоз, а руки у людей бывают кривые… так наверно правильнее будет…
Ага. вот например запрос из старого wp для выборки постов с указаными тэгами. На дедике отрабатывал за две-три минуты :)
SELECT DISTINCT p.post_title, p.comment_count, p.post_date, p.ID, COUNT(tr.object_id) AS counter
FROM wp_posts AS p
INNER JOIN wp_term_relationships AS tr ON (p.ID = tr.object_id)
INNER JOIN wp_term_taxonomy AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE tt.taxonomy = 'post_tag'
AND (tt.term_id IN («54», «42», «30», «19»))
AND p.ID <> 4818
AND p.post_status = 'publish'
AND p.post_date < '2008-07-15 12:40:08'
AND p.post_type IN ('page', 'post')
GROUP BY tr.object_id
ORDER BY counter DESC, p.post_title DESC
LIMIT 0, 5
SELECT DISTINCT p.post_title, p.comment_count, p.post_date, p.ID, COUNT(tr.object_id) AS counter
FROM wp_posts AS p
INNER JOIN wp_term_relationships AS tr ON (p.ID = tr.object_id)
INNER JOIN wp_term_taxonomy AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE tt.taxonomy = 'post_tag'
AND (tt.term_id IN («54», «42», «30», «19»))
AND p.ID <> 4818
AND p.post_status = 'publish'
AND p.post_date < '2008-07-15 12:40:08'
AND p.post_type IN ('page', 'post')
GROUP BY tr.object_id
ORDER BY counter DESC, p.post_title DESC
LIMIT 0, 5
Обычно так пишут люди не знающие предмета обсуждения.
Насчет «Неиспользование ON DUPLICATE KEY UPDATE», есть же в mysql REPLACE.
Цитата: «Оператор REPLACE работает точно так же, как INSERT, за исключением того, что если старая запись в данной таблице имеет то же значение индекса UNIQUE или PRIMARY KEY, что и новая, то старая запись перед занесением новой будет удалена. „
Цитата: «Оператор REPLACE работает точно так же, как INSERT, за исключением того, что если старая запись в данной таблице имеет то же значение индекса UNIQUE или PRIMARY KEY, что и новая, то старая запись перед занесением новой будет удалена. „
1. Лучше переименовать заголовок в anySQL
2. По INNER JOIN table USING уточняйте обязательно версию СУБД и ее марку, ибо на старых это может на канать (желательно вообще все СУБД перечислить, где это будет работать или конкретный диалект ANSI SQL`я дать)
3. CASE WHEN для UPDATE`а — крайне неудачный совет. Ибо UPDATE`ы случаются гораздо реже и логика просачивается в данные, что есть коряво.
4. Аналог LIMIT`а в MSSQL`е: select top 10 Name, Surname from Person order by PersonID /* первые 10 */ или select * from (select top 10 Name, Surname from Person order by PersonID DESC) TMP order by PersonID /* последние 10 */
/* Промежуточные сами додумайте :) Хотя наверное в 2008 это уже по-человечьи сделали */
5. Также неудачный совет про «auto_increment'ный первичный ключ и нет пропусков» ибо это только в демоверсиях так :) Как говаривал Конфуций: «Всё течет, все удаляется»
6. Тьфу, бла, это перевод… Афтар! Убей сибя ап стены, т. е. юзай топик-ссылки… :(
2. По INNER JOIN table USING уточняйте обязательно версию СУБД и ее марку, ибо на старых это может на канать (желательно вообще все СУБД перечислить, где это будет работать или конкретный диалект ANSI SQL`я дать)
3. CASE WHEN для UPDATE`а — крайне неудачный совет. Ибо UPDATE`ы случаются гораздо реже и логика просачивается в данные, что есть коряво.
4. Аналог LIMIT`а в MSSQL`е: select top 10 Name, Surname from Person order by PersonID /* первые 10 */ или select * from (select top 10 Name, Surname from Person order by PersonID DESC) TMP order by PersonID /* последние 10 */
/* Промежуточные сами додумайте :) Хотя наверное в 2008 это уже по-человечьи сделали */
5. Также неудачный совет про «auto_increment'ный первичный ключ и нет пропусков» ибо это только в демоверсиях так :) Как говаривал Конфуций: «Всё течет, все удаляется»
6. Тьфу, бла, это перевод… Афтар! Убей сибя ап стены, т. е. юзай топик-ссылки… :(
USING( сработает только в случае одинаковых названий столбцов
рекурсии и циклы и при запросах исключены, в этом я согласен
INSERT INTO logs (value) VALUES (...), (...) не работает если много строчек!!! кто не скажет заветную цифру того сколько мускул может сожрать за раз буду благодарен — если она конечно же одинакова везде что мало вероятно).
все равно статья здравая для новичков очень сгодится
рекурсии и циклы и при запросах исключены, в этом я согласен
INSERT INTO logs (value) VALUES (...), (...) не работает если много строчек!!! кто не скажет заветную цифру того сколько мускул может сожрать за раз буду благодарен — если она конечно же одинакова везде что мало вероятно).
все равно статья здравая для новичков очень сгодится
Мда, опять разочаровался в качестве написанного. В комментарияю в б0льшей части правильно сказано — если так — делай вот так. Глупо это.
Подскажите плиз, оч нужно, можно ли оптимизировать след запрос:
Таблица с 3,5 млн записей, идет выборка по дате, где дата больше такого-то числа, по этому полю есть индекс. скрипт запускается в кроне и перебирает все с limit 0, 100 и т.д.
explain показывает что проходится по 2,7 млн строкам, можно ли оптимизировать как-то???
Таблица с 3,5 млн записей, идет выборка по дате, где дата больше такого-то числа, по этому полю есть индекс. скрипт запускается в кроне и перебирает все с limit 0, 100 и т.д.
explain показывает что проходится по 2,7 млн строкам, можно ли оптимизировать как-то???
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Оптимизация MySQL запросов