Комментарии 34
Неплохо, в закладки. спасибо
А как же всё связанное EXPLAIN? Без этого получается однобоко — результат был бы более наглядно.
А что насчет хаков по принуждению тупого оптимизатора к использованию конкретных индексов?
Но спасибо, людей носом тыкать в одну ссылку гораздо удобнее, перечислены ключевые вещи.
А что насчет хаков по принуждению тупого оптимизатора к использованию конкретных индексов?
Но спасибо, людей носом тыкать в одну ссылку гораздо удобнее, перечислены ключевые вещи.
Можно в следующих сериях рассмотреть, конечно
А это где вы встретили тупой оптимизатор? :)
в Mysql некоторых версий приходилось дополнительно уговаривать использовать специально созданные индексы просто потому, что это начинало работать быстрее.
http://dev.mysql.com/doc/refman/5.7/en/index-hints.html
http://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html
конструкции пока в движке еще есть, так что кому-то это нужно,
но я сейчас уже не работаю с тем проектом, где жить без этого было медленно, так что реальных примеров не смогу дать.
http://dev.mysql.com/doc/refman/5.7/en/index-hints.html
http://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html
конструкции пока в движке еще есть, так что кому-то это нужно,
но я сейчас уже не работаю с тем проектом, где жить без этого было медленно, так что реальных примеров не смогу дать.
Иногда бывает удобно принудительно указывать на индекс в случае работы с большими таблицами и когда имеется несколько «частичных» индексов (partial index). Оптимизатор тут может долго гадать какой использовать или просто выбрать самый большой индекс, чтобы уж наверняка.
В email lists PostgreSQL была в своё время одна светлая мысль, которая звучала вкратце — «не считайте себя умнее планирощика запросов».
В 99,9% это работает. Возможно и с MySQL тоже.
Если немного расширить — «сначала разберитесь, почему планировщик выбрал именно этот индекс или не выбрал вообще никакой и, возможно, поймёте, почему вы были неправы».
В 99,9% это работает. Возможно и с MySQL тоже.
Если немного расширить — «сначала разберитесь, почему планировщик выбрал именно этот индекс или не выбрал вообще никакой и, возможно, поймёте, почему вы были неправы».
Большая таблица(миллионы строк), в запросе множество условий(и под них попадает большая часть данных), приправленные LIKE и в конце ODER BY date DESC LIMIT 20.
Оптимизатор пытается подобрать индекс для отсеивания по части условий(меньшее из больших подмножеств), но потом при исполнении нужно проверять остальные и сортировать результат( в выбранном индексе date может присутствовать, но до него поле, где получился range)
Если же ему подсказать использовать индекс date — будет читать строки по нему и в 95% случаев получит желаемые 20 проверив меньше 100, хоть и нужно проверять все условия.
Да, причина в несоответствии структуры хранения «эволюционировавшим» условиям выборок, но работать должно сейчас, а отрефакторят быть может в следующей жизни.
Оптимизатор пытается подобрать индекс для отсеивания по части условий(меньшее из больших подмножеств), но потом при исполнении нужно проверять остальные и сортировать результат( в выбранном индексе date может присутствовать, но до него поле, где получился range)
Если же ему подсказать использовать индекс date — будет читать строки по нему и в 95% случаев получит желаемые 20 проверив меньше 100, хоть и нужно проверять все условия.
Да, причина в несоответствии структуры хранения «эволюционировавшим» условиям выборок, но работать должно сейчас, а отрефакторят быть может в следующей жизни.
Со временными таблицами нужно быть осторожным. Во-первых, СУБД может сама решить создать временный индекс и(или) будет использовать знания о сортировке. Во-вторых, в какой-то момент СУБД может решить изменить in-memory таблицу на дисковую. В третьих, размер временной таблицы может неожиданно превысить размер раздела для временных файлов, а установление этого факта может оказаться не тривиальным.
Извините, конечно, но это рекомендации начального уровня.
Если в системе, напротив которой выполняется нагрузочное тестирование, они не были изначально выполнены — то это значит, что перед нами — однопользовательский проект студенческого уровня, а не бакенд системы массового обслуживания.
Если в системе, напротив которой выполняется нагрузочное тестирование, они не были изначально выполнены — то это значит, что перед нами — однопользовательский проект студенческого уровня, а не бакенд системы массового обслуживания.
Повсеместная проблема состоит в том, что все обычно забывают про базовые моменты :)
В таком случае, эти «все» — поголовно студенты. Хотя в этом вы правы — в процессе вертикальной разработки систем типичен холивар по поводу naming convention, coding style и иерархии классов, а на продумывание и проектирование хорошего бакенда времени почему-то не остается. Code first, и вперед!
Чаще всего эти проблемы возникают именно у «опытных». При чем, что характерно, студенты просто ошибаются — ну не набил он ещё этих шишек, это простительно. А вот опытные разработчики часто не делают из принципа. Любимые мантры «эта таблица никогда не вырастет», «индекс увеличивает время апдейта» (играет в кепа, но расчитать статистику не способен) и «да зачем нужен индекс на родительской таблице под форейн кеем, я никогда этого не делаю» (детская ошибка с тяжкими последствиями в Оракл).
Еще часто слышно:
1) «Этим все должна заниматься ORM»
2) «Надо просто увеличить кэш»
3) «Надо просто нарастить мощности»
4) «Это недо-БД. Вот postgres|oracle|MSSQL|… с этим сама справится»
5) «Некогда этим заниматься»
1) «Этим все должна заниматься ORM»
2) «Надо просто увеличить кэш»
3) «Надо просто нарастить мощности»
4) «Это недо-БД. Вот postgres|oracle|MSSQL|… с этим сама справится»
5) «Некогда этим заниматься»
1) Нужно пояснить, что речь идет про MySQL. Для других СУБД советы могут быть неверными.
2)
Это актуально только если это улучшает использование индексов (например, при использовании двух полей индекса вместо одного.) и тем самым уменьшает количество записей, читаемых из таблицы.
Но в случае с неравенством это редко бывает.
Если же улучшения использования индексов не происходит, то смысла в этой проверке нет, так как MySQL все равно ее производит самостоятельно и не перезаписывает неизменённые записи.
2)
Убедитесь, что операция WHERE не перезаписывает одинаковые значения.
Это актуально только если это улучшает использование индексов (например, при использовании двух полей индекса вместо одного.) и тем самым уменьшает количество записей, читаемых из таблицы.
Но в случае с неравенством это редко бывает.
Если же улучшения использования индексов не происходит, то смысла в этой проверке нет, так как MySQL все равно ее производит самостоятельно и не перезаписывает неизменённые записи.
Тоже удивился когда прочел пост, звучит как общие рекомендации однако например совет 2 не верен для MS SQL, поскольку практически всегда кореллирующий подзапрос преобразуется к OUTER JOIN.
Насчет EXISTS разница с SELECT TOP 1 будет не большая, а сам пример выглядит как хороший вредный совет при использовании LIKE и индексов. Нужно стараться избегать LIKE начинающихся с %.
По факту из полезных советов только используйте индексы и объединяйте кучу мелких запросов в один, все остальное микрооптимизации которые врядли дадут буст производительности и являются скорее хорошим стилем.
Насчет EXISTS разница с SELECT TOP 1 будет не большая, а сам пример выглядит как хороший вредный совет при использовании LIKE и индексов. Нужно стараться избегать LIKE начинающихся с %.
По факту из полезных советов только используйте индексы и объединяйте кучу мелких запросов в один, все остальное микрооптимизации которые врядли дадут буст производительности и являются скорее хорошим стилем.
Не использовать коррелирующие подзапросы — самый полезный совет во всей статье. Они я вам скажу очень не всегда правильно раскладываются оптимизатором.
p.s. У меня есть три любимых пункта на тему «как максимально усложнить жизнь себе, оптимизатору и вселенной»
1. Используйте коррелированные подзапросы — много, да еще и желательно вложенные в друг друга.
2. Используйте инлайн функции, особенно хорошо когда они тоже сами вызывают функции.
3. Используйте триггера, тоже сами понимаете лучше вызывающие срабатывание других триггеров и т.д.
p.s. У меня есть три любимых пункта на тему «как максимально усложнить жизнь себе, оптимизатору и вселенной»
1. Используйте коррелированные подзапросы — много, да еще и желательно вложенные в друг друга.
2. Используйте инлайн функции, особенно хорошо когда они тоже сами вызывают функции.
3. Используйте триггера, тоже сами понимаете лучше вызывающие срабатывание других триггеров и т.д.
Коррелирующие подзапросы часть языка не использовать их только потому, что оптимизатор может ошибиться мне кажется глупым. Запрос как и любая программа должен хорошо читаться, если для выразительности подзапрос выглядит лучше объединения, я буду использовать подзапрос. Когда же мы говорим о производительности, запрос сам по себе не может быть хорошим или плохим, всегда плохая производительность результат неудачного плана, а тот в свою очередь результат множества факторов из которых такой простой рефакторинг как замена subquery на join очень редко являются первопричиной (буду говорить только об оптимизаторе MSSQL поскольку плотно работал только с ним). Возможно для других движков имеют место подобные проблемы, тогда и говорить о подобных советах следует в контексте конкретного движка В общем случае только план запроса может быть необходимым и достаточным условием для оптимизации запроса.
1. Осторожно с индексами. Они должны быть продуманы. Бывает ускоришь один селект, а десять упадут.
2. Bulk Insert'ы для загрузки таблицы. Если это еще и процесс например миграции, то желательно снести все индексы перед инсертами.
6. На счет count vs exists можно ограничить count по rownumber = 1. Так же exists может быть намного быстрее чем конструкция IN (), но может и не быть.
7. Писать такие запросы, которые кэшируются и не требуют парсинга каждый раз, когда выполняются. Как пример preparedstatement
В целом надо смотреть на планы.
2. Bulk Insert'ы для загрузки таблицы. Если это еще и процесс например миграции, то желательно снести все индексы перед инсертами.
6. На счет count vs exists можно ограничить count по rownumber = 1. Так же exists может быть намного быстрее чем конструкция IN (), но может и не быть.
7. Писать такие запросы, которые кэшируются и не требуют парсинга каждый раз, когда выполняются. Как пример preparedstatement
В целом надо смотреть на планы.
Еще подготовленные запросы — очень полезная штука.
И про лимиты не стоит забывать.
А вообще, если требуется серьезно поднять производительность работы БД(без повышения производительности аппаратной части), лучшим будет изучить хотя бы поверхностно, как ведет себя конкретная БД в определенных случаях(в транзакции; при первичной записи; что и в каких случаях попадает в кэш БД; как работает журналирование;… прочее...). Чуточку в настройках покопаться. И разобраться, как все вышеперечисленное между собой связано.
Да и в общем особенности реализации конкретной БД.
Многие оптимизации сами придут в голову, при понимании работы конкретной БД.
//------------------------
Ну и на мой взгляд, под производительностью стоит подразумевать не только скорость обработки запросов, но и объем потребляемых ресурсов. Бывает и такое, что ваш не самый частый запрос стал обрабатываться чуток быстрее, но выбил из кэша много полезного и часто используемого.
И про лимиты не стоит забывать.
А вообще, если требуется серьезно поднять производительность работы БД(без повышения производительности аппаратной части), лучшим будет изучить хотя бы поверхностно, как ведет себя конкретная БД в определенных случаях(в транзакции; при первичной записи; что и в каких случаях попадает в кэш БД; как работает журналирование;… прочее...). Чуточку в настройках покопаться. И разобраться, как все вышеперечисленное между собой связано.
Да и в общем особенности реализации конкретной БД.
Многие оптимизации сами придут в голову, при понимании работы конкретной БД.
//------------------------
Ну и на мой взгляд, под производительностью стоит подразумевать не только скорость обработки запросов, но и объем потребляемых ресурсов. Бывает и такое, что ваш не самый частый запрос стал обрабатываться чуток быстрее, но выбил из кэша много полезного и часто используемого.
Оптимизатор может делать все это за вас, а вы пытаясь переписать код в пустуе потратите время. Оптимизатор зачастую отлично справится с COUNT и построит такой же план как и для EXISTS.
IF (SELECT count(*) FROM Users WHERE active = 1) > 0
|--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1006] = [PROBE VALUE]))
|--Constant Scan
|--Index Scan(OBJECT:([TestDB].[dbo].[Users].[IX_Users_Email]), WHERE:([TestDB].[dbo].[Users].[Active]=(1)))
IF EXISTS (SELECT 1 FROM Users WHERE active = 1)
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1005] = [PROBE VALUE]))
|--Constant Scan
|--Index Scan(OBJECT:([TestDB].[dbo].[Users].[IX_Users_Email]), WHERE:([TestDB].[dbo].[Users].[Active]=(1)))
Свободно преобразует подзапрос к джойну:
select top 100 CourseRegistrationID, (SELECT Email FROM Users WHERE UserID = cr.UserID) FROM CourseRegistrations cr
|--Top(TOP EXPRESSION:((100)))
|--Compute Scalar(DEFINE:([Expr1007]=[TestDB].[dbo].[Users].[Email]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([cr].[UserID], [Expr1008]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([TestDB].[dbo].[CourseRegistrations].[IX_CourseRegistrations_UserID] AS [cr]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([TestDB].[dbo].[Users].[PK_Users]), SEEK:([TestDB].[dbo].[Users].[UserID]=[TestDB].[dbo].[CourseRegistrations].[UserID] as [cr].[UserID]) ORDERED FORWARD)
Оптимизировать не имея статистики и не глядя в планы исполнения изначально является глупой затеей. Соберите статистику, проанализируйте план запроса и только после этого начинайте оптимизацию.
Если условия слишком сильно зависят от параметров, а параметры могут влиять на логику, в таком случае иногда выгоднее иметь не preparedstatement, а динамически сформированный запрос.
IF (SELECT count(*) FROM Users WHERE active = 1) > 0
|--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1006] = [PROBE VALUE]))
|--Constant Scan
|--Index Scan(OBJECT:([TestDB].[dbo].[Users].[IX_Users_Email]), WHERE:([TestDB].[dbo].[Users].[Active]=(1)))
IF EXISTS (SELECT 1 FROM Users WHERE active = 1)
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1005] = [PROBE VALUE]))
|--Constant Scan
|--Index Scan(OBJECT:([TestDB].[dbo].[Users].[IX_Users_Email]), WHERE:([TestDB].[dbo].[Users].[Active]=(1)))
Свободно преобразует подзапрос к джойну:
select top 100 CourseRegistrationID, (SELECT Email FROM Users WHERE UserID = cr.UserID) FROM CourseRegistrations cr
|--Top(TOP EXPRESSION:((100)))
|--Compute Scalar(DEFINE:([Expr1007]=[TestDB].[dbo].[Users].[Email]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([cr].[UserID], [Expr1008]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([TestDB].[dbo].[CourseRegistrations].[IX_CourseRegistrations_UserID] AS [cr]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([TestDB].[dbo].[Users].[PK_Users]), SEEK:([TestDB].[dbo].[Users].[UserID]=[TestDB].[dbo].[CourseRegistrations].[UserID] as [cr].[UserID]) ORDERED FORWARD)
Оптимизировать не имея статистики и не глядя в планы исполнения изначально является глупой затеей. Соберите статистику, проанализируйте план запроса и только после этого начинайте оптимизацию.
Если условия слишком сильно зависят от параметров, а параметры могут влиять на логику, в таком случае иногда выгоднее иметь не preparedstatement, а динамически сформированный запрос.
--Пример выше можно переделать, используя один INSERT или UPDATE
И тут вы быстро упретесь в ограничение на размер запроса в 8 Кб.
Лучше всего пихать в xml и передавать как параметер, тогда и 2 Тб можно вставить одним запросом.
C#:
И тут вы быстро упретесь в ограничение на размер запроса в 8 Кб.
Лучше всего пихать в xml и передавать как параметер, тогда и 2 Тб можно вставить одним запросом.
C#:
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO CoreAccount SELECT X.C.value('@name', 'nvarchar(255)') FROM (SELECT @data AS XML_DATA) DATA CROSS APPLY DATA.XML_DATA.nodes('//value') as X(C) ";
cmd.Parameters.Add("@data", SqlDbType.Xml);
cmd.Parameters[0].Value = userList;
cmd.ExecuteNonQuery();
}И тут вы быстро упретесь в ограничение на размер запроса в 8 Кб.Это где такое ограничение?
По крайней мере, в самом MySQL такого ограничения нет.
Точнее, есть max_allowed_packet, но у него дефолтное значение 1 или 4 мегабайта (зависит от версии MySQL).
Вы пишите о ограничениях сервера, а есть еше ограничения библиотек. ODBC, JDBC, ADO,…
Там бывает все печальнее.
Там бывает все печальнее.
2 тб так вставлять тоже не стоит. Bulk load наш выбор
2 Тб это конечно гротеск, но из реального — пришлось недавно напистаь сервис, что в идеале должен раз в сутки вставлять список из миллиона аккаунтов. никаког bulk разумеется в той архитектуре не предвидится.
Пир средней длине имени 10 символов (тестирпвал twitter) это примерно 30М в конечном файле с UTF-8.
Пир средней длине имени 10 символов (тестирпвал twitter) это примерно 30М в конечном файле с UTF-8.
Пользуйтесь временными таблицами с умом
Не знаю как в MySQL, но в MS SQL вместо
create table #tempTable (id int) ... drop table #tempTable
лучше использовать table variable
declare @temp table(id int)
А если ещё использовать тип, то можно быть 100% уверенным в том, что таблица будет in memory.
На самом деле в MSSQL table variable и temp table хранятся в temp db и имеют очень мало отличий (речь не идет о использовании типов). Из главных отличий (в моей практике) это отсутствие статистики для table variable, в большенстве случае вы будете получать estimate в 1 запись, особенно это критично в сторед процедурах. Так же хочу заметить что записи в table variable не являются частью пользовательской транзакции. Наверное самый толковый пост по сравнению этих объектов (там же есть ссылка на Memory-Optimized Table Types) What's the difference between a temp table and table variable in SQL Server?
Очень много споров и обсуждений касательно COUNT(1) or COUNT(*)?
Я заметил, что вы используете COUNT(1).
Прокомментируйте пожалуйста, что же всё таки предпочтительнее/быстрее? * or 1 ?)
Я заметил, что вы используете COUNT(1).
Прокомментируйте пожалуйста, что же всё таки предпочтительнее/быстрее? * or 1 ?)
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Немного о повышении производительности БД: Практические советы