Вопросы по теории mysql?

Question

[Hfnas]

1) Если нужно сортировать по каждому столбцу каждой таблицы , то индекс лучше не создавать по каждому столбцу? Есть таблица, кликаем на заголовок таблицы. идет сортировка.
2) Сколько по времени должны выполняться все запросы на одной странице?
3) Как правильно составить запросы, сначала нужно исследовать запрос при помощи explain, а потом писать в php?
4)Нужно составлять запросы так, чтобы explain: type<>ALL
5) Подскажите, пожалуйста, хороший ресурс про создание индексов.

Comments

[Zhainar]

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

[Hfnas]

Zhainar, не могли бы посоветовать Хорошую книжку? проблема в основном , в подаче автора. Если написано замудренно, я мало ли что пойму, хоть читай несколько раз, все равно не поймешь.-(

[Zhainar]

Hfnas, читал только MySQL Оптимизация производительности (кроме части про администрирование), очень понравилась

Answer 1

[ThunderCat]

Есть таблица, кликаем на заголовок таблицы. идет сортировка

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

Сколько по времени должны выполняться все запросы на одной странице?

В общем случае запросы должны выполняться за время меньше секунды, близкое к 1 секунде уже считается весьма высоким. Исключения составляют приложения с очень сложной логикой, где ожидание ответа не сильно меняет ценность результата. Например на сайте новостей 1 секунда это много, а на приложении с миллиардами записей статистики по мировым продажам жвачки в розницу для планирования расширения сети продаж с 30 объединениями/группировками/дистинкт выборками надцать секунд или даже минут это норм, так как задача другая совершенно.

Как правильно составить запросы, сначала нужно исследовать запрос при помощи explain, а потом писать в php?

сначала пишется запрос с требуемым результатом, затем его можно оптимизировать. Так как оптимизация редко идет по пути изменения самого запроса, переписывать его не надо, а explain подскажет где можно подкрутить что-то на уровне индексов/памяти, код при этом вообще не трогается. Да и по большому счету 99% типовых задач не пишутся руками, а пускаются через модель/орм.

Нужно составлять запросы так, чтобы explain: type<>ALL

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

Подскажите, пожалуйста, хороший ресурс про создание индексов.

Дока по используемой бд достаточно хорошо описывает работу с индексами. Более подробно можно спросить ребят профильно занимающихся работой с бд, Akina, Лентюй ...

Comments

[Hfnas]

ThunderCat, большое спасибо за развернутый и подробный ответ от Души!!!
Можно уточнить некоторые нюансы?

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

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

Или если у меня веб-странице есть таблица Users, при клике на заголовок которой меняется сортировка по убыванию/возрастанию, то нужно создавать индекс на каждый столбец данной таблицы?

Аналогично, создать индекс на поле присоединяемой таблицы посредством джойна? (Например, у меня таблица Users(поля сущности:"ид", "логин", "пароль", "адрес"(ид на таблицу Address, foreign key)),есть сущность Address(поля: ид, наименование), присоединена к сущности Users как OnyToMany. Естествеено, если я вывожу таблицу пользователи("логин", "наименование адреса") на веб-странице, я добавляю индекс и на поле "Наименование" сущности Address). Повторюсь, мне нужно отсортировать "Логин" , "Наименование адреса" на веб-странице.

Просто я вычитала в доках, что сортировка -делает временную таблицу. А я постоянно применяю сортировку в своем проекте, в тч и для расчета статистистики, и чаще всего в join-таблицах (как в случае адреса в таблице пользователи). И поэтому, правильный путь, это создать индексы на поля, которые нужно отсортировать, в тч и в join таблицах? explain показывает Using filesort, Using temporary , что очень плохо. Наверное, нужно сделать так, чтобы не показывал, добавлением индексов, верно?

Например на сайте новостей 1 секунда это много, а на приложении с миллиардами записей статистики по мировым продажам жвачки в розницу для планирования расширения сети продаж с 30 объединениями/группировками/дистинкт выборками надцать секунд или даже минут это норм, так как задача другая совершенно.

спасибо! За подробный ответ.

а explain подскажет где можно подкрутить что-то на уровне индексов/памяти, код при этом вообще не трогается. Да и по большому счету 99% типовых задач не пишутся руками, а пускаются через модель/орм.

у меня страница не превышает в запросах более 1с, но, если посмотреть профайлер симфони,explain, то я вижe что type=ALL , значит, запросы нужно оптимизировать: добавить индексы. По поводу памяти, в первые слышу, в роликах на ютубе про индексы про память не говорилось, может, быть вы имели ввиду править конфиг mysql? Пожалуйста, подскажите, в каком направлении копать, по "память"? Что погуглить?

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

Правильно ли я понимаю, если индексы не спасают проблему(долго выполняются), нужно настраивать сервер?

[ThunderCat]

Hfnas,

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

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

Или если у меня веб-странице есть таблица Users, при клике на заголовок которой меняется сортировка по убыванию/возрастанию, то нужно создавать индекс на каждый столбец данной таблицы?

На каждый столбец, по которому идет сортировка или объединение нужен индекс.

По поводу памяти, в первые слышу,

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

Правильно ли я понимаю, если индексы не спасают проблему(долго выполняются), нужно настраивать сервер?

Сервер нужно всегда настраивать, так как профиль нагрузки на базу всегда разный. На простых проектах с небольшой общей нагрузкой можно забить, разницы между 0,01 и 0,1 секунды пользователь не заметит, но на больших проектах это критично.

[Hfnas]

ThunderCat, Доброго Вам вечера!
Вот такое большущее СПАСИБО Вам за столь подробный ответ!!!!

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

Буду гуглить "Кэш запросов, индексов, временные таблицы , чтобы в память поставить."