SQL Index (edit)

Clustered Index: Sắp xếp dữ liệu vật lý vẫn ở trong bảng hiện tại

Non-Clustered Index: Sắp sếp được chứa ở ngoài bảng hiện tại => tốn bộ nhớ

Composite index
Là kiểu Index có nhiều hơn 1 cột. Cả hai kiểu index cơ sở là Clustered Index và Non Clustered Index cũng có thể đồng thời là là kiểu Composite index.

Unique Index
Là kiểu Index dùng để đảm bảo tính duy nhất trong các cột được tạo Index. Nếu Index loại này được tạo dựa trên nhiều cột, thì tính duy nhất của giá trị được tính trên tất cả các cột đó, không chỉ riêng rẽ từng cột. Ví dụ, nếu bạn đã tạo ra một Index trên các cột FirstName và LastName trong một bảng, thì giá trị của 2 cột này kết hợp với nhau phải là duy nhất, nhưng riêng rẽ từng cột thì giá trị vẫn có thể trùng nhau.
Một Unique Index được tự động tạo ra khi bạn định nghĩa một khóa chính (Primary Key) hoặc một ràng buộc duy nhất (Unique Constraint):

Primary Key
Khi bạn định nghĩa một ràng buộc khoá chính trên một hoặc nhiều cột của bảng, SQL Server tự động tạo ra một Unique - Clustered Index nếu chưa có một Clustered Index nào tồn tại trên bảng hoặc view.

Unique
Khi bạn định nghĩa một ràng buộc duy nhất, SQL Server tự động tạo ra một index có các đặc tính là Unique và là Non Clustered Index. Bạn cũng hoàn toàn có thể tạo ra một Unique và là Clustered Index nếu như chưa có một Clustered Index nào được tạo ra trước đó trên bảng.

Covering index: sử dụng tất cả các cột trong câu truy vấn để tạo ra index

Các chỉ mục hay index trong sql là các bảng tra cứu đặc biệt mà công cụ tìm kiếm cơ sở dữ liệu có thể sử dụng để tăng tốc độ truy xuất dữ liệu. Đơn giản chỉ cần thiết lập một chỉ số là một con trỏ đến dữ liệu trong một bảng. Một chỉ mục trong một cơ sở dữ liệu rất giống với một chỉ mục ở mặt sau của một cuốn sách.

Một chỉ mục giúp tăng tốc các truy vấn SELECT và các mệnh đề WHERE , nhưng nó làm chậm dữ liệu nhập vào, với các câu lệnh UPDATE  INSERT . Các chỉ mục có thể được tạo ra hoặc bỏ đi mà không ảnh hưởng đến dữ liệu.

https://sites.google.com/site/mymssqldb/sql/indexes/01-cacloaiindextrongmssql

  • Index giống như chỉ mục của cuốn sách
  • Index có thể thực hiện trên Table hoặc View
  • Index có thể thực hiện trên nhiều cột
  • Index nên được tạo trên các cột mà có giá trị là Interger hơn là giá trị Chuỗi

CREATE INDEX index_name ON table_name;

CREATE INDEX index_name
ON table_name (column_name);

CREATE UNIQUE INDEX index_name
on table_name (column_name);

CREATE INDEX index_name
on table_name (column1, column2);

DROP INDEX index_name;

Chú ýMặc dù các chỉ mục nhằm mục đích nâng cao hiệu suất của một cơ sở dữ liệu, nhưng đôi khi cần tránh.

  • Các chỉ mục không nên được sử dụng trong các bảng nhỏ.
  • Bảng mà thường xuyên có các hoạt động update, insert.
  • Các chỉ mục không nên được sử dụng trên các cột mà chứa một số lượng lớn giá trị NULL.
  • Không nên dùng chỉ mục trên các cột mà thường xuyên bị sửa đổi.

Các hướng dẫn sau đây chỉ ra khi sử dụng một chỉ mục nên được xem xét lại.

  • Các chỉ mục không được sử dụng trên các bảng nhỏ.
  • Các bảng có cập nhật hàng loạt thường xuyên, hoặc các hoạt động insert.
  • Các chỉ mục không nên được sử dụng trên các cột có chứa một số lượng lớn các giá trị NULL.
  • Các cột thường được thao tác không nên được lập chỉ mục.

