Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 49
Интересная инфографика. Есть, кстати, замечательный сайт по теме — вот. Лично я с помощью него постигал реляционные БД =)
P.S. Респект за отсутствие воды.
P.S. Респект за отсутствие воды.
Можно узнать параметры железа, на котором тестировали?
Хочу сравнить со своей СУБД.
15-20 минут, по-моему, слишком много.
Хочу сравнить со своей СУБД.
15-20 минут, по-моему, слишком много.
На скорость вставки данных индексы не влияютВсегда ли?
А можно посмотреть план выполнения запроса для большой селективности?
неверный выводы т.к. план запроса строит оптимизатор а он учитывает не только наличие/отсутствие индексов (например он может брать данные из кеша, частично индексировать на лету, повторно использовать результаты предидущих запросов и т.д.).
— индексы нужны всегда и почти везде (ваш К.О.)
— на скорость вставки индексы влияют всегда (не всегда значительно)
— при выборке большого количества строк непосредственно чтение результата займёт больше времени чем сам поиск
— не знаю что такое вычислимые поля но выигрыш индексирования зависит от типа поля
об этом учебники пишут. В таком виде статьи скорей вредны чем полезны.
— индексы нужны всегда и почти везде (ваш К.О.)
— на скорость вставки индексы влияют всегда (не всегда значительно)
— при выборке большого количества строк непосредственно чтение результата займёт больше времени чем сам поиск
— не знаю что такое вычислимые поля но выигрыш индексирования зависит от типа поля
об этом учебники пишут. В таком виде статьи скорей вредны чем полезны.
Кстати, про вставку я вас поддержу. Влияют и очень влияют. Проверено недавним добавлением индекса в таблицу с 4 млн.записей. Сильно глубоко в тестирование не влазили. Но на глаз ускорение в несколько десятков раз.
Дополню так же тем, что тестить желательно полноценно. Производительность всегда определяется двумя параметрами: пропускная способность и время ответа. И при тесте производительности желательно смотреть на обе метрики.
Буквально этой ночью писал статью как тестировать базы данных с помощью jmeter, если кому-то интересно смотрите тут http://schiz.me/blog/2013/01/04/mysql-load-testing/. Все акутуально и для MS SQL, но нужно поставить соотв. драйвер.
Кроме того, как было замечено выше, индекс влияет на запись. Было бы здорово сравить тогда уж и производительность без и с индексом:)
Буквально этой ночью писал статью как тестировать базы данных с помощью jmeter, если кому-то интересно смотрите тут http://schiz.me/blog/2013/01/04/mysql-load-testing/. Все акутуально и для MS SQL, но нужно поставить соотв. драйвер.
Кроме того, как было замечено выше, индекс влияет на запись. Было бы здорово сравить тогда уж и производительность без и с индексом:)
Скорость записи без индекса и с ним получилась одинаковая, только надо индексы периодически перестраивать:)
Сразу отмечу, что я не знаток MSSQL.
В других СУБД есть понятие отложенной перестройки индекса, т.е. вы данные модифицируете, но индекс при этом не пересчитывается.
Обычно используется при BULK insert.
Но чаще — как правило по умолчанию — индекс перестраивается немедленно при изменении данных.
И на это серверу просто необходимо потратить дополнительное время.
Какая стратегия постройки индекса была использована вами?
IMHO:
Выборка по первичному ключу должна происходить мгновенно, а не за 1,5 с. и даже не за 0,1 с.
Все приведённые цифры теста для меня слишком велики даже с поправкой на железо и VM, а потому сомнительны.
В других СУБД есть понятие отложенной перестройки индекса, т.е. вы данные модифицируете, но индекс при этом не пересчитывается.
Обычно используется при BULK insert.
Но чаще — как правило по умолчанию — индекс перестраивается немедленно при изменении данных.
И на это серверу просто необходимо потратить дополнительное время.
Какая стратегия постройки индекса была использована вами?
IMHO:
Выборка по первичному ключу должна происходить мгновенно, а не за 1,5 с. и даже не за 0,1 с.
Все приведённые цифры теста для меня слишком велики даже с поправкой на железо и VM, а потому сомнительны.
> В других СУБД есть понятие отложенной перестройки индекса, т.е. вы данные модифицируете, но индекс при этом не пересчитывается.
Т.е. субд может вернуть неактуальный результат?
Т.е. субд может вернуть неактуальный результат?
Конечно.
Но при BULK insert никто с этими данными и не должен работать.
Если к примеру вам нужно залить 5Тб данных из каких-нибудь журналов для нужд BI, то эффективнее будет отключить построение индексов, залить данные, а потом запустить параллельную индексацию с построением кубов.
Это будет быстрее, чем одновременно делать сразу всё.
Ещё есть вариант построения индексов асинхронно процессу записи собственно данных.
PS: а как вам вариант построения сначала индексов, а заливки данных потом?
Такое тоже возможно, когда СУБД предоставляет прямой доступ (NoSQL), как внутри, так и снаружи, — наряду с традиционным реляционным.
Но при BULK insert никто с этими данными и не должен работать.
Если к примеру вам нужно залить 5Тб данных из каких-нибудь журналов для нужд BI, то эффективнее будет отключить построение индексов, залить данные, а потом запустить параллельную индексацию с построением кубов.
Это будет быстрее, чем одновременно делать сразу всё.
Ещё есть вариант построения индексов асинхронно процессу записи собственно данных.
PS: а как вам вариант построения сначала индексов, а заливки данных потом?
Такое тоже возможно, когда СУБД предоставляет прямой доступ (NoSQL), как внутри, так и снаружи, — наряду с традиционным реляционным.
Одно дело — разовая заливка 5тб данных, другое — штатная работа.
В любом случае — если такое происходит в штатном режиме — то это не ACID. Вероятно, такое допустимо в больших системах, при этом не связанных с деньгами или другими критичными данными, вроде комментариев фейсбука.
PS:
под bulk insert я понял почему-то INSERT INTO… VALUES (...), (...)
В любом случае — если такое происходит в штатном режиме — то это не ACID. Вероятно, такое допустимо в больших системах, при этом не связанных с деньгами или другими критичными данными, вроде комментариев фейсбука.
PS:
под bulk insert я понял почему-то INSERT INTO… VALUES (...), (...)
Мое имхо, тесты выполнены не совсем корректно. Сравнивать производительность лишь на основе времени выполнения запроса — это неправильно. На время выполнения запроса может повлиять много факторов: наличие/отсутствие нужных страниц в памяти, статистика, кэш планов выполнения и т.д. Один и тот же запрос может выполниться с разным временем (и не всегда последующие запросы будут быстрее предыдущих).
И кстати, «вычислимые» поля можно создавать как persisted. Тогда вы сможете создавать на них полноценные индексы. А обычные вычисляемые поля являются виртуальными, т.е. каждый раз при обращении к ним SQL server вычисляет их значение заново
Хочу дать несколько советов автору из личного опыта:
1) Пользуйтесь DBCC DROPCLEANBUFFERS и DBCC FREEPROCCACHE (в данном случае менее критично). Без этого можно получить недостоверные результаты. Про измерение секунд выше уже говорили.
2) Анализируйте планы выполнения запросов — это даст дополнительную пищу для размышлений. Например, вероятно интересным наблюдением будет то, что индекс не очень помогает при поиске по шаблону "%x%" — разве что может использоваться чтобы сканировать не таблицу а индекс (что иногда на некоторых запросах быстрее, но см. пункт (1)).
P.S. «Индексы лучше работают с таблицами, содержащими большое количество строк» — не очень понятное для меня утверждение. В моём понимании — на маленьком количестве строк сканирование таблицы может быть достаточно эффективным, а индексы работают практически одинаково.
1) Пользуйтесь DBCC DROPCLEANBUFFERS и DBCC FREEPROCCACHE (в данном случае менее критично). Без этого можно получить недостоверные результаты. Про измерение секунд выше уже говорили.
2) Анализируйте планы выполнения запросов — это даст дополнительную пищу для размышлений. Например, вероятно интересным наблюдением будет то, что индекс не очень помогает при поиске по шаблону "%x%" — разве что может использоваться чтобы сканировать не таблицу а индекс (что иногда на некоторых запросах быстрее, но см. пункт (1)).
P.S. «Индексы лучше работают с таблицами, содержащими большое количество строк» — не очень понятное для меня утверждение. В моём понимании — на маленьком количестве строк сканирование таблицы может быть достаточно эффективным, а индексы работают практически одинаково.
Было бы гораздо полезнее, если бы, например, в разделе «Объединение таблиц» было объяснено, почему, при выборке по большому массиву индексы ни на что не влияют. А так, в общем, набор азбучных истин, основываясь только на которых ничего особо не наоптимизируешь
Попутано Объединение (union) и Соединение (Join) а так — неплохо.
S."somedatetime" BETWEEN '2012-26-11 11:30:00' AND '2012-31-12 11:32:00';В порядке месяца и дня в датах ошибки точно нет?
Точно. По другому и не работало.
Вы уверены? MSDN ничего о таком формате не знает: msdn.microsoft.com/en-us/library/ms187819.aspx
Я не с потолка это взял, '2012-11-26 11:30:30' не пропустило.
С каким сообщением об ошибке?
Неверный формат Datetime.
У вас какой-то странный SQL Server или клиент для него: http://sqlfiddle.com/#!6/41c5e/3/1
По вашей же ссылке
Date formats:… [19]96/[0]4/15 — (ymd)
Time formats:… 14:30[:20:999]
… When you use numeric date format, specify the month, day, and year in a string that uses slash marks (/), hyphens (-), or periods (.) as separators. ...
И там же выше — [19]96/15/[0]4 — (ydm)
Вероятно в SQL Server встроена мощная система телепатии, чтобы отличить, что есть что в дате 2013-01-02
А если серьёзно — в скобках указано имя формата, которое указывается явно с помощью msdn.microsoft.com/en-us/library/ms189491.aspx
Но вообще, да, мой изначальный коммент становится некорректным — такой формат есть (хоть он и дикий)
Вероятно в SQL Server встроена мощная система телепатии, чтобы отличить, что есть что в дате 2013-01-02
А если серьёзно — в скобках указано имя формата, которое указывается явно с помощью msdn.microsoft.com/en-us/library/ms189491.aspx
Но вообще, да, мой изначальный коммент становится некорректным — такой формат есть (хоть он и дикий)
Это не «дикий» формат, а обычный sortable datetime. Вероятно все зависит от локали сервера/базы.
ydm sortable?
«диким» я назвал его скорее потому, что в реальной жизни никто такой формат не использует.
m/d/y — используют американцы, такой формат можно представить
d/m/y — использует весь остальной мир
y-m-d — используют все
y-d-m — кто использует такой?
«диким» я назвал его скорее потому, что в реальной жизни никто такой формат не использует.
m/d/y — используют американцы, такой формат можно представить
d/m/y — использует весь остальной мир
y-m-d — используют все
y-d-m — кто использует такой?
Не скажу за всех, но мне так проще сортировать, например фотоальбомы — «2012-01-23 Ботсад (Юля)», «2012-01-23 Ботсад (Юля) RAW», «2012-01-23 Ботсад (Юля) фотошоп» и «2012-01-23 Ботсад (Юля) for web» при сортированном выводе будут явно рядом. Да, я в курсе про кучу п/о для каталогизации фотографий со своими индексами, но это не отменяет сортировку на уровне каталогов.
Пример взят с потолка, но, как показала практика — некоторые мои знакомые фотографы тоже придерживаются такого формата названий как самого оптимального.
Ну и вообще раз формат есть — значит он был для чего-то придуман и стандартизирован
Пример взят с потолка, но, как показала практика — некоторые мои знакомые фотографы тоже придерживаются такого формата названий как самого оптимального.
Ну и вообще раз формат есть — значит он был для чего-то придуман и стандартизирован
У вас y-m-d, который отлично сортируется, а речь о y-d-m, который, на самом деле, несортируемый. Потому я и сказал «дикий», подразумевая, что практической ценности у него нет (есть?) и что в реальной жизни его никто не использует (используют?).
ps: у меня фотки точно так же как у вас хранятся :-)
ps: у меня фотки точно так же как у вас хранятся :-)
zerkms — скорее сортируемый на уровне паттернов с обёртками, а не по принципу KISS, которого мы придерживаемся в приведённом примере.
В каком-нить sql или моём софте мне не сложно будет определить такой вывод дат при использовании, но я сходу реально не могу привести примеров, где такой формат вывода нужен, да.
Как оно хранится в базе или ещё где, должно волновать не конечного пользователя а разработчика/тестера.
В каком-нить sql или моём софте мне не сложно будет определить такой вывод дат при использовании, но я сходу реально не могу привести примеров, где такой формат вывода нужен, да.
Как оно хранится в базе или ещё где, должно волновать не конечного пользователя а разработчика/тестера.
Я не уверен, что такое «сортируемый на уровне паттернов с обёртками», но я уверен, что он не «sortable datetime»
Так или иначе — для вывода в своём приложении можно использовать хоть d-m-d-y-y-m-d-m, но это не прибавит обсуждаемому формату полезности :-)
Так или иначе — для вывода в своём приложении можно использовать хоть d-m-d-y-y-m-d-m, но это не прибавит обсуждаемому формату полезности :-)
Про сортировку на уровне «патернов с обёртками» я имел в виду парсинг списка файлов,
названия которых — вышеприведённый y-d-m физически на диске. Там придется сортировать как раз такими обертками, с парсингом имени файла, сравнением и составлением корректного списка вывода:
2012-13-01
…
2012-31-01
2012-01-02
2012-02-02
2012-03-02
…
А под KISS я имел в виду что при использовании y-m-d у нас четко понятно что
2012-01-01
…
2012-01-31
2012-02-01
…
2012-02-31
Без нарушения правил логики сортировки
названия которых — вышеприведённый y-d-m физически на диске. Там придется сортировать как раз такими обертками, с парсингом имени файла, сравнением и составлением корректного списка вывода:
2012-13-01
…
2012-31-01
2012-01-02
2012-02-02
2012-03-02
…
А под KISS я имел в виду что при использовании y-m-d у нас четко понятно что
2012-01-01
…
2012-01-31
2012-02-01
…
2012-02-31
Без нарушения правил логики сортировки
> Но индекс не всегда увеличивает производительность. Например, запрос (420 000 строк)
Естественно. Вы выбрали половину записей таблицы. Зачем использовать индекс, если вам нужно так много записей.
Естественно. Вы выбрали половину записей таблицы. Зачем использовать индекс, если вам нужно так много записей.
> Индекс не поможет, если конструкция WHERE содержит условия поиска для двух таблиц;
Неправда. Перед выполнением запроса с INNER JOIN оптимизатор выполняет условия так, как ему удобно будет, а не так, как вы их записали в запросе (часть в ON, часть в WHERE). Потому — правильные составные индексы вполне себе будут использованы.
Неправда. Перед выполнением запроса с INNER JOIN оптимизатор выполняет условия так, как ему удобно будет, а не так, как вы их записали в запросе (часть в ON, часть в WHERE). Потому — правильные составные индексы вполне себе будут использованы.
>>>Один кластеризованный индекс создаётся явно — это первичный ключ.
Тут кластерный индекс создается неявно!
И вообще Primary Key может быть и некластерным. Не вводите людей в заблуждения.
Тут кластерный индекс создается неявно!
И вообще Primary Key может быть и некластерным. Не вводите людей в заблуждения.
Ужас-ужас! Читайте планы запросов, читайте литературу, лучше не пишите если не разобрались до конца.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Оптимизация производительности SQL Server с использованием индексов