blob: 71e2fbe4a689a0947de2d82452c92e35e7667f7f (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
-----------------------
-- Кластерный индекс --
-----------------------
SELECT * INTO Customers_copy FROM Customers
GO
SELECT * FROM Customers WHERE id=12345;
GO
SELECT * FROM Customers_copy WHERE id=12345;
GO
--------------------------
-- Некластерные индексы --
--------------------------
-- составной (средне)
SELECT Boxes.id, Orders.id, date
FROM Boxes JOIN Orders
ON Boxes.order_id = Orders.id
WHERE Boxes.warehouse_id = 12465
GO
CREATE INDEX Boxes_Composite ON Boxes (order_id, warehouse_id);
GO
DROP INDEX Boxes_Composite ON Boxes;
GO
-- покрывающий (не очень)
SELECT * FROM Orders
WHERE EXISTS(
SELECT * FROM Customers
WHERE customer_id = Customers.id
AND Customers.email LIKE N'%@mail.ru'
);
CREATE INDEX Orders_Index_Covering ON Orders (id)
INCLUDE (customer_id, city_id, date);
GO
DROP INDEX Orders_Index_Covering ON Orders;
GO
-- уникальный (норм)
SELECT email FROM Customers WHERE name=N'customer-12451';
CREATE UNIQUE INDEX Customers_Unique ON Customers (name);
GO
DROP INDEX Customers_Unique ON Customers;
GO
-- индекс с включеными столбцами (не очень)
SELECT date FROM Orders
WHERE EXISTS(
SELECT * FROM Customers
WHERE customer_id = Customers.id
AND Customers.email LIKE N'%@mail.ru'
);
CREATE INDEX Orders_Index_Columns ON Orders (id)
INCLUDE (customer_id, date);
GO
DROP INDEX Orders_Index_Columns ON Orders;
GO
-- отфильтрованный индекс (средне)
SELECT SUM(amount) FROM Payments WHERE date >= '2021-12-01';
CREATE INDEX Payments_Filtered ON Payments (amount)
WHERE date >= '2021-12-01';
GO
DROP INDEX Payments_Filtered ON Payments;
GO
|
