10 câu lệnh T-SQL Index cần thiết với DBA
10 câu l nh T-SQL Index c n thi t v i DBAệ ầ ế ớ
Nh ng ng i SQL Server DBA (Database Administrator) – qu n tr c s ữ ườ ả ị ơ ở
d li u bi t r t rõ r ng các danh m c Index trong database r t gi ng v i ữ ệ ế ấ ằ ụ ấ ố ớ
Index trong m c Library. Ho c hi u theo cách đ n gi n r ng Index trong ụ ặ ể ơ ả ằ
Database là 1 h c u trúc có liên k t ch t ch v i các b ng đ nhanh chóngệ ấ ế ặ ẽ ớ ả ể
thu th p thông tin t các dòng trong b ng đó.ậ ừ ả Trong bài vi t d i đây, ế ướ
chúng tôi s gi i thi u v i các b n nh ng câu l nh v T-SQL có liên quan ẽ ớ ệ ớ ạ ữ ệ ề
t i Index trong SQL r t có ích đ i v i b t kỳ ng i làm công vi c DBA ớ ấ ố ớ ấ ườ ệ
nào. C th , chúng ta s chia ra làm 3 phân m c chính: khái ni m v Index,ụ ể ẽ ụ ệ ề
t o các câu l nh truy v n – Query có liên quan đ n thông tin, và cu i cùng ạ ệ ấ ế ố
là quá trình b o d ng – Maintenance.ả ưỡ
Khái ni m chung v Index:ệ ề
1. Clustered Index:
Có nhi m v chính là l u tr d li u c a các dòng đ c s p x p theo th ệ ụ ư ữ ữ ệ ủ ượ ắ ế ứ
t trong b ng d a trên giá tr c a khóa key. Ch 1 clustered index có th ự ả ự ị ủ ỉ ể
đ c t o trên m i b ng, b i vì d li u c a các dòng ch có th s p x p ượ ạ ỗ ả ở ữ ệ ủ ỉ ể ắ ế
theo 1 th t nh t đ nh. Thêm 1 đi m n a là clustered index có th đ c ứ ự ấ ị ể ữ ể ượ
“s n sinh” trong khi t o nh ng ràng bu c gi ng nh Primary key trên b ng ả ạ ữ ộ ố ư ả
d li u có s n.ữ ệ ẵ
Ví d nh :ụ ư
ALTER TABLE [MyAddress]
ADD CONSTRAINT [PK_Address_AddressID] PRIMARY KEY
CLUSTERED
(
[AddressID] ASC
) ON [PRIMARY]
GO
Bên c nh đó, clustered index cũng có th đ c t o trên m i c t mà không ạ ể ượ ạ ỗ ộ
có liên k t đi kèm. Ví d :ế ụ
CREATE CLUSTERED INDEX [MyAddress_id_CIX] ON [MyAddress1]
(
[ID] ASC
)ON [PRIMARY]
GO
2. Non Clustered Index:
Đ c t o ra đ c i thi n ho t đ ng, hi u su t c a nh ng chu i câu l nh ượ ạ ể ả ệ ạ ộ ệ ấ ủ ữ ỗ ệ
truy v n th ng xuyên đ c s d ng, nh ng l i không bao g m v i ấ ườ ượ ử ụ ư ạ ồ ớ
clustered index. Bên trong kh i nonclustered index, vi c s p x p thông tin ố ệ ắ ế
theo th t c a ch s index không kh p v i th t l u tr xét theo khía ứ ự ủ ỉ ố ớ ớ ứ ự ư ữ
c nh v t lý c a các dòng d li u trên đĩa.ạ ậ ủ ữ ệ ổ
Nonclustered Index có th đ c t o trên b ng có s n, bao g m các c t ể ượ ạ ả ẵ ồ ộ
không có trong clustered index. Ví d :ụ
CREATE UNIQUE NONCLUSTERED INDEX
[NIX_col5_col2_col3_col4_col6]
ON [MyAddress]
(
[AddressLine1] ASC,
[AddressLine2] ASC,
[City] ASC,
[StateProvinceID] ASC,
[PostalCode] ASC
)ON [PRIMARY]
GO
Ho c, nonclustered index cũng có th làm đ c trong khi t o liên k t trong ặ ể ượ ạ ế
b ng có s n, ví d :ả ẵ ụ
ALTER TABLE [MyAddressType]
ADD CONSTRAINT [DEFF_MyAddressType_ModifiedDate]
DEFAULT (getdate()) FOR [ModifiedDate]
GO
3. XML Index:
Là 1 khái ni m khác, đ c sinh ra trên c t d li u XML và các b ng có ệ ượ ộ ữ ệ ả
clustered index trên khóa Primary. 1 XML index th c d ng Frimary:ộ ạ
CREATE PRIMARY XML INDEX idx_xCol_MyTable on MyTable (xCol)
Và v i XML index secondary thì nh sau:ớ ư
CREATE TABLE MyTable (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary index.
CREATE PRIMARY XML INDEX PIdx_MyTable_XmlCol
ON T(XmlCol)
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_MyTable_XmlCol_PATH ON MyTable(XmlCol)
USING XML INDEX PIdx_MyTable_XmlCol
FOR PATH
GO
CREATE XML INDEX PIdx_MyTable_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_MyTable_XmlCol
FOR VALUE
GO
4. Spatial Index:
1 thành ph n trong SQL Server 2008 cung c p cho ng i dùng nh ng c t ầ ấ ườ ữ ộ
d li u đ c bi t, có liên quan đ n d li u bi u tr ng có liên quan đ n lĩnh ữ ệ ặ ệ ế ữ ệ ể ư ế
v c không gian, ví d nh đ a lý và hình h c.ự ụ ư ị ọ
1 c u trúc spatial index có th đ c t o b ng cú pháp sau:ấ ể ượ ạ ằ
CREATE TABLE MySpatialTable(id int primary key, geometry_col
geometry);
CREATE SPATIAL INDEX SIndx_MySpatialTable_geometry_col1
ON MySpatialTable(geometry_col)
WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );
Query Index có liên quan đ n d li u metadata:ế ữ ệ
5. Tìm ki m t t c index:ế ấ ả
Tr c tiên, n u mu n tìm ki m t t c index thì hãy s d ng câu l nh truy ướ ế ố ế ấ ả ử ụ ệ
v n – query theo b ng, c t và khóa index c a c s d li u s n có.ấ ả ộ ủ ơ ở ữ ệ ẵ
SELECT OBJECT_SCHEMA_NAME(BaseT.[object_id],DB_ID()) AS
[Schema],
BaseT.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS
[column_name],
I.[type_desc]
FROM sys.[tables] AS BaseT
INNER JOIN sys.[indexes] I ON BaseT.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
INNER JOIN sys.[all_columns] AC ON BaseT.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE BaseT.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
ORDER BY BaseT.[name], I.[index_id], IC.[key_ordinal]
6. Fragmentation:
Còn v i quá trình Fragmentation – tìm ki m nh ng thành ph n index trong ớ ế ữ ầ
tình tr ng “Fragmentation” c a t t c các b ng d li u trong database hi n ạ ủ ấ ả ả ữ ệ ệ
t i. Ví d nh sau:ạ ụ ư
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL,
NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND
IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
order by IPS.avg_fragment_size_in_pages desc
7. Missing Index:
V i các thành ph n index b m t, SQL Server v n có kh năng giám sát, ớ ầ ị ấ ẫ ả
theo dõi tình hình c a index đ c t o ra nh m m c đích c i thi n hi u ủ ượ ạ ằ ụ ả ệ ệ
su t ho t đ ng c a chu i câu l nh truy v n. Ph n mã d i đây có ch c ấ ạ ộ ủ ỗ ệ ấ ầ ướ ứ
năng li t kê t t c các m c index b m t:ệ ấ ả ụ ị ấ
SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS
Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' +
sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' +
IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS
NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN
mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS
CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON
migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON
mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID =
sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks +
user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC
8. Index không còn s d ng:ử ụ
Thành ph n cu i cùng chúng ta đ c p đ n trong m c này là index không ầ ố ề ậ ế ụ
còn s d ng, các b n hãy áp d ng chu i câu l nh d i đây đ li t kê t t ử ụ ạ ụ ỗ ệ ướ ể ệ ấ
c các ph n index ch a t ng đ c s d ng, bên c nh đó còn t o ra l nh ả ầ ư ừ ượ ử ụ ạ ạ ệ
DROP:
SELECT o.name, indexname=i.name, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id
= s.index_id AND s.object_id = p.object_id)
, CASE
có thể bạn quan tâm
Làm thế nào để Google Index website của bạn nhanh nhất có thể
5
613
258
Tài liệu CNTT khác
5
(New)
Những tính năng hiệu quả nhất của hàm INDEX trong Excel
13
643
277
Tài liệu CNTT khác
13
(New)
TIN HỌC :Câu lệnh If – Then
3
969
404
Giáo án, bài giảng lớp 11
3
(New)
TIN HỌC :Câu lệnh If – Then ( TT)
2
1.059
402
Giáo án, bài giảng lớp 11
2
(New)
TIN HỌC :Câu lệnh If – Then (T3)
2
869
359
Giáo án, bài giảng lớp 11
2
(New)
TIN HỌC :Câu lệnh If – Then (T4)
2
841
396
Giáo án, bài giảng lớp 11
2
(New)
Tổng hợp lệnh Run
4
812
294
Tài liệu CNTT khác
4
(New)
Một số lệnh và thủ thuật cho Mac OS X
8
704
287
Cơ sở dữ liệu
8
(New)
thông tin tài liệu
Những người SQL Server DBA (Database Administrator) – quản trị cơ sở dữ liệu biết rất rõ rằng các danh mục Index trong database rất giống với Index trong mục Library. Trong bài viết dưới đây, chúng tôi sẽ giới thiệu với các bạn những câu lệnh về T-SQL có liên quan tới Index trong SQL rất có ích đối với bất kỳ người làm công việc DBA nào
Mở rộng để xem thêm
từ khóa liên quan
tài liệu mới trong mục này
tài liệu hot trong mục này
tài liệu giúp tôi
Nếu bạn không tìm thấy tài liệu mình cần có thể gửi yêu cầu ở đây để chúng tôi tìm giúp bạn!
×
