Details on indexes, primary keys, unique keys

Question

I like to think that I know enough theory, but I have little experience optimizing the database in the real world. I would like to know points of view, thoughts or experiences.

Imagine a scenario like:

Table A Key: c1, c2, c3, c4 Index: c7, c3, c2

Table B Key: c1, c2, c3, c4 Index: c1, c5

All are not clustered. Tables contain 40 fields. They eat daily at night and have some updates throughout the day.

Table A, if more queries benefit from the key than the index, can the index affect negatively? Since insert / delete you need to update 2 indexes instead of 1.

Table B has an additional field in the index that is not present in the key.

Can a request use c1, c5

Use this key ?: Key: c1, c2, c3, c4, c5

So that this index can be lost.

What effect does the field order have? Key: c1, c2, c3 Key: c3, c1, c2

A typical scenario for me is process_date, client_number, operation. And it feeds on a bunch of data every day (process_date).

Answer 1

if more queries benefit from the key, how can the Index affect the index negatively? Since insert / delete has 2 pointers instead of 1 for updating.

A non-clustered index adversely affects insert / update / delete performance. Negative effects are usually outweighed by increased sample performance.

Can the request use c1, c5 Benefit from this key: key: c1, c2, c3, c4, c5

, c1, .

? : c1, c2, c3 : c3, c1, c2

, , . (c1, c2) where c1 = 1 where c1 = 1 and c2 = 1, where c2 = 1. , order by c1, order by c2.

Answer 2

, , ?

.

...

, , . . , .

c1, c5 ?:: c1, c2, c3, c4, c5

. . . ( ) , .

.

.

? : c1, c2, c3 : c3, c1, c2

. , ORDER BY. , .

, , - - .

, .

Answer 3

, sys.dm_db_index_usage_stats, , . ( SQL Server).

, . , .

, , . " c1, c5" , , , , . , , , , , . , .

Answer 4

, , (, ). , . - , , .

, . . , , email_address, "[e-mail]" . , , : "[e-mail]" , " № 1" AND "[e-mail]" , " # 2". , .

, . , c1, c2, c1, c2, c3 . c1, c3, c2, .

, , , "TO". . , , , ? , , "TO".