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 và 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
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 Orders where UserID<10
UNION ALL
select * from Orders where UserID<20
select * from Orders 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 Orders WHERE StartTime > '2013-01-23' AND EndTime < '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://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
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
