Различия

Показаны различия между двумя версиями страницы.

@@ Строка 1: / Строка 1: @@
+====== Индекс в базах данных Postgresql ======
+~~Title: Индекс в базах данных Postgresql ~~
+{{htmlmetatags>
+metatag-description=(Индексы - это специальные структуры в базах данных, которые позволяют ускорить поиск и сортировку по определенному полю или набору полей.)
+}}
+{{ ::indeksy-v-bazah-dannyh.jpg?nolink&400 |}}
+Индекс в базах данных Postgresql: INDEX, REINDEX.
+  * [[http://postgresql.ru.net/node/214136|Переводы/Перевод главы "Индексы" мануала]]
+  * [[http://www.postgresql.org/docs/current/static/sql-reindex.html|REINDEX -- rebuild indexes]]
+Индексы - это специальные структуры в базах данных, которые позволяют ускорить поиск и сортировку по определенному полю или набору полей.
+**Индексом** называется объект базы данных, позволяющий значительно повысить скорость обращения к БД за счет ускоренной обработки команд, содержащих сравнительные критерии. Хранимая в индексе информация о размещении данных по одному или нескольким полям таблицы повышает эффективность поиска записей при условной выборке (например, с использованием секции WHERE). После того, как индекс будет создан, система должна поддерживать ее синхронизированным с таблицей. Это добавляет дополнительные расходы на операции с данными. **Поэтому индексы, которые редко или никогда не используются в запросах должны быть удалены.**
+Создание индекса в большой таблице может занять длительное время. <note warning>По умолчанию, [[PostgreSQL]] позволяет читать (выбирать) из таблицы, параллельно с созданием индекса, но блокирует запись (вставку, обновление и удаление) до конца построения индекса.</note>
+[[http://postgresql.ru.net/docs/FAQ_Russian.html#q6.41|Почему мои запросы работают медленно? Почему они не используют мои индексы?]] Индексы не используются для каждого запроса. Они используются только если таблица больше минимального размера и запрос выбирает только маленький процент строк в таблице. Так устроено, потому что доступ к диску с применением рандомизации при сканировании индексов может быть медленнее, чем простое чтение таблицы или ее последовательное сканирование.
+**Команда REINDEX** используется для перестройки существующих индексов. Использовать её имеет смысл в случае:
+  * порчи индекса;
+  * постоянного увеличения его размера.
+Второй случай требует пояснений. Индекс, как и таблица, содержит блоки со старыми версиями записей. PostgreSQL не всегда может заново использовать эти блоки, и поэтому файл с индексом постепенно увеличивается в размерах. Если данные в таблице часто меняются, то расти он может весьма быстро. Если вы заметили подобное поведение какого-то индекса, то стоит настроить для него периодическое выполнение команды REINDEX. Учтите: команда REINDEX, как и VACUUM FULL, полностью блокирует таблицу, поэтому выполнять её надо тогда, когда загрузка сервера минимальна.
+  * Перестроить только индекс 'index_ratesheet_id'<file>
+REINDEX INDEX index_ratesheet_id;
+</file>
+  * Данный запрос показывает размер объектов в базе данных (например, таблиц и индексов), исключая pg_catalog information_schema.<file>
+SELECT C.oid,C.relfilenode, nspname, relname AS "relation",
+relkind, pg_size_pretty(pg_relation_size(C.oid)) AS "size"
+FROM pg_class C
+LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
+WHERE nspname NOT IN ('pg_catalog', 'information_schema')
+ORDER BY pg_relation_size(C.oid) DESC;
+</file>
+  * При помощи команды [[time]], замерим время переиндексации индекса (в данном случае размер индекса составляет 12 GB)<file>
+time -p psql -U postgres mbillcz5054 -c "REINDEX INDEX i_cdr_pairs;"
+REINDEX
+real 920.49
+user 0.00
+sys 0.00
+</file>В поле real видно, что задача выполнялась более 15 минут.
+  * **reindexdb** - эта утилита запущенная из операционной системы выполнит [[SQL]] команду REINDEX для каждой таблицы в заданной по умолчанию базе данных. Ключ -а заставит выполнить REINDEX для всех индексов во всех базах данных сервера.
+====== Технология HOT ======
+Технология HOT (Heap-only tuples). Изменения с использованием HOT происходят, когда команда UPDATE  изменяет значения в никем не индексируемых полях. Если изменяется значение в индексируемом поле, HOT не используется, и задействуется больше ресурсов. Поэтому правильное построение индексов может увеличить производительность при изменениях и снизить потребность в обслуживании.
+  * Статистика использования HOT для операции UPDATE:<file>
+select n_tup_upd, n_tup_hot_upd, schemaname,relname from pg_stat_user_tables;
+</file>
+<note tip>Изменения записей, без использования HOT могут вызывать раздувание индексов. При автоматической сборки мусора (VACUUM) раздутые индексы не обнаруживаются и никакие индексы не перестраиваются.</note>
+====== Обслуживание индексов: Indexes Concurrently ======
+  * [[http://www.postgresql.org/docs/8.4/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY|Building Indexes Concurrently]] создание нового индекса без блокировки таблицы.
+[[PostgreSQL]] поддерживает команду **CREATE INDEX CONCURRENTLY**, которая создает индекс без полной блокировки таблицы, она позволяет добавлять, изменять и удалять записи во время создания индекса. Также PostgreSQL позволяет иметь два индекса с разными именами, но с одним и тем же определением.
+<note warning>CREATE INDEX CONCURRENTLY нельзя использовать с первичными индексами.</note>
+**Идея в том, чтобы создать новый индекс, идентичный старому, удалить старый индекс и присвоить новому имя старого.**
+**Пример использования CREATE INDEX CONCURRENTLY.**
+Старый индекс<file>
+oid  | relfilenode | nspname  |              relation              | relkind |    size
+-------+-------------+----------+------------------------------------+---------+------------
+|       67875 | public   | i_cdr_out                          | i       | 1628 MB
+</file>Новый индекс<file>
+  oid  | relfilenode | nspname  |              relation              | relkind |    size
+-------+-------------+----------+------------------------------------+---------+------------
+|       68397 | public   | i_cdr_out                          | i       | 1557 MB
+</file>Изменился внутренний идентификатор (oid)  и номер текущего файла(relfilenode) - это подтверждает, что индекс был заменен успешно. Так же уменьшился размер индекса. Это было достигнуто нижеприведенными SQL командами. В которых создается составной индекс i_cdr_out_new, после его создания в транзакции происходит удаление старого индекса и переименование нового.<file>
+CREATE INDEX CONCURRENTLY i_cdr_out_new
+  ON cdr
+  USING btree (begin_time, dst_peer_id, cause_local);
+BEGIN;
+DROP INDEX i_cdr_out;
+ALTER INDEX i_cdr_out_new RENAME TO i_cdr_out;
+COMMIT;
+</file>

📌 Удобный подбор VPS по параметрам доступен на DIEGfinder.com - официальном инструменте проекта DIEG. Это часть единой экосистемы, созданной для того, чтобы помочь быстро найти подходящий VPS/VDS сервер для любых задач хостинга.

📌 Для тестирования скриптов, установщиков VPN и Python-ботов рекомендуем использовать надежные VPS на короткий срок. Подробнее о быстрой аренде VPS для экспериментов - читайте здесь.

💥 Подпишись в Телеграм 💥 и задай вопрос по сайтам и хостингам бесплатно!

Различия

7 Самых Популярных Статей

7 Самых Популярных Обзоров