https://vietjack.com/sql/chi_muc_index_trong_sql.jsp

https://viettuts.vn/sql/index-trong-sql

http://www.sqlviet.com/blog/de-dung-duoc-index-trong-dieu-kien-tim-kiem-cua-cau-lenh

https://viblo.asia/p/su-dung-index-trong-sql-query-1ZnbRlPQR2Xo

https://viblo.asia/p/chi-mucindex-trong-co-so-du-lieu-phan-1-bJzKmMJBK9N

https://viblo.asia/p/chi-mucindex-trong-co-so-du-lieu-phan-2-924lJYN0ZPM

https://viblo.asia/p/chi-mucindex-trong-co-so-du-lieu-phan-3-OeVKB4prlkW

Tối ưu truy vấn

https://viblo.asia/p/5-meo-de-toi-uu-cau-truy-van-sql-cua-ban-RnB5pX4Y5PG

http://www.vertabelo.com/blog/technical-articles/5-tips-to-optimize-your-sql-queries

http://www.nanosoft.vn/home/news/97/detail/185/nguyen-nhan-va-cach-toi-uu-cau-truy-van-sql-trong-lap-trinh.aspx

1) Đánh Index hợp lý

2) Chỉ lấy những cột cần thiết

3) Tránh sử dụng hàm đối với cột của bảng: SELECT * FROM Users WHERE CreatedOn > '2015-04-30';

4) Loại bỏ câu truy vấn con (sub query)

5) Sử dụng '%' OR '_' ở đầu giá trị tìm kiếm LIKE sẽ ngăn việc DB sử dụng index (nếu có) của cột

6) Sử dụng EXISTS thay cho OUTER JOIN (Consider replacing the OUTER JOIN with EXISTS)

7) Để xác định những câu truy vấn chậm bạn có thể sử dụng vết "TSQL by Duration" trong SQL Server Profiler để theo dõi thời gian xử lý của các câu truy vấn

Có thể tìm ra bảng cần đánh index bằng cách sử dụng SQL Server Profiler để tạo Trace Wizard với vết là "Identify Scans of Large Tables"

8) Hạn chế sử dụng function trên column

SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,DOB,GETDATE()) > 21

SELECT member_number, first_name, last_name FROM members WHERE DOB < DATEADD(yy,-21,GETDATE())

9) Sử dụng SQL Stored Procedure vì nó được biên dịch ngay ở lần đầu chạy, những lần sau nó sẽ sử dụng lại cái đã biên dịch trước đó nên tốc độ sẽ nhanh hơn

10) Hạn chế sử dụng DISTINCT và ORDER BY (chú ý rằng UNION tương đương SELECT DISTINCT còn UNION ALL sẽ tương đương SELECT)

11) Hạn chế sử dụng các hàm chuyển đổi kiểu dữ liệu trong mệnh đề WHERE.

12) Ép sử dụng Index để truy vấn nhanh hơn

SELECT lname, fname, address FROM Customers WHERE CusID in (1, 5, 10)

SELECT lname, fname, address FROM Customers (INDEX = IX_CusID) WHERE CusID in (1, 5, 10)

13) Trong các câu truy vấn có 1/nhiều OR bạn có thể viết lại bằng cách kết hợp UNION ALL để tăng tốc độ truy vấn

SET STATISTICS IO ON
SET STATISTICS TIME ON

select * from TimeEntry where UserID<10
UNION ALL
select * from TimeEntry where UserID<20

select * from TimeEntry where UserID < 10 and UserID < 20

14) View không được khuyến khích dùng và nên tránh sử dụng các Views lồng nhau

15) Khi nào dùng IN, EXISTS?

Dùng IN khi: Dữ liệu bảng T1 lớn hơn dữ liệu ở bảng T2
Dùng EXISTS khi: Dữ liệu bảng T2 lớn hơn dữ liệu bảng T1, trường điều kiện được đánh index
Nếu dữ liệu cả 2 bảng đều lớn, hoặc tương đương nhau thì 2 cách dùng có thời gian thực hiện tương đương nhau

16) Sử dụng câu lệnh JOIN thay vì sử dụng SUB-QUERY

17) Sử dụng UPSERT

https://stackoverflow.com/questions/11010511/how-to-upsert-update-or-insert-in-sql-server-2005

18) Nên dùng Constraints thay cho Triggers

19) Thêm câu lệnh SET NOCOUNT ON vào Stored Procedures để dừng thông báo về số dòng được thực thi bởi câu lệnh T-SQL

Database Guide

https://database.guide/how-to-change-the-current-date-format-in-sql-server-t-sql/

https://database.guide/how-to-set-the-current-language-in-sql-server-tsql/

Ví dụ tối ưu

https://dba.stackexchange.com/questions/1215/how-do-i-replace-this-where-clause-with-a-join

http://www.gregreda.com/2013/06/03/join-vs-exists-vs-in/

SET DATEFORMAT ymd;

select * from TimeEntry where StartTimeStamp > '2013-01-23' and StartTimeStamp < '2013-01-24'

Thiết kế CSDL

https://viblo.asia/p/nhung-buoc-co-ban-de-xay-dung-len-mot-co-so-du-lieu-maGK7jXb5j2

https://viblo.asia/p/nhung-sai-lam-trong-thiet-ke-co-so-du-lieu-1Je5E83wlnL

Có thể xóa các Execution Plan được lưu trong bộ nhớ hay không?

Câu trả lời là có, dùng câu lệnh DBCC FREEPROCCACHE

Để hiển thị chi tiết các thông tin vào ra và thời gian CPU:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Các Stored Procedure hệ thống nên biết

sp_help

sp_helptext

sp_MSforeachtable (link)

Đây là store procedure hệ thống giúp bạn duyệt qua tất cả các table trong Database. Khi đó bạn dùng kí tự ? để làm kí tự đại diện cho table. Ví dụ cơ bản nhất khi dùng sp_MSforeachtable là đếm số dòng của tất cả các bảng trong DB

EXEC sp_MSforeachtable 'SELECT ''?'' TableName, Count(1) NumRecords FROM ?'

https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database

https://www.sqlmatters.com/Articles/Listing%20all%20tables%20in%20a%20database%20and%20their%20row%20counts%20and%20sizes.aspx (Count * Size of all tables)

https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/ (HAY HAY HAY)

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

sp_depends

Đây là stored procedure hệ thống giúp bạn liệt kê các Views, store procedure do người dùng định nghĩa, hàm do người dùng định nghĩa, trigger, ... có phụ thuộc vào tên đối tượng truyền vào

sp_spaceused

Đây là stored procedure hệ thống giúp bạn lấy kích thước của Database hoặc kích thước của một đối tượng trong Database.

Full Text Index và Full Text Search

https://techblog.vn/fulltext-search-don-gian-ma-huu-ich

https://www.red-gate.com/simple-talk/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

https://www.databasejournal.com/features/mssql/article.php/1438211/Setting-Up-Full-Text-Search-A-Step-by-step-Guide.htm

Full Text Search ~ Inverted Index ~ Tokenize

Full-text search là một cơ chế mà sql server cho phép bạn tìm kiếm một từ hay một cụm từ một cách hiệu quả và nhanh chóng.

Để tạo Full Text Search ta thực hiện qua các bước sau:

1. Tạo Full-Text Catalog 
2. Tạo Full-Text Index 
3. Tạo Index

  • SELECT SERVERPROPERTY('IsFullTextInstalled')
    • LIKE
    • FREETEXT
    • CONTAINS
  • Ý nghĩa
    • Tìm từ đồng nghĩa
    • Tìm từ gần đúng, so từng từ trong cụm từ, đảo vị trí
    • Hỗ trợ tìm kiếm có đấu
    • Loại bỏ các từ khóa noise (nhiễu)

Việc tách string hay còn gọi là tokenize là kỹ thuật quan trọng trong Full Text Search

  • char
  • nchar
  • varchar
  • nvarchar
  • text
  • ntext

CREATE TABLE Products
(
    [ProductID] VARCHAR(10) CONSTRAINT PK_Products PRIMARY KEY,
    [Description] NVARCHAR(MAX) NOT NULL
)

CREATE FULLTEXT CATALOG MyCatalogName
GO

CREATE FULLTEXT INDEX ON Products(ProductID)
 KEY INDEX PK_Products
 ON MyCatalogName
GO

 

SELECT name, collation_name FROM sys.databases  
WHERE name = 'AdventureWorks2008'
SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs