Справка при понимании нескольких столбцов на индексе?

Question

Справка при понимании нескольких столбцов на индексе?

Для генерации рэнда вызова номеров с результатом выражения "10 к питанию 10"

rand(10 ** 10)

Для дополнения числа нулями, можно использовать оператор

'%010d' % rand(10 ** 10)

формата строки или rjust метод строки

rand(10 ** 10).to_s.rjust(10,'0')

5

sql-server tsql indexing

задан Xaisoft 16 September 2009 в 17:32

5 ответов

A non-clustered index on (a, b) is a "copy" of a part of the table whose rows are sorted on a then on b and contain the reference to the original row.

It helps to run the queries like this:

SELECT  *
FROM    mytable
WHERE   a = @A
        AND b = @B

, this:

SELECT  *
FROM    mytable
ORDER BY
        a, b

, this:

SELECT  *
FROM    mytable
WHERE   a = @A
ORDER BY
        b

and many others.

For instance, we have a table like this:

#       col1    col2    col3
1       1       1       1
2       1       4       8
3       7       2       3
4       3       3       9
5       8       9       4
6       2       2       7
7       5       3       5
8       3       9       4

If we create an index on (col2, col3), it will contain the following data:

col2    col3    #
1       1       1
2       3       3
2       7       6
3       5       7
3       9       4
4       8       2
9       4       5
9       4       8

, i. e. sorted first on col2, then on col3, then on the reference to the row.

It's easy to see that this index is an index on col2 just as well (sorting on (col2, col3) implies sorting on col2 alone).

Order matters, so if we create an index on (col3, col2), the rows will be sorted differently:

col2    col3    #
1       1       1
2       3       3
9       4       5
9       4       8
3       5       7
2       7       6
4       8       2
3       9       4

This index is an index on col3 too.

If we want to find the rows within a certain range of (col2, col3) we just take a slice from the ordered data:

SELECT  col2, col3
FROM    mytable
WHERE   col2 BETWEEN 2 AND 3

col2    col3    #
1       1       1
----
2       3       3
2       7       6
3       5       7
3       9       4
----
4       8       2
9       4       5
9       4       8

Easy to see that we cannot take this slice on col3 using this index, since col3 is not ordered by itself.

The "reference" mentioned above is a RID of the row (a pointer to the place in the tablespace), if the table is non-clustered itself, or the value of the table's cluster key if the table is clustered.

A clustered index does not create a shadow copy of the values. Instead, it rearranges the tables rows themselves.

If you create a clustered index on (col2, col3) above, it will just rearrange the table rows:

#       col1    col2    col3
1       1       1       1
3       7       2       3
6       2       2       7
7       5       3       5
4       3       3       9
2       1       4       8
5       8       9       4
8       3       9       4

Clustered or non-clustered, therefore, is a storage method rather than an index.

In Oracle, this is called index-organized table (rows are sorted), as opposed to a heap-organized table (rows are not sorted).

3

ответ дан 18 December 2019 в 06:23

Некластеризованный индекс по столбцам a, b то же, что и некластеризованный индекс на столбцы б, а? (Обратите внимание на порядок.)

НЕТ! Порядок важен. Если у вас есть некластеризованный индекс на (a, b), вы можете использовать его, если ваше предложение WHERE имеет ограничение на a и b - или если оно имеет только ограничение на a (но не , если у него есть только проверка на b).

Кроме того, является некластеризованным индексом на столбец a такой же, как некластеризованный index on a, c?

Нет, это не так, но оптимизатор запросов SQL Server будет использовать этот некластеризованный индекс, если встретит запрос с предложением WHERE только на «a».

Marc

2

ответ дан 18 December 2019 в 06:23

index A,B is different from index B,A

That is because an index is organized along a particular sort order. So imagine you need to search with the following WHERE clause

WHERE A='somecrit' AND B='SomepartialCrit%'  -- notice the wildcard

The A,B index will be very efficient at resolving the query, but if it were

WHERE   A='SomepartialCrit%'  AND B='somecrit'

The (A,B) index would only partially help (could be better than full table scan but not optimal..) whereby the (B,A) index would come to the rescue...

For use with a query that included both A and B as exact match (no wildcard), either index could be used in an equivalent fashion (efficiency-wise), although the choice of one particular index could be driven by other part of the query such as ORDER BY clauses etc.

An index on A is different from an index on A,C For one the index on A,C could be used to resolve queries that involve both A and C criteria, and also the A,C index could be use to "cover" the SELECT clause or part thereof, that is: If the SELECT clause only includes column A and C (from this particular table), SQL could provide the results without having to get data from the table proper, it would get the A and C values from the index alone.

Are "redundant" indexes a bad thing ?

As said above, extra indexes may help resolve SELECT queries more efficiently. On the down side, they a) use storage space and b) make the INSERT, UPDATE and DELETE queries less efficient (because the new/updated/deleted values need to be added/changed/deleted in more places.

It is therefore a matter of finding the right balance based on the available storage space available and the use case (some mostly read-only databases can have a slew of indexes without hurting performance at all, databases with frequent inserts can see their performance degrade terribly with too many indices)

On clustered indexes

See explanation by Joel Coehoorn.
No, the clustered index of a given table does not need to be based on the primary key. Selecting a good clustered index (or indeed deciding to not use a clustered index) is a part science part art process which scope is beyond this short response.

2

ответ дан 18 December 2019 в 06:23

Думайте об индексе как о телефонной книге. Обычно телефонные книги упорядочиваются по фамилии, имени, улице. Итак, если вы хотите найти телефонный номер Джо Смита, 101 Main Street, вы открываете телефонную книгу в S для Smith, затем просматриваете всех Джо Смита, ищите Джо Смита, который живет на 101 Main Street, и вы найдите номер телефона.

Телефонную книгу можно было упорядочить по-разному, например, по улице, имени, фамилии. Тогда вы сначала посмотрите на Мэйн-стрит, затем на Джо и, наконец, на Смита. Если вы хотите найти номер только одного человека, это будет одинаково быстро.

Разница становится важной, если вы хотите перечислить номера телефонов всех людей, которые живут на Мэйн-стрит и чье имя - Джо. С обычной телефонной книгой это тяжелая работа: вам нужно перебрать все фамилии, найти Джо с этой фамилией, и живут ли они на Мэйн-стрит. Для этого нужно просмотреть всю телефонную книгу. Но если порядок указателей - улица, имя, фамилия, задача почти тривиальна: найдите Main Street, Joe и скопируйте все фамилии и их номера телефонов. Намного быстрее.

Кроме того, тот факт, что в телефонной книге перечислены улицы, тоже не имеет значения, если вас интересуют только названия. Если вы хотите найти номера телефонов всех Джо Смитов, вам понадобится телефонная книга, упорядоченная по фамилии, имени (или имени, фамилии). Вам все равно, все ли в телефонной книге Джо Смиты отсортированы по улицам или нет. В этом смысле индекс на (фамилия, имя, улица) включает в себя индекс на (фамилия, имя